Представьте, что ваш сайт или приложение начинает тормозить. Пользователи жалуются, а вы видите, что сервер баз данных загружен на 90%. Чаще всего проблема не в железе, а в неоптимальных SQL-запросах. Инструменты EXPLAIN и EXPLAIN ANALYZE в MySQL — это ваш рентген, который показывает внутреннюю работу запросов, позволяя находить узкие места и ускорять работу в разы, а иногда и на порядки.
Что такое EXPLAIN и чем EXPLAIN ANALYZE лучше?
Команда EXPLAIN существует давно и показывает предполагаемый план выполнения запроса — то, как оптимизатор MySQL планирует его выполнить. Вы видите ключевые метрики: тип соединения таблиц, возможные и используемые индексы, приблизительное количество проверяемых строк.
EXPLAIN — это прогноз. EXPLAIN ANALYZE — это отчёт о реальной работе запроса, с точными замерами времени и количества строк на каждом этапе. Это как разница между планом тренировки и детальным отчётом фитнес-браслета после забега.
Команда EXPLAIN ANALYZE, появившаяся в MySQL 8.0.18, выполняет запрос на самом деле и собирает реальную статистику. Это ключевое отличие! Теперь вы видите не только «что думает оптимизатор», но и «что произошло на деле».
Как читать вывод EXPLAIN ANALYZE: Разбираем дерево выполнения
Вывод выглядит как древовидная структура. Давайте разберём ключевые элементы на примере:
EXPLAIN ANALYZE
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.city = 'Москва' AND o.status = 'completed';
В результате вы увидите что-то вроде:
-> Nested loop inner join (cost=... rows=... actual time=0.150..25.730 rows=1050 loops=1)
-> Filter: (o.status = 'completed') (actual time=0.050..12.200 rows=2100 loops=1)
-> Index scan on o using idx_user_id (actual time=0.030..8.500 rows=5000 loops=1)
-> Filter: (u.city = 'Москва') (actual time=0.001..0.005 rows=0.5 loops=2100)
-> Single-row index lookup on u using PRIMARY (id=o.user_id) (actual time=0.001..0.002 rows=1 loops=2100)
Ключевые метрики для анализа:
- actual time: Формат «начало..конец» в миллисекундах на одну операцию (первое значение) и общее время для всех итераций (второе значение).
- rows: Фактическое количество строк, обработанных на этом шаге.
- loops: Сколько раз выполнялась эта операция.
- Filter: Фильтрация строк после доступа к таблице. Если здесь большие числа — возможно, не хватает индекса.
Практические шаги оптимизации на основе анализа
Увидев план, вы можете действовать целенаправленно.
1. Борьба с полными сканированиями таблиц (Full Table Scan)
Если видите Table scan on <table_name> с большим actual time — это главный кандидат на оптимизацию. Решение — добавление индекса на колонки, используемые в WHERE, JOIN или ORDER BY.
Создавайте составные индексы, учитывая порядок колонок. Индекс (city, status) будет работать для фильтрации по city и по паре (city, status), но НЕ для фильтрации только по status.
2. Оптимизация вложенных циклов (Nested Loop)
Если во внутреннем цикле (второй строке вложенного соединения) много loops и каждая итерация медленная, попробуйте:
- Добавить индекс для быстрого поиска во второй таблице.
- Переписать запрос, изменив порядок JOIN (иногда оптимизатор ошибается в выборе ведущей таблицы).
- Рассмотреть возможность денормализации данных для часто используемых связей.
3. Устранение временных таблиц и файловых сортировок (Using temporary; Using filesort)
Эти операции в плане (особенно для больших наборов данных) очень дороги. Они часто возникают при GROUP BY и ORDER BY по колонкам, отличным от индекса. Решение — создание индекса, покрывающего условия сортировки и группировки.
Распространённые ошибки и мифы
- «Больше индексов = лучше». Нет! Каждый индекс замедляет INSERT/UPDATE/DELETE и занимает место. Анализируйте, какие индексы реально используются (через мониторинг или
sys.schema_unused_indexes). - «EXPLAIN ANALYZE можно всегда запускать на продакшене». Осторожно! Запрос выполняется реально. Для тяжёлых запросов используйте тестовый стенд или делайте это в период минимальной нагрузки.
- «Оптимизация по одной метрике». Смотрите на общую картину. Ускорение одного запроса в 100 раз может не дать общего прироста, если он выполняется раз в день.
Интеграция в процесс разработки
Сделайте анализ запросов рутиной:
- Для всех новых нетривиальных запросов в Pull Request прикладывайте вывод EXPLAIN ANALYZE.
- Настройте мониторинг медленных запросов (slow query log, Performance Schema).
- Периодически (раз в квартал) проводите аудит самых частых и тяжелых запросов приложения.
FAQ: Часто задаваемые вопросы
В чём главное отличие EXPLAIN от EXPLAIN ANALYZE?
EXPLAIN показывает предполагаемый план выполнения запроса, не выполняя его. EXPLAIN ANALYZE реально выполняет запрос и предоставляет фактические метрики времени и количества обработанных строк, что делает анализ точным.
Безопасно ли использовать EXPLAIN ANALYZE на рабочей базе?
С осторожностью. Поскольку запрос выполняется реально, тяжёлый SELECT может создать нагрузку. Лучше тестировать на копии данных (стенде) или в период минимальной активности.
Какой самый частый признак проблемы в плане запроса?
Полное сканирование таблицы (Full Table Scan или ALL в поле "type" старого EXPLAIN) при большом количестве строк. Это почти всегда сигнал к созданию или изменению индекса.
Почему оптимизатор MySQL иногда выбирает «плохой» план?
Из-за устаревшей статистики по таблицам. MySQL оценивает селективность индексов на основе статистики, которая может быть неточной, особенно после больших изменений данных. Помогает команда ANALYZE TABLE <имя_таблицы>.
EXPLAIN ANALYZE показывает, что индекс не используется. Почему?
Возможные причины: 1) Низкая селективность индекса (например, индекс по колонке с полом — всего 2 значения). Оптимизатор решает, что быстрее просканировать таблицу. 2) Неверный порядок колонок в составном индексе. 3) Использование функций или преобразований типов в условии WHERE (например, WHERE DATE(created_at) = '...').