Explain

Оператор EXLPAIN используется для анализа запросов. При помощи этого оператора можно увидеть какие запросы не являются оптимальными и оптимизировать их. Для получения информации о запросе необходимо записать оператор EXPLAIN прямо перед самим запросом, например:

EXPLAIN SELECT * FROM student ;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | student | ALL  | NULL          | NULL | NULL    | NULL |    8 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.14 sec)

  • id – порядковый номер для каждого SELECT’а внутри запроса, строк будет столько, сколько операторов SELECT в запросе.

  • select_type – тип запроса SELECT.

    • SIMPLE — Простой запрос SELECT без подзапросов или UNION’ов
    • PRIMARY – данный SELECT – самый внешний запрос в JOIN’е
    • DERIVED – данный SELECT является частью подзапроса внутри FROM
    • SUBQUERY – первый SELECT в подзапросе
    • DEPENDENT SUBQUERY – подзапрос, который зависит от внешнего запроса
    • UNCACHABLE SUBQUERY – не кешируемый подзапрос (существуют определенные условия для того, чтобы запрос кешировался)
    • UNION – второй или последующий SELECT в UNION’е
    • DEPENDENT UNION – второй или последующий SELECT в UNION’е, зависимый от внешнего запроса
    • UNION RESULT – результат UNION’а

  • table – таблица, которая использована для запроса. Значения могут совпадать с существующими таблицами, либо могут принимать специальные значения, например если был записан запрос в предложении FROM или если был использован оператор UNION.

  • type — то как система осуществляет соединение таблиц. Иногда говорят, что это то, как осуществляется доступ к значениям в таблице. Например, производился поиск по всей таблице, либо же по определенному интервалу. Или же поиск производился исключительно по индексу. Это одно из наиболее полезных полей в выводе потому, что может сообщать об отсутствующих индексах или почему написанный запрос должен быть пересмотрен и переписан. Возможные значения:

    • System – таблица имеет только одну строку
    • Const – таблица имеет только одну соответствующую строку, которая проиндексирована. Это наиболее быстрый тип соединения потому, что таблица читается только один раз и значение строки может восприниматься при дальнейших соединениях как константа.
    • Eq_ref – все части индекса используются для связывания. Используемые индексы: PRIMARY KEY или UNIQUE NOT NULL. Это еще один наилучший возможный тип связывания.
    • Ref – все соответствующие строки индексного столбца считываются для каждой комбинации строк из предыдущей таблицы. Этот тип соединения для индексированных столбцов выглядит как использование операторов = или < = >
    • Ref_or_null – то же самое, что и ref, но также содержит строки со значением null для столбца
    • Fulltext – соединение использует полнотекстовый индекс таблицы
    • Index_merge – соединение использует список индексов для получения результирующего набора. Столбец key вывода команды EXPLAIN будет содержать список использованных индексов.
    • Unique_subquery – подзапрос IN возвращает только один результат из таблицы и использует первичный ключ.
    • Index_subquery – то же, что и предыдущий, но возвращает более одного результата.
    • Range – индекс, использованный для нахождения соответствующей строки в определенном диапазоне, обычно, когда ключевой столбец сравнивается с константой, используя операторы вроде: BETWEEN, IN, >, >=, etc.
    • Index – сканируется все дерево индексов для нахождения соответствующих строк.
    • All – Для нахождения соответствующих строк используются сканирование всей таблицы. Это наихудший тип соединения и обычно указывает на отсутствие подходящих индексов в таблице.

  • possible_keys – показывает индексы, которые могут быть использованы для нахождения строк в таблице. На практике они могут использоваться, а могут и не использоваться. Фактически, этот столбец может сослужить добрую службу в деле оптимизации запросов, т.к. значение NULL указывает на то, что не найдено ни одного подходящего индекса.

  • key– указывает на использованный индекс. Этот столбец может содержать индекс, не указанный в столбце possible_keys. Это происходит в тех случаях, когда, например, SELECT FROM таблицы никаких условий WHERE нет. Оптимизатор видит, что, вроде как, никаких ключей использовать не надо, а потом смотрит, что столбцы являются частью ключа и, в принципе, можно их выбрать именно по индексу и тогда делать покрывающий индекс. Так может получиться, что possible_key: NULL, а key – покрывающий индекс, это значит, что он будет использовать именно индекс.

  • key_len – длина индекса, которую оптимизатор MySQL выбрал для использования. Например, значение key_len, равное 4, означает, что памяти требуется для хранения 4 знаков.

  • ref – указываются столбцы или константы, которые сравниваются с индексом, указанным в поле key. MySQL выберет либо значение константы для сравнения, либо само поле, основываясь на плане выполнения запроса.

  • rows – отображает число записей, обработанных для получения выходных данных. Это еще одно очень важное поле, которое дает повод оптимизировать запросы, особенно те, которые используют JOIN’ы и подзапросы.

  • extra – содержит дополнительную информацию, относящуюся к плану выполнения запроса:

    • Distinct - После нахождения первой совпадающей строки MySQL не будет продолжать поиск строк для текущей комбинации строк.
    • Not exists - MySQL смог осуществить оптимизацию LEFT JOIN для запроса и после нахождения одной строки, соответствующей критерию LEFT JOIN, не будет искать в этой таблице последующие строки для предыдущей комбинации строк. Например: SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL; Предположим, что столбец t2.id определен как NOT NULL. В этом случае MySQL просмотрит таблицу t1 и будет искать строки в t2 соответствующие t1.id. Если MySQL находит в t2 нужную строку, он знает, что t2.id никогда не может иметь значение NULL, и не будет искать в t2 оставшуюся часть строк, имеющих тот же самый id. Другими словами, для каждой строки в t1 MySQL должен выполнить только один поиск в t2, независимо от того, сколько совпадающих строк содержится в t2.
    • range checked for each record (index map: #) - MySQL не нашел достаточно хорошего индекса для использования. Вместо этого для каждой комбинации строк в предшествующих таблицах он будет проверять, какой индекс следует использовать (если есть какой-либо индекс), и применять его для поиска строк в таблице. Это делается не очень быстро, но таким образом таблицы связываются быстрее, чем без индекса.
    • Using filesort - MySQL должен будет сделать дополнительный проход, чтобы выяснить, как извлечь строки в порядке сортировки. Для выполнения сортировки выполняется просмотр всех строк согласно типу связывания (join type) и сохраняются ключ сортировки + указатель на строку для всех строк, удовлетворяющих выражению WHERE. После этого ключи сортируются и строки извлекаются в порядке сортировки.
    • Using index - Для извлечения данных из столбца используется только информация дерева индексов; при этом нет необходимости производить собственно чтение записи. Это применимо для случаев, когда все используемые столбцы таблицы являются частью одного индекса.
    • Using temporary - Чтобы выполнить запрос, MySQL должен будет создать временную таблицу для хранения результата. Это обычно происходит, если предложение ORDER BY выполняется для набора столбцов, отличного от того, который используется в предложении GROUP BY.
    • Where used - Выражение WHERE будет использоваться для выделения тех строк, которые будут сопоставляться со следующей таблицей или тех, которые будут посланы клиенту. Если этой информации нет, а таблица имеет тип ALL или index, то, значит, в вашем запросе есть какая-то ошибка (если вы не собираетесь делать выборку/тестирование всех строк таблицы).

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

Кроме того появился json формат вывода EXPLAIN - он менее удобен для чтения, но чуть более информативен.

Дополнительно:

  • https://habr.com/post/211022/

  • http://www.mysql.ru/docs/man/EXPLAIN.html

  • https://habr.com/post/31129/

  • https://devionity.com/ru/courses/mysql-pro/explain