الدرس 13 · الوحدة 6: تحسين أداء جداول البيانات وإنشاء الفهارس

إنشاء الفهارس وإدارتها

فهرس واحد مصمم جيدًا يغني عن ثلاثة فهارس عشوائية. ⏱ نحو 40 دقيقة.

بعد هذا الدرس ستستطيع أن:

تنشئ فهرسًا مركّبًا وترتّب أعمدته حسب الاستعلام.
تستخدم الفهرس الفريد والجزئي وعلى تعبير.
تستخدم INCLUDE ليجيب الفهرس وحده.
تدير الفهارس: حجمها وحذفها وإعادة بنائها.

١الفهرس المركّب وترتيب أعمدته

الاستعلام المتكرر «طلبات عميل معيّن مرتبة بالتاريخ» يستفيد من فهرس على عمودين. وترتيب العمودين مهم: الفهرس المركّب مثل دليل الهاتف المرتب بالعائلة ثم الاسم، ينفع البحث بالعائلة، ولا ينفع البحث بالاسم الأول وحده.

query.sql

CREATE INDEX ix_orders_cust_date ON orders (customer_id, order_date); EXPLAIN SELECT id, order_date FROM ordersWHERE customer_id = 42 ORDER BY order_date DESC LIMIT 5;

✓ أُنشئ الفهرس

خطة التنفيذ (EXPLAIN)

Limit (cost=0.29..23.23 rows=5 width=8)
-> Index Scan Backward using ix_orders_cust_date on orders (cost=0.29..32.41 rows=7 width=8)
Index Cond: (customer_id = 42)

«آخر 5 طلبات للعميل 42»: الفهرس يجد طلباته ويقدّمها مرتبة بالتاريخ أصلًا، فيقرؤه المحرك بالعكس (Index Scan Backward) ويتوقف بعد 5 صفوف، بلا عقدة Sort.

query.sql

CREATE INDEX ix_orders_cust_date ON orders (customer_id, order_date); EXPLAIN SELECT id FROM orders WHERE order_date = '2025-09-01';

✓ أُنشئ الفهرس

خطة التنفيذ (EXPLAIN)

Seq Scan on orders (cost=0.00..373.00 rows=42 width=4)
Filter: (order_date = '2025-09-01'::date)

الشرط على العمود الثاني وحده: الفهرس لا يفيد، و Seq Scan.

٢أنواع خاصة من الفهارس

النوع	الصيغة	متى؟
فريد	`CREATE UNIQUE INDEX … (email)`	يمنع التكرار مثل قيد UNIQUE
جزئي Partial	`CREATE INDEX … (order_date) WHERE status = 'new'`	تبحث دائمًا في جزء صغير من الجدول: أصغر وأسرع
على تعبير	`CREATE INDEX … (lower(email))`	الشرط يستخدم دالة على العمود (الدرس 6)
تغطية INCLUDE	`CREATE INDEX … (customer_id) INCLUDE (status)`	الفهرس يحمل أعمدة إضافية فيجيب وحده

الطلبات الجديدة التي تنتظر المعالجة جزء صغير من الجدول، ولوحة الموظفين تسأل عنها كل دقيقة. الفهرس الجزئي يفهرسها وحدها:

query.sql

CREATE INDEX ix_orders_new ON orders (order_date) WHERE status = 'new'; EXPLAIN SELECT id, order_date FROM ordersWHERE status = 'new' AND order_date >= '2025-09-20';

✓ أُنشئ الفهرس

خطة التنفيذ (EXPLAIN)

Bitmap Heap Scan on orders (cost=4.72..106.42 rows=57 width=8)
Recheck Cond: ((order_date >= '2025-09-20'::date) AND (status = 'new'::text))
-> Bitmap Index Scan on ix_orders_new (cost=0.00..4.71 rows=57 width=0)
Index Cond: (order_date >= '2025-09-20'::date)

