Depurar um plano ruim com EXPLAIN ANALYZE e o optimizer trace

Comando

-- 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';

O que faz

EXPLAIN ANALYZE mostra lado a lado os valores estimados (cost, rows) e reais (actual time, rows, loops) de cada etapa do plano; o optimizer trace mostra os caminhos de acesso considerados, seus custos e por que cada um foi escolhido ou rejeitado.

Quando usar

Quando uma consulta fica lenta de repente (mudança de plano), o otimizador escolhe o índice errado, ou para achar a origem de uma divergência entre linhas estimadas e reais.

Atenção

EXPLAIN ANALYZE executa a consulta de fato; cuidado com consultas pesadas em produção. Funciona com SELECT, TABLE e UPDATE / DELETE multi-tabela. Uma diferença de mais de 10x entre linhas estimadas e reais aponta para um problema de estatísticas / histograma (ANALYZE TABLE, histogramas). No lado interno de um nested loop, actual rows é por loop; total = rows × loops. Se MISSING_BYTES_BEYOND_MAX_MEM_SIZE > 0, o trace foi truncado; aumente optimizer_trace_max_mem_size. O trace guarda apenas o último statement da mesma sessão.