optimizer_trace 활용: 실행 계획 선택 과정을 추적하는 방법
MySQL optimizer_trace로 실행 계획 후보, 비용 비교, 선택 이유를 확인하고 운영 진단에 적용하는 방법을 정리한다.
optimizer_trace 활용: 실행 계획 선택 과정을 추적하는 방법
EXPLAIN은 MySQL 옵티마이저가 최종적으로 선택한 실행 계획을 보여준다. EXPLAIN FORMAT=JSON은 비용과 내부 속성을 더 자세히 보여주고, EXPLAIN ANALYZE는 실제 실행 통계를 붙여 준다. 그러나 운영자가 자주 마주치는 질문은 한 단계 더 안쪽에 있다. “왜 이 인덱스를 골랐는가?”, “왜 조인 순서가 바뀌었는가?”, “분명 더 좋아 보이는 인덱스가 있는데 왜 쓰지 않았는가?” 같은 질문이다.
optimizer_trace는 이 질문에 답하기 위한 MySQL의 진단 기능이다. 세션 단위로 켜고 쿼리를 실행하면, 옵티마이저가 후보 접근 경로를 만들고 비용을 계산하고 일부 후보를 버리는 과정을 JSON 형태로 information_schema.OPTIMIZER_TRACE에 남긴다. 이 기능은 상시 모니터링 도구가 아니라, 특정 쿼리의 계획 선택 과정을 짧게 추적하는 분석 도구에 가깝다.
운영 환경에서는 실행 계획 문제가 단순히 “인덱스를 추가한다”로 끝나지 않는다. 통계가 오래되었는지, 히스토그램이 필요한지, 조건식이 sargable하지 않은지, 조인 순서가 데이터 분포와 맞지 않는지, 비용 모델이 디스크·메모리·랜덤 읽기를 어떻게 평가했는지를 확인해야 한다. optimizer_trace는 이런 판단을 EXPLAIN보다 더 깊게 검증할 때 유용하다.
1. optimizer_trace가 보여주는 범위
MySQL 옵티마이저는 SQL을 받으면 대략 다음 단계를 거친다.
graph TD
A[SQL 수신] --> B[파싱과 query block 구성]
B --> C[조건식 정리와 상수 전파]
C --> D[가능한 접근 경로 생성]
D --> E[인덱스 range/ref/scan 후보 비용 계산]
E --> F[조인 순서와 조인 방식 비교]
F --> G[정렬, 그룹화, 임시 테이블 비용 평가]
G --> H[최종 실행 계획 선택]
H --> I[Executor 실행]
D -. optimizer_trace .-> T[(information_schema.OPTIMIZER_TRACE)]
E -. optimizer_trace .-> T
F -. optimizer_trace .-> T
G -. optimizer_trace .-> T
optimizer_trace는 위 흐름 중 주로 옵티마이저 단계의 판단 근거를 기록한다. 실제 row를 얼마나 읽었는지, 각 iterator가 실제로 몇 번 반복되었는지는 EXPLAIN ANALYZE가 더 직접적이다. 반대로 EXPLAIN ANALYZE는 선택되지 않은 후보 계획을 자세히 보여주지 않는다. 따라서 두 기능의 역할은 다음처럼 나누어 이해하는 것이 좋다.
| 도구 | 주 질문 | 강점 | 주의점 |
|---|---|---|---|
EXPLAIN |
어떤 계획이 선택되었는가 | 빠르고 간단하다 | 선택 이유와 버려진 후보가 부족하다 |
EXPLAIN FORMAT=JSON |
선택 계획의 비용과 속성은 무엇인가 | 비용, attached condition, used key parts 확인이 쉽다 | 여전히 최종 계획 중심이다 |
EXPLAIN ANALYZE |
실제 실행은 추정과 맞았는가 | actual rows/time으로 추정 오류를 찾기 좋다 | 쿼리를 실제 실행한다 |
optimizer_trace |
왜 그 계획을 선택했는가 | 후보 접근 경로와 비용 비교를 볼 수 있다 | JSON이 길고 버전별 표현 차이가 있다 |
2. 기본 사용 흐름
optimizer_trace는 세션 변수로 켠다. 켠 뒤 분석 대상 SQL을 실행하고, 같은 세션에서 information_schema.OPTIMIZER_TRACE를 조회한다. 진단이 끝나면 다시 끄는 것이 좋다.
다음 예제는 하나의 테스트 테이블에 두 개의 후보 인덱스를 만들고, 옵티마이저 trace가 생성되는지 확인한다. 실제 운영 쿼리에 적용할 때도 구조는 같다. 다만 운영에서는 SELECT라도 비용이 큰 쿼리를 그대로 실행하지 말고, 먼저 EXPLAIN, 샘플 조건, 낮은 부하 시간대, 읽기 복제본 활용 여부를 검토해야 한다.
SET optimizer_trace = 'enabled=on';
SET optimizer_trace_max_mem_size = 1048576;
DROP TABLE IF EXISTS trace_order_items;
CREATE TABLE trace_order_items (
id BIGINT NOT NULL PRIMARY KEY,
customer_id BIGINT NOT NULL,
status VARCHAR(16) NOT NULL,
created_at DATETIME NOT NULL,
amount DECIMAL(10,2) NOT NULL,
KEY idx_customer_status (customer_id, status),
KEY idx_status_created (status, created_at)
) ENGINE=InnoDB;
INSERT INTO trace_order_items VALUES
(1, 101, 'PAID', '2026-01-01 10:00:00', 120.00),
(2, 101, 'PAID', '2026-01-03 11:00:00', 80.00),
(3, 101, 'CANCELLED', '2026-01-05 09:00:00', 30.00),
(4, 102, 'PAID', '2026-01-07 12:00:00', 220.00),
(5, 103, 'PENDING', '2026-01-08 13:00:00', 45.00),
(6, 104, 'PAID', '2026-02-01 14:00:00', 70.00),
(7, 105, 'CANCELLED', '2026-02-02 15:00:00', 15.00),
(8, 101, 'PAID', '2026-02-03 16:00:00', 95.00);
ANALYZE TABLE trace_order_items;
SELECT SUM(amount) AS paid_amount
FROM trace_order_items
WHERE customer_id = 101
AND status = 'PAID'
AND created_at >= '2026-01-01';
SELECT QUERY,
MISSING_BYTES_BEYOND_MAX_MEM_SIZE,
JSON_CONTAINS_PATH(TRACE, 'one', '$.steps') AS has_steps,
JSON_LENGTH(JSON_EXTRACT(TRACE, '$.steps')) AS step_count,
CASE
WHEN TRACE LIKE '%range_analysis%' THEN 'range 분석 포함'
ELSE 'range 분석 문자열 미확인'
END AS range_trace_signal
FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace = 'enabled=off';
DROP TABLE trace_order_items;
실행 결과(MySQL 8.0.x):
mysql> CREATE TABLE trace_order_items (...);
Query OK, 0 rows affected
mysql> INSERT INTO trace_order_items VALUES (...);
Query OK, 8 rows affected
Records: 8 Duplicates: 0 Warnings: 0
mysql> ANALYZE TABLE trace_order_items;
+-----------------------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------------------+---------+----------+----------+
| mysql_tech_note.trace_order_items | analyze | status | OK |
+-----------------------------------+---------+----------+----------+
mysql> SELECT SUM(amount) AS paid_amount
-> FROM trace_order_items
-> WHERE customer_id = 101
-> AND status = 'PAID'
-> AND created_at >= '2026-01-01';
+-------------+
| paid_amount |
+-------------+
| 295.00 |
+-------------+
mysql> SELECT QUERY,
-> MISSING_BYTES_BEYOND_MAX_MEM_SIZE,
-> JSON_CONTAINS_PATH(TRACE, 'one', '$.steps') AS has_steps,
-> JSON_LENGTH(JSON_EXTRACT(TRACE, '$.steps')) AS step_count,
-> CASE ... END AS range_trace_signal
-> FROM information_schema.OPTIMIZER_TRACE;
+----------------------+-----------------------------------+-----------+------------+---------------------+
| QUERY | MISSING_BYTES_BEYOND_MAX_MEM_SIZE | has_steps | step_count | range_trace_signal |
+----------------------+-----------------------------------+-----------+------------+---------------------+
| SELECT SUM(amount)... | 0 | 1 | 3 | range 분석 포함 |
+----------------------+-----------------------------------+-----------+------------+---------------------+
위 예제에서 중요한 부분은 SELECT SUM(amount) 자체보다 그 직후의 information_schema.OPTIMIZER_TRACE 조회다. QUERY에는 추적 대상 SQL이 들어가고, TRACE에는 JSON 문서가 들어간다. MISSING_BYTES_BEYOND_MAX_MEM_SIZE가 0보다 크면 trace가 잘렸다는 뜻이므로 optimizer_trace_max_mem_size를 늘리거나 더 작은 쿼리로 재현해야 한다.
3. trace JSON을 읽는 관점
TRACE 컬럼은 JSON이다. 전체를 그대로 읽으면 길고 세부 키가 MySQL minor version에 따라 달라질 수 있다. 실무에서는 먼저 큰 구조를 확인하고, 그 다음 필요한 영역만 좁혀 보는 편이 안전하다.
일반적으로 많이 보는 영역은 다음과 같다.
join_preparation: query block 준비, 조건식 정리, view/derived table 처리의 흔적join_optimization: 접근 경로 선택, 조건 처리, 조인 순서 평가의 핵심 영역rows_estimation: table scan, range scan, index dive, histogram 등 row 추정 관련 단서considered_execution_plans: 조인 순서와 plan prefix별 비용 비교attaching_conditions_to_tables: 조건이 어느 테이블에 붙었는지 확인하는 영역refine_plan: 선택된 계획을 실행 가능한 형태로 다듬는 단계
다음 쿼리는 trace 문서의 최상위 단계 수와, 실제 trace 안에 특정 옵티마이저 단계 문자열이 포함되어 있는지 확인하는 작은 진단 예제다. 버전별 JSON 세부 경로를 강하게 가정하지 않고, 먼저 “trace가 정상적으로 남았고 필요한 단서가 들어 있는가”를 확인하는 용도다.
SET optimizer_trace = 'enabled=on';
SET optimizer_trace_max_mem_size = 1048576;
CREATE TABLE trace_customer_orders (
id BIGINT NOT NULL PRIMARY KEY,
customer_id BIGINT NOT NULL,
order_date DATE NOT NULL,
status VARCHAR(16) NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
KEY idx_customer_date (customer_id, order_date),
KEY idx_status_date (status, order_date)
) ENGINE=InnoDB;
INSERT INTO trace_customer_orders VALUES
(1, 201, '2026-01-01', 'PAID', 100.00),
(2, 201, '2026-01-02', 'PAID', 110.00),
(3, 201, '2026-01-03', 'PENDING', 90.00),
(4, 202, '2026-01-04', 'PAID', 200.00),
(5, 203, '2026-01-05', 'CANCELLED', 50.00),
(6, 204, '2026-01-06', 'PAID', 70.00);
ANALYZE TABLE trace_customer_orders;
SELECT id, total_amount
FROM trace_customer_orders
WHERE customer_id = 201
AND status = 'PAID'
ORDER BY order_date;
SELECT JSON_LENGTH(JSON_EXTRACT(TRACE, '$.steps')) AS top_level_steps,
CASE WHEN TRACE LIKE '%rows_estimation%' THEN 'rows 추정 단계 확인' END AS rows_estimation,
CASE WHEN TRACE LIKE '%considered_execution_plans%' THEN '실행 계획 후보 확인' END AS considered_plans,
CASE WHEN TRACE LIKE '%attaching_conditions_to_tables%' THEN '조건 부착 단계 확인' END AS condition_attachment
FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace = 'enabled=off';
DROP TABLE trace_customer_orders;
실행 결과(MySQL 8.0.x):
mysql> CREATE TABLE trace_customer_orders (...);
Query OK, 0 rows affected
mysql> INSERT INTO trace_customer_orders VALUES (...);
Query OK, 6 rows affected
Records: 6 Duplicates: 0 Warnings: 0
mysql> ANALYZE TABLE trace_customer_orders;
+---------------------------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------------------------+---------+----------+----------+
| mysql_tech_note.trace_customer_orders | analyze | status | OK |
+---------------------------------------+---------+----------+----------+
mysql> SELECT id, total_amount
-> FROM trace_customer_orders
-> WHERE customer_id = 201
-> AND status = 'PAID'
-> ORDER BY order_date;
+----+--------------+
| id | total_amount |
+----+--------------+
| 1 | 100.00 |
| 2 | 110.00 |
+----+--------------+
mysql> SELECT JSON_LENGTH(JSON_EXTRACT(TRACE, '$.steps')) AS top_level_steps,
-> CASE WHEN TRACE LIKE '%rows_estimation%' THEN 'rows 추정 단계 확인' END AS rows_estimation,
-> CASE WHEN TRACE LIKE '%considered_execution_plans%' THEN '실행 계획 후보 확인' END AS considered_plans,
-> CASE WHEN TRACE LIKE '%attaching_conditions_to_tables%' THEN '조건 부착 단계 확인' END AS condition_attachment
-> FROM information_schema.OPTIMIZER_TRACE;
+-----------------+---------------------------+-----------------------------+-----------------------------+
| top_level_steps | rows_estimation | considered_plans | condition_attachment |
+-----------------+---------------------------+-----------------------------+-----------------------------+
| 3 | rows 추정 단계 확인 | 실행 계획 후보 확인 | 조건 부착 단계 확인 |
+-----------------+---------------------------+-----------------------------+-----------------------------+
이런 방식은 처음 분석할 때 유용하다. 이후 실제 운영 분석에서는 TRACE 전체를 파일로 저장하거나, JSON 경로를 더 좁혀 range_analysis, chosen_range_access_summary, considered_execution_plans 근처를 읽는다. 다만 MySQL 버전에 따라 키의 위치와 표현이 바뀔 수 있으므로, 공개 runbook이나 자동화 스크립트에서 특정 JSON path에 과도하게 의존하는 것은 피하는 편이 좋다.
4. 실행 계획 선택을 해석하는 절차
optimizer_trace를 읽을 때는 JSON을 위에서 아래로 모두 번역하려고 하면 비효율적이다. 운영 진단에서는 다음 순서가 실용적이다.
4.1 최종 계획을 먼저 고정한다
먼저 EXPLAIN FORMAT=JSON 또는 EXPLAIN ANALYZE로 현재 선택 계획을 확인한다. 어떤 테이블이 먼저 읽히는지, 어떤 인덱스가 쓰이는지, rows 추정이 어느 정도인지 알아야 trace에서 무엇을 찾아야 하는지 정해진다.
4.2 버려진 후보를 찾는다
optimizer_trace의 핵심 가치는 선택되지 않은 후보를 볼 수 있다는 점이다. 예를 들어 idx_customer_status와 idx_status_created가 모두 후보처럼 보이는데 하나만 선택되었다면, trace에서 두 후보의 row 추정, range 조건, 비용 평가가 어떻게 달랐는지 확인한다.
비용이 높게 평가된 이유는 여러 가지일 수 있다.
- 선행 컬럼 조건이 없어 인덱스 사용 범위가 넓다.
- 조건식이 함수나 암시적 형 변환 때문에 range 조건으로 내려가지 못했다.
- 통계가 오래되었거나 데이터 분포가 심하게 비균등하다.
- 선택도는 좋아 보여도 정렬 또는 추가 row lookup 비용이 크다.
- 조인에서는 앞 테이블의 결과 row 수 추정이 뒤 테이블 비용을 크게 바꾼다.
4.3 추정 오류와 실제 실행 오류를 분리한다
옵티마이저가 “그 시점의 통계와 비용 모델로는 합리적인 선택”을 했을 수도 있고, 통계가 틀려서 나쁜 선택을 했을 수도 있다. optimizer_trace는 전자와 후자를 구분하는 데 도움을 준다. trace의 row 추정이 현실과 다르면 ANALYZE TABLE, 히스토그램, 인덱스 설계, 조건식 수정이 후보가 된다. 반면 추정은 맞는데 비용이 여전히 높다면 쿼리 구조나 인덱스 커버링 여부를 봐야 한다.
5. 운영 환경에서의 사용 원칙
optimizer_trace는 세션 단위 기능이므로 전체 서버에 즉시 영향을 주는 기능은 아니다. 하지만 운영에서 사용할 때는 몇 가지 원칙을 지켜야 한다.
첫째, trace는 메모리를 사용한다. optimizer_trace_max_mem_size가 너무 작으면 JSON이 잘리고, 너무 크게 잡으면 복잡한 쿼리 분석 중 세션 메모리 사용량이 커질 수 있다. 보통은 문제 쿼리 하나를 재현할 수 있을 만큼만 늘리고, 분석 후 끈다.
둘째, 분석 대상 SQL은 실제 실행된다. 단순 SELECT라도 큰 테이블을 읽으면 부하가 발생한다. DML은 당연히 더 위험하다. 운영 데이터 변경 SQL은 optimizer_trace를 위해 그대로 실행하지 말고, 트랜잭션 롤백 가능성, 스테이징 재현, 읽기 복제본, EXPLAIN 대체 가능성을 먼저 판단해야 한다.
셋째, trace는 “왜 지금 그렇게 판단했는가”를 보여준다. 데이터 분포, 통계, MySQL 버전, 파라미터, 히스토그램 존재 여부가 바뀌면 같은 SQL의 trace도 바뀔 수 있다. 따라서 장애 분석 문서에는 trace 일부만 붙이는 것이 아니라, 분석 시점의 EXPLAIN, 테이블 통계 갱신 여부, 관련 인덱스, 주요 조건 값 범위를 함께 남겨야 한다.
넷째, Aurora MySQL에서는 스토리지 계층과 운영 도구가 Community MySQL과 다르다. optimizer_trace 자체는 MySQL 호환 기능으로 사용할 수 있지만, Aurora의 스토리지 구조, 클러스터 캐시 상태, 리더/라이터 역할, Performance Insights 지표는 실행 시간 해석에 영향을 준다. 즉, “계획 선택 이유”는 optimizer_trace로 보고, “그 계획이 실제로 느린 이유”는 Aurora 지표와 함께 해석해야 한다.
6. 자주 발생하는 오해와 실패 모드
trace가 없거나 이전 쿼리만 보이는 경우
optimizer_trace는 같은 세션에서 켜고, 대상 SQL을 실행하고, 같은 세션에서 조회해야 한다. 커넥션 풀이 있는 애플리케이션 환경에서 세션이 바뀌면 trace를 볼 수 없다. CLI에서 재현하거나, 세션을 고정할 수 있는 진단 환경을 사용하는 것이 안전하다.
trace가 잘린 경우
MISSING_BYTES_BEYOND_MAX_MEM_SIZE가 0보다 크면 trace가 끝까지 기록되지 않았다. 이 상태에서 JSON 일부만 보고 결론을 내리면 위험하다. optimizer_trace_max_mem_size를 늘리거나, 조인 테이블 수를 줄인 축소 재현을 만든다.
비용이 낮은데 실제로 느린 경우
비용 모델은 실제 wall-clock time이 아니다. 랜덤 I/O, 버퍼 풀 적중률, 동시성, 잠금, 네트워크, Aurora 스토리지 지연, 디스크 상태는 trace 비용과 직접 일치하지 않는다. 이 경우 EXPLAIN ANALYZE, Performance Schema, slow query log, Aurora Performance Insights를 함께 봐야 한다.
힌트로 계획을 고정하고 끝내는 경우
optimizer_trace를 보면 “힌트를 넣으면 해결”처럼 보일 수 있다. 그러나 힌트는 데이터 분포가 바뀌면 장기 부채가 된다. 먼저 통계 갱신, 히스토그램, 인덱스 컬럼 순서, 쿼리 조건식, 불필요한 함수 적용 여부를 확인하고, 힌트는 회피책이나 임시 완화책으로 제한하는 것이 좋다.
7. DBA 점검표
- 대상 쿼리의 현재 계획을
EXPLAIN또는EXPLAIN FORMAT=JSON -
optimizer_trace -
MISSING_BYTES_BEYOND_MAX_MEM_SIZE - 통계 갱신(
ANALYZE TABLE - 분석 후
optimizer_trace
8. 결론
optimizer_trace는 실행 계획 문제를 “옵티마이저가 틀렸다”는 막연한 판단에서 “어떤 후보가 어떤 비용 때문에 선택되거나 버려졌는가”라는 검증 가능한 질문으로 바꾸어 준다. 특히 인덱스가 여러 개 있고, 조인 순서가 민감하며, 데이터 분포가 비균등한 쿼리에서는 EXPLAIN만으로 부족한 부분을 채워 준다.
다만 optimizer_trace는 최종 답이 아니라 분석 기록이다. trace는 비용 모델과 통계의 관점이고, 실제 성능은 실행기의 실제 row 수, I/O, 락, 메모리, Aurora 스토리지 상태까지 함께 봐야 한다. 다음 단계의 실행 계획 분석에서는 EXPLAIN, EXPLAIN FORMAT=JSON, EXPLAIN ANALYZE, optimizer_trace를 서로 보완적으로 사용해야 안정적인 운영 판단에 도달할 수 있다.