Читаем EXPLAIN на максималках

Многим, кто работает с MySQL, известно, что команда EXPLAIN используется для оптимизации запросов, получения информации об использованных и возможных индексах. Большинство разработчиков и администраторов СУБД этой информацией и ограничивается. Я же предлагаю изучить команду EXPLAIN максимально подробно.

Логическая архитектура MySQL

Чтобы понять, как работает EXPLAIN,  стоит вспомнить логическую архитектуру MySQL. 

Её можно разделить на несколько уровней:

1. Уровень приложения или клиентский уровень. Он не является уникальным для MySQL. Обычно здесь находятся утилиты, библиотеки или целые приложения, которые подключаются к серверу MySQL.

2. Уровень сервера MySQL. Его можно разделить на подуровни:
   
   A. Пул соединений. Сюда относятся аутентификация, безопасность и обработка соединений/потоков. Всякий раз, когда клиент подключается к серверу MySQL, тот выполняет аутентификацию по имени пользователя, хосту клиента и паролю. После того, как клиент успешно подключился, сервер проверяет, имеет ли этот клиент привилегии для выполнения определенных запросов, и, если да, то он получает для своего соединения отдельный поток. Потоки кешируются сервером, поэтому их не нужно создавать и уничтожать для каждого нового соединения.

   B. Сервер MySQL. Этот подуровень во многих источниках называют «мозгами» MySQL. К нему относятся такие компоненты, как кеши и буферы, парсер SQL, оптимизатор, а также все встроенные функции (например, функции даты/времени и шифрования).

3. Уровень подсистем хранения. Подсистемы хранения отвечают за хранение и извлечение данных в MySQL.

Нас интересует второй уровень, точнее подуровень «сервер MySQL», ведь именно здесь выполняет свои прямые обязанности оптимизатор запросов. Набор операций, которые оптимизатор выбирает для выполнения эффективного запроса, называется «Планом выполнения запроса», также известного как EXPLAIN-план.

Команда EXPLAIN

Выражение EXPLAIN предоставляет информацию о том, как MySQL выполняет запрос. Оно работает с выражениями SELECT, UPDATE, INSERT, DELETE и REPLACE.

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

Стандартный вывод команды EXPLAIN покажет колонки:

id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra

Представим, что у нас есть база данных нашей небольшой фирмы такси, в, которой хранятся водители, автомобили, клиенты и заказы.

Для начала выполним простой запрос:

EXPLAIN SELECT 1

id: 1
select_type: SIMPLE
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: No tables used

Столбец ID

Этот столбец можно назвать идентификатором или порядковым номером каждого SELECT- запроса. Все выражения SELECT нумеруются последовательно, их можно отнести к простым (без подзапросов и объединений) и составным. Составные запросы SELECT можно отнести к:

A. Простым подзапросам

EXPLAIN SELECT (SELECT 1 from Orders) from Drivers

B. Подзапросам с производными таблицами, то есть с подзапросом в разделе FROM

EXPLAIN SELECT * FROM (SELECT 1, 2) AS tmp (a, b)

Как я уже писал выше, этот запрос создаст временную таблицу и MySQL будет ссылаться на неё по псевдониму tmp. В более сложных запросах этот псевдоним будет указан в столбце ref. В первой строке, в столбце table можно увидеть название таблицы , которое формируется по правилу , где N — ID запроса.

C. Подзапросам с объединением UNION

EXPLAIN SELECT id FROM Cars UNION SELECT id FROM Drivers

Здесь есть несколько отличий от примера c FROM-подзапросом. Во-первых, MySQL помещает результат объединения во временную таблицу, из которой, затем, считывает данные. К тому же эта временная таблица отсутствует в исходной SQL-команде, поэтому в столбце id для неё будет null. Во-вторых, временная таблица, появившаяся в результате объединения, показана последней, а не первой.

Точно по такому же правилу формируется название таблица в столбце  table <unionN,M>, где N — ID первого запроса, а M — второго.

Столбец select_type

Показывает тип запроса SELECT для каждой строки результата EXPLAIN. Если запрос простой, то есть не содержит подзапросов и объединений, то в столбце будет значение SIMPLE. В противном случае, самый внешний запрос помечается как PRIMARY, а остальные следующим образом:

• SUBQUERY. Запрос SELECT, который содержится в подзапросе, находящимся в разделе SELECT (т.е. не в разделе FROM).

