ANALYZE وVACUUM في SQLite: إحصائيات وتحرير المساحة

مهمتان مختلفتان للصيانة

كثيرًا ما يُذكر ANALYZE وVACUUM معًا، لكن كلًّا منهما يعالج مشكلة مختلفة.

• ANALYZE يجمع إحصائيات عن بياناتك حتى يتخذ مخطّط الاستعلامات قرارات أذكى. يكتب هذه الإحصائيات في جدول اسمه sqlite_stat1 دون أن يمسّ صفوفك الفعلية.
• VACUUM يعيد بناء ملف قاعدة البيانات نفسه لاسترجاع الصفحات غير المستخدمة وتقليل التشتت في التخزين. وهو لا يغيّر خطط التنفيذ بشكل مباشر.

إذا كانت الاستعلامات تختار الفهرس الخطأ، فأنت بحاجة إلى ANALYZE. وإذا تضخّم حجم الملف بعد عمليات حذف كثيرة، فأنت بحاجة إلى VACUUM. الخلط بينهما يضيّع وقتًا طويلًا في صيانة لا طائل منها.

ماذا يفعل ANALYZE فعليًا؟

مخطّط الاستعلامات مضطر للتخمين. حين يرى شرطًا مثل WHERE status = 'active'، عليه أن يقدّر عدد الصفوف المطابقة — صف واحد؟ أم مليون؟ — ليقرّر هل يستخدم فهرسًا أم يمسح الجدول كاملًا. وبدون إحصائيات، يلجأ إلى تقديرات تقريبية بدائية.

ANALYZE يمرّ على كل فهرس ويسجّل معلومات موجزة عن توزيع القيم فيه:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  status TEXT,
  customer_id INTEGER
);

CREATE INDEX idx_orders_status ON orders(status);

INSERT INTO orders(status, customer_id) VALUES
  ('shipped', 1), ('shipped', 2), ('shipped', 3),
  ('shipped', 4), ('pending', 5);

ANALYZE;

SELECT * FROM sqlite_stat1;

يخبر صف sqlite_stat1 المخطِّط بعدد الصفوف التقريبي في الفهرس وعدد التكرارات المتوقَّع لأي مفتاح. ففي المرة القادمة التي تنفِّذ فيها استعلامًا مثل WHERE status = 'pending'، سيدرك أن قيمة pending نادرة فيلجأ إلى الفهرس، أما مع WHERE status = 'shipped' فقد يرى أن المسح الكامل أرخص.

كما يمكنك تحليل جدول أو فهرس بعينه بدلًا من قاعدة البيانات بالكامل:

ANALYZE orders;
ANALYZE idx_orders_status;

شغّل ANALYZE بعد عمليات الإدراج الضخمة، أو بعد إجراء تغييرات كبيرة على المخطط، أو عندما تلاحظ أن مخطِّط الاستعلامات بدأ يختار خططًا سيئة على جداول تغيّر توزيع بياناتها.

PRAGMA optimize: الخيار الافتراضي الحديث

تشغيل ANALYZE بشكل أعمى عند كل إغلاق للاتصال هو إهدار للموارد، فغالبًا لا يكون هناك تغيير يستدعي ذلك. لهذا توفّر SQLite غلافًا أذكى يقوم بالمهمة:

PRAGMA optimize;

PRAGMA optimize يفحص التغييرات التي طرأت على قاعدة البيانات منذ آخر تحليل، ثم يُشغّل ANALYZE فقط على الجداول التي تحتاج إليه فعلاً. التوصية الرسمية هي استدعاؤه على كل اتصال طويل العمر قبل إغلاقه مباشرة، وبشكل دوري على الاتصالات التي تبقى مفتوحة لساعات.

تكلفته زهيدة حين لا يتغير شيء، وفعّال حين يتغير شيء فعلاً. ابدأ بـ optimize دائماً، ولا تلجأ إلى ANALYZE المباشر إلا حين تحتاج إلى إجبار التحديث.

ماذا يفعل VACUUM في SQLite فعلياً؟

عند حذف صفوف أو إسقاط جدول، يكتفي SQLite بتعليم تلك الصفحات على أنها فارغة دون تقليص حجم الملف. تُعاد هذه الصفحات الفارغة للاستخدام في عمليات الإدراج اللاحقة، وهذا أمر مقبول في معظم الحالات. لكن مع كثرة التعديلات على المدى الطويل، تتراكم مشكلتان:

