طَبِّقْهاتعلّم المبدأ، وطبّقه بيدكمقرر 121 · مبادئ قواعد البيانات
الدرس 24 · الوحدة 11: تحسين أداء استعلامات SQL

خطة التنفيذ: EXPLAIN

اسأل النظام كيف سينفّذ استعلامك قبل أن تخمّن سبب بطئه. ⏱ نحو 30 دقيقة.

بعد هذا الدرس ستستطيع أن:
  • تشرح دور المحسّن في اختيار خطة التنفيذ.
  • تقرأ خطة SQLite وتميّز SCAN و SEARCH و TEMP B-TREE و AUTOMATIC INDEX.
  • تقابل أعمدة EXPLAIN في MySQL بعبارات SQLite.
  • تتبع دورة التحسين: قِس، اقرأ، عدّل، قِس.
جرّب بنفسك أسئلة التقييم

١من يقرر كيف يُنفَّذ الاستعلام؟

في SQL تكتب ماذا تريد، لا كيف يُجلب. والذي يقرر «كيف» جزء في نظام إدارة قواعد البيانات اسمه المحسّن Query Optimizer: ينظر إلى الجداول والفهارس المتاحة، ويوازن بين الطرق الممكنة، ويختار أرخصها.

الاستعلامSELECT …التحليلParserالمحسّن يختار خطةOptimizerالتنفيذExecutorEXPLAIN يعرض هذه الخطة قبل التنفيذ

والأمر EXPLAIN QUERY PLAN في SQLite، أو EXPLAIN في MySQL، يطلب من المحسّن أن يعرض خطته دون أن ينفّذ الاستعلام.

٢قاموس الخطة في SQLite

العبارة في الخطةمعناهاالحكم
SCAN tيقرأ الجدول كله صفًّا صفًّا.🟠 مقبول للجداول الصغيرة، ومكلف للكبيرة
SCAN t USING COVERING INDEX iيمر على الفهرس كله بدل الجدول.🔵 أخف من قراءة الجدول
SEARCH t USING INDEX i (col=?)يقفز إلى الصفوف المطلوبة بالفهرس.🟢 ممتاز
SEARCH t USING INTEGER PRIMARY KEYيقفز بالمفتاح الأساسي.🟢 الأسرع
USE TEMP B-TREE FOR ORDER BYيرتب النتيجة في ذاكرة مؤقتة بعد جلبها.🟡 عمل إضافي يمكن تجنبه بفهرس
… AUTOMATIC … INDEXبنى النظام فهرسًا مؤقتًا لهذا الاستعلام فقط.🟡 إشارة: أنشئ الفهرس بنفسك
CORRELATED … SUBQUERYاستعلام فرعي يُنفَّذ مرة لكل صف.🟡 قد يكون بطيئًا

٣قراءة خطة الربط JOIN

كيف يربط النظام الطلبات بالعملاء؟ اقرأ الخطة من الأعلى إلى الأسفل، فهي مثل حلقتين متداخلتين:

query.sql
EXPLAIN QUERY PLANSELECT c.name, o.order_dateFROM orders oJOIN customers c ON c.id = o.customer_id;
خطة التنفيذ (EXPLAIN QUERY PLAN)
  • SCAN o
  • SEARCH c USING INTEGER PRIMARY KEY (rowid=?)

المعنى: «لكل طلب في orders (SCAN)، اقفز إلى عميله في customers بالمفتاح الأساسي (SEARCH)». وهذا ممتاز.

لكن في الاتجاه المعاكس، من العميل إلى طلباته، لا يوجد فهرس على المفتاح الأجنبي orders.customer_id:

query.sql
EXPLAIN QUERY PLANSELECT c.name, COUNT(o.id) AS ordersFROM customers cLEFT JOIN orders o ON o.customer_id = c.idGROUP BY c.id;
خطة التنفيذ (EXPLAIN QUERY PLAN)
  • SCAN c
  • BLOOM FILTER ON o (customer_id=?)
  • SEARCH o USING AUTOMATIC COVERING INDEX (customer_id=?) LEFT-JOIN

AUTOMATIC COVERING INDEX تعني أن SQLite اضطر لبناء فهرس مؤقت في كل مرة. والحل الدائم:

query.sql
CREATE INDEX idx_orders_customer ON orders (customer_id); EXPLAIN QUERY PLANSELECT c.name, COUNT(o.id) AS ordersFROM customers cLEFT JOIN orders o ON o.customer_id = c.idGROUP BY c.id;
خطة التنفيذ (EXPLAIN QUERY PLAN)
  • SCAN c
  • SEARCH o USING COVERING INDEX idx_orders_customer (customer_id=?) LEFT-JOIN

٤الترتيب المكلف: TEMP B-TREE

«أعلى 5 مبيعات في أبها». بلا فهرس مناسب: يقرأ النظام 200 ألف صف، ثم يرتب مبيعات أبها كلها ليأخذ أول 5:

query.sql
EXPLAIN QUERY PLANSELECT id, amountFROM salesWHERE city = 'أبها'ORDER BY amount DESCLIMIT 5;
خطة التنفيذ (EXPLAIN QUERY PLAN)
  • SCAN sales
  • USE TEMP B-TREE FOR ORDER BY