• DERIVED. Обозначает производную таблицу, то есть этот запрос SELECT является подзапросом в разделе FROM. Выполняется рекурсивно и помещается во временную таблицу, на которую сервер ссылается по имени “derived table”.
  
  Обратите внимание: все подзапросы в разделе FROM являются производной таблицей, однако, не все производные таблицы являются подзапросами в разделе FROM.

• UNION. Если присутствует объединение UNION, то первый входящий в него запрос считается частью внешнего запроса и помечается как PRIMARY  (см. пример выше). Если бы объединение UNION было частью подзапроса в разделе FROM, то его первый запрос SELECT был бы помечен как DERIVED. Второй и последующий запросы помечаются как UNION.

• UNION RESULT. Показывает результата запроса SELECT, который сервер MySQL применяет для чтения из временной таблицы, которая была создана в результате объединения UNION.

Кроме того, типы SUBQUERY, UNION и DERIVED могут быть помечены как DEPENDENT, то есть результат SELECT зависит от данных, которые встречаются во внешнем запросе SELECT.

Также типы SUBQUERY и UNION могут быть помечены как UNCACHABLE. Это говорит о том, что результат SELECT не может быть закеширован и должен быть пересчитан для каждой строки внешнего запроса. Например, из-за функции RAND().

Начиная с версии MySQL 5.7 в столбце select_type появилось еще одно значение MATERIALIZED, которое говорит о том, что результат подзапроса был сохранен как виртуальная временная таблица и не будет выполняться для каждой следующей строки внешнего запроса.

Столбец table

Показывает, к какой таблице относится эта строка. В самом простом случае — это таблица (или её псевдоним) из вашей SQL- команды.

При объединении таблиц стоит читать столбец table сверху вниз.

EXPLAIN SELECT Clients.id
        FROM Clients
        JOIN Orders ON Orders.client_id = Clients.id
        JOIN Drivers ON Orders.driver_id = Drivers.id

Здесь мы видим порядок объединения таблиц, который выбрал оптимизатор. Однако, порядок не всегда будет таким, в каком объединяются таблицы в вашем запросе.

Если запрос содержит подзапрос FROM или объединение UNION, то столбец table читать будет не так просто, потому что MySQL будет создавать временные таблицы, на которые станет ссылаться.

О столбце table для подзапроса FROM я уже писал выше. Ссылка derived.N является- опережающей, то есть N — ID запроса ниже. А ссылка UNION RESULT (union N,M) является обратной, поскольку встречается после всех строк, которые относятся к объединению UNION.

Попробуем, для примера, прочитать столбец table для следующего странного запроса:

EXPLAIN SELECT id, (SELECT 1 FROM Orders WHERE client_id = t1.id LIMIT 1)
       FROM (SELECT id FROM Drivers LIMIT 5) AS t1
       UNION
       SELECT driver_id, (SELECT @var1 FROM Cars LIMIT 1)
       FROM (
           SELECT driver_id, (SELECT 1 FROM Clients)
           FROM Orders LIMIT 5
       ) AS t2

Не так просто разобраться в этом, но, тем не менее, мы попробуем.

1. Первая строка является опережающей ссылкой на производную таблицу t1, помеченную как <derived3>.

2. Значение идентификатора строки равно 3, потому что строка относится к третьему по порядку SELECT. Поле select_type имеет значение DERIVED, потому что подзапрос находится в разделе FROM.

3. Третья строка с ID = 2 идет после строки с бОльшим ID, потому что соответствующий ей подзапрос выполнился позже, что логично, ведь нельзя получить значение t1.id, не выполнив подзапрос с ID = 3. Признак DEPENDENT SUBQUERY означает, что результат зависит от результатов внешнего запроса.

4. Четвертая строка соответствует второму или последующему запросу объединения, поэтому она помечена признаком UNION. Значение <derived6> означает, что данные будут выбраны из подзапроса FROM и добавятся во временную таблицу для результатов UNION.

5. Пятая строка —- это наш подзапрос FROM, помеченный как t2.

6. Шестая строка указывает на обычный подзапрос в SELECT. Идентификатор этой строки равен 7, что важно, потому что следующая строка уже имеет ID = 5.

