MySQL EXPLAIN ANALYZE spiegato: leggi i piani di esecuzione e ottimizza le query (Guida 8.0)

1. Introduzione

Piani di esecuzione: essenziali per l’ottimizzazione delle prestazioni del database

Nelle applicazioni web e nei sistemi aziendali, le prestazioni del database sono un fattore critico che influisce direttamente sul tempo di risposta complessivo. Quando si utilizza MySQL in particolare, comprendere il “piano di esecuzione” è essenziale per valutare l’efficienza delle query. Il comando tradizionale EXPLAIN visualizza il piano di esecuzione prima di eseguire un’istruzione SQL e da tempo fornisce agli sviluppatori preziose informazioni.

“EXPLAIN ANALYZE” introdotto in MySQL 8.0

Introdotto in MySQL 8.0.18, EXPLAIN ANALYZE è un potente miglioramento del tradizionale EXPLAIN. Mentre EXPLAIN forniva solo un “piano teorico”, EXPLAIN ANALYZE esegue effettivamente la query e restituisce dati misurati come il tempo di esecuzione e il numero di righe elaborate. Questo consente un’identificazione più accurata dei colli di bottiglia e la convalida dei risultati dell’ottimizzazione delle query.

Perché EXPLAIN ANALYZE è importante

Ad esempio, l’ordine dei JOIN, l’uso degli indici e le condizioni di filtro influenzano significativamente il tempo di esecuzione. Utilizzando EXPLAIN ANALYZE, è possibile confermare visivamente come si comporta un’istruzione SQL e determinare dove esistono inefficienze e cosa dovrebbe essere ottimizzato. Questo è particolarmente indispensabile quando si lavora con grandi set di dati o query complesse.

Scopo di questo articolo e pubblico di riferimento

Questo articolo spiega tutto, dalle basi di EXPLAIN ANALYZE di MySQL all’interpretazione del suo output e all’applicazione di tecniche pratiche di ottimizzazione. È destinato a sviluppatori e ingegneri dell’infrastruttura che utilizzano regolarmente MySQL, nonché a ingegneri interessati al tuning delle prestazioni. Per garantire chiarezza anche ai principianti, includiamo spiegazioni della terminologia ed esempi concreti lungo tutto il testo.

2. Differenze tra EXPLAIN e EXPLAIN ANALYZE

Il ruolo e l’uso base di EXPLAIN

Il EXPLAIN di MySQL è uno strumento di analisi utilizzato per comprendere in anticipo come verrà eseguita un’istruzione SQL (in particolare una SELECT). Consente di confermare piani di esecuzione come l’uso degli indici, l’ordine dei join e gli intervalli di ricerca.

Ad esempio:

EXPLAIN SELECT * FROM users WHERE age > 30;

Quando questo comando viene eseguito, MySQL non esegue realmente la query, ma visualizza invece come intende elaborarla in forma tabellare. L’output include informazioni come l’indice utilizzato (key), il metodo di accesso (type) e il numero stimato di righe (rows).

Il ruolo e le funzionalità di EXPLAIN ANALYZE

Al contrario, EXPLAIN ANALYZE, introdotto in MySQL 8.0.18, esegue la query e visualizza il piano di esecuzione basato su valori misurati effettivamente. Questo rende possibile confermare dettagli che non erano visibili nel tradizionale EXPLAIN, come il tempo di elaborazione reale e il numero di righe effettivamente elaborate.

Esempio:

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

Questo comando esegue la query e restituisce un output che include:

• Tempo di esecuzione per ogni passo del piano (ad es., 0.0022 sec )
• Il numero reale di righe lette (rows)
• La struttura di elaborazione (facilmente visualizzabile usando il formato TREE)

Riepilogo delle principali differenze

Quale dovresti usare?

• Usa EXPLAIN quando vuoi controllare rapidamente la struttura della query.
• Usa EXPLAIN ANALYZE quando hai bisogno di dettagli concreti sul tempo di esecuzione e sul costo della query.

Specialmente in scenari di ottimizzazione delle prestazioni, EXPLAIN ANALYZE consente di ottimizzare basandosi su dati di esecuzione reali piuttosto che su stime, rendendolo uno strumento estremamente potente.

3. Formati di output di EXPLAIN ANALYZE

Tre formati di output: TRADITIONAL, JSON e TREE

Il EXPLAIN ANALYZE di MySQL può restituire risultati in diversi formati a seconda del tuo scopo. In MySQL 8.0 e versioni successive, sono disponibili i seguenti tre formati.

Esaminiamo più da vicino le differenze.

Formato TRADITIONAL (Predefinito)

TRADITIONAL output è simile allo stile classico EXPLAIN e consente di esaminare i piani di esecuzione in una forma familiare. Se esegui EXPLAIN ANALYZE senza specificare un formato, il risultato viene generalmente mostrato in questo formato.

