HarborDB
بازگشت به وبلاگ
۱۰ نکته عملکرد SQLite برای برنامه‌های iOS و macOS
SQLite
Performance
iOS
macOS

۱۰ نکته عملکرد SQLite برای برنامه‌های iOS و macOS

۲۳ مهر ۱۴۰۴
Team HarborDB

SQLite ستون فقرات اکوسیستم Apple است. این Core Data را تغذیه می‌کند، از دسترسی مستقیم معمولی به پایگاه داده در iOS و macOS پشتیبانی می‌کند و به عنوان فرمت فایل برنامه برای برنامه‌های بی‌شماری خدمت می‌کند. در حالی که SQLite به طور مشهور "بدون پیکربندی" است، بردن آن از یک پیاده‌سازی پایه به یک موتور با کارایی بالا که قادر به مدیریت گیگابایت‌ها داده و هزاران عملیات در ثانیه است، نیاز به درک عمیق و تنظیم عمدی دارد.

در این راهنمای جامع، ما به عمق داخلی SQLite می‌پردازیم، پیکربندی پیشرفته (PRAGMAs)، استراتژی‌های ایندکس‌گذاری و الگوهای معماری را که به طور خاص برای محدودیت‌ها و قابلیت‌های دستگاه‌های iOS و macOS طراحی شده‌اند، بررسی می‌کنیم.

۱. فعال‌سازی حالت Write-Ahead Logging (WAL)

مؤثرترین تغییری که می‌توانید در یک پایگاه داده استاندارد SQLite ایجاد کنید، کمک به آن در مدیریت همزمانی است. به طور پیش‌فرض، SQLite از یک "rollback journal" برای اطمینان از اتمی بودن و پایداری استفاده می‌کند. هنگامی که یک تراکنش نوشتاری رخ می‌دهد، SQLite صفحات اصلی را قبل از نوشتن در فایل پایگاه داده اصلی در یک فایل journal جداگانه کپی می‌کند. در طول این فرآیند، پایگاه داده برای خواندن قفل می‌شود. این به این معنی است که خوانندگان نویسندگان را مسدود می‌کنند و نویسندگان خوانندگان را مسدود می‌کنند.

حالت WAL (Write-Ahead Logging) این را معکوس می‌کند. تغییرات در یک "فایل WAL" جداگانه (که اغلب به -wal ختم می‌شود) نوشته می‌شوند و خوانندگان به خواندن از فایل پایگاه داده اصلی (و فایل WAL) بدون مسدود شدن توسط نویسنده ادامه می‌دهند.

چرا WAL برای برنامه‌های iOS اهمیت دارد

در iOS، رشته UI (Main Thread) اغلب نیاز به خواندن داده برای پر کردن table views یا collection views دارد. اگر یک رشته پس‌زمینه در حال همگام‌سازی داده از یک API و نوشتن در DB در حالت Rollback Journal باشد، Main Thread شما مسدود می‌شود و باعث لرزش UI (فریم‌های از دست رفته) می‌شود. با حالت WAL، همگام‌سازی پس‌زمینه شما می‌تواند آزادانه بنویسد در حالی که رشته UI شما آخرین اسنپ‌شات commit شده را می‌خواند.

نحوه فعال‌سازی

شما این را از طریق یک دستور PRAGMA اجرا می‌کنید. این پایدار است، بنابراین شما تکنیکی فقط نیاز دارید یک بار این کار را انجام دهید، اما عمل خوبی است که آن را در باز شدن اتصال اجرا کنید.

PRAGMA journal_mode = WAL;

خروجی: wal

غواصی عمیق: Checkpoints

