تحسين استعلام قاعدة البيانات: الفهارس وخطط التنفيذ والتقسيم

يمكن لفهرس واحد مفقود أن يحول استعلامًا مدته 2 مللي ثانية إلى فحص جدول مدته 20 ثانية. ومع نمو قاعدة بياناتك من آلاف إلى ملايين الصفوف، فإن الفرق بين الاستعلام المحسّن وغير المحسّن هو الفرق بين التطبيق سريع الاستجابة والتطبيق الذي تنتهي مهلته تحت التحميل. يوفر تحسين قاعدة البيانات أعلى عائد على الوقت الهندسي مقارنة بأي عمل أداء يمكنك القيام به.

الوجبات الرئيسية

• يعد EXPLAIN ANALYZE أقوى أداة تشخيصية لديك - تعلم قراءة خطط التنفيذ قبل تحسين أي شيء
• اختر أنواع الفهرس بشكل استراتيجي: B-tree للمساواة والنطاق، وGIN للنص الكامل وJSONB، والفهارس الجزئية للمجموعات الفرعية التي تمت تصفيتها
• استعلامات N+1 هي أكثر العوامل القاتلة شيوعًا للأداء في التطبيقات المستندة إلى ORM - اكتشفها مبكرًا من خلال تسجيل الاستعلام
• يصبح تقسيم الجدول ضروريًا عندما تتجاوز الجداول 10-50 مليون صف، مما يقلل من وقت تخطيط الاستعلامات ويتيح إدارة دورة حياة البيانات بكفاءة

---

قراءة خطط التنفيذ مع شرح التحليل

قبل تحسين أي استعلام، يجب عليك فهم كيفية تنفيذ PostgreSQL له حاليًا. يقوم برنامج EXPLAIN ANALYZE بتشغيل الاستعلام وإظهار خطة التنفيذ الفعلية ببيانات التوقيت الحقيقية.

يوضح لك مخرج التحليل الأساسي الاستراتيجية التي اختارها المخطط، وعدد الصفوف المقدرة مقابل الفعلية، والوقت الذي يقضيه في كل خطوة. المقاييس الرئيسية التي يجب التركيز عليها هي:

• Seq Scan - تقرأ قاعدة البيانات كل صف في الجدول. مقبول للجداول الصغيرة (أقل من 10000 صف) ولكنه علامة حمراء للجداول الأكبر حجمًا.
• مسح الفهرس - تستخدم قاعدة البيانات فهرسًا للعثور على الصفوف المطابقة بكفاءة. هذا هو ما تريده للاستعلامات المصفاة على الجداول الكبيرة.
• مسح الفهرس فقط - تجيب قاعدة البيانات على الاستعلام بالكامل من الفهرس دون لمس الجدول. أسرع نوع المسح.
• حلقة متداخلة - لربط الجداول عن طريق مسح الجدول الداخلي مرة واحدة لكل صف في الجدول الخارجي. فعال عندما يستخدم الفحص الداخلي فهرسًا.
• Hash Join -- ينشئ جدول تجزئة من أحد جوانب الصلة، ثم يقوم باستكشافه من الجانب الآخر. فعالة لمجموعات نتائج أكبر.
• الفرز -- خطوة فرز واضحة، غالبًا ما تكون للترتيب حسب. انتبه للأنواع التي تتسرب إلى القرص (يُشار إليها بواسطة "طريقة الفرز: الدمج الخارجي").

ما الذي تبحث عنه

الإشارة الأكثر أهمية في خطة التنفيذ هي الفجوة بين الصفوف المقدرة والفعلية. عندما يقدر PostgreSQL 10 صفوف لكنه يجد 100000، فإنه اختار الخطة الخاطئة. يحدث هذا عندما تكون إحصائيات الجدول قديمة - قم بتشغيل ANALYZE على الجدول لتحديثها.

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

---

أنواع الفهرس ومتى يتم استخدامها

يقدم PostgreSQL عدة أنواع من الفهارس، كل منها مُحسّن لأنماط استعلام مختلفة. يعد اختيار النوع الصحيح أمرًا بالغ الأهمية - فمؤشر GIN الموجود على عمود يحتاج فقط إلى المساواة يتحقق من تخزين النفايات ويبطئ عملية الكتابة دون تحسين عمليات القراءة.

