تحليل خطط تنفيذ الاستعلامات باستخدام EXPLAIN في MySQLMySQL Analyzing Query Execution Plans with EXPLAIN

كيف تتأكد برمجياً من أن استعلام SELECT يستخدم الفهرس الذي أنشأته ولا يجري فحصاً كاملاً خفياً؟ يوفر MySQL 8.4 أداة التشخيص الهندسية الأهم EXPLAIN: فعند كتابة الكلمة EXPLAIN قبل أي استعلام، يعرض لك محسّن الاستعلامات القائم على التكلفة (Cost-Based Optimizer) خطة التنفيذ المتوقعة دون إرجاع صفوف البيانات الفعلية.

تتكون مخرجات EXPLAIN الجدولية من 12 عموداً تشخيصياً، أهمها للتحليل السريع:

• type: طريقة وصول المحرك لصفوف الجدول (من الأسرع const إلى الأبطأ ALL).
• possible_keys وkey: الفهارس المرشحة مقابل الفهرس الفعلي الذي وقع عليه اختيار المحسّن.
• rows وfiltered: العدد التقديري للصفوف التي سيفحصها المحرك ونسبة الصفوف المتوقع بقاؤها بعد تطبيق الشروط.
• Extra: تفاصيل داخلية حاسمة مثل القراءة من الفهرس فقط Using index أو التحذير من الفرز المكلف Using filesort.

يُعدّ العمود type المؤشر الأول الذي ينظر إليه مهندس الأداء عند فحص مخرجات EXPLAIN؛ فهو يحدد خوارزمية الوصول للصفوف مرتبةً من الأعلى كفاءةً إلى الأبطأ:

• const: أسرع وصول على الإطلاق؛ يظهر عندما تبحث بمساواة على مفتاح أساسي PRIMARY KEY أو فريد UNIQUE، حيث يقرأ المحرك صفاً واحداً فقط ويعامله كثابت.
• eq_ref: أفضل نوع وصول في عمليات الربط JOIN، ويحدث عندما يرتبط كل صف من الجدول الأول بصف واحد بالضبط في الجدول الثاني عبر مفتاحه الأساسي.
• ref: بحث سريع بالمساواة = عبر فهرس ثانوي عادي قد يطابق بضعة صفوف.
• range: مسح نطاق جزئي محدد داخل شجرة الفهرس (مع BETWEEN أو > أو IN).
• index ثم ALL: يعني index مسح كامل شجرة الفهرس، بينما يعني ALL الفحص الكامل للجدول (Full Table Scan) وهو الإشارة الحمراء التي تتطلب التحسين.

عند قراءة مخرجات EXPLAIN لجدول يملك عدة فهارس، تبرز أهمية المقارنة بين ثلاثة أعمدة متجاورة: possible_keys وkey وkey_len:

• possible_keys (الفهارس المرشحة): يعرض أسماء جميع الفهارس التي تصلح نظرياً لتنفيذ شرط البحث.
• key (الفهرس المختار): يعرض اسم الفهرس الفائز الذي اختاره المحسّن فعلياً لأنه صاحب أقل تكلفة تقديرية (Lowest Cost). إذا كان possible_keys ممتلئاً بينما key = NULL، فهذا يعني أن المحسّن وجد أن قراءة الجدول مباشرة أرخص من القفز المتكرر عبر الفهرس (مثلاً عندما يطابق الشرط أكثر من 30% من صفوف الجدول).
• key_len (طول المفتاح المستغل بالبايت): يكشف لك كم عموداً من أعمدة الفهرس المركب تم استغلاله فعلياً في عملية البحث!

يحمل العمود الأخير Extra في مخرجات EXPLAIN رسائل تشخيصية بالغة الأهمية قد تقلب تقييمك لأداء الاستعلام رأساً على عقب. إليك أهم أربع عبارات يجب على كل مطور التمييز بينها:

• Using index (ممتاز جداً): تعني أن الاستعلام نُفّذ بالكامل من أوراق الفهرس الثانوي (Covering Index) دون الحاجة لقراءة أي صفحة من الجدول الأساسي!
• Using index condition (جيد جداً): تفعيل ميزة دفع شرط الفهرس (Index Condition Pushdown - ICP) لتصفية الصفوف داخل محرك InnoDB قبل إرسالها لطبقة الخادم.
• Using filesort (تحذير أداء): تعني أن الفهرس لم يكن مرتباً حسب جملة ORDER BY، فاضطر MySQL لإجراء خوارزمية فرز إضافية في الذاكرة (Sort Buffer) أو على القرص إذا كان الحجم كبيراً.
• Using temporary (تحذير أداء): تعني أن MySQL أنشأ جدولاً مؤقتاً داخلياً لتنفيذ GROUP BY أو DISTINCT أو UNION.

يعتمد الأمر التقليدي EXPLAIN على الإحصائيات التقريبية المخزنة، وقد يقدّر أن الاستعلام سيفحص 10 صفوف بينما يفحص في الواقع 5,000 صف! لمعالجة هذه الفجوة، يوفر MySQL 8 أداة القياس الحقيقي EXPLAIN ANALYZE.

كيف يختلف EXPLAIN ANALYZE عن EXPLAIN العادي؟

• ينفذ الاستعلام فعلياً: يشغّل المحرك الاستعلام بالكامل ويقيس الزمن الحقيقي المستغرق في كل خطوة ثم يتجاهل عرض جدول النتائج ليعرض شجرة القياس.
• يقارن التقدير بالواقع: يعرض لك في كل عقدة كلاً من التقدير (cost=... rows=...) والقياس الحقيقي (actual time=start..end rows=... loops=...).
• تُقرأ شجرة EXPLAIN ANALYZE من الداخل (الخطوات المسننة الأعمق) إلى الخارج (الخطوة النهائية في الأعلى).

يلخّص الجدول المرجعي التالي دلالة أعمدة وقيم EXPLAIN التشخيصية في MySQL 8.4 وكيفية التصرف الهندسي تجاه كل حالة: