Quando si lavora con database di dimensioni medio-grandi, scrivere una query “corretta” dal punto di vista sintattico non è sufficiente. Una query può restituire il risultato giusto e allo stesso tempo essere estremamente inefficiente. È qui che entra in gioco EXPLAIN, uno degli strumenti più importanti messi a disposizione da MySQL per comprendere il comportamento interno dell’ottimizzatore.
Il comando EXPLAIN (o DESCRIBE in alcune varianti) permette di ottenere il piano di esecuzione di una query SQL. In altre parole, MySQL ci mostra come intende eseguire una query prima ancora di farlo realmente. In particolare, EXPLAIN consente di capire:
- quali indici verranno utilizzati
- quali tabelle verranno scansionate per prime
- quante righe MySQL prevede di analizzare
- se verranno create tabelle temporanee
- se verrà utilizzato un filesort
Queste informazioni sono fondamentali per ottimizzare query lente e prevenire problemi di performance in ambienti di produzione.
Esempio pratico: una tabella per la gestione delle visite
Immaginiamo di essere i proprietari di una catena di barbieri. Per tenere traccia delle visite dei clienti, utilizziamo una tabella chiamata visite.
CREATE TABLE visite (
visit_id INT AUTO_INCREMENT PRIMARY KEY,
service_id INT NOT NULL,
date_book DATETIME NOT NULL,
user_id INT NOT NULL,
location_id INT NOT NULL,
status_id INT NOT NULL,
note TEXT,
FOREIGN KEY (service_id) REFERENCES servizi(service_id),
FOREIGN KEY (user_id) REFERENCES utenti(user_id),
FOREIGN KEY (location_id) REFERENCES posizioni(location_id)
) ENGINE=InnoDB;
Supponiamo ora di voler sapere quale sede (location) ha il maggior numero di visite. Una query tipica potrebbe essere la seguente:
EXPLAIN
SELECT
location_id,
COUNT(*) AS conteggio
FROM
visite
GROUP BY
location_id
ORDER BY
conteggio DESC
LIMIT 1;
È importante sottolineare un concetto fondamentale: EXPLAIN non esegue la query. MySQL analizza la query e restituisce il piano di esecuzione stimato, senza accedere realmente ai dati. Questo lo rende uno strumento sicuro anche su database di produzione.
Output di EXPLAIN: lettura della tabella dei risultati
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | SIMPLE | visite | NULL | index | location_id | location_id | 4 | NULL | 190608 | 100.00 | Using index; Using temporary; Using filesort |
Questa tabella contiene una grande quantità di informazioni. Alcune colonne sono intuitive, altre meritano un’analisi più approfondita.
Colonne principali del piano di esecuzione
| Nome colonna | Descrizione |
| type | Indica la strategia utilizzata da MySQL per accedere ai dati. L’ordine seguente va dal più efficiente al meno efficiente:
Una scansione ALL indica una scansione completa della tabella ed è quasi sempre un campanello d’allarme. |
| possible_keys | Elenco degli indici che MySQL potrebbe teoricamente utilizzare per eseguire la query. |
| key | L’indice effettivamente scelto dall’ottimizzatore. |
| rows | Numero stimato di righe che MySQL dovrà leggere. Nel nostro caso quasi l’intera tabella, segno che la query non è ottimale. |
| Extra | Contiene informazioni aggiuntive, tra cui:
La presenza combinata di Using temporary e Using filesort indica operazioni costose. |
EXPLAIN FORMAT=JSON
A partire da MySQL 5.6 è possibile ottenere il piano di esecuzione in formato JSON. Questo formato è estremamente utile perché fornisce molte più informazioni rispetto alla visualizzazione tabellare.
Aggiungiamo prima un indice composito:
ALTER TABLE visite ADD INDEX user_status_idx (user_id, status_id);
Ora analizziamo una query con EXPLAIN in formato JSON:
EXPLAIN FORMAT=JSON SELECT * FROM visite WHERE user_id = 133;
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "7.00"
},
"table": {
"table_name": "visite",
"access_type": "ref",
"possible_keys": ["user_status_idx"],
"key": "user_status_idx",
"used_key_parts": ["user_id"],
"key_length": "4",
"rows_examined_per_scan": 20,
"rows_produced_per_join": 20,
"filtered": "100.00"
}
}
}
Da questo output possiamo vedere chiaramente che l’indice è stato utilizzato solo parzialmente, limitandosi alla colonna user_id.
Aggiungendo anche la condizione sullo stato:
EXPLAIN FORMAT=JSON SELECT * FROM visite WHERE user_id = 133 AND status_id IN (3,4,5);
In questo caso MySQL utilizza completamente l’indice composito, migliorando ulteriormente l’efficienza della query.
EXPLAIN ANALYZE: il piano reale di esecuzione
Se utilizzi MySQL 8.0.18 o superiore, puoi sfruttare EXPLAIN ANALYZE. A differenza di EXPLAIN tradizionale, questa versione esegue realmente la query e misura i tempi effettivi di ogni operazione.
EXPLAIN ANALYZE SELECT posizioni.nome, COUNT(*) AS count_visits FROM visite INNER JOIN posizioni USING (location_id) GROUP BY location_id;
Il risultato mostra passo dopo passo l’ordine delle operazioni, i tempi reali e il numero di righe elaborate.
Analizzando i dati emerge chiaramente che:
- l’aggregazione tramite tabella temporanea è l’operazione più costosa
- le join annidate incidono fortemente sui tempi complessivi
- la presenza di indici riduce drasticamente il numero di righe esaminate
Errori comuni nell’interpretazione di EXPLAIN
Uno degli errori più frequenti quando si inizia a utilizzare EXPLAIN è pensare che basti “dare un’occhiata” all’output per capire se una query è ottimizzata oppure no. In realtà, l’interpretazione corretta del piano di esecuzione richiede attenzione, contesto e una buona comprensione di come lavora il Query Optimizer.
In questa sezione analizzeremo gli errori più comuni che portano a conclusioni sbagliate, ottimizzazioni inutili o addirittura peggioramenti delle prestazioni.
Errore 1: pensare che EXPLAIN mostri ciò che accade realmente
Il primo errore concettuale è credere che EXPLAIN descriva ciò che MySQL fa davvero durante l’esecuzione della query. EXPLAIN, nella sua forma classica, mostra solo il piano di esecuzione stimato, basato su statistiche, cardinalità e distribuzione dei dati. Non misura tempi reali e non garantisce che il piano scelto sia identico a quello utilizzato in fase di esecuzione. Questo significa che due query con lo stesso output di EXPLAIN possono avere tempi di esecuzione molto diversi. Per questo motivo, quando possibile, è sempre consigliabile affiancare:
- EXPLAIN
- EXPLAIN FORMAT=JSON
- EXPLAIN ANALYZE
Solo EXPLAIN ANALYZE consente di osservare il comportamento reale della query, misurando i tempi effettivi di ogni singola operazione.
Errore 2: fissarsi esclusivamente sulla colonna type
Molti sviluppatori imparano rapidamente che il valore della colonna type è importante e che una scansione ALL è generalmente un cattivo segnale. Questo è vero, ma limitarsi a questo dato è un errore. Una scansione completa della tabella non è sempre sbagliata. Se la tabella contiene poche righe, oppure se la query deve comunque leggere una grande percentuale dei dati, una scansione completa può essere la scelta migliore. Esempio:
EXPLAIN SELECT * FROM configurazioni_globali;
Se la tabella contiene poche decine di record, un type = ALL non rappresenta alcun problema.
Il valore di type va sempre interpretato insieme a:
- numero stimato di righe
- dimensione della tabella
- frequenza di esecuzione della query
Errore 3: ignorare completamente la colonna rows
La colonna rows indica il numero stimato di righe che MySQL prevede di analizzare. Molto spesso questo valore viene ignorato, ma è uno degli indicatori più importanti. Una query che utilizza un indice ma analizza comunque centinaia di migliaia di righe può essere più lenta di una query senza indice che analizza poche righe. Esempio:
EXPLAIN SELECT * FROM visite WHERE data_recensione > '2023-01-01';
Se la maggior parte delle righe soddisfa la condizione, l’indice sulla data potrebbe essere utilizzato ma non portare reali benefici. Il numero di righe è spesso più importante del tipo di accesso utilizzato.
Errore 4: considerare possible_keys come indici realmente utilizzati
La colonna possible_keys elenca gli indici che MySQL potrebbe utilizzare, ma non dice nulla su quelli effettivamente scelti. Molti sviluppatori vedono il proprio indice comparire in possible_keys e danno per scontato che venga utilizzato. L’unica colonna che conta davvero è key.
EXPLAIN SELECT * FROM visite WHERE user_id = 133;
Se key è NULL, significa che nessun indice è stato utilizzato, anche se possible_keys ne elenca diversi.
Errore 5: sottovalutare il campo Extra
La colonna Extra viene spesso ignorata perché contiene stringhe poco intuitive, ma in realtà fornisce informazioni fondamentali. Alcuni segnali da interpretare con attenzione:
- Using temporary: viene creata una tabella temporanea
- Using filesort: MySQL deve ordinare i dati manualmente
- Using where: filtro applicato dopo la lettura
- Using index: indice di copertura
La combinazione Using temporary + Using filesort è spesso indice di una query costosa, soprattutto su grandi dataset.
Errore 6: forzare un indice senza verificare il risultato
Dopo aver analizzato EXPLAIN, molti sviluppatori tentano di “correggere” il piano di esecuzione utilizzando FORCE INDEX. Questo approccio è pericoloso. Se MySQL ignora un indice, nella maggior parte dei casi lo fa perché ha stimato che l’indice non porterà benefici.
SELECT * FROM visite FORCE INDEX (user_status_idx) WHERE user_id = 133;
Forzare un indice senza misurare le prestazioni reali può portare a query più lente e difficili da manutenere. Ogni utilizzo di FORCE INDEX dovrebbe essere accompagnato da:
- EXPLAIN ANALYZE
- test su dati reali
- monitoraggio nel tempo
Errore 7: non aggiornare le statistiche del database
EXPLAIN basa le sue stime su statistiche che possono diventare obsolete nel tempo, specialmente su tabelle che subiscono molte modifiche. Statistiche non aggiornate portano a piani di esecuzione errati e a decisioni subottimali da parte dell’optimizer.
ANALYZE TABLE visite;
Eseguire periodicamente ANALYZE TABLE aiuta MySQL a mantenere stime più accurate.
Errore 8: ottimizzare una query senza considerare il contesto
Un ultimo errore molto comune è ottimizzare una query isolata senza considerare il contesto applicativo. Una query che viene eseguita una volta al giorno può permettersi un piano di esecuzione meno efficiente rispetto a una query eseguita centinaia di volte al secondo. L’ottimizzazione deve sempre tener conto di:
- frequenza di esecuzione
- dimensione delle tabelle
- carico complessivo del database
- concorrenza delle query
Considerazioni finali
EXPLAIN non è solo uno strumento di debug, ma una vera e propria lente d’ingrandimento sul funzionamento interno di MySQL. Utilizzarlo regolarmente permette di:
- individuare query inefficienti
- verificare l’utilizzo corretto degli indici
- prevenire problemi di scalabilità
- migliorare sensibilmente le prestazioni complessive del database
La regola d’oro è sempre la stessa: non fidarsi delle sensazioni, ma dei dati. E EXPLAIN è uno dei modi migliori per ottenerli.
