MySQL EXPLAIN — функция используемая для анализа медленных запросов в MySQL и определения запросов, которые нужно оптимизировать.
Синтаксис команды очень прост — в консоли сервера баз данных вводится EXPLAIN, затем запрос к MySQL
Цикл по принципам работы с MySQL, более ранние материалы:
- Вводная статья цикла
- Начало работы с MySQL и основные команды
- MySQL SELECT, выборка из таблиц MySQL
- Регулярные выражения в MySQL
- MySQL SELECT JOIN таблиц
- MySQL select: ORDER BY и порядок вывода результатов
На практике EXPLAIN обычно передаются для анализа запросы, выполняющиеся непосредственно в настоящее время. Их можно посмотреть с помощью SHOW PROCESSLIST; или запросы из slow-query-log MySQL
Такой тактики стоит придерживаться если оптимизируется работа сервера баз данных при высоких нагрузках и грузит систему именно MySQL.
В MySQL EXPLAIN можно передать и любой другой запрос. Возьмем один из использованных в предыдущем материале цикла.
EXPLAIN SELECT name, price FROM PEOPLE WHERE PRICE = '20000';
+—-+————-+———+——+—————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———+——+—————+——+———+——+——+————-+
| 1 | SIMPLE | PEOPLE | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
+—-+————-+———+——+—————+——+———+——+——+————-+
1 row in set (0.00 sec)
Особый интерес при оптимизации представляют значения следующих полей:
possible_keys — возможные ключи, здесь выводятся созданные MySQL индексы, которые можно использовать
key — использованные при запросе индексы
rows — количество записей, которые MySQL пришлось проверить чтобы вернуть результат; при оптимизации следует стремиться к минимальному значению
Extra — словесное описание того, что используется в запросе
Другие поля:
id — идентификатор SELECT — важно в случае если в одном запросе их несколько
select_type — тип SELECT-а; для простых запросов это SIMPLE, для те в которых используются UNION операторы и вложенные запросы выводятся другие значения
table — таблица, которая задействуется
type — здесь для запросов, не использующих индексы, можно увидеть значение all, это самый плохой вариант говорящий о том, MySQL приходится обойти все значения чтобы вывести результат. const полученный в примере ниже означает, что была проведена одна строка и это позволило вывести результат — такой запрос будет обработан быстрее всего. Все типы приведены в документации
key_len — важно при составных индексах для того чтобы понять используется ли он весь или только какая-то часть
ref — в качестве значения выводятся те поля, которые были использованы оптимизатором запросов вместе с ключем
В примере индексов нет и серверу баз данных пришлось перебрать 7 значений чтобы вернуть результат.
Создадим простой индекс и проверим изменится ли вывод
CREATE INDEX PRICE on PEOPLE(PRICE);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
Индексы и принципы их создания будут рассмотрены позже.
Проверяем снова выполняя EXPLAIN на тот же SELECT
EXPLAIN SELECT name, price FROM PEOPLE WHERE PRICE = '20000';
+—-+————-+———+——+—————+——-+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———+——+—————+——-+———+——-+——+————-+
| 1 | SIMPLE | PEOPLE | ref | PRICE | PRICE | 23 | const | 1 | Using where |
+—-+————-+———+——+—————+——-+———+——-+——+————-+
1 row in set (0.00 sec)
Сейчас используется индекс PRICE и MySQL выдал результат проверив только одно поле.
EXPLAIN особенно эффективен при тонкой настройке и оптимизации сложных запросов к базе в больших приложениях. EXPLAIN выводит ту информацию, которая используется оптимизатором, которые является одним из основных механизмов MySQL.
Фактически запрос, который мы анализировали возвращает пустое значение.
SELECT name, price FROM PEOPLE WHERE PRICE = '20000';
Empty set (0.00 sec)
Но скорость обработки уменьшилась в 7 раз, при больших нагрузках это может иметь значение.
Индексы MySQL рассмотрены подробнее в следующем материале.