1. مساحة فارغة لا يراها نظام التشغيل. يبقى ملف .db بحجم 2 جيجابايت رغم أن البيانات الفعلية لا تتجاوز 800 ميجابايت.
2. التجزئة (Fragmentation). تتبعثر صفوف الجدول الواحد على صفحات غير متجاورة، مما يُضعف أداء عمليات المسح.

يحلّ VACUUM المشكلتين معاً عبر نسخ قاعدة البيانات كاملةً إلى ملف جديد مرصوص بإحكام، ثم استبدال الملف الأصلي به:

CREATE TABLE logs (id INTEGER PRIMARY KEY, body TEXT);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 1000
)
INSERT INTO logs(body) SELECT 'إدخال ' || value FROM n;
DELETE FROM logs WHERE id < 900;

VACUUM;

SELECT count(*) FROM logs;

بعد تنفيذ VACUUM، يصبح حجم الملف مساوياً لما كان سيكون عليه لو أنك أدخلت الصفوف المئة المتبقية فقط من الصفر. ومن الآثار الجانبية لذلك أن جميع قيم rowid تبقى كما هي، لكن تخطيط البيانات على القرص يعود متراصاً من جديد.

قبل أن تشغّله، هناك بعض الأمور التي يجب أن تعرفها:

• يحتاج إلى قفل حصري (exclusive lock) على قاعدة البيانات طوال فترة التنفيذ، فلا يمكن لأي اتصال آخر أن يكتب فيها.
• يحتاج إلى مساحة فارغة على القرص تعادل تقريباً ضعف حجم قاعدة البيانات، لأنه يبني الملف الجديد بجانب القديم.
• لا يمكن تشغيله داخل معاملة (transaction)، وسيُرجع خطأً إذا كانت هناك أي معاملات نشطة مفتوحة.
• على قواعد بيانات بحجم عدة جيجابايتات، قد يستغرق وقتاً طويلاً، فخطط لذلك مسبقاً.

متى تشغّل VACUUM فعلاً؟

بالنسبة لمعظم التطبيقات: لا تشغّله، إلا إذا تغير شيء محدد يستدعي ذلك.

أسباب وجيهة لتشغيل VACUUM:

• حذفت للتو جدولاً كبيراً أو عدداً ضخماً من الصفوف وتريد استرجاع المساحة على القرص.
• قاعدة البيانات تعمل وتتغير منذ سنوات، وأصبحت الاستعلامات التي تمسح الجداول أبطأ مما كانت عليه.
• تنوي توزيع ملف قاعدة البيانات ضمن إصدار، وتريده بأصغر حجم ممكن.

أسباب غير وجيهة:

• "فقط من باب الاحتياط." VACUUM يعيد كتابة الملف بالكامل في كل مرة، ولا شيء "احتياطي" في فعل ذلك على نظام يعمل في الإنتاج.
• بعد كل دفعة من عمليات الحذف. الصفحات المُحرَّرة كانت ستُعاد استخدامها على أي حال.

auto_vacuum و Incremental VACUUM

إذا أردت أن تُدير SQLite الصفحات الفارغة تلقائياً، فعليك ضبط auto_vacuum عند إنشاء قاعدة البيانات، إذ لا يمكن تغييره لاحقاً دون تنفيذ vacuum كامل:

PRAGMA auto_vacuum = INCREMENTAL;

هناك ثلاثة أوضاع:

• NONE (الوضع الافتراضي): تبقى الصفحات الفارغة داخل الملف، ويُعاد استخدامها في عمليات الإدراج اللاحقة.
• FULL: كل عملية commit تُحرِّر صفحات تقوم أيضًا باقتطاع الملف. مريح، لكن كل معاملة تدفع الثمن.
• INCREMENTAL: يتتبّع SQLite الصفحات الفارغة لكنه لا يُحرِّرها إلا عند طلبك:

PRAGMA auto_vacuum = INCREMENTAL;
CREATE TABLE t(x INTEGER);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 1000
)
INSERT INTO t SELECT value FROM n;
DELETE FROM t;

PRAGMA incremental_vacuum(100);
PRAGMA freelist_count;

الأمر PRAGMA incremental_vacuum(N) يُحرِّر حتى N صفحة فارغة ويُعيدها إلى نظام التشغيل — سريع، ولا يحتفظ بقفل حصري لفترة طويلة، ويمكنك تشغيله ضمن مهمة مجدولة. هذا هو الخيار الأمثل لقواعد البيانات كثيفة الكتابة التي تحتاج أن تبقى مضغوطة دون تحمُّل تكلفة VACUUM الكامل.

VACUUM INTO: تصدير نسخة مضغوطة

