Indexarea și optimizarea interogărilor

1) Obiective de indexare și optimizare

Latenţă: reducere a P50/P95/P99.
Transfer: creștere QPS fără scară-out.
Predictibilitate: planuri stabile și fără „salturi” în timpul răspunsului.
Economii: Mai puțin IO/CPU, mai puțină factură cloud.
Fiabilitate: reducerea blocajelor și blocajelor datorită accesului corect.

• Orice optimizare trebuie să mențină corectitudinea și consecvența.
• Urmăriți efectul în valori și jurnalele de planuri.

2) structuri de index de bază și când să le aplice

2. 1 B-Tree (implicit)

Egaluri/intervale, sortare, „ORDINE DE”.
Bun pentru majoritatea filtrelor de timp/ID/stare.

2. 2 Hash

Egalități pure ('='), mai ieftine în memorie, dar în afara ordinii (PG: constrângeri eliminate, dar încă alegere de nișă).

2. 3 GIN/GiST (PostgreSQL)

GIN: matrice/chei JSONB, text complet (tsvector), izolare ('@>').
GiST: geo, intervale, kNN.

2. 4 BRIN (PostgreSQL)

Index super-ieftin prin tabele „sortate natural” (adăugați-numai după timp). Bun pentru seria de timp cu mese mari.

2. 5 Bitmap (MySQL/InnoDB: nici unul nativ; DW-DBMS/OLAP)

Eficient pentru cardinalitate scăzută și fațete, mai des în depozitarea coloanelor.

2. 6 Indicii coloanei (ClickHouse)

Cheie primară + sărind peste date (minmax), через secundar „sări peste indici” (bloom, set).
Interogări OLAP cu agregări și intervale.

2. 7 Indici inversați (Elasticsearch/OpenSearch)

Text complet, faţete, căutare hibridă. Pentru filtre precise, utilizaţi câmpurile de cuvinte cheie şi valorile doc.

2. 8 MongoDB

Single, compus, multikey (matrice), parțială, TTL, text, hashed (pentru sharding cheie uniformă).

3) Design cheie și index compozit

3. 1 Regula prefixului stâng

Ordinea câmpurilor din indice determină gradul de utilizare.
Query 'WHERE tenant_id =? ȘI created_at> =? ORDINUL created_at DESC "→ индекс" (tenant_id, created_at DESC, id DESC) ".

3. 2 Tie-breaker

Adăugați o coadă unică (de obicei "id') pentru sortare stabilă și căutați paginare.

3. 3 Indici parțiali/filtrați

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 Indici de acoperire

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

3. 5 Funcțional/calculat

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

4) Partiționarea și sharding

4. 1 Partiționare (PG moștenire nativă/tabel; MySQL RANGE/LIST)

Rotația partidelor în funcție de timp ('zilnic/săptămânal') simplifică 'VACUUM/DELETE'.
Indexurile sunt partiții locale → mai mici decât B-Tree, plan mai rapid.

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

4. 2 Cheie de partiționare

În OLTP - by 'tenant _ id' (localizare sarcină).
În time-series/OLAP - by 'ts' (interval de interogări).
Hibrid: „(tenant_id, ts)” + subpărți.

4. 3 Sharding

Hashing consistent/range-shard prin "chiriaș _ id' sau la timp.
Interogarea cross-shard → scatter-gather și k-way fuzionează; țineți cursorul per-shard.

5) Statistici, cardinalitate și planuri

5. 1 Statistici actualizate

Activați auto-analiza ('kovacuum/autoanalize'), creșteți 'default _ statistics _ target' pentru distribuții murdare.

5. 2 Statistici avansate (PG)

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

5. 3 Planul de execuție

• 'Rânduri', 'Bucle', 'Timp real', 'Citire/Lovire partajată', 'Verificare din nou'.
• Типы alăturați-vă: Imbricate Loop, Hash Join, Merge Alăturați-vă.
• Scanare Seq vs Scanare index/Numai scanare/scanare Bitmap Heap Scanare.

5. 4 Stabilitatea planurilor

Parametrizarea (instrucțiuni pregătite) poate „lipi” pe un plan rău. Utilizați guardrails cache plan (PG: 'plan _ cache _ mode = force_custom_plan' pentru interogări de probleme) sau „redirecționare” constante.

6) Optimizarea alăturărilor și sortimentelor

6. 1 Strategii

Bucla imbricată: indice extern mic, rapid pe intern.
Hash Join: seturi mari, suficientă memorie pentru masa hash.
Merge Join: intrări sortate, avantajoase în ordinea deja disponibilă.

6. 2 Indici sub se alăture

Pentru 'A JOIN B ON B.a_id = A.id', → indexul la 'B (a_id)'.
Pentru filtrul după alăturare - indexul de pe coloanele filtrului tabelului intern.

6. 3 Triaj

Evitați 'ORDER BY' without un index corespunzător; sortarea pe seturi mari este costisitoare prin memorie/disc.

7) Interogare rescrie

Scapa de „fulgi de zapada” de subintrebari; extinde în JOIN.
Utilizați inline CTE-inline (PG ≥12 CTE implicit, dar „MATERIALIZAT” poate comite un rezultat intermediar, dacă este necesar).
Eliminați 'SELECT' → listați câmpurile (economii IO/rețea).
Transferați calcule de la 'WHERE' la forma indexată (coloane pre-calculate).
Agregări: tabele sumare preliminare/vizualizări materializate cu actualizare incrementală.

8) Butching, limitare și paginare

