Когда вы отправляете запрос, MySQL Query Optimizer пытается разработать оптимальный план для выполнения запроса. Вы можете увидеть информацию о плане, добавив префикс запроса к EXPLAIN . EXPLAIN — один из самых мощных инструментов в вашем распоряжении для понимания и оптимизации проблемных запросов MySQL, но это печальный факт, что многие разработчики редко используют его. В этой статье вы узнаете, каким может быть вывод EXPLAIN и как его использовать для оптимизации вашей схемы и запросов.
Понимание вывода EXPLAIN
Использование EXPLAIN так же просто, как предварительное ожидание перед запросами SELECT . Давайте проанализируем вывод простого запроса, чтобы ознакомиться со столбцами, возвращаемыми командой.
EXPLAIN SELECT * FROM categoriesG
********************** 1. ряд ********************** id: 1 select_type: SIMPLE таблица: категории Тип: ВСЕ возможные_ключи: NULL ключ: NULL key_len: NULL ref: NULL ряды: 4 Дополнительно: 1 ряд в наборе (0,00 сек)
Может показаться, что это не так, но в эти 10 столбцов упаковано много информации! Столбцы, возвращаемые запросом:
-
id— последовательный идентификатор для каждогоSELECTв запросе (для случаев, когда у вас есть вложенные подзапросы) -
select_type— тип запросаSELECT. Возможные значения:-
SIMPLE— запрос представляет собой простой запросSELECTбез каких-либо подзапросов илиUNION - ПЕРВИЧНЫЙ —
SELECTнаходится в самом внешнем запросе вJOIN - DERIVED —
SELECTявляется частью подзапроса в предложенииFROM - SUBQUERY — первый
SELECTв подзапросе - DEPENDENT SUBQUERY — подзапрос, который зависит от внешнего запроса
- UNCACHEABLE SUBQUERY — подзапрос, который не кэшируется (существуют определенные условия для кэширования запроса)
- СОЮЗ —
SELECTявляется вторым или последующим утверждением СОЮЗА - ЗАВИСИМЫЙ СОЮЗ — второй или более поздний
SELECTUNIONзависит от внешнего запроса - РЕЗУЛЬТАТ СОЮЗА —
SELECT— результатUNION
-
-
table— таблица, на которую ссылается строка -
type— как MySQL объединяет используемые таблицы. Это одно из наиболее проницательных полей в выходных данных, поскольку оно может указывать на отсутствующие индексы или то, как пишется запрос, следует пересмотреть. Возможные значения:- система — таблица имеет только ноль или одну строку
- const — таблица имеет только одну соответствующую строку, которая проиндексирована. Это самый быстрый тип объединения, потому что таблицу нужно читать только один раз, а значение столбца можно рассматривать как константу при объединении с другими таблицами.
- eq_ref — все части индекса используются соединением, и индекс равен
PRIMARY KEYилиUNIQUE NOT NULL. Это следующий наилучший тип соединения. - ref — все совпадающие строки индексированного столбца считываются для каждой комбинации строк из предыдущей таблицы. Этот тип объединения отображается для индексированных столбцов по сравнению с использованием операторов
=или<=>. - fulltext — объединение использует индекс таблицы
FULLTEXT. - ref_or_null — это то же самое, что и ref, но также содержит строки с нулевым значением для столбца.
- index_merge — объединение использует список индексов для получения результирующего набора. Ключевой столбец вывода
EXPLAINбудет содержать используемые ключи. - unique_subquery — подзапрос
INвозвращает только один результат из таблицы и использует первичный ключ. - index_subquery — то же самое, что и unique_subquery, но возвращает более одной строки результатов.
- диапазон — индекс используется для поиска совпадающих строк в определенном диапазоне, обычно, когда ключевой столбец сравнивается с константой с использованием таких операторов, как
BETWEEN,IN,>,>=и т. д. - index — все дерево индексов сканируется, чтобы найти соответствующие строки.
- all — сканируется вся таблица, чтобы найти подходящие строки для объединения. Это худший тип соединения и обычно указывает на отсутствие соответствующих индексов в таблице.
-
possible_keys— показывает ключи, которые могут использоваться MySQL для поиска строк в таблице, хотя они могут или не могут использоваться на практике. Фактически, этот столбец часто может помочь в оптимизации запросов, поскольку, если столбец имеет значение NULL, это означает, что соответствующие индексы не могут быть найдены. -
key— указывает фактический индекс, используемый MySQL. Этот столбец может содержать индекс, который не указан в столбцеpossible_key. Оптимизатор MySQL всегда ищет оптимальный ключ, который можно использовать для запроса. Присоединяясь ко многим таблицам, он может определить некоторые другие ключи, которые отсутствуют в спискеpossible_keyно являются более оптимальными. -
key_len— указывает длину индекса, который оптимизатор запросов выбрал для использования. Например, значениеkey_len4 означает, что для хранения четырех символов требуется память. Ознакомьтесь с требованиями к хранилищу типов данных MySQL, чтобы узнать больше об этом. -
ref— Показывает столбцы или константы, которые сравниваются с индексом, указанным в ключевом столбце. MySQL выберет постоянное значение для сравнения или сам столбец на основе плана выполнения запроса. Вы можете увидеть это в приведенном ниже примере. -
rows— перечисляет количество записей, которые были проверены, чтобы произвести вывод. Это еще один важный столбец, на котором стоит сосредоточиться на оптимизации запросов, особенно для запросов, использующихJOINи подзапросы. -
Extra— содержит дополнительную информацию о плане выполнения запроса. Такие значения, как «Использование временного», «Использование сортировки файлов» и т. Д. В этом столбце, могут указывать на проблемный запрос. Полный список возможных значений и их значения можно найти в документации по MySQL .
Вы также можете добавить ключевое слово EXTENDED после EXPLAIN в свой запрос, и MySQL покажет вам дополнительную информацию о том, как он выполняет запрос. Чтобы увидеть информацию, следуйте вашему запросу EXPLAIN с SHOW WARNINGS . Это в основном полезно для просмотра запроса, который выполняется после любых преобразований, выполненных оптимизатором запросов.
EXPLAIN EXTENDED SELECT City.Name FROM City JOIN Country ON (City.CountryCode = Country.Code) WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia'G
********************** 1. ряд ********************** id: 1 select_type: SIMPLE стол: страна тип: конст возможные_ключи: ПЕРВИЧНЫЕ ключ: первичный key_len: 3 ref: const ряды: 1 отфильтрованный: 100,00 Дополнительно: ********************** 2. строка ********************** id: 1 select_type: SIMPLE стол: город Тип: ВСЕ возможные_ключи: NULL ключ: NULL key_len: NULL ref: NULL ряды: 4079 отфильтрованный: 100,00 Дополнительно: Использование где 2 ряда в комплекте, 1 предупреждение (0,00 сек)
SHOW WARNINGSG
********************** 1. ряд ********************** Уровень: Примечание Код: 1003 Сообщение: выберите `World` .City`. Name` AS `Name` из` World` .City` присоединиться к `World` .Country` where ((` `World` .City` IND ')) 1 ряд в наборе (0,00 сек)
Устранение неполадок производительности с EXPLAIN
Теперь давайте посмотрим, как мы можем оптимизировать плохо работающий запрос, анализируя вывод EXPLAIN . При работе с реальными приложениями, несомненно, будет множество таблиц с множеством взаимосвязей между ними, но иногда трудно предсказать наиболее оптимальный способ написания запроса.
Здесь я создал образец базы данных для приложения электронной коммерции, в котором нет индексов или первичных ключей, и продемонстрирую влияние такого плохого дизайна, написав довольно ужасный запрос. Вы можете скачать образец схемы с GitHub.
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 = 10101G
********************** 1. ряд ********************** id: 1 select_type: SIMPLE стол: л Тип: ВСЕ возможные_ключи: NULL ключ: NULL key_len: NULL ref: NULL ряды: 7 Дополнительно: ********************** 2. строка ********************** id: 1 select_type: SIMPLE стол: р Тип: ВСЕ возможные_ключи: NULL ключ: NULL key_len: NULL ref: NULL ряды: 110 Дополнительно: Использование где; Использование буфера соединения ********************** 3. ряд ********************** id: 1 select_type: SIMPLE стол: с Тип: ВСЕ возможные_ключи: NULL ключ: NULL key_len: NULL ref: NULL ряды: 122 Дополнительно: использование буфера соединения ********************** 4. ряд ********************** id: 1 select_type: SIMPLE стол: о Тип: ВСЕ возможные_ключи: NULL ключ: NULL key_len: NULL ref: NULL ряды: 326 Дополнительно: Использование где; Использование буфера соединения ********************** 5. ряд ********************** id: 1 select_type: SIMPLE стол: д Тип: ВСЕ возможные_ключи: NULL ключ: NULL key_len: NULL ref: NULL ряды: 2996 Дополнительно: Использование где; Использование буфера соединения 5 рядов в наборе (0,00 сек)
Если вы посмотрите на приведенный выше результат, вы увидите все признаки плохого запроса. Но даже если бы я написал лучший запрос, результаты остались бы такими же, поскольку индексов нет. Тип объединения отображается как «ВСЕ» (что является наихудшим), что означает, что MySQL не смог определить какие-либо ключи, которые могут быть использованы в соединении, и, следовательно, possible_keys и столбцы key имеют нулевое значение. Что наиболее важно, столбец rows показывает, что MySQL сканирует все записи каждой таблицы для запроса. Это означает, что для выполнения запроса он будет сканировать 7 × 110 × 122 × 326 × 2996 = 91 750 822 240 записей, чтобы найти четыре соответствующих результата. Это действительно ужасно, и оно будет расти только в геометрической прогрессии по мере роста базы данных.
Теперь давайте добавим некоторые очевидные индексы, такие как первичные ключи для каждой таблицы, и снова выполним запрос. Как общее практическое правило, вы можете рассматривать столбцы, используемые в предложениях JOIN запроса, в качестве хороших кандидатов на ключи, поскольку MySQL всегда сканирует эти столбцы, чтобы найти совпадающие записи.
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);
Давайте снова запустим тот же запрос после добавления индексов, и результат должен выглядеть следующим образом:
********************** 1. ряд ********************** id: 1 select_type: SIMPLE стол: о тип: конст возможные_кнопки: ПЕРВИЧНЫЙ, customerNumber ключ: первичный key_len: 4 ref: const ряды: 1 Дополнительно: ********************** 2. строка ********************** id: 1 select_type: SIMPLE стол: с тип: конст возможные_ключи: ПЕРВИЧНЫЕ ключ: первичный key_len: 4 ref: const ряды: 1 Дополнительно: ********************** 3. ряд ********************** id: 1 select_type: SIMPLE стол: д тип: ссылка возможные_ключи: ПЕРВИЧНЫЕ ключ: первичный key_len: 4 ref: const ряды: 4 Дополнительно: ********************** 4. ряд ********************** id: 1 select_type: SIMPLE стол: р тип: eq_ref Возможные ключи: PRIMARY, productLine ключ: первичный key_len: 17 ref: classicmodels.d.productCode ряды: 1 Дополнительно: ********************** 5. ряд ********************** id: 1 select_type: SIMPLE стол: л тип: eq_ref возможные_ключи: ПЕРВИЧНЫЕ ключ: первичный key_len: 52 ref: classicmodels.p.productLine ряды: 1 Дополнительно: 5 рядов в наборе (0,00 сек)
После добавления индексов количество отсканированных записей было уменьшено до 1 × 1 × 4 × 1 × 1 = 4. Это означает, что для каждой записи с orderNumber 10101 в таблице orderdetails MySQL смог напрямую найти соответствующую запись во всех другие таблицы используют индексы и не должны прибегать к сканированию всей таблицы.
В выводе первой строки вы можете увидеть используемый тип соединения — «const», который является самым быстрым типом соединения для таблицы с более чем одной записью. MySQL был в состоянии использовать PRIMARY ключ в качестве индекса. В столбце ref отображается «const», которое является ничем иным, как значением 10101, используемым в WHERE запроса.
Давайте посмотрим на еще один пример запроса. Здесь мы в основном примем объединение двух таблиц, products и вариантов productvariants , каждый из которых объединен с productvariants productline . Таблица productvariants состоит из различных вариантов продукта с productCode и их ценами.
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 50G
********************** 1. ряд ********************** id: 1 select_type: PRIMARY таблица: <производная2> Тип: ВСЕ возможные_ключи: NULL ключ: NULL key_len: NULL ref: NULL ряды: 219 Дополнительно: Использование где ********************** 2. строка ********************** id: 2 select_type: DERIVED стол: р Тип: ВСЕ возможные_ключи: NULL ключ: NULL key_len: NULL ref: NULL ряды: 110 Дополнительно: ********************** 3. ряд ********************** id: 2 select_type: DERIVED стол: л тип: eq_ref возможные_ключи: ПЕРВИЧНЫЕ ключ: первичный key_len: 52 ref: classicmodels.p.productLine ряды: 1 Дополнительно: ********************** 4. ряд ********************** id: 3 select_type: UNION таблица: v Тип: ВСЕ возможные_ключи: NULL ключ: NULL key_len: NULL ref: NULL ряды: 109 Дополнительно: ********************** 5. ряд ********************** id: 3 select_type: UNION стол: р тип: eq_ref возможные_ключи: ПЕРВИЧНЫЕ ключ: первичный key_len: 17 ref: classicmodels.v.productCode ряды: 1 Дополнительно: ********************** 6. ряд ********************** id: 3 select_type: UNION стол: л тип: eq_ref возможные_ключи: ПЕРВИЧНЫЕ ключ: первичный key_len: 52 ref: classicmodels.p.productLine ряды: 1 Дополнительно: ********************** 7. строка ********************** id: NULL select_type: UNION RESULT таблица: <union2,3> Тип: ВСЕ возможные_ключи: NULL ключ: NULL key_len: NULL ref: NULL строки: NULL Дополнительно: 7 рядов в наборе (0,01 с)
Вы можете увидеть ряд проблем в этом запросе. Он сканирует все записи в таблицах products и productvariants . Так как в этих таблицах нет индексов для productLine и buyPrice , выходные данные possible_keys и key столбцы показывают нулевое значение. Состояние products и линий products проверяется после UNION , поэтому перемещение их внутри UNION уменьшит количество записей. Давайте добавим несколько дополнительных индексов и перепишем запрос.
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);
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 ) productG
********************** 1. ряд ********************** id: 1 select_type: PRIMARY таблица: <производная2> Тип: ВСЕ возможные_ключи: NULL ключ: NULL key_len: NULL ref: NULL ряды: 12 Дополнительно: ********************** 2. строка ********************** id: 2 select_type: DERIVED стол: р тип: диапазон возможные_ключи: idx_buyPrice, idx_productLine ключ: idx_buyPrice key_len: 8 ref: NULL ряды: 23 Дополнительно: Использование где ********************** 3. ряд ********************** id: 2 select_type: DERIVED стол: л тип: eq_ref возможные_ключи: ПЕРВИЧНЫЕ ключ: первичный key_len: 52 ref: classicmodels.p.productLine ряды: 1 Дополнительно: Использование где ********************** 4. ряд ********************** id: 3 select_type: UNION таблица: v тип: диапазон возможные_ключи: idx_buyPrice, idx_productCode ключ: idx_buyPrice key_len: 9 ref: NULL ряды: 1 Дополнительно: Использование где ********************** 5. ряд ********************** id: 3 select_type: UNION стол: р тип: eq_ref возможные_ключи: ПЕРВИЧНЫЕ, idx_productLine ключ: первичный key_len: 17 ref: classicmodels.v.productCode ряды: 1 Дополнительно: Использование где ********************** 6. ряд ********************** id: 3 select_type: UNION стол: л тип: eq_ref возможные_ключи: ПЕРВИЧНЫЕ ключ: первичный key_len: 52 ref: classicmodels.p.productLine ряды: 1 Дополнительно: Использование где ********************** 7. строка ********************** id: NULL select_type: UNION RESULT таблица: <union2,3> Тип: ВСЕ возможные_ключи: NULL ключ: NULL key_len: NULL ref: NULL строки: NULL Дополнительно: 7 рядов в наборе (0,01 с)
Как вы можете видеть в результате, теперь число отсканированных приблизительных строк значительно уменьшено с 2 625 810 (219 × 110 × 109) до 276 (12 × 23), что является огромным приростом производительности. Если вы попытаетесь выполнить тот же запрос без предыдущих перестановок, просто после добавления индексов, вы не увидите значительного сокращения. MySQL не может использовать индексы, поскольку в производном результате есть WHERE . Переместив эти условия в UNION , он может использовать индексы. Это означает, что простого добавления индекса не всегда достаточно; MySQL не сможет использовать его, если вы не напишите свои запросы соответствующим образом.
Резюме
В этой статье я обсудил ключевое слово EXPLAIN MySQL, что означает его вывод и как вы можете использовать его вывод для построения более совершенных запросов. В реальном мире это может быть более полезным, чем сценарии, продемонстрированные здесь. Чаще всего вы будете объединять несколько таблиц и использовать сложные WHERE . Просто добавленные индексы для нескольких столбцов могут не всегда помочь, и тогда пришло время более внимательно посмотреть на ваши запросы.
Изображение через Efman / Shutterstock