تحسين الاستعلامات البطيئة في MySQL: EXPLAIN والفهارس بالأرقام

تحسين الاستعلامات البطيئة في MySQL يمرّ بثلاث خطوات مرتّبة: أولاً اصطد الاستعلامات الثقيلة عبر تفعيل slow_query_log، ثانياً افهم لماذا كل استعلام بطيء عبر أمر EXPLAIN، ثالثاً أصلح بإضافة الفهرس الصحيح أو إعادة صياغة الاستعلام. لتفعيل سجلّ الاستعلامات البطيئة تضبط slow_query_log = ON وlong_query_time = 1 فيسجّل MySQL كل استعلام يتجاوز ثانية واحدة مع عدد الصفوف المفحوصة وزمن التنفيذ. بعد جمع عيّنة، تأخذ أبطأ استعلام وتضع أمامه كلمة EXPLAIN فيريك خطة التنفيذ: هل يمسح MySQL الجدول كاملاً (type=ALL وهو الأسوأ) أم يقفز مباشرة عبر فهرس (type=ref أو eq_ref وهو الأفضل)، وكم صفاً يتوقّع فحصه (عمود rows)، وهل يلجأ لفرز مؤقت مكلف على القرص (Using filesort أو Using temporary). الفهرس الصحيح يحوّل استعلاماً من مسح مليون صف إلى قراءة عشرة فقط، فينخفض زمن الاستجابة من ثوانٍ إلى ميلي ثوانٍ ويهبط استهلاك vCPU بدرجة تُقاس فعلياً في لوحة السيرفر. هذا المقال يشرح كل خطوة عملياً: كيف تقرأ EXPLAIN حقلاً حقلاً، متى يفيد الفهرس المركّب وكيف ترتّب أعمدته، والفخ الذي يبطل فهرسك من حيث لا تدري.

الخطوة الأولى: اصطد الاستعلام البطيء

لا تحسّن بالتخمين. فعّل slow_query_log واترك التطبيق يعمل تحت حمل حقيقي بضع ساعات، ثم اقرأ الملف الناتج بأداة mysqldumpslow التي تجمّع الاستعلامات المتشابهة وترتّبها بالأثر الكلي، فتظهر لك القائمة الحقيقية للجناة. غالباً تكتشف أن استعلاماً واحداً يتكرر آلاف المرات ويستهلك أكثر من استعلام ضخم يعمل مرة يومياً. ركّز على الأثر الكلي (عدد مرات التنفيذ × زمن كل مرة) لا على أبطأ استعلام منفرد، فتحسين استعلام يعمل 5000 مرة في الساعة بتوفير 200 ميلي ثانية لكل مرة يوفّر أكثر بكثير من تحسين تقرير ليلي بطيء يعمل مرة واحدة.

الخطوة الثانية: اقرأ EXPLAIN

ضع EXPLAIN قبل استعلام SELECT فتحصل على خطة التنفيذ في صف أو أكثر. الأعمدة الأهم أربعة. عمود type يخبرك بطريقة الوصول مرتّبة من الأسوأ للأفضل، وأهم قيمه في الجدول أدناه. عمود rows يقدّر عدد الصفوف التي سيفحصها MySQL، وكلما قلّ كان أفضل، فرقم بالملايين إنذار مباشر. عمود key يخبرك أي فهرس استُخدم فعلاً، وإذا كان NULL فلا فهرس يُستعمل والمسح كامل. عمود Extra يحمل تحذيرات ثمينة: Using filesort يعني فرزاً إضافياً مكلفاً لأن ترتيبك لا يعتمد على فهرس، وUsing temporary يعني جدولاً مؤقتاً في الذاكرة أو على القرص، وكلاهما يستنزف موارد وينبغي إزالته إن أمكن. أما Using index فخبر سار يعني أن الاستعلام أُجيب من الفهرس وحده بلا لمس بيانات الجدول أصلاً.

الخطوة الثالثة: أضف الفهرس الصحيح

الفهرس بنية بيانات جانبية ترتّب قيم عمود مسبقاً فيقفز MySQL للصف المطلوب بدل مسح كل شيء. القاعدة: فهرس الأعمدة التي تظهر في شروط WHERE وفي JOIN وفي ORDER BY. لكن ليس كل فهرس يفيد، والإفراط في الفهارس يبطئ عمليات الكتابة (INSERT/UPDATE) لأن كل فهرس يجب تحديثه مع كل تعديل، ويستهلك مساحة قرص إضافية. الحل التوازن: فهرس ما تبحث به فعلاً وبكثرة، لا كل عمود احتياطاً. بعد إضافة الفهرس أعد تشغيل EXPLAIN وتأكد أن type تحسّن وأن rows انخفض بدرجة كبيرة، فالتحقق بالأرقام لا بالإحساس هو المعيار.

الفهرس المركّب وترتيب أعمدته

حين يبحث استعلامك بعمودين معاً (مثلاً user_id وstatus)، فالفهرس المركّب على العمودين معاً أقوى من فهرسين منفصلين. لكن الترتيب حاسم ويحكمه ما يسمى "قاعدة البادئة اليسرى": الفهرس المركّب على (user_id, status) يخدم بحثاً بـuser_id وحده، وبحثاً بـuser_id وstatus معاً، لكنه لا يخدم بحثاً بـstatus وحده. القاعدة العملية: ضع أولاً العمود الذي يظهر في شروط المساواة الأكثر انتقائية (الذي يقلّص الصفوف أكثر)، ثم أعمدة المدى والترتيب. ترتيب خاطئ يعني فهرساً موجوداً لكن غير مستخدَم، وهو أسوأ الحالات لأنه يكلّف مساحة وصيانة بلا فائدة.

