شرح EXPLAIN QUERY PLAN في SQLite وتحليل الاستعلامات

كيف تعرف خطة تنفيذ استعلام SQLite باستخدام EXPLAIN QUERY PLAN

قبل أن تبدأ بتحسين أي استعلام بطيء في SQLite، يجب أن تعرف أولاً ماذا يفعل المحرك فعلياً خلف الكواليس. الأمر EXPLAIN QUERY PLAN يعرض لك ملخصاً مختصراً للخطة التي اختارها مُخطِّط الاستعلامات: ما هي الجداول التي سيمر عليها، وبأي ترتيب، وهل سيستخدم فهارس أم لا. الاستعلام نفسه لن يُنفَّذ، أنت تحصل فقط على الخطة.

كل ما عليك هو إضافة هذه الكلمات قبل أي عبارة SQL:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    country TEXT
);

INSERT INTO users (email, country) VALUES
    ('a@x.com', 'US'),
    ('b@x.com', 'UK'),
    ('c@x.com', 'US');

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'a@x.com';

المخرجات تبدو بهذا الشكل تقريبًا:

QUERY PLAN
`--SEARCH users USING INDEX sqlite_autoindex_users_1 (email=?)

هذا السطر وحده يكشف لك الكثير: قاعدة SQLite تنفّذ عملية SEARCH (وليس scan) على جدول users، مستخدمةً الفهرس الفريد الذي أُنشئ تلقائيًا لعمود email، مع email كمفتاح للبحث. هذا تحديدًا ما كنت تتمنى رؤيته.

الفرق بين SCAN و SEARCH: أول ما يجب أن تقرأه

كل سطر في خطة التنفيذ يبدأ إمّا بـ SCAN أو SEARCH، والتمييز بينهما هو أهم إشارة في المخرجات كلها.

• SCAN <table> — يقرأ SQLite كل صف في الجدول (أو كل مدخل في الفهرس)، فالتكلفة تتضخّم مع حجم الجدول.
• SEARCH <table> USING ... — يقفز SQLite مباشرةً إلى الصفوف المطابقة عبر فهرس أو مفتاح أساسي، فالتكلفة هنا مرتبطة بحجم النتيجة لا بحجم الجدول.

إليك مقارنة مباشرة بين الحالتين: عمود يملك فهرسًا وآخر لا يملك:

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

CREATE INDEX idx_orders_customer ON orders(customer_id);

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

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE status = 'paid';

الخطة الأولى تُظهر SEARCH orders USING INDEX idx_orders_customer، بينما الثانية تُظهر SCAN orders — لا يوجد فهرس على عمود status، لذلك يقرأ SQLite كل صف على حدة. على جدول صغير لن تلاحظ شيئًا، أما على جدول يحتوي مليون صف فالفرق بين أجزاء من الثانية وعدة ثوانٍ كاملة.

ظهور SCAN ليس خطأً دائمًا؛ فمع جداول البحث الصغيرة، أو الاستعلامات التي تُرجع فعليًا معظم الصفوف، يكون المسح الكامل هو الخيار الأنسب. لكن عندما يكون الجدول ضخمًا والمُرشِّح انتقائيًا، فإن ظهور SCAN إشارة واضحة إلى أنك بحاجة لإضافة فهرس.

كيف تتأكد أن الاستعلام يستخدم فهرس sqlite

العبارة التي تبحث عنها هي USING INDEX <name> (أو USING COVERING INDEX <name> — وسنتحدث عنها بعد قليل). إذا أنشأت فهرسًا وأنت تأمل أن يلتقطه مُخطِّط الاستعلام، فهذه هي الطريقة للتحقق:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    created_at TEXT
);

CREATE INDEX idx_events_user ON events(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id = 42;

يفترض أن ترى SEARCH events USING INDEX idx_events_user (user_id=?). أمّا إذا ظهرت لك الخطة بصيغة SCAN events، فهذا يعني أن شيئًا ما يمنع مُخطِّط الاستعلامات من استخدام الفهرس. الأسباب الشائعة لهذه المشكلة هي تغليف العمود بدالة (مثل WHERE lower(user_id) = ...)، أو المقارنة بين أنواع بيانات مختلفة، أو استخدام LIKE '%foo%' بمحرف بدل (wildcard) في البداية.

هذا اختبار سريع للحالة:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    kind TEXT
);

CREATE INDEX idx_events_user ON events(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id + 0 = 42;

‏إضافة + 0 تُبطل عمل الفهرس، فترى الخطة قد رجعت إلى SCAN events. أي عملية حسابية على العمود المفهرس تؤدي إلى النتيجة نفسها.

الفهارس المُغطية تظهر بشكل مختلف

عندما يحتوي الفهرس على جميع الأعمدة التي يحتاجها الاستعلام، يستطيع SQLite الإجابة عليه من الفهرس وحده دون الرجوع إلى الجدول. في هذه الحالة تُظهر الخطة USING COVERING INDEX:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT,
    name TEXT,
    price REAL
);

CREATE INDEX idx_products_sku_price ON products(sku, price);

EXPLAIN QUERY PLAN
SELECT price FROM products WHERE sku = 'ABC-123';

الخطة: SEARCH products USING COVERING INDEX idx_products_sku_price (sku=?). الاستعلام يطلب عمود price، والفهرس بالأصل يحتوي على sku وprice معًا، فلا يحتاج SQLite لقراءة الجدول الأصلي إطلاقًا. الفهارس المُغطِّية (Covering Indexes) هي أسرع خطة ممكنة لأي عملية بحث — معلومة تستحق التذكر حين تقرر أي الأعمدة تضعها معًا في فهرس واحد.

قراءة خطط الـ JOIN في sqlite

عمليات الـ JOIN هي المكان التي تصير فيه خطط الاستعلام ممتعة فعلًا. كل سطر في الخطة يقابل جدولًا واحدًا من جداول الـ JOIN، وترتيب السطور هو نفسه ترتيب زيارة SQLite للجداول. الجدول الأول هو الجدول الخارجي (outer)، أما الجداول التي تليه فيُبحث فيها مرة واحدة لكل صف من صفوف الجدول الخارجي.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total REAL
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN QUERY PLAN
SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 7;

خطة تنفيذ نموذجية:

QUERY PLAN
|--SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH o USING INDEX idx_orders_customer (customer_id=?)

اقرأ الخطة من الأعلى إلى الأسفل: يعثر SQLite على العميل المطلوب عبر المفتاح الأساسي، ثم يجلب الطلبات المرتبطة بهذا العميل باستخدام الفهرس الموجود على customer_id. لاحظ أن السطرين كليهما يبدآن بـ SEARCH بدون أي مسح كامل، وهذا هو السلوك المثالي الذي تسعى إليه.

أما لو ظهر لك SCAN o في السطر الثاني، فهذا يعني أن كل عملية بحث عن عميل ستتسبب في مرور كامل على جدول orders. ومع جدول ضخم تكون النتيجة كارثية على الأداء. الحل في الغالب بسيط: أضف فهرسًا على عمود الربط (join column).

الاستعلامات المركّبة والاستعلامات الفرعية

خطط التنفيذ الخاصة بـ UNION وEXCEPT والاستعلامات الفرعية تظهر بشكل متداخل، حيث يُعرض كل فرع بإزاحة تحت الفرع الأب الخاص به:

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);

EXPLAIN QUERY PLAN
SELECT x FROM a
UNION ALL
SELECT x FROM b;

ستجد صفّين فرعيّين تحت العنوان COMPOUND QUERY، صفًّا لكل فرع. الاستعلامات الفرعية وتعابير CTE تعمل بالأسلوب ذاته تقريبًا؛ يحصل كلٌّ منها على عقدة خطة مستقلة بمسافة بادئة خاصة بها، وتقرأها بالمنطق نفسه: SCAN مقابل SEARCH.

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);

EXPLAIN QUERY PLAN
SELECT * FROM a
WHERE x IN (SELECT x FROM b WHERE x > 10);

الاستعلام الفرعي يظهر كعقدة مستقلة في الخطة (باسم "LIST SUBQUERY" أو ما شابه)، ولها استراتيجية وصول خاصة بها. طبّق نفس الفحوصات على كل مستوى.

الفرق بين EXPLAIN و EXPLAIN QUERY PLAN

هذان أمران مختلفان تمامًا، وكثيرًا ما يخلط بينهما المطورون.

CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT);

EXPLAIN
SELECT * FROM t WHERE id = 1;

EXPLAIN (بدون QUERY PLAN) يُخرج لك البايت كود الذي ستُنفّذه آلة SQLite الافتراضية — عشرات الأوامر منخفضة المستوى مثل OpenRead وSeekRowid وColumn وResultRow. مفيد لو كنت تُصحّح أخطاء المحرك نفسه، لكنه نادراً ما يفيد في تحسين الأداء.

أمّا EXPLAIN QUERY PLAN فهو الملخّص المقروء الذي تحتاجه فعلاً. كلما ترددت، اذهب مباشرةً إلى EXPLAIN QUERY PLAN.

خطوات عملية لتشخيص الاستعلامات البطيئة في sqlite

حين يكون الاستعلام بطيئاً، تسير الدورة على هذا النحو:

1. شغّل عليه EXPLAIN QUERY PLAN.
2. لكل سطر يخصّ جدولاً، اسأل نفسك: هل هذا SCAN أم SEARCH؟ في الجداول الكبيرة، SCAN هو المتّهم الأول.
3. إذا كان SCAN يُرشّح بناءً على عمود معيّن، ففكّر في إضافة فهرس على ذلك العمود.
4. في عمليات الـ JOIN، تأكّد أن جداول الحلقة الداخلية تستخدم SEARCH USING INDEX على عمود الربط.
5. أعِد تشغيل EXPLAIN QUERY PLAN بعد إضافة الفهرس. يُفترض أن تتغيّر الخطة. إن لم تتغيّر، فهذا يعني أن المُخطّط رأى أن فهرسك لا يستحقّ الاستخدام — عادةً لأن الجدول صغير أو لأن المُرشِّح ليس انتقائياً بما يكفي.

مثال تطبيقي على الخطوة الخامسة:

CREATE TABLE logs (
    id INTEGER PRIMARY KEY,
    level TEXT,
    message TEXT
);

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR';
-- مسح كامل للجدول logs

CREATE INDEX idx_logs_level ON logs(level);

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR';
-- بحث في logs باستخدام الفهرس idx_logs_level (level=?)

تغيّرت الخطة من SCAN إلى SEARCH. هذه هي الإشارة التي تخبرك بأنّ الفهرس يقوم بعمله. (مع جدول جديد شبه فارغ، قد يستمر المخطط في إجراء مسح كامل لأنّ البيانات ليست كافية ليتكبّد عناء استخدام الفهرس — املأ الجدول أو نفّذ ANALYZE وستجد الاختيار يتبدّل في الغالب.)

ما الذي لن تخبرك به خطة الاستعلام

أمر EXPLAIN QUERY PLAN يصف لك الاستراتيجية لا التكلفة. فهو لن يخبرك أنّ الاستعلام استغرق 800 مللي ثانية أو أنّه أعاد 50,000 صف. لمعرفة ذلك تحتاج إلى قياس الزمن (‎.timer on داخل الـ CLI) وعدّ الصفوف. الخطة والتوقيت يكمّلان بعضهما — الخطة تخبرك لماذا الاستعلام بطيء، والمؤقّت يخبرك هل هو بطيء فعلًا أم لا.

وهناك قيدان آخران يستحقّان الانتباه:

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

التالي: ANALYZE و VACUUM

يبني مخطّط الاستعلامات قراراته اعتمادًا على إحصائيات حول جداولك وفهارسك. وإذا كانت هذه الإحصائيات غائبة أو قديمة، فحتى المخطّط (schema) المُفهرَس بشكل مثالي قد يُنتج خطّة سيّئة. الأمر ANALYZE هو وسيلتك للحفاظ على هذه الإحصائيات محدّثة — والأمر VACUUM هو رفيقه الذي يستعيد المساحة ويعيد ترتيب ملفّ قاعدة البيانات. هذا ما سنتناوله تاليًا.