Представьте, что ваш сайт или приложение начинает тормозить, пользователи жалуются на долгую загрузку, а сервер задыхается под нагрузкой. Часто корень проблемы — неоптимальные SQL-запросы, которые незаметно съедают ресурсы. К счастью, в арсенале MySQL есть мощный инструмент — EXPLAIN ANALYZE, который превращает поиск узких мест из гадания на кофейной гуще в точную науку. Давайте разберемся, как им пользоваться.
Что такое EXPLAIN и EXPLAIN ANALYZE?
Команда EXPLAIN существует давно и показывает предполагаемый план выполнения запроса — что, по мнению оптимизатора MySQL, произойдет. Но с версии 8.0.18 появился EXPLAIN ANALYZE — это революция. Он не только показывает план, но и реально выполняет запрос, возвращая фактические метрики: сколько времени заняло, сколько строк было обработано на каждом этапе, какие циклы были выполнены.
Ключевое отличие: EXPLAIN дает теоретический план, EXPLAIN ANALYZE — фактические результаты выполнения, что гораздо точнее для диагностики.
Как читать вывод EXPLAIN ANALYZE
Выполните запрос в формате: EXPLAIN ANALYZE SELECT * FROM users WHERE city = 'Moscow' ORDER BY created_at DESC;
Вы получите дерево операций. Разберем ключевые элементы:
1. Типы доступа (Access Type)
- ALL (Полное сканирование таблицы) — самый дорогой вариант. MySQL читает всю таблицу. Тревожный звоночек.
- index (Сканирование индекса) — читается весь индекс, иногда бывает необходимо.
- range (Диапазон по индексу) — хорошо, используется индекс для диапазона.
- ref/eq_ref — отличные варианты, эффективный поиск по ключу.
- const — идеально, строка найдена по первичному ключу или уникальному индексу.
2. Ключевые метрики в выводе
Формат: -> Filter: (city = 'Moscow') (cost=... rows=... actual time=0.1..120.5 rows=1500 loops=1)
- actual time — фактическое время в миллисекундах (формат: начальное..конечное).
- rows — реальное количество строк, обработанных на этом шаге.
- loops — сколько раз выполнялась эта операция.
- cost — оценочная стоимость операции (в условных единицах).
Сравнивайте estimated rows (план) и actual rows (реальность). Если оптимизатор сильно ошибся (оценка 100, реальность 10000), значит, статистика по таблице устарела. Выполните ANALYZE TABLE table_name;.
Практические шаги оптимизации
- Найдите самые медленные запросы через slow query log или performance_schema.
- Примените EXPLAIN ANALYZE к подозрительным запросам.
- Ищите «узкие места» — операции с самым большим actual time и операциями типа ALL.
- Добавьте недостающие индексы. Индекс должен покрывать условия WHERE, JOIN и ORDER BY. Но не переусердствуйте — каждый индекс замедляет INSERT/UPDATE.
- Перепишите запрос. Иногда проще изменить логику, чем добавить индекс. Разбейте сложный запрос на несколько простых, избегайте SELECT *, используйте LIMIT.
- Проверьте структуру таблиц. Подходящие типы данных (INT вместо VARCHAR для ID), отсутствие избыточных столбцов.
Распространенные проблемы и их решения
Проблема 1: Временные таблицы и файловая сортировка (Using temporary; Using filesort)
Если видите эти флаги в обычном EXPLAIN — запрос создает временную таблицу или сортирует на диске. Решение: добавьте индекс, покрывающий поля в ORDER BY и WHERE.
Проблема 2: Неиспользование индекса (type=ALL)
Самая частая проблема. Причины: нет подходящего индекса, функция в условии WHERE (WHERE YEAR(date) = 2023), неселективное условие.
Проблема 3: Вложенные циклы (Nested Loop) с большим количеством строк
Проверьте, можно ли добавить индекс для соединения или изменить порядок JOIN.
FAQ: Часто задаваемые вопросы
В чем разница между EXPLAIN и EXPLAIN ANALYZE?
EXPLAIN показывает предполагаемый план выполнения запроса, основанный на статистике. EXPLAIN ANALYZE реально выполняет запрос и показывает фактические затраты времени и обработанные строки, что делает анализ точнее.
Как часто нужно выполнять ANALYZE TABLE?
После значительных изменений в данных (более 10-20% строк). Для часто обновляемых таблиц можно настроить периодическое выполнение (раз в день/неделю).
Можно ли использовать EXPLAIN ANALYZE для запросов на UPDATE/DELETE?
Да, можно. Но помните, что EXPLAIN ANALYZE реально выполнит запрос, изменив данные! Всегда проверяйте запрос и используйте транзакции (START TRANSACTION; ... ROLLBACK;) для тестов в production.
Почему запрос с индексом все равно медленный?
Возможные причины: индекс не покрывающий (требуется обращение к таблице), высокий уровень фрагментации индекса, неверная оценка кардинальности оптимизатором, блокировки.
Что лучше: один сложный запрос или несколько простых?
Часто несколько простых запросов выполняются быстрее одного сложного, особенно если они используют эффективные индексы. Всегда проверяйте на реальных данных с помощью EXPLAIN ANALYZE.