EXPLAIN - значения результирующих столбцов

EXPLAIN - значения результирующих столбцов

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

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

Как использовать EXPLAIN ?

Для примера: посмотрим, как работает запрос выборки товаров из определённого заказа.

Имеем таблицу заказов orders с полем id заказа и таблицу продуктов каждого заказа order_product с полем order_id , соответствующему полю id в таблице заказов. Таблица тестовая, поэтому количество заказов и продуктов будет небольшим.

Поле orders.id имеет первичный ключ.

Рассмотрим 1-й вариант — поле order_product.order_id не имеет индекса.

Делаем sql-запрос

EXPLAIN SELECT * FROM order_product JOIN orders WHERE order_product.order_id = orders.id AND orders.id = 8

Результат выдаст такую таблицу:

explain

Обратите внимание на поле rows – оно отображает количество записей в таблице order_product, обработанных для получения выходных данных (напомню: таблица тестовая – в ней всего 17 записей, в реальных же проектах их будет сколь угодно много).

2-й вариант — добавим полю order_product.order_id индекс.

Делаем тот же самый sql-запрос

EXPLAIN SELECT * FROM order_product JOIN orders WHERE order_product.order_id = orders.id AND orders.id = 8

Результат выдаст такую таблицу:

explain

В поле rows количество обработанных записей снизилось до 2-х — да, именно столько товаров относятся к данному заказу. Соответственно, затраченное время на обработку самого SELECT запроса в проекте будет значитеьно меньше.

Конечно, результирующая таблица EXPLAIN не ограничивается только полем rows, вы можете заметить изменения и других полях, информация в которых позволяет разглядеть уровень оптимизации при данном запросе и сделать соответствующие выводы.

Ниже привожу список полей EXPLAIN и их значений

  • 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, >, >= и т.п.
    • 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, то, значит, в вашем запросе есть какая-то ошибка (если вы не собираетесь делать выборку/тестирование всех строк таблицы)

**  В SQL оператор <=> (не путать со spaceship в php), как и оператор = , выполняет сравнение на равенство, но возвращает 1 не только в случае равенства операндов, но и если оба операнда равны NULL . В случае, если только один из операндов равен NULL , возвращает 0.


mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
        -> 1, NULL, NULL

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

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

Поиск

Популярное