7. Почему же важно, что седьмая строка имеет меньший ID, чем шестая? Потому что каждая строка, помеченная как DERIVED , открывает вложенную область видимости. Эта область видимости закрывается, когда встречается строка с ID меньшим, чем у DERIVED (в данном случае 5 < 6). Отсюда можно понять, что седьмая строка является частью SELECT, в котором выбираются данные из <derived6>. Признак UNCACHEABLE в колонке select_type добавляется из-за переменной @var1.

8. Последняя строка UNION RESULT представляет собой этап считывания строк из временной таблицы после объединения UNION.

При чтении с помощью EXPLAIN больших запросов часто приходится читать результат сверху вниз и наоборот. Например, в этом примере имеет смысл начать чтение с последней строки, чтобы понять, что первая строка является частью UNION.

Столбец partitions

Показывает, какой партиции соответствуют данные из запроса. Если вы не используете партиционирование, то значение этой колонки будет null.

Столбец type

Показывает информацию о том, каким образом MySQL выбирает данные из таблицы. Хотя в документации MySQL это поле описывается как “The join type”, многих такое описание смущает или кажется не до конца понятным. Столбец type принимает одно из следующих значений, отсортированных в порядке скорости извлечения данных:

• ALL. Обычно речь идет о полном сканировании таблицы, то естьт.е. MySQL будет просматривать строчку за строчкой, если только в запросе нет LIMIT или в колонке extra не указано Distinct/not exists, к чему мы вернемся позже.

• index. В этом случае MySQL тоже просматривает таблицу целиком, но в порядке, заданном индексом. В этом случае не требуется сортировка, но - строки выбираются в хаотичном порядке. Лучше, если в колонке extra будет указано using index, что означает, что вместо полного сканирования таблицы, MySQL проходит по дереву индексов. Такое происходит, когда удалось использовать покрывающий индекс

• range. Индекс просматривается в заданном диапазоне. Поиск начинается в определенной точке индекса и возвращает значения, пока истинно условие поиска. range может быть использован, когда проиндексированный столбец сравнивается с константой с использованием операторов =, <>, >, >=, <, <=, IS_NULL, <=>, BETWEEN, LIKE или IN.

• index_subquery. Вы увидите это значение, если в операторе IN есть подзапрос, для которого оптимизатор MySQL смог использовать поиск по индексу.

• unique_subquery. Похож на index_subquery, но, для подзапроса используется уникальный индекс, такой как Primary key или Unique index.

• index_merge. Если оптимизатор использовал range-сканирование для нескольких таблиц, он может объединить их результаты. В зависимости от метода слияния, поле extra примет одно из следующих значений:  Using intersect — пересечение, Using union — объединение, Using sort_union — объединение сортировки слияния (подробнее читайте здесь)

• ref_or_null. Этот случай похож на ref, за исключением того, что MySQL будет выполнять второй просмотр для поиска записей, содержащих NULL- значения.

• fulltext. Использование FULLTEXT-индекса.

• ref. Поиск по индексу, в результате которого возвращаются все строки, соответствующие единственному заданному значению. Применяется в случаях, если ключ не является уникальным, то есть не Primary key или Unique index , либо используется только крайний левый префикс ключа. ref может быть использован только для операторов = или <=>.

• eq_ref. Считывается всего одна строка по первичному или уникальному ключу. Работает только с оператором =. Справа от знака “=” может быть константа или выражение.

• const. Таблица содержит не более одной совпадающей строки. Если при оптимизации MySQL удалось привести запрос к константе, то столбец type будет равен const. Например, если вы ищете что-то по первичному ключу, то оптимизатор может преобразовать значение в константу и исключить таблицу из соединения JOIN.

• system. В таблице только одна строка. Частный случай const.

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

Столбец possible_keys

Показывает, какие индексы можно использовать для запроса. Этот столбец не зависит от порядка таблиц, отображаемых EXPLAIN, поскольку список создается на ранних этапах оптимизации. Если в столбце значение NULL, то соответствующих индексов не нашлось.

Столбец keys

Указывает на ключ (индекс), который оптимизатор MySQL решил фактически использовать. Может вообще отсутствовать в столбце possible_keys. Так бывает в случаях, когда ни один из ключей в possible_keys не подходит для поиска строк, но все столбцы, выбранные запросом, являются частью какого-то другого индекса. Тогда оптимизатор может задействовать эти столбцы для поиска по покрывающему индексу, так как сканирование индекса эффективнее, чем сканирование таблицы целиком.

