Come leggere l'EXPLAIN di MySQL: possible_keys, key, Extra e l'ordine delle colonne di un indice

All'inizio della mia carriera di DBA ho sofferto molto con le query lente. Vedevo che una query ci metteva troppo, ma non sapevo come capire cosa si potesse ottimizzare. Col tempo ho imparato a leggere EXPLAIN, che è il modo di chiedere al motore come pensa di eseguire una query prima di eseguirla, e questo articolo è quello che avrei voluto che qualcuno mi spiegasse allora.

A differenza del precedente, questo parla solo di MySQL. MariaDB, nato da MySQL, restituisce quasi la stessa tabella, e dove cambia lo dico.

Le tabelle degli esempi

Duecentomila ordini di cinquemila clienti: il 90% consegnati, il 7% spediti e il 3% in attesa, con date distribuite tra il 2022 e il 2025. Le righe vengono da una serie generata con una CTE ricorsiva, che ho spiegato nell'articolo su come riempire una tabella con dati di test:

CREATE TABLE clientes (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    pais   CHAR(2)      NOT NULL
);

CREATE TABLE pedidos (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    cliente_id INT           NOT NULL,
    estado     VARCHAR(20)   NOT NULL,
    fecha      DATE          NOT NULL,
    total      DECIMAL(10,2) NOT NULL
);

SET SESSION cte_max_recursion_depth = 200000;

INSERT INTO clientes (nombre, pais)
WITH RECURSIVE serie (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM serie WHERE n < 5000
)
SELECT CONCAT('Cliente ', n), ELT(1 + n % 5, 'CR', 'MX', 'ES', 'AR', 'CO')
FROM serie;

INSERT INTO pedidos (cliente_id, estado, fecha, total)
WITH RECURSIVE serie (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM serie WHERE n < 200000
)
SELECT 1 + FLOOR(RAND() * 5000),
       CASE WHEN n % 100 < 90 THEN 'entregado'
            WHEN n % 100 < 97 THEN 'enviado'
            ELSE 'pendiente' END,
       DATE '2022-01-01' + INTERVAL FLOOR(RAND() * 1400) DAY,
       ROUND(RAND() * 1000, 2)
FROM serie;

In MariaDB la variabile del SET si chiama max_recursive_iterations.

Per ora pedidos ha solo la sua chiave primaria. Gli indici li creo strada facendo, per vedere cosa cambia nel piano con ciascuno. I dati sono casuali, quindi se ripeti gli esempi i numeri di rows non ti verranno identici ai miei; gli output sono di MySQL 8.0.

Chiedere il piano a MySQL

Basta scrivere EXPLAIN davanti al SELECT:

EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 1234;

+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | pedidos | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 200000 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+

EXPLAIN non esegue la query: restituisce il piano, con una riga per ogni tabella che la query legge. Cosa dice ogni colonna:

