Un SELECT veloce che prima terminava in 20 millisecondi all'improvviso si blocca per 12 secondi. Il database è cresciuto da 50 mila righe a 5 milioni, e ogni query è diventata una lotteria. Ti suona familiare?

Gli indici sono qualcosa di cui tutti hanno sentito parlare ma che pochi configurano in modo consapevole. Ne hai aggiunti un paio, le cose sembravano più veloci, e sei andato avanti. Poi sei mesi dopo le INSERT sono più lente delle SELECT senza indice, perché ogni inserimento ricostruisce cinque B-tree inutili.

Vediamo come funzionano davvero gli indici, quali tipi esistono e, soprattutto, quali regole seguire per crearli senza provocare disastri in produzione.

💡 Panoramica rapida:

• Comprendere i meccanismi degli indici B-tree, hash e compositi, perché senza questa conoscenza è impossibile scegliere il tipo giusto
• Padroneggiare 7 pratiche chiave: dall'indicizzazione delle chiavi esterne all'eliminazione degli indici inutilizzati
• Imparare a leggere EXPLAIN e a distinguere un indice utile da uno inutile

Cos'è un indice di database

Un indice di database è una struttura separata che memorizza valori ordinati di una o più colonne della tabella insieme a puntatori alle righe. Quando esegui SELECT ... WHERE category_id = 42, il server senza indice scandisce la tabella riga per riga (scansione completa). Con un indice trova i record necessari in O(log n), come cercare un nome in un elenco telefonico.

Un indice si crea con CREATE INDEX:

1
-- Regular index
2
CREATE INDEX idx_category
3
ON products (category_id);
4
 
5
-- Unique index
6
CREATE UNIQUE INDEX idx_email
7
ON users (email);

Ma il prezzo di letture più veloci sono scritture più lente. Ogni INSERT, UPDATE e DELETE deve aggiornare non solo la tabella ma tutti gli indici associati. Tre indici su una tabella con un milione di righe, e gli inserimenti massivi rallentano di un ordine di grandezza. L'equilibrio tra velocità di lettura e velocità di scrittura è la questione centrale nella progettazione degli indici.

Un'analisi dettagliata dell'argomento a cura di Hussein Nasser: 497 mila iscritti, un approccio ingegneristico senza fronzoli. Usando PostgreSQL come esempio, mostra i meccanismi interni degli indici, perché un CREATE INDEX accelera una query di 100 volte mentre un altro non serve a nulla.

Tipi di indice: quando usare quale

La scelta del tipo di indice determina l'efficienza con cui il database elabora la tua query. I diversi DBMS li implementano in modo diverso, ma i principi sono universali.

B-tree (albero bilanciato)

Il tipo predefinito nella maggior parte dei DBMS relazionali. PostgreSQL, MySQL, Oracle e SQL Server usano tutti il B-tree come indice predefinito. Memorizza le chiavi in ordine, supporta operazioni di confronto, intervalli (BETWEEN), ricerca per prefisso (LIKE 'prefix%') e ordinamento. È la scelta migliore per la stragrande maggioranza dei casi.

Indice hash

Funziona solo per confronti di uguaglianza esatta =. Fulmineo sulle query puntuali ma inutile per intervalli e ordinamento. In PostgreSQL, gli indici hash sono pronti per la produzione a partire dalla versione 10; in MySQL non sono disponibili in InnoDB (solo in MEMORY).

Indice clustered

Definisce l'ordine fisico delle righe su disco. In MySQL InnoDB, la chiave primaria è sempre clustered: le righe sono memorizzate nell'ordine della PRIMARY KEY. In SQL Server, esiste un indice clustered per tabella. Scegliere la chiave clustered giusta (a crescita monotona: BIGSERIAL, AUTO_INCREMENT o UNIQUEIDENTIFIER con NEWSEQUENTIALID()) offre vantaggi sulle query con intervallo e previene la frammentazione delle pagine.

Indice composito

Un indice su due o più colonne. Fondamentale per le query che filtrano e ordinano per più colonne:

1
CREATE INDEX idx_order_date_status
2
ON orders (order_date, status);

L'ordine delle colonne è importante: metti per prima la colonna con la selettività più alta usata nella WHERE. Il principio del prefisso più a sinistra: un indice (A, B, C) funziona per WHERE A e WHERE A AND B, ma non per WHERE B AND C.

