نمایه سازی و بهینه سازی پرس و جو

1) اهداف نمایه سازی و بهینه سازی

تاخیر: کاهش P50/P95/P99.
بهره وری: رشد QPS بدون مقیاس کردن.
پیش بینی: برنامه های پایدار و بدون «جهش» در زمان پاسخ.
صرفه جویی: IO/CPU کمتر، صورتحساب ابر کمتر.
قابلیت اطمینان: کاهش قفل ها و بن بست ها به دلیل دسترسی صحیح.

• هر گونه بهینه سازی باید صحت و سازگاری را حفظ کند.
• پیگیری اثر در معیارها و سیاهههای مربوط به برنامه.

2) ساختارهای شاخص اصلی و زمانی که آنها را اعمال کنید

2. 1 درخت بی (به طور پیش فرض)

Equals/ranges, sort, «ترتیب بر اساس».
مناسب برای اکثر فیلترهای زمان/شناسه/وضعیت.

2. 2 هش

تساوی خالص ('=')، ارزانتر در حافظه اما خارج از ترتیب (PG: محدودیت حذف شده اما هنوز هم انتخاب طاقچه).

2. 3 GIN/GiST (PostgreSQL)

GIN: آرایه ها/کلید های JSONB، متن کامل (tsvector)، containment ('@>').
GiST: جغرافیایی، محدوده، kNN.

2. 4 برین (PostgreSQL)

شاخص فوق العاده ارزان بر اساس جداول «به طور طبیعی مرتب شده» (اضافه کردن تنها با زمان). خوب برای سری زمان با جداول بزرگ.

2. 5 بیت مپ (MySQL/InnoDB: هیچ بومی ؛ DW-DBMS/OLAP)

موثر برای cardinality کم و جنبه، اغلب در ذخیره سازی ستون.

2. 6 شاخص ستون (کلیک هاوس)

کلید اصلی + پرش داده ها (minmax)، через ثانویه «جست و خیز شاخص ها» (شکوفه، مجموعه).
نمایش داده شد OLAP با تجمع و محدوده.

2. 7 شاخص های معکوس (Elasticsearch/OpenSearch)

متن کامل، چهره، جستجو ترکیبی. برای فیلترهای دقیق، از فیلدهای کلمه کلیدی و مقادیر doc استفاده کنید.

2. 8 MongoDB

تک، ترکیب، multikey (آرایه)، جزئی، TTL، متن، هش (برای sharding کلید یکنواخت).

3) طراحی شاخص کلیدی و کامپوزیت

3. 1 قانون پیشوند چپ

ترتیب فیلدها در فهرست، قابلیت استفاده را تعیین می کند.
پرسوجو در کجا tenant_id =? و created_at> =? ORDER BY DESC → '( ، DESC، ID DESC)'.

3. 2 کراوات شکن

اضافه کردن یک دم منحصر به فرد (معمولا «id») برای مرتب سازی پایدار و به دنبال صفحه بندی.

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/لیست)

چرخش احزاب توسط زمان (روزانه/هفتگی) ساده «خلاء/حذف».
Indexes پارتیشن های محلی هستند، کوچکتر از B-Tree، برنامه سریعتر.

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

4. 2 کلید پارتیشن بندی

در OLTP - توسط 'tenant _ id' (محلی سازی بار).
در سری زمانی/OLAP - توسط 't' (نمایش داده شد محدوده).
ترکیبی: '(tenant_id، ts)' + احزاب فرعی.

4. 3 شاردینگ

هش کردن مداوم/محدوده قطعه توسط 'tenant _ id' یا با زمان.
Cross-shard query → spatter-gather and k-way merge; نشانگر هر شارد را نگه دارید.

5) آمار، کاردینالیتی و برنامه ها

5. 1 آمار به روز

فعال کردن خودکار تجزیه و تحلیل ('autovacuum/autoanalyze')، افزایش 'default _ statistics _ target' برای توزیع کثیف.

5. 2 آمار پیشرفته (PG)

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

5. 3 برنامه اجرایی

• 'Rows', 'Loops', 'Real time', 'Shared Read/Hit', 'Recheck Cond'.
• Типы ملحق شوید: Nested Loop، Hash Join، Merge Join.
• Seq Scan در مقابل Index Scan/فقط اسکن/بیت مپ Heap Scan.

