استعلام JOIN على جدول ملايين الصفوف: خطة عملية لتفادي filesort

السيناريو: MySQL 8.0.33 على Ubuntu 22.04، InnoDB، جدول orders بحوالي 12 مليون صف وجدول customers بحوالي 900 ألف، خادم 16GB RAM و buffer pool مضبوط على 10GB. الاستعلام الأصلي كان:

SELECT c.id, c.name, o.created_at, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 50;

الزمن 8.4 ثانية وEXPLAIN يظهر Using where; Using temporary; Using filesort على orders، رغم وجود فهرس على status وآخر على created_at.

ما فعلته أولاً: قياس التوزيع بدل التخمين.

SELECT status, COUNT(*) FROM orders GROUP BY status;

النتيجة: paid يمثل 31% أي حوالي 3.7 مليون صف. هنا السبب واضح: فهرس status منخفض الانتقائية، والتحسين يفضل مسحاً كاملاً ثم فرزاً. الحل لم يكن إضافة فهرس مركب عشوائياً بل فهرس يخدم الشرط والترتيب معاً:

ALTER TABLE orders
  ADD INDEX idx_status_created (status, created_at, customer_id),
  ALGORITHM=INPLACE, LOCK=NONE;

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

SELECT c.id, c.name, o.created_at, o.total
FROM (
  SELECT customer_id, created_at, total
  FROM orders
  WHERE status = 'paid'
  ORDER BY created_at DESC
  LIMIT 50
) o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.created_at DESC;

النتيجة: من 8.4 ثانية إلى 41ms. السبب أن الفهرس المركب يسمح بقراءة آخر 50 صفاً بترتيب مسبق من نهاية الشجرة، فيختفي filesort تماماً، ثم JOIN على 50 صفاً فقط. الثمن: الفهرس الجديد يضيف كتابة على كل INSERT/UPDATE لـorders، وحجمه على القرص حوالي 600MB. على جدول كتابته كثيفة هذا مقايضة حقيقية، وليس تحسيناً مجانياً.

أين يفشل هذا: إذا كان status موزعاً بشكل يجعل الشرط يغطي أكثر من 60% من الجدول، فإن قراءة الفهرس تصبح أغلى من المسح المتسلسل وقد يعود المحسن لخطة أسوأ. كذلك إذا احتجت WHERE status IN (...) متعددة أو شرطاً على created_at بنطاق، ينكسر ترتيب الفهرس المركب ويظهر filesort من جديد. جربت أيضاً ORDER BY o.created_at DESC, o.id DESC لكسر التعادل؛ هذا يتطلب فهرساً رباعياً وإلا رجع الفرز. وأسوأ خطأ وقعت فيه: إضافة الفهرس أثناء ساعات الذروة مع LOCK=NONE لكن ALGORITHM=INPLACE يعني إعادة بناء كاملة، وأخذت الكتابة على القرص وقتاً أطول مما توقعت.

السؤال: عندما يكون عمود الحالة منخفض الانتقائية عندك، هل تلجأ إلى فهرس مركب يطابق ترتيب الفرز، أم تفصل الاستعلام إلى مرحلتين (جلب المعرفات ثم الجلب النهائي)؟ وما الحد الذي عنده تعتبر الفهرس المركب غير مبرر؟