EXPLAIN

Синтаксис

EXPLAIN tbl_name [col_name | wild]

Или

EXPLAIN [EXTENDED | PARTITIONS | FORMAT=JSON] 
  {SELECT select_options | UPDATE update_options | DELETE delete_options}

Описание

Оператор EXPLAIN может использоваться как синоним для DESCRIBE, а также для получения информации о том, как MariaDB выполняет оператор SELECT, UPDATE или DELETE:

• 'EXPLAIN tbl_name' является синонимом для 'DESCRIBE tbl_name' или 'SHOW COLUMNS FROM tbl_name'.
• При добавлении ключевого слова EXPLAIN перед оператором SELECT, UPDATE или DELETE, MariaDB отображает информацию из оптимизатора о плане выполнения запроса. То есть, MariaDB объясняет, как он обработает оператор SELECT, UPDATE или DELETE, включая информацию о том, как и в каком порядке таблицы объединяются. EXPLAIN EXTENDED может использоваться для предоставления дополнительной информации.
  
• EXPLAIN PARTITIONS полезен только при рассмотрении запросов, включающих разнесённые таблицы.
  Для получения подробностей см. Обрезка и выборка разнесений.
• Оператор ANALYZE выполняет запрос и выводит результат EXPLAIN, а также предоставляет фактические и оценочные статистические данные.
• Результат EXPLAIN может быть выведен в журнале медленных запросов. Подробности см. в EXPLAIN в журнале медленных запросов.

SHOW EXPLAIN отображает вывод выполняемого оператора. В некоторых случаях его вывод может быть ближе к реальности, чем EXPLAIN.

Оператор ANALYZE выполняет оператор и возвращает информацию о плане его выполнения. Он также показывает дополнительные столбцы, чтобы проверить насколько близки оценки оптимизатора по фильтрации и найденным строкам к реальности.

Существует онлайн EXPLAIN-анализатор, который вы можете использовать для совместного использования вывода EXPLAIN и EXPLAIN EXTENDED с другими пользователями.

Столбцы в EXPLAIN ... SELECT

Ниже приведены описания значений некоторых более сложных столбцов в EXPLAIN ... SELECT:

Столбец "Select_type"

Столбец select_type может содержать следующие значения:

Столбец "Type"

Этот столбец содержит информацию о том, как осуществляется доступ к таблице.

Столбец "Extra"

Этот столбец состоит из одного или нескольких следующих значений, разделенных точкой с запятой

Обратите внимание, что некоторые из этих значений обнаруживаются после фазы оптимизации.

Фаза оптимизации может внести следующие изменения в WHERE:

• Добавить выражения из ON и USING в WHERE.
• Распространение констант: Если существует column=constant, заменить все вхождения столбца этой константой.
• Заменить все столбцы из таблиц 'const' их значениями.
• Удалить используемые столбцы ключа из WHERE (так как они будут проверены в рамках поиска по ключу).
• Удалить невозможные константные подвыражения. Например, WHERE '(a=1 and a=2) OR b=1' становится 'b=1'.
• Заменить столбцы другими столбцами, имеющими идентичные значения: Пример: WHERE a=b и a=c могут рассматриваться как 'WHERE a=b and a=c and b=c'.
• Добавить дополнительные условия для обнаружения невозможных условий строк ранее. Это происходит в основном с OUTER JOIN, где в некоторых случаях мы добавляем обнаружение NULL значений в WHERE (часть оптимизации 'Not exists'). Это может привести к неожиданному значению 'Using where' в столбце Extra.
• Для каждого уровня таблицы мы удаляем выражения, которые уже были проверены при чтении предыдущей строки. Например, при объединении таблиц t1 с t2 с помощью WHERE 't1.a=1 and t1.a=t2.b', мы не должны проверять 't1.a=1' при проверке строк в t2, так как мы уже знаем, что это выражение истинно.

Подробное EXPLAIN

Ключевое слово EXTENDED добавляет в вывод ещё один столбец, filtered, это процентная оценка строк таблицы, которые будут отфильтрованы условием.

Вывод EXPLAIN EXTENDED всегда генерирует предупреждение, так как он добавляет дополнительную информацию Message к последующей инструкции SHOW WARNINGS. Это включает в себя то, как SELECT запрос будет выглядеть после применения оптимизаций и правил переписывания, и как оптимизатор квалифицирует столбцы и таблицы.

Примеры

Как синоним для DESCRIBE или 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    |                |
+------------+----------+------+-----+---------+----------------+

Несколько примеров, чтобы увидеть, как EXPLAIN может определить неэффективное использование индексов:

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      |         |               |
+-------------------+------------+---------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

SELECT по первичному ключу:

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 |       |
+------+-------------+-------------------+-------+---------------+---------+---------+-------+------+-------+

Тип const, это означает, что может быть возвращён только один результат. Теперь, возвращая ту же запись, но ища по номеру телефона:

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 |
+------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+

Здесь тип All, это значит, что ни один индекс не может быть использован. Учитывая количество строк, для извлечения записи потребовалось полное сканирование таблицы (все шесть строк). Если необходимо искать по номеру телефона, нужно создать индекс.

SHOW EXPLAIN пример:

SHOW EXPLAIN FOR 1;
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | tbl   | index | NULL          | a    | 5       | NULL | 1000107 | Using index |
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
1 row in set, 1 warning (0.00 sec)

Пример оптимизации ref_or_null

SELECT * FROM table_name
  WHERE key_column=expr OR key_column IS NULL;

ref_or_null часто происходит, когда вы используете подзапросы с NOT IN, так как тогда нужно сделать дополнительную проверку на NULL значения, если первое значение не имело соответствующей строки.

См. также

• SHOW EXPLAIN
• Игнорируемые индексы