Depurar un mal plan con EXPLAIN ANALYZE y el 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';

Qué hace

EXPLAIN ANALYZE muestra lado a lado los valores estimados (cost, rows) y reales (actual time, rows, loops) de cada paso del plan; el optimizer trace muestra las rutas de acceso consideradas, sus costos y por qué se eligió o descartó cada una.

Cuándo usarlo

Cuando una consulta se ralentiza de repente (cambio de plan), el optimizador elige el índice equivocado, o para encontrar el origen de una discrepancia entre filas estimadas y reales.

Precaución

EXPLAIN ANALYZE ejecuta realmente la consulta; tenga cuidado con consultas pesadas en producción. Funciona con SELECT, TABLE y UPDATE / DELETE multitabla. Una diferencia de más de 10x entre filas estimadas y reales apunta a un problema de estadísticas / histogramas (ANALYZE TABLE, histogramas). En el lado interno de un nested loop, las filas reales son por loop; total = rows × loops. Si MISSING_BYTES_BEYOND_MAX_MEM_SIZE > 0 la traza se truncó; aumente optimizer_trace_max_mem_size. La traza solo contiene la última sentencia de la misma sesión.