مؤشرات SQL - لماذا تكون استعلامك بطيئًا وما يجب فعله بالفعل بشأنه

استعلامك يستغرق 2 مللي ثانية على localhost مع 500 صفًا. على الإنتاج مع 3 مليون صف، يستغرق 9 ثانية ويُنظر إلى علامة التحميل. قد يكون الاستعلام جيدًا — المشكلة تكمن في التصميم. تحتاج إلى فهرس، وتحتاج إلى فهم لماذا لذا توقف عن التخمين عند حدوث هذا الأمر مرة أخرى.

ما الذي يفعله قاعدة البيانات بدون فهرس

فهرس B-tree هو نسخة مرتبة من عمود (أو أعمدة)، مخزنة في شجرة متوازنة يمكن للقاعدة أن تبحث فيها باستخدام طريقة البحث الثنائي في وقت O(log n). بدونه، تقوم القاعدة بـ فحص الجدول بالكامل — تقرأ كل صف لتحديد المطابقات. مع 3 مليون صف، هذا يعني 3 مليون قراءة صف. مع فهرس على العمود المناسب، قد تصل إلى 20-30 تصفح للعقد في الشجرة لتحديد الصفوف المطابقة.

تخيلها مثل مطبخ الأسماء. إيجاد "سميث، جون" بالترتيب الأبجدي يستغرق ثوانٍ. إيجاد كل الأشخاص الذين يحملون اسم "جون" من خلال قراءة كل مدخل من الصفحة الأولى يستغرق وقتًا لا نهائيًا. الفهرس هو الترتيب الأبجدي — يستخدمه قاعدة البيانات للانتقال مباشرة إلى الصفوف المطلوبة بدلًا من فحص كل شيء.

شيء مهم: توجد الفرسات على القرص بجانب جدولك. يحافظ النظام تلقائيًا على الفرسات أثناء الكتابة. هذا مجانٍ للقراءة، لكنه يُكلف كل عملية إدراج، تحديث، أو حذف — وهذا هو السبب الذي يجعلك لا يمكنك تثبيت فهرس على كل شيء.

إيجاد المشكلة باستخدام EXPLAIN

قبل أي تدخل، اجرِ EXPLAIN على الاستعلام البطيء. إليك ما تبحث عنه في MySQL:

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 2847391 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

مجالان مهمان:

• type: ALL — فحص الجدول بالكامل. غالبًا ما يكون سيئًا بمجرد أن يتجاوز عدد الصفوف 100 ألف.
• rows: 2847391 — يقدر المُحسّن أن يفحص 2.8 مليون صف لتقديم النتائج.

الآن أضف الفهرس واجريه مرة أخرى:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys          | key                    | key_len | ref   | rows | Extra |
+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+
|  1 | SIMPLE      | orders | ref  | idx_orders_customer_id | idx_orders_customer_id | 4       | const |   12 |       |
+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+

type: ref و rows: 12. هذا هو الحل. 2.8 مليون صف مُفحص → 12.

في PostgreSQL، استخدم EXPLAIN ANALYZE (الذي ينفذ الاستعلام فعليًا) لاستلام أرقام التوقيت الحقيقية:

-- Before index
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

Seq Scan on orders  (cost=0.00..89432.00 rows=14 width=156)
                    (actual time=142.341..8934.221 rows=12 loops=1)
  Filter: (customer_id = 12345)
  Rows Removed by Filter: 2847379
Planning Time: 0.4 ms
Execution Time: 8934.8 ms

-- After: CREATE INDEX idx_orders_customer_id ON orders (customer_id);
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

Index Scan using idx_orders_customer_id on orders
                    (cost=0.43..92.15 rows=12 width=156)
                    (actual time=0.042..0.198 rows=12 loops=1)
  Index Cond: (customer_id = 12345)
Planning Time: 0.5 ms
Execution Time: 0.3 ms

8934 مللي ثانية → 0.3 مللي ثانية. هذا ليس مثال مُختَرَع — هذا هو التأثير الحقيقي لفهرس مفقود على جدول يحتوي على عدة ملايين من الصفوف.

الفرسات المركبة والقاعدة المُتَّسقة من اليسار

بمجرد أن يكون لديك أكثر من عمود في شرط WHERE، تحتاج إلى فرسات مركبة — وترتيب الأعمدة مهم.

SELECT * FROM orders
WHERE customer_id = 12345
  AND status = 'pending'
  AND created_at > '2024-01-01';

ال قاعدة اليسار المُتَّسقة: يستخدم قاعدة البيانات فهرس مركب بدءًا من العمود الأيسر ويتحرك إلى اليمين، لكنه يتوقف عن القيمة عندما يصل إلى فجوة. مع فهرس على (a, b, c):

