Indici Query Tuning — Cheat Sheet

Tag: sql, database, performance, indexing, query-tuning, rdbms

Livello: intermedio

TL;DR: Guida rapida all'ottimizzazione delle query SQL tramite indici. Focalizzata su tipologie di indici, execution plan e best practice per evitare colli di bottiglia in lettura e scrittura.

Concetti chiave

Un indice e' una struttura dati (es. B-Tree, Hash) che velocizza il recupero dei dati, al costo di maggior spazio e tempo di scrittura.

L'indice Clustered (primario) ordina fisicamente i dati su disco; e' consentito un solo indice clustered per tabella.

Gli indici Non-Clustered (secondari) memorizzano chiavi e puntatori alle righe fisiche; possono essercene multipli.

La selettivita' indica la percentuale di valori unici in una colonna; colonne ad alta selettivita' sono i migliori candidati per l'indicizzazione.

Un Covering Index include tutte le colonne richieste da una query (in SELECT, JOIN e WHERE), evitando letture extra sulla tabella (Bookmark Lookup).

L'ordine delle colonne negli indici compositi e' vitale: il database sfrutta l'indice solo seguendo il principio del "Leftmost prefix".

Le statistiche del database devono essere aggiornate regolarmente affinche' l'optimizer scelga il piano di esecuzione migliore.

L'uso di troppi indici (Over-indexing) rallenta pesantemente le operazioni DML (INSERT, UPDATE, DELETE).

Domande tipiche e risposte pronte

Q: Qual e' la differenza tra Index Seek e Index Scan?R: L'Index Seek naviga l'albero dell'indice puntando direttamente ai record cercati ed e' molto efficiente. L'Index Scan legge l'intero indice sequenzialmente, spesso sintomo di query non ottimizzate.

Q: Perche' il database ignora l'indice che ho appena creato?R: Di solito succede per statistiche non aggiornate, selettivita' troppo bassa, oppure l'uso di funzioni direttamente sulla colonna nel WHERE, che invalida l'indice (SARGability).

Q: Quando ha senso creare un indice composito anziche' indici singoli?R: Li uso quando le query filtrano o raggruppano frequentemente su piu' colonne specifiche contemporaneamente. Metto sempre come prima colonna quella con selettivita' maggiore.

Insidie e errori comuni

Usare la clausola LIKE '%termine%': la wildcard iniziale impedisce al database di scorrere il B-Tree, forzando un Full Table Scan.

Creare indici su colonne booleane (es. is_active) o a bassissima cardinalita' (es. sesso, stato civile); l'optimizer tendera' a ignorarli.

Inserire le colonne indicizzate dentro funzioni nel WHERE, ad esempio WHERE YEAR(created_at) = 2023. Questo rende la query "non-SARGable".

Dimenticare l'overhead di manutenzione: ogni indice richiede aggiornamenti sincroni durante le scritture e frammenta il disco nel tempo.

Snippet e comandiSQL

```-- Creazione indice standard B-Tree

CREATE INDEX idx_users_email ON users(email);

-- Creazione indice composito (l'ordine conta)

CREATE INDEX idx_orders_status_date ON orders(status, created_at DESC);

-- Creazione indice univoco

CREATE UNIQUE INDEX uq_users_username ON users(username);

-- Indice filtrato/parziale (utile per stati specifici molto usati)

CREATE INDEX idx_orders_active ON orders(id) WHERE status = 'ACTIVE';

-- Analisi del piano di esecuzione (PostgreSQL / MySQL)

EXPLAIN ANALYZE SELECT id, name FROM users WHERE email = 'test@example.com';

```

Da ripassare

[ ] Analisi avanzata dei costi negli Execution Plan (cost, rows, width).

[ ] Differenze di implementazione indici tra PostgreSQL (es. BRIN, GIN) e MySQL.

[ ] Risoluzione dei Key Lookup / RID Lookup in SQL Server tramite Include columns.