مخرج SQL لا يجب أن يبدو كنص غريب

أنت تُجري EXPLAIN على استعلام بطيء. يُرجع لك قاعدة البيانات شيء يشبه هذا:

Hash Join  (cost=145.00..578.00 rows=3200 width=96)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders  (cost=0.00..248.00 rows=16000 width=64)
  ->  Hash  (cost=132.50..132.50 rows=1000 width=32)
        ->  Seq Scan on customers  (cost=0.00..132.50 rows=1000 width=32)
              Filter: ((country)::text = 'US'::text)
              Rows Removed by Filter: 9000

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

يُخبرك EXPLAIN بشيء محدد وقابل للتطبيق. إليك كيفية قراءته.

EXPLAIN مقابل EXPLAIN ANALYZE: أيهما لاستخدامه؟

سهل EXPLAIN يُظهر لك المخطط الذي يُخطط له. لا يُنفذ الاستعلام — فقط يُعرض المخطط مع تكاليف مُقدّمة. سريع، لكن التقديرات قد تكون خاطئة.

EXPLAIN ANALYZE يُنفذ الاستعلام فعليًا ويضيف التوقيت الحقيقي. تُحصل على المخطط والبيانات الفعلية:

-- PostgreSQL: run the query and show real timings
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

-- MySQL equivalent
EXPLAIN ANALYZE SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

ال BUFFERS الخيار هو متوفر فقط في PostgreSQL ويضيف عدداً من الاصطدامات والانعدامات في المُخزن — مفيد لتشخيص مشاكل الاتصال، لكن تجاهله الآن.

ملاحظة واحدة: EXPLAIN ANALYZE يُنفذ الاستعلام بشكل حقيقي. في حالة DELETE أو UPDATE، اجعله داخل معاملة وانسخها:

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'pending';
ROLLBACK;

أرقام التكلفة: ما معناها وما لا يحتويه

كل عقدة في المخطط تُظهر (cost=X..Y rows=N width=W). يرى الناس هذه الأرقام ويُفترض أنها ملحوظات. ليست كذلك.

• cost=X..Y — X هو التكلفة الابتدائية (العمل المُبذول قبل عرض أول صف)، Y هي التكلفة الإجمالية لمعالجة كل الصفوف. الوحدة هي "تكلفة الصفحة" المُستقلة — تقريبًا متناسبة مع قراءة صفحة القرص، لكنها مُحددة بواسطة ثوابت التكلفة في PostgreSQL. تكلفة 1.0 تعني قراءة صفحة متتالية واحدة.
• rows=N — عدد الصفوف المُقدر من قبل المخطط. يمكن أن يكون خاطئًا بشكل كبير إذا كانت بيانات الجدول قديمة.
• width=W — حجم الصف المُتوسط بالبايت. الصفوف الأكبر تبطئ التصاريح والترتيب.

عندما تُنفذ EXPLAIN ANALYZE، يحصل كل عقدة أيضًا على (actual time=X..Y rows=N loops=L). هذه متماسكة — من حيث التماثل الهندسي. مقارنة ذلك مع مليون ملحوظة. فجوة كبيرة بين المقدر والواقع في عدد الصفوف هي الإشارة الأكثر موثوقية أن المخطط اتخذ قرارًا خاطئًا.

مراجعة مفصلة لـ EXPLAIN ANALYZE المُعزّزة

إليك نفس الاستعلام من المقدمة، هذه المرة مع ANALYZE وكل سطر مُفسّر:

Hash Join  (cost=145.00..578.00 rows=3200 width=96)
           (actual time=2.451..12.879 rows=3168 loops=1)
