쿼리 옵티마이저와 실행계획 읽기

옵티마이저는 비용을 추정하는 엔진이다

MySQL이 쿼리를 실행할 때 SQL문 그대로 실행하지 않는다. 옵티마이저(optimizer)가 먼저 실행 가능한 여러 계획(plan)의 비용을 추정하고, 가장 비용이 낮은 계획을 선택한다. 이를 비용 기반 옵티마이저(CBO, Cost-Based Optimizer) 라고 한다.

비용 추정에 사용하는 주요 정보는 다음과 같다.

• 통계 정보(statistics): information_schema.STATISTICS, InnoDB 내부 통계. 컬럼 선택도(cardinality), 인덱스 크기 등.
• 비용 상수: 디스크 I/O, 메모리 I/O, row 비교 비용 등 설정 가능한 상수값(mysql.server_cost, mysql.engine_cost).

통계가 오래되거나 부정확하면 옵티마이저가 잘못된 계획을 선택할 수 있다.

-- 테이블 통계 갱신
ANALYZE TABLE component;

-- InnoDB 통계 자동 갱신 설정 확인
SHOW VARIABLES LIKE 'innodb_stats_auto_recalc';

EXPLAIN: 비용 추정 결과를 보는 창

EXPLAIN은 옵티마이저가 선택한 실행 계획을 보여준다. 실제로 쿼리를 실행하지 않고 계획만 출력한다(단, subquery, derived table 등 일부는 실행될 수 있다).

1편에서 소개한 주요 컬럼에 더해 더 자세히 봐야 할 항목이 있다.

type 컬럼 해석

type은 테이블 접근 방식이다. 좋은 순서대로 나열하면 다음과 같다.

ALL이 나왔다고 무조건 나쁜 것은 아니다. 작은 테이블에서는 full scan이 더 빠를 수 있다. 하지만 수십만 row 이상의 테이블에서 ALL이 나오면 인덱스 설계를 다시 봐야 한다.

Extra 컬럼에서 주목할 것

Using filesort와 Using temporary가 함께 나오고 대용량 쿼리라면 성능 영향이 크다.

EXPLAIN ANALYZE: 실제로 실행하고 측정한다

EXPLAIN은 추정 비용이다. 추정이 틀릴 수 있다. EXPLAIN ANALYZE(MySQL 8.0.18+)는 쿼리를 실제로 실행하고 각 단계의 실측 비용을 보고한다.

EXPLAIN ANALYZE
SELECT c.name, c.version, v.severity
FROM component c
JOIN vulnerability v ON v.component_id = c.id
WHERE c.ecosystem = 'npm'
  AND v.severity = 'HIGH'
ORDER BY v.cvss_score DESC
LIMIT 20;

출력 형식은 트리 형태(TREE)다.

-> Limit: 20 row(s)  (actual time=8.21..8.22 rows=20 loops=1)
    -> Sort: v.cvss_score DESC  (actual time=8.18..8.19 rows=20 loops=1)
        -> Filter: (v.severity = 'HIGH')  (actual time=6.30..7.91 rows=142 loops=1)
            -> Nested loop inner join  (actual time=0.51..7.44 rows=621 loops=1)
                -> Index range scan on c (idx_ecosystem)  (actual time=0.22..1.89 rows=514 loops=1)
                -> Index lookup on v (component_id)  (actual time=0.01..0.01 rows=1 loops=514)

읽는 방향은 안에서 밖으로, 아래에서 위로다. 가장 안쪽이 먼저 실행된다.

각 노드의 숫자를 읽는 방법은 다음과 같다.

actual time=A..B rows=R loops=L

• A: 첫 번째 row를 내보내기까지 걸린 평균 시간(ms)
• B: 마지막 row까지의 평균 시간(ms)
• R: 실제로 반환한 row 수
• L: 이 노드가 실행된 횟수. 조인이면 outer loop 수와 같다.

주의: loops가 여러 개라면 actual time은 loop당 평균이다. 총 비용은 B × L에 비례한다.

추정치와 실측치 비교가 핵심이다

EXPLAIN과 EXPLAIN ANALYZE를 나란히 보면 어디서 추정이 틀렸는지 알 수 있다.

-- EXPLAIN: 추정
EXPLAIN SELECT ...;
-- rows 컬럼이 추정 row 수

-- EXPLAIN ANALYZE: 실측
EXPLAIN ANALYZE SELECT ...;
-- actual rows가 실제 row 수

추정 rows가 100인데 실측 rows가 50,000이라면 통계 정보가 낡았거나, 인덱스 선택도가 실제 데이터 분포와 다른 것이다.

