الدرس 9 · الوحدة 4: تصميم جداول البيانات بالعلاقات والفهارس

الفهارس في التصميم: متى ولماذا؟

الفهرس قرار تصميم: أين يسرّع القراءة، وأين يكلّفك أكثر مما ينفع؟ ⏱ نحو 35 دقيقة.

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

تشرح فكرة الفهرس وبنية B-tree.
تحدد الأعمدة المرشحة للفهرسة، وتعرف أن المفاتيح الأجنبية لا تُفهرس تلقائيًّا.
تقدّر الانتقائية وأثرها في استخدام الفهرس.
توازن بين مكسب الفهرس في القراءة وثمنه في الكتابة والمساحة.

١ما الفهرس؟

تخيّل كتابًا من 500 صفحة تبحث فيه عن كلمة «المعاملة». بلا فهرس تقرأ الصفحات كلها (Seq Scan). ومع فهرس آخر الكتاب، المرتب أبجديًّا، تقفز إلى الكلمة ثم إلى أرقام صفحاتها مباشرة.

فهرس قاعدة البيانات مثله: بنية مرتبة منفصلة عن الجدول. أشهر أنواعه B-tree، شجرة متوازنة تصل إلى أي قيمة في خطوات قليلة حتى مع ملايين الصفوف:

فهرس B-tree: من الجذر إلى الورقة في خطوات قليلة، والأوراق تشير إلى مواقع الصفوف في الجدول.

٢أي الأعمدة تستحق فهرسًا؟

العمود	هل يُفهرس؟	السبب
المفتاح الأساسي	تلقائيًّا	PostgreSQL ينشئ فهرسًا فريدًا له
أعمدة UNIQUE	تلقائيًّا	القيد يُنفّذ بفهرس فريد
المفاتيح الأجنبية	يدويًّا غالبًا	PostgreSQL لا يفهرسها تلقائيًّا، والربط والحذف من الجدول الأصل يحتاجانها
أعمدة WHERE المتكررة	نعم	مثل البحث بالبريد أو الحالة
أعمدة ORDER BY و JOIN	غالبًا	الفهرس مرتب أصلًا
عمود قيمه قليلة جدًّا	غالبًا لا	مثل عمود منطقي `true/false`: الانتقائية ضعيفة

لنتأكد مما ينشئه PostgreSQL وحده. جدول النظام pg_indexes يعرض كل فهارس القاعدة:

query.sql

SELECT tablename, indexnameFROM pg_indexesWHERE schemaname = 'public'ORDER BY tablename, indexname;

النتيجة · 7 صفوف

tablename	indexname
categories	categories_name_key
categories	categories_pkey
customers	customers_email_key
customers	customers_pkey
order_items	order_items_pkey
orders	orders_pkey
products	products_pkey

فهارس المفاتيح الأساسية و UNIQUE فقط. لا فهرس على orders.customer_id ولا products.category_id رغم أنهما مفتاحان أجنبيان.

٣الانتقائية وثمن الفهرس

الانتقائية Selectivity: كم صفًّا يعيده الشرط من الجدول؟ كلما قلّت النسبة كان الفهرس أنفع. حالة الطلب لها 4 قيم فقط، فشرطها يعيد ربع الجدول تقريبًا:

query.sql

SELECT status, count(*),       round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS النسبةFROM orders GROUP BY status ORDER BY status;

النتيجة · 4 صفوف

status	count	النسبة
cancelled	4760	23.8
new	5714	28.6
paid	4765	23.8
shipped	4761	23.8

لعمود كهذا يفضّل المخطِّط المسح الكامل غالبًا حتى لو وُجد فهرس. أما customer_id (3000 قيمة) فانتقائيته عالية.

ما يكسبه الفهرس	ما يكلّفه
قراءة أسرع بكثير للشروط الانتقائية	كل INSERT و UPDATE و DELETE يحدّث الفهرس أيضًا: كتابة أبطأ
ترتيب جاهز لـ ORDER BY	مساحة إضافية على القرص
ربط أسرع على المفاتيح الأجنبية	فهارس زائدة يتجاهلها المخطِّط ونتحمل تكلفتها

توقّع وتحقّق

أشهر أنواع الفهارس:

أي عمود ينشئ له PostgreSQL فهرسًا تلقائيًّا؟

ما ثمن كثرة الفهارس؟

عمود الجنس (قيمتان) انتقائيته:

الخلاصة

الفهرس بنية مرتبة منفصلة، و B-tree تصل إلى أي قيمة في خطوات قليلة.
المفتاح الأساسي و UNIQUE مفهرسان تلقائيًّا؛ المفتاح الأجنبي لا.
الفهرس ينفع الشروط الانتقائية (نسبة صغيرة من الصفوف).
كل فهرس يبطئ الكتابة ويأخذ مساحة: فهرس بقدر الحاجة.

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

متوسط

قاعدة المتجر فيها أربعة مفاتيح أجنبية بلا فهارس. أنشئ فهرسًا لكل منها، ثم اعرض فهارس الجداول.

المطلوب:

فهارس على: products(category_id) و orders(customer_id) و order_items(product_id).
المفتاح الرابع order_items(order_id) أول عمود في المفتاح المركّب، فهو مفهرس بالفعل: لا تكرره.
أنهِ الكود باستعلام يعرض أسماء الفهارس من pg_indexes.

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

النتيجة المطلوبة

tablename	indexname
categories	categories_name_key
categories	categories_pkey
customers	customers_email_key
customers	customers_pkey
order_items	idx_items_product
order_items	order_items_pkey
orders	idx_orders_customer
orders	orders_pkey
products	idx_products_category
products	products_pkey

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

الكود يعمل بلا أخطاء
فهرس على products(category_id)
فهرس على orders(customer_id)
فهرس على order_items(product_id)
بلا فهرس مكرر على order_items(order_id)
يعرض الفهارس من pg_indexes

💡 تلميح 1

الصيغة: CREATE INDEX idx_orders_customer ON orders (customer_id);

💡 تلميح 2

سمِّ الفهرس باسم يدل على جدوله وعموده.

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

solution.sql

CREATE INDEX idx_products_category ON products (category_id);CREATE INDEX idx_orders_customer   ON orders (customer_id);CREATE INDEX idx_items_product     ON order_items (product_id); SELECT tablename, indexname FROM pg_indexes WHERE schemaname = 'public' ORDER BY 1, 2;

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

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