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

Original author: Justin Swanhart
  • Translation
Большинство разработчиков на 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)
Share post

Similar posts

Comments 29

    +2
    Спасибо. Хоть и не фанат mysql, но информация оказалось полезной. :)
      –16
      А зачем вы переводите подобные вещи?
        +8
        Статьи переводят для тех, кто не знает язык оригинала. Ваш кэп.
          –27
          а может лучше, тех, кто не знает язык оригинала и не дружит с переводчиками и гугл-транслей-тулз выгонять сразу, за проф не пригодность?
            –6
            Вижу вы такой же умный, как я.
              +1
              т.е вы предлагаете 2 выхода:
              1) выучить язык оригинала
              и
              2) использовать гугл-транслейт для техничный статей.

              успехов
                –8
                я предлагаю выучить язык оригинала или английский. Использовать гугл-транслейт можно, если приходиться переписывать с китайцами.
                  +4
                  а я вам предлагаю родной для начала подучить.
                0
                А я предлагаю выгонять за профнепригодность тех, у кого подбородок на груди лежит.
            0
            Спасибо. Про команду знал, но пользовался редко. Жалко что все примеры на табличках в несколько записей. С ними как раз проблем обычно не возникает
              +1
              Нормальные примеры.
              Эта статейка рассматривает узкий вопрос переписывания запросов. Про обычный EXPLAIN понаписано и так достаточно. Прочитайте сначала все про него.
              –2
              Хорошая статья.
                +3
                Те, кто обладает достаточным уровнем, чтобы таким интересовать, английский знают, чтобы прочитать в оригинале.
                  +2
                  Что-то мне подсказывает, что не станет хуже от того, что знающие английский язык и интересующиеся как сделать свои SQL-запросы лучше люди увидят подходящую по тематике статью на Хабре, а не на одном из массы форумов.
                    0
                    Ничего подобного. Я обладаю достаточным уровнем, чтобы таким интересоваться, но английский не знаю.
                      –2
                      и как вы достигли такого уровня, не научившись читать не сложный английский текст?
                        0
                        Вот именно из таких статей, написанных по-русски.
                    +2
                    Ребята, которые ведут MySQL Performance Blog (см. первоисточник статьи), написали совершенно изумительную книгу по оптимизации производительности MySQL сервера. В прошлом месяце выпустили русскоязычную версию этой книги.

                    MySQL. Оптимизация производительности, 2-е издание
                    www.symbol.ru/alphabet/734220.html
                      –5
                      Вот я и говорю. Тонкие оптимизаторы ищут материалы не на хабре отнюдь, и не надо пытаться этот ход вещей поменять. Хабр — по сути своей курилка для Околоайтишной русскоязычной тусовки. Это не специализированный технический форум MySQL или любых других штучек.
                        +1
                        А что вы изволите читать в разделе MySQL? ;)
                          +3
                          Вас кто-то обманул. На хабре куча статей по всяким тонким штучкам, которых больше нет нигде.
                            –1
                            Ну и зачем минусовать? Есть куча статей, есть. Но это не форум для обсуждения технических вопросов все равно. Не SQL.ru например…
                              +1
                              Вы еще скажите, что Хабр — это не медицинский сайт. Формально будете совершенно правы. А по сути — спорете не имеющую никакого отношения к делу хуйню.
                        0
                        Пользуюсь EXPLAIN EXTENDED давно. На самом деле очень удобно. Спасибо автору.
                          +1
                          У меня что-то не совсем так, как у вас работает:

                          mysql> drop database test;
                          Query OK, 3 rows affected (0.39 sec)

                          mysql> create database test;
                          Query OK, 1 row affected (0.01 sec)

                          mysql> use test
                          Database changed
                          mysql> CREATE TABLE j1 (c1 int);
                          Query OK, 0 rows affected (0.14 sec)

                          mysql> CREATE TABLE j2 (c1 int);
                          Query OK, 0 rows affected (0.09 sec)

                          mysql> CREATE TABLE j3 (c1 int);
                          Query OK, 0 rows affected (0.09 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 | filtered | Extra |
                          +----+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------+
                          | 1 | SIMPLE | j1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | |
                          | 1 | SIMPLE | j2 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer |
                          | 1 | SIMPLE | j3 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer |
                          +----+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------+
                          3 rows in set, 1 warning (0.03 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.02 sec)

                          mysql>
                            0
                            Какая версия mysql? Какой движок у таблиц?
                              0
                              такое может быть, например, если движок по умолчанию иннодб.
                                0
                                да, так и есть :(
                                  0
                                  вероятно это от того, что в innodb не хранится статистика таблицы (например количество всех записей), в отличие от myisam

                            Only users with full accounts can post comments. Log in, please.