Что интересного нам расскажет EXPLAIN EXTENDED?

Большинство разработчиков на MySQL знакомы с командой EXPLAIN, однако значительно меньше людей знают о команде EXPLAIN EXTENDED, появившуюся ещё в MySQL 4.1, и ещё меньше умеют ею пользоваться.

EXPLAIN EXTENDED умеет показывать, что же конкретно делает с Вашим запросом оптимизатор MySQL. Для разработчика может быть совсем не очевидно, насколько сильно может отличаться написанный им запрос от того, который в действительности будет выполнен сервером. Этот процесс называется механизмом перезаписи запросов (query-rewrite), и он является частью любого хорошего SQL-оптимизатора. Команда EXPLAIN EXTENDED добавляет дополнительные предупреждения (warnings) к выводу команды EXPLAIN, в том числе и переписанный SQL-запрос.


Для начала давайте создадим три пустых таблицы. Сейчас нам важно, чтобы таблицы были именно пустыми, т.к. оптимизатор MySQL по особенному работает с пустыми таблицами (а иногда и с таблицами, содержащими только одну строку), чем с таблицами, содержащими более одной строки.

mysql> CREATE TABLE j1 (c1 int);
Query OK, 0 rows affected (0.16 sec)
CREATE TABLE j2 (c1 int);
Query OK, 0 rows affected (0.11 sec)
mysql> CREATE TABLE j3 (c1 int);
Query OK, 0 rows affected (0.10 sec)

mysql> EXPLAIN extended SELECT j1.c1 FROM j1, j2, j3 WHERE j1.c1 = j2.c1 AND j3.c1 = j1.c1;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const TABLES
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row IN SET, 1 warning (0.04 sec)
mysql> SHOW warnings;
+-------+------+-------------------------------------------------------------------------------+
| Level | Code | Message
+-------+------+-------------------------------------------------------------------------------+
| Note | 1003 | SELECT '0' AS `c1` FROM `test`.`j1` JOIN `test`.`j2` JOIN `test`.`j3` WHERE 0
+-------+------+-------------------------------------------------------------------------------+
1 row IN SET (0.00 sec)

Здесь Вы можете заметить несколько странных вещей в выводе команды EXPLAIN. Первое — в нём не перечислено ни одной таблицы. Взглянув на колонку Extra можно увидеть, что MySQL упомянул там слово 'const'. Таблицы 'const' — это как раз те таблицы, что содержат 0 или 1 строку, или таблицы, все части PRIMARY- или UNIQUE-ключей которых полностью удовлетворяют параметрам в WHERE. Если таблица типа 'const' не содержит строк и не используется в OUTER JOIN, то MySQL сразу же вернёт пустой результат, т.к. не может существовать ни одного пересечения таблиц, удовлетворяющего запросу. MySQL добивается этого добавляет вместо параметров WHERE — WHERE 0.

Давайте посмотрим, что будет, если в каждую из таблиц добавить по единице (1):
mysql> INSERT INTO j1 VALUES (1); INSERT INTO j2 SELECT * FROM j1; INSERT INTO j3 SELECT * FROM j2;
Query OK, 1 row affected (0.00 sec)
mysql> EXPLAIN extended SELECT j1.c1 FROM j1, j2, j3 WHERE j1.c1 = j2.c1 AND j3.c1 = j1.c1;
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | j1 | system | NULL | NULL | NULL | NULL | 1 |
| 1 | SIMPLE | j2 | system | NULL | NULL | NULL | NULL | 1 |
| 1 | SIMPLE | j3 | system | NULL | NULL | NULL | NULL | 1 |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
3 rows IN SET, 1 warning (0.00 sec)
mysql> SHOW warnings;
+-------+------+-------------------------------------------------------------------------------+
| Level | Code | Message
+-------+------+-------------------------------------------------------------------------------+
| Note | 1003 | SELECT '1' AS `c1` FROM `test`.`j1` JOIN `test`.`j2` JOIN `test`.`j3` WHERE 1
+-------+------+-------------------------------------------------------------------------------+
1 row IN SET (0.00 sec)

