MySQL: EXPLAIN ANALYZE и optimizer_trace — читаем план, считаем время, находим узкие места

Зачем вам сразу два инструмента: EXPLAIN ANALYZE и optimizer_trace

Когда всплывает slow query, первая реакция — запустить EXPLAIN и посмотреть, какой план выбрал MySQL. Но у классического EXPLAIN есть фундаментальное ограничение: он показывает прогноз оптимизатора, а не то, что реально произошло во время выполнения. Отсюда типичный сценарий: «по плану всё хорошо», а по факту запрос «жует» секунды и нагружает диск.

EXPLAIN ANALYZE (MySQL 8.0+) запускает запрос и возвращает фактические метрики: время на узлах, реальное количество строк и число повторов (loops). Это уже не гадание, а измерение.

optimizer_trace — инструмент другого класса: он объясняет, почему оптимизатор выбрал именно такой план. В трассировке видно, какие индексы рассматривались, как оценивалась селективность, почему какой-то индекс «не подошёл», и по какой логике выбрали порядок join’ов.

Думайте так: EXPLAIN ANALYZE отвечает на вопрос «что реально случилось?», а optimizer_trace — «почему оптимизатор решил именно так?».

База для диагностики slow query: что собрать до оптимизации

Чтобы не оптимизировать вслепую, сначала зафиксируйте контекст. Минимум, который помогает в 80% случаев:

• Текст запроса и конкретные параметры. Разные значения могут давать разную селективность и другой план.
• Версия MySQL, движок таблиц (обычно InnoDB), примерные объёмы данных.
• Схемы таблиц и индексы (важно: составные индексы и их порядок колонок).
• Симптом: упор в CPU или I/O, рост чтений, временные таблицы, блокировки.

Если запрос «плавающий», обязательно отметьте: он медленный всегда или только на части значений. Очень часто проблема связана с неверными оценками кардинальности и переключением планов в зависимости от параметров.

Если вы воспроизводите проблему на стенде или реплике, заранее проверьте, есть ли актуальная статистика у InnoDB и не «уехали» ли распределения данных.

Облачный VDS-сервер в России

Аренда виртуальных серверов с моментальным развертыванием инфраструктуры от 195₽ / мес

Подробнее

EXPLAIN ANALYZE: как запускать безопасно и что он реально делает

EXPLAIN ANALYZE выполняет запрос. Это означает три практичные вещи:

• Он может быть дорогим по времени и ресурсам, особенно на больших таблицах.
• Он учитывает реальную конкуренцию и блокировки на момент запуска.
• Для обычного SELECT это чаще всего безопасно, но с SELECT ... FOR UPDATE и похожими сценариями нужно быть аккуратнее.

Запуск выглядит так:

EXPLAIN ANALYZE
SELECT ...;

Вывод содержит дерево итераторов (iterator tree) с фактическими метриками. Поля, которые почти всегда дают быстрый ответ «где болит»:

• actual time — фактическое время на узле (обычно диапазон start..end).
• rows — сколько строк реально прошло через узел.
• loops — сколько раз узел выполнялся (особенно важно при nested loop join).

Как быстро найти узкое место по дереву

Практическое правило: ищите узел, где одновременно есть большой actual time и заметная «мясорубка» по объёму — много rows и/или много loops. Отдельный красный флаг — когда количество строк резко «раздувается» на одном из узлов (по сравнению с ожиданиями).

Частый кейс: «маленький» index lookup, который выполняется десятки тысяч раз из-за неверного join-order. В классическом EXPLAIN это иногда заметно, но EXPLAIN ANALYZE показывает фактическое число повторов и реальное время на каждом шаге.

Сверяем прогноз и реальность: где план “правильный”, но медленно

Одна из самых ценных практик с EXPLAIN ANALYZE — сравнение оценок оптимизатора и факта. Если реальное число строк и время сильно расходятся с оценками, это сигнал, что модель оптимизатора промахнулась.

На практике это обычно означает одно из следующих:

• статистика устарела;
• распределение данных неравномерное (скошенное), и «средняя температура по больнице» не работает;
• условие плохо индексируется или оптимизатор не может корректно оценить селективность;
• мешают функция/приведение типов/коллация/не-SARGable выражение.

