استراتيجيات الفهرسة لـ queries تحليلية

index يجعل استعلاماً ٢٠٠× أسرع، والـ index التالي يجعل نفس الاستعلام ٤× أبطأ لأنّ المخطّط توقّف عن استخدام الأوّل. الـ indexes ليست «المزيد أفضل». هذا المقال عن الأنماط التي عملت للاستعلامات التحليليّة التي نشحنها (لوحات معلومات، تقارير، «أرني الإجماليّات لهذا الشهر») والأنماط التي أهدرت ليالي تصحيح.

شكلا استعلام، استراتيجيّتا index#

الاستعلامات التحليليّة تنقسم إلى عائلتَين تحتاجان indexing مختلف.

العائلة A: مقصور بمستأجر، فلتر عمود ضيّق#

SELECT SUM(amount), COUNT(*)
FROM invoices
WHERE tenant_id = ? AND status = 'paid' AND due_date >= ?

انتقائيّ على عدد صغير من الأعمدة، يعيد عدداً صغيراً من الصفوف أو تجميعات. يريد index مركّب بالأعمدة بترتيب الانتقائيّة، يقود بـ tenant_id.

CREATE INDEX idx_invoices_tenant_status_due
  ON invoices (tenant_id, status, due_date);

الترتيب يهمّ. tenant_id أوّلاً لأنّ كلّ استعلام لديه. status ثانياً لأنّه أكثر انتقائيّة من due_date. due_date أخيراً لأنّه فلتر مدى وفلاتر المدى تنهي البادئة القابلة للاستخدام من الـ index.

العائلة B: مقصور بمستأجر، مسح عريض مع تجميع#

SELECT contact_id, SUM(amount)
FROM invoices
WHERE tenant_id = ? AND created_at >= ?
GROUP BY contact_id
ORDER BY SUM(amount) DESC
LIMIT 20

مسح عريض على مدى تاريخ، GROUP BY على عمود ليس في الـ WHERE. الـ indexes المركّبة لا تساعد؛ المسح يجب أن يقرأ كلّ صفّ في مدى التاريخ لعمل التجميع. ما يساعد:

1. التقسيم بـ created_at كي يكون المسح محدوداً.
2. التجسيد للإجابة إن كان الاستعلام يُضرَب كثيراً (جدول tenant_top_contacts صغير، يُحدَّث على جدول).
3. read replicas للمسح، كي لا يتنافس مع الكتابات الحيّة.

محاولة «الـ index طريقك» إلى استعلامات العائلة B السريعة كلّفنا أيّاماً أكثر من أيّ درس قاعدة بيانات آخر. الـ indexes لا تساعد المسوحات.

الأخطاء التي كلّفتنا#

خمسة أنماط ضربناها، بترتيب الألم الذي سبّبته.

١. إضافة indexes واحداً تلو الآخر دون فحص المخطّط#

مطوّر مبتدئ يضيف index لإصلاح استعلام بطيء. الاستعلام يصبح سريعاً. ثلاثة استعلامات أخرى استخدمت index مختلفاً تصبح بطيئة لأنّ المخطّط الآن يختار الجديد (في MySQL: مفاجآت index_merge). كلّ index جديد يحتاج EXPLAIN على كلّ استعلام ضدّ الجدول.

٢. النمط المضادّ «covering index»#

«أضف كلّ عمود يشير إليه الاستعلام إلى الـ index، فلن يضطرّ للمس الجدول أبداً». هذا أحياناً صحيح وأحياناً فخّ: الـ index يصبح عريضاً جدّاً بحيث يصبح تحديث أيّ من أعمدته مكلفاً، والجدول الآن لديه ١٢ indexes تحتاج كلّها للصيانة على كلّ كتابة.

نستخدم covering indexes فقط حين:

• الاستعلام يعمل > ١٠٠ مرّة في الدقيقة، و
• الأعمدة المضافة للتغطية للقراءة فقط أو نادرة التحديث.

٣. indexes على أعمدة منخفضة العدديّة#

CREATE INDEX ON invoices(status) حيث status لديه ٤ قيم متمايزة. الـ index عديم الفائدة: أيّ قيمة تطابق ربع الجدول، وهو كثير. الأسوأ، المخطّط أحياناً يستخدمه على أيّ حال («الـ index هناك، يجب أن يكون مفيداً») والاستعلام يصبح أبطأ ممّا كان سيكون مع مسح الجدول.

الإصلاح: indexes مركّبة تقود بعمود عالي العدديّة (tenant_id، created_at)، مع العمود منخفض العدديّة ثانياً.

٤. ردّ فعل «أضف فقط index» على جدول بمعدّل كتابة عالٍ#

index على جدول ثقيل الكتابة يكلّف بالتناسب لكلّ كتابة. كان لدينا جدول يقوم بـ ١٠٠٠ إدراج في الدقيقة نما إلى ٨ indexes؛ throughput الكتابة انخفض ٣×. أسقطنا أربعة من الـ indexes واستبدلناها بجدول «ملخّص» غير مُطبَّع batched التحديثات.