Esempio di output (estratto):

-> Filter: (age > 30)  (cost=0.35 rows=10) (actual time=0.002..0.004 rows=8 loops=1)

• cost : costo stimato
• actual time : tempo misurato
• rows : numero stimato di righe elaborate (prima dell’esecuzione)
• loops : conteggio dei cicli (soprattutto importante per JOIN)

Il formato TRADITIONAL è facile da scansionare e comprendere per gli esseri umani, rendendolo adatto ai principianti e a controlli rapidi.

Formato JSON

Il formato JSON è più dettagliato e più facile da gestire programmaticamente. L’output è strutturato, con ogni nodo rappresentato come un oggetto annidato.

Comando:

EXPLAIN ANALYZE FORMAT=JSON SELECT * FROM users WHERE age > 30;

Parte dell’output (formattato):

{
  "query_block": {
    "table": {
      "table_name": "users",
      "access_type": "range",
      "rows_examined_per_scan": 100,
      "actual_rows": 80,
      "filtered": 100,
      "cost_info": {
        "query_cost": "0.35"
      },
      "timing": {
        "start_time": 0.001,
        "end_time": 0.004
      }
    }
  }
}

Questo formato è meno leggibile visivamente, ma è estremamente comodo quando si desidera analizzare i dati e inserirli in strumenti di analisi o dashboard.

Formato TREE (Leggibile e Ottimo per Visualizzare la Struttura)

Il formato TREE visualizza la struttura di esecuzione della query come un albero, facilitando la comprensione dell’ordine di elaborazione di JOIN e sottoquery.

Comando:

EXPLAIN ANALYZE FORMAT=TREE SELECT * FROM users WHERE age > 30;

Esempio di output (semplificato):

-> Table scan on users  (actual time=0.002..0.004 rows=8 loops=1)

Per query complesse, l’annidamento può apparire così:

-> Nested loop join
    -> Table scan on users
    -> Index lookup on orders using idx_user_id

Il formato TREE è particolarmente utile per query con molti JOIN o annidamenti complessi, dove è necessario comprendere il flusso di elaborazione.

Quale Formato Dovresti Usare?

Scegli il formato che meglio si adatta al tuo obiettivo e analizza il piano di esecuzione nello stile più leggibile e analizzabile.

4. Come Interpretare i Piani di Esecuzione

Perché È Necessario Leggere i Piani di Esecuzione

Le prestazioni delle query MySQL possono variare notevolmente a seconda del volume dei dati e della disponibilità degli indici. Interpretando correttamente l’output del piano di esecuzione da EXPLAIN ANALYZE, è possibile identificare in modo oggettivo dove il lavoro viene sprecato e cosa dovrebbe essere migliorato. Questa abilità è una pietra angolare dell’ottimizzazione delle prestazioni, soprattutto per le query che gestiscono grandi set di dati o join complessi.

Struttura di Base di un Piano di Esecuzione

L’output di EXPLAIN ANALYZE include informazioni come le seguenti (spiegate qui in base all’output in stile TRADITIONAL):

-> Filter: (age > 30)  (cost=0.35 rows=10) (actual time=0.002..0.004 rows=8 loops=1)

Questa singola riga contiene più campi importanti.

Come Leggere i Campi Chiave

1. cost vs. actual time

• cost è una stima interna calcolata da MySQL ed è usata per la valutazione relativa.
• actual time riflette il tempo reale trascorso ed è più importante per l’analisi delle prestazioni.

Ad esempio:

(cost=0.35 rows=100) (actual time=0.002..0.004 rows=100)

Se le stime e le misurazioni corrispondono strettamente, il piano di esecuzione è probabilmente accurato. Se la differenza è grande, le statistiche della tabella potrebbero essere imprecise.

2. rows vs. actual rows

• rows è il numero di righe che MySQL prevede di leggere.
• actual rows è il numero di righe effettivamente lette (incluse tra parentesi nell’output in stile TRADITIONAL).

Se c’è una grande discrepanza, potrebbe essere necessario aggiornare le statistiche o riconsiderare il design degli indici.

3. loops

If loops=1, il passaggio viene eseguito una volta. Con i JOIN o le sottoquery, potresti vedere loops=10 o loops=1000. Più grande è il valore, più è probabile che i loop annidati stiano causando un’elaborazione pesante.

Comprendere la Struttura Annidata dei Piani di Esecuzione

Quando più tabelle sono unite, il piano di esecuzione viene mostrato come un albero (soprattutto chiaro nel formato TREE).

Esempio:

-> Nested loop join
    -> Table scan on users
    -> Table scan on orders

Problema

• Entrambe le tabelle vengono scansionate completamente, risultando in un alto costo di join.

Contromisura

• Aggiungi un indice su users.age e filtra prima per ridurre il carico del join.

