طَبِّقْهاتعلّم المبدأ، وطبّقه بيدكمقرر 122 · برمجة قواعد البيانات
الدرس 27 · الوحدة 12: تنفيذ أمثلة عملية لتحسين استعلامات البيانات

مشروع: من استعلام بطيء إلى سريع

تقرير بطيء على 20 ألف طلب: نقيسه ونشخّصه ونسرّعه خطوة خطوة. ⏱ نحو 60 دقيقة.

بعد هذا الدرس ستستطيع أن:
  • تطبق منهجية تحسين الأداء: قِس، اقرأ، أعد الكتابة، فهرِس، قِس.
  • تقرأ EXPLAIN ANALYZE وتكتشف SubPlan و loops.
  • تحوّل الاستعلام المترابط إلى ربط وتجميع.
  • تختار فهرسًا يناسب شرط الاستعلام.
جرّب بنفسك أسئلة التقييم

١المهمة

مدير المبيعات يشتكي: «تقرير الطلبات المدفوعة لكل مدينة في 2025 بطيء». البيانات: 3000 عميل و 20 ألف طلب و 40 ألف بند (قاعدة متجر الريان: بيانات كبيرة). سنعمل كما يعمل مهندس الأداء، في خمس خطوات:

الخطوةالسؤال
1. قِسكم يستغرق الآن؟ لا تحسين بلا رقم قبله وبعده
2. اقرأ الخطةأين يذهب الوقت؟
3. أعد الكتابةهل يمكن وصف النتيجة بأمر مجموعات واحد؟
4. اجعل الشرط قابلًا للفهرسةهل على العمود دالة تمنع الفهرس؟
5. أضف الفهرس المناسب وقِسهل تحسّن الرقم فعلًا؟

٢1 و 2: القياس وقراءة الخطة

هذا هو التقرير الحالي. شغّله ولاحظ ⏱:

query.sql
SELECT c.city,  (SELECT count(*) FROM orders o JOIN customers c2 ON c2.id = o.customer_id    WHERE c2.city = c.city AND o.status = 'paid'      AND extract(year FROM o.order_date) = 2025) AS paid_ordersFROM (SELECT DISTINCT city FROM customers) cORDER BY paid_orders DESC, c.city;
النتيجة · 8 صفوف
citypaid_orders
الدمام338
المدينة337
أبها326
تبوك326
جدة326
حائل326
الرياض325
مكة325

EXPLAIN ANALYZE ينفّذ الاستعلام فعلًا ويعرض الخطة مع الأزمنة الحقيقية وعدد مرات تنفيذ كل عقدة (loops):

query.sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)SELECT c.city,  (SELECT count(*) FROM orders o JOIN customers c2 ON c2.id = o.customer_id    WHERE c2.city = c.city AND o.status = 'paid'      AND extract(year FROM o.order_date) = 2025) AS paid_ordersFROM (SELECT DISTINCT city FROM customers) cORDER BY paid_orders DESC, c.city;
خطة التنفيذ (EXPLAIN)
  • Sort (actual rows=8.00 loops=1)
  • Sort Key: ((SubPlan 1)) DESC, c.city
  • Sort Method: quicksort Memory: 17kB
  • Buffers: shared hit=1245
  • -> Subquery Scan on c (actual rows=8.00 loops=1)
  • Buffers: shared hit=1245
  • -> HashAggregate (actual rows=8.00 loops=1)
  • Group Key: customers.city
  • Batches: 1 Memory Usage: 32kB
  • Buffers: shared hit=29
  • -> Seq Scan on customers (actual rows=3000.00 loops=1)
  • Buffers: shared hit=29
  • SubPlan 1
  • -> Aggregate (actual rows=1.00 loops=8)
  • Buffers: shared hit=1216
  • -> Hash Join (actual rows=328.62 loops=8)
  • Hash Cond: (o.customer_id = c2.id)
  • Buffers: shared hit=1216
  • -> Seq Scan on orders o (actual rows=2629.00 loops=8)
  • Filter: ((status = 'paid'::text) AND (EXTRACT(year FROM order_date) = '2025'::numeric))
  • Rows Removed by Filter: 17371
  • Buffers: shared hit=984
  • -> Hash (actual rows=375.00 loops=8)
  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
  • Buffers: shared hit=232
  • -> Seq Scan on customers c2 (actual rows=375.00 loops=8)
  • Filter: (city = c.city)
  • Rows Removed by Filter: 2625
  • Buffers: shared hit=232
  • Planning:
  • Buffers: shared hit=73
  • Planning Time: 0.555 ms
  • Execution Time: 26.220 ms

ابحث عن SubPlan و loops=8: الاستعلام الفرعي المترابط يُنفَّذ مرة لكل مدينة، وفي كل مرة يمسح جدول الطلبات كاملًا (Seq Scan) ويربطه بالعملاء.

٣3 و 4: إعادة الكتابة والشرط القابل للفهرسة

المشكلتان: (أ) استعلام فرعي مترابط = حلقة مخفية، و(ب) extract(year FROM order_date) دالة على العمود تمنع أي فهرس (الدرس 6). الحل: ربط وتجميع مرة واحدة، ومدى تاريخ صريح:

