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이면 trace가 잘린 것이므로 optimizer_trace_max_mem_size를 늘린다. trace는 같은 세션의 마지막 문장만 보관한다.