بفهرس مركّب (city, amount) تكون مبيعات أبها في الفهرس مرتبة بالمبلغ أصلًا، فيأخذ النظام آخر 5 مباشرة بلا ترتيب:

query.sql
CREATE INDEX idx_sales_city_amount ON sales (city, amount); EXPLAIN QUERY PLANSELECT id, amount FROM salesWHERE city = 'أبها' ORDER BY amount DESC LIMIT 5; SELECT id, amount FROM salesWHERE city = 'أبها' ORDER BY amount DESC LIMIT 5;
✓ أُنشئ الفهرس
خطة التنفيذ (EXPLAIN QUERY PLAN)
  • SEARCH sales USING COVERING INDEX idx_sales_city_amount (city=?)
النتيجة · 5 صفوف
idamount
108591008.99
1324771008.97
868721008.92
412671008.87
1628851008.85

جرّبه، ثم احذف سطر CREATE INDEX وشغّل مرة أخرى، وقارن زمن الاستعلام الأخير في الحالتين.

٥EXPLAIN في MySQL

في MySQL Workbench تكتب EXPLAIN قبل الاستعلام، فتظهر الخطة جدولًا. هذا شكل تقريبي لخطة استعلام العميل 777 قبل الفهرس وبعده:

mysql.sql
EXPLAIN SELECT * FROM sales WHERE customer_id = 777;
typekeyrowsExtra
قبل الفهرسALLNULL≈ 200000Using where
بعد الفهرسrefidx_sales_customer10—
قيمة type في MySQLمعناهايقابلها في SQLite
ALLقراءة الجدول كلهSCAN t
indexقراءة الفهرس كلهSCAN t USING COVERING INDEX
rangeنطاق من الفهرس (BETWEEN، > ، <)SEARCH … (col>? AND col<?)
refبحث بقيمة في فهرس غير فريدSEARCH … (col=?)
eq_ref / constصف واحد بمفتاح أساسي أو فريدSEARCH … PRIMARY KEY

وفي العمود Extra: Using filesort يقابل TEMP B-TREE FOR ORDER BY، و Using index يعني فهرسًا مغطّيًا. وفي MySQL 8 يوجد EXPLAIN ANALYZE الذي ينفّذ الاستعلام فعلًا ويعرض الزمن الحقيقي لكل خطوة.

٦طريقة العمل: قِس، اقرأ، عدّل، قِس

  1. قِس زمن الاستعلام البطيء عدة مرات.
  2. اقرأ الخطة: ابحث عن SCAN على جدول كبير، و TEMP B-TREE، و AUTOMATIC INDEX.
  3. عدّل: أضف فهرسًا مناسبًا، أو أعد صياغة الاستعلام (الدرس القادم).
  4. قِس مرة أخرى، وتأكد من الخطة الجديدة، واحسب نسبة التحسن.

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

🧪 ملعب SQLأضف فهرسًا على city قبل الاستعلام، ولاحظ كيف تتغير الخطة.

توقّع وتحقّق

الجزء الذي يختار طريقة تنفيذ الاستعلام:

يوازن بين الخطط.

USE TEMP B-TREE FOR ORDER BY تعني:

يمكن تجنبه بفهرس.

type = ALL في MySQL تقابل في SQLite:

قراءة كاملة.

AUTOMATIC INDEX في الخطة إشارة إلى:

أنشئه بنفسك.

الخلاصة

تحدٍّ برمجي: أعلى المبيعات بلا ترتيب إضافي

متقدم

تقرير «أعلى 5 مبيعات في الرياض» يُطلب باستمرار، وخطته فيها SCAN و TEMP B-TREE. اجعل خطته بحثًا مباشرًا في الفهرس بلا ترتيب إضافي.

المطلوب:
  • أنشئ فهرسًا مركّبًا اسمه idx_sales_city_amount مناسبًا لهذا التقرير.
  • ثم اعرض id و amount لأعلى 5 مبيعات في «الرياض» مرتبة تنازليًّا بالمبلغ.
👀 اعرض النتيجة المطلوبة
النتيجة المطلوبة
idamount
1216181008.98
760131008.93
1976311008.91
304081008.88
1520261008.86
✓ التحقق الآلي من حلّك:
  • الكود يعمل بلا أخطاء
  • النتيجة: أعلى 5 مبيعات في الرياض بالترتيب
  • الخطة: بحث في الفهرس بلا TEMP B-TREE

💡 تلميح 1
العمود الذي في WHERE أولًا، ثم العمود الذي في ORDER BY.
💡 تلميح 2
CREATE INDEX idx_sales_city_amount ON sales (city, amount);

حاول بنفسك أولًا؛ للمسألة أكثر من حلّ صحيح.

solution.sql
CREATE INDEX idx_sales_city_amount ON sales (city, amount); SELECT id, amount FROM salesWHERE city = 'الرياض'ORDER BY amount DESCLIMIT 5;
انتقل إلى تقييم الدرساختيار من متعدد، وصح وخطأ، وأكمل الفراغ، وتتبّع الناتج.ابدأ التقييم ←

📝 معلم؟ أرسل ملاحظة للمؤلف عن هذا الدرس