PostgreSQL / المتوسط: التزامن وSQL القابلة للصيانة / الدرس 8
الفهارس وخطط EXPLAIN
اختر فهرساً لبحث محدد وميز تقدير الخطة عن التنفيذ المقاس، ووازن فائدة القراءة بكلفة الكتابة والصيانة.
في هذه الصفحة
أهداف الدرس
- ربط ترتيب B-tree والانتقائية والتغطية.
- تفسير EXPLAIN وEXPLAIN ANALYZE بوعي.
الفهارس مسارات وصول تحتاج صيانة
يخزن B-tree مفاتيح مرتبة تشير إلى صفوف الجدول ولا يرتب heap دائماً بالمفتاح الأساسي. قد يفيد البحث الانتقائي ويكون المسح أرخص لجدول صغير أو تطابق واسع. يزيد كل فهرس المساحة وWAL والكتابة. توفر PostgreSQL أيضاً GIN وGiST وSP-GiST وBRIN وhash؛ اختر وفق المعامل والحمل لا افتراض فهرس عام لكل بحث.
طابق المساواة والمجال والترتيب
لبحث شحنات عميل ضع مساواة customer_id ثم مجال created_at وshipment_id للتعادل. هذا مرشح لا وعد باختيار المحسن. تناسب الأعمدة الفهرس لكن المسح بالفهرس وحده يعتمد على الرؤية والخطة؛ قد تستلزم التحديثات الحديثة فحص heap. راجع عينة ممثلة قبل ادعاء تقليل الوصول.
CREATE INDEX ix_ship_customer_time
ON quay_lab.shipments (customer_id, created_at, shipment_id);
EXPLAIN SELECT shipment_id, created_at FROM quay_lab.shipments
WHERE customer_id = 7 AND created_at >= timestamptz '2026-10-01 00:00+00'
ORDER BY created_at, shipment_id LIMIT 20;لا نختلق خلايا خطة أو نسب زمن، والتعريف توضيح مختبر معزول.
الكلفة ليست زمناً بالمللي ثانية
تعرض EXPLAIN دون ANALYZE تقديرات. الكلفة وحدات نسبية وrows مخرجات العقدة وwidth متوسط الحجم. المسح التسلسلي ليس عيباً تلقائياً وقد يلائم شرطاً واسعاً. اقرأ الشجرة من المسح إلى الربط والفرز والتجميع وميز شرط الفهرس عن التصفية المتبقية. تشمل كلفة العقدة كلفة فروعها فلا تجمع كل المجاميع فتضاعفها.
تنفذ ANALYZE العبارة
تنفذ EXPLAIN (ANALYZE, BUFFERS) الاستعلام وتعرض الصفوف والنشاط. استخدم قراءة محدودة في عينة معزولة؛ حتى SELECT قد تثقل العمل. لا تطبقها عشوائياً على UPDATE أو DELETE لأن التغيير ينفذ. يؤثر القياس والتخزين المخبأ والحلقات، ولا تعني إصابة المخزن عملاً مجانياً أو كل قراءة وصولاً فيزيائياً للقرص.
EXPLAIN (ANALYZE, BUFFERS)
SELECT shipment_id FROM quay_lab.shipments
WHERE customer_id = 7
ORDER BY created_at, shipment_id LIMIT 20;المتوقع خطة تنفيذ تعتمد صفوفها وحلقاتها وأوقاتها على العينة؛ لم نقسها هنا.
للفهارس الجزئية والتعبيرية شروط
يحفظ الفهرس الجزئي صفوف شرط كالمعلق فقط، ويجب أن يستلزم الاستعلام الشرط؛ يهم ذلك للمعاملات والخطط العامة. قد يخدم فهرس تعبيري lower(label) بمعنى مساواة مقصود. تضيف PostgreSQL 18 تخطي B-tree لبعض الحالات متعددة الأعمدة ولا تلغي تصميم ترتيب الأعمدة. اختبر الإصدار والحمل قبل الاعتماد على التحويلات.
الأخطاء الشائعة وإصلاحها
يُعتبر المسح التسلسلي فشلاً تلقائياً
- لماذا يحدث؟
- حُكم على اسم لا عمل وانتقائية.
- كيف تصلحه؟
- قارن العمل المقدر والفعلي ببيانات واقعية.
- كيف تتجنب تكراره؟
- ضمّن الجداول الصغيرة والتطابق الواسع في التقييم.
تغير EXPLAIN ANALYZE البيانات خلاف المتوقع
- لماذا يحدث؟
- استُخدم تحليل منفذ على عبارة تغيير.
- كيف تصلحه؟
- استخدم حد التعافي المراجع وانقل التشخيص لعينة آمنة.
- كيف تتجنب تكراره؟
- ميز التقدير والتنفيذ في إجراءات التشغيل.
يجلب المسح بالفهرس صفوف heap
- لماذا يحدث؟
- لا تكفي خريطة الرؤية الحالية لفحص الظهور.
- كيف تصلحه؟
- افحص جلب heap وسياق الصيانة قبل الفهرسة.
- كيف تتجنب تكراره؟
- ضمّن التحديث والتنظيف في اختبار التغطية.
اختبر فهمك
1. تقدر عقدة عشرة صفوف ويخرج التنفيذ عشرة آلاف لكل حلقة. ما الدليل المطلوب؟
اعرض الحل المشروح
سجل الحلقات والشروط والمعاملات والتوزيع وافحص حداثة الإحصاءات والترابط. اجمع العمل وفق تعريف المقاييس. قد يخفي فرض فهرس العرض لمعامل ويضر آخر. اختبر تغييراً مبرراً واحفظ عقد النتيجة.
2. لماذا قد لا يحسن فهرس لبحث عشرين صفاً تقريراً يقرأ معظم جدول كبير؟
اعرض الحل المشروح
تختلف متطلبات البحث الانتقائي عن المسح الواسع. قد يكلف جلب صفوف متناثرة أكثر من المسح ويظل الفهرس عبئاً على الكتابة. قيم الحمل والتوزيع؛ لا تثبت تجربة صغيرة فائدة الإنتاج أو تبرر الفهارس الزائدة.
أهم ما تعلمته
- اقرأ التقديرات والحلقات والصفوف والمخازن كمقاييس مختلفة.
- يجب أن يشمل التصميم الكتابة والرؤية وسلوك الإصدار.