Отладка плохого плана с EXPLAIN ANALYZE и optimizer trace

Команда

-- Plan + actual time and rows (the query actually runs)
EXPLAIN ANALYZE
SELECT region, COUNT(*) FROM shop.big WHERE id < 20000 GROUP BY region;

-- Why the optimizer chose this plan: optimizer trace
SET SESSION optimizer_trace = 'enabled=on', SESSION optimizer_trace_max_mem_size = 1048576;
SELECT * FROM shop.big WHERE region = 'TR' AND id > 100 LIMIT 5;
SELECT JSON_PRETTY(JSON_EXTRACT(TRACE, '$**.considered_execution_plans')) AS plans,
       MISSING_BYTES_BEYOND_MAX_MEM_SIZE
FROM information_schema.OPTIMIZER_TRACE;
SET SESSION optimizer_trace = 'enabled=off';

Что делает

EXPLAIN ANALYZE показывает для каждого шага плана оценочные (cost, rows) и фактические (actual time, rows, loops) значения рядом; optimizer trace показывает рассмотренные пути доступа, их стоимость и почему каждый был выбран или отвергнут.

Когда использовать

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

Внимание

EXPLAIN ANALYZE реально выполняет запрос; осторожно с тяжёлыми запросами на продакшене. Работает с SELECT, TABLE и многотабличными UPDATE / DELETE. Расхождение оценочных и фактических строк более чем в 10 раз указывает на проблему статистики / гистограмм (ANALYZE TABLE, гистограммы). На внутренней стороне nested loop actual rows указывается на один цикл; итого = rows × loops. Если MISSING_BYTES_BEYOND_MAX_MEM_SIZE > 0, трассировка обрезана; увеличьте optimizer_trace_max_mem_size. Трассировка хранит только последнюю команду той же сессии.