-- Top-level node. Joins orders and customers via hashing.
-- cost estimate: 145..578 | actual: 2.4ms startup, 12.9ms total
-- rows estimate: 3200 | actual: 3168 — pretty close here

  Hash Cond: (o.customer_id = c.id)
  -- This is the join key. The planner built a hash table on customers.id
  -- then probed it with every row from orders.

  ->  Seq Scan on orders  (cost=0.00..248.00 rows=16000 width=64)
                          (actual time=0.019..3.125 rows=16000 loops=1)
  -- Full table scan on orders. 16,000 rows scanned.
  -- No filter here — we pull all orders and join them below.
  -- This is expected: we need all orders, so no index would help here.

  ->  Hash  (cost=132.50..132.50 rows=1000 width=32)
            (actual time=2.412..2.413 rows=1000 loops=1)
            Buckets: 1024  Batches: 1  Memory Usage: 64kB
  -- Builds the in-memory hash table from the customers result.
  -- 1 batch = fits in work_mem. Multiple batches = spilling to disk (bad).

        ->  Seq Scan on customers  (cost=0.00..132.50 rows=1000 width=32)
                                   (actual time=0.012..1.345 rows=1000 loops=1)
              Filter: ((country)::text = 'US'::text)
              Rows Removed by Filter: 9000
        -- Scanned all 10,000 customer rows. Kept 1,000 where country='US'.
        -- *** This is the expensive part. An index on customers.country
        --     would let PostgreSQL skip the 9,000 discarded rows entirely. ***

Planning Time: 0.187 ms
Execution Time: 13.451 ms

يظهر المطلوب فورًا: Rows Removed by Filter: 9000 على مسح كامل للجدول هو النمط الأكثر شيوعًا لـ "تحتاج إلى مؤشر هنا."

أنواع العقد التي ستجدُها فعليًا

مُسح تسلسلي

يُقرأ كل صف في الجدول، بترتيب السجل. يرى الناس هذا ويُريدون فورًا إضافة مؤشر. لكن مسح التسلسل ليس دائمًا خاطئًا — إذا كان التصفية تُغطي 20%+ من الصفوف، فإن المخطط يُقرر بشكل صحيح أن التصفح العشوائي سيكون أبطأ. مسح تسلسلي على جدول بـ 500 صفًا لا يُعتبر خاطئًا. مسح تسلسلي على جدول بـ 10 مليون صف مع تصفية مُختارة بشكل عالٍ هو مشكلة.

مُسح مؤشر

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

مُسح مُستقل من المؤشر

يمكن الإجابة على الاستعلام بالكامل من المؤشر دون تدخل السجل. يتطلب أن تكون كل عمود في جملة SELECT و WHERE مُغطى بالمؤشر. أسرع نوع من المسح — إذا رأيت هذا، فإن تصميم المؤشر يُحقق وظيفته.

مُسح سلسلة مُتسلسلة

نقطة وسطى. يُبني المخطط خريطة تُظهر الصفوف التي تحتوي على صفوف مطابقة (من خلال Bitmap Index Scan)، ثم يأخذ فقط تلك الصفوف بترتيب، مما يقلل من الاتصال العشوائي. شائع عندما يكون مسح المؤشر سيءًا من حيث الاتصال العشوائي ولكن مسح التسلسل سيُمسح عددًا كبيرًا من الصفوف غير الضرورية.

مُسح مُدمج

يُبني جدولًا من المدخل الأصغر، ثم يُستكشف به كل صف من المدخل الأكبر. مفيد للترابطات الكبيرة دون ترتيب مفيد. انتبه إلى Batches: > 1 في عقدة المُدمج — يعني أن الجدول انتهى إلى القرص لأنه تجاوز work_mem. زيادة work_mem للمُستند في تلك الجلسة غالبًا ما تُحل المشكلة.

مُسح مُتداخل

لكل صف على الجانب الخارجي، يُستكشف الجانب الداخلي (غالبًا من خلال مؤشر). ممتاز عندما يكون أحد الجانبين صغيرًا — O(n) بدلًا من O(n log n). سيء عندما يكون كلا الجانبين كبيرين، لأنها تُنفذ مسح الجانب الداخلي مرة واحدة لكل صف على الجانب الخارجي. إذا رأيت مسحًا مُتداخلًا مع loops=50000، فإن مسح الجانب الداخلي يُنفذ 50,000 مرة.

مُسح تجميعي

يصل المدخلان مُرتّبان على مفتاح الترابط؛ يمشي المخطط بينهما بالتوازي. فعّال لكنه يتطلب الترتيب مسبقًا. ستجد هذا عندما يكون كلا الجانبين مُتّصلين بمؤشر على مفتاح الترابط، أو عندما يقرر المخطط أن ترتيب المُدخل مُفضل أكثر من التصنيف.

