Индексатсия ва оптимизатсияи дархостҳо

1) Ҳадафҳои индексатсия ва оптимизатсия

Таъхир: P50/P95/P99 коҳиш.
Гузариш: афзоиши QPS бидуни миқёс.
Пешгӯӣ: нақшаҳои устувор ва дар вақти посух "ҷаҳида" нестанд.
Сарфа: Камтар IO/CPU, векселҳои абрӣ.
Эътимоднокӣ: кам кардани қуфлҳо ва монеаҳо аз сабаби дастрасии дуруст.

• Ҳама гуна оптимизатсия бояд дурустӣ ва пайдарҳамиро нигоҳ дорад.
• Таъсирро дар ченакҳо пайгирӣ кунед ва гузоришҳоро ба нақша гиред.

2) Сохторҳои асосии индексатсия ва кай ба кор бурдани онҳо

2. 1 B-дарахт (пешфарз)

Баробарӣ/диапазонҳо, навъ, 'Фармоиш аз ҷониби'.
Филтрҳои аксари вақт/ID/status хуб аст.

2. 2 Ҳаш

Баробарии пок ('='), дар хотира арзонтар аст, аммо фармоиш нест (PG: маҳдудиятҳо бартараф карда шуданд, аммо интихоби чароғак).

2. 3 GIN/Gi

GIN: массивҳо/калидҳои JSONB, матни пурра (tsvector), нигоҳдорӣ ('@>').
GIST: гео, диапазон, KNN.

2. 4 BRIN (PostgrE SQL)

Индекси супер арзон аз рӯи ҷадвалҳои "табиатан мураттабшуда" (танҳо вақт илова карда мешавад). Хуб барои силсилаи вақт бо мизҳои калон.

2. 5 Bitmap (My DW-DBMS/OLAP)

Самаранок барои кардинализм ва паҳлӯҳои паст, бештар дар нигаҳдории сутун.

2. 6 Индекси сутунҳо (Click-House)

Калиди ибтидоӣ + партофтани маълумот (minmax), черези дуюмдараҷа 'индексатсияҳои гузаред' (балоғат, маҷмӯа).
Дархостҳои OLAP бо агрегатҳо ва диапазонҳо.

2. 7 Индекси баръакс (Elasticsearch/Open

Матни пурра, паҳлӯҳо, ҷустуҷӯи гибридӣ. Барои филтрҳои дақиқ, майдонҳои калимаҳои калидӣ ва арзишҳои doc -ро истифода баред.

2. 8 Mongo-DB

Ягона, мураккаб, мултикей (массивҳо), қисман, TTL, матн, hashed (барои якранг кардани калидҳои ягона).

3) Тарҳи шохиси калидӣ ва таркибӣ

3. 1 Қоидаи префикси чап

Тартиби майдонҳо дар шохис қобили истифода будани онро муайян мекунад.
Пурсиш 'WHERE tenant_id =? ВА created_at> =? Фармоиш аз ҷониби created_at DESC '→ indeks' (tenant_id, created_at 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; RANGE/LIST)

Ротатсияи тарафҳо бо мурури замон ('ҳаррӯза/ҳафтаина') 'VACUUM/DELETE' -ро содда мекунад.
Индексҳо қисмҳои маҳаллӣ § аз B-Tree хурдтар мебошанд, нақшаи тезтар.

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

4. 2 Калиди тақсимкунӣ

Дар OLTP - аз ҷониби 'иҷорагир _ ид' (маҳаллисозии сарборӣ).
Дар силсилаи вақт/OLAP - аз ҷониби 'ts' (дархостҳои диапазон).
Гибрид: '(tenant_id, ts)' + зерқисматҳо.

4. 3 Шарманда

Хэшинг/диапазони пайваста аз ҷониби 'иҷорагир _ ид' ё бо мурури замон.
Пурсиши кросс-шард → якҷоя кардани пароканда ва k-way; курсори ҳар як шардро нигоҳ доред.

5) Омор, кардинал ва нақшаҳо

5. 1 Омори муосир

Фаъол кардани таҳлили худкор ('autovacuum/autoanalyze'), зиёд кардани 'пешфарз _ омор _ ҳадаф' барои тақсимоти ифлос.

5. 2 Омори пешрафта (PG)

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

5. 3 Нақшаи иҷро

• 'Сатрҳо', 'Ҳалқаҳо', 'Вақти воқеӣ', 'Хондани муштарак/Хит', 'Recheck Cond'.
• Типы ҳамроҳ: ҳалқаи лона, ҳамроҳ шудан ба ҳаш, ҳамроҳ шудан.
• Seq Scan vs Index Scan/Танҳо скан/Bitmap Heap Scan.