Indice covering

Contiene tutte le colonne necessarie alla query, sia per il filtraggio che per l'output. Il database recupera tutto dall'indice senza toccare la tabella. In PostgreSQL si realizza con le colonne INCLUDE; in MySQL InnoDB, il covering implicito avviene tramite l'indice clustered.

7 Pratiche chiave per l'indicizzazione

1. Indicizza le colonne usate nella WHERE

La prima regola: ogni colonna usata regolarmente in WHERE, JOIN ... ON e HAVING dovrebbe essere indicizzata. Sono queste le operazioni che traggono il massimo beneficio dagli indici.

Prima di creare un indice, verifica la selettività della colonna. Se la colonna status ha solo tre valori ('new', 'processing', 'done') e ci sono 2 milioni di righe, un indice su status è quasi inutile: il planner sceglierà una scansione completa della tabella come opzione più economica. Un indice ha senso quando il numero di valori univoci è sufficientemente grande rispetto alla dimensione della tabella.

2. Indicizza le colonne usate per l'ordinamento

ORDER BY senza indice significa filesort (MySQL) o ordinamento esplicito (PostgreSQL): il server raccoglie tutte le righe e le ordina in memoria (o su disco se work_mem/sort_buffer_size è troppo piccolo). Un indice sulle stesse colonne di ORDER BY è a costo zero, poiché i dati sono già ordinati nel B-tree.

1
-- No index on created_at: filesort on millions of rows
2
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;
3
 
4
-- Index solves the problem
5
CREATE INDEX idx_posts_created ON posts (created_at);

3. Indicizza le colonne usate in GROUP BY e aggregazioni

Il raggruppamento senza indice richiede una scansione completa e la costruzione di una tabella hash. Un indice sulle colonne di GROUP BY trasforma l'operazione in un'aggregazione a flusso: le righe sono già raggruppate nell'ordine delle chiavi.

4. Indicizza tutte le chiavi esterne

Una chiave esterna non indicizzata è una bomba a orologeria. DELETE FROM users WHERE id = 5 con un vincolo FOREIGN KEY (user_id) REFERENCES users(id) sulla tabella orders ma senza indice su user_id significa una scansione completa di orders per ogni cancellazione. Tutti i DBMS più diffusi richiedono un indice sulla chiave esterna o ne creano uno implicitamente (MySQL InnoDB lo fa automaticamente, PostgreSQL no).

5. Indicizza le colonne univoche e le chiavi primarie

La chiave primaria viene indicizzata automaticamente (spesso come indice clustered). Un UNIQUE INDEX esplicito protegge dai duplicati e accelera anche le ricerche. Qualsiasi colonna con un vincolo di unicità aziendale (ad esempio email, slug o external_id) dovrebbe avere un indice univoco, sia per l'integrità che per le performance.

6. Usa l'indice clustered in modo consapevole

Per tabelle grandi (decine di milioni di righe), la chiave clustered giusta è critica. Una buona scelta è un valore a crescita monotona: AUTO_INCREMENT, BIGSERIAL o UUID v7. Un UUID casuale come chiave clustered causa frammentazione delle pagine: ogni inserimento finisce in un punto casuale del B-tree, dividendo le pagine piene in due mezze vuote.

7. Elimina gli indici inutilizzati

Un indice che nessuna query utilizza è una perdita netta. Rallenta le scritture, occupa spazio su disco e memoria del buffer pool, e fuorvia il query planner. In PostgreSQL, la tabella di sistema pg_stat_user_indexes fornisce un elenco degli indici inutilizzati:

1
SELECT schemaname, relname, indexrelname, idx_scan
2
FROM pg_stat_user_indexes
3
WHERE idx_scan = 0
4
ORDER BY relname;

In MySQL, informazioni simili sono disponibili da sys.schema_unused_indexes (a partire dalla versione 5.7). Pianifica un audit mensile ed elimina gli indici che non sono stati usati nemmeno una volta durante il periodo di riferimento.

Come verificare che i tuoi indici funzionino

Una volta creato un indice, verifica che venga effettivamente utilizzato. Il comando EXPLAIN (o EXPLAIN ANALYZE) mostra il piano di esecuzione della query e l'uso effettivo degli indici:

1
EXPLAIN ANALYZE
2
SELECT * FROM orders
3
WHERE customer_id = 12345
4
ORDER BY order_date DESC;

Nell'output, cerca Index Scan o Index Only Scan (PostgreSQL) / Using index (MySQL). Se vedi Seq Scan (PostgreSQL) o Using where; Using filesort (MySQL), l'indice non viene usato. Possibili cause: bassa selettività, tipo di indice sbagliato, ordine delle colonne non corrispondente in un indice composito o statistiche obsolete (ANALYZE table_name;).

Monitora regolarmente le metriche: pg_stat_user_indexes.idx_scan in PostgreSQL, sys.schema_index_statistics in MySQL. Un indice con zero scansioni in un mese è un candidato all'eliminazione.

⁉️🤔 Domande frequenti

Quanti indici dovrebbe avere una tabella?

Idealmente, da 2 a 6 indici per tabella attivamente utilizzata. Meno di due significa quasi certamente che alcune query sono subottimali. Più di sei, e dovresti verificare attentamente se sono tutti realmente necessari: ogni indice extra rallenta le scritture. Per le tabelle di lookup (poche scritture, molte letture) più indici sono giustificati. Per le tabelle operative ad alto traffico (molte INSERT/UPDATE) mantieni il numero al minimo.

Perché un indice composito è migliore di diversi indici a colonna singola?

Un indice composito (A, B) è UNA struttura. Il server la attraversa una volta sola. Tre indici separati (A), (B) e (C) per WHERE A=1 AND B=2 costringono il server a scegliere un indice (e filtrare il resto) o a eseguire una scansione con indice bitmap (unendo le bitmap). Un indice composito è quasi sempre più efficiente, a patto che l'ordine delle colonne corrisponda alle tue query.

Quando un indice danneggia invece di aiutare?

Tre scenari tipici. Primo: la tabella è piccola (fino a poche migliaia di righe), e una scansione completa è più veloce della lettura dell'indice più il recupero delle righe. Secondo: un indice su una colonna a bassa selettività (is_deleted, status con tre valori). Terzo: inserimenti massivi durante ETL/import, dove gli indici vengono ricostruiti a ogni batch. Eliminali prima del caricamento e ricreali dopo.

Bisogna indicizzare le colonne usate nelle JOIN?

Assolutamente sì. Ogni JOIN senza indice sulla colonna di join della tabella esterna è un nested loop con scansione completa. Per LEFT JOIN orders ON users.id = orders.user_id, un indice su orders.user_id trasforma il nested loop in una ricerca tramite indice. Indicizza sempre le colonne su cui fai il join.

B-tree o hash: quale scegliere per ricerche esatte?

Per =, l'hash è più veloce: una ricerca hash richiede tempo costante, mentre il B-tree attraversa l'albero in un numero logaritmico di passi. Ma l'hash non supporta intervalli, ordinamento o UNIQUE. In pratica, il B-tree copre la stragrande maggioranza degli scenari; l'hash è uno strumento di nicchia per ricerche puntuali per chiave in sistemi ad alto carico (sessioni, cache). In PostgreSQL, gli indici hash sono pronti per la produzione dalla versione 10 e occupano meno spazio del B-tree.

Conviene indicizzare "per sicurezza"?

No. Ogni indice è un compromesso. Accelera le letture al costo di scritture più lente e spazio su disco aggiuntivo. Non indicizzare "per sicurezza"; indicizza per query specifiche che vengono effettivamente eseguite nella tua applicazione. Profila le query lente (pg_stat_statements, slow_query_log), aggiungi indici in modo chirurgico e verifica EXPLAIN prima e dopo.

Il concetto chiave è semplice: gli indici sono uno strumento, non un fine in sé. Un indice composito ben progettato può sostituire tre indici a colonna singola e risparmiare gigabyte di spazio su disco. E un indice inutilizzato su una tabella con molte scritture può rallentare l'intera applicazione.

Se vuoi approfondire, inizia con la guida ufficiale alla progettazione degli indici di SQL Server e la documentazione di PostgreSQL sui tipi di indice. E se incontri una query che gli indici non riescono a risolvere, il problema potrebbe risiedere nel modello dati stesso.