فهارس شجرة B

B-tree هو نوع الفهرس الافتراضي والأكثر تنوعًا. وهو يدعم المساواة (=)، والنطاق (<، >، BETWEEN)، والفرز (ORDER BY)، ومطابقة نمط البادئة (مثل 'abc%'). بالنسبة لمعظم الأعمدة في عبارات WHERE وJOIN وORDER BY، فإن فهرس B-tree هو الاختيار الصحيح.

الفهارس المركبة تجمع أعمدة متعددة في شجرة B واحدة. ترتيب الأعمدة مهم: الفهرس الموجود في (الحالة، create_at) يدعم بكفاءة تصفية الاستعلامات حسب الحالة وحدها أو على كل من الحالة وcreated_at، ولكن ليس على create_at وحدهما. ضع العمود الأكثر انتقائية أولاً والعمود المستخدم لتصفية النطاق أخيرًا.

فهارس الجين

تتفوق فهارس GIN في البحث ضمن القيم المركبة. وهي ضرورية للبحث عن النص الكامل (أعمدة tsvector)، واستعلامات احتواء JSONB (@>، ?)، واستعلامات تداخل المصفوفة (&&، @>). تعد فهارس GIN أكبر وأبطأ في التحديث من فهارس B-tree، لذا استخدمها فقط عندما لا تستطيع B-tree تقديم نمط الاستعلام.

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

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

الفهارس الجزئية تقوم فقط بفهرسة الصفوف المطابقة لشرط WHERE. إنها فعالة بالنسبة للجداول حيث يتم تصفية الاستعلامات باستمرار لمجموعة فرعية صغيرة من البيانات.

على سبيل المثال، إذا كان جدول الطلبات الخاص بك يحتوي على 10 ملايين صف ولكنك تستفسر بشكل حصري تقريبًا عن الطلبات النشطة (5% من الجدول)، فهرس جزئي على (customer_id، create_at) حيث تكون الحالة = "نشط" أصغر بمقدار 20 مرة من الفهرس الكامل وبنفس السرعة لاستفساراتك الفعلية.

---

اكتشاف وإصلاح استعلامات N+1

تعد مشكلة الاستعلام N+1 هي مشكلة الأداء الأكثر شيوعًا في التطبيقات التي تستخدم ORMs. ويحدث ذلك عندما تقوم التعليمات البرمجية بتحميل قائمة من سجلات N، ثم تنفيذ استعلام إضافي واحد لكل سجل لتحميل البيانات ذات الصلة، مما يؤدي إلى إجمالي استعلامات N+1 بدلاً من 1-2.

كيف تحدث استعلامات N+1

فكر في تحميل قائمة الطلبات بأسماء عملائها. يقوم التنفيذ الساذج بتحميل قائمة الطلبات (استعلام واحد)، ثم لكل طلب، يقوم بتحميل العميل (N استعلامات). مع 100 طلب، يؤدي ذلك إلى إنشاء 101 رحلة ذهابًا وإيابًا لقاعدة البيانات. عند 1 مللي ثانية لكل استعلام، يكون ذلك 101 مللي ثانية - ولكن في ظل التحميل المتزامن مع تنافس تجمع الاتصال، يمكن أن يصبح بسهولة 500 مللي ثانية أو أكثر.

طرق الكشف

1. تسجيل الاستعلام - قم بتمكين تسجيل استعلام PostgreSQL مؤقتًا وابحث عن استعلامات متطابقة متكررة بقيم معلمات مختلفة
2. التسجيل على مستوى ORM - يدعم كل من Drizzle ORM وPrisma وTypeORM تسجيل الاستعلام الذي يعرض كل عبارة SQL تم تنفيذها
3. أدوات APM -- يمكن لـ Datadog وNew Relic وSentry تجميع الاستعلامات حسب نقطة النهاية وتمييز أنماط N+1 تلقائيًا
4. pg_stat_statements - يتتبع ملحق PostgreSQL إحصائيات تنفيذ الاستعلام ويكشف عن قوالب الاستعلام المتطابقة التي يتم تنفيذها بشكل متكرر

إصلاح استعلامات N+1

