Indicizzazione e ottimizzazione delle richieste

1) Obiettivi di indicizzazione e ottimizzazione

Latenza: riduzione P50/P95/P99.
Larghezza di banda: crescita QPS senza scalabilità orizzontale.
Prevedibilità: piani stabili e nessuna «corsa» del tempo di risposta.
Risparmio: meno IO/CPU, meno bolletta cloud.
Affidabilità: riduzione dei blocchi e dei dedotti grazie alla corretta disponibilità.

• Ogni ottimizzazione deve mantenere correttezza e coerenza.
• Controlla gli effetti nelle metriche e nei fogli di pianificazione.

2) Strutture di indice di base e quando applicarle

2. 1 B-Tree (default)

Uguale a/intervalli, ordinamento, 'ORDER BY'.
Ottimo per la maggior parte dei filtri in termini di tempo/ID/stato.

2. 2 Hash

Eguaglianze pulite ('='), più economiche in memoria, ma senza ordine (PG: limitazioni eliminate, ma ancora scelte di nicchia).

2. 3 GIN / GiST (PostgreSQL)

GIN: array/JSONB, full text (tsvector), containment ('@>').
GiST: geo, intervalli, kNN.

2. 4 BRIN (PostgreSQL)

Indice super economico per tavoli «naturalmente ordinati» (append-only in base al tempo). Bene per le time-series con grandi tabelle.

2. 5 Bitmap (MySQL/InnoDB: no nativo; DW-DATABASE/OLAP)

Efficaci per la scarsa radicalità e sfaccettatura, più spesso nei depositi a colonne.

2. 6 Indici di colonna (ClickHouse)

Primary key + data skipping (minmax), secondary через `skip indexes` (bloom, set).
Query OLAP con aggregazioni e intervalli.

2. 7 Indici invertiti (Elasticsearch/OpenSearch)

Full text, sfaccettature, ricerca ibrida. Per filtri precisi, utilizzare i campi keyboard e doc values.

2. 8 MongoDB

Single, compound, multikey (array), partiale, TTL, text, hased (charding in chiave uniforme).

3) Progettazione di chiavi e indici compositi

3. 1 Regola prefisso sinistro

L'ordine dei campi nell'indice determina l'uso.
Query "WHERE tenant _ id =? AND created_at >=? ORDER BY created_at DESC` → индекс `(tenant_id, created_at DESC, id DESC)`.

3. 2 Tie-breaker

Aggiungi una coda unica (solitamente «id») per l'ordinamento stabile e la paginazione seek.

3. 3 Indici parziali/filtrati

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 di copertura

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

3. 5 Funzionali/calcolabili

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

4) Partizionamento e scardinamento

4. 1 Partizionamento (PG native/eredità tabellare; MySQL RANGE/LIST)

Rotazione temporale («daily/week») semplifica VACUUM/DELETE.
Gli indici delle partenze locali sono più piccoli di B-Tre, più veloci del piano.

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

4. 2 Chiave di partizionamento

In OLTP, per «tenant _ id» (localizzazione del carico).
In time-series/OLAP - per «ts» (query intervallo).
Ibrido: '(tenant _ id, ts)' + sottopartiti.

4. 3 Sharding

Consistent hasing/range-shard à tenant _ id "o in base all'ora.
Richiesta di cross-shard → scatter-gather e k-way merge; tenete per-shard cursor.

5) Statistiche, cardinalità e piani

5. 1 Statistiche aggiornate

Attivare l'analisi automatica ('autovacuum/autoanalyze'), ingrandire 'default _ statistics _ target' per le distribuzioni «sporche».

5. 2 Statistiche avanzate (PG)

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

5. 3 Piano di esecuzione

• `Rows`, `Loops`, `Actual time`, `Shared Read/Hit`, `Recheck Cond`.
• Типы join: Nested Loop, Hash Join, Merge Join.
• Seq Scan vs Index Scan/Only Scan/Bitmap Heap Scan.

5. 4 Stabilità dei piani

La parametrizzazione può non essere corretta. Utilizzare plan cache guardrails (PG: 'plan _ cache _ mode = force _ custom _ plan'per le query problematiche) o perimetrare le costanti.

6) Ottimizzazione di join-ow e ordinamenti

6. 1 Strategie

Nested Loop è un piccolo indice esterno veloce sull'interno.
Hash John è un insieme di grandi dimensioni, ha memoria sotto la tabella hash.
Merge Join: ingressi ordinati, vantaggiosi con l'ordine esistente.

6. 2 Indici sotto join

Per «A JOIN B ON B.a _ id = A.id», → l'indice su «B (a _ id)».
Il filtro dopo join è un indice nelle colonne del filtro della tabella interna.

6. 3 Ordinamenti

Evitare ORDER BY senza l'indice appropriato; ordinamento su set di grandi dimensioni percorso per memoria/disco.

7) Riscrivi query (query rewrite)

Sbarazzatevi di «fiocchi di neve»; espandersi in JOIN.
Utilizzare CTE-inline (PG ≥12 CTE di default, ma MATERIALIZI'può fissare il risultato intermedio se necessario).
Rimuovi SELECT e elenca i campi (risparmio IO/rete).
Spostare i calcoli da WHERE a una forma indicizzata (colonne predefinite).
Aggregazioni: tabelle di riepilogo preliminari/viste materializzate con aggiornamento incrementale.

8) Batching, limitazione e paginazione