• WHERE a = 1 — يستخدم الفهرس
• WHERE a = 1 AND b = 2 — يستخدم كلا العمودين في الفهرس
• WHERE a = 1 AND b = 2 AND c = 3 — يستخدم الفهرس بالكامل
• WHERE b = 2 — لا يستخدم الفهرس (العمود a مُستبعد)
• WHERE a = 1 AND c = 3 — يستخدم فقط الجزء a، يتجاهل c

قاعدة الترتيب العملية: الشروط المتساوية أولاً، ثم الشروط المُتغيرة في النهاية. الشروط المُتغيرة (>, <, BETWEEN, LIKE 'prefix%') تُكسر المُتَّسقة المُمكنة للعمود التالي. لذا:

-- Good: equality columns first, range column last
CREATE INDEX idx_orders_compound ON orders (customer_id, status, created_at);

-- This query uses all three columns of the index:
SELECT * FROM orders
WHERE customer_id = 12345   -- equality, goes first
  AND status = 'pending'    -- equality, goes second
  AND created_at > '2024-01-01'; -- range, goes last

-- Bad ordering for this query: range column in the middle
-- CREATE INDEX idx_bad ON orders (customer_id, created_at, status);
-- The status filter won't use the index because created_at is a range predicate

أيضًا، فكر في التمييز: ضع العمود الأكثر تمييزًا (أقل قيم متكررة) في البداية في الفهرس. إذا كان customer_id يحتوي على 50,000 قيمة مختلفة و status يحتوي على 3، customer_id يجب أن يأتي أولًا — لأنه يُقلل من عدد النتائج أسرع.

عندما تؤثر الفرسات سلبًا

الفرسات ليست مجانية. كل فهرس يُكلف أداء الكتابة ومساحة القرص. يُحدث النظام تحديثًا لجميع الفرسات مع كل إدراج، تحديث، أو حذف على الجدول. على الجداول ذات الأداء العالي في الكتابة، فإن الفرسات الزائدة تؤثر بشكل حقيقي على السرعة. إليك المواقف التي تفشل فيها الفرسات أو تؤثر سلبًا:

• العمود ذو القيم المنخفضة. فهرس على عمود منطقي أو عمود يحتوي على 3 قيم مختلفة غالبًا ما يُتجاهله من قبل المُحسّن. إذا كان الاستعلام سيُرجع أكثر من ~10-15% من الصفوف من خلال الفهرس، فإن القاعدة غالبًا ما تفضل فحص الجدول — لا يُعتبر تكلفة البحث في الفهرس مبررة. تحقق من خلال EXPLAIN ما إذا كان الفهرس يُستخدم فعليًا. status الجداول ذات الأداء العالي في الكتابة.
• الجداول السجلية، جداول الأحداث، جداول المراقبة — ابقَ حذرًا. جدول يحتوي على 10 فرسات وعدد كبير من الكتابات في الساعة يدفع 10 أضعاف من التكلفة في الكتابة على هذه الفرسات. الفرسات غير المستخدمة.
• في PostgreSQL، يُظهر pg_stat_user_indexes — عدد مرات استخدام كل فهرس منذ آخر تعيين للبيانات. الفهرس الذي لا يُستخدم هو تكلفة مفرطة. يُراقب هذا في MySQL من خلال مخطط الأداء ( idx_scan في MySQL 5.7+).sys.schema_unused_indexes الاستنتاج الخاطئ من قبل المُحسّن.
• لا يُعتبر المُخططات دائمًا صحيحة. في بعض الأحيان يختار فهرس أبطأ من فحص الجدول بالكامل. سيُكتشف هذا — مقارنة بين عدد الصفوف المُقدر والواقع. في MySQL يمكنك التوجيه باستخدام EXPLAIN ANALYZE ؛ في PostgreSQL يمكنك اختباره باستخدام USE INDEX (idx_name)(لا تتركه في الإنتاج). SET enable_seqscan = off الفرس أو إعادة صياغة الاستعلام؟ التنازل الحقيقي

في بعض الأحيان يكون الفهرس هو الحل. في بعض الأحيان يكون الاستعلام هو المشكلة، والفرس فقط يُخففها. بعض الأنماط التي يجب إعادة صياغة الاستعلام بدلًا من (أو بالإضافة إلى) إضافة فهرس:

الوظائف على الأعمدة المُفرسة

إدراج وظيفة على عمود يمنع استخدام الفهرس القياسي. يحتوي الفهرس على قيم من