В таких случаях иногда быстрее даёт эффект не «магия с индексами», а приведение условий к SARGable-форме и обновление статистики, чтобы оптимизатор перестал ошибаться в оценках.

Index Condition Pushdown (ICP): что это и как увидеть пользу

Index condition pushdown (ICP) — оптимизация, когда часть условий WHERE проверяется прямо на уровне индекса, чтобы уменьшить количество обращений к строкам таблицы (для InnoDB — меньше чтений по первичному ключу).

ICP помогает, когда условие можно проверить по данным, лежащим в индексной записи. Типовой пример: составной индекс используется для диапазона по ведущей колонке, а дополнительное условие фильтруется уже «по индексу», не трогая лишние строки таблицы.

В классическом EXPLAIN ICP обычно виден в Extra как Using index condition. В EXPLAIN ANALYZE эффект видно косвенно: меньше фактически прошедших строк на узлах и меньше времени на чтение строк из таблицы.

Типичная ошибка — ожидать ICP там, где условие использует выражение над индексируемым столбцом. Например, WHERE DATE(created_at) = ... часто ломает возможность эффективного range-скана (если только вы не используете подходящий функциональный индекс и корректную форму запроса).

optimizer_trace: как включить и как его читать без боли

optimizer_trace включается на сессию. Глобально включать на продакшене без необходимости не стоит: трассировка увеличивает объём внутренней диагностики, а результат может получиться большим.

Базовый сценарий включения и получения трассы:

SET optimizer_trace="enabled=on";
SET optimizer_trace_max_mem_size=1048576;
SELECT ...;
SELECT TRACE FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";

В ответ вы получите JSON (как текст) с этапами оптимизации. Не пытайтесь читать его сверху вниз целиком. В реальной работе полезнее «выцеплять» ключевые блоки:

• join_optimization — выбор порядка таблиц и сравнение стоимости планов;
• rows_estimation — как оценивалось число строк по условиям;
• considered_execution_plans — какие планы рассматривались и почему отбрасывались;
• range_analysis — анализ применимости индексов под ваши предикаты.

Что искать в optimizer_trace при проблемах с индексами

Когда кажется, что «индекс есть, но не используется», optimizer_trace часто показывает конкретную причину. Самые частые находки:

• индекс признан невыгодным из-за низкой селективности (слишком много строк на одно значение);
• несовместимость типов приводит к неэффективному сравнению и отказу от range-скана;
• условия с OR ломают простой range-план и уводят в полный проход;
• оптимизатор выбрал другой индекс, потому что статистика считает его дешевле.

Самый полезный участок — там, где MySQL в терминах трассировки фиксирует причину отказа от конкретного индекса. Это экономит часы «переписываний наугад» и экспериментов.

Практический разбор: почему “по плану норм”, а по факту плохо

Частая ситуация: запрос соединяет «маленькую» справочную таблицу и «большую» таблицу событий. По логике разработчика сначала должен примениться фильтр по диапазону дат, а уже затем join. Но оптимизатор может решить иначе, если оценит фильтр по дате как слабый (или если статистика устарела).

Что делаем пошагово:

1. Снимаем EXPLAIN и фиксируем базовый план: порядок join, тип доступа (ALL, range, ref), выбранный ключ, оценку rows, долю filtered.
2. Запускаем EXPLAIN ANALYZE и проверяем, не раздувается ли loops на lookup в «большую» таблицу.
3. Если раздувается — включаем optimizer_trace и выясняем, почему выбран такой порядок join’ов и почему не «продавился» нужный range-скан по индексу.

По результату обычно всплывает одна из причин:

• не хватает составного индекса под фильтр + join (например, нужен (created_at, foreign_id) или наоборот — по факту вашего плана);
• условие по дате написано так, что MySQL не делает range (например, функция на колонке);
• статистика считает фильтр «слишком широким», и оптимизатор выбирает другой порядок и другой способ доступа.

Если вы готовите диагностику на отдельной машине, удобнее делать это на VDS с копией данных или на реплике: так вы безопаснее воспроизводите тяжёлые планы и можете сравнивать «до/после» без влияния на продакшен.