5. ۴ ثبات برنامهها

Parameterization (اظهارات آماده) می تواند «چوب» در یک طرح بد است. استفاده از طرح guardrails کش (PG: 'plan _ cache _ mode = force_custom_plan' برای نمایش داده شد مشکل) و یا «حمل و نقل» ثابت.

6) بهینه سازی پیوست ها و انواع

6. 1 استراتژی ها

حلقه تو در تو: کوچک خارجی، شاخص سریع در داخلی.
Hash Join: مجموعه های بزرگ، حافظه کافی برای جدول هش.
ادغام عضویت: ورودی های مرتب شده، سودمند در نظم در حال حاضر در دسترس است.

6. 2 شاخص در حال پیوستن

برای 'A JOIN B ON B.a_id = A.id', → شاخص به 'B (a_id)'.
برای فیلتر پس از پیوستن - شاخص در ستون های فیلتر جدول داخلی.

6. 3 تریاژ

اجتناب از «سفارش توسط» بدون شاخص مربوطه ؛ مرتب سازی بر روی مجموعه های بزرگ توسط حافظه/دیسک گران است.

7) بازنویسی پرس و جو

خلاص شدن از «دانه های برف» subqueries ؛ گسترش دهید.
از خطوط پیش فرض CTE-inline (PG ≥12 CTE استفاده کنید، اما «MATERIALIZED» می تواند در صورت لزوم یک نتیجه متوسط ایجاد کند).
«SELECT» → لیست زمینه ها (صرفه جویی در IO/شبکه) را حذف کنید.
انتقال محاسبات از «WHERE» به فرم نمایه شده (ستونهای از پیش محاسبه شده).
Aggregations: جداول خلاصه اولیه/نمایش های تحقق یافته با به روز رسانی افزایشی.

8) Butching، محدود کردن و صفحه بندی

دسته درج/به روز رسانی: 500-5000 دسته به جای یک به یک.
به دنبال صفحه بندی توسط «(sort_key، id)» به جای عمیق «OFFSET».
محدود کردن شماره گیری قبل از مرتب سازی/joyne (فشار پایین 'LIMIT').

9) ذخیره سازی و خنثی سازی

Application-level query-cache (key = SQL + bind-vars + rights version).
دیدگاه های مادی برای مصالح سنگین ؛ چرخش/برنامه مرجع.
Denormalization - فروشگاه اغلب به عنوان خوانده شده زمینه های محاسبه شده (قیمت از جمله تخفیف) اما با ماشه/کار پس زمینه برای ثبات.
Redis به عنوان L2 برای کلید های داغ (با TTL و ناتوانی رویداد).

10) مشخصات موتورهای محبوب

10. 1 PostgreSQL

Индексы: B-درخت، هش، GIN/GiST، برین، جزئی، عملکردی، شامل.

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

آمار سابقهنما) "جدول تحلیل... به روز رسانی هیستوگرام 'в 8. 0).

10. 3 کلیک خانه

کلید اصلی = مرتب سازی ؛ 'سفارش توسط (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 } } });

برای تشخیص از «hint ()» استفاده کنید، برای «covered query» تماشا کنید.

10. 5 الستیک سرچ/جستجوی باز

زمینه های کلیدی در مقابل متن ؛ doc_values برای مرتب سازی/جمع آوری.
تقسیم بندی پشته: تجمع - سنگین ؛ محدود کردن «اندازه» و استفاده از تجمع «composite» (صفحه بندی).
آنالایزرها را شامل نمی شود که در آن یک مقایسه دقیق لازم است.

11) رقابت، interlocks و MVCC

معاملات کوتاه مدت ؛ اجتناب از «طولانی» خواندن تحت «تکرار خواندن» بدون نیاز.
عملیات شاخص نیز قفل (نوشتن کاهش توان) را.
نمایه سازی آنلاین برنامه: «CREATE INDEX CONCRETELY» (PG)، «ALGORITHM = INPLACE »/« ONLINE» (MySQL).
درج در دم برای یک ساعت/شناسه → «صفحات داغ» شاخص ؛ توزیع کلید (UUIDv7/salt).