در حالت WAL، فایل -wal به طور نامحدود رشد می‌کند تا زمانی که یک عملیات "checkpoint" صفحات WAL را به پایگاه داده اصلی .sqlite منتقل کند. به طور پیش‌فرض، SQLite یک checkpoint خودکار ایجاد می‌کند وقتی WAL به ۱۰۰۰ صفحه برسد (تقریباً ۴MB). با این حال، برای بارهای کاری نوشتن سنگین، ممکن است بخواهید wal_autocheckpoint را تنظیم کنید یا آنها را به صورت دستی با استفاده از PRAGMA wal_checkpoint(PASSIVE|FULL|RESTART|TRUNCATE) فعال کنید.

۲. استفاده صریح از Transactions

این یک تصور نادرست رایج است که دستورات SQL فردی به اندازه کافی سریع هستند. به طور پیش‌فرض، SQLite یک تراکنش برای هر دستور واحد باز می‌کند اگر یکی به صورت دستی شروع نشده باشد. این به این معنی است که INSERT INTO table... در واقع این کار را انجام می‌دهد:

  1. باز کردن تراکنش
  2. ایجاد فایل journal
  3. نوشتن داده
  4. همگام‌سازی با دیسک (fsync)
  5. بستن تراکنش
  6. حذف/باطل کردن journal

این fsync پرهزینه است، به خصوص در حافظه فلش موبایل.

رویکرد دسته‌ای

عملیات خود را گروه‌بندی کنید:

// بد: ۱۰۰۰ fsync
for user in users {
    db.run("INSERT INTO users (name) VALUES (?)", user.name)
}

// خوب: ۱ fsync
db.transaction {
    for user in users {
        db.run("INSERT INTO users (name) VALUES (?)", user.name)
    }
}

معیارها معمولاً بهبود سرعت ۱۰۰ برابر تا ۱۰۰۰ برابر را برای درج‌های عمده هنگام استفاده از تراکنش‌های صریح نشان می‌دهند.

۳. تسلط بر Indexes: B-Trees، Covering و Partial Indexes

ایندکس‌گذاری یک هنر است. خیلی کم، و شما کل جدول را اسکن می‌کنید (O(n)). خیلی زیاد، و ایجادها/به‌روزرسانی‌های شما کند می‌شوند (O(log n) به ازای هر ایندکس).

تجسم B-Tree

SQLite از B-Tree های جداگانه برای جدول (مبتنی بر rowid) و برای هر ایندکس استفاده می‌کند.

  • یک Table B-Tree داده‌های واقعی را در گره‌های برگ ذخیره می‌کند.
  • یک Index B-Tree کلید ایندکس شده + rowid را ذخیره می‌کند.

Covering Indexes

برای پرس‌وجو صرفاً از ایندکس بدون لمس جدول اصلی (بارگذاری صفحات اضافی)، تمام ستون‌های بازیابی شده را در ایندکس قرار دهید.

سناریو: شما یک جدول users با id، name، email، last_login دارید. پرس‌وجو: SELECT email FROM users WHERE name = 'Alice'

ایندکس استاندارد: CREATE INDEX idx_name ON users(name);

  1. idx_name را برای 'Alice' جستجو کنید -> rowid دریافت کنید.
  2. rowid را در Table B-Tree جستجو کنید -> صفحه را بارگذاری کنید -> email دریافت کنید.

Covering Index: CREATE INDEX idx_name_email ON users(name, email);

  1. idx_name_email را برای 'Alice' جستجو کنید. email احتمالاً درست در آنجا در گره برگ ایندکس است. نتیجه فوراً برگردانده می‌شود. صفر جستجوی جدول.

Partial Indexes

اگر فقط یک زیرمجموعه از داده را پرس‌وجو می‌کنید (مثلاً کاربران "فعال")، برای صرفه‌جویی در فضا کل جدول را ایندکس نکنید.

CREATE INDEX idx_active_users_email 
ON users(email) 
WHERE status = 'active';

این ایندکس کوچکتر است، سریعتر به‌روزرسانی می‌شود و سریعتر پرس‌وجو می‌شود.

۴. Prepared Statements غیرقابل مذاکره هستند

تجزیه SQL فشرده CPU است. SELECT * FROM users WHERE id = 1 و SELECT * FROM users WHERE id = 2 رشته‌های کاملاً متفاوتی برای تجزیه‌کننده SQLite هستند.