Столбец key_len

Показывает длину выбранного ключа (индекса) в байтах.  Например, если у вас есть primary key id типа int, то, при его использовании, key_len будет равен 4, потому что длина int всегда равна 4 байта. В случае составных ключей key_len будет равен сумме байтов их типов. Если столбец key равен NULL, то значение key_len так же будет NULL.

EXPLAIN SELECT * FROM Orders
WHERE client_id = 1

EXPLAIN SELECT * FROM Orders
WHERE client_id = 1 AND driver_id = 2

Столбец ref

Показывает, какие столбцы или константы сравниваются с указанным в key индексом. Принимает значения NULL, const или название столбца другой таблицы. Возможно значение func, когда сравнение идет с результатом функции. Чтобы узнать, что это за функция, можно после EXPLAIN выполнить команду SHOW WARNINGS.

EXPLAIN SELECT * FROM Drivers

EXPLAIN SELECT * FROM Drivers
WHERE id = 1

EXPLAIN SELECT * FROM Drivers
JOIN Orders ON Drivers.id = Orders.driver_id

Столбец rows

Показывает количество строк, которое, по мнению MySQL, будет прочитано. Это число является приблизительным и может оказаться очень неточным. Оно вычисляется при каждой итерации плана выполнения с вложенными циклами. Часто это значение путают с количеством строк в результирующем наборе, что неверно, потому что столбец rows показывает количество строк, которые нужно будет просмотреть. При вычислении значения не учитываются буферы соединения и кеши (в том числе кеши ОС и оборудования), поэтому реальное число может быть намного меньше предполагаемого.

Столбец filtered

Показывает, какую долю от общего количества числа просмотренных строк вернет движок MySQL. Максимальное значение 100, то есть будет возвращено все 100 % просмотренных строк. Если умножить эту долю на значение в столбце rows, то получится приблизительная оценка количества строк, которые MySQL будет соединять с последующими таблицами. Например, если в строке rows 100 записей, а значение filtered 50,00 (50 %), то это число будет вычислено как 100 x 50 % = 50.

Столбец Extra

В этом столбце собрана различная дополнительная информация. Ниже я разберу все возможные значения. Наиболее важные из них будут помечены (!!!). Такие значения не всегда означают, что это плохо или хорошо, но точно стоит обратить на них внимание.

• const row not found. Для запроса, вида SELECT … FROM table, таблица table оказалась пустая.

• Deleting all rows. Некоторые движки MySQL, такие как MyISAM, поддерживают методы быстрого удаления всех строк из таблицы. Если механизм удаления поддерживает эту оптимизацию, то значение Deleting all rows будет значением в столбце Extra.

• Distinct. Если в запросе присутствует DISTINCT, то MySQL прекращает поиск, после нахождения первой подходящей строки.

• FirstMatch (table_name). Если в системной переменной optimizer_switch есть значение firstmatch=on, то MySQL может использовать для подзапросов стратегию FirstMatch, которая позволяет избежать поиска дублей, как только будет найдено первое совпадение. Представим, что один и тот же водитель возил клиента с id = 10 больше, чем один раз, тогда для этого запроса:
  
  EXPLAIN
  SELECT id FROM Drivers
  WHERE Drivers.id IN (SELECT driver_id FROM Orders WHERE client_id = 10)
  
  MySQL может применить стратегию FirstMatch, поскольку нет смысла дальше искать записи для этого водителя.

• Full scan on NULL key. Обычно такая запись идет после Using where как запасная стратегия, если оптимизатор не смог использовать метод доступа по индексу.

• Impossible HAVING. Условие HAVING всегда ложно.

• Impossible WHERE. Условие WHERE всегда ложно.

• Impossible WHERE noticed after reading const tables. MySQL просмотрел все const (и system) таблицы и заметил, что условие WHERE всегда ложно.

• LooseScan(m..n). Стратегия сканирования индекса при группировке GROUP BY. Подробнее читайте здесь.

• No matching min/max row. Ни одна строка не удовлетворяет условию запроса, в котором используются агрегатные функции MIN/MAX.

• No matching rows after partition pruning. По смыслу похож на Impossible WHERE для выражения SELECT, но для запросов DELETE или UPDATE.

