Используем EXPLAIN для оптимизации запросов к MySql

Что включает в себя вывод EXPLAIN.

Чтобы использовать EXPLAIN, нужно добавить его перед вашим запросом:

SQL

   EXPLAIN SELECT * FROM categories;

Хоть в это и трудно поверить, но в 10 строчках, которые возвращает запрос, хранится много полезной информации. Что же выводит EXPLAIN?

• id - порядковый идентификатор каждого SELECT, находящегося внутри запроса (в случае использования вложенных подзапросов)
• select_type– тип SELECT запроса. Возможные значения:
  • SIMPLE – запрос содержит простую выборку без подзапросов и UNION'ов
  • PRIMARY – запрос является внешним запросов в JOIN
  • DERIVED – запрос SELECT является частью подзапроса внутри выражения FROM
  • SUBQUERY – первый SELECT в подзапросе
  • DEPENDENT SUBQUERY - первый SELECT, зависящий от внешнего подзапроса
  • UNCACHEABLE SUBQUERY – некешируемый подзапрос
  • UNION – SELECT является вторым или последующим в UNION
  • DEPENDENT UNION – SELECT является вторым или последующим запросом в UNIONи зависит от внешних запросов/li>
  • UNION RESULT – SELECT является результатом UNION'а
• table – таблица, которой относится текущая строка
• type – тип связывания таблиц. Это один из самых важных столбцов в результате, потому что по нему можно вычислить потерянные индексы или понять, как можно улучшить запрос.
  Возможные значения:
  • system – таблица содержит только одну строку (системная таблица);
  • const - таблица содержит не более одной соответствующей строки, которая будет считываться в начале запроса. Поскольку имеется только одна строка, оптимизатор в дальнейшем может расценивать значения этой строки в столбце как константы. Таблицы const являются очень быстрыми, поскольку они читаются только однажды;
  • eq_ref - для каждой комбинации строк из предыдущих таблиц будет cчитываться одна строка из этой таблицы. Это наилучший возможный тип связывания среди типов, отличных от const. Данный тип применяется, когда все части индекса используются для связывания, а сам индекс - UNIQUE или PRIMARY KEY;
  • ref - из этой таблицы будут считываться все строки с совпадающими значениями индексов для каждой комбинации строк из предыдущих таблиц. Тип ref применяется, если для связывания используется только крайний левый префикс ключа, или если ключ не является UNIQUE или PRIMARY KEY (другими словами, если на основании значения ключа для связывания не может быть выбрана одна строка). Этот тип связывания хорошо работает, если используемый ключ соответствует только нескольким строкам;
  • fulltext – объединение, использующее полнотекстовый (FULLTEXT) индекс таблиц;
  • ref_or_null – то же самое, что и ref, только содержащее строки со значением NULL в полях;
  • index_merge – объединение, использующее список индексов для получения результата запроса;
  • unique_subquery – результат подзапроса в выражении IN возвращает одну строку, используемую в качестве первичного ключа;
  • index_subquery – то же самое, что и unique_subquery, только в результате больше одной строки;
  • range – в запросе происходит сравнение ключевого поля с диапазоном значений (используются операторы BETWEEN,IN, >, >=);
  • index – в процессе выполнения запроса сканируется только дерево индексов;
  • all – в процессе выполнения запроса сканируются все таблицы. Это наихудший тип объединения и обычно указывает на отсутствие надлежащих индексов в таблице;