و INCLUDE يجعل الفهرس يحمل عمودًا إضافيًّا ليجيب دون زيارة الجدول (Index Only Scan):

query.sql

CREATE INDEX ix_orders_cust_inc ON orders (customer_id) INCLUDE (status);VACUUM ANALYZE orders; EXPLAIN SELECT customer_id, status FROM orders WHERE customer_id = 42;

✓ أُنشئ الفهرس

✓ حُدّثت الإحصاءات

خطة التنفيذ (EXPLAIN)

Index Only Scan using ix_orders_cust_inc on orders (cost=0.29..4.41 rows=7 width=10)
Index Cond: (customer_id = 42)

بعد VACUUM يعرف المحرك أن صفحات الجدول نظيفة، فيكتفي بالفهرس.

٣إدارة الفهارس

query.sql

CREATE INDEX ix_orders_customer ON orders (customer_id); SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) AS الحجمFROM pg_indexes WHERE tablename = 'orders' ORDER BY 1;

✓ أُنشئ الفهرس

النتيجة · صفّان

indexname	الحجم
ix_orders_customer	232 kB
orders_pkey	456 kB

العملية	الأمر
حذف فهرس	`DROP INDEX ix_name;`
إعادة بناء فهرس منتفخ	`REINDEX INDEX ix_name;`
إنشاء دون إيقاف الكتابة (خادم حي)	`CREATE INDEX CONCURRENTLY …`

توقّع وتحقّق

فهرس (customer_id, order_date) يفيد أيًّا من الشروط؟

الفهرس الجزئي:

ما الذي يتيح Index Only Scan؟

لإنشاء فهرس على جدول حي دون إيقاف الكتابة:

الخلاصة

المركّب: عمود المساواة أولًا ثم المدى أو الترتيب.
الجزئي يفهرس صفوف شرط معيّن فقط: أصغر وأسرع.
INCLUDE يتيح Index Only Scan.
DROP INDEX للحذف، و REINDEX لإعادة البناء، و CONCURRENTLY على الخوادم الحية.

تحدٍّ برمجي: فهرس لوحة الموظفين

متقدم

لوحة الموظفين تعرض كل دقيقة الطلبات المدفوعة لعميل معيّن من الأحدث للأقدم: WHERE status = 'paid' AND customer_id = … ORDER BY order_date DESC. صمّم فهرسًا واحدًا يخدمها بأقل حجم.

المطلوب:

فهرس جزئي على الطلبات المدفوعة فقط.
أعمدته: customer_id ثم order_date.
الخطة تستخدم فهرسك.

👀 اعرض الناتج المطلوب

خطة التنفيذ (EXPLAIN)

Sort (cost=11.57..11.58 rows=2 width=8)
Sort Key: order_date DESC
-> Bitmap Heap Scan on orders (cost=4.30..11.56 rows=2 width=8)
Recheck Cond: ((customer_id = 42) AND (status = 'paid'::text))
-> Bitmap Index Scan on ix_paid_cust_date (cost=0.00..4.30 rows=2 width=0)
Index Cond: (customer_id = 42)

✓ التحقق الآلي من حلّك:

الكود يعمل بلا أخطاء
فهرس جزئي على الطلبات المدفوعة
الخطة تستخدم الفهرس

💡 تلميح 1

الصيغة: CREATE INDEX اسم ON orders (customer_id, order_date) WHERE status = 'paid';

💡 تلميح 2

لماذا قد ترى عقدة Sort؟ لأن طلبات العميل قليلة، فيجلبها المحرك ثم يرتبها، وهذا أرخص.

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

solution.sql

CREATE INDEX ix_paid_cust_date ON orders (customer_id, order_date) WHERE status = 'paid'; EXPLAIN SELECT id, order_date FROM ordersWHERE status = 'paid' AND customer_id = 42ORDER BY order_date DESC;

انتقل إلى تقييم الدرساختيار من متعدد، وصح وخطأ، وأكمل الفراغ، وتتبّع الناتج.ابدأ التقييم ←

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