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

1) أهداف الفهرسة والتحسين

الكمون: تخفيض P50/P95/P99.
الإنتاجية: نمو QPS دون توسيع.
القدرة على التنبؤ: خطط مستقرة ولا «قفزات» في وقت الاستجابة.
المدخرات: أقل IO/CPU، أقل فاتورة سحابية.
الموثوقية: تقليل الأقفال والمآزق بسبب الوصول الصحيح.

• يجب أن يحافظ أي تحسين على الصواب والاتساق.
• تتبع التأثير في المقاييس وخطط السجلات.

2) هياكل المؤشر الأساسية ومتى يتم تطبيقها

2. 1 B-Tree (افتراضي)

يساوي/نطاقات، فرز، «ترتيب حسب».
جيد لمعظم الوقت/مرشحات الهوية/الحالة.

2. 2 هاش

المساواة النقية ('=')، أرخص في الذاكرة ولكن خارج النظام (PG: تمت إزالة القيود ولكن لا يزال الاختيار متخصصًا).

2. 3 GIN/GiST (PostgreSQL)

GIN: مصفوفات/مفاتيح JSONB، النص الكامل (tsvector)، الاحتواء ('@>').
GiST: geo، ranges، kNN.

2. 4 برين (PostgreSQL)

مؤشر رخيص للغاية حسب الجداول «المصنفة بشكل طبيعي» (مذيلة فقط حسب الوقت). جيد لسلسلة زمنية مع طاولات كبيرة.

2. 5 Bitmap (MySQL/InnoDB: لا أحد من السكان الأصليين ؛ DW-DBMS/OLAP)

فعالة لانخفاض الكاردينالية والأوجه، في كثير من الأحيان في تخزين الأعمدة.

2. 6 مؤشرات العمود (ClickHouse)

المفتاح الأساسي + تخطي البيانات (minmax)، через الثانوي «تخطي الفهارس» (ازدهار، مجموعة).
ويتساءل المكتب عن التجميعات والنطاقات.

2. 7 فهارس مقلوبة (Elasticsearch/OpenSearch)

النص الكامل، الجوانب، البحث الهجين. للحصول على مرشحات دقيقة، استخدم حقول الكلمات الرئيسية وقيم المستند.

2. 8 MongoDB

مفردة، مركبة، متعددة المفاتيح (مصفوفات)، جزئية، TTL، نص، مجزأة (لشحن مفتاح موحد).

3) تصميم الفهرس الرئيسي والمركب

3. 1 قاعدة البادئة اليسرى

يحدد ترتيب الحقول في المؤشر قابلية الاستخدام.
استفسار "أين tenant_id = ؟ و created_at> = ؟ أمر من created_at DESC '→ индекс' (tenant_id, created_at DESC, id DESC) '.

3. 2 كسر التعادل

أضف ذيلًا فريدًا (عادةً «هوية») للفرز المستقر وابحث عن التثبيت.

3. 3 مؤشرات جزئية/مصفاة

sql
CREATE INDEX idx_orders_paid_recent
ON orders (created_at DESC, id DESC)
WHERE status = 'paid' AND created_at > now() - interval '90 days';

3. 4 مؤشرات تغطي

sql
CREATE INDEX idx_user_lastseen_inc ON users (tenant_id, last_seen DESC) INCLUDE (email, plan);

3. 5 وظيفية/محسوبة

sql
CREATE INDEX idx_norm_email ON users (lower(email));

4) التقسيم والشحن

4. التقسيم 1 (الميراث الأصلي PG/الجدول ؛ MySQL RANGE/LIST)

تناوب الأطراف حسب الوقت («يومي/أسبوعي») يبسط «فراغ/حذف».
الفهارس هي أقسام محلية → أصغر من B-Tree، خطة أسرع.

sql
CREATE TABLE events (
tenant_id bigint,
ts timestamptz,
...
) PARTITION BY RANGE (ts);

4. 2 مفتاح التقسيم

في OLTP - حسب «المستأجر _ id» (توطين الحمل).
في السلسلة الزمنية/OLAP - بواسطة ts' (استفسارات النطاق).
الهجين: '(tenant_id، ts) + الأطراف الفرعية.

4. 3 الشحن

التجزئة المتسقة/قطعة المدى بواسطة «مستأجر _ هوية» أو حسب الوقت.
الاستعلام عن القطع المتقاطعة → التجمع المتناثر والدمج في اتجاه الكيلومتر ؛ امسك المؤشر لكل قطعة.

5) الإحصاءات والكاردينية والخطط

5. 1 - أحدث الإحصاءات

تمكين التحليل التلقائي («autovacuuum/autoanalyze»)، زيادة «الافتراضي _ الإحصائيات _ الهدف» للتوزيعات القذرة.

5. 2 الإحصاءات المتقدمة (PG)

sql
CREATE STATISTICS stat_user_country_city (dependencies) ON country, city FROM users;
ANALYZE users;

5. 3 خطة التنفيذ