ANALYZE TABLE component;  -- 통계 갱신

옵티마이저 힌트

통계가 정확해도 옵티마이저가 항상 최선을 선택하지는 않는다. 이럴 때 옵티마이저 힌트로 개입할 수 있다.

-- 특정 인덱스 강제 사용
SELECT /*+ INDEX(c idx_ecosystem) */ c.name, c.version
FROM component c
WHERE c.ecosystem = 'npm';

-- 특정 인덱스 금지
SELECT /*+ NO_INDEX(c idx_name) */ *
FROM component c
WHERE c.name = 'lodash';

-- 조인 순서 고정
SELECT /*+ JOIN_ORDER(v, c) */ *
FROM vulnerability v
JOIN component c ON c.id = v.component_id;

-- 세미조인 전략 조정
SELECT /*+ NO_SEMIJOIN(@subq FIRSTMATCH) */ *
FROM orders
WHERE product_id IN (SELECT id FROM product WHERE category = 'X');

힌트는 주석 형태(/*+ ... */)로 작성한다. FORCE INDEX보다 힌트를 권장하는 이유는 힌트가 더 세밀하고 범위를 제한할 수 있기 때문이다.

힌트를 사용하기 전에 먼저 확인할 것이 있다.

1. 통계 갱신 후 재시도했는가?
2. 인덱스 설계가 맞는가?
3. 쿼리 구조 자체를 바꿀 수 없는가?

힌트는 마지막 수단이다. 코드에 힌트가 많아지면 유지 관리가 어렵고, 데이터 패턴이 바뀌면 오히려 역효과가 난다.

흔한 느린 쿼리 패턴

1. filesort + large result

-- 인덱스 없이 ORDER BY → filesort
SELECT * FROM scan_result ORDER BY created_at DESC LIMIT 50;

-- 해결: created_at 인덱스 추가 또는 (status, created_at) 복합 인덱스

2. Using temporary on GROUP BY

-- GROUP BY 컬럼에 인덱스 없음 → 임시 테이블
SELECT ecosystem, COUNT(*) FROM component GROUP BY ecosystem;

-- 해결: ecosystem 인덱스 추가 또는 쿼리 분리

3. IN subquery cardinality 오추정

-- 옵티마이저가 subquery row 수를 과소/과대 추정하는 경우
SELECT * FROM orders
WHERE product_id IN (SELECT id FROM product WHERE active = 1);

-- EXPLAIN ANALYZE로 actual rows 확인 후 JOIN으로 변환 검토
SELECT o.*
FROM orders o
JOIN product p ON p.id = o.product_id AND p.active = 1;

4. Nested loop 과다 loops

-- outer가 100만 rows, inner per row = 1이면 loops=100만
-- EXPLAIN ANALYZE에서 loops 값 확인
-- 해결: LIMIT/WHERE로 outer를 먼저 줄이거나 인덱스 보완

5. 함수 적용으로 인덱스 미사용

-- DATE() 함수로 created_at 인덱스 사용 불가
WHERE DATE(created_at) = '2026-06-15'

-- 해결: 범위 조건으로 변환
WHERE created_at >= '2026-06-15 00:00:00'
  AND created_at <  '2026-06-16 00:00:00'

실행계획 디버깅 워크플로

1. 느린 쿼리를 slow_query_log에서 확보한다.
2. EXPLAIN으로 전체 계획 구조를 파악한다.
3. type=ALL이 있거나 Extra에 Using filesort/temporary가 있으면 표시한다.
4. EXPLAIN ANALYZE로 실측 비용을 확인한다. 추정치와 크게 다른 단계를 찾는다.
5. 그 단계에서 인덱스, 통계, 쿼리 구조를 순서대로 검토한다.
6. 변경 후 다시 EXPLAIN ANALYZE로 비교한다.

-- slow query log 활성화 확인
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- 실행계획 비교: 변경 전/후 actual time 기록

한 줄 정리

실행계획 튜닝은 추정과 실측의 차이를 찾는 일이다. EXPLAIN으로 계획을 확인하고, EXPLAIN ANALYZE로 실제 비용을 측정하고, 가장 비싼 단계부터 인덱스·통계·쿼리 순서로 개선한다.

References

• https://dev.mysql.com/doc/refman/8.4/en/execution-plan-information.html
• https://dev.mysql.com/doc/refman/8.4/en/explain.html
• https://dev.mysql.com/doc/refman/8.4/en/optimizer-hints.html
• https://dev.mysql.com/blog-archive/mysql-explain-analyze/
• https://minervadb.com/mysql-execution-plans-explained/
• https://dev.mysql.com/doc/refman/8.4/en/explain-output.html