Déboguer un mauvais plan avec EXPLAIN ANALYZE et l'optimizer trace

Commande

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

Ce que fait la commande

EXPLAIN ANALYZE affiche côte à côte les valeurs estimées (cost, rows) et réelles (actual time, rows, loops) pour chaque étape du plan ; l'optimizer trace montre les chemins d'accès envisagés, leurs coûts et pourquoi chacun a été choisi ou rejeté.

Quand l'utiliser

Quand une requête ralentit soudainement (changement de plan), que l'optimiseur choisit le mauvais index, ou pour trouver l'origine d'un écart entre lignes estimées et réelles.

Attention

EXPLAIN ANALYZE exécute réellement la requête ; prudence avec les requêtes lourdes en production. Il fonctionne avec SELECT, TABLE et UPDATE / DELETE multi-tables. Un écart de plus de 10x entre lignes estimées et réelles indique un problème de statistiques / d'histogrammes (ANALYZE TABLE, histogrammes). Côté interne d'une nested loop, actual rows est par boucle ; total = rows × loops. Si MISSING_BYTES_BEYOND_MAX_MEM_SIZE > 0, la trace a été tronquée ; augmentez optimizer_trace_max_mem_size. La trace ne contient que la dernière instruction de la même session.