الفخ الذي يبطل فهرسك

أشهر خطأ يقتل الفهرس صامتاً: تطبيق دالة على العمود المفهرس داخل شرط WHERE. فإذا كتبت شرطاً يلفّ العمود بدالة مثل YEAR على عمود تاريخ أو LOWER على عمود نصّي، فإن MySQL لا يستطيع استخدام الفهرس ويعود للمسح الكامل، لأن الفهرس مبني على القيمة الخام لا على ناتج الدالة. الحل إعادة صياغة الشرط ليبقى العمود عارياً على يسار المقارنة: بدل تغليف عمود التاريخ بدالة تستخرج السنة، قارن العمود مباشرة بمدى تاريخي (من أول السنة إلى آخرها). القاعدة الذهبية: أبقِ العمود المفهرس عارياً في يسار الشرط، وحوّل أي حساب إلى الطرف الآخر من المقارنة. فخ آخر مشابه: المقارنة بأنواع مختلفة (رقم بنصّ) تجبر MySQL على تحويل ضمني يبطل الفهرس أيضاً، فطابق نوع القيمة مع نوع العمود دائماً.

تحسينات أبعد من الفهرس

الفهرس أقوى أداة، لكنه ليس الوحيدة. أولاً، لا تطلب أعمدة لا تحتاجها: كتابة SELECT بأسماء الأعمدة المطلوبة بدل النجمة يقلّل البيانات المنقولة ويفتح باب "الفهرس المغطّي" (covering index) الذي يجيب الاستعلام من الفهرس وحده بلا لمس الجدول. ثانياً، استخدم LIMIT حين تريد صفوفاً معدودة، فجلب عشرة صفوف مرتّبة عبر فهرس أرخص بكثير من جلب الآلاف ثم رميها. ثالثاً، احذر الترقيم العميق بـOFFSET كبير، فالانتقال للصفحة الألف عبر OFFSET يجبر MySQL على قراءة كل الصفوف السابقة ورميها، والبديل الترقيم بمفتاح (تذكّر آخر معرّف رأيته وابدأ منه). رابعاً، الاستعلامات المتداخلة الثقيلة قد تُعاد صياغتها كـJOIN أنظف يخطّطه MySQL أفضل. خامساً، راقب حجم جداولك مع الزمن، فجدول ينمو للملايين قد يحتاج تقسيماً (partitioning) أو أرشفة الصفوف القديمة لجدول منفصل. القاعدة الجامعة: قِس قبل وبعد كل تغيير عبر EXPLAIN وسجلّ الاستعلامات البطيئة، فالتحسين بلا قياس تخمين، والتخمين في قواعد البيانات مكلف.

أثر تحسين استعلام واحد على vCPU

التحسين ليس ترفاً نظرياً، بل ينعكس رقماً على فاتورة مواردك. استعلام يمسح مليون صف يشغّل المعالج والقرص بكثافة، وإذا تكرر آلاف المرات في الساعة تحت حمل حقيقي فقد يرفع استهلاك vCPU إلى الذروة ويبطئ كل الطلبات الأخرى على نفس السيرفر. تحويل ذلك الاستعلام إلى قراءة عشرة صفوف عبر فهرس واحد قد يخفض حمل قاعدة البيانات بنسبة كبيرة، فيتنفّس السيرفر ويخدم زوّاراً أكثر بنفس المواصفات. هذا يعني عملياً أن تحسين استعلام قد يؤجّل الحاجة لترقية الباقة، فإذا كنت قد بلغت السقف فعلاً بعد التحسين، فترقية خادمك السحابي فورية: تضيف vCPU بـ500 ريال سنوياً أو 2GB رام بـ600 ريال سنوياً بلا توقف الخدمة. راجع الأسعار والباقات لتوازن بين تحسين الكود وترقية العتاد.

الخلاصة العملية

لا تحسّن بالتخمين: فعّل slow_query_log واصطد الجناة الحقيقيين بالأثر الكلي. اقرأ EXPLAIN وركّز على type وrows وkey وExtra، واحذر من Using filesort وUsing temporary. أضف فهرساً لأعمدة WHERE وJOIN وORDER BY بلا إفراط، ورتّب أعمدة الفهرس المركّب بقاعدة البادئة اليسرى. لا تلفّ العمود المفهرس بدالة أبداً، وطابق أنواع القيم. تحقّق دائماً بإعادة EXPLAIN بعد كل تغيير، فالرقم هو الحكم، واستعلام واحد محسّن قد يوفّر عليك ترقية كاملة. اجعل هذه دورة مستمرة لا حدثاً لمرة واحدة: راجع سجلّ الاستعلامات البطيئة كل فترة، لأن استعلاماً كان سريعاً على ألف صف قد يصير بطيئاً حين يبلغ الجدول مليوناً، فالتحسين مع نمو بياناتك عادة دائمة لا إصلاحاً عابراً تنساه بعد أول نجاح. تذكّر أن أرخص ترقية للسيرفر تظل تكلفة، بينما فهرس صحيح مجاني ويخدمك سنوات، فابدأ دائماً بتحسين الكود قبل شراء عتاد أكبر، ولا تلجأ للترقية إلا حين تستنفد ما يمكن للفهارس وإعادة الصياغة أن تقدّمه.