Как можно заметить, теперь таблицы появились в результатах выполнения EXPLAIN, однако обратите Ваше внимание на то, что тип таблицы указан как 'system'. Таблица этого типа являются разновидностью таблиц типа 'const', содержащей только одну строку. Содержимое этой таблицы считывается полностью до начала выполнения запроса, благодаря чему MySQL может сравнить значения из таблицы с заданными параметрами как константы ещё до формирования плана исполнения. К тому же MySQL заменил все параметры в WHERE на WHERE 1, т.к. он знает, что все используемые в запросе таблицы содержат одинаковые значения. Если бы это было не так, то он, как и в предыдущем случае, добавил бы WHERE 0.

Наконец, давайте добавил ещё немного данных в таблицы и протестируем запрос:
mysql> INSERT INTO j1 VALUES (1); INSERT INTO j2 SELECT * FROM j1; INSERT INTO j3 SELECT * FROM j2;
mysql> EXPLAIN extended SELECT j1.c1 FROM j1, j2, j3 WHERE j1.c1 = j2.c1 AND j3.c1 = j1.c1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | j1 | ALL | NULL | NULL | NULL | NULL | 2 |
| 1 | SIMPLE | j2 | ALL | NULL | NULL | NULL | NULL | 3 | USING WHERE
| 1 | SIMPLE | j3 | ALL | NULL | NULL | NULL | NULL | 4 | USING WHERE
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
3 rows IN SET, 1 warning (0.00 sec)
mysql> SHOW warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | SELECT `test`.`j1`.`c1` AS `c1` FROM `test`.`j1` JOIN `test`.`j2` JOIN `test`.`j3` WHERE ((`test`.`j2`.`c1` = `test`.`j1`.`c1`) AND (`test`.`j3`.`c1` = `test`.`j1`.`c1`))
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row IN SET (0.00 sec)

В колонке type Вы теперь можете видеть значение ALL, что означает, что MySQL читает таблицу целиком. Это происходит потому, что в таблицах нет индексов.

Есть ещё кое-что интересное, на что стоило бы обратить внимание и раньше: Вы, наверное, уже заметили, что все перечисленные через запятую таблицы автоматически объединяются оптимизатором MySQL JOIN'ами. Таким образом споры о том, что работает быстрее и оптимальнее — перечисление таблиц через запятую или JOIN, — становятся бессмысленными, т.к. это по сути одно и то же.

И последнее. Давайте посмотрим, что EXPLAIN EXTENDED может нам рассказать о поведении оптимизатора MySQL при использовании view, который использует алгоритм MERGE:
mysql> CREATE VIEW v1 AS SELECT * FROM j1;
Query OK, 0 rows affected (0.10 sec)
mysql> EXPLAIN extended SELECT * FROM v1 WHERE c1=1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | j1 | ALL | NULL | NULL | NULL | NULL | 2 | USING WHERE
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row IN SET, 1 warning (0.00 sec)
mysql> SHOW warnings;
+-------+------+-------------------------------------------------------------------------------+
| Level | Code | Message
+-------+------+-------------------------------------------------------------------------------+
| Note | 1003 | SELECT `test`.`j1`.`c1` AS `c1` FROM `test`.`j1` WHERE (`test`.`j1`.`c1` = 1)
+-------+------+-------------------------------------------------------------------------------+
1 row IN SET (0.00 sec)

Самая интересное и важное тут написано в WHERE. Как Вы видите, запрос, описанный при создании view, был переписан согласно параметрам, указанным мной в WHERE запроса, обращающегося к нему.

От переводчика

Интересные факты из комментариев к статье:

• EXPLAIN EXTENDED удобно использовать для определения, почему же не используется созданный Вами индекс. В особенности это становится очевидным, когда где-то в полях не совпадает кодировка. В этом случае вывод будет выглядеть примерно так:
  SELECT … where (`b`.`t1`.`a` = convert(`b`.`t2`.`b` using utf8))
• Пользоваться командой командой EXPLAIN EXTENDED на продакшеновых серверах стоит с большой осторожностью, т.к. описаны баги, роняющие сервер (proof link)