Как анализировать медленный запрос с помощью EXPLAIN?

Форматы EXPLAIN

-- Классический табличный вывод
EXPLAIN
SELECT o.id, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 20;

-- Древовидный формат (MySQL 8.0+)
EXPLAIN FORMAT=TREE
SELECT o.id, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 20;

-- Фактические метрики выполнения (MySQL 8.0.18+)
EXPLAIN ANALYZE
SELECT o.id, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 20;

Как читать вывод EXPLAIN

MySQL EXPLAIN возвращает план выполнения запроса до его фактического запуска. Каждая строка соответствует одной операции (таблице или подзапросу). Анализ начинается с самых дорогих строк по полю rows.

Ключевые поля

• type — тип доступа по убыванию стоимости: ALL (full scan) → index → range → ref → eq_ref → const/system. Цель — избавиться от ALL на больших таблицах.
• key — фактически использованный индекс; NULL означает отсутствие подходящего индекса.
• key_len — длина использованной части составного индекса в байтах; помогает понять, сколько колонок задействовано.
• rows — оценка числа строк, которые MySQL планирует прочитать; реальное значение может отличаться.
• filtered — процент строк, прошедших условие WHERE после чтения; низкое значение указывает на неселективный индекс.
• Extra — дополнительные сигналы: Using filesort (лишняя сортировка), Using temporary (временная таблица), Using index (covering index — хорошо), Using where.

Практический алгоритм диагностики

1. Включить slow query log: SET GLOBAL slow_query_log = 1; SET GLOBAL long_query_time = 1;
2. Найти запрос в /var/log/mysql/slow.log или через pt-query-digest.
3. Запустить EXPLAIN ANALYZE и найти строку с максимальным actual rows.
4. Проверить type: если ALL — нужен индекс или переписывание запроса.
5. Проверить Extra: Using filesort — добавить индекс на ORDER BY; Using temporary — пересмотреть GROUP BY или подзапрос.
6. Создать индекс, повторить EXPLAIN ANALYZE, сравнить фактические метрики.

Полезные системные таблицы

-- Статистика ожиданий InnoDB (MySQL 8.0, performance_schema)
SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1e12 AS total_sec
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE COUNT_STAR > 0
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- Текущие блокировки
SELECT * FROM performance_schema.data_locks\G

Подводные камни

• EXPLAIN не запускает запрос (кроме ANALYZE) — оценки optimizer могут сильно расходиться с реальностью при устаревшей статистике; запустите ANALYZE TABLE перед диагностикой.
• Индекс есть, но не используется — optimizer может предпочесть full scan, если таблица маленькая, или статистика некорректна; используйте FORCE INDEX(idx_name) для проверки гипотезы.
• Using filesort не всегда плохо — на небольших выборках filesort быстрее обращения к индексу; ориентируйтесь на фактическое время из EXPLAIN ANALYZE, а не только на Extra.
• Covering index и key_len — составной индекс (a, b, c) будет использован только до первого диапазонного условия; WHERE a=1 AND b>5 AND c=3 задействует только (a, b).
• EXPLAIN на replica может отличаться — plan зависит от актуальности статистики и настроек optimizer_switch, которые могут расходиться между primary и replica.
• Временные таблицы и UNION — EXPLAIN для UNION показывает несколько строк с <union1,2>; легко упустить дорогой шаг.
• JSON/TREE формат в старых клиентах — FORMAT=JSON и FORMAT=TREE доступны только в MySQL 8.0+; на 5.7 выбросит синтаксическую ошибку.
• EXPLAIN ANALYZE изменяет состояние — для DML-запросов (UPDATE, DELETE) EXPLAIN ANALYZE фактически выполняет операцию внутри транзакции, которую автоматически откатывает; не запускайте в production без осознания этого.

• Не уточнять storage engine и говорить о explain для медленного запроса так, будто MyISAM и InnoDB ведут себя одинаково.
• Игнорировать implicit commit, autocommit, isolation level или binlog там, где они меняют ответ.
• Описывать PostgreSQL-поведение как MySQL-поведение.
• Забывать проверить план через EXPLAIN и фактическую версию MySQL.

• Кандидат объясняет explain для медленного запроса через реальный механизм MySQL, а не общими словами.
• Приводит корректный SQL или диагностический запрос для этой СУБД.
• Называет ограничения, версионные отличия или эксплуатационные последствия.
• Связывает ответ с проектированием приложения, производительностью, надежностью или безопасностью.