Batch-insert/update: pacchetti 500-5000 invece di una cosa.
Seek-paginazione per '(fort _ key, id)' invece di OFFSET profondo.
Limita il set prima dell'ordinamento/del gioiello (push-down 'LIMIT').

9) Cache e denormalizzazione

Query-cache livello applicazione (chiave = SQL + bind-vars + versione dei diritti).
Materialization views per unità pesanti; piano di rotazione/refresco.
Denormalizzazione: memorizza i campi calcolabili spesso leggibili (prezzo scontato), ma con attività trigger/di fondo per la consistenza.
Redis come L2 per chiavi «hot» (con TTL e invalidità per eventi).

10) Specificità dei motori popolari

10. 1 PostgreSQL

Индексы: B-Tree, Hash, GIN/GiST, BRIN, partial, functional, 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

Statistiche sull'istogramma ('ANALYZE TABLE'... UPDATE HISTOGRAM` в 8. 0).

10. 3 ClickHouse

Chiave primaria = ordinamento; 'ORDER BY (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 } } });

Usa «hint ()» per diagnosticare, controlla «covered query».

10. 5 Elasticsearch/OpenSearch

Keyboard vs text campi; doc _ values per ordinare/aggregare.
Segmentazione heap: aggregazioni - heavy; limitare «size» e utilizzare «composite» di aggregazione (campionamento di pagine).
Non attivare gli analizzatori dove si desidera un confronto preciso.

11) Concorrenza, blocco e MVCC

Transazioni brevi evitare le letture «lunghe» sotto «REPEATABLE READ» senza bisogno.
Anche le operazioni di indice vengono bloccate (riduzione del throughput write).
Pianifica l'indicizzazione online: 'CREATE INDEX CONCERTLY' (PG), 'ALGORITHM = INPLACE '/' ONLINE' (MySQL).
Inserimento nella coda dell'ora/ID delle pagine hot dell'indice; distribuire la chiave (UUIDv7/sale).

12) Osservabilità e SLO

• db _ query _ latency _ ms (P50/P95/P99) per nome della query.
• `rows_examined`, `rows_returned`, `buffer_hit_ratio`.
• `deadlocks`, `lock_wait_ms`, `temp_sort_disk_usage`.
• Parte dei piani con'Seq Scan'dove si aspettava «Index Scan».
• Regress alert quando si cambia la versione/le impostazioni del database.

• Attivare slow query log con una soglia (ad esempio 200 ms).
• Correlazione query con span (trace _ id).
• Disattivare i piani di query con problemi e memorizzarli nell'archivio oggetti per la retrospettiva.

• P95 letture «<= 150 ms» con «LIMIT <= 50» e «hot» tenante.
• P95 record «<= 200 ms» con batch fino a 1000 righe.

13) Sicurezza e multi-tenenza

Gli indici dei campi di controllo di accesso ('tenant _ id', 'owner _ id') sono obbligatori.
I criteri (RLS/ABAC) devono essere pre-filtro; Altrimenti l'ottimizzatore non è corretto.
Non indicizzare i campi sensibili all'aperto; Usa hash/token.

14) Anti-pattern

Profondo «OFFSET» senza alternativa al cursore seek.
Un indice per tutto - Sovraccarico di memoria e write-path.
SELECT nei percorsi critici.
Funzioni sopra la colonna in WHERE senza indice funzionale.
Piani instabili a causa delle vecchie statistiche.
Nessun ORDER BY in attesa di un ordine stabile.
Indici per indici: RE <0 a causa del costo di scrittura/supporto.

15) Assegno-foglio di implementazione

1. La Top N delle richieste QPS e il tempo è necessario selezionare 3-5 candidati.
2. Togliere i piani «EXPLORER ANALYZE», verificare la cardinalità vs effettiva.
3. Progettare gli indici: ordine dei campi, INCLUDE/partial/functional.
4. Implementare la partizione per grandi tabelle (chiavi temporanee/tenanti).
5. Riscrivi query: rimuovi SELECT, incolla CTE semplici, limita la raccolta.
6. Abilita il batching e la paginazione seek.
7. Configura la cache: L1/L2, disabilità per evento.
8. Inserire il monitoraggio dei piani e slow-logue, gli alert per le regressioni.
9. Eseguire test di carico con distribuzione effettiva dei dati.
10. Aggiorna le haydline per lo sviluppo (hit ORM, indicizzazione, limiti).

16) Esempi di prima/dopo

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 e protocolli API

Evitare N + 1: campionamenti avidi («includes», «JOHN FETCH», «pratoad»).
Proiezioni esplicite dei campi paginate con il cursore.
gRPC/REST: Limita «page _ size», fissa «fort», usa i token opachi.
Piano-cache: utilizzare la parametrizzazione non generare SQL «univoco» per ogni chiamata.

18) Migrazioni e operazioni

Aggiungere indici online e segnare come INVISIBILE/CONCURRENTLY, testare i piani, quindi cambiare.
Le revisioni degli indici - pulizia ordinaria, duplicati, inutilizzati, morti per i vecchi Fich.
Piano di rotazione delle partizioni (drop vecchie) e «VACUUM/OTTIMIZE» pianificazione.

19) Riepilogo

L'ottimizzazione delle richieste è ingegneria di sistema: chiavi e indici corretti, pianificazioni accurate, partitura e scardinamento elaborati, disciplina delle richieste e dell'ORM, cache e osservabilità. Rispettando i pattern descritti, è possibile ottenere un sistema rapido, prevedibile e conveniente, resistente alla crescita dei dati e al carico di lavoro.