• «Rows'،» Loops'، «الوقت الفعلي»، «Shared Read/Hit'،» Recheck Cond'.
• Типы الانضمام: Nested Loop، Hash Join، Merge Join.
• Seq Scan vs Index Scan/Only Scan/Bitmap Heap Scan.

5. 4 استقرار الخطط

يمكن لـ parameterization (البيانات المعدة) «التمسك» بخطة سيئة. استخدم حاجز حماية مخبأ الخطة (PG: 'plan _ cache _ mode = force_custom_plan' للاستفسارات عن المشكلة) أو ثوابت «إعادة التوجيه».

6) تحسين عمليات الانضمام والأنواع

6. 1 الاستراتيجيات

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

6. 2 الفهارس قيد الانضمام

للحصول على "A GONE B ON B.a_id = A.Id'، → المؤشر إلى" B (a_id) ".
للفلتر بعد الانضمام - الفهرس الموجود على أعمدة مرشح الجدول الداخلي.

6. 3 فرز

تجنب «ترتيب حسب» بدون مؤشر مقابل ؛ الفرز على مجموعات كبيرة مكلف حسب الذاكرة/القرص.

7) إعادة كتابة الاستعلام

تخلص من «رقاقات الثلج» من الاستفسارات الفرعية ؛ التوسع في الانضمام.
استخدم CTE-inline (خطوط PG ≥12 CTE الافتراضية، ولكن «MATERIALIZED» يمكن أن ترتكب نتيجة وسيطة إذا لزم الأمر).
حذف 'SELECT' → قائمة الحقول (مدخرات IO/network).
نقل الحسابات من 'WHERE' إلى النموذج المفهرس (أعمدة محسوبة مسبقا).
التجميعات: الجداول الموجزة الأولية/الآراء المجسدة مع استكمال تدريجي.

8) الجزخ والحد والتجميع

إدخال الدفعة/التحديث: 500-5000 دفعة بدلاً من واحدة تلو الأخرى.
ابحث عن الوثب بواسطة «(sort_key، id)» بدلاً من «OFFSET» العميق.
حد من الاتصال قبل الفرز/الفرح (الضغط لأسفل «الحد»).

9) التخزين المؤقت وإزالة الطابع الطبيعي

Application-level query-cache (key = SQL + bind-vars + rights version).
وجهات نظر ملموسة بشأن التجميعات الثقيلة ؛ خطة التناوب/المرجع.
إلغاء التطبيع - قم بتخزين الحقول المحسوبة بشكل متكرر (السعر بما في ذلك الخصم) ولكن مع مهمة التشغيل/الخلفية من أجل الاتساق.
Redis as L2 للمفاتيح الساخنة (مع TTL وإعاقة الحدث).

10) تفاصيل المحركات الشعبية

10. 1 PostgreSQL

Индексы: B-Tree, Hash, GIN/GiST, BRIN, partial, functional, INCLUSION.

sql
CREATE INDEX idx_orders_tenant_created_desc
ON orders (tenant_id, created_at DESC, id DESC)
INCLUDE (amount, status);

sql
CREATE INDEX idx_docs_fts ON docs USING GIN (to_tsvector('russian', title          ' '          body));

10. 2 MySQL/InnoDB

sql
ALTER TABLE orders ALTER INDEX idx_old INVISIBLE; -- check risk-free plans

Histogram statistics ('ANALYZE TABLE... تحديث в HISTOGRAM 8. 0).

10. 3 ClickHouse

المفتاح الأساسي = النوع ؛ «طلب من قبل (tenant_id، ts، id)».

sql
CREATE TABLE events (
tenant_id UInt64,
ts DateTime64,
id UInt64,
payload String,
INDEX idx_bloom_payload payload TYPE bloom_filter GRANULARITY 4
) ENGINE = MergeTree()
ORDER BY (tenant_id, ts, id);

10. 4 MongoDB

js db. orders. createIndex({ tenant_id: 1, created_at: -1, _id: -1 });
db. orders. createIndex({ status: 1 }, { partialFilterExpression: { archived: { $ne: true } } });

استخدم «تلميح ()» للتشخيص، راقب «الاستعلام المغطى».

10. 5 Elasticsearch/OpenSearch

الكلمات الرئيسية مقابل الحقول النصية ؛ doc_values للفرز/التجميع.
تجزئة الأكوام: التجميعات - الثقيلة ؛ تقييد «الحجم» واستخدام التجميعات «المباشرة» (استدعاء).
لا تشمل المحللين حيث يلزم إجراء مقارنة دقيقة.

11) القدرة التنافسية والتشابكات ولجنة متطوعي الأمم المتحدة

المعاملات القصيرة ؛ تجنب القراءات «الطويلة» تحت «قراءة قابلة للتكرار» بلا داع.
تأخذ عمليات المؤشر أيضًا أقفالًا (كتابة تخفيض الإنتاجية).
خطة الفهرسة عبر الإنترنت: «إنشاء مؤشر ملموس» (PG)، «ALGORITHM = INPLACE »/« ONLINE» (MySQL).
تدرج في الذيل لمدة ساعة/هوية → «صفحات ساخنة» من الفهرس ؛ توزيع المفتاح (UUIDv7/الملح).