5. 4 Устувории нақшаҳо

Параметри (изҳороти омодашуда) метавонад ба нақшаи бад "часпад". Нақшаи посбонҳои кэшро истифода баред (PG: 'plan _ cache _ mode = force_custom_plan' барои дархостҳои мушкилот) ё "фиристодани" константҳо.

6) Оптимизатсияи узвҳо ва навъҳо

6. 1 Стратегияҳо

Давраи ҷойгиршуда: индекси хурди беруна, зуд дар дохили.
Ҳамроҳ кардани Hash: маҷмӯаҳои калон, хотираи кофӣ барои ҷадвали hash.
Якҷояшавӣ: вурудоти мураттабшуда, дар тартиботе, ки аллакай дастрас аст, муфид аст.

6. 2 Индексҳо дар якҷоягӣ

Барои 'A JOIN B ON B.a_id = A.id', → индекс ба 'B (a_id)'.
Барои филтр баъди ҳамроҳ шудан - индекс дар сутунҳои филтри ҷадвали дохилӣ.

6. 3 Сегона

Аз индекси мувофиқ дурӣ ҷӯед; навъбандӣ дар маҷмӯаҳои калон аз ҷониби хотира/диск гарон аст.

7) Навиштани дархост

Аз "барфҳои барфӣ" -и зергурӯҳҳо халос шавед; васеъ дар JOIN.
CTE-inline-ро истифода баред (PG ≥ 12 CTE хатҳои пешфарз, аммо 'MATERIALIZED' метавонад дар ҳолати зарурӣ натиҷаи мобайнӣ диҳад).
Хориҷ кардани 'SELECT' → рӯйхати майдонҳо (пасандозҳои IO/шабака).
Гузаронидани ҳисобҳо аз 'WHERE' ба шакли индексатсияшуда (сутунҳои пешакӣ ҳисобшуда).
Маҷмӯаҳо: ҷадвалҳои пешакии ҷамъбастӣ/назари моддӣ бо навсозии афзоянда.

8) Буттинг, маҳдуд ва пагинатсия

Бастабандӣ/навсозӣ: 500-5000 партия ба ҷои як ба як.
Ба ҷои чуқури 'OFFSET' аз ҷониби '(sort_key, id) саҳифабандӣ ҷустуҷӯ кунед.
Пеш аз навъбандӣ/joyne рақамро маҳдуд кунед (push-down 'LIMIT').

9) Кэшкунӣ ва denormalization

Сатҳи дархост-кэш (калид = SQL + bind-vars + версияи ҳуқуқ).
Андешаҳои моддӣ барои агрегатҳои вазнин; ротатсия/нақшаи истинод.
Denormalization - Мағозаҳо майдонҳои ҳисобшударо зуд-зуд мехонанд (нарх, аз ҷумла тахфиф), аммо бо вазифаи триггер/замина барои мувофиқат.
Redis ҳамчун L2 барои калидҳои гарм (бо TTL ва маъюбии ҳодиса).

10) Хусусиятҳои муҳаррикҳои маъмул

10. 1 Postgre- SQL

Индексы: B-Tree, Hash, GIN/GIST, BRIN, қисман, функсионалӣ, INFEREND.

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 Ман SQL/InnodB

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

Омори гистограмма ('Ҷадвали таҳлилӣ... UPDATE HISTOGRAM 'v 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 Монго- ДБ

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

Барои ташхис 'ишора ()' -ро истифода баред, 'дархости пӯшида' -ро тамошо кунед.

10. 5 Elasticsearch/Ҷустуҷӯи Open

Keyword vs майдонҳои матнӣ; doc_values барои навъбандӣ/агрегатҳо.
Тақсимоти теппа: агрегатҳо - вазнин; маҷмӯаҳои 'андоза' ва истифодаи 'composite' (пейджинг) -ро маҳдуд кунед.
Таҳлилгаронро дар бар нагиред, ки муқоисаи дақиқ лозим аст.

11) Рақобат, ҳамбастагӣ ва MVCC

Амалиётҳои кӯтоҳ; аз хондани "дароз" дар зери 'REPATABLE READ' худдорӣ кунед.
Амалиёти индексатсия инчунин қуфлҳоро мегирад (кам кардани интиқоли навиштан).
Индексатсияи онлайнро ба нақша гиред: 'CREATE INDEX CONCRETELY' (PG), 'ALGORITHM = INPLACE '/' ONLINE' (My 'SQL).
Дар дум барои як соат/ID → "саҳифаҳои гарм" -и шохис дохил карда мешавад; калид тақсим кунед (UUID .v7/намак).

12) Мушоҳида ва SLO

