Risposta.

Storia della domanda:
Sin dall'inizio, le query SQL sono state progettate come dichiarative: "cosa ottenere", non "come ottenere". L'ottimizzatore del DBMS costruisce un piano di esecuzione (execution plan), che definisce l'ordine delle unioni, delle filtrazioni, delle scansioni e dell'uso degli indici.

Problema:
Senza comprendere il piano di esecuzione, è impossibile spiegare perché una query, apparentemente semplice, venga eseguita troppo lentamente, mentre una complessa funziona rapidamente. Un piano errato può bloccare il server per ore a causa di operazioni non necessarie o di un uso scorretto degli indici.

Soluzione:
Gli strumenti di analisi sono istruzioni come EXPLAIN, EXPLAIN ANALYZE (PostgreSQL), SHOW EXPLAIN (MySQL), Management Studio Execution Plan (SQL Server). Con essi si può vedere, passo dopo passo, come è stata eseguita la query, quale volume di dati è stato letto, quale indice è stato utilizzato, dove ci sono state scansioni dell'intera tabella (table scan) e dove ci sono state delle attese.

Esempio di codice:

EXPLAIN ANALYZE SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.status = 'shipped';

Caratteristiche chiave:

Il piano fornirà uno schema dettagliato di quale operazione è stata eseguita in quale ordine dal DBMS.
È possibile vedere dove è stato utilizzato l'index scan e dove il table scan.
Permette di identificare i colli di bottiglia per l'ottimizzazione e di verificare l'ipotesi sull'influenza della riscrittura della query sulle prestazioni.

Domande insidiose.

Se aggiungo un indice, la query accelererà sempre?

No! L'indice aiuterà solo se la filtrazione sul suo campo restringe significativamente il numero di righe restituite. Se la maggior parte dei record soddisfa la condizione, l'ottimizzatore potrebbe ignorare l'indice.

Esempio:

-- Il campo 'gender' ha solo due valori — l'indice non aiuterà
CREATE INDEX idx_gender ON people(gender);
SELECT * FROM people WHERE gender = 'M';

L'ordine delle tabelle in un JOIN influisce sul risultato dell'esecuzione?

No, i dati finali saranno gli stessi, tuttavia l'ottimizzatore può cambiare l'ordine di esecuzione delle unioni per migliorare le prestazioni. Ma se viene scritto un JOIN specifico o sono utilizzati suggerimenti come "JOIN HINT", l'ordine può influenzare l'efficacia dell'esecuzione.

Perché analizzare "Estimated rows" e "Actual rows" nel piano di esecuzione?

La differenza tra di essi può mostrare che le statistiche sulle tabelle sono obsolete o non corrispondono alla realtà, e il piano selezionato non è ottimale—è necessario aggiornare le statistiche o rivedere la struttura della query.

-- PostgreSQL
ANALYZE table_name; -- aggiornare le statistiche

Errori comuni e anti-pattern

Non si esamina il piano di esecuzione quando si verificano problemi: si cerca di ottimizzare "alla cieca"
Aggiunta di indici superflui/non mirati per "velocizzare", che rallenta solo le modifiche ai dati
Le statistiche sulle tabelle non vengono aggiornate
Le query vengono progettate senza tenere conto di come saranno eseguite dall'ottimizzatore

Esempio dalla vita reale

Caso negativo

Nel progetto, gli analisti si lamentavano a lungo di "blocchi" nei report. Una query con cinque JOIN richiedeva 25 minuti per l'esecuzione. Si è scoperto che era stato scelto un piano con scansione completa di una gigantesca tabella invece di un index scan, gli indici non erano sui campi corretti, e le statistiche non erano state aggiornate per un anno.

Vantaggi:

Manutenzione semplice, minimizzazione degli indici

Svantaggi:

La query era lenta, si sprecavano ore di lavoro umano
Insoddisfazione degli utenti
Carico sul server

Caso positivo

È stata effettuata un'analisi del piano di esecuzione, è stato aggiunto un indice su un campo realmente filtrante, sono state aggiornate le statistiche. Il tempo di esecuzione della query è sceso a 20 secondi. Il carico sul server è diminuito di un ordine di grandezza.