Lot-insert/update: 500-5000 loturi în loc de unul câte unul.
Căutați paginare prin „(sort_key, id)” în loc de „OFFSET”.
Limitați apelarea înainte de sortare/joyne (push-down' LIMIT ").

9) Caching și denormalizare

Cerere-nivel de interogare-cache (cheie = SQL + bind-vars + versiunea de drepturi).
Vizualizări materializate pentru agregate grele; planul de rotație/referință.
Denormalizare - Stochează câmpuri calculate citite frecvent (preț inclusiv reducere), dar cu sarcină de declanșare/fundal pentru consistență.
Redis ca L2 pentru tastele fierbinți (cu TTL și handicap eveniment).

10) Specificul motoarelor populare

10. 1 PostgreSQL

Индексы: B-Tree, Hash, GIN/GiST, BRIN, parțial, funcțional, INCLUDE.

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

Statistici histogramă ("ANALIZA TABELULUI... ACTUALIZARE HISTOGRAMĂ 'в 8. 0).

10. 3 ClickHouse

Cheie primară = sortare; „ORDIN DE (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 } } });

Utilizați „indiciu ()” pentru diagnosticare, urmăriți „interogare acoperită”.

10. 5 Căutare elastică/OpenSearch

Cuvinte cheie vs câmpuri de text; doc_values pentru sortare/agregate.
Segmentarea Heap: agregări - grele; restricționează „dimensiunea” și utilizează agregatele „composite” (paginare).
Nu includeți analizoare în care este necesară o comparație exactă.

11) Competitivitate, interblocări și MVCC

Tranzacții scurte; evita „lung” citeşte în „REPETABIL CITIT” inutil.
Operațiunile de index iau, de asemenea, încuietori (reducerea debitului de scriere).
Planificați indexarea online: 'CREATE INDEX CONCRETELY' (PG), 'ALGORITHM = INPLACE '/' ONLINE' (MySQL).
Introduceți în coadă timp de o oră/ID → „pagini fierbinți” ale indexului; distribuie cheia (UUIDv7/sare).

12) Observabilitate și SLO

• 'db _ query _ latency _ ms' (P50/P95/P99) după numele interogării.
• 'rows _ examined', 'rows _ returned', 'buffer _ hit _ ratio'.
• 'deadlocks',' lock _ wait _ ms', 'temp _ sort _ disk _ usage'.
• Ponderea planurilor cu 'Seq Scan' unde 'Index Scan' era de așteptat.
• Alerte de regresie la schimbarea versiunii/parametrilor DBMS.

• Activați jurnalul de interogare lent cu un prag (de exemplu, 200 ms).
• Corelarea interogărilor cu intervalele (trace_id).
• Eliminați planurile de interogare a problemelor și salvați pentru stocarea obiectelor pentru retrospectivă.

• Citește P95 '<= 150 ms' with' LIMIT <= 50 'și chiriaș fierbinte.
• P95 înregistrează <= 200 ms 'cu loturi de până la 1000 de linii.

13) Siguranță și multi-chirie

Sunt necesare indexuri pe câmpurile de control acces ('tenant _ id',' owner _ id').
Politicile (RLS/ABAC) trebuie să fie pre-filtru; în caz contrar, planurile de optimizare incorect.
Nu indexați câmpurile sensibile în text clar; utilizați hashes/jetoane.

14) Anti-modele

Deep 'OFFSET' fără a căuta-cursor alternativă.
„Un index pentru toți” - suprasarcină de memorie și scrie-cale.
SELECTAȚI 'în căi critice.
Funcții deasupra coloanei în 'WHERE' fără indice de funcție.
Planuri instabile din cauza statisticilor vechi.
Lipseşte ordinul BY 'în timp ce aşteaptă ordinea stabilă.
Indici de dragul indexurilor: ROI <0 din cauza scrierii/suportului scump.

15) Lista de verificare a implementării

1. Top N cereri de QPS și timp → selectați 3-5 candidați.
2. Eliminați planurile „EXPLICA ANALIZA”, verificați cardinalitatea vs real.
3. Indici de proiectare: comanda câmp, INCLUDE/parțială/funcțională.
4. Implementați partiționarea pentru tabele mari (chei temporare/chiriași).
5. Suprascrieți interogările: eliminați 'SELECT', CTE-urile simple inline, restricționați setul.
6. Activați butching și căutați paginare.
7. Configurați memoria cache: L1/L2, handicap după evenimente.
8. Introduceți monitorizarea planurilor și lent-log, alerte pentru regresii.
9. Efectuați teste de încărcare cu distribuție reală de date.
10. Actualizați ghidurile de dezvoltare (sugestii ORM, indexare, limite).

16) Înainte/după exemple

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) Protocoale ORM și API

Evitați N + 1: mostre lacome („include”, „JOIN FETCH”, „preîncărcare”).
Proiecții explicite ale câmpului, paginate de cursor.
gRPC/REST: limitați 'page _ size', fixați 'sort', utilizați jetoane opace.
Planul cache: utilizați parametrizarea; nu generează „unic” SQL pe apel.

18) Migrații și operațiuni

Adăugați indici online și marcați ca INVISIBLE/CONCURENT, planuri de testare, apoi comutați.
Revizii index - curățare sanitară regulată: duplicate, neutilizate, „moarte” pentru caracteristici vechi.
Planul de rotație partid (picătură vechi) și „VACUUM/OPTIMIZA” program.

19) Rezumat

Optimizarea interogării este ingineria sistemelor: chei și indici corecți, planuri îngrijite, partiționare și sharding atent, disciplină în interogări și ORM, caching și observabilitate. Urmând modelele descrise, veți obține un sistem rapid, previzibil și economic, care este rezistent la creșterea și încărcarea datelor.