Come Identificare i Collo di Bottiglia delle Prestazioni

Concentrarsi sui seguenti punti rende più facile individuare i colli di bottiglia:

• Nodi con tempo reale lungo e molte righe : consumano la maggior parte del tempo di esecuzione
• Luoghi in cui si verifica una scansione completa della tabella : probabilmente mancano indici o sono inutilizzati
• Passaggi con molti loop : indicano un ordine di JOIN inefficiente o annidamento
• Grandi differenze tra le righe stimate e quelle reali : suggeriscono statistiche inaccurate o accessi eccessivi ai dati

Usa queste intuizioni come base per le tecniche di “Ottimizzazione delle Query” introdotte nella sezione successiva.

5. Esempi Pratici di Ottimizzazione delle Query

Cos’è l’Ottimizzazione delle Query?

L’ottimizzazione delle query si riferisce alla revisione e al miglioramento delle istruzioni SQL affinché possano essere eseguite più efficientemente. In base a come MySQL elabora internamente le query (piani di esecuzione), si applicano miglioramenti come l’aggiunta di indici, la regolazione dell’ordine dei join e l’eliminazione di elaborazioni non necessarie.

Qui dimostriamo come migliorare le query usando EXPLAIN ANALYZE con esempi concreti.

Esempio 1: Miglioramento della Velocità con gli Indici

Prima dell’Ottimizzazione

SELECT * FROM users WHERE email = 'example@example.com';

Piano di Esecuzione (Estratto)

-> Table scan on users  (cost=10.5 rows=100000) (actual time=0.001..0.230 rows=1 loops=1)

Problema

• L’output mostra Table scan, il che significa che viene eseguita una scansione completa della tabella. Con dataset di grandi dimensioni, ciò porta a ritardi significativi.

Soluzione: Aggiungere un Indice

CREATE INDEX idx_email ON users(email);

Piano di Esecuzione Dopo l’Ottimizzazione

-> Index lookup on users using idx_email  (cost=0.1 rows=1) (actual time=0.001..0.002 rows=1 loops=1)

Risultato

• Il tempo di esecuzione è stato notevolmente ridotto.
• La scansione completa della tabella è stata evitata utilizzando l’indice.

Esempio 2: Ottimizzare l’Ordine dei Join

Prima dell’Ottimizzazione

SELECT * FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.age > 30;

Piano di Esecuzione (Estratto)

-> Nested loop join
    -> Table scan on orders
    -> Table scan on users

Problema

• Entrambe le tabelle vengono scansionate completamente, risultando in costi di join elevati.

Soluzione

• Aggiungi un indice su users.age e filtra prima per ridurre la dimensione del target del join.

  CREATE INDEX idx_age ON users(age);
  

Piano di Esecuzione Dopo l’Ottimizzazione

-> Nested loop join
    -> Index range scan on users using idx_age
    -> Index lookup on orders using idx_user_id

Risultato

• I target del JOIN vengono filtrati per primi, riducendo il carico complessivo di elaborazione.

Esempio 3: Revisionare una Sottoquery

Prima dell’Ottimizzazione

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

Problema

• La sottoquery potrebbe essere valutata ripetutamente, degradando le prestazioni.

Soluzione: Riscrivere come JOIN

SELECT DISTINCT users.*
FROM users
JOIN orders ON users.id = orders.user_id
WHERE orders.total > 1000;

Risultato

• Il piano di esecuzione è ottimizzato per l’elaborazione dei JOIN, e gli indici sono più probabilmente utilizzati.

L’Importanza del Confronto Prima/Dopo

Using EXPLAIN ANALYZE, è possibile verificare i risultati dell’ottimizzazione con valori misurati effettivi. Confrontando il tempo di esecuzione e il numero di righe prima e dopo le migliorie, si garantisce che gli sforzi di tuning si basino su reali guadagni di prestazioni anziché su supposizioni.

Considerazioni importanti nell’ottimizzazione

• Aggiungere troppi indici può essere controproducente (prestazioni più lente per INSERT/UPDATE).
• I piani di esecuzione dipendono dal volume dei dati e dalle statistiche, quindi è necessaria una validazione per ambiente.
• Una singola ottimizzazione raramente risolve tutto. L’analisi dei colli di bottiglia è la prima cosa da fare.

6. Precauzioni e migliori pratiche

Note importanti quando si utilizza EXPLAIN ANALYZE

Sebbene EXPLAIN ANALYZE sia estremamente potente, un uso improprio può portare a fraintendimenti o addirittura a rischi operativi. Tenere a mente i seguenti punti garantisce un’analisi delle query sicura ed efficace.

1. Evitare di eseguire incautamente in produzione

Poiché EXPLAIN ANALYZE esegue effettivamente la query, usarlo per errore con istruzioni di modifica (INSERT/UPDATE/DELETE) può modificare i dati.