• possible_keys – показаны возможные индексы, которые могут использоваться MySQL для поиска данных в таблице. На самом деле, значение этого столбца, очень часто помогает оптимизировать запросы. Если значение равно NULL, значит, никаких индексов не используется.
• key – отображается текущий ключ, используемый MySQL в данный момент. В этом столбце может отображаться индекс, отсутствующий в possible_keys. Оптимизатор запросов MySQL всегда пытается найти оптимальный ключ, который будет использоваться в запросе. При объединении нескольких таблиц, MySQL может использовать индексы, также не указанные в possible_keys.
• key_len – содержит длину ключа, выбранного оптимизатором запросов MySQL. Если значение key равно NULL, то key_len тоже NULL. По значению длины ключа можно определить, сколько частей составного ключа в действительности будет использовать MySQL. Подробнее об этом можно почитать в руководстве по MySQL.
• ref – показаны поля или константы, которые используются совместно с ключом, указанным в столбце key.
• rows – количество строк, которые анализируются MySQL в процессе запроса. Это еще один важный показатель, указывающий на необходимость оптимизации запросов, особенно тех, которые содержат JOIN и подзапросы.
• extra – содержит дополнительную информацию о процессе выполнения запроса. Если значениями этого столбца являются ”Using temporary”, “Using filesort” и т.п, то это говорит о том, что это «проблемный» запрос, требующий оптимизации. С полным список значений этого столбца можно ознакомиться в руководстве по MySQL.

Вы можете добавить ключевое слово EXTENDED после EXPLAIN, чтобы увидеть дополнительную информацию о выполнении запроса. После запроса EXPLAIN бывает полезно выполнить запрос SHOW WARNING, показывающий предупреждения и сообщения, касающиеся последнего запроса, а именно перобразований, сделанных оптимизатором запросов MySQL.

SQL

   EXPLAIN EXTENDED SELECT City.Name FROM City JOIN Country ON (City.CountryCode = Country.Code) WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia';

SQL

   SHOW WARNINGS;

Оптимизация производительности с помощью EXPLAIN.

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

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

SQL

   EXPLAIN SELECT * FROM orderdetails d INNER JOIN orders o ON d.orderNumber = o.orderNumber INNER JOIN products p ON p.productCode = d.productCode 
   INNER JOIN productlines l ON p.productLine = l.productLine 
   INNER JOIN customers c on c.customerNumber = o.customerNumber 
   WHERE o.orderNumber = 10101

Если вы взглянете на рисунок выше, то увидите все признаки «плохого» запроса. Но даже если я поправлю запрос, результаты не сильно изменятся, потому что в таблицах отсутствуют индексы. Тип объединения равен ”ALL” (напоминаю, что это наихудший вариант). Это значит, что MySQL не может найти ни одного ключа, который может участвовать в объединении, поэтому значение столбцов possible_keys и key равно NULL. Хуже всего то, что в процессе запроса MySQL будет сканировать все записи во всех таблицах, об этом говорит значение столбцов rows. При выполнении запроса будут просмотрены 91.750.822.240 записей (7 × 110 × 122 × 326 × 2996), чтобы получить результат из 4 записей. Это действительно ужасно, и будет только хуже, когда количество записей в базе данных будет увеличиваться.

А сейчас давайте добавим первичные ключи у всех таблиц и выполним запрос еще раз. Как правило, при создании индексов, обращают внимание на поля, по которым происходит объединение (JOIN), - это отличные кандидаты, для присвоения индексов, потому что MySQL всегда «просматривает» их при поиске связанных записей.

SQL

   ALTER TABLE customers ADD PRIMARY KEY (customerNumber);
   ALTER TABLE employees ADD PRIMARY KEY (employeeNumber);
   ALTER TABLE offices ADD PRIMARY KEY (officeCode);
   ALTER TABLE orderdetails ADD PRIMARY KEY (orderNumber, productCode);
   ALTER TABLE orders ADD PRIMARY KEY (orderNumber), ADD KEY (customerNumber);
   ALTER TABLE payments ADD PRIMARY KEY (customerNumber, checkNumber);
   ALTER TABLE productlines ADD PRIMARY KEY (productLine);
   ALTER TABLE products ADD PRIMARY KEY (productCode), ADD KEY (buyPrice), ADD KEY (productLine);
   ALTER TABLE productvariants ADD PRIMARY KEY (variantId), ADD KEY (buyPrice), ADD KEY (productCode);

Теперь давайте выполним наш сложный запрос еще раз после добавления индексов. Результат будет следующий:

После добавления индексов, количество сканируемых записей снизилось до 4 (1 × 1 × 4 × 1 × 1). Это говорит о том, что для каждой записи с ключом orderNumber из таблицы orderdetails MySQL сможет найти связанные записи во всех таблицах, используя индексы, а не сканируя все таблицы полностью.

