هنطلع من الدرس بإيه؟
هتقدر تقرأ query plan وتعرف الـ query بتعمل SCAN ولا SEARCH، تصمم composite index بالترتيب الصح، تفهم الفرق بين rollback journal و WAL، تتعامل مع database is locked، وتاخد backup صحيح لـ database شغالة.
- تفسّر خرج
EXPLAIN QUERY PLAN. - تقيس أثر الـ transaction على سرعة الكتابة بدل ما تخمّن.
- تفعّل WAL وتضبط
busy_timeoutوتشرح حدود الـ concurrency. - تاخد backup بـ Online Backup API وتتحقق بـ
integrity_check.
المتطلبات: SQLite من الصفر. مفهوم الـ index عامة مشروح في SQL Query Optimization.
Indexes وقراءة الـ Query Plan
الـ index هو هيكل مرتب (B-tree) بيخلي SQLite تروح للصفوف المطلوبة بدل ما تقرا الجدول كله. بنسأل SQLite نفسها عن الخطة بـ EXPLAIN QUERY PLAN: كلمة SCAN يعني قراءة كل الصفوف، وSEARCH يعني استخدام index للوصول المباشر.
import sqlite3
con = sqlite3.connect(":memory:")
con.execute("""CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL,
status TEXT NOT NULL, total_cents INTEGER NOT NULL, created TEXT NOT NULL) STRICT""")
con.executemany("INSERT INTO orders (customer_id,status,total_cents,created) VALUES (?,?,?,?)",
[(i % 500, 'paid' if i % 7 else 'new', 1000 + i, f"2026-01-{1 + i % 28:02d}") for i in range(20000)])
con.commit()
def plan(sql):
return [r[3] for r in con.execute("EXPLAIN QUERY PLAN " + sql)]
q = "SELECT total_cents FROM orders WHERE customer_id = 42 AND status = 'paid'"
print("1) no index :", plan(q))
con.execute("CREATE INDEX ix_orders_customer_status ON orders (customer_id, status)")
print("2) composite index :", plan(q))
print("3) leading col only :", plan("SELECT total_cents FROM orders WHERE customer_id = 42"))
print("4) skipped leading :", plan("SELECT total_cents FROM orders WHERE status = 'paid'"))
print("5) expression on col :", plan("SELECT total_cents FROM orders WHERE customer_id + 0 = 42"))
print("6) covering :", plan("SELECT customer_id, status FROM orders WHERE customer_id = 42"))
print("7) ORDER BY :", plan("SELECT id FROM orders WHERE customer_id = 42 ORDER BY created"))1) no index : ['SCAN orders']
2) composite index : ['SEARCH orders USING INDEX ix_orders_customer_status (customer_id=? AND status=?)']
3) leading col only : ['SEARCH orders USING INDEX ix_orders_customer_status (customer_id=?)']
4) skipped leading : ['SCAN orders']
5) expression on col : ['SCAN orders']
6) covering : ['SEARCH orders USING COVERING INDEX ix_orders_customer_status (customer_id=?)']
7) ORDER BY : ['SEARCH orders USING INDEX ix_orders_customer_status (customer_id=?)', 'USE TEMP B-TREE FOR ORDER BY']اقرأ النتايج بدقة: (1) من غير index = SCAN. (2) بعد الـ composite index بقت SEARCH على العمودين. (3) الـ query اللي بتستخدم العمود الأول بس بتستفيد برضه (leftmost prefix). (4) لو تخطيت العمود الأول، مفيش SEARCH ورجعنا لـ SCAN (الـ index مرتب بالعمود الأول فمينفعش للبحث بالتاني لوحده). (5) وضع expression على العمود (customer_id + 0) بيمنع استخدامه للـ SEARCH (نفس مبدأ SARGability في SQL Server). (6) COVERING INDEX يعني كل الأعمدة المطلوبة جوه الـ index فمفيش رجوع للجدول. (7) عند ORDER BY بعمود مش في الـ index، هتلاقي خطوة USE TEMP B-TREE FOR ORDER BY لو ظهرت في الخرج.
=) الأول، وبعدين عمود الـ range أو الـ ORDER BY. كل index إضافي بيبطّئ INSERT/UPDATE ويكبّر الملف، فاعمل index للـ queries الحقيقية بس، وقِس بـ EXPLAIN وبالـ benchmark على بيانات بحجم الإنتاج.Transactions: ليه الكتابة الكتير لازم تتجمّع؟
كل statement كتابة خارج transaction صريحة بيعمل commit لوحده، وكل commit = مزامنة مع الـ disk (fsync). تجميع الكتابات في transaction واحدة بيحوّل آلاف المزامنات لمزامنة واحدة. بدل ما نقول «أسرع بكتير» بنقيس ونطبع النسبة فقط (الأرقام المطلقة بتختلف من جهاز لجهاز).
import sqlite3, tempfile, os, time
def bench(batched, n=2000):
path = os.path.join(tempfile.mkdtemp(), "t.db")
con = sqlite3.connect(path, isolation_level=None) # autocommit: إحنا اللي بنتحكم في BEGIN/COMMIT
con.execute("CREATE TABLE log (id INTEGER PRIMARY KEY, msg TEXT NOT NULL)")
t0 = time.perf_counter()
if batched:
con.execute("BEGIN")
for i in range(n):
con.execute("INSERT INTO log (msg) VALUES (?)", (f"row {i}",))
if batched:
con.execute("COMMIT")
dt = time.perf_counter() - t0
count = con.execute("SELECT COUNT(*) FROM log").fetchone()[0]
con.close()
return dt, count
slow, c1 = bench(False)
fast, c2 = bench(True)
print("rows written (each mode):", c1, c2)
print("one transaction faster than many:", fast < slow)
print("speed-up >= 3x on this machine :", slow / fast >= 3)
# atomicity: rollback بيرجّع كل حاجة
con = sqlite3.connect(":memory:", isolation_level=None)
con.execute("CREATE TABLE acct (id INTEGER PRIMARY KEY, bal INTEGER NOT NULL CHECK (bal >= 0))")
con.executemany("INSERT INTO acct VALUES (?, ?)", [(1, 100), (2, 0)])
try:
con.execute("BEGIN")
con.execute("UPDATE acct SET bal = bal + 150 WHERE id = 2")
con.execute("UPDATE acct SET bal = bal - 150 WHERE id = 1") # هتخالف CHECK
con.execute("COMMIT")
except sqlite3.IntegrityError as e:
con.execute("ROLLBACK")
print("transfer failed and rolled back:", e)
print("balances after rollback:", con.execute("SELECT id, bal FROM acct ORDER BY id").fetchall())rows written (each mode): 2000 2000
one transaction faster than many: True
speed-up >= 3x on this machine : True
transfer failed and rolled back: CHECK constraint failed: bal >= 0
balances after rollback: [(1, 100), (2, 0)]الجزء التاني بيوضح الـ atomicity: التحويل فشل في الخطوة التانية، والـ ROLLBACK رجّع الخطوة الأولى كمان، فالرصيدين زي ما هما. ملاحظة تنفيذية: في Python module sqlite3 سلوك الـ transactions الضمني اتغيّر بين الإصدارات (الـ attribute autocommit في 3.12+)، لذلك استخدمنا isolation_level=None للتحكم الصريح؛ راجع توثيق إصدارك.
Journal modes و WAL والـ Concurrency
الوضع الافتراضي (rollback journal) بيكتب النسخة القديمة للصفحات في journal قبل التعديل، والكاتب بيقفل الـ database بحيث القراء بيستنوا وقت الكتابة. وضع WAL (Write-Ahead Log) بيعكس الفكرة: التعديلات بتتكتب append في ملف -wal، فـالقراء ما بيتحجبوش بالكاتب، والكاتب ما بيتحجبش بالقراء. لكن لسه فيه كاتب واحد في كل وقت. الملفات الناتجة: shop.db و shop.db-wal و shop.db-shm، وأي backup يدوي لازم ياخدهم في الاعتبار (الأحسن تستخدم Backup API).
import sqlite3, tempfile, os
path = os.path.join(tempfile.mkdtemp(), "shop.db")
setup = sqlite3.connect(path)
print("default journal_mode:", setup.execute("PRAGMA journal_mode").fetchone()[0])
print("after enabling :", setup.execute("PRAGMA journal_mode=WAL").fetchone()[0])
setup.execute("CREATE TABLE stock (sku TEXT PRIMARY KEY, qty INTEGER NOT NULL) STRICT")
setup.execute("INSERT INTO stock VALUES ('KB-1', 10)")
setup.commit(); setup.close()
def open_db(timeout):
c = sqlite3.connect(path, timeout=timeout, isolation_level=None, check_same_thread=False) # timeout = busy_timeout بالثواني
return c
writer, reader, writer2 = open_db(0), open_db(0), open_db(0)
writer.execute("BEGIN IMMEDIATE") # الكاتب أخد write lock
writer.execute("UPDATE stock SET qty = 99 WHERE sku = 'KB-1'")
# 1) القارئ مش متحجب، وبيشوف آخر قيمة committed (snapshot)
print("reader during write sees qty =", reader.execute("SELECT qty FROM stock").fetchone()[0])
# 2) كاتب تاني بيفشل فورًا لأن فيه كاتب واحد بس
try:
writer2.execute("BEGIN IMMEDIATE")
except sqlite3.OperationalError as e:
print("second writer:", e)
writer.execute("COMMIT")
print("reader after commit sees qty =", reader.execute("SELECT qty FROM stock").fetchone()[0])
# 3) نفس السيناريو بـ busy_timeout: الكاتب التاني يستنى بدل ما يفشل
writer.execute("BEGIN IMMEDIATE")
import threading, time
def release_soon():
time.sleep(0.3); writer.execute("COMMIT")
threading.Thread(target=release_soon).start()
w3 = open_db(2.0)
t0 = time.perf_counter()
w3.execute("BEGIN IMMEDIATE") # هيستنى لحد ما writer يعمل COMMIT
waited = time.perf_counter() - t0
w3.execute("COMMIT")
print("writer with busy_timeout waited and then succeeded:", 0.2 < waited < 2.0)
print("synchronous:", w3.execute("PRAGMA synchronous").fetchone()[0], "(1 = NORMAL, 2 = FULL)")default journal_mode: delete
after enabling : wal
reader during write sees qty = 10
second writer: database is locked
reader after commit sees qty = 99
writer with busy_timeout waited and then succeeded: True
synchronous: 2 (1 = NORMAL, 2 = FULL)الخطوات: (1) القارئ شاف القيمة القديمة الـ committed (10) مش القيمة اللي لسه ما اتعملهاش commit (99): ده snapshot isolation للقراء في WAL. (2) الكاتب التاني اتضرب بخطأ database is locked لأن timeout = 0. (3) مع busy_timeout الكاتب التاني استنى لحد ما الأول خلّص. الدرس العملي: في تطبيق فيه أكتر من connection كاتب: فعّل WAL، اضبط busy timeout، خلّي الـ write transactions قصيرة، وابدأ الكتابة بـ BEGIN IMMEDIATE لو هتقرا الأول وتعدّل (بدل ما تترقى من read لـ write وتاخد SQLITE_BUSY في النص). الـ checkpoint هو عملية بتنقل محتوى ملف WAL لملف الـ database الرئيسي، وبتحصل تلقائيًا (الافتراضي كل ~1000 صفحة) أو يدويًا بـ PRAGMA wal_checkpoint.
Backup و Integrity و VACUUM
نسخ ملف الـ database بـ cp وهي شغالة ممكن يطلع نسخة تالفة (نص كتابة). الطريقة الصح: Online Backup API (في Python: Connection.backup) أو الأمر VACUUM INTO 'file'. وبعدها نتحقق بـ PRAGMA integrity_check.
import sqlite3, tempfile, os
d = tempfile.mkdtemp()
live = sqlite3.connect(os.path.join(d, "live.db"))
live.execute("PRAGMA journal_mode=WAL")
live.execute("CREATE TABLE note (id INTEGER PRIMARY KEY, body TEXT NOT NULL)")
live.executemany("INSERT INTO note (body) VALUES (?)", [(f"note {i}" * 20,) for i in range(5000)])
live.commit()
# 1) Online backup: نسخة متسقة حتى لو الـ database شغالة
dest = sqlite3.connect(os.path.join(d, "backup.db"))
live.backup(dest)
print("backup rows :", dest.execute("SELECT COUNT(*) FROM note").fetchone()[0])
print("integrity_check :", dest.execute("PRAGMA integrity_check").fetchone()[0])
dest.close()
# 2) VACUUM INTO: نسخة مضغوطة ومرتبة
live.execute("VACUUM INTO ?", (os.path.join(d, "vacuumed.db"),))
print("vacuum-into file exists:", os.path.exists(os.path.join(d, "vacuumed.db")))
# 3) الحذف مش بيصغّر الملف؛ VACUUM هو اللي بيعمل كده
size = lambda: os.path.getsize(os.path.join(d, "live.db"))
live.execute("PRAGMA wal_checkpoint(TRUNCATE)")
before_delete = size()
live.execute("DELETE FROM note WHERE id > 100"); live.commit()
live.execute("PRAGMA wal_checkpoint(TRUNCATE)")
after_delete = size()
live.execute("VACUUM")
live.execute("PRAGMA wal_checkpoint(TRUNCATE)") # في وضع WAL نتيجة VACUUM بتتنقل للملف الرئيسي بعد الـ checkpoint
after_vacuum = size()
print("file size unchanged by DELETE :", after_delete == before_delete)
print("file shrank after VACUUM :", after_vacuum < after_delete)
# 4) Restore = فتح ملف الـ backup (أو نسخه مكان الملف الأصلي والتطبيق متوقف)
restored = sqlite3.connect(os.path.join(d, "backup.db"))
print("restored rows :", restored.execute("SELECT COUNT(*) FROM note").fetchone()[0])backup rows : 5000
integrity_check : ok
vacuum-into file exists: True
file size unchanged by DELETE : True
file shrank after VACUUM : True
restored rows : 5000لاحظ إن الـ backup شاف 5000 صف مع إن الـ live اتقلّصت بعدين لـ 100: النسخة لقطة متسقة من لحظة الـ backup. والـ DELETE ما صغّرش الملف (الصفحات الفاضية بتتعاد استخدامها)، لكن VACUUM بيعيد كتابة الملف. تفصيلة اتأكدت بالتجربة: في وضع WAL حجم الملف الرئيسي ما بيتغيرش بعد VACUUM إلا بعد الـ checkpoint، لأن النتيجة بتتكتب الأول في ملف WAL (أول محاولة لنا قاست الحجم قبل الـ checkpoint وطلعت «ما صغرش»). VACUUM محتاج مساحة فاضية تقريبًا بحجم الـ database، ويقفل الكتابة وقت التنفيذ.
WAL من جوه: checkpoints وليه الـ readers ما بيستنوش
- الكتابة: بدل ما تعدّل pages في الملف الأصلي، الـ writer بيضيف نسخ معدلة في آخر الـ
-wal. الـ commit = frame خاص بيقول «لحد هنا». - القراءة: كل reader بياخد snapshot (آخر commit لحظة بدايته) وبيقرأ الـ page من الـ WAL لو ليها نسخة أحدث ≤ الـ snapshot، وإلا من الملف الأصلي. عشان كده القرّاء ما بيستنوش الكاتب.
- Checkpoint: بينقل الـ pages من الـ WAL للملف الأصلي. ما يقدرش يخلص لو فيه reader طويل لسه بيستخدم snapshot قديم ← الـ WAL بيكبر. Long-running read transactions = WAL ضخم.
PRAGMA journal_mode = WAL; -- persistent: stored in the database file
PRAGMA wal_autocheckpoint = 1000; -- pages (default); lower = smaller WAL, more checkpoint work
PRAGMA wal_checkpoint(TRUNCATE); -- e.g. before copying the file for a backupLocking و SQLITE_BUSY: الأسباب والحلول
| العرض | السبب الشائع | الحل |
|---|---|---|
database is locked فورًا | مفيش busy_timeout، writer تاني شغال | PRAGMA busy_timeout=5000 |
| Deadlock-like BUSY حتى مع timeout | transaction بدأت read وبعدين حاولت تكتب (upgrade) وفيه writer تاني | BEGIN IMMEDIATE لأي transaction ناوية تكتب — تاخد write lock من الأول |
| Writes بطيئة جدًا | autocommit لكل صف | batch في transaction واحدة |
| WAL بيكبر GBs | reader طويل/connection مفتوحة في transaction | اقفل read transactions بسرعة، checkpoint دوري |
| Corruption على network share | SQLite على NFS/SMB (locking غير موثوق) | ما تحطش SQLite على network filesystem؛ استخدم server DB |
await using var conn = new SqliteConnection(cs);
await conn.OpenAsync(ct);
await using (var begin = conn.CreateCommand()) { begin.CommandText = "BEGIN IMMEDIATE"; await begin.ExecuteNonQueryAsync(ct); }
try
{
// read-modify-write safely: no other writer can sneak in between the read and the write
...
await using var commit = conn.CreateCommand(); commit.CommandText = "COMMIT"; await commit.ExecuteNonQueryAsync(ct);
}
catch { await using var rb = conn.CreateCommand(); rb.CommandText = "ROLLBACK"; await rb.ExecuteNonQueryAsync(CancellationToken.None); throw; }Backup صح لملف شغال
نسخ الملف بـ File.Copy وهو شغال ممكن يطلع نسخة متضاربة (pages من قبل ومن بعد الكتابة، أو WAL ما اتنسخش). الطرق الآمنة: Online Backup API (connection.BackupDatabase(dest) في Microsoft.Data.Sqlite) أو VACUUM INTO 'backup.db' اللي بيعمل نسخة مضغوطة ومتسقة. وبعد الـ backup: افتح النسخة واعمل PRAGMA integrity_check — backup ما اتختبرش مش backup.
await using var src = new SqliteConnection("Data Source=app.db"); await src.OpenAsync();
await using var dst = new SqliteConnection($"Data Source=backup-{DateTime.UtcNow:yyyyMMddHHmm}.db"); await dst.OpenAsync();
src.BackupDatabase(dst); // page-by-page consistent copy
await using var check = dst.CreateCommand(); check.CommandText = "PRAGMA integrity_check";
var result = (string?)await check.ExecuteScalarAsync();
if (result != "ok") throw new InvalidOperationException($"Backup failed integrity check: {result}");تمرين محلول إضافي
ملف الـ -wal وصل 3 GB والتطبيق شغال من أسبوع. ليه وإيه الحل؟
الحل: الـ checkpoint ما بيقدرش يرجّع الـ WAL لأوله طول ما فيه reader ماسك snapshot قديم — غالبًا connection فاتحة read transaction ومش بتقفلها. اقفل الـ transactions بسرعة، اعمل PRAGMA wal_checkpoint(TRUNCATE) في وقت هادي، وراقب حجم الـ WAL كـ metric.
أخطاء شائعة
- إضافة index على كل عمود «احتياطي»: بتبطّئ الكتابة وتكبر الملف.
- كتابة 10,000 صف كل واحد في transaction لوحده.
- تجاهل
database is lockedبدل ما تضبط WAL و busy timeout وتقصّر الـ transactions. - نسخ ملف
.dbوحده وتجاهل-walأو نسخه أثناء الكتابة. - transaction طويلة بتفضل مفتوحة أثناء استدعاء HTTP خارجي: الكاتب الوحيد محجوز.
- تشغيل SQLite على ملف مشترك بين أجهزة.
خمس تمارين محلولة
1 (Easy): query بتعمل SCAN على جدول كبير. إيه أول خطوة؟
الحل: شغّل EXPLAIN QUERY PLAN على الـ query، حدد أعمدة الـ WHERE، واعمل index عليهم (المساواة الأول)، وأعد الاختبار. لو ما اتغيرش، شوف إن العمود مش جوه expression أو function.
2 (Easy): ليه الـ index على (customer_id, status) بيخدم query على customer_id بس؟ وليه ما يخدمش status بس؟
الحل: الـ index مرتب بـ customer_id أولًا ثم status، فالبحث بالعمود الأول مباشر (leftmost prefix). البحث بـ status وحده محتاج يعدّي على كل الـ customers، فهو مش SEARCH (وده اللي شفناه في النتيجة 4).
3 (Intermediate): عندك تحميل CSV بـ 100,000 صف بياخد وقت طويل. إيه الحل؟
الحل: لف الإدخال كله (أو دفعات 5–10 آلاف) في transaction واحدة واستخدم executemany مع parameters. لو التحميل مرة واحدة، ممكن تعمل الـ indexes بعد التحميل بدل قبله. قيس قبل وبعد.
4 (Intermediate): تطبيق ويب بيرجّع database is locked تحت حمل. اكتب خطة علاج من 4 نقاط
الحل: (1) فعّل WAL. (2) اضبط busy_timeout (مثلًا 5000ms). (3) قصّر الـ write transactions وما تعملش network calls جواها. (4) استخدم BEGIN IMMEDIATE للـ read-then-write. وبعدها لو الحمل فعلاً كتابة عالية ومتوازية: ده مؤشر إن محتاج server database (PostgreSQL).
5 (Challenging): صمم استراتيجية backup يومية لتطبيق inventory بيشتغل 24/7 بـ WAL
الحل: job يومي بيستخدم Backup API أو VACUUM INTO لملف بتاريخه في مكان تاني، يتحقق بـ PRAGMA integrity_check على النسخة، يحسب checksum، ويحتفظ بـ N نسخ (retention). لازم تجرّب الـ restore فعليًا على جهاز تاني دوريًا؛ backup ما اتجرّبش restore منه مش backup. وثّق RPO (آخر نسخة = أقصى فقد بيانات) و RTO (وقت الاسترجاع).
Mini Project: أداة صيانة database
اكتب CLI صغيرة dbtool بالأوامر: plan "<sql>" (تعرض EXPLAIN QUERY PLAN)، backup <dest> (Backup API + integrity_check)، vacuum، و stats (حجم الملف، عدد الصفحات page_count، freelist_count). معيار النجاح: اختبارات unittest على database مؤقتة، backup بيتحقق منه تلقائيًا، و README بيشرح RPO/RTO.
أسئلة إنترفيو
إزاي تعرف إن query بتستخدم index في SQLite؟
EXPLAIN QUERY PLAN: SEARCH ... USING INDEX يعني استخدام index، SCAN يعني قراءة الجدول (أو index كامل).إيه الفرق بين rollback journal و WAL؟
ليه transaction واحدة لدفعة insert أسرع؟
إزاي تاخد backup آمن لـ SQLite شغالة؟
VACUUM INTO، وبعدها integrity_check واختبار restore. مش نسخ الملف بشكل مباشر أثناء الكتابة.يعني إيه covering index؟
(أصعب) تطبيقك محتاج 200 كتابة متزامنة في الثانية من 50 مستخدم. SQLite تنفع؟
المراجع والقراءة الإضافية
الدرس بيشرح الفكرة نفسها؛ الروابط دي للتحقق والتعمق والرجوع لتفاصيل الـ API. (محتاجة إنترنت.)
التوثيق الرسمي — Official documentation
خلصت الدرس وحليت الأسئلة؟ علّم عليه عشان يتحسب في تقدّمك.