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 istruzione SELECT, MariaDB mostra informazioni sul piano di esecuzione della query, ottenute dall'ottimizzatore. In pratica, MariaDB spiega come intende eseguire la SELECT, 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

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:

Colonna "Type"

Questa colonna contiene informazioni sul modo in cui avviene l'accesso alla tabella.

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 e USING alla clausola WHERE.
• 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 and a=c possono essere cambiati in
  1. '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 valori NULL nella WHERE (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 e t2 utilizzando WHERE 't1.a=1 and t1.a=t2.b', non c'è bisogno di controllare 't1.a=1' mentre si cercano le righe in t2, perché questa espressione è necessariamente vera.

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.