Частые ошибки при чтении query plan и как не попасться

1) Слепо верить «rows» в EXPLAIN

Оценка строк — это модель. Если модель ошиблась, план может стать катастрофическим при некоторых значениях параметров. EXPLAIN ANALYZE нужен, чтобы увидеть расхождение оценки и реальности.

2) Путать “Using index” и “Using index condition”

Using index часто означает «покрывающий индекс» (данные берутся из индекса без чтения таблицы). Using index condition — это ICP: часть фильтра применяется на уровне индекса, но чтение таблицы всё ещё может быть нужно. Это разные оптимизации и эффект у них разный.

3) Лечить запрос FORCE INDEX без понимания причины

Иногда это временно помогает, но часто превращается в технический долг: данные меняются, статистика меняется, а принудительный индекс начинает вредить. Обычно правильнее понять причину выбора плана через optimizer_trace и затем поправить схему/индекс/условие.

Мини-набор действий для ускорения: от «быстро» к «правильно»

Когда вы нашли узкое место через EXPLAIN ANALYZE и поняли мотивы оптимизатора через optimizer_trace, обычно работают такие шаги (от наименее инвазивных):

1. Привести условия к SARGable-форме: без функций по колонке, без неявных преобразований типов и неожиданных коллаций.
2. Добавить или пересобрать индекс под реальные предикаты: как правило, сначала колонки из WHERE, затем колонки join, затем сортировка/группировка (но финальный порядок всегда проверяйте планом).
3. Сузить выборку: выбирать только нужные поля, избегать SELECT * на широких таблицах.
4. Проверить, не провоцирует ли запрос большие сортировки, временные таблицы и агрегации на огромном объёме.

Перед тем как менять индексы на проде, удобно погонять «до/после» на отдельной среде. Для таких экспериментов подойдёт виртуальный хостинг для небольших баз или VDS, если нужны права на тюнинг инстанса и более тяжёлые тесты.

Виртуальный хостинг для сайтов

Универсальное решение для создания и размещения сайтов любой сложности в Интернете от 95₽ / мес

Подробнее

Нюансы эксплуатации: когда диагностика сама становится нагрузкой

На загруженной базе любые диагностические действия должны быть дозированными. Рекомендации из практики:

• Если запрос тяжёлый, запускайте EXPLAIN ANALYZE на копии данных или на реплике.
• Учитывайте таймауты на стороне приложения/клиента и делайте проверки в «тихие» окна.
• optimizer_trace включайте только на свою сессию и выключайте сразу после получения результата.

Если запрос настолько тяжёлый, что вы боитесь запускать EXPLAIN ANALYZE, сначала снимите обычный EXPLAIN, оцените масштаб чтений и только потом воспроизводите на стенде.

Шпаргалка: как связать slow query → план → причина → фикс

1. Есть slow query: фиксируем запрос, параметры, частоту и влияние на систему.
2. Снимаем EXPLAIN: понимаем выбранные индексы и порядок операций.
3. Снимаем EXPLAIN ANALYZE: ищем узел с максимальным временем, строками и loops.
4. Снимаем optimizer_trace: проверяем, почему оптимизатор выбрал этот путь и почему отверг альтернативы.
5. Меняем запрос/индекс/схему, повторяем измерения, фиксируем эффект (до/после).

Для восстановления после неудачных изменений и безопасных экспериментов полезно иметь план отката и понимание point-in-time recovery. Если тема актуальна, загляните в материал про PITR в MySQL/MariaDB по binlog и GTID.

Заключение

EXPLAIN ANALYZE даёт то, чего не хватало классическому EXPLAIN: факт выполнения и реальную стоимость узлов плана. А optimizer_trace объясняет логику оптимизатора, из-за которой вы иногда видите «странный» план и неожиданные провалы по времени.

Вместе эти инструменты превращают оптимизацию из магии в инженерный процесс: измерили, поняли, исправили, проверили. Если slow query повторяются, выработайте привычку: сначала измерять через EXPLAIN ANALYZE, затем подтверждать причины через optimizer_trace, и только после этого менять индексы или переписывать запрос.