Quando una query che prima rispondeva in un istante inizia a impiegare secondi, la causa più frequente è un indice mancante. Allo stesso tempo, chi scopre gli indici tende a crearne ovunque, rallentando le scritture e sprecando spazio. Capire come funzionano davvero permette di metterli dove servono e solo lì.
Il problema che risolvono
Senza indici, per trovare i clienti con un certo cognome il database deve leggere ogni riga della tabella e controllarla. Si chiama scansione completa, o full table scan. Su mille righe non si nota, su dieci milioni sì.
Un indice è una struttura separata che contiene i valori di una o più colonne, ordinati, ciascuno accompagnato dal riferimento alla riga corrispondente. L'analogia classica è l'indice analitico di un libro: invece di sfogliare tutte le pagine per trovare un argomento, si cerca la voce in ordine alfabetico e si va direttamente alla pagina indicata.
Come è fatto un indice B-tree
La struttura più diffusa nei database relazionali è il B-tree, o una sua variante. È un albero bilanciato in cui:
- la radice e i nodi intermedi contengono valori di separazione che indicano in quale ramo proseguire;
- le foglie contengono i valori ordinati e i puntatori alle righe;
- tutte le foglie si trovano alla stessa profondità.
Poiché ogni nodo contiene molti valori, l'albero è molto largo e poco profondo. Anche con milioni di righe bastano pochi livelli, quindi pochi accessi, per arrivare al valore cercato. In più, essendo le foglie ordinate e collegate tra loro, l'indice è efficiente anche per gli intervalli: «tutti gli ordini tra il 1° e il 15 marzo» si trova scendendo fino al primo valore e scorrendo le foglie fino all'ultimo.
Che cosa un indice accelera
Un indice B-tree su una colonna è utile per:
- uguaglianze:
WHERE email = 'anna@esempio.it'; - intervalli:
WHERE data_ordine BETWEEN '2025-03-01' AND '2025-03-15'; - ordinamenti:
ORDER BY data_ordine, perché i valori sono già in ordine; - JOIN: la colonna di chiave esterna usata per unire le tabelle;
- ricerche per prefisso:
WHERE cognome LIKE 'Ros%'.
Non aiuta, invece, nelle ricerche con il carattere jolly iniziale, come LIKE '%ossi', perché l'ordine alfabetico parte dall'inizio della stringa. Per questi casi esistono strutture diverse, come gli indici per la ricerca testuale.
Gli indici composti e l'ordine delle colonne
Un indice può contenere più colonne. In questo caso l'ordine conta moltissimo:
CREATE INDEX idx_ordini_cliente_data
ON ordini (id_cliente, data_ordine);
Questo indice è ordinato prima per cliente e, a parità di cliente, per data. Come un elenco telefonico ordinato per cognome e poi per nome, funziona per:
WHERE id_cliente = 42WHERE id_cliente = 42 AND data_ordine >= '2025-01-01'WHERE id_cliente = 42 ORDER BY data_ordine
Ma non è di grande aiuto per WHERE data_ordine >= '2025-01-01' da sola, così come un elenco ordinato per cognome non aiuta a trovare tutte le persone di nome Maria. La regola pratica è mettere per prime le colonne usate con l'uguaglianza e dopo quelle usate per intervalli o ordinamenti.
Il costo degli indici
Gli indici non sono gratuiti:
- Scritture più lente. Ogni
INSERT,UPDATEoDELETEdeve aggiornare anche tutti gli indici della tabella. - Spazio su disco e in memoria. Un indice può occupare una frazione importante della tabella stessa, e funziona meglio quando sta in memoria.
- Manutenzione. Indici inutilizzati vanno comunque aggiornati e salvati nei backup.
Per questo una tabella che riceve molte scritture e poche letture va indicizzata con prudenza, mentre una tabella consultata di continuo e aggiornata di rado può permettersi più indici.
Selettività: quando un indice non serve
Un indice è efficace quando filtra poche righe rispetto al totale. Su una colonna «attivo» con due soli valori, dove il 95% delle righe vale vero, cercare i record attivi con l'indice è più lento che leggere la tabella. L'ottimizzatore del database lo sa e spesso ignora l'indice. Alcuni database permettono invece indici parziali, che contengono solo le righe di interesse, per esempio solo quelle con attivo = false.
Come decidere quali indici creare
Un metodo pratico:
- partire dalle query reali, non da ipotesi: i log delle query lente sono la fonte migliore;
- verificare con il piano di esecuzione se la query fa una scansione completa;
- creare l'indice più mirato possibile e controllare di nuovo il piano;
- periodicamente cercare gli indici mai usati, che molti database segnalano nelle loro statistiche, e valutarne la rimozione.
Le chiavi primarie e i vincoli UNIQUE creano già un indice automaticamente. Le chiavi esterne, in molti database, no: indicizzarle è spesso il primo intervento utile.
In sintesi
- Un indice è una copia ordinata di alcune colonne che evita di leggere tutta la tabella.
- Il B-tree è poco profondo e gestisce bene uguaglianze, intervalli e ordinamenti.
- Negli indici composti l'ordine delle colonne determina quali query ne beneficiano.
- Ogni indice rallenta le scritture e occupa spazio: va giustificato da query reali.
- Le chiavi esterne sono spesso le prime candidate a essere indicizzate.