• In generale, usarlo solo con istruzioni SELECT.
• Preferire l’esecuzione in un ambiente di staging o test piuttosto che in produzione.

2. Considerare l’impatto della cache

MySQL può restituire risultati dalla cache se la stessa query viene eseguita più volte. Di conseguenza, il tempo di esecuzione riportato da EXPLAIN ANALYZE può differire dal comportamento reale.

Contromisure:

• Svuotare la cache prima dell’esecuzione (RESET QUERY CACHE;).
• Eseguire più volte e valutare in base ai valori medi.

3. Mantenere le statistiche aggiornate

MySQL costruisce i piani di esecuzione basandosi sulle statistiche di tabelle e indici. Se le statistiche sono obsolete, sia EXPLAIN che EXPLAIN ANALYZE possono fornire informazioni fuorvianti.

Dopo grandi operazioni di INSERT o DELETE, aggiornare le statistiche usando ANALYZE TABLE.

ANALYZE TABLE users;

4. Gli indici non sono una soluzione miracolosa

Sebbene gli indici migliorino spesso le prestazioni, troppi indici rallentano le operazioni di scrittura.

Scegliere tra indici compositi e indici a colonna singola è altrettanto importante. Progettare gli indici con cura in base ai pattern delle query e alla frequenza di utilizzo.

5. Non giudicare solo in base al tempo di esecuzione

I risultati di EXPLAIN ANALYZE riflettono solo le prestazioni di una singola query. Nelle applicazioni reali, la latenza di rete o l’elaborazione backend possono essere il collo di bottiglia reale.

Pertanto, analizzare le query nel contesto dell’intera architettura del sistema.

Riepilogo delle migliori pratiche

7. Domande frequenti (FAQ)

Q1. Da quale versione è disponibile EXPLAIN ANALYZE?

A.
EXPLAIN ANALYZE di MySQL è stato introdotto nella versione 8.0.18 e successive. Non è supportato nelle versioni precedenti alla 8.0, quindi è consigliabile verificare la versione di MySQL prima di usarlo.

Q2. L’esecuzione di EXPLAIN ANALYZE può modificare i dati?

A.
EXPLAIN ANALYZE esegue internamente la query.
Quando viene usato con una istruzione SELECT, non modifica i dati.

Pertanto, quando viene usato con una istruzione SELECT, non modifica i dati.

Tuttavia, se lo si utilizza per errore con INSERT, UPDATE o DELETE, i dati verranno modificati proprio come con una query normale.

Per sicurezza, è consigliato eseguire le analisi in un database di test o di staging piuttosto che in produzione.

Q3. EXPLAIN da solo è sufficiente?

A.
EXPLAIN è sufficiente per rivedere il piano di esecuzione “stimato”. Tuttavia, non fornisce valori misurati come tempo di esecuzione reale o conteggio reale delle righe.

Se è necessario un tuning serio delle query o si vuole verificare gli effetti dell’ottimizzazione, EXPLAIN ANALYZE è più utile.

Q4. Quanto sono precisi valori come “loops” e “actual time”?

A.
Valori come actual time e loops sono metriche di esecuzione reali misurate internamente da MySQL. Tuttavia, possono variare leggermente a seconda delle condizioni del sistema operativo, dello stato della cache e del carico del server.

Per questo motivo, non fare affidamento su una singola misurazione. Invece, esegui la query più volte e valuta le tendenze.

Q5. Cosa rappresenta esattamente il “cost”?

A.
cost è un valore stimato calcolato dal modello di costo interno di MySQL. Rappresenta una valutazione relativa dei costi CPU e I/O. Non è espresso in secondi.

Ad esempio, se vedi (cost=0.3) e (cost=2.5), quest’ultimo è stimato più costoso in termini relativi.

Q6. Quali sono i vantaggi dell’utilizzo del formato JSON o TREE?

A.

• Formato JSON: output strutturato facile da analizzare programmaticamente. Utile per strumenti di automazione e dashboard.
• Formato TREE: rende il flusso di esecuzione e l’annidamento visivamente chiari. Ideale per comprendere query complesse e l’ordine dei JOIN.

Scegli il formato che meglio si adatta al tuo scopo.

Q7. Cosa devo fare se non riesco a migliorare le prestazioni dopo aver esaminato il piano di esecuzione?

A.
Considera approcci aggiuntivi come:

• Ridisegnare gli indici (indici compositi o indici covering)
• Riscrivere le query (subquery → JOIN, rimuovendo colonne SELECT non necessarie)
• Utilizzare viste o tabelle temporanee
• Rivedere la configurazione di MySQL (dimensioni dei buffer, allocazione della memoria, ecc.)

L’ottimizzazione delle prestazioni raramente ha successo con una singola tecnica. È essenziale un approccio completo e iterativo.