Corso MariaDB / Leggere EXPLAIN e confrontare i piani prima e dopo un indice
EXPLAIN mostra il piano di esecuzione scelto dall'ottimizzatore. Confrontare la stessa query e i suoi risultati prima e dopo l'aggiunta di un indice, leggendo metodo di accesso, chiave scelta e stime delle righe senza confondere le stime con il tempo effettivamente misurato.
Leggere i campi del piano nel contesto della query
Partire dalle tabelle lette e dalle chiavi che possono restringere i dati in ingresso. Un accesso ALL non è automaticamente sbagliato e Using filesort, da solo, non dimostra che l'ordinamento usi file su disco. Considerare dimensioni dei dati, quota di righe selezionate e latenza reale. La guida agli indici singoli e composti aiuta a motivare gli indici candidati.
| Campo | Significato | Presupposto da evitare |
|---|---|---|
| table | Tabella o alias della fase | Una query ha sempre una sola fase |
| type | Metodo di accesso | Scansionare una tabella piccola è sempre negativo |
| possible_keys / key | Indici candidati / indice scelto | Un indice presente viene necessariamente scelto |
| rows | Stima delle righe lette | Numero esatto delle righe risultanti |
| Extra | Informazioni su filtro, ordinamento e copertura | Una frase determina la qualità dell'intera query |
Il riferimento MariaDB di EXPLAIN descrive campi e metodi di accesso del piano.
Creare mille righe riproducibili
Collegarsi come course_user a shopcourse e controllare SELECT VERSION(), DATABASE();. La guida al client e ai file SQL spiega come salvare il codice ed eseguirlo con SOURCE usando il percorso effettivo. Eseguire SQL nel client MariaDB.
Verificare con SHOW TABLES che digits_40_lab ed explain_40_lab non esistano. Se esistono, esaminarne definizioni e dati prima di decidere se sono eliminabili. La preparazione completa combina tre cifre decimali per generare 1000 righe, senza leggere né modificare gli ordini applicativi.
USE shopcourse;
CREATE TABLE digits_40_lab(n TINYINT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO digits_40_lab VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE explain_40_lab(
order_id INT PRIMARY KEY,customer_id INT NOT NULL,
ordered_at DATETIME NOT NULL,amount DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;
INSERT INTO explain_40_lab
SELECT 1+a.n+10*b.n+100*d.n,
MOD(a.n+10*b.n+100*d.n,100)+1,
DATE_ADD('2026-01-01',INTERVAL (a.n+10*b.n+100*d.n) MINUTE),1000
FROM digits_40_lab a CROSS JOIN digits_40_lab b CROSS JOIN digits_40_lab d;
Verificare che SELECT COUNT(*) FROM explain_40_lab; restituisca 1000. Gli ID cliente vanno da 1 a 100 e ciascun cliente ha dieci ordini. Questa distribuzione artificiale serve a leggere i piani e non rappresenta squilibri, dimensioni o carichi di un ambiente di produzione.
Confrontare lo stesso EXPLAIN prima e dopo l'indice
EXPLAIN SELECT order_id,ordered_at FROM explain_40_lab
WHERE customer_id=47 AND ordered_at>='2026-01-01'
ORDER BY ordered_at,order_id;
CREATE INDEX idx_e40_customer_date
ON explain_40_lab(customer_id,ordered_at,order_id);
EXPLAIN SELECT order_id,ordered_at FROM explain_40_lab
WHERE customer_id=47 AND ordered_at>='2026-01-01'
ORDER BY ordered_at,order_id;
Il primo piano non dispone ancora dell'indice secondario cliente–tempo; il secondo dispone del nuovo candidato. Leggere key effettivamente scelto e stima rows nel proprio risultato. Versione, statistiche dell'ottimizzatore e distribuzione dei dati possono cambiare i dettagli: non memorizzare un numero come risultato universale.
La tabella mostra l'osservazione su MariaDB 12.3.3 con esattamente le mille righe generate da questo esempio. In altri ambienti il risultato può differire.
| Fase | type | key | rows | Extra |
|---|---|---|---|---|
| Prima dell'indice | ALL | NULL | 1000 | Using where; Using filesort |
| Dopo l'indice | range | idx_e40_customer_date | 10 | Using where; Using index |
In questa osservazione la scansione completa diventa un accesso per intervallo cliente–tempo e la stima scende da 1000 a 10 righe. Non significa che la velocità misurata sia aumentata di cento volte. Verificare gli stessi dieci ordini e analizzare separatamente l'esecuzione effettiva.
Verificare i risultati effettivi separatamente dalle stime
SELECT order_id,ordered_at FROM explain_40_lab
WHERE customer_id=47 AND ordered_at>='2026-01-01'
ORDER BY ordered_at,order_id;
Gli ID restituiti sono dieci valori da 47 a 947, con incremento di 100. La stessa SELECT deve produrre righe e ordine uguali con o senza il nuovo indice. rows di EXPLAIN stima il lavoro di una fase di accesso, non il numero finale dei risultati.
Un piano non garantisce una latenza. Un confronto temporale significativo controlla query, dati, carico concorrente e stato della cache e usa osservazioni ripetute. Non trasformare piccole differenze su dati artificiali in percentuali di miglioramento dell'intero servizio.
Distinguere ANALYZE dalla manutenzione delle statistiche
La famiglia di istruzioni ANALYZE di MariaDB esegue la query per raccogliere informazioni di esecuzione. Non considerarla equivalente a un EXPLAIN privo degli effetti della query quando precede UPDATE o DELETE. Per analizzare l'esecuzione, usare un ambiente separato adeguato e verificare prima ambito e privilegi.
ANALYZE TABLE è un'operazione diversa che aggiorna le statistiche della tabella. Statistiche obsolete possono compromettere le stime, ma anche aggiornarle presenta costi e considerazioni sui blocchi secondo la versione. Esaminare forma della query e distribuzione dei dati prima di forzare un piano con FORCE INDEX.
Per le informazioni effettive eseguire il blocco seguente soltanto sulla SELECT dell'esercizio. ANALYZE FORMAT=JSON esegue la query: distinguere r_rows, media delle righe lette in quella fase, e r_loops, numero di esecuzioni della fase, dalla stima rows. Il tempo dipende dall'ambiente e non è un risultato fisso da raggiungere.
ANALYZE FORMAT=JSON
SELECT order_id,ordered_at FROM explain_40_lab
WHERE customer_id=47 AND ordered_at>='2026-01-01'
ORDER BY ordered_at,order_id;
Il riferimento dei campi di ANALYZE FORMAT=JSON spiega queste informazioni effettive. Individuare il nodo della tabella corretto prima di confrontare i valori tra fasi.
Conservare prove riproducibili ed eliminare solo gli oggetti di prova
Registrare versione del server, definizioni di tabelle e indici, metodo di generazione dei dati, query di confronto e piani precedenti e successivi. Conservare risultati SELECT e informazioni ANALYZE pertinenti per non dipendere da una singola immagine del piano senza contesto.
Dopo aver verificato che non vi siano dati da conservare, eliminare soltanto i due oggetti seguenti. Non hanno relazioni di chiave esterna. Nessuna tabella di ordini applicativi appartiene a questa pulizia. DROP TABLE elimina struttura e righe e non viene annullato da un normale ROLLBACK.
DROP TABLE shopcourse.explain_40_lab;
DROP TABLE shopcourse.digits_40_lab;