12) قابلیت مشاهده و SLO

• 'db _ query _ latency _ ms' (P50/P95/P99) با نام پرس و جو.
• 'rows _ examined', 'rows _ returned', 'buffer _ hit _ ratio'.
• 'deadlocks', 'lock _ wait _ ms', 'temp _ sort _ disk _ usage'.
• سهم برنامه ها با «Seq Scan» که در آن «Index Scan» انتظار می رود.
• هشدارهای رگرسیون هنگام تغییر نسخه/پارامترهای DBMS.

• فعالسازی ثبت پرسوجوی آهسته با یک آستانه) برای مثال، 200 ms (.
• ارتباط پرس و جو با دهانه (trace_id).
• حذف برنامه های پرس و جو مشکل و صرفه جویی در ذخیره سازی شی برای گذشته نگر.

• «<= 150 ms» P95 با «LIMIT <= 50» و مستاجر گرم بخوانید.
• P95 «<= 200 ms» را با دسته هایی تا 1000 خط ضبط می کند.

13) ایمنی و چند اجاره ای

شاخص های مربوط به زمینه های کنترل دسترسی («tenant _ id»، «owner _ id») مورد نیاز است.
سیاست ها (RLS/ABAC) باید قبل از فیلتر باشد ؛ در غیر این صورت، بهینه ساز نادرست برنامه ریزی می کند.
فیلدهای حساس را در متن واضح فهرست نکنید ؛ از هش ها/نشانه ها استفاده کنید.

14) ضد الگوهای

عمیق 'OFFSET' بدون جایگزین جستجوگر مکان نما.
«یک شاخص برای همه» - اضافه بار حافظه و مسیر نوشتن.
'SELECT' در مسیرهای بحرانی.
توابع بالای ستون در 'WHERE' بدون شاخص تابع.
برنامه های ناپایدار به دلیل آمار قدیمی.
گم شدن سفارش توسط در حالی که منتظر سفارش پایدار است.
شاخص ها به خاطر شاخص ها: ROI <0 به دلیل نوشتن/پشتیبانی گران قیمت.

15) چک لیست پیاده سازی

1. درخواست های بالا N توسط QPS و زمان → 3-5 نامزد را انتخاب کنید.
2. حذف برنامه های 'توضیح تجزیه و تحلیل، بررسی cardinality در مقابل واقعی است.
3. شاخص های طراحی: نظم میدان، INCLUDE/جزئی/عملکردی.
4. پیاده سازی پارتیشن بندی برای جداول بزرگ (کلید موقت/مستاجر).
5. بازنویسی نمایش داده شد: حذف 'SELECT', درون خطی CTEs ساده, محدود کردن مجموعه.
6. فعال کردن butching و به دنبال صفحه بندی.
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 اجتناب کنید: نمونه های حریص («شامل»، «JOIN FETCH»، «پیش بارگذاری»).
پیش بینی های صریح زمینه، صفحه بندی توسط مکان نما.
gRPC/REST: محدود کردن 'page _ size'، رفع 'sort'، استفاده از نشانه های مبهم.
کش برنامه: استفاده از پارامتری ؛ SQL «منحصر به فرد» را در هر تماس ایجاد نکنید.

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

اضافه کردن شاخص های آنلاین و علامت گذاری به عنوان INVISIBLE/CONCURRENT، برنامه های تست، و سپس تغییر دهید.
تجدید نظر در فهرست - تمیز کردن بهداشتی منظم: تکراری، استفاده نشده، «مرده» برای ویژگی های قدیمی.
برنامه چرخش حزب (رها کردن قدیمی) و برنامه «VACUUM/OPTIMIZE».

19) خلاصه

بهینه سازی پرس و جو مهندسی سیستم است: کلید ها و شاخص های صحیح، برنامه های شسته و رفته، پارتیشن بندی و تقسیم متفکر، نظم و انضباط در پرس و جو و ORM، ذخیره و مشاهده پذیری. با پیروی از الگوهای توصیف شده، شما یک سیستم سریع، قابل پیش بینی و اقتصادی خواهید داشت که در برابر رشد و بارگذاری داده ها مقاوم است.