Для анализа медленных запросов в MySQL используется инструкция EXPLAIN:
EXPLAIN [запрос]
Пример анализа запроса, использующего индекс:
EXPLAIN SELECT * FROM users WHERE email = 'orkhanalyshov@gmail.com';
+----+-------------+-------+-------+---------------+-------+---------+-------+------+-------+ | 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 - Для всех комбинаций строк оптимизатор производит полный анализ таблицы. Самый медленный вариант.