يعتمد الإصلاح على ORM ونمط الاستعلام الخاص بك:

• تحميل حريص - اطلب من ORM تحميل البيانات ذات الصلة في الاستعلام الأولي باستخدام JOINs. في Drizzle، استخدم الخيار with في منشئي الاستعلامات.
• تحميل الدفعة - اجمع كل معرفات المفاتيح الخارجية، ثم قم بتحميل السجلات ذات الصلة في استعلام واحد بعنوان WHERE id IN (...). هذا هو نمط DataLoader.
• إلغاء التسوية -- بالنسبة لحالات الاستخدام كثيفة القراءة، قم بتخزين البيانات ذات الصلة مباشرة في السجل الأصلي. تداول تعقيد الكتابة لأداء القراءة.

---

تقنيات إعادة كتابة الاستعلام

في بعض الأحيان يحتاج الاستعلام نفسه إلى إعادة الهيكلة، وليس فقط إلى فهارس أفضل.

استعلام فرعي لتحويل الانضمام

يتم تنفيذ الاستعلامات الفرعية المرتبطة مرة واحدة لكل صف في الاستعلام الخارجي. يتيح تحويلها إلى JOINs لـ PostgreSQL استخدام إستراتيجيات ربط أكثر كفاءة.

بدلاً من تحديد الطلبات باستخدام استعلام فرعي يبحث عن أحدث تاريخ طلب لكل عميل، أعد كتابته كـ JOIN باستخدام جدول مشتق أو وظيفة نافذة. يسمح إصدار JOIN لـ PostgreSQL بالاختيار بين الحلقة المتداخلة، وربط التجزئة، ودمج الانضمام بناءً على توزيع البيانات.

تعبيرات الجدول الشائعة (CTEs)

في PostgreSQL 12 والإصدارات الأحدث، يتم تضمين CTEs افتراضيًا، مما يعني أن المُحسِّن يمكنه دفع المسندات إليها. استخدم CTEs لسهولة القراءة دون القلق بشأن أسوار الأداء. بالنسبة للحالات التي تريد فيها صراحة التجسيد (لمنع إعادة تنفيذ الاستعلامات الفرعية باهظة الثمن)، قم بإضافة الكلمة الأساسية MATERIALIZED.

وظائف النافذة مقابل GROUP BY

عندما تحتاج إلى صفوف التفاصيل والتجميعات، تتجنب وظائف النافذة الحاجة إلى الانضمام الذاتي أو الاستعلام الفرعي. يعد حساب الإجمالي الجاري أو الترتيب داخل المجموعات أو مقارنة كل صف بمتوسط ​​المجموعة أكثر كفاءة مع وظائف النافذة مقارنة بالاستعلامات الفرعية المرتبطة.

---

استراتيجيات تقسيم الجدول

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

أنواع الأقسام

تقسيم النطاق حسب التاريخ

النمط الأكثر شيوعًا هو التقسيم الشهري حسب عمود الطابع الزمني. توجد بيانات كل شهر في القسم الخاص بها. الاستعلامات التي تتم تصفيتها حسب التاريخ تقوم تلقائيًا بفحص الأقسام ذات الصلة فقط (تقليم الأقسام).

فوائد التقسيم على أساس الوقت:

• أداء الاستعلام - تقوم الاستعلامات الخاصة بالبيانات الحديثة بفحص الأقسام الحديثة فقط
• الصيانة - يعمل الفراغ والتحليل بشكل أسرع على الأقسام الأصغر
• دورة حياة البيانات - يعد إسقاط الأقسام القديمة أمرًا فوريًا مقارنةً بحذف ملايين الصفوف
• كفاءة النسخ الاحتياطي - إجراء نسخ احتياطي للأقسام الحديثة فقط من أجل الاسترداد في الوقت المناسب

اعتبارات التقسيم

يضيف التقسيم التعقيد. يجب أن يتضمن كل استعلام مفتاح القسم في عبارة WHERE الخاصة به لكي يعمل تقليم القسم. يجب أن تتضمن القيود الفريدة مفتاح القسم. المفاتيح الخارجية التي تشير إلى الجداول المقسمة لها قيود. ابدأ التقسيم فقط عندما تقوم بقياس حجم الجدول الذي يتسبب في تدهور الأداء.

