MySQL EXPLAIN spiegato: come leggere i piani di esecuzione e ottimizzare le query SQL

1. Cos’è MySQL EXPLAIN? Perché Dovresti Usarlo?

Cos’è EXPLAIN? Un Comando per Visualizzare i Piani di Esecuzione

In MySQL, EXPLAIN è un comando utilizzato per visualizzare come viene eseguita una query SQL. È particolarmente utile per comprendere come vengono recuperati i dati nelle istruzioni SELECT e visualizza il piano di esecuzione della query.

Ad esempio, quando si esegue una query come SELECT * FROM users WHERE age > 30, EXPLAIN ti permette di vedere dettagli interni come quale indice sta utilizzando MySQL e in che ordine vengono scansionate le tabelle.

L’uso è semplice — basta aggiungere EXPLAIN all’inizio della tua query.

EXPLAIN SELECT * FROM users WHERE age > 30;

Scritto in questo modo, verranno visualizzate molteplici colonne che descrivono il piano di esecuzione della query. Ogni elemento verrà spiegato in dettaglio nelle sezioni successive.

Perché Dovresti Usarlo: Rendi Visibili le Cause delle Query Lente

Un errore comune che molti sviluppatori commettono è assumere che “se l’SQL funziona, non c’è problema”. Tuttavia, l’esecuzione lenta delle query può impattare negativamente sulle prestazioni complessive dell’applicazione.

Nei sistemi che gestiscono grandi volumi di dati, anche una singola query inefficiente può diventare un collo di bottiglia e mettere un carico significativo sul server.

È qui che EXPLAIN diventa estremamente utile. Rivedendo il piano di esecuzione, puoi vedere chiaramente se viene eseguita una scansione completa della tabella o se gli indici vengono utilizzati correttamente.

In altre parole, utilizzare EXPLAIN ti permette di identificare i colli di bottiglia delle prestazioni e determinare come ottimizzarli. L’efficacia degli indici, in particolare, diventa molto più chiara quando si analizza l’output di EXPLAIN.

Istruzioni SQL Supportate da EXPLAIN (SELECT, UPDATE, ecc.)

EXPLAIN funziona non solo con le istruzioni SELECT ma anche con le seguenti istruzioni SQL:

• SELECT
• DELETE
• INSERT
• REPLACE
• UPDATE

Ad esempio, quando si esegue un’istruzione DELETE su un grande dataset, se gli indici non vengono utilizzati correttamente, MySQL potrebbe eseguire una scansione completa della tabella, aumentando significativamente il tempo di esecuzione. Per prevenire tali problemi, è altamente efficace controllare il piano di esecuzione con EXPLAIN prima di eseguire istruzioni DELETE o UPDATE.

A seconda della versione di MySQL, potresti anche utilizzare EXPLAIN ANALYZE, che fornisce informazioni di esecuzione ancora più dettagliate. Questo verrà trattato più avanti nell’articolo.

2. Comprensione delle Colonne di Output di EXPLAIN (Con Immagine Illustrata)

Elenco e Spiegazione delle Colonne di Output Base

L’output di EXPLAIN include le seguenti colonne (leggermente diverse a seconda della versione di MySQL):

Tra queste, le quattro colonne più importanti per l’ottimizzazione delle prestazioni sono type / key / rows / Extra.

Come Leggere le Quattro Colonne Chiave: type / key / rows / Extra

1. type (Metodo di Accesso)

Questa colonna indica come MySQL accede alla tabella. Influenza direttamente le prestazioni.

Se type = ALL, significa che non viene utilizzato alcun indice e tutte le righe vengono scansionate — il metodo di accesso più lento. Idealmente, dovresti ottimizzare le query verso ref o const.

2. key (Indice Utilizzato)

Questa colonna visualizza il nome dell’indice effettivamente utilizzato.
Se non è mostrato nulla, la query probabilmente non sta utilizzando un indice.

3. rows (Righe Stimate da Scansionare)

Questo mostra quante righe MySQL stima di scansionare. Più grande è il numero, più lungo tende a essere il tempo di esecuzione. L’obiettivo è ottimizzare la tua query in modo che rows sia il più vicino possibile a 1.

4. Extra (Informazioni Aggiuntive)

La colonna Extra include dettagli aggiuntivi come operazioni di ordinamento o utilizzo di tabelle temporanee.

Se vedi Using temporary o Using filesort, dovresti rivedere la tua istruzione SQL o il design degli indici.

[Illustration] Output di Esempio di EXPLAIN

EXPLAIN SELECT * FROM users WHERE age > 30;

In questo esempio, sebbene l’indice (age_index) esista, non viene effettivamente utilizzato, risultando in ALL (scansione completa della tabella). Questo indica spazio per l’ottimizzazione.