یک Prepared Statement طرح پرس‌وجوی SQL را به بایت‌کد کامپایل می‌کند. سپس می‌توانید پارامترها را به آن "bind" کنید و چندین بار اجرا کنید.

مثال Swift (با استفاده داخلی از GRDB یا SQLite.swift)

وقتی از یک کتابخانه به طور مؤثر استفاده می‌کنید، این دستورات را کش می‌کند. از placeholders (?) به جای interpolation رشته استفاده کنید:

// بد - کامپایل در هر تکرار حلقه رخ می‌دهد
let sql = "INSERT INTO users (name) VALUES ('\(name)')"
try db.execute(sql)

// خوب - یک بار کامپایل، چندین بار bind
let stmt = try db.prepare("INSERT INTO users (name) VALUES (?)")
for name in names {
    try stmt.run(name)
}

این سربار CPU را به طور قابل توجهی کاهش می‌دهد که همچنین عمر باتری را در دستگاه‌های موبایل ذخیره می‌کند.

۵. بهینه‌سازی تنظیمات PRAGMA خود

پیش‌فرض‌های SQLite محافظه‌کارانه هستند (با هدف سخت‌افزار دوران ۲۰۰۵). iPhone های مدرن دارای ذخیره‌سازی کلاس NVMe و گیگابایت‌ها RAM هستند.

temp_store

به طور پیش‌فرض، جداول موقت و ایندکس‌ها روی دیسک ذخیره می‌شوند. آنها را به RAM منتقل کنید:

PRAGMA temp_store = MEMORY;

mmap_size

I/O نگاشت حافظه می‌تواند سریعتر از syscall های خواندن/نوشتن استاندارد باشد زیرا از کپی کردن داده بین فضای kernel و فضای کاربر جلوگیری می‌کند.

PRAGMA mmap_size = 268435456; -- 256MB

توجه: اگر هشدارهای فشار حافظه بالا در iOS دارید با mmap مراقب باشید، اما برای مجموعه داده‌های متوسط، این یک تقویت‌کننده خواندن بزرگ است.

synchronous

در حالت WAL، پیش‌فرض NORMAL است. این برای اکثر برنامه‌ها به اندازه کافی امن است (ممکن است آخرین تراکنش را در صورت قطع برق از دست بدهید، اما فساد پایگاه داده نه). از FULL یا EXTRA اجتناب کنید مگر اینکه نرم‌افزار بانکی می‌نویسید که در آن پایداری بهبود یافته ارزش جریمه سرعت نوشتن ~۲۰٪ را دارد.

PRAGMA synchronous = NORMAL;

۶. درک Query Planner (EXPLAIN QUERY PLAN)

هرگز حدس نزنید چرا یک پرس‌وجو کند است. SQLite به شما می‌گوید. پرس‌وجوی خود را با EXPLAIN QUERY PLAN پیشوند دهید.

EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 5 AND created_at > 1000;

تحلیل خروجی:

  • SCAN TABLE: بد. در حال خواندن هر ردیف است.
  • SEARCH TABLE USING INDEX: خوب. از جستجوی B-Tree استفاده می‌کند.
  • USE TEMP B-TREE: هشدار. مجبور شد یک ایندکس موقت بسازد (احتمالاً برای مرتب‌سازی).

HarborDB (محصول ما) به شدت بر تجسم این طرح‌ها تمرکز دارد، اما می‌توانید خروجی خام را در هر CLI بخوانید.

۷. مدیریت دقیق SQLITE_BUSY

حتی در حالت WAL، قفل‌های مشترک می‌توانند رخ دهند. اگر یک نویسنده در حال نهایی کردن commit باشد، یک خواننده ممکن است برای مدت کوتاهی مسدود شود. به طور پیش‌فرض، SQLite فوراً SQLITE_BUSY را برمی‌گرداند. در iOS، باید یک timeout تنظیم کنید تا SQLite قبل از تسلیم شدن به صورت داخلی دوباره تلاش کند.