• 'db _ query _ latency _ ms' (P50/P95/P99) бо номи дархост.
• 'сатрҳо _ санҷида мешаванд', 'сатрҳо баргаштанд', 'buffer _ hit _ таносуб'.
• 'deadlocks', 'lock _ wait _ ms', 'temp _ sort _ disk _ usage'.
• Ҳиссаи нақшаҳо бо 'Seq Scan' were 'Index Scan' интизор буд.
• Огоҳии регрессия ҳангоми тағир додани версия/параметрҳои DBMS.

• Фаъол кардани сабти дархости суст бо остона (масалан, 200 мс).
• Таносуби дархостҳо бо фосила (trace_id).
• Нақшаҳои дархости мушкилотро нест кунед ва барои нигоҳдории объект барои ретроспективӣ захира кунед.

• P95 '<= 150 ms' -ро бо 'LIMIT <= 50' ва иҷорагири гарм хонед.
• P95 сабти '<= 200 ms' бо маҷмӯаҳо то 1000 сатр.

13) Бехатарӣ ва бисёрҳуҷрагӣ

Индексҳо дар майдонҳои назорати дастрасӣ ('иҷорагир _ ид', 'соҳиби _ id') талаб карда мешаванд.
Сиёсатҳо (RLS/ABAC) бояд пеш аз филтр бошанд; дар акси ҳол, оптимизатор нодуруст нақша мегирад.
Майдонҳои ҳассосро дар матни тоза индексатсия накунед; hashes/токенҳоро истифода баред.

14) Анти-намунаҳо

Deep 'OFFSET' бидуни алтернатива-курсор.
"Як шохис барои ҳама" - изофабори хотира ва роҳи навиштан.
'Роҳҳои интиқодӣ интихоб кунед.
Функсияҳои болои сутун дар 'WHERE' бе индекси функсия.
Нақшаҳои ноустувор аз сабаби омори кӯҳна.
Бедарак 'Фармоиш BY' ҳангоми интизории тартиботи устувор.
Индексҳо ба хотири индексатсияҳо: ROI <0 бо сабаби навиштан/дастгирии гаронбаҳо.

15) Рӯйхати назорати амалисозӣ

1. Дархостҳои Top N аз ҷониби QPS ва вақт → 3-5 номзадро интихоб кунед.
2. Нақшаҳои 'FAILSON ANALIZE' -ро хориҷ кунед, кардинализмро санҷед.
3. Индексҳои тарроҳӣ: тартиби саҳроӣ, INSIDE/қисман/функсионалӣ.
4. Амалисозии тақсимот барои ҷадвалҳои калон (калидҳои муваққатӣ/иҷорагир).
5. Бознависии дархостҳо: хориҷ кардани 'SELECT', CTE-ҳои оддӣ, маҷмӯи маҳдуд.
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', 'preload').
Пешгӯиҳои саҳроӣ, пагинат бо курсор.
GRPC/REST: 'page _ size' -ро маҳдуд кунед, 'sort' -ро ислоҳ кунед, нишонаҳои ношаффофро истифода баред.
Нақшаи кэш: параметризатсияро истифода баред; дар як занг SQL "беназир" тавлид накунед.

18) Муҳоҷират ва амалиёт

Индексҳоро онлайн илова кунед ва ҳамчун INVISIBLE/CONCURRENT нишон диҳед, нақшаҳои санҷиш ва баъд гузаред.
Таҷдиди индексатсия - тозакунии мунтазами санитарӣ: нусхабардорӣ, истифоданашуда, "мурда" барои хусусиятҳои кӯҳна.
Нақшаи гардиши ҳизбҳо (кӯҳна) ва ҷадвали 'VACUUM/OPTIMIZE'.

19) Хулоса

Оптимизатсияи дархостҳо муҳандисии системаҳо мебошад: калидҳои дуруст ва индексатсияҳо, нақшаҳои тозаву озода, тақсимот ва тақаллуби оқилона, интизом дар дархостҳо ва ORM, кэш ва мушоҳида. Бо пайравӣ аз намунаҳои тавсифшуда, шумо як системаи зуд, пешгӯишаванда ва сарфакорона мегиред, ки ба афзоиш ва сарбории маълумот тобовар аст.