Come ottimizzi le query SQL lente?

Question

Accepted Answer

L'ottimizzazione delle query lente inizia con l'**analisi del piano di esecuzione** (`EXPLAIN`) per individuare il collo di bottiglia, quindi applicare correzioni mirate — più comunemente **aggiungere indici**, riscrivere la query e evitare anti-pattern comuni. Il guadagno più significativo è solitamente trasformare le scansioni complete della tabella in ricerche tramite indice.

## Passaggio 1: analizzare con EXPLAIN

```sql
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 5;
-- shows the EXECUTION PLAN: scan type (Seq Scan = full scan, Index Scan = using an index),
-- estimated/actual rows, cost, and timing → reveals WHERE the time goes
```

`EXPLAIN` (oppure `EXPLAIN ANALYZE`) mostra come il database esegue la query — crucialmente, se sta effettuando una **scansione completa della tabella** (lenta) o utilizzando un **indice** (veloce). Profilare sempre prima di ottimizzare.

## Il guadagno più significativo: aggiungere indici

```sql
-- if EXPLAIN shows a Seq Scan on a filtered/joined column → add an index
CREATE INDEX idx_orders_customer ON orders(customer_id);
-- turns a full scan into a fast index lookup
```

La correzione più comune e impattante: **aggiungere indici** sulle colonne utilizzate in `WHERE`, `JOIN` e `ORDER BY`. Questo trasforma le scansioni complete della tabella in ricerche veloci.

## Anti-pattern comuni da correggere

```sql
-- ❌ functions on a column prevent index use
WHERE UPPER(email) = 'X@Y.COM'        -- can't use an index on email
WHERE email = 'x@y.com'               -- ✅ can use the index

-- ❌ leading wildcard LIKE can't use an index
WHERE name LIKE '%smith'              -- ✅ 'smith%' (prefix) can use an index

-- ❌ SELECT * fetches unneeded columns
SELECT * → SELECT only_needed_columns  -- less data, may use a covering index

-- ❌ N+1 queries (querying in a loop) → use a single JOIN instead
```

## Altre tecniche di ottimizzazione

```text
✓ Fix N+1 queries (one query per row) → use JOINs / batch into one query
✓ Add appropriate indexes (including COMPOSITE for multi-column filters)
✓ Reduce data scanned — filter early, select only needed columns, paginate
✓ Rewrite subqueries as JOINs where more efficient
✓ Use EXISTS instead of IN for existence checks on large subqueries
✓ Keep statistics updated (ANALYZE) so the planner makes good choices
✓ Consider denormalization / materialized views for expensive repeated queries
✓ Watch for missing indexes on FOREIGN KEYS (joins scan without them)
```

## Perché è importante

L'ottimizzazione delle query è una conoscenza critica per le prestazioni del database — le query lente sono una delle cause principali di scarsa performance dell'applicazione, e saper trovarle e correggerle è un'abilità essenziale di livello senior per chiunque lavori con database su larga scala.

La disciplina fondamentale è **analizzare con `EXPLAIN`** (esaminare il piano di esecuzione per trovare il vero collo di bottiglia — crucialmente se la query effettua una scansione completa della tabella lenta o una ricerca su indice veloce) prima di ottimizzare, piuttosto che indovinare — questo approccio basato sulla misurazione è ciò che rende l'ottimizzazione efficace.

Il **singolo guadagno più grande e più comune è aggiungere indici** sulle colonne filtrate, unite e ordinate (trasformando le scansioni complete della tabella in ricerche veloci — spesso migliorando il tempo di query di ordini di grandezza), rendendo la conoscenza degli indici centrale nell'ottimizzazione.

Comprendere gli **anti-pattern comuni** è ugualmente importante: funzioni sulle colonne e `LIKE` con wildcard iniziale impediscono l'uso dell'indice, `SELECT *` recupera dati non necessari, e soprattutto le **query N+1** (eseguire query in un ciclo invece di un singolo JOIN — un killer di prestazioni frequente e grave nel codice dell'applicazione) — riconoscere e correggere questi problemi affronta le cause più comuni di query lente.

Altre tecniche (indici compositi, ridurre i dati scansionati, riscrivere subquery come JOIN, mantenere aggiornate le statistiche, viste materializzate per query costose) completano il toolkit.

Poiché le prestazioni delle query influiscono direttamente sulla reattività e la scalabilità dell'applicazione, e poiché l'approccio sistematico (analizzare con EXPLAIN, poi correggere — solitamente aggiungendo indici ed eliminando anti-pattern come N+1) è ciò che mantiene i database veloci sotto carico, padroneggiare l'ottimizzazione delle query è una conoscenza senior importante e di alto valore che distingue gli sviluppatori che possono costruire applicazioni database-backed performanti e scalabili — un'abilità critica e frequentemente rilevante, e un argomento di intervista comune per le prestazioni del database.