-- Will NOT use an index on created_at — the function result isn't indexed:
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- Rewrite to use the index:
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01';

، وليس قيم من created_at— هذه أشياء مختلفة. تدعم PostgreSQL فرسات التعبير ( YEAR(created_at) ) كحل بديل، لكن إعادة صياغة الاستعلام هي أبسط وأكثر فعالية وتعمل في كل الأماكن.CREATE INDEX ON orders (YEAR(created_at))الفرسات المُغطاة لمسارات القراءة السريعة

فهرس مُغطى

أ يحتوي على جميع الأعمدة التي يحتاجها الاستعلام، لذا لا يحتاج قاعدة البيانات إلى قراءة الصفوف الفعلية — تُجيب من الفهرس بالكامل. يمكن أن يكون هذا أسرع بكثير من الفهرس العادي للجداول المُقرّة بشدة: أنماط الـ LIKE

-- Query that only needs id, status, total:
SELECT id, status, total FROM orders WHERE customer_id = 12345;

-- Regular index: database finds matching rows via index, then reads each row from table
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- Covering index: database reads everything it needs from the index alone
CREATE INDEX idx_orders_cover ON orders (customer_id, id, status, total);
-- Note: customer_id first (the WHERE column), then the SELECT columns

الوكلات المُقدمة في البداية لا يمكن تحسينها باستخدام فرسات B-tree. إذا كنت بحاجة إلى بحث في النهاية أو في الجزء المُستخرج، فتحتاج إلى فهرس مُخصص (

-- Uses the index (prefix match — database knows where to start):
SELECT * FROM users WHERE email LIKE 'john%';

-- Does NOT use the index (leading wildcard — database doesn't know where to start):
SELECT * FROM users WHERE email LIKE '%@gmail.com';

في MySQL،FULLTEXT في PostgreSQL) أو نظام بحث خارجي مثل Elasticsearch. GIN المسائل الشائعة التي يجب مراقبتها pg_trgm الاستعلامات N+1

لا يُعالج الفهرس لمشكلة N+1. إذا كان نظامك يُجرِّي استعلامًا واحدًا لاسترجاع 200 طلب، ثم استعلامًا منفصلًا لكل طلب لاسترجاع اسم العميل، فهناك 201 استعلام. كل واحد قد يكون سريعًا. المشكلة هي الحجم. حلها بـ JOIN أو استعلام مُجمّع — لا يمكن تغيير عدد الاستعلامات بغض النظر عن الفرسات.

شيء آخر: انظف الاستعلام قبل تحليله

قبل تشغيل EXPLAIN على استعلام بطيء، يساعد أن يكون الاستعلام مُصاغ بوضوح. استعلام بسيط بطول 200 حرف بدون تجميل يصعب فهمه مقارنة بطلب مُنظّم. يُعالج

مُصاغ الاستعلام في IO Tools يُعالج MySQL، PostgreSQL، SQL Server، وغيرها من الأنواع — مفيد عند نسخ استعلام من سجلات أو مخرجات نظام إدارة الأكواد وتحتاج لرؤية هيكله بسرعة. القائمة العملية

اجري

• قبل إضافة أي فهرس. تأكد من وجود المشكلة أولاً. EXPLAIN ابحث عن
• (MySQL) أو type: ALL (PostgreSQL) — عادةً ما تكون هذه هي المكان الذي تقع فيه المشكلة. Seq Scan تحقق من تقدير
• في EXPLAIN. فحص ملايين الصفوف لتقديم عشرات من النتائج = فهرس مفقود. rows في الفرسات المركبة: الشروط المتساوية أولاً، ثم الشروط المُتغيرة في النهاية.
• العمود ذو التمييز الأعلى يأتي في البداية في الفهرس المركب.
• لا تُفرس الأعمدة ذات القيم المنخفضة دون التحقق من أن المُحسّن يستخدم الفهرس فعليًا.
• تحقق من الفرسات غير المستخدمة بشكل دوري:
• (PostgreSQL)، pg_stat_user_indexes (MySQL). sys.schema_unused_indexes إذا كان وظيفة تُحيط بالعمود في شرط WHERE، فكر في إعادة صياغة الاستعلام بدلًا من تثبيت الوظيفة.
• للمسارات السريعة المُقرّة ذات عدد قليل من الأعمدة، أعدّ فهرسًا مُغطى.
• N+1 هو مشكلة في بنية الاستعلام، وليس مشكلة في الفهرس.
• N+1 هو مشكلة في بنية الاستعلام، وليس مشكلة في المؤشر.

قد يعجبك أيضاً