В первой строке результата, который выводит EXPLAIN, вы можете видеть, что тип объединения равен ”const”, - это самым быстрый тип объединения таблиц, содержащих более одной записи. В данном случае MySQL будет использовать первичный ключ в качестве индекса.

Давайте рассмотрим еще один запрос. Объединим 2 запроса SELECT к таблицам products и productvariants с помощью UNION, при этом в каждом из запросов будет участвовать таблица productline. В таблице productvariants хранятся разновидности товара. В ней содержатся поля productCode (ссылка на записи в таблице products) и поле с ценой buyPrice.

SQL

   EXPLAIN SELECT * FROM (
   SELECT p.productName, p.productCode, p.buyPrice, l.productLine, p.status, l.status AS lineStatus FROM
   products p
   INNER JOIN productlines l ON p.productLine = l.productLine
   UNION
   SELECT v.variantName AS productName, v.productCode, p.buyPrice, l.productLine, p.status, l.status AS lineStatus FROM productvariants v
   INNER JOIN products p ON p.productCode = v.productCode
   INNER JOIN productlines l ON p.productLine = l.productLine
   ) products
   WHERE status = 'Active' AND lineStatus = 'Active' AND buyPrice BETWEEN 30 AND 50;

Вы можете увидеть некоторые проблемы в этом запросе. Сканируются все записи из таблиц products и productvariants. Так как в этих таблицах нет индексов по полям productLine и buyPrice, значения possible_keys и key, которые выводит EXPLAIN, имеют значения NULL.

Статус таблиц products и productlines проверяется после UNION, если перенести их внутрь UNION, это уменьшит количество обрабатываемых записей. Давайте добавим еще несколько дополнительных индексов и повторим запрос.

SQL

  CREATE INDEX idx_buyPrice ON products(buyPrice);
  CREATE INDEX idx_buyPrice ON productvariants(buyPrice);
  CREATE INDEX idx_productCode ON productvariants(productCode);
  CREATE INDEX idx_productLine ON products(productLine);

SQL

  EXPLAIN SELECT * FROM (
  SELECT p.productName, p.productCode, p.buyPrice, l.productLine, p.status, l.status as lineStatus FROM products p
  INNER JOIN productlines AS l ON (p.productLine = l.productLine AND p.status = 'Active' AND l.status = 'Active') 
  WHERE buyPrice BETWEEN 30 AND 50
  UNION
  SELECT v.variantName AS productName, v.productCode, p.buyPrice, l.productLine, p.status, l.status FROM productvariants v
  INNER JOIN products p ON (p.productCode = v.productCode AND p.status = 'Active') 
  INNER JOIN productlines l ON (p.productLine = l.productLine AND l.status = 'Active')
  WHERE
  v.buyPrice BETWEEN 30 AND 50
  ) product;

Как вы видите, сейчас количество обрабатываемых строк значительно снизилось с 2.625.810 (219 × 110 × 109) до 276 (12 × 23), что дает огромный прирост производительности. MySQL не будет использовать индексы в этом запросе, из-за условий в WHERE. После переноса этих условий внутрь UNION, использование индексов стало возможным. Все это говорит о том, что не всегда достаточно создавать индексы, MySQL не сможет использовать их в определенных запросах.

Заключение

В этой статье я познакомил вас с ключевым словом EXPLAIN, рассказал о том, что означают данные, которые выводятся в результате его работы и как вы можете их использовать для оптимизации запросов. Использовать EXPLAIN в реальном приложении это будет более полезно, чем на демонстрационной базе данных из этой статьи. Почти всегда вы будете объединять несколько таблиц вместе, и использовать WHERE. Простое добавление индексов нескольким полям обычно не приносит выгоды, поэтому в процессе составления запросов особое внимание надо уделять самим запросам.

Перевод – Земсков Матвей

Оригинал статьи: http://phpmaster.com/using-explain-to-write-better-mysql-queries/