3. Imparare con Esempi: Come Usare EXPLAIN e Interpretare i Risultati

Esempio 1: Output di EXPLAIN per una Query SELECT Semplice (Con Spiegazione)

Iniziamo con una semplice query SELECT su una singola tabella.

EXPLAIN SELECT * FROM users WHERE age > 30;

Supponiamo che l’output di EXPLAIN sia simile a questo:

Spiegazione:

• type: ALL → Scansione completa della tabella. Nessun indice è utilizzato.
• key: NULL → Nessun indice è effettivamente utilizzato.
• rows: 5000 → MySQL stima che scannerà circa 5.000 righe.

Come migliorare:

Aggiungendo un indice alla colonna age, puoi migliorare significativamente le prestazioni della query.

CREATE INDEX idx_age ON users(age);

Se esegui nuovamente EXPLAIN, dovresti vedere il type cambiare in range o ref, confermando che l’indice è ora utilizzato.

Esempio 2: Analizzare l’Output di EXPLAIN per una Query con JOIN

Successivamente, diamo un’occhiata a un esempio che effettua JOIN su più tabelle.

EXPLAIN
SELECT orders.id, users.name
FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.age > 30;

Esempio di output:

Spiegazione:

• La tabella users sta effettuando una scansione completa (ALL), quindi questa è la parte da migliorare.
• Nel frattempo, la tabella orders utilizza un indice con ref, il che è efficiente.

Punti di ottimizzazione:

• Aggiungere un indice su users.age può velocizzare la scansione della tabella users.
• L’obiettivo è progettare gli indici in modo che la clausola WHERE possa filtrare le righe prima del JOIN.

Quando gli Indici Non Vengono Usati (Esempio Negativo → Esempio Positivo)

Esempio negativo: clausola WHERE che utilizza una funzione

SELECT * FROM users WHERE DATE(created_at) = '2024-01-01';

Con una query di questo tipo, l’indice diventa inutilizzabile perché la funzione DATE() trasforma il valore della colonna, impedendo a MySQL di utilizzare l’indice in modo efficiente.

Esempio migliorato: specificare un intervallo senza usare una funzione

SELECT * FROM users
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';

Ciò abilita l’indice sulla colonna created_at, consentendo a MySQL di recuperare i dati in modo efficiente.

Conclusione: Utilizzare Esempi Reali di EXPLAIN per Diagnosticare le Prestazioni

Analizzando l’output di EXPLAIN in query reali, è possibile identificare chiaramente dove si trovano i colli di bottiglia e come ottimizzarli.

• ALL → Scansione completa. Considera di aggiungere o modificare gli indici.
• key = NULL → Indice non utilizzato. Richiede indagine.
• Extra contiene Using temporary → Avviso di prestazioni.
• L’uso di funzioni o calcoli nelle condizioni può disabilitare l’uso dell’indice.

Mantenere questi punti in mente ti aiuterà a migliorare continuamente le prestazioni delle query con EXPLAIN.

4. Tecniche Pratiche di Ottimizzazione delle Query Basate sui Risultati di EXPLAIN

Fondamenti della Progettazione degli Indici per Evitare “type: ALL”

Se EXPLAIN mostra type: ALL, significa che MySQL sta eseguendo una scansione completa della tabella. È un’operazione molto costosa e diventa un collo di bottiglia importante per tabelle contenenti migliaia o milioni di righe.

Come evitarlo:

• Aggiungi indici alle colonne usate nella clausola WHERE

  CREATE INDEX idx_age ON users(age);
  

• Se hai più condizioni, considera un indice composito

  CREATE INDEX idx_status_created ON orders(status, created_at);
  

• Evita pattern LIKE che non iniziano con un prefisso

  -- Bad example (index won’t work)
  WHERE name LIKE '%tanaka%'
  
  -- Good example (index may work)
  WHERE name LIKE 'tanaka%'
  

Cosa Significa “Extra: Using temporary” e Come Risolverlo

Se la colonna Extra mostra “Using temporary”, significa che MySQL sta creando una tabella temporanea internamente per elaborare la query. Questo accade spesso quando operazioni come GROUP BY o ORDER BY non possono essere gestite solo dagli indici, quindi MySQL deve usare una memorizzazione temporanea per organizzare i dati manualmente.

Come risolverlo:

• Applica indici alle colonne usate in GROUP BY e ORDER BY

  CREATE INDEX idx_group_col ON sales(department_id);
  

• Rimuovi ordinamenti o GROUP BY non necessari dal tuo SQL
• Usa LIMIT o sottoquery per ridurre i dati target

Capire Cosa Ti Dicono “rows” e “key” per Migliorare le Prestazioni

