company_banner

Читаем 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.

    Если у вас версия ниже 5.6

    До версии 5.6 команда EXPLAIN работала только с выражениями типа SELECT, и, если вам нужен анализ других выражений, то придется переписать запрос в эквивалентный запрос SELECT.

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

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

    id
    select_type
    table
    partitions
    type
    possible_keys
    key
    key_len
    ref
    rows
    filtered
    Extra
    Если у вас версия ниже 5.6

    В этом случае вы не увидите столбцов filtered и partitions. Для их вывода необходимо, после EXPLAIN, добавить ключевые слова EXTENDED или PARTITIONS, но не оба сразу.

    Если у вас версия 5.6

    В версии 5.6 и выше столбец partitions будет включено по-умолчанию, однако для вывода столбца filtered вам всё еще придется воспользоваться ключевым словом EXTENDED.

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

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

    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

    id

    select_type

    table

    1

    PRIMARY

    Drivers

    2

    SUBQUERY

    Orders

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

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

    id

    select_type

    table

    1

    PRIMARY

    <derived2>

    2

    SUBQUERY

    null

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

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

    EXPLAIN SELECT id FROM Cars UNION SELECT id FROM Drivers

    id

    select_type

    table

    1

    PRIMARY

    Cars

    2

    UNION

    Drivers

    null

    UNION RESULT

    <union1,2>

    Здесь есть несколько отличий от примера 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.

    Если у вас версия 5.7 и ниже

    Поле DEPENDENT DERIVED появилось только в 8 версии MySQL.

    Также типы 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

    id

    select_type

    table

    1

    SIMPLE

    Clients

    1

    SIMPLE

    Orders

    1

    SIMPLE

    Drivers

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

    Если запрос содержит подзапрос 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

    id

    select_type

    table

    1

    PRIMARY

    <derived3>

    3

    DERIVED

    Drivers

    2

    DEPENDENT SUBQUERY

    Orders

    4

    UNION

    <derived6>

    6

    DERIVED

    Orders

    7

    SUBQUERY

    Clients

    5

    UNCACHEABLE SUBQUERY

    Cars

    null

    UNION RESULT

    <union1,4>

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

    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

    id

    table

    possible_keys

    key

    key_len

    1

    Orders

    Orders_Clients_id_fk

    Orders_Clients_id_fk

    4

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

    id

    table

    possible_keys

    key

    key_len

    1

    Orders

    Orders_Drivers_id_fk,

    Orders_client_id_driver_id

    Orders_client_id_driver_id

    8

    Столбец ref

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

    EXPLAIN SELECT * FROM Drivers

    id

    table

    ref

    1

    Drivers

    null

    EXPLAIN SELECT * FROM Drivers
    WHERE id = 1

    id

    table

    ref

    1

    Drivers

    const

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

    id

    table

    ref

    1

    Orders

    null

    1

    Drivers

    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, поскольку нет смысла дальше искать записи для этого водителя.

    id

    table

    extra

    1

    Orders

    Using index;

    2

    Drivers

    Using index; FirstMatch(Orders)

    • 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. Тогда вы получите реконструированный запрос.

    Если у вас MySQL 5.6 и ниже

    SHOW WARNINGS работает только после EXPLAIN EXTENDED.

    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

    Comments 14

      +1

      Спасибо за статью, узнал несколько новых для себя вещей. Поставил плюсик.
      Впечатления, однако, смешанные.
      С одной стороны, хорошо что она есть, но с другой — видно, что написана по разнарядке для продвижения блога компании. Хотелось бы большей вовлечённости. Поменьше источников и побольше реальных юзкейсов из собственного опыта.
      Не секрет, что explain используется в первую очередь для оптимизации запросов. А этой теме в статье посвящено до обидного мало. Тому, кто уже разбирается, она даст несколько новых подсказок, но тому, кто захочет с ее помощью научиться оптимизировать запросы, будет очень непросто вычленить ключевые моменты.
      Желательно структурировать информацию, выделяя более значимую. К примеру, действительно важные значения столбца Extra стоит дать подробнее, а всякие диковины типа impossible having, которые только на бумаге и встречаются, я бы убрал под спойлер.


      Чтобы не быть голословным, несколько вещей из собственного опыта


      • надо обращать внимание на размер key length. Если он большой, то стоит подумать над уменьшением. У нас в практике был случай, когда требовалась уникальность для поля, содержащего довольно большой объем данных. Убрав с него индекс и добавив рядом поле, содержащее md5() от этих данных, получили ощутимый прирост производительности.
      • перемножать значения rows надо не "если не лень", а в обязательном порядке. Ну или точнее — следить чтобы там в идеале были единички во всех дополнительных таблицах, поскольку перемножение строк и является основной причиной медленных запросов
      • не нужно переживать из-за Filesort, если значение Rows небольшое и эти действия производятся на финальной стадии — БД вполне может просто отсортировать уже полученный результат прямо в памяти, это никак не повлияет на скорость
      • если запрос очень большой, то я всегда "упрощаю" его, выкидывая из него различные элементы, следя за тем, чтобы ключевые проблемы в EXPLAIN оставались теми же — помогает увидеть самое основное и не отвлекаться на не имеющие значения детали, а так же сформулировать конкретный вопрос для google/stackoverflow
        0
        Спасибо за такой подробный комментарий. Если тема интересна сообществу, то буду рад развивать её дальше на примерах собственного опыта.
          +1

          Эх, ну как тут удержаться от сентенции "Хабр уже не торт"? Статью про "предателя" три дня обсуждают взахлеб, а в статью по тематике, для которой изначально создавался сайт, зашло полтора инвалида.


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

        0
        Оптимизация запроса это конечно хорошо, но она имеет смысл только тогда, когда есть оптимальная конструкция самой БД и таблиц.

        А вот как решить задачу оптимизации, например, если нужно делать запросы к FULLTEXT индексу в постоянно нарастающей по объему таблице (и отсечь данные нельзя ни под дате, ни как-либо еще — всегда нужно делать запрос ко всем текстовым данным в таблице, которые каждый день прирастают по сотне тысяч строк текста).

        Любой такой запрос подбрасывает расход CPU в 100% пока запрос не получит результат.
        Вот это задача номер раз!
          0

          Я не встречал на продакшене использования фуллтекст индексов. Полнотекстовый поиск всегда выносится в Сфинкс/Эластик.

            0
            Это увеличит нагрузку на CPU на 30% в среднем.
            А не встречали, почему?
            Вот хабр сейчас использует для поиска похожих статей метки, был-ли раньше полнотекстовый поиск для этого на хабре? (по идее был). Поиск в поле поиска на хабре — что использует? (сфинкс, или другой поиск, который как-то индексирует тексты и затем использует что? — правильно всё те же полнотекстовые индексы).
            И это пример с низкой скоростью добавления данных.

            А взять риа например — новости каждые 20-30 секунд в БД, и поиск похожих новостей перед добавлением, за год например — как ищутся?
              0

              Стоп. Тут какая-то куча-мала из вопросов.
              Во-первых, с переходом на отдельный поисковый сервис нагрузка на процессор сервера базы данных нас перестаёт интересовать от слова "совсем". Ну то есть понятно, что если у нас ВДС за три копейки на двух виртуальных ядрах, которые обслуживают и веб и БД и всех демонов впридачу — тогда нагрузка на поиск будет волновать.
              Но если у нас система с "приростом по сотне тысяч строк в день", то логично разнести её на разные физические серверы — веб на одном, БД на втором, поиск — на третьем. И пусть себе грузит свой процессор сколько влезет.


              Метки вообще никакого отношения к полнотекстовому поиску не имеют, это простой поиск по обычному индексу:


              SELECT article_id FROM articles_tags at, tags t 
              WHERE at.tag_id=t.id AND t.name IN (...)
              GROUP BY article_id 
              ORDER BY count(*) DESC

              чтобы отсортировать по релевантности


              Поиск "похожих новостей" и полнотекстовый поиск — это две большие разницы. Я думаю, для поиска "похожих" надо сначала научиться выделять ключевые слова в тексте. А дальше уже чисто техническая задача.

                0
                Нет, не разнесем — может сразу ДЦ купим ради десятка миллионов запсией?
                Всё на одном сервере, и внешний поисковый двиг только добавит нагрузку.

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

                «Поиск „похожих новостей“ и полнотекстовый поиск — это две большие разницы» нет, никакие метки вам не дадут гарантии похожести, полнотекст лидирует, либо человек должен проверять глазами выборку после меток.

                Полнотекст обязателен для вывода похожих новостей в релевантном поиске (как у гулга например).

                Но, сотрудник тех части google — Ubl, в недавней беседе упомянул структуру их индексов поиска (у них обычный индекс текста по словам, такой же в принципе, как создает любая БД), и сказал, что помимо самих индексов у них есть индексы — индексов, и указатели индексов — индексов.
                Вот это и интересно. Это решает, как я понимаю, проблему с нагрузкой при полнотекстовом поиске, но как это реализовано у них, пока представить не могу в полной мере.
                  +1

                  Как-то немного разнобой получается. По запросам у вас РИА и Гуголь, а по возможностям — один арендованный сервак. Ну в общем, я наверное ничего не смогу посоветовать для вашего случая.

                    –2
                    А вы считает, что раз гул и риа то должны кидать деньги налево и направо и брать горлом мощностью, а не оптимизированностью?
                    Нет, чем больше и круче компания, тем серьезнее и интереснее стараются подойти к оптимизации.

                    Мои вот движки держат легко 120-200 тыс посетителей в стуки даже на одно-двух процессорных вдс за доллар, при этом, обновление данных каждые 20-40 секунд.
                    И именно потому, что я очень жестко подхожу к борьбе за каждый байт и процессорную операцию при разработке, это уже как азарт просто, само-собой разумеющийся.

                    Сейчас вот как раз озадачаен решением выше-озвученной задачи для вдс на 2-3 CPU ядра средней линейки.
                      0
                      Ели жестко ограничиваться mysql, то решений не так много:
                      — выделение ключевых слов/тэгов, вынесение в отдельную таблицу и создание индексов по ним
                      — хранить тексты в файлах и юзать linux-grep (не знаю насколько быстрее будет)
                      Т.е. полнотекстовый поиск по большому объему текста в ограниченных условиях CPU врядли решаем.

                      Если придумаете интересное решение, будем ждать статья на хабре))
                        0
                        Да решений для тестов много, нужно просто правильно подобрать размеры и веса решений.

                        Вот сейчас откатываю разные размеры партиций и отдельных таблиц, а не партиций и вместо одного — 5-20 запросов, если процент выигрыша будет существенный, это будет очередной костыль, пока не будет найдено протестировано более быстрое и незатратное решение.

                        Пробую и файловые операции использовать на регэкспах, вместо полнотекста в БД, посмотрим, понюхаем.
                          0
                          Партиции дадут выигрыш, только если вы можете как-то ограничить выборку (т.е. вместо сканирований большой таблицы, сканируем под-таблицы поменьше), но у вас вроде условие всю таблицу сканировать.

                          Желаю успехов в решении
                            0
                            Не, в партициях ровно просто разделить и делать много запросов, по однму в каждую партицию, узнать разницу между кодом работы партиций и кодом работы с разными таблицами, чтобы увидеть где быстрее.
                            В любом случае спасибо за напутствие, буду пробовать.

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