Che cos'è un indice in SQL? Quando dovresti e non dovresti usarne uno?

Question

Accepted Answer

Un **indice** è una struttura dati (solitamente un **B-tree**) che consente al database di trovare righe rapidamente **senza scansionare l'intera tabella** — come l'indice di un libro. Accelera drammaticamente ricerche, filtri, join e ordinamenti sulla colonna (o le colonne) indicizzata, al costo di uno spazio di archiviazione aggiuntivo e scritture più lente.

## Come un indice aiuta

```sql
-- without an index on email, this scans EVERY row (slow on a big table) — O(n)
SELECT * FROM users WHERE email = 'ann@x.com';

-- create an index → the database can find the row directly — O(log n)
CREATE INDEX idx_users_email ON users(email);
-- now the same query uses the index → dramatically faster
```

Senza un indice, il database esegue una **scansione completa della tabella** (controllando ogni riga). Con uno, naviga un B-tree per trovare le righe corrispondenti in tempo logaritmico — la differenza tra scansionare milioni di righe e un numero limitato di ricerche.

## Quando usare gli indici

```text
✓ Columns in WHERE clauses (frequently filtered)
✓ Columns used in JOINs (foreign keys especially)
✓ Columns in ORDER BY (sorting can use the index)
✓ Columns with high SELECTIVITY (many distinct values — email, id)
✓ Primary keys (auto-indexed) and unique constraints
```

## Quando NON usare gli indici (i compromessi)

```text
✗ Columns rarely queried — wasted storage and write overhead
✗ Small tables — a full scan is already fast; an index adds no benefit
✗ Low-selectivity columns (e.g. a boolean with 2 values) — index barely helps
✗ Write-heavy columns — EVERY INSERT/UPDATE/DELETE must also update the indexes
  → too many indexes SLOW DOWN writes significantly
```

Gli indici non sono gratuiti: consumano **spazio di archiviazione** e **rallentano le scritture** (ogni modifica deve aggiornare gli indici). L'indicizzazione eccessiva danneggia le prestazioni di scrittura.

## Indici compositi e altri tipi di indice

```sql
CREATE INDEX idx_users_country_name ON users(country, name);   -- COMPOSITE (multi-column)
-- helps WHERE country = ? AND name = ?, and WHERE country = ? (leftmost prefix)
-- but NOT WHERE name = ? alone (order matters!)

CREATE UNIQUE INDEX ...      -- enforces uniqueness
-- also: partial indexes, covering indexes, GIN/GiST (Postgres) for special data
```

## Perché è importante

Gli indici sono uno degli argomenti più importanti nelle prestazioni del database — sono lo strumento principale per rendere le query veloci, e capire quando e come usarli è una conoscenza essenziale per chiunque lavori con database su larga scala.

Il valore principale è drammatico: senza indici, le query che filtrano, uniscono o ordinano su una colonna richiedono **scansioni complete della tabella** (controllando ogni riga — catastroficamente lento man mano che i dati crescono), mentre gli indici trasformano queste operazioni in ricerche logaritmiche veloci — spesso la differenza tra una query che richiede millisecondi rispetto a secondi o minuti.

Capire **quando indicizzare** (colonne WHERE frequentemente filtrate, colonne JOIN/chiave esterna, colonne ORDER BY, colonne ad alta selettività) è fondamentale per le prestazioni delle query.

Ugualmente importante è capire i **compromessi e quando NON indicizzare**: gli indici consumano spazio di archiviazione e, crucialmente, **rallentano le scritture** (ogni INSERT/UPDATE/DELETE deve aggiornare tutti gli indici), quindi l'indicizzazione eccessiva danneggia le prestazioni di scrittura, e gli indici aggiungono poco valore su tabelle piccole o colonne a bassa selettività.

Questo equilibrio — indicizzare ciò che viene interrogato senza indicizzare eccessivamente — è un'abilità chiave.

Conoscere gli **indici compositi** (multi-colonna, dove l'ordine delle colonne importa per le query che aiutano) completa la conoscenza pratica degli indici.

Poiché le prestazioni delle query sono critiche per le applicazioni reali, e poiché la corretta indicizzazione (gli indici giusti sulle colonne giuste, bilanciando la velocità di lettura rispetto al costo di scrittura e archiviazione) è l'ottimizzazione del database più impattante, padroneggiare gli indici — come funzionano, quando usarli, i compromessi di scrittura/archiviazione e gli indici compositi — è una conoscenza essenziale, di alto valore per database performanti e uno degli argomenti di database più importanti e comunemente testati.