• No tables used. В запросе нет FROM или есть FROM DUAL.

• Not exists. Сервер MySQL применил алгоритм раннего завершения. То есть применена оптимизация, чтобы избежать чтения более, чем одной строки из индекса. Это эквивалентно подзапросу NOT EXISTS(), прекращение обработки текущей строки, как только найдено соответствие.

• Plan isn’t ready yet. Такое значение может появиться при использовании команды EXPLAIN FOR CONNECTION, если оптимизатор еще не завершил построение плана.

• Range check for each record (!!!). Оптимизатор не нашел подходящего индекса, но обнаружил, что некоторые индексы могут быть использованы после того, как будут известны значения столбцов из предыдущих таблиц. В этом случае оптимизатор будет пытаться применить стратегию поиска по индексу range или index_merge.

• Recursive.Такое значение появляется для рекурсивных (WITH) частей запроса в столбце extra.

• Scanned N databases. Сколько таблиц INFORMATION_SCHEMA было прочитано. Значение N может быть 0, 1 или all.

• Select tables optimized away (!!!). Встречается в запросах, содержащих агрегатные функции (но без GROUP BY). Оптимизатор смог молниеносно получить нужные данные, не обращаясь к таблице, например, из внутренних счетчиков или индекса. Это лучшее значение поля extra, которое вы можете встретить при использовании агрегатных функций.

• Skip_open_table, Open_frm_only, Open_full_table. Для каждой таблицы, которую вы создаете, MySQL создает на диске файл .frm, описывающий структуру таблицы. Для подсистемы хранения MyISAM так же создаются файлы .MYD с данными и .MYI с индексами. В запросах к INFORMATION_SCHEMA Skip_open_table означает, что ни один из этих файлов открывать не нужно, вся информация уже доступна в словаре (data dictionary). Для Open_frm_only потребуется открыть файлы .frm. Open_full_table указывает на необходимость открытия файлов .frm, .MYD и .MYI.

• Start temporary, End temporary. Еще одна стратегия предотвращения поиска дубликатов, которая называется DuplicateWeedout. При этом создаётся временная таблица, что будет отображено как Start temporary. Когда значения из таблицы будут прочитаны, это будет отмечено в колонке extra как End temporary. Неплохое описание читайте здесь.

• unique row not found (!!!). Для запросов SELECT … FROM table ни одна строка не удовлетворяет поиску по PRIMARY или UNIQUE KEY.

• Using filesort (!!!). Сервер MySQL вынужден прибегнуть к внешней сортировке, вместо той, что задаётся индексом. Сортировка может быть произведена как в памяти, так и на диске, о чем EXPLAIN никак не сообщает.

• Using index (!!!). MySQL использует покрывающий индекс, чтобы избежать доступа к таблице.

• Using index condition (!!!). Информация считывается из индекса, чтобы затем можно было определить, следует ли читать строку целиком. Иногда стоит поменять местами условия в WHERE или прокинуть дополнительные данные в запрос с вашего бэкенда, чтобы Using index condition превратилось в Using index.

• Using index for group-by (!!!). Похож на Using index, но для группировки GROUP BY или DISTINCT. Обращения к таблице не требуется, все данные есть в индексе.

• Using join buffer (Block nested loop | Batched Key Access | hash join). Таблицы, получившиеся в результате объединения (JOIN), записываются в буфер соединения (Join Buffer). Затем новые таблицы соединяются уже со строками из этого буфера. Алгоритм соединения (Block nested loop | Batched Key Access | hash join) будет указан в колонке extra.

• Using sort_union, Using union, Using intersect. Показывает алгоритм слияния, о котором я писал выше для index_merge столбца type.

• Using temporary (!!!). Будет создана временная таблица для сортировки или группировки результатов запроса.

• Using where (!!!). Сервер будет вынужден дополнительно фильтровать те строки, которые уже были отфильтрованы подсистемой хранения. Если вы встретили Using where в столбце extra, то стоит переписать запрос, используя другие возможные индексы.

• Zero limit. В запросе присутствует LIMIT 0.

Команда SHOW WARNINGS

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

EXPLAIN SELECT
              Drivers.id,
              Drivers.id IN (SELECT Orders.driver_id FROM Orders)
FROM Drivers;

SHOW WARNINGS;