٥. نسيان أنّ المخطّط يُخزِّن الخطط مؤقّتاً#

تضيف index، التشغيل التالي للاستعلام سريع. بعد أسبوع نفس الاستعلام بطيء مجدّداً لأنّ الخطّة المُخزَّنة كانت القديمة والمخطّط لم يُعِد التقييم. لدينا خطوة نشر تشغّل ANALYZE TABLE على الجداول الحارّة بعد تغييرات schema؛ بدونها، بعض الاستعلامات تستغرق أيّاماً «لاكتشاف» الـ index الجديد.

ما نُقَيس#

ثلاثة مقاييس، بالأولويّة:

p99 زمن الاستجابة لكلّ استعلام مسمّى#

كلّ استعلام نهتمّ به مُعلَّم باسم في طبقة التطبيق (DB::statement في Laravel مع تعليق، /* invoice_summary */). سجلّ الاستعلامات البطيئة يُجمِّع حسب الـ tag. نتتبّع p50/p95/p99 أسبوعيّاً. قفزة في p99 هي المؤشّر السابق على أنّ index توقّف عن الاستخدام أو أنّ نمو البيانات عبر عتبة.

استخدام الـ index#

SELECT * FROM sys.schema_unused_indexes (MySQL) أو pg_stat_user_indexes (Postgres) يخبرنا أيّ indexes لا تُضرَب أبداً. نراجع شهريّاً ونُسقط غير المستخدمة. حوالي ٢٠٪ من الـ indexes التي أضفناها على الإطلاق أُسقطت لاحقاً.

تضخّم الكتابة#

إدراجات في الثانية إلى جدول، مقابل عدد الـ indexes. إن انخفض معدّل الإدراج مع تراكم الـ indexes، إمّا نُسقط indexes أو ننقل الكتابة إلى جدول staging أقلّ indexing يُلخَّص بشكل غير متزامن.

مثال مدروس#

استعلام حقيقيّ بدأ عند ١٤ ثانية وانتهى عند ٨٠ مللي ثانية:

-- البطيء الأصليّ
SELECT i.id, i.number, c.name AS contact_name, SUM(p.amount) AS paid
FROM invoices i
JOIN contacts c ON c.id = i.contact_id
LEFT JOIN payments p ON p.invoice_id = i.id
WHERE i.tenant_id = ?
  AND i.status = 'open'
  AND i.due_date < CURRENT_DATE
GROUP BY i.id
ORDER BY i.due_date ASC
LIMIT 50;

ما غيّرناه، بالترتيب:

1. أضفنا index (tenant_id, status, due_date) على invoices. الاستعلام: ١٤ ث → ٤ ث. الفوز الكبير، بتقليل المسح إلى مجموعة فرعيّة صغيرة من الفواتير.
2. أضفنا index (invoice_id) يغطّي (amount) على payments. الاستعلام: ٤ ث → ١٫٢ ث. الـ LEFT JOIN كان يمسح كلّ الدفعات لكلّ مستأجر؛ الآن يبحث بـ invoice id.
3. أزلنا الـ JOIN contacts c من الاستعلام وحمّلنا أسماء جهات الاتّصال بشكل كسول في طبقة التطبيق لـ ٥٠ صفّاً تعرضها الصفحة. الاستعلام: ١٫٢ ث → ٨٠ مللي ثانية. الـ join كان يجبر فرزاً على مفتاح مختلف؛ سحبه دع المخطّط يختار مساراً نظيفاً.

الخطوة الثالثة هي التي فاجأتني. «أضف فقط index» لم يعمل بعد نقطة. إعادة تشكيل الاستعلام فعلت.

ماذا تفعل حين لا يعمل أيّ index#

ثلاثة خيارات صادقة:

1. عرض مُجسَّد. احسب الإجابة بشكل غير متزامن، خزّنها، اخدم من التخزين. أعد التحديث على جدول يطابق متطلّب الطزاجة.
2. read replica. انقل الاستعلام التحليليّ بعيداً عن الـ primary كي لا يتنافس مع الكتابات.
3. تخزين مختلف. بعض الاستعلامات تحليليّة بنيويّاً. انقل البيانات إلى متجر موجّه عمود (ClickHouse، DuckDB، حتّى Postgres مع TimescaleDB) حيث المسوحات العريضة رخيصة.

كلّ منها يكلّف تعقيداً تشغيليّاً. لا تمدّ يدك إليها حتّى تستبعد الـ indexing وإعادة تشكيل الاستعلام.

الخلاصة المملّة#

معظم الاستعلامات البطيئة يمكن إصلاحها بشيئَين: index مركّب صحيح، وإعادة تشكيل تدع المخطّط يختار مساراً نظيفاً. معظم شكاوى «الـ index لا يعمل» عن index بترتيب أعمدة خاطئ أو عمود قائد منخفض العدديّة. معظم تذاكر «قاعدة البيانات بطيئة» عن استعلامات تحليليّة على نفس قاعدة البيانات التي تخدم الكتابات.

المنشور التالي عن لماذا نقيس على أجهزة حقيقيّة. أو تصفّح موضوع الأداء للبقيّة.