PRAGMA busy_timeout = 3000; -- 3000 میلی‌ثانیه

این تنظیم ساده ۹۹٪ خطاهای "Database is locked" را در برنامه‌های همزمان جلوگیری می‌کند.

۸. Denormalization برای سرعت موبایل

نرمال‌سازی دقیق (3NF) برای کارایی ذخیره‌سازی و سازگاری عالی است، اما صفحه‌های موبایل اغلب به داده "Joined" فوراً نیاز دارند. Join ها گران هستند (O(M * log N)).

اگر یک لیست Posts دارید که AuthorName و CommentCount را نشان می‌دهد، ذخیره AuthorName و CommentCount را مستقیماً در جدول Posts در نظر بگیرید. بله، باید جدول Posts را هنگامی که یک نویسنده نام خود را تغییر می‌دهد یا یک نظر اضافه می‌شود به‌روزرسانی کنید (Triggers می‌توانند این را خودکار کنند!)، اما سرعت READ شما (که ۱۰۰ برابر بیشتر از نوشتن اتفاق می‌افتد) به جای انجام JOIN های عظیم در اسکرول به O(1) به ازای هر ردیف تبدیل می‌شود.

۹. Pagination کارآمد (از OFFSET اجتناب کنید)

صفحه‌بندی وب استاندارد LIMIT 20 OFFSET 100 برای مجموعه داده‌های بزرگ در SQLite سمی است. برای رسیدن به OFFSET 10000، SQLite باید ۱۰,۰۰۰ ردیف اول را محاسبه کند و آنها را دور بیندازد. هر چه عمیق‌تر صفحه‌بندی کنید، به صورت خطی کندتر می‌شود.

روش Keysset (Seek): آخرین مقدار دیده شده را به خاطر بسپارید. پرس‌وجو: SELECT * FROM items WHERE created_at < ? ORDER BY created_at DESC LIMIT 20

این از ایندکس برای پرش فوری به نقطه صحیح استفاده می‌کند. بدون توجه به عمق صفحه O(log N) است.

۱۰. Vacuuming و نگهداری

پایگاه‌های داده تکه‌تکه می‌شوند. وقتی داده را حذف می‌کنید، SQLite صفحات را به عنوان "آزاد" علامت‌گذاری می‌کند اما اندازه فایل را کوچک نمی‌کند.

  • VACUUM: فایل DB تخصصی را از ابتدا بازسازی می‌کند. کاملاً فشرده. کند. قفل انحصاری.
  • PRAGMA auto_vacuum = INCREMENTAL: به شما اجازه می‌دهد فضا را به صورت دوره‌ای بدون قفل کردن دنیا بازیابی کنید.

برای برنامه‌های iOS، توصیه می‌کنیم:

  1. از auto_vacuum = NONE (پیش‌فرض) برای عملکرد استفاده کنید.
  2. فقط در طول مهاجرت‌های اصلی یا پنجره‌های نگهداری نادر (مثلاً به‌روزرسانی برنامه) VACUUM کامل را اجرا کنید، زیرا به فضای دیسک (۲ برابر اندازه DB) و زمان نیاز دارد.

نتیجه‌گیری

بهینه‌سازی عملکرد SQLite/PostgreSQL سفری از درک سخت‌افزار، سیستم فایل و ساختارهای B-Tree است. با فعال کردن حالت WAL، استفاده از ایندکس‌های مناسب و درک query planner، تقریباً هر عملکرد "کند" در برنامه iOS/macOS شما می‌تواند فوری شود.

برای توسعه‌دهندگانی که می‌خواهند این معیارهای عملکرد را به صورت real-time ببینند و طرح‌های پرس‌وجوی خود را تجسم کنند، ابزارهای بومی مانند HarborDB مزیت قابل توجهی نسبت به حدس‌زدن خط فرمان ارائه می‌دهند.