/* select#1 */ select `explain`.`Drivers`.`id` AS `id`,
<in_optimizer>(`explain`.`Drivers`.`id`,
<exists>(<index_lookup>(<cache>(`explain`.`Drivers`.`id`) in Orders on Orders_Drivers_id_fk))) AS `Drivers.id 
IN (SELECT Orders.driver_id FROM Orders)` from `explain`.`Drivers`

Отображаемое сообщение получено напрямую из плана запроса, а не из исходной SQL- команды. Сервер генерирует информацию, исходя из плана выполнения. Поэтому отображённый запрос будет иметь ту же семантику, что и исходный, но не тот же текст (в большинстве случаев).

SHOW WARNINGS содержит специальные маркеры, которые не являются допустимым SQL -выражением. Вот их список:

• <auto_key>. Автоматически сгенерированный ключ для временной таблицы.

• <cache> (expr). Выражение expr выполняется один раз, значение сохраняется в памяти. Если таких значений несколько, то вместо <cache> будет создана временная таблица с маркером <temporary table>.

• <exists> (query fragment). Предикат подзапроса был преобразован в EXISTS -предикат, а сам подзапрос был преобразован таким образом, чтобы его можно было использовать совместно с EXISTS.

• <in_optimizer> (query fragment). Внутренний объект оптимизатора, не обращаем внимания.

• <index_lookup> (query fragment). Этот фрагмент запроса обрабатывается с помощью поиска по индексу.

• <if> (condition, expr1, expr2). Если условие истинно, то выполняем expr1, иначе expr2.

• <is_not_null_test> (expr). Тест для оценки того, что выражение expr не преобразуется в null.

• <materialize> (query fragment). Подзапрос был материализован.

• ‘materialized-subquery’.col_name. Ссылка на столбец col_name была материализована.

• <primary_index_lookup> (query fragment). Фрагмент запроса обрабатывается с помощью индекса по первичному ключу.

• <ref_null_helper> (expr). Внутренний объект оптимизатора, не обращаем внимания.

• /* select # N */. SELECT относится к строке с номером id = N из результата EXPLAIN.

• <temporary table>. Представляет собой временную таблицу, которая используется для кеширования результатов.

Читаем EXPLAIN

Учитывая всё вышесказанное, пора дать ответ на вопрос -  так как же стоит правильно читать EXPLAIN?

Начинаем читать каждую строчку сверху вниз. Смотрим на колонку type. Если индекс не используется — плохо (за исключением случаев, когда таблица очень маленькая или присутствует ключевое слово LIMIT). В этом случае оптимизатор намеренно предпочтет просканировать таблицу. Чем ближе значение столбца type к NULL (см. пункт о столбце type), тем лучше.

Далее стоит посмотреть на колонки rows и filtered. Чем меньше значение rows  и чем больше значение filtered,- тем лучше. Однако, если значение rows слишком велико и filtered стремится к 100 %  - это очень плохо.

Смотрим, какой индекс был выбран из колонки key , и сравниваем со всеми ключами из possible_keys. Если индекс не оптимальный (большая селективность), то стоит подумать, как изменить запрос или пробросить дополнительные данные в условие выборки, чтобы использовать наилучший индекс из possible_keys.

Наконец, читаем колонку Extra. Если там значение, отмеченное выше как (!!!), то, как минимум, обращаем на это вниманием. Как максимум, пытаемся разобраться, почему так. В этом нам может хорошо помочь SHOW WARNINGS.

Переходим к следующей строке и повторяем всё заново.

Если не лень, то в конце перемножаем все значения в столбце rows всех строк, чтобы грубо оценить количество просматриваемых строк.

При чтении всегда помним о том, что:

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

• EXPLAIN не работает с хранимыми процедурами.

• EXPLAIN не расскажет об оптимизациях, которые MySQL производит уже на этапе выполнения запроса.

• Большинство статистической информации — всего лишь оценка, иногда очень неточная.

• EXPLAIN не делает различий между некоторыми операциями, называя их одинаково. Например, filesort может означать сортировку в памяти и на диске, а временная таблица, которая создается на диске или в памяти, будет помечена как Using temporary.

• В разных версиях MySQL EXPLAIN может выдавать совершенно разные результаты, потому что оптимизатор постоянно улучшается разработчиками, поэтому не забываем обновляться.

EXPLAIN TREE FORMAT и EXPLAIN ANALYZE