12) إمكانية الرصد و SLO

• 'db _ query _ latency _ ms' (P50/P95/P99) باسم الاستعلام.
• "rows _ tested", "rows _ return'," buffer _ hit _ rato ".
• «deadlocks'،» lock _ wait _ ms'، «temp _ sort _ disk _ usage».
• حصة الخطط مع «Seq Scan» حيث كان من المتوقع «Index Scan».
• تنبيهات الانحدار عند تغيير نسخة/بارامترات DBMS.

• تمكين سجل الاستعلام البطيء مع عتبة (على سبيل المثال، 200 مللي ثانية).
• ترابط الاستفسارات مع الامتدادات (trace_id).
• قم بإزالة خطط الاستعلام عن المشكلة وحفظها للتخزين الكائن لأثر رجعي.

• اقرأ P95 «<= 150 مللي ثانية» مع «الحد الأقصى <= 50» والمستأجر الساخن.
• سجلات P95 «<= 200 مللي ثانية» مع دفعات تصل إلى 1000 خط.

13) السلامة وتعدد الحيازات

ويلزم وضع فهارس لحقول مراقبة الدخول ('المستأجر - الهوية'، 'المالك - الهوية').
يجب أن تكون السياسات (RLS/ABAC) مرشحة مسبقًا ؛ خلاف ذلك، يخطط الأفضل بشكل غير صحيح.
لا تفهرس المجالات الحساسة بنص واضح ؛ استخدام التجزئة/الرموز.

14) الأنماط المضادة

«تعويض» عميق بدون بديل البحث عن المؤشر.
«مؤشر واحد للجميع» - التحميل الزائد للذاكرة ومسار الكتابة.
«حدد» في المسارات الحرجة.
الوظائف فوق العمود في 'WHERE' بدون فهرس الدالة.
خطط غير مستقرة بسبب الإحصائيات القديمة.
مفقود «طلب من قبل» أثناء انتظار النظام المستقر.
الفهارس من أجل الفهارس: عائد الاستثمار <0 بسبب الكتابة/الدعم باهظ الثمن.

15) قائمة التنفيذ المرجعية

1. أعلى طلبات N من QPS والوقت → اختيار 3-5 مرشحين.
2. قم بإزالة الخطط «اشرح التحليل»، وتحقق من الكاردينالية مقابل الفعلية.
3. فهارس التصميم: الترتيب الميداني، INCLUSION/partial/functional.
4. تنفيذ تقسيم الجداول الكبيرة (مفاتيح مؤقتة/مستأجرة).
5. الكتابة الفوقية الاستفسارات: إزالة «SELECT' SELECT»، inline simple CTEs، مجموعة التقييد.
6. قم بتمكين التثبيت والبحث عن التثبيت.
7. مخبأ التهيئة: L1/L2، الإعاقة حسب الأحداث.
8. إدخال مراقبة الخطط والسجل البطيء، وتنبيهات الانحدار.
9. إجراء اختبارات التحميل مع توزيع البيانات الحقيقي.
10. تحديث المبادئ التوجيهية لتطوير (تلميحات ORM، الفهرسة، الحدود).

16) قبل/بعد الأمثلة

sql
SELECT FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;

sql
-- Индекс: (status, created_at DESC, id DESC) INCLUDE (amount, currency)
SELECT id, amount, currency, created_at
FROM orders
WHERE status = 'paid'
AND (created_at, id) < (:last_ts,:last_id)   -- seek
ORDER BY created_at DESC, id DESC
LIMIT 50;

17) بروتوكولات ORM و API

تجنب N + 1: عينات جشعة («تشمل»، «انضم إلى FETCH»، «التحميل المسبق»).
إسقاطات ميدانية صريحة، تستعد بالمؤشر.
gRPC/REST: حد «الصفحة _ الحجم»، إصلاح «الفرز»، استخدام الرموز المميزة غير الشفافة.
مخبأ الخطة: استخدام البارامترات ؛ لا تولد SQL «فريد» لكل مكالمة.

18) الهجرات والعمليات

أضف الفهارس عبر الإنترنت وقم بوضع علامة على INVISIBLE/CONCURRENT، واختبار الخطط، ثم التبديل.
تنقيحات الفهرس - التنظيف الصحي المنتظم: نسخ مكررة، غير مستخدمة، «ميتة» للميزات القديمة.
خطة تناوب الأطراف (إسقاط قديم) وجدول «فراغ/تحسين».

19) موجز

تحسين الاستعلام هو هندسة الأنظمة: المفاتيح والفهارس الصحيحة، والخطط الأنيقة، والتقسيم والتشظي المدروس، والانضباط في الاستفسارات و ORM، والتخزين المؤقت وقابلية الملاحظة. من خلال اتباع الأنماط الموصوفة، ستحصل على نظام سريع واقتصادي يمكن التنبؤ به ومقاوم لنمو البيانات وتحميلها.