Для анализа медленных запросов в MySQL используется инструкция EXPLAIN:

EXPLAIN [запрос]

Пример анализа запроса, использующего индекс:

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
+----+-------------+-------+-------+---------------+-------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+-------+------+-------+
|  1 | SIMPLE      | users | const | email         | email | 767     | const |    1 |       |
+----+-------------+-------+-------+---------------+-------+---------+-------+------+-------+

Внимание стоит обратить на колонку key - в ней должно быть название индекса, который использует MySQL для выборки. Колонка rows покажет количество обработанных строк (должно быть очень маленьким - десятки...сотни).

Пример EXPLAIN для медленного запроса:

EXPLAIN SELECT * FROM users WHERE email LIKE '%alyshov%';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 2154 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

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


Вывод команды:

table - Имя таблицы, с которой будут производиться действия.

type - Тип связи в таблице.

possible_keys - Здесь указываются индексы, возможные для отыскания подходящих строк в таблице. Столбец не зависит от порядка таблиц, т. е. в реальной ситуации не все указанные ключи подойдут для сгенерированного оптимизатором перечня таблиц. В столбце данные могут отсутствовать, т. е. оптимизатор не смог подобрать индекс. Тогда для ускорения работы запроса надо посмотреть на условие WHERE и попытаться отыскать в нем ссылки на столбец, который можно добавить в индекс.

key - Здесь содержится имя индекса, который оптимизатор MySQL будет применять во время выполнения запроса.

key_len - Длина ключа, примененного оптимизатором. По полученному значению можно увидеть, сколько частей выбранного составного ключа будет применено.

ref - Показывает столбцы или константы, которые будут использованы с ключом key при выборке.

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

Extra - Здесь указывается дополнительная информация о выполнении запроса.

Distinct - Поиск строк заканчивается после обнаружения первого совпадения строки. MySQL не будет продолжать поиск строк для текущей комбинации.

Not exists - MySQL произвел анализ LEFT JOIN, нашел присоединяемую строку и не будет искать в этой таблице другие совпадающие строки.

range checked for each record (index map: #) - MySQL не удалось отыскать нужного и подходящего запросу индекса для использования. Тогда каждому набору строк в предшествующих таблицах он будет искать подходящий индекс и использовать его. Это медленная операция, но она быстрее, чем поиск без индекса.

Using filesort - MySQL делает дополнительный поиск, чтобы выяснить метод извлечения строк для сортировки.

Using index - Для получения данных используется только информация из индекса.

Using temporary - Для выполнения выборки MySQL должен создать временную таблицу. Классическая ситуация для ORDER BY отличного от GROUP BY.

Where used - WHERE применяется в процессе выявления строк, сопоставляемых со следующей таблицей, или данных, посылаемых в ответе.

В процессе оптимизации скорости работы ваших SQL-запросов смотрите на наличие Using filesort и Using temporary и старайтесь убирать их.


Нижеуказанные типы связывания таблиц мы рассмотрим в порядке убывания скорости работы:

system - Указанная таблица состоит только из 1 строки.

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

eq_ref - Для всех наборов строк MySQL будет сопоставлять 1 строку из текущей таблицы. Это самый лучший тип связи строк в реальных многострочных таблицах.

ref - Читаются абсолютно все строки с соответствующими им индексами для всех имеющихся комбинаций строк. Работает хорошо, когда ключ сопоставлен не всей таблице, а только нескольким её строкам.

range - Обрабатываются строки, находящиеся в указанном диапазоне. Выбранный оптимизатором индекс выводится в значении key.

index - Тот же ALL, но идет поиск только по дереву индексов. Он быстрее ALL, так как индекс почти всегда меньше реальных данных.

ALL - Для всех комбинаций строк оптимизатор производит полный анализ таблицы. Самый медленный вариант.