Если вы счастливый обладатель восьмой версии MySQL, то в вашем арсенале появляются очень полезные команды, которые позволяют читать план выполнения и информацию о стоимости запроса без использования SHOW WARNINGS.

С версии 8.0.16 можно вывести план выполнения в виде дерева, используя выражение FORMAT=TREE:

EXPLAIN FORMAT = TREE select * from Drivers
   join Orders on Drivers.id = Orders.driver_id
   join Clients on Orders.client_id = Clients.id

-> Nested loop inner join  (cost=1.05 rows=1)
   -> Nested loop inner join  (cost=0.70 rows=1)
       -> Index scan on Drivers using Drivers_car_id_index  (cost=0.35 rows=1)
       -> Index lookup on Orders using Orders_Drivers_id_fk (driver_id=Drivers.id)  (cost=0.35 rows=1)
   -> Single-row index lookup on Clients using PRIMARY (id=Orders.client_id)  (cost=0.35 rows=1)

Удобство такого формата в том, что мы можем видеть план запроса в виде вложенного дерева. Каждая вложенная строка означает новый цикл. В скобках указана информация об оценочной стоимости и расчетное количество прочитанных строк. Стоимость или стоимость запроса — это некая внутренняя оценка того, насколько «дорого» для MySQL выполнять этот запрос, основанная на различных внутренних метриках.

Еще более подробную информацию можно получить, заменив FORMAT = TREE на выражение ANALYZE, которое предоставляет MySQL с версии 8.0.18.

EXPLAIN ANALYZE select * from Drivers
   join Orders on Drivers.id = Orders.driver_id
   join Clients on Orders.client_id = Clients.id

-> Nested loop inner join  (cost=1.05 rows=1) (actual time=0.152..0.152 rows=0 loops=1)
   -> Nested loop inner join  (cost=0.70 rows=1) (actual time=0.123..0.123 rows=0 loops=1)
       -> Index scan on Drivers using Drivers_car_id_index  (cost=0.35 rows=1) (actual time=0.094..0.094 rows=0 loops=1)
       -> Index lookup on Orders using Orders_Drivers_id_fk (driver_id=Drivers.id)  (cost=0.35 rows=1) (never executed)
   -> Single-row index lookup on Clients using PRIMARY (id=Orders.client_id)  (cost=0.35 rows=1) (never executed)

В дополнение к стоимости и количеству строк можно увидеть фактическое время получения первой строки и фактическое время получения всех строк, которые выводятся в формате actual time={время получения первой строки}..{время получения всех строк}. Также теперь появилось еще одно значение rows, которое указывает на фактическое количество прочитанных строк. Значение loops — это количество циклов, которые будут выполнены для соединения с внешней таблицей (выше по дереву). Если не потребовалось ни одной итерации цикла, то вместо расширенной информации вы увидите значение (never executed).

Как видите, обновлять MySQL полезно не только с точки зрения производительности и улучшения оптимизатора, но и для получения новых инструментов для профилирования запросов.

Заключение

Команда EXPLAIN станет отличным оружием в вашем арсенале при работе с БД. Зная несложные правила, вы можете быстро оптимизировать ваши запросы, узнавать различную статистическую информацию, пусть и приближенную. Расширенный вывод подскажет, что было закешировано и где создаются временные таблицы.

Слишком большие запросы могут генерировать пугающие результаты EXPLAIN, но тут, как и в любом деле, важна практика. Переходите от простых запросов к сложным.

Пытайтесь, даже просто так, читать различные виды запросов, содержащие FROM, UNION и JOIN , и сами не заметите, как станете мастером оптимизации.

Литература и источники

1. High Performance MySQL (by Baron Schwartz, Peter Zaitsev, Vadim Tkachenko)

2. https://dev.mysql.com/

3. https://stackoverflow.com/

4. http://highload.guide/

5. https://taogenjia.com/2020/06/08/mysql-explain/

6. https://www.eversql.com/mysql-explain-example-explaining-mysql-explain-using-stackoverflow-data/

7. https://dba.stackexchange.com/

8. https://mariadb.com/

9. https://andreyex.ru/bazy-dannyx/baza-dannyx-mysql/explain-analyze-v-mysql/

10. https://programming.vip/docs/explain-analyze-in-mysql-8.0.html

11. А также много страниц из google.com