EXPLAIN ANALYZE в MySQL: Как читать план запроса и ускорять базу данных в 10 раз

Представьте, что ваш сайт начал тормозить, а база данных отвечает по 5 секунд на простые запросы. Вместо хаотичных изменений индексов или переписывания кода, умные разработчики используют один мощный инструмент — EXPLAIN ANALYZE. Это не просто диагностика, а полная кардиограмма запроса, показывающая каждую его \"операцию\", стоимость и реальное время выполнения. В этой статье мы разберем, как превратить медленный запрос в молниеносный, понимая каждую строчку плана выполнения.

Что такое EXPLAIN и EXPLAIN ANALYZE?

В мире MySQL EXPLAIN существует давно — он показывает предполагаемый план выполнения запроса, который оптимизатор базы данных собирается использовать. Это как маршрут, построенный навигатором до начала поездки. Но с версии MySQL 8.0.18 появился EXPLAIN ANALYZE — это уже не план, а отчет о реальной поездке. Он фактически выполняет запрос (поэтому используйте его осторожно на продакшене!) и возвращает детальную статистику: сколько строк было обработано на каждом этапе, сколько времени это заняло, какие индексы использовались, а какие были проигнорированы.

Ключевое отличие: EXPLAIN показывает, что оптимизатор планирует сделать. EXPLAIN ANALYZE показывает, что он сделал на самом деле, с точными замерами времени. Всегда начинайте анализ с EXPLAIN ANALYZE для реальной картины.

Как читать вывод EXPLAIN ANALYZE: Разбираем дерево выполнения

Вывод выглядит как древовидная структура. Каждая строка — это операция. Давайте разберем ключевые столбцы:

->: Обозначает вложенность операций. Чем глубже, тем позже выполняется.
cost: Оценочная стоимость операции (в условных единицах). Суммируется снизу вверх.
rows: Сколько строк обработала эта операция.
actual time: Самое важное! Формат \"от.. до..\" в миллисекундах. Показывает время инициализации операции и общее время для всех циклов.

Пример плохого плана и как его исправить

Допустим, у нас запрос к таблице `users` и `orders`:

EXPLAIN ANALYZE
SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.city = 'Москва' AND o.total > 10000;

В выводе вы видите:

-> Nested loop inner join  (cost=... rows=...) (actual time=15.000..1500.000 rows=1000 loops=1)
    -> Filter: (o.total > 10000)  (cost=... rows=...) (actual time=10.000..1400.000 rows=50000)
        -> Table scan on o  (cost=... rows=...) (actual time=0.100..800.000 rows=100000)
    -> Filter: (u.city = 'Москва')  (cost=... rows=...) (actual time=0.200..0.300 rows=0.02)
        -> Single-row index lookup on u using PRIMARY (id=o.user_id)  (actual time=0.100..0.150 rows=1)

Тревожный звоночек: Операция Table scan on o с actual time=800ms! Это полное сканирование таблицы `orders` всех 100k строк. Оптимизатор не смог использовать индекс для фильтра `o.total > 10000`.

Решение: Добавляем индекс на поле `total`:

CREATE INDEX idx_total ON orders(total);

После этого план изменится: вместо `Table scan` появится `Index range scan` с гораздо меньшим `actual time`.

Типичные проблемы и их решения

Full Table Scan (Полное сканирование таблицы): Самая частая причина тормозов. Лечится созданием индекса на поля в условиях WHERE, JOIN или ORDER BY.
Using filesort: Сортировка происходит во временном файле, а не с помощью индекса. Создайте составной индекс, покрывающий поля сортировки.
Using temporary: Создается временная таблица для обработки GROUP BY или DISTINCT. Проверьте, можно ли добавить индекс на поля группировки.
Неверный порядок JOIN: MySQL начинает соединение с самой большой таблицы. Используйте подсказки `STRAIGHT_JOIN` или меняйте порядок таблиц в запросе, если план неоптимален.

Практический чек-лист оптимизации

Всегда запускайте EXPLAIN ANALYZE на тестовой копии базы с репрезентативным объемом данных.
Ищите операции с самым большим `actual time` — это ваша главная цель.
Сравнивайте `estimated rows` и `actual rows`. Если разница велика, обновите статистику таблиц командой `ANALYZE TABLE`.
Помните про избирательность индекса. Индекс на поле с двумя значениями (например, `gender`) почти бесполезен.
Используйте покрывающие индексы (индекс, который содержит все поля, запрашиваемые в SELECT).

FAQ: Ответы на частые вопросы

В чем разница между EXPLAIN и EXPLAIN ANALYZE?

EXPLAIN показывает предполагаемый план запроса, а EXPLAIN ANALYZE выполняет запрос и показывает фактические затраты времени и ресурсов. Для реальной оптимизации всегда используйте EXPLAIN ANALYZE.

Безопасно ли использовать EXPLAIN ANALYZE на рабочем сервере?

С осторожностью! Поскольку он выполняет запрос, тяжелый SELECT может нагрузить базу. Лучше делать это в часы минимальной нагрузки или на точной копии (реплике, дампе) продакшен-данных.

Почему оптимизатор MySQL не использует созданный мной индекс?

Причин несколько: низкая избирательность индекса, устаревшая статистика по таблице (поможет `ANALYZE TABLE`), или условие в запросе написано так, что не позволяет использовать индекс (например, `WHERE YEAR(date_column) = 2023` вместо `WHERE date_column BETWEEN '2023-01-01' AND '2023-12-31'`).

Как понять, какой индекс создать?

Смотрите на условия в WHERE, JOIN и ORDER BY/GROUP BY. Часто помогает составной индекс. Порядок полей в нем критичен: сначала идут поля для фильтрации (=), затем для диапазонов (>, <, BETWEEN), и в конце — для сортировки/группировки.