La colonna rows indica quante righe MySQL prevede di dover leggere dalla tabella. Ad esempio, una query che mostra rows = 100000 può impattare significativamente sulle prestazioni.

Quando questo valore è grande, probabilmente devi applicare indici che riducono il numero di righe scansionate o riscrivere le tue condizioni.

D’altra parte, la colonna key mostra l’indice effettivamente utilizzato. Se è NULL, è un avvertimento che nessun indice viene utilizzato.

Checklist di ottimizzazione:

• Se rows è grande → I tuoi filtri sono efficaci? Gli indici vengono utilizzati correttamente?
• Se key = NULL → Stai usando pattern in WHERE/JOIN che impediscono l’utilizzo degli indici?

Rendi EXPLAIN e l’Ottimizzazione un’Abitudine

Per ottimizzare le query in modo efficace, l’approccio base è ripetere questo ciclo: scrivi → verifica con EXPLAIN → migliora → verifica di nuovo.

Tieni presente questo flusso di lavoro:

1. Scrivi la query normalmente
2. Verifica il piano di esecuzione con EXPLAIN
3. Rivedi type , key , rows , e Extra
4. Se c’è un collo di bottiglia, rivedi gli indici o riscrivi la query
5. Esegui di nuovo EXPLAIN per confermare i miglioramenti

Le prestazioni delle query sono influenzate non solo dagli indici ma anche da come è scritta la query stessa. Confronti semplici (invece di funzioni) e condizioni dirette possono essere sorprendentemente efficaci.

5. Analisi Visiva con MySQL Workbench Visual EXPLAIN

Verifica i Piani di Esecuzione Visivamente con uno Strumento GUI

MySQL Workbench è uno strumento GUI specializzato per l’amministrazione e lo sviluppo MySQL. Uno dei suoi più grandi vantaggi è che può visualizzare graficamente i piani di esecuzione, che sono spesso difficili da leggere nell’output del terminale.

Con Visual EXPLAIN, puoi rivedere le seguenti informazioni in una struttura ad albero:

• Ordine di accesso di ogni tabella
• Tipo di JOIN utilizzato
• Stato di utilizzo degli indici
• Se sta avvenendo una scansione completa della tabella
• Operazioni di filtraggio e ordinamento dei dati

Poiché il piano è visualizzato graficamente, anche i principianti possono identificare più facilmente dove esistono colli di bottiglia nelle prestazioni.

[With Images] Come Usare e Leggere Visual EXPLAIN (Passo per Passo)

Segui questi passaggi per usare Visual EXPLAIN:

1. Avvia MySQL Workbench e apri la connessione al tuo database → Assicurati che la connessione sia configurata in anticipo.
2. Inserisci la tua query target nell’editor SQL

   SELECT * FROM users WHERE age > 30;
   

3. Clicca sull’icona “EXPLAIN VISUAL” accanto al pulsante EXPLAIN → O fai clic destro e seleziona “Visual Explain” dal menu.
4. Il piano di esecuzione verrà visualizzato graficamente Quando clicchi su ciascun nodo (tabella), appaiono informazioni dettagliate come le seguenti:

• Metodo di accesso (ALL, ref, range, ecc.)
• Indice utilizzato
• Righe stimate (rows)
• Condizioni di filtro e metodo JOIN

Nota:
In Visual EXPLAIN, i colori e le icone dei nodi aiutano a evidenziare operazioni pesanti o parti inefficienti.
Presta particolare attenzione ai nodi evidenziati in rosso, poiché indicano tipicamente problemi di prestazioni.

Anche i Principianti Possono Trovare Facilmente i Colli di Bottiglia

L’output EXPLAIN basato su testo può sembrare travolgente all’inizio, ma Visual EXPLAIN fa risaltare visivamente le aree problematiche.

Ad esempio, diventa più facile identificare:

• Tabelle che usano type: ALL
• Blocchi di query che mostrano Using temporary
• Pattern con JOIN non necessari
• Tabelle dove gli indici non vengono utilizzati

Con la sua interfaccia GUI, puoi formare rapidamente ipotesi di ottimizzazione, ed è anche utile per condividere e rivedere le prestazioni SQL all’interno di un team.

Visual EXPLAIN è particolarmente prezioso per utenti SQL principianti a intermedi.
Se non sei sicuro di come interpretare i risultati EXPLAIN, prova a usare questa funzionalità.

6. Domande Frequenti (FAQ)

Q1. Quando dovrei usare EXPLAIN?

A. Dovresti usare EXPLAIN ogni volta che sei incerto sulla velocità di esecuzione di una query — soprattutto se una query “sembra lenta”. È anche utile quando vuoi verificare se una query appena creata utilizza correttamente gli indici.

Controllando il piano di esecuzione prima del deployment, puoi identificare i rischi di prestazioni in anticipo.