الأمر VACUUM INTO يكتب نسخة جديدة ومضغوطة في ملف منفصل دون المساس بالملف الأصلي:

VACUUM INTO 'backup.db';

هذه فوائد حقيقية ومفيدة:

• النسخ الاحتياطي. الناتج يكون لقطة (snapshot) متّسقة ومُفرَّغة بالكامل — بلا صفحات نصف مكتوبة، وبلا قلق من ملف .wal. أفضل بكثير من نسخ الملف بأمر cp.
• تقليص الحجم دون تعطيل الكتابة طويلًا. تُنفِّذ عملية الـ VACUUM على ملف جانبي، ثم تستبدله ذرّيًا بالملف الأصلي. هكذا لا يبقى الكُتّاب محجوبين طوال مدة العملية.
• التوزيع. يمكنك شحن نسخة صغيرة ومُفرَّغة من قاعدة بيانات التطوير.

ملاحظة مهمة: ملف الوجهة يجب ألّا يكون موجودًا مسبقًا، وإلّا ستحصل على خطأ.

وصفة عملية للصيانة الدورية

إليك ما يصلح لقاعدة بيانات تطبيق نموذجية:

-- في كل اتصال طويل الأمد، قبل الإغلاق:
PRAGMA optimize;

-- بعد تحميل مجمّع كبير أو تغيير في المخطط:
ANALYZE;

-- بعد حذف كمية كبيرة من البيانات والرغبة في استعادة مساحة القرص:
VACUUM;

-- للنسخ الاحتياطية:
VACUUM INTO '/backups/app-2026-04-23.db';

إذا كانت قاعدة البيانات تتعرض لعمليات كتابة وحذف مكثّفة وتعمل على مدار الساعة دون توقف، فالأفضل ضبط auto_vacuum = INCREMENTAL لحظة إنشائها، ثم تشغيل PRAGMA incremental_vacuum(N) بشكل دوري — مرة واحدة يوميًا في أوقات الضغط المنخفض مثلًا.

تشخيص السؤال: "لماذا ملف قاعدة البيانات صار بهذا الحجم؟"

في أمران من نوع pragma يكشفان لك ما يجري خلف الكواليس:

CREATE TABLE t(x INTEGER);
WITH RECURSIVE n(value) AS (
    SELECT 1 UNION ALL SELECT value + 1 FROM n WHERE value < 10000
)
INSERT INTO t SELECT value FROM n;
DELETE FROM t WHERE x > 100;

PRAGMA page_count;
PRAGMA freelist_count;
PRAGMA page_size;

• page_count × page_size = الحجم الحالي للملف.
• freelist_count × page_size = البايتات المهدرة في صفحات غير مستخدمة.

إذا كانت قيمة freelist_count تمثّل نسبة كبيرة من page_count، فإن تنفيذ VACUUM (أو incremental_vacuum) سيُقلّص حجم الملف بشكل ملحوظ. أما إذا كانت النسبة صغيرة، فالملف مرصوص بكفاءة أصلًا ولن يُجدي VACUUM نفعًا.

أخطاء شائعة عند استخدام VACUUM وANALYZE

• تشغيل VACUUM داخل معاملة (transaction). هذا غير ممكن. نفّذ COMMIT أولًا.
• نسيان أن VACUUM يحتاج مساحة قرص فارغة. قاعدة بيانات بحجم 10 جيجابايت تحتاج إلى ~10 جيجابايت إضافية متوفرة لإتمام العملية.
• ضبط auto_vacuum بعد إدخال البيانات. الأمر يصبح بلا أثر حتى تشغيل VACUUM كامل لاحقًا. اضبطه عند إنشاء قاعدة البيانات إن كنت تريد تفعيله فعلًا.
• توقّع أن ANALYZE سيُصغّر حجم الملف. هذه مهمة VACUUM.
• توقّع أن VACUUM سيُحسّن خطط الاستعلامات. هذه مهمة ANALYZE.

الأمران يُكمّل أحدهما الآخر؛ ولا يُغني أيٌّ منهما عن الآخر.

الخطوة التالية: المعاملات (Transactions)

أوامر الصيانة مثل VACUUM تُلفت الانتباه إلى شيء كنّا نعتبره من المسلّمات: نموذج المعاملات في SQLite، وما الذي يُقفله ومتى. الفصل التالي يبدأ من هنا — كيف تعمل المعاملات، وما الذي تضمنه فعلًا أوامر BEGIN / COMMIT / ROLLBACK، وكيف تستخدمها لتجعل العمل المتكوّن من عدة جُمَل ذرّيًا (atomic).