EXPLAIN
Sintassi
EXPLAIN nome_tab
Oppure:
EXPLAIN [EXTENDED | PARTITIONS] SELECT opzioni_select
Spiegazione
L'istruzione EXPLAIN
può essere utilizzata come sinonimo di DESCRIBE
oppure per ottenere informazioni sul modo in cui MariaDB esegue un'istruzione SELECT
:
'EXPLAIN nome_tab'
è sinonimo di'DESCRIBE nome_tab'
o'SHOW COLUMNS FROM nome_tab'
.- Quando la parola chiave
EXPLAIN
precede una istruzioneSELECT
, MariaDB mostra informazioni sul piano di esecuzione della query, ottenute dall'ottimizzatore. In pratica, MariaDB spiega come intende eseguire laSELECT
, compreso il modo e l'ordine in cui intende concatenare le tabelle.EXPLAIN EXTENDED
serve a fornire maggiori informazioni.
Per ulteriori informazioni su come usare EXPLAIN e EXPLAIN EXTENDED per conoscere i piani di esecuzione delle query, si veda http://dev.mysql.com/doc/refman/5.1/en/using-explain.html. EXPLAIN PARTITIONS
è supportato a partire da MariaDB 5.1.5. Serve solo quando si esaminano le query che coinvolgono le tabelle partizionate.
Per i dettagli, si veda http://dev.mysql.com/doc/refman/5.1/en/partitioning-info.html.
E' disponibile online un EXPLAIN Analyzer che può essere utilizzato per condividere con altre persone gli output di EXPLAIN
e EXPLAIN EXTENDED
.
Le colonne di EXPLAIN ... SELECT
Nome Colonna | Spiegazione |
---|---|
id | Numero di sequenza che indica in quale ordine vengono concatenate le tabelle. |
select_type | Da che tipo di SELECT viene la tabella. |
table | Alias dei nomi di tabella. Tabelle temporanee materializzate per le subquery chiamate <subquery#> |
type | In che modo le righe vengono lette (tipo di join). |
possible_keys | Chiavi che potrebbero essere utilizzate per trovare righe nella tabella |
key | The name of the key that is used to retrieve rows. NULL is no key was used. |
key_len | Quanti byte verranno letti dalla chiave (indica se si sta usando solo parte di un indice multi-colonna). |
ref | The reference that is used to as the key value. |
rows | Una stima di quante righe verranno trovate nella tabella per ogni ricerca su indice. |
Extra | Informazioni aggiuntive sulla join. |
Ecco le spiegazioni dei valori di alcune delle colonne più complesse di EXPLAIN ... SELECT
:
Colonna "select_type"
La colonna select_type
può avere i seguenti valori:
Valore | Spiegazione |
---|---|
PRIMARY | La SELECT è primaria. |
SIMPLE | La SELECT è semplice. |
DERIVED | La SELECT è derivata da quella primaria. |
SUBQUERY | La SELECT è una subquery di quella primaria. |
DEPENDENT SUBQUERY | La SUBQUERY è dipendente. |
UNCACHEABLE SUBQUERY | La SUBQUERY non può essere messa nella cache. |
UNION | La SELECT è una UNION di quella primaria. |
UNION RESULT | Il risultato di una UNION. |
DEPENDENT UNION | La UNION è dipendente. |
UNCACHEABLE UNION | La UNION non può essere messa nella cache. |
Colonna "Type"
Questa colonna contiene informazioni sul modo in cui avviene l'accesso alla tabella.
Valore | Spiegazione |
---|---|
ALL | Scansione completa della tabella (tutte le righe vengono lette). Questo è male se la tabella è grande e se è concatenata ad un'altra tabella che è alla sua sinistra! Succede se l'ottimizzatore non ha trovato indici utilizzabili per leggere le righe. |
const | Nella tabella c'è una sola riga che potrebbe soddisfare la ricerca. Viene letta prima della fase di ottimizzazione e tutte le colonne della tabella vengono trattate come costanti. |
eq_ref | Un indice univoco viene usato per trovare le righe. Questo è il miglior piano possibile per trovare le righe. |
fulltext | Un indice fulltext viene usato per trovare le righe. |
index_merge | Un accesso 'range' viene usato per diversi indici e le righe trovate vengono unite. La colonna key dice quali chiavi vengono usate. |
index_subquery | Simile a ref, ma è usata per le subquery che vengono trasformate in ricerche su indice. |
index | Scansione completa di un indice. Meglio di ALL, ma non va bene se l'indice è grande e la tabella è concatenata ad un'altra che si trova alla sua sinistra. |
range | L'accesso avviene attraverso una chiave, basato su uno o più intervalli. |
ref_or_null | Come 'ref' ma viene cercato anche il valore 'null' se il primo valore non viene trovato. Solitamente avviene con le subquery. |
ref | Un indice non univoco o un prefisso di un indice univoco viene usato per trovare le righe. Buono se il prefisso non corrisponde ad alcuna riga. |
system | La tabella ha 0 o 1 riga. |
unique_subquery | Simile a eq_ref, ma è usato per le subquery che vengono trasformate in ricerche su indice. |
La colonna "Extra"
Questa colonna contiene uno o più dei seguenti valori, separato da ';'
Si noti che alcuni di essi vengono rilevati dopo la fase di ottimizzazione.
La fase di ottimizzazione può apportare le seguenti modifiche alla clausola WHERE
:
- Spostare espressioni dalle clausole
ON
eUSING
alla clausolaWHERE
. - Propagazione delle costanti: se c'è
colonna=costante
, sostituisce tutte le istanze di 'colonna' con la costante. - Sostituisce tutte le colonne dalle tabelle '
const
' con i loro valori. - Rimuove dalla
WHERE
le colonne indice utilizzate (perché questa operazione viene fatta nella parte di ricerca su indice). - Elimina le sottoespressioni costanti impossibili.
Per esempio
WHERE '(a=1 and a=2) OR b=1'
becomes'b=1'
. - Sostituisce le colonne con altre colonne che hanno identici valori:
Esempio:
WHERE
a=b
anda=c
possono essere cambiati in- 'WHERE a=b and a=c and b=c'
.
- 'WHERE a=b and a=c and b=c'
- Aggiunge condizioni aggiuntive per rilevare più velocemente le condizioni di riga impossibili. Accade principalmente con le
OUTER JOIN
, dove talvolta viene aggiunto il rilevamento dei valoriNULL
nellaWHERE
(fa parte dell'ottimizzazione 'Not exists
'). Questo può far apparire un 'Using where
' inaspettato nella colonna Extra. - Per ogni livello della tabella vengono eliminate le espressioni che sono già state controllate leggendo la riga precedente. Esempio: quando vengono concatenate le tabelle
t1
et2
utilizzandoWHERE 't1.a=1 and t1.a=t2.b'
, non c'è bisogno di controllare't1.a=1'
mentre si cercano le righe int2
, perché questa espressione è necessariamente vera.
Valore | Spiegazione |
---|---|
const row not found | La tabella è di tipo system (dovrebbe contenere esattamente una riga), ma non è stata trovata alcuna riga. |
Distinct | Viene usata l'ottimizzazione distinct (eliminazione dei duplicati). Questo valore può essere presente solo per l'ultima tabella della SELECT. |
Full scan on NULL key | La tabella è parte di una subquery e se il valore che viene letto è NULL, viene eseguita una scansione completa. |
Impossible HAVING | La clausola HAVING specificata è sempre false, perciò la SELECT non restituisce alcuna riga. |
Impossible WHERE noticed after reading const tables. | La clausola WHERE specificata è sempre falsa, perciò la SELECT non restituisce alcuna riga. Questa condizione viene rilevata dopo aver letto tutte le tabelle 'const' e aver trasformato i valori delle colonne in costanti nella clausola WHERE. Per esempio: WHERE col_cost=5 and col_cost=4. |
Impossible WHERE | La clausola WHERE specificata è sempre falsa, perciò la SELECT non restituisce alcuna riga. Per esempio: WHERE 1=2 |
No matching min/max row | Durante l'ottimizzazione iniziale di MIN()/MAX() è stato rilevato che nessuna riga può soddisfare la clausola WHERE. Le funzioni MIN()/MAX() restituiranno NULL. |
no matching row in const table | La tabella è di tipo 'const' (non più di una riga potrebbe soddisfare la ricerca), ma nessuna riga è stata trovata. |
No tables used | La SELECT è una subquery che non coinvolge alcuna tabella. In pratica non c'è una clausola FROM, oppure c'è una clausola FROM DUAL. |
Not exists | Appena trova una riga, smette di cercarne altre. Questa ottimizzazione viene usata con le LEFT JOIN dove una cerca esplicitamente righe che non esistono nella LEFT JOIN. Esempio: SELECT * FROM t1 LEFT JOIN t2 on (...) WHERE t2.not_null_column IS NULL. Siccome t2.not_null_column può essere NULL solo se nessuna riga soddisfa la clausola on, appena trova una riga smette di cercare. |
Open_frm_only | Per le tabelle dell'INFORMATION_SCHEMA. Per ogni riga trovata, viene aperto solo il file frm (che contiene la definizione di una tabella). |
Open_full_table | Per le tabelle dell'INFORMATION_SCHEMA. Per ogni riga trovata una tabella viene aperta completamente. (Lento) |
Open_trigger_only | Per le tabelle dell'INFORMATION_SCHEMA. Per ogni riga trovata viene aperto solo il file di definizione dei trigger. |
Range checked for each record (index map: ...) | Succede solo se non c'è un buon indice di default da usare ma potrebbe esserci qualche indice utilizzabile se si trattano come costanti tutte le colonne lette dalla tabella precedenti. Per ogni singola combinazione di righe, l'ottimizzatore decide quale indice utilizzare (se ce ne sono) per estrarre una riga da questa tabella. Non è un metodo veloce, ma è comunque più rapido di una scansione completa della tabella, che è l'unica alternativa. La "index map" è una maschera di bit che indica quali indici vengono presi in considerazione per ogni riga. |
Scanned 0/1/all databases | Per le tabelle dell'INFORMATION_SCHEMA. Indica quante volte è stata eseguita la scansione di una directory. |
Select tables optimized away | Tutte le tabelle della join sono state eliminate dall'ottimizzatore. Questo accade quando nella SELECT si usano solo le funzioni COUNT(*), MIN() e MAX(), ed è stato possibile sostituirle con valori costanti. |
Skip_open_table | Per le tabelle dell'INFORMATION_SCHEMA. Non è stato necessario aprire la tabella da interrogare. |
unique row not found | Durante la fase di ottimizzazione la tabella è stata giudicata di tipo const (non più di una riga potrebbe soddisfare la ricerca), ma nessuna riga è stata trovata. |
Using filesort | Per risolvere la query è necessario un filesort. Questo implica una fase aggiuntiva, durante la quale vengono raccolte tutte le colonne da ordinare, vengono ordinate con un merge sort su disco e infine la lista ordinata viene usata per estrarre le righe nell'ordine desiderato. Se le colonne sono poche, vengono registrate tutte nel sort file per evitare di accedere al database la seconda volta. |
Using index | Viene usato solo l'indice per restituire le informazioni dalla tabella. Non sono necessarie ricerche aggiuntive per leggere i record. |
Using index condition | Simile a 'Using where', ma la condizione WHERE viene passata allo Storage Engine, che effettuerà ottimizzazioni interne sugli indici. |
Using index condition(BKA) | Simile a 'Using index condition', ma in aggiunta viene usato un batch key access per leggere le righe. |
Using index for group-by | Viene usato un indice per risolvere una GROUP BY o una DISTINCT. Le righe non vengono lette. E' molto efficiente se gli indici contengono molte voci identiche, perché i duplicati vengono ignorati. |
Using intersect(...) | Per le join index_merge. Indica quali indici sono parte dell'intersezione. |
Using join buffer | Le combinazioni delle righe della tabella precedente vengono registrate in un buffer delle righe, per poterle confrontare con le combinazioni presenti nel buffer della join tutte in una volta. |
Using sort_union(...) | Per le join index_merge. Indica quali indici sono parte della union. |
Using temporary | Il risultato viene scritto in una tabella temporanea. Solitamente accade se si usa GROUP BY, DISTINCT o ORDER BY. |
Using where | Viene usata un'espressione WHERE (in aggiunta a un'eventuale ricerca su indice) per controllare se la riga ci interessa. Se 'Using where' non è accompagnata dal "join type" ALL, probabilmente si sta sbagliando qualcosa! |
Using where with pushed condition | Simile a 'Using where', ma la condizione WHERE viene passata allo Storage Engine, che effettuerà ottimizzazioni interne a livello di riga. |
EXPLAIN EXTENDED
La parola chiave EXTENDED aggiunge la colonna filtered all'output. E' una stima in percentuale delle righe che verranno filtrate dalla condizione.
EXPLAIN EXTENDED produce sempre uno warning, perché aggiunge un'informazione Message al prossimo comando SHOW WARNINGS. Esso mostra la SELECT ottimizzata e riscritta dall'ottimizzatore.
Examples
As synonym for DESCRIBE or SHOW COLUMNS FROM:
DESCRIBE city; +------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+----------------+ | Id | int(11) | NO | PRI | NULL | auto_increment | | Name | char(35) | YES | | NULL | | | Country | char(3) | NO | UNI | | | | District | char(20) | YES | MUL | | | | Population | int(11) | YES | | NULL | | +------------+----------+------+-----+---------+----------------+
A simple set of examples to see how EXPLAIN can identify poor index usage:
CREATE TABLE IF NOT EXISTS `employees_example` ( `id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(30) NOT NULL, `last_name` varchar(40) NOT NULL, `position` varchar(25) NOT NULL, `home_address` varchar(50) NOT NULL, `home_phone` varchar(12) NOT NULL, `employee_code` varchar(25) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `employee_code` (`employee_code`), KEY `first_name` (`first_name`,`last_name`) ) ENGINE=Aria; INSERT INTO `employees_example` (`first_name`, `last_name`, `position`, `home_address`, `home_phone`, `employee_code`) VALUES ('Mustapha', 'Mond', 'Chief Executive Officer', '692 Promiscuous Plaza', '326-555-3492', 'MM1'), ('Henry', 'Foster', 'Store Manager', '314 Savage Circle', '326-555-3847', 'HF1'), ('Bernard', 'Marx', 'Cashier', '1240 Ambient Avenue', '326-555-8456', 'BM1'), ('Lenina', 'Crowne', 'Cashier', '281 Bumblepuppy Boulevard', '328-555-2349', 'LC1'), ('Fanny', 'Crowne', 'Restocker', '1023 Bokanovsky Lane', '326-555-6329', 'FC1'), ('Helmholtz', 'Watson', 'Janitor', '944 Soma Court', '329-555-2478', 'HW1'); SHOW INDEXES FROM employees_example; +-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | employees_example | 0 | PRIMARY | 1 | id | A | 7 | NULL | NULL | | BTREE | | | | employees_example | 0 | employee_code | 1 | employee_code | A | 7 | NULL | NULL | | BTREE | | | | employees_example | 1 | first_name | 1 | first_name | A | NULL | NULL | NULL | | BTREE | | | | employees_example | 1 | first_name | 2 | last_name | A | NULL | NULL | NULL | | BTREE | | | +-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
SELECTING on a primary key:
EXPLAIN SELECT * FROM employees_example WHERE id=1; +------+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | employees_example | const | PRIMARY | PRIMARY | 4 | const | 1 | | +------+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+
The type is const, which means that only one possible result could be returned. Now, returning the same record but searching by their phone number:
EXPLAIN SELECT * FROM employees_example WHERE home_phone='326-555-3492'; +------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | employees_example | ALL | NULL | NULL | NULL | NULL | 6 | Using where | +------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+
Here, the type is All, which means no index could be used. Looking at the rows count, a full table scan (all six rows) had to be performed in order to retrieve the record. If it' a requirement to search by phone number, and index will have to be created.
Esempi
Esempio di ottimizzazione ref_or_null
:
SELECT * FROM nome_tabella WHERE colonna_chiave=espr OR colonna_chiave IS NULL;
ref_or_null
si verifica spesso quando si usano subquery con NOT IN
perché, se nessuna riga contiene il primo valore, occorre un controllo aggiuntivo per i valori NULL.