query.sql
SELECT c.city, count(*) AS paid_ordersFROM orders o JOIN customers c ON c.id = o.customer_idWHERE o.status = 'paid'  AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'GROUP BY c.cityORDER BY paid_orders DESC, c.city;
النتيجة · 8 صفوف
citypaid_orders
الدمام338
المدينة337
أبها326
تبوك326
جدة326
حائل326
الرياض325
مكة325

النتيجة نفسها تمامًا. قارن ⏱ مع النسخة الأولى.

٤5: الفهرس المناسب والقياس الأخير

الاستعلام يبحث في الطلبات المدفوعة ضمن مدى تواريخ. الفهرس الجزئي على التاريخ للمدفوعة فقط يخدمه بأصغر حجم (الدرس 13):

query.sql
CREATE INDEX ix_paid_date ON orders (order_date) WHERE status = 'paid';ANALYZE orders; EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)SELECT c.city, count(*) AS paid_ordersFROM orders o JOIN customers c ON c.id = o.customer_idWHERE o.status = 'paid'  AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'GROUP BY c.cityORDER BY paid_orders DESC, c.city;
✓ أُنشئ الفهرس
✓ حُدّثت الإحصاءات
خطة التنفيذ (EXPLAIN)
  • Sort (actual rows=8.00 loops=1)
  • Sort Key: (count(*)) DESC, c.city
  • Sort Method: quicksort Memory: 17kB
  • Buffers: shared hit=133 read=5
  • -> HashAggregate (actual rows=8.00 loops=1)
  • Group Key: c.city
  • Batches: 1 Memory Usage: 32kB
  • Buffers: shared hit=133 read=5
  • -> Hash Join (actual rows=2629.00 loops=1)
  • Hash Cond: (o.customer_id = c.id)
  • Buffers: shared hit=133 read=5
  • -> Bitmap Heap Scan on orders o (actual rows=2629.00 loops=1)
  • Recheck Cond: ((order_date >= '2025-01-01'::date) AND (order_date < '2026-01-01'::date) AND (status = 'paid'::text))
  • Heap Blocks: exact=104
  • Buffers: shared hit=104 read=5
  • -> Bitmap Index Scan on ix_paid_date (actual rows=2629.00 loops=1)
  • Index Cond: ((order_date >= '2025-01-01'::date) AND (order_date < '2026-01-01'::date))
  • Index Searches: 1
  • Buffers: shared read=5
  • -> Hash (actual rows=3000.00 loops=1)
  • Buckets: 4096 Batches: 1 Memory Usage: 135kB
  • Buffers: shared hit=29
  • -> Seq Scan on customers c (actual rows=3000.00 loops=1)
  • Buffers: shared hit=29
  • Planning:
  • Buffers: shared hit=63 read=1
  • Planning Time: 0.698 ms
  • Execution Time: 2.752 ms

الخطة الآن تقرأ الطلبات المدفوعة في 2025 من الفهرس، وتربطها بالعملاء مرة واحدة (Hash Join).

النسخةما تغيّر
الأصليةاستعلام فرعي مترابط لكل مدينة + extract على العمود
بعد إعادة الكتابةربط وتجميع واحد + مدى تواريخ
بعد الفهرسقراءة الجزء المطلوب فقط

توقّع وتحقّق

ما الفرق بين EXPLAIN و EXPLAIN ANALYZE؟

loops=8 تحت SubPlan تعني:

extract(year FROM d) = 2025 يُستبدل بـ:

أول خطوة في تحسين الأداء:

الخلاصة

تحدٍّ برمجي: سرّع تقرير الإيراد

متقدم

هذا التقرير يحسب لكل حالة طلب عدد الطلبات وإيرادها في الربع الثالث من 2025 (يوليو إلى سبتمبر)، لكنه بطيء. أعد كتابته بالنتيجة نفسها.

المطلوب:
  • النتيجة نفسها بالترتيب نفسه.
  • بلا استعلامات فرعية داخل SELECT.
  • بلا دوال على order_date (لا extract ولا date_part ولا to_char).
  • أضف فهرسًا مناسبًا على orders.
👀 اعرض النتيجة المطلوبة
النتيجة المطلوبة
statusordersrevenue
cancelled8262024478
new9952437188
paid8322045100
shipped8322046896
✓ التحقق الآلي من حلّك:
  • الكود يعمل بلا أخطاء
  • النتيجة نفسها بالترتيب نفسه
  • بلا استعلام فرعي داخل SELECT
  • بلا دوال على order_date
  • فهرس على orders

💡 تلميح 1
ربط orders مع order_items ثم GROUP BY o.status.
💡 تلميح 2
عدد الطلبات بعد الربط بالبنود: count(DISTINCT o.id) لا count(*).
💡 تلميح 3
الربع الثالث: order_date >= '2025-07-01' AND order_date < '2025-10-01'.

حاول بنفسك أولًا؛ للمسألة أكثر من حلّ صحيح.

solution.sql
CREATE INDEX ix_orders_date ON orders (order_date); SELECT o.status, count(DISTINCT o.id) AS orders, sum(i.qty * i.unit_price) AS revenueFROM orders o JOIN order_items i ON i.order_id = o.idWHERE o.order_date >= '2025-07-01' AND o.order_date < '2025-10-01'GROUP BY o.statusORDER BY o.status;
انتقل إلى تقييم الدرساختيار من متعدد، وصح وخطأ، وأكمل الفراغ، وتتبّع الناتج.ابدأ التقييم ←

📝 معلم؟ أرسل ملاحظة للمؤلف عن هذا الدرس