Q2. L’output mostra type = ALL. Cosa devo fare?

A. type: ALL significa che MySQL sta eseguendo una scansione completa della tabella. È un’operazione ad alto costo e può degradare significativamente le prestazioni, soprattutto su tabelle grandi.

Considera le seguenti azioni:

• Aggiungi indici alle colonne usate nella clausola WHERE
• Evita funzioni o operazioni che disabilitano l’uso degli indici
• Evita SELECT * e recupera solo le colonne necessarie

Q3. “Using temporary” nella colonna Extra è un problema?

A. Using temporary indica che MySQL sta creando internamente una tabella temporanea per elaborare la query. Questo accade spesso con GROUP BY o ORDER BY, e può aumentare i costi di memoria e I/O su disco.

Possibili soluzioni includono:

• Aggiungi indici alle colonne usate in GROUP BY / ORDER BY
• Riduci ordinamenti o aggregazioni non necessari
• Usa LIMIT o sottoquery per ridurre il set di dati

Q4. Come utilizzo Visual EXPLAIN?

A. Puoi usare lo strumento ufficiale di MySQL “MySQL Workbench” per visualizzare facilmente i risultati di EXPLAIN in una GUI. Inserisci semplicemente la tua query e clicca sul pulsante “Visual Explain”.

Questo è particolarmente consigliato per:

• Utenti che trovano difficile leggere l’output testuale di EXPLAIN
• Chi vuole comprendere visivamente JOIN complessi
• Team che revisionano le prestazioni SQL insieme

Q5. Perché il mio indice non viene usato anche se esiste?

A. Anche se un indice esiste, MySQL non lo utilizza sempre. Gli indici possono essere ignorati in casi come:

• Usare funzioni o espressioni nella clausola WHERE (es. WHERE YEAR(created_at) = 2024 )
• Bassa cardinalità (bassa distribuzione dei valori), dove una scansione completa è considerata più veloce
• L’ordine delle colonne non corrisponde a una definizione di indice composito

Per confermare se un indice viene usato correttamente, controlla sempre la colonna key in EXPLAIN.

7. Riepilogo: Usa EXPLAIN per Scoprire Opportunità di Ottimizzazione SQL

L’ottimizzazione delle prestazioni in MySQL non riguarda solo l’aggiunta di indici.
Lo strumento essenziale per identificare quali query sono colli di bottiglia, perché sono lente e come risolverle è EXPLAIN.

In questo articolo, abbiamo coperto i seguenti punti chiave:

✅ Il Ruolo e l’Uso Base di EXPLAIN

• Basta aggiungere EXPLAIN prima di una query per controllare il suo piano di esecuzione
• Problemi come scansioni complete (ALL) e Using temporary diventano visibili

✅ Come Leggere le Colonne di Output e Valutare le Prestazioni

• Le quattro colonne più importanti sono type, key, rows e Extra
• Evita scansioni complete della tabella e punta a un corretto utilizzo degli indici
• Fai attenzione quando vedi Using temporary o Using filesort

✅ Diagnosi Pratica e Ottimizzazione Attraverso Esempi Reali

• Non solo aggiungere indici, ma anche migliorare la sintassi SQL è importante
• Anche query complesse con JOIN o sottoquery possono essere analizzate usando EXPLAIN
• Raffinare continuamente le query basandosi sui piani di esecuzione è il modo più veloce per migliorare le prestazioni

✅ Usa Strumenti GUI per la Conferma Visiva

• Usa “Visual EXPLAIN” in MySQL Workbench per visualizzare graficamente i piani di esecuzione
• Più semplice per i principianti identificare i colli di bottiglia visivamente
• Utile per discussioni di team e revisioni delle prestazioni SQL

✅ Copertura FAQ per Scenari Reali

• Spiegati cause e soluzioni per problemi come type=ALL e key=NULL
• Forniti esempi del perché gli indici potrebbero non essere usati

✍️ Fai di EXPLAIN un’Abitudine per Migliorare le Tue Competenze SQL

Se sviluppi l’abitudine di controllare le query con EXPLAIN ogni volta che scrivi SQL, inizierai naturalmente a scrivere query più veloci e più efficienti.

Non è solo un trucco tecnico — è parte dello sviluppo di una alfabetizzazione SQL professionale.

• Esegui EXPLAIN subito dopo aver scritto una query
• Correggi subito i piani di esecuzione sospetti
• Progetta indici efficienti con attenzione

Padroneggiando questo ciclo, le tue competenze MySQL miglioreranno costantemente.

Speriamo che questo articolo diventi il tuo primo passo verso una migliore ottimizzazione delle query.

Se hai domande o desideri che vengano trattati ulteriori argomenti, sentiti libero di lasciare un commento!