• id e select_type: a quale SELECT appartiene la riga. Contano quando ci sono sottoquery o UNION; in una query semplice valgono 1 e SIMPLE.
• table: la tabella, o il suo alias.
• type: come vi si accede. Dal migliore al peggiore, i più frequenti sono const (una sola riga, per chiave), eq_ref (una riga per ogni riga della tabella precedente), ref (le righe con un valore dell'indice), range (un tratto dell'indice), index (l'indice intero) e ALL (la tabella intera).
• possible_keys e key: gli indici che potrebbero servire e quello scelto. Hanno la loro sezione più avanti.
• key_len: quanti byte dell'indice usa; con un indice su più colonne dice quante colonne ha usato.
• ref: con cosa viene confrontato l'indice: una costante (const) o una colonna di un'altra tabella.
• rows: quante righe prevede di esaminare. È una stima ricavata dalle statistiche della tabella, non un conteggio.
• filtered: quale percentuale di quelle righe prevede che superi il WHERE.
• Extra: ciò che non entra nelle altre colonne: se filtra, se ordina, se crea una tabella temporanea.

In questo caso type è ALL, key è vuota e rows è 200.000: per restituire i 40 ordini di un cliente, MySQL leggerà tutti i duecentomila.

Se il tuo MySQL restituisce un albero di testo invece di questa tabella, la variabile explain_format (esiste da MySQL 8.0.32) è impostata su TREE; il MySQL più recente del mio banco di prova è configurato così per impostazione predefinita. EXPLAIN FORMAT=TRADITIONAL chiede la tabella.

possible_keys e key: avere indici non è usarli

Sono le due colonne che ho fatto più fatica a capire. possible_keys sono gli indici che l'ottimizzatore considera per la query; key, quello che userà davvero. Posso avere dieci indici in possible_keys, e se in key non ne compare nessuno, la query non usa indici.

Ci sono due motivi perché key risulti vuota. Il primo è che non ci sia nessun indice utile, e allora anche possible_keys è vuota, come nell'esempio sopra. Il secondo è che ci sia e l'ottimizzatore decida di non usarlo. Per vederlo, creo un indice sulla data:

CREATE INDEX idx_fecha ON pedidos (fecha);

Gli ordini di un mese lo usano:

EXPLAIN SELECT * FROM pedidos
WHERE fecha >= '2025-10-01' AND fecha < '2025-11-01';

+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| range | idx_fecha     | idx_fecha | 3       | NULL | 4399 |   100.00 | Using index condition |
+-------+---------------+-----------+---------+------+------+----------+-----------------------+

Quelli di un anno intero, no:

EXPLAIN SELECT * FROM pedidos
WHERE fecha >= '2024-01-01' AND fecha < '2025-01-01';

+------+---------------+------+---------+------+--------+----------+-------------+
| type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+------+---------------+------+---------+------+--------+----------+-------------+
| ALL  | idx_fecha     | NULL | NULL    | NULL | 200000 |    50.00 | Using where |
+------+---------------+------+---------+------+--------+----------+-------------+

idx_fecha è in possible_keys, ma key è vuota e type torna a essere ALL. Un anno è un quarto degli ordini, e leggere un quarto della tabella a salti, cercando ogni riga dall'indice, gli costa più che leggerla tutta in ordine. Non è un errore: è una decisione, e a volte è quella giusta. Guarda anche filtered: stima il 50% quando il conteggio reale è il 26%. Sono stime, e possono sbagliare.

La trappola più comune è un'altra: avere l'indice e scrivere la condizione in modo che non si possa usare. Gli stessi ordini di ottobre, chiesti per anno e mese:

EXPLAIN SELECT * FROM pedidos
WHERE YEAR(fecha) = 2025 AND MONTH(fecha) = 10;

+------+---------------+------+---------+------+--------+----------+-------------+
| type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+------+---------------+------+---------+------+--------+----------+-------------+
| ALL  | NULL          | NULL | NULL    | NULL | 200000 |   100.00 | Using where |
+------+---------------+------+---------+------+--------+----------+-------------+

Ora possible_keys è vuota. L'indice conserva le date in ordine, non i loro anni né i loro mesi, e per sapere quali date soddisfano YEAR(fecha) = 2025 MySQL deve calcolarlo riga per riga. Lo stesso succede con qualsiasi funzione o calcolo sulla colonna: DATE(fecha), LOWER(email), total * 1.13. La soluzione è scrivere la condizione come intervallo sulla colonna, come nel primo esempio di ottobre.

La colonna Extra

È l'ultima colonna, e quella che dice di più quando tutto il resto sembra a posto. I valori che incontro più spesso:

• Using where: il WHERE si applica alle righe dopo averle lette. Con type su ALL, vuol dire che legge la tabella intera e scarta ciò che non soddisfa.
• Using index: tutto ciò che chiede la query è dentro l'indice, quindi non arriva a leggere la tabella. È il meglio che possa comparire qui.
• Using index condition: parte del WHERE viene verificata dentro l'indice, prima di andare a prendere la riga.
• Using filesort: deve ordinare il risultato a parte, perché non lo legge già ordinato da un indice. Il nome inganna: non vuol dire che scriva su disco.
• Using temporary: crea una tabella temporanea per risolvere la query, tipicamente con un GROUP BY e un ORDER BY su colonne diverse.

Contare gli ordini di ottobre si risolve senza toccare la tabella, perché la data è nell'indice e non serve altro:

EXPLAIN SELECT COUNT(*) FROM pedidos
WHERE fecha >= '2025-10-01' AND fecha < '2025-11-01';

+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| range | idx_fecha     | idx_fecha | 3       | NULL | 4399 |   100.00 | Using where; Using index |
+-------+---------------+-----------+---------+------+------+----------+--------------------------+

Gli ordini di un giorno, dal più caro al più economico, si leggono dall'indice della data ma vanno ordinati per totale:

EXPLAIN SELECT * FROM pedidos
WHERE fecha = '2024-05-10'
ORDER BY total DESC;

+------+---------------+-----------+---------+-------+------+----------+----------------+
| type | possible_keys | key       | key_len | ref   | rows | filtered | Extra          |
+------+---------------+-----------+---------+-------+------+----------+----------------+
| ref  | idx_fecha     | idx_fecha | 3       | const |  124 |   100.00 | Using filesort |
+------+---------------+-----------+---------+-------+------+----------+----------------+

E i dieci clienti che hanno speso di più a ottobre richiedono entrambe le cose: una tabella temporanea per sommare per cliente e un ordinamento sulla somma:

EXPLAIN SELECT cliente_id, SUM(total) AS gastado
FROM pedidos
WHERE fecha >= '2025-10-01' AND fecha < '2025-11-01'
GROUP BY cliente_id
ORDER BY gastado DESC
LIMIT 10;

+-------+---------------+-----------+---------+------+------+----------+--------------------------------------------------------+
| type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                                                  |
+-------+---------------+-----------+---------+------+------+----------+--------------------------------------------------------+
| range | idx_fecha     | idx_fecha | 3       | NULL | 4399 |   100.00 | Using index condition; Using temporary; Using filesort |
+-------+---------------+-----------+---------+------+------+----------+--------------------------------------------------------+

Nessuno dei due è negativo di per sé. Quello che guardo è se compaiono in una query che si esegue migliaia di volte al giorno, o su molte righe.

Quando la query legge più tabelle, il piano ha una riga per ciascuna, nell'ordine in cui le leggerà:

EXPLAIN SELECT c.nombre, p.fecha, p.total
FROM pedidos AS p
JOIN clientes AS c ON c.id = p.cliente_id
WHERE p.fecha = '2024-05-10';

+-------+--------+---------------+-----------+---------+--------------------------------+------+----------+-------+
| table | type   | possible_keys | key       | key_len | ref                            | rows | filtered | Extra |
+-------+--------+---------------+-----------+---------+--------------------------------+------+----------+-------+
| p     | ref    | idx_fecha     | idx_fecha | 3       | const                          |  124 |   100.00 | NULL  |
| c     | eq_ref | PRIMARY       | PRIMARY   | 4       | tienda.p.cliente_id |    1 |   100.00 | NULL  |
+-------+--------+---------------+-----------+---------+--------------------------------+------+----------+-------+

Prima gli ordini del giorno, tramite idx_fecha; e per ciascuno, il suo cliente tramite la chiave primaria (eq_ref), confrontata con p.cliente_id, come dice ref.

L'ordine delle colonne di un indice

Un indice può avere più colonne. Ne creo uno con tre, pensato per cercare gli ordini di un cliente in uno stato e in una data:

CREATE INDEX idx_cliente_estado_fecha ON pedidos (cliente_id, estado, fecha);

Con le tre colonne nel WHERE, lo usa intero:

EXPLAIN SELECT * FROM pedidos
WHERE cliente_id = 1234 AND estado = 'pendiente' AND fecha = '2024-05-10';

+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+
| type | possible_keys                      | key                      | key_len | ref               | rows | filtered | Extra |
+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+
| ref  | idx_fecha,idx_cliente_estado_fecha | idx_cliente_estado_fecha | 89      | const,const,const |    1 |   100.00 | NULL  |
+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+

Per molto tempo ho creduto che le condizioni del WHERE dovessero essere nello stesso ordine delle colonne dell'indice, e che altrimenti l'indice non servisse. Mi sbagliavo: l'ottimizzatore abbina ogni condizione alla sua colonna, ovunque la si scriva. Con l'ordine invertito, il piano è identico:

EXPLAIN SELECT * FROM pedidos
WHERE fecha = '2024-05-10' AND estado = 'pendiente' AND cliente_id = 1234;

+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+
| type | possible_keys                      | key                      | key_len | ref               | rows | filtered | Extra |
+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+
| ref  | idx_fecha,idx_cliente_estado_fecha | idx_cliente_estado_fecha | 89      | const,const,const |    1 |   100.00 | NULL  |
+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+

Quello che conta, invece, è l'ordine delle colonne nell'indice. Un indice su più colonne è ordinato per la prima; all'interno di ogni valore della prima, per la seconda; e così via. È come un elenco telefonico ordinato per cognome e nome: serve a cercare un cognome, o un cognome con il suo nome, ma non tutte le persone che si chiamano Ana. Il manuale di MySQL lo chiama prefisso sinistro: un indice su (cliente_id, estado, fecha) serve a cercare per cliente_id, per cliente_id ed estado, o per tutte e tre. Non serve a cercare per estado o per fecha senza cliente_id.

Gli ordini in attesa di quest'anno, senza dire di quale cliente:

EXPLAIN SELECT * FROM pedidos
WHERE estado = 'pendiente' AND fecha >= '2025-01-01';

+------+---------------+------+---------+------+--------+----------+-------------+
| type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+------+---------------+------+---------+------+--------+----------+-------------+
| ALL  | idx_fecha     | NULL | NULL    | NULL | 199728 |     4.34 | Using where |
+------+---------------+------+---------+------+--------+----------+-------------+

La tabella ha un indice che contiene estado e fecha, ma comincia con cliente_id, e non compare nemmeno in possible_keys. C'è solo idx_fecha, che l'ottimizzatore scarta per lo stesso motivo di prima: quasi un anno di ordini.

key_len: quante colonne ha usato

Quando l'indice ha più colonne, key_len dice fin dove è arrivato. Ogni colonna occupa un numero fisso di byte nell'indice: cliente_id è un INT, 4 byte; estado è un VARCHAR(20) in utf8mb4, 20 caratteri da al massimo 4 byte più 2 per la lunghezza, 82; e fecha è un DATE, 3. Tutte e tre insieme fanno gli 89 di sopra. Una colonna che ammette NULL aggiunge un byte.

Con il cliente e lo stato, senza la data, usa le prime due colonne, 4 + 82:

EXPLAIN SELECT * FROM pedidos
WHERE cliente_id = 1234 AND estado = 'pendiente';

+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
| type | possible_keys            | key                      | key_len | ref         | rows | filtered | Extra |
+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
| ref  | idx_cliente_estado_fecha | idx_cliente_estado_fecha | 86      | const,const |    1 |   100.00 | NULL  |
+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+

Con il cliente e la data, saltando lo stato, può usare solo la prima:

EXPLAIN SELECT * FROM pedidos
WHERE cliente_id = 1234 AND fecha >= '2024-01-01';

+------+------------------------------------+--------------------------+---------+-------+------+----------+-----------------------+
| type | possible_keys                      | key                      | key_len | ref   | rows | filtered | Extra                 |
+------+------------------------------------+--------------------------+---------+-------+------+----------+-----------------------+
| ref  | idx_fecha,idx_cliente_estado_fecha | idx_cliente_estado_fecha | 4       | const |   40 |    50.00 | Using index condition |
+------+------------------------------------+--------------------------+---------+-------+------+----------+-----------------------+

key dice idx_cliente_estado_fecha, e chi guardasse solo quella colonna penserebbe che l'indice funzioni per intero. key_len dice 4: cerca per cliente_id e nient'altro. La data viene verificata dentro l'indice (Using index condition), ma non serve a restringere la ricerca, perché tra il cliente e la data c'è lo stato.

Quando key ha un nome e legge comunque tutto

C'è un altro caso che conviene conoscere. Se la query chiede solo colonne presenti nell'indice, MySQL può leggere l'indice intero invece della tabella, anche se il filtro non comincia dalla prima colonna:

EXPLAIN SELECT cliente_id, estado, fecha FROM pedidos
WHERE estado = 'pendiente';

+-------+--------------------------+--------------------------+---------+------+--------+----------+--------------------------+
| type  | possible_keys            | key                      | key_len | ref  | rows   | filtered | Extra                    |
+-------+--------------------------+--------------------------+---------+------+--------+----------+--------------------------+
| index | idx_cliente_estado_fecha | idx_cliente_estado_fecha | 89      | NULL | 199728 |    10.00 | Using where; Using index |
+-------+--------------------------+--------------------------+---------+------+--------+----------+--------------------------+

key ha un nome, ma type è index e rows è 199.728: scorre tutte le duecentomila voci dell'indice, una per una. È più veloce che scorrere la tabella, perché l'indice occupa meno, ma non è una ricerca. In MariaDB questo stesso piano esce con possible_keys vuota e key valorizzata, che è proprio il caso descritto dal manuale di MySQL: l'indice non serve a trovare le righe, ma contiene tutte le colonne richieste. Per questo key non si legge mai da sola: va insieme a type e rows.

La soluzione

Se la query frequente è per stato e data, l'indice deve cominciare dallo stato:

CREATE INDEX idx_estado_fecha ON pedidos (estado, fecha);

EXPLAIN SELECT * FROM pedidos
WHERE estado = 'pendiente' AND fecha >= '2025-01-01';

+-------+----------------------------+------------------+---------+------+------+----------+-----------------------+
| type  | possible_keys              | key              | key_len | ref  | rows | filtered | Extra                 |
+-------+----------------------------+------------------+---------+------+------+----------+-----------------------+
| range | idx_fecha,idx_estado_fecha | idx_estado_fecha | 85      | NULL | 1321 |   100.00 | Using index condition |
+-------+----------------------------+------------------+---------+------+------+----------+-----------------------+

Da 199.728 righe a 1.321, e key_len 85: lo stato intero, 82, più la data, 3. La regola che seguo per decidere l'ordine è mettere prima le colonne confrontate con = e per ultima quella confrontata con un intervallo (>=, BETWEEN): dopo un intervallo, le colonne successive dell'indice non restringono più la ricerca.

Succede lo stesso negli altri motori?

Sì. Il prefisso sinistro è una proprietà degli indici B-tree, che sono quelli predefiniti in tutti i motori relazionali, e la documentazione di PostgreSQL, SQLite e Oracle lo spiega allo stesso modo. In nessuno conta l'ordine in cui si scrivono le condizioni del WHERE.

Quello che alcuni hanno imparato col tempo è saltare la prima colonna quando ha pochissimi valori distinti: cercano per la seconda una volta per ogni valore della prima. Si chiama skip scan, e lo fanno MySQL dalla 8.0.13, PostgreSQL dalla 18, Oracle e SQLite (in SQLite solo se è stato eseguito ANALYZE). In MySQL compare in Extra come Using index for skip scan, e solo quando la query legge una tabella e chiede esclusivamente colonne dell'indice, tra le altre condizioni. Qui non sarebbe servito a niente: la prima colonna è il cliente, e ce ne sono cinquemila.

Misurare invece di stimare

Tutto ciò che precede sono stime: EXPLAIN non esegue la query. Da MySQL 8.0.18, EXPLAIN ANALYZE invece la esegue, e accanto a ogni stima mette quello che è successo davvero:

EXPLAIN ANALYZE SELECT * FROM pedidos
WHERE estado = 'pendiente' AND fecha >= '2025-01-01';

-> Index range scan on pedidos using idx_estado_fecha over (estado = 'pendiente' AND '2025-01-01' <= fecha), with index condition: ((pedidos.estado = 'pendiente') and (pedidos.fecha >= DATE'2025-01-01'))  (cost=595 rows=1321) (actual time=1.01..12.6 rows=1321 loops=1)

rows=1321 tra le prime parentesi è ciò che ha stimato; actual time e rows=1321 tra le seconde, quanto ci ha messo in millisecondi e le righe che ha letto. Quando i due numeri di righe si somigliano poco, le statistiche della tabella non sono aggiornate, e ANALYZE TABLE pedidos le ricalcola.

MariaDB non ha EXPLAIN ANALYZE. Il suo equivalente è ANALYZE davanti alla query, dalla 10.1, che restituisce la tabella di sempre con due colonne in più, r_rows e r_filtered, con ciò che è successo:

ANALYZE SELECT * FROM pedidos
WHERE estado = 'pendiente' AND fecha >= '2025-01-01';

Entrambi eseguono la query, quindi ci mettono quanto lei. E in MariaDB, un ANALYZE UPDATE o un ANALYZE DELETE modifica davvero i dati.

In Calíope

Nell'Editor SQL, con la query scritta, il pulsante Visual EXPLAIN della barra chiede il piano al server senza eseguire la query e lo disegna come un albero, con un colore per tipo di accesso: rosso per la scansione completa di una tabella, arancione per quella di un indice intero, giallo per un tratto di indice e verde per una ricerca per chiave. Lo chiede in JSON, quindi non dipende da come hai impostato explain_format. E il pulsante Indici della barra dei risultati apre il Consulente indici, che cerca in quel piano le scansioni complete e, se la query filtra per una colonna che nessun indice copre, propone il CREATE INDEX.

Cosa guardo per primo

Quando una query è lenta, questo è l'ordine in cui leggo il suo EXPLAIN:

• key: se è vuota, non usa nessun indice. Se possible_keys ne ha qualcuno, l'ottimizzatore l'ha scartato; se è vuota anche quella, non ce n'è nessuno utile, o la condizione non permette di usarlo.
• type e rows insieme: ALL o index con molte righe vuol dire leggere tutta la tabella o tutto l'indice, anche se key ha un nome.
• key_len, se l'indice ha più colonne: dice quante ne ha usate davvero.
• Extra: Using temporary e Using filesort in una query che si esegue spesso meritano una seconda occhiata.