---

ضبط تكوين PostgreSQL

يعد تكوين PostgreSQL الافتراضي محافظًا، ومصممًا للعمل على الحد الأدنى من الأجهزة. تستفيد أعباء عمل الإنتاج من ضبط المعلمات الأساسية.

يجب ضبط هذه الإعدادات لتناسب أجهزتك وعبء العمل المحدد لديك. قم بمراقبة pg_stat_bgwriter وpg_stat_activity وpg_stat_user_tables للتحقق من أن التغييرات تعمل على تحسين الأداء.

---

الأسئلة المتداولة

كم عدد الفهارس التي يجب أن يحتوي عليها الجدول؟

لا يوجد حد ثابت، ولكن كل فهرس يبطئ عمليات INSERT وUPDATE وDELETE لأنه يجب الحفاظ على الفهرس. القاعدة الأساسية الجيدة هي إنشاء فهارس للأعمدة التي تظهر في عبارات WHERE وJOIN ON وORDER BY الخاصة باستعلاماتك الأكثر شيوعًا. استخدم pg_stat_user_indexes للعثور على الفهارس غير المستخدمة التي يمكن إسقاطها.

هل يجب علي استخدام UUID أو المفاتيح الأساسية الصحيحة للأداء؟

تعد المفاتيح الأساسية الصحيحة (BIGSERIAL) أسرع في عمليات الانضمام والفهرسة لأنها أصغر (8 بايت مقابل 16 بايت) ومرتبة بشكل طبيعي. توفر UUIDs تفردًا عالميًا بدون تنسيق، وهو أمر مهم بالنسبة للأنظمة الموزعة. بالنسبة لمعظم التطبيقات، استخدم UUIDs للمعرفات الخارجية والأعداد الصحيحة للصلات الداخلية.

متى يجب علي التبديل من قاعدة بيانات واحدة لقراءة النسخ المتماثلة؟

عندما يتجاوز عبء عمل القراءة 70-80% من سعة قاعدة البيانات الخاصة بك، أو عندما تتنافس استعلامات التقارير مع استعلامات المعاملات على الموارد. تتعامل النسخ المتماثلة للقراءة مع حمل القراءة بينما تركز النسخة الأساسية على الكتابة. عادةً ما يكون هذا مطلوبًا من 5000 إلى 10000 مستخدم متزامن لتطبيق ويب نموذجي.

كيف يمكنني التعامل مع الاستعلامات البطيئة في الإنتاج دون توقف؟

قم بإنشاء فهارس باستخدام الخيار "متزامن" لتجنب قفل الجدول. استخدم pg_stat_statements لتحديد أبطأ الاستعلامات. نشر تحسينات الاستعلام خلف علامات الميزات. بالنسبة لتغييرات المخطط التي تعيد كتابة الجداول، استخدم أدوات مثل pg_repack لإعادة تنظيم الجداول دون قفلها.

---

ما هو التالي

تحسين قاعدة البيانات هو أساس أداء النظام الأساسي. ابدأ بتمكين pg_stat_statements، وحدد أبطأ استعلاماتك، وقم بالتعامل معها بشكل منهجي باستخدام EXPLAIN ANALYZE. قم بإضافة الفهارس المفقودة، وأصلح أنماط N+1، وفكر في تقسيم أكبر الجداول لديك.

للحصول على صورة الأداء الأوسع، راجع دليلنا الأساسي حول توسيع نطاق النظام الأساسي لأعمالك من بدء التشغيل إلى المؤسسة. للتعرف على الطبقة التالية من التحسين، اقرأ دليلنا حول استراتيجيات التخزين المؤقت باستخدام التخزين المؤقت لـ Redis وCDN وHTTP.

يوفر ECOSIRE تحسينًا احترافيًا لقاعدة البيانات للأنظمة الأساسية المدعومة من PostgreSQL بما في ذلك Odoo ERP والتطبيقات المخصصة. اتصل بنا لتدقيق أداء قاعدة البيانات.

---

تم النشر بواسطة ECOSIRE — لمساعدة الشركات على التوسع باستخدام الحلول المدعومة بالذكاء الاصطناعي عبر Odoo ERP، وShopify eCommerce، وOpenClaw AI.