لماذا يختار المخطط مسح التسلسل على مسح المؤشر؟

يُسبب هذا ارتباكًا لدى الناس. لديك مؤشر. الاستعلام بطيء. يظهر EXPLAIN مسح تسلسلي.

يستخدم المخطط بيانات العمود المُخزنة في pg_statistic، مُحدّثة من خلال ANALYZE — لتقدير عدد الصفوف التي سيعودها التصفية. إذا قدر أن 30% من الجدول سيعود، فإن مسح التسلسل هو بالفعل أرخص من 300,000 تصفح عشوائي للمؤشر. التصفح العشوائي مكلف.

الحد الذي يفضل فيه المخطط مسح التسلسل هو تقريبًا 10–20% من التمييز، وفقًا لجهازك وثوابت التكلفة. يمكنك مراجعة تقدير المخطط مقابل الواقع:

-- Check the planner's selectivity estimate
EXPLAIN SELECT * FROM orders WHERE status = 'pending';

-- Compare with actual count
SELECT COUNT(*) FROM orders WHERE status = 'pending';
SELECT COUNT(*) FROM orders;

إذا قدر المخطط 8,000 صفًا لكن هناك فقط 80، فإن ذلك مشكلة في البيانات. اعمل على ANALYZE orders; وأعد التحقق من المخطط. هذا يحل مشاكل المخطط بشكل أسرع من إضافة مؤشرات.

قراءة الإخراج المُصاغ كصيغة JSON

يمكن لـ PostgreSQL إخراج المخطط كصيغة JSON، مما يسهل تحليله بشكل تلقائي أو استكشافه في عرض شجرة:

EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

الإخراج JSON مكثف. إذا قمت بوضعه في IO Tools’ JSON Formatter، يصبح شجرة قابلة للتنقل — مفيد عند التعامل مع مخططات عميقة من استعلامات معقدة تحتوي على مُستندات متعددة أو CTEs.

مُسار عملي لتصحيح الأخطاء

عندما يكون الاستعلام بطيئًا، اتبع هذا التسلسل بدل التخمين:

1. قبل إضافة أي فهرس. تأكد من وجود المشكلة أولاً. EXPLAIN (ANALYZE, BUFFERS) على الاستعلام الدقيق مع معلمات مماثلة للإنتاج (بدلاً من $1 مُستندات — استخدم القيم الفعلية).
2. ابحث عن العقد الأعلى تكلفة. ابحث عن أعلى actual time، وليس أعلى تقدير التكلفة. قد تختلف.
3. مُقارنة بين عدد الصفوف المُقدر والواقع في تلك العقدة. فرق 10 مرة أو أكثر يعني أن المخطط كان يعتمد على معلومات خاطئة. ابدأ بتشغيل ANALYZE <table>; أولًا.
4. ابحث عن مسح تسلسلي على جداول كبيرة مع تصفية مُختارة. Rows Removed by Filter: <large number> مباشرة تحت مسح تسلسلي هو مرشح المؤشر.
5. تحقق من عقدة المُدمج لوجود Batches > 1. إذا وُجد، فإن الترابط انتهى إلى القرص. زد work_mem للمُستند في تلك الجلسة واعمل على إعادة الاختبار.
6. تحقق من مسح مُتداخل مع عددين مرتفعًا. عدد مُتداخل في الآلاف يعني أن مسح الجانب الداخلي يُعاني من ضغط. عادةً ما يُحل هذا بمؤشر على عمود الترابط في الجدول الداخلي.

قبل إضافة أي مؤشرات جديدة، اعمل على تمرير الاستعلامات عبر IO Tools’ SQL Formatter — الاستعلامات القابلة للقراءة أسهل في التحليل، وغالبًا ما تُحل المشكلة بالكامل ببساطة.

فهم المخطط هو الخطوة الأولى، وليس المؤشر. بمجرد أن تتمكن من قراءة إخراج EXPLAIN، ستفقد وقت التخمين وستستثمر وقتًا أكبر في تغييرات موجهة تُعمل فعليًا.

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