[MySQL] EXPLAIN ANALYZE 카디널리티 오차 진단과 실행 계획 튜닝 순서

728x90

EXPLAIN ANALYZE: 실무적 정의와 운영상 중요성

MySQL에서 EXPLAIN은 옵티마이저가 쿼리를 실행하기 전에 계산한 예상 실행 계획을 반환한다. 반면 EXPLAIN ANALYZE는 쿼리를 실제로 실행한 뒤 각 iterator의 소요 시간, 반환 행 수, 반복 횟수를 예상값과 함께 표시한다.

추정 행 수와 실제 처리 행 수가 다를 때 가장 먼저 확인할 항목은 전체 실행 시간이 아니다. 실행 계획의 하위 노드부터 estimated rows와 actual rows가 처음 크게 벌어지는 지점을 찾아야 한다. 상위 조인 노드의 오차는 하위 테이블 접근 단계에서 발생한 카디널리티 오류가 누적된 결과일 가능성이 높다.

카디널리티 추정이 틀리면 옵티마이저는 잘못된 인덱스, 조인 순서, Nested Loop 반복 횟수를 선택할 수 있다. 운영 환경에서는 이 오차가 불필요한 랜덤 I/O, 버퍼 풀 오염, CPU 사용량 증가로 이어지며 동시 실행 세션이 많을수록 장애 위험이 커진다.

Part 1. EXPLAIN ANALYZE 기본 문법 및 사용법 (Basic Syntax)

진단 대상은 동일한 SQL과 동일한 바인딩 값이어야 한다. 일반 EXPLAIN으로 예상 계획을 보관한 다음 EXPLAIN ANALYZE를 실행하면 예상값과 실제값의 차이를 비교할 수 있다.

EXPLAIN
SELECT o.order_id, o.customer_id
FROM orders AS o
JOIN customers AS c
  ON c.customer_id = o.customer_id
WHERE o.status = 'PAID'
  AND o.created_at >= '2026-08-01'
  AND o.created_at <  '2026-09-01';

EXPLAIN ANALYZE
SELECT o.order_id, o.customer_id
FROM orders AS o
JOIN customers AS c
  ON c.customer_id = o.customer_id
WHERE o.status = 'PAID'
  AND o.created_at >= '2026-08-01'
  AND o.created_at <  '2026-09-01';

EXPLAIN ANALYZE는 SELECT 결과를 단순히 예측하지 않고 쿼리를 실제 실행한다. 따라서 대용량 조회, 잠금 경합 가능성이 있는 문장, 자원 사용량을 알 수 없는 쿼리는 운영 서버에서 즉시 실행하면 안 된다. 읽기 전용 복제본이나 재현 가능한 검증 환경에서 먼저 수행해야 한다.

항목	의미	우선 확인할 내용
cost	옵티마이저가 계산한 상대적 예상 비용	실제 밀리초와 직접 비교하지 않는다.
rows	해당 iterator가 반환할 것으로 추정한 행 수	actual rows와 동일한 반복 기준으로 비교한다.
actual time=a..b	첫 행 반환까지의 평균 시간과 완료까지의 평균 시간	a가 크면 첫 행 생성 전 작업, b가 크면 전체 처리량을 확인한다.
rows=n	한 번의 loop에서 반환한 평균 실제 행 수	필터 선택도와 추정 행 수의 오차를 확인한다.
loops=n	해당 iterator가 실행된 횟수	Nested Loop 내부 노드의 반복 증폭을 확인한다.
never executed	상위 조건에 의해 해당 노드가 실행되지 않음	실행되지 않은 노드의 성능을 현재 결과만으로 판단하지 않는다.

출력된 actual time은 각 iterator의 누적 벽시계 시간과 완전히 동일한 개념이 아니다. 부모 노드와 자식 노드의 시간이 겹치므로 모든 노드의 시간을 더해 전체 실행 시간을 계산하면 중복 집계가 발생한다.

추정·실제 행 수 오차율 계산

오차는 배수와 백분율을 함께 기록하는 것이 유용하다. 추정 행 수가 0이 아니면 실제 행 수를 추정 행 수로 나누어 오차 배수를 계산한다. 백분율 오차는 절댓값을 이용한다.

오차 배수 = 실제 행 수 / 추정 행 수
절대 오차율(%) = |실제 행 수 - 추정 행 수| / 추정 행 수 × 100

loops가 1보다 큰 iterator는 비교 기준을 통일해야 한다. 출력된 실제 rows가 loop당 평균이라면 추정 rows와 loop당 기준으로 비교한다. 전체 처리량을 분석할 때는 actual rows와 loops를 곱한 값을 별도로 계산한다. 한쪽은 loop당 행 수이고 다른 쪽은 전체 행 수인 상태로 비교하면 오차를 잘못 판단하게 된다.

추정값이 0이면 나눗셈 기반 오차율을 계산할 수 없다. 이 경우에는 ‘추정 0, 실제 N’으로 기록하고 별도 결함으로 분류한다. 실제값이 0인 조건은 데이터가 존재하는 다른 바인딩 값에서도 계획을 재검증해야 한다.

Part 2. 내부 동작 메커니즘과 안티 패턴

MySQL 옵티마이저는 테이블과 인덱스 통계, 조건식의 선택도, 조인 관계를 이용해 각 단계의 행 수를 추정한다. 실제 데이터를 모두 읽어 계획을 결정하는 것이 아니므로 오래된 통계, 데이터 편향, 컬럼 간 상관관계가 존재하면 추정값이 실제값과 달라질 수 있다.

진단 순서는 실행 계획 트리의 가장 안쪽 테이블 접근 노드에서 시작한다. Index Lookup 또는 Table Scan의 추정·실제 행 수를 비교하고, 그다음 Filter의 통과 행 수, Nested Loop의 반복 횟수, 최종 집계와 정렬 노드로 이동한다. 최초 오차 노드가 원인이고 이후 노드는 전파된 결과인 경우가 많다.

관찰 결과	가능성이 높은 원인	다음 확인 항목
Scan 단계부터 실제 행 수가 훨씬 많음	통계 노후화, 데이터 편향, 조건 선택도 오판	테이블 통계, 인덱스 분포, 히스토그램
Scan은 유사하지만 Filter 이후 오차가 큼	조건식 결합 또는 컬럼 상관관계	각 조건의 단독·결합 건수
외부 입력은 정확하지만 내부 lookup의 loops가 큼	비효율적인 조인 순서 또는 조인 키 인덱스 부족	조인 순서, 내부 테이블 인덱스
행 수는 유사하지만 actual time이 큼	랜덤 I/O, 넓은 행, 캐시 상태, 정렬·임시 테이블	접근 페이지, 반환 컬럼, 정렬 및 임시 처리
특정 조건값에서만 오차가 큼	값 분포 편향 또는 parameter-sensitive 특성	빈도가 높은 값과 낮은 값의 계획 비교

가장 흔한 안티 패턴 1: 최상위 노드만 확인

최종 SELECT 노드에서 추정값과 실제값이 크게 다르다는 사실만 확인해서는 원인을 찾을 수 없다. 여러 Filter와 Join을 통과하는 동안 오차가 곱해질 수 있기 때문이다. DBA는 하위 노드에서 최초로 오차가 확대되는 위치를 식별해야 한다.

가장 흔한 안티 패턴 2: actual time만 보고 통계 오류로 단정

실행 시간이 길다고 해서 카디널리티 추정이 반드시 틀린 것은 아니다. 예상 행 수와 실제 행 수가 유사한데 시간이 길다면 스토리지 지연, 버퍼 풀 적중 상태, 넓은 레코드, 과도한 loops를 구분해야 한다. 행 수 오차와 물리적 실행 비용은 별도의 축으로 진단한다.

가장 치명적인 안티 패턴: 컬럼 가공으로 인덱스 탐색 차단

-- 안티 패턴
WHERE DATE_FORMAT(o.created_at, '%Y-%m') = '2026-08'

-- 범위 조건으로 변경
WHERE o.created_at >= '2026-08-01'
  AND o.created_at <  '2026-09-01'

위 안티 패턴은 created_at 컬럼을 행마다 가공한다. 이로 인해 일반적인 B-Tree 인덱스의 범위 탐색이 제한되고 Full Table Scan과 CPU 사용량 증가를 유발할 수 있다. 함수 기반 날짜 조건의 상세한 원리는 MySQL DATE_FORMAT이 유발하는 Full Table Scan과 쿼리 튜닝 가이드에서 확인할 수 있다.

문자열 날짜 변환도 같은 관점에서 검토해야 한다. 조회 컬럼에 STR_TO_DATE를 반복 적용하면 선택도 추정뿐 아니라 인덱스 접근에도 영향을 준다. 관련 사례는 STR_TO_DATE 오남용과 CPU 병목 분석에 정리되어 있다.

Part 3. 성능 최적화 및 튜닝 전략

1단계: 비교 조건을 고정한다

동일 쿼리라도 데이터 변경, 바인딩 값, 버퍼 풀 상태, 동시 부하에 따라 실제 시간이 달라진다. SQL 텍스트, 조건값, 대상 데이터 시점, 서버와 세션 설정을 함께 기록해야 한다. 실행 시간만 한 번 측정해서 전후 성능을 단정하면 안 된다.

먼저 일반 EXPLAIN과 EXPLAIN ANALYZE 원문을 보존한다. 이후 각 노드에 대해 estimated rows, actual rows, loops, loop당 오차 배수, 전체 처리 행 수를 표로 정리한다. 실제 결과를 확보하면 다음 순서로 기록한다.

1. 동일 쿼리의 EXPLAIN 원문을 수정 없이 보존한다.
2. 동일 쿼리와 바인딩 값으로 수집한 EXPLAIN ANALYZE 원문을 보존한다.
3. 하위 노드부터 비교해 최초 오차 노드를 찾고 estimated rows, actual rows, loops를 기록한다.
4. 오차 배수와 절대 오차율을 위 공식으로 계산한다.

2단계: 통계 갱신 필요성을 검증한다

대량 INSERT, DELETE, 데이터 적재, 파티션 단위 변경 이후에는 통계가 현재 분포를 충분히 반영하는지 확인한다. 통계 갱신이 필요하다고 판단되면 영향 범위를 검토한 뒤 대상 테이블에 ANALYZE TABLE을 수행한다.

ANALYZE TABLE orders, customers;

ANALYZE TABLE은 단순한 성능 개선 명령이 아니다. 통계가 바뀌면 기존에 정상적이던 다른 SQL의 실행 계획도 변경될 수 있다. 운영 환경에서는 대상 테이블, 실행 시점, 관련 핵심 쿼리의 계획 변화를 함께 관리해야 한다.

갱신 후에는 같은 조건으로 EXPLAIN과 EXPLAIN ANALYZE를 다시 수집한다. 카디널리티 오차가 줄었는지 먼저 확인하고 실행 시간 변화는 그다음 평가한다. 실제 측정값은 아래 비교표의 판정 절차에 따라 평가하는 것이 적절하다.

구분	통계 갱신 전 확인	통계 갱신 후 확인	판정
최초 오차 노드	하위 노드부터 최초 오차가 발생한 iterator를 식별한다.	같은 순서로 최초 오차 iterator를 다시 식별한다.	노드 변경 여부
추정 행 수	해당 노드의 estimated rows를 기록한다.	동일 노드의 estimated rows를 다시 기록한다.	실제값 접근 여부
실제 행 수와 loops	actual rows와 loops를 같은 기준으로 기록한다.	동일한 SQL, 바인딩 값과 데이터 시점에서 다시 기록한다.	동일 조건 여부
오차 배수·오차율	오차 배수와 절대 오차율을 계산한다.	같은 공식과 loop당 기준으로 다시 계산한다.	오차 축소 여부
전체 실행 시간	동일 조건에서 반복 측정한 결과의 범위를 기록한다.	동일한 횟수와 조건으로 반복 측정한 결과의 범위를 기록한다.	반복 측정 범위 비교

3단계: 데이터 편향과 조건 상관관계를 확인한다

통계를 갱신해도 오차가 유지되면 데이터 분포를 확인한다. status처럼 일부 값에 데이터가 집중된 컬럼은 균등 분포 가정과 실제 분포가 크게 다를 수 있다. 조건값별 GROUP BY 집계로 빈도를 확인하되 대용량 테이블에서는 운영 부하를 고려해 복제본 또는 적절한 표본 환경을 사용한다.

SELECT status, COUNT(*) AS row_count
FROM orders
GROUP BY status;

SELECT status, COUNT(*) AS row_count
FROM orders
WHERE created_at >= '2026-08-01'
  AND created_at <  '2026-09-01'
GROUP BY status;

첫 번째 쿼리는 전체 status 분포를 보여주고 두 번째 쿼리는 날짜 조건과 결합된 분포를 보여준다. 각각의 단독 분포는 단순하지만 두 컬럼이 강하게 연관되면 결합 조건의 실제 선택도가 달라질 수 있다. 이 경우 개별 컬럼 통계만으로 정확한 추정이 어려울 수 있다.

적합한 컬럼에는 히스토그램 활용 여부를 검토할 수 있다. 다만 히스토그램 생성은 실제 분포, 기존 인덱스, 쿼리 패턴과 MySQL 버전을 확인한 뒤 결정해야 한다. 무조건 생성하거나 bucket 수를 관행적으로 고정하면 관리 비용만 증가할 수 있다.

4단계: 인덱스와 조인 순서를 실행 계획으로 검증한다

선택도가 높은 조건과 조인 키를 기준으로 후보 인덱스를 설계한다. 예제 쿼리에서는 status와 created_at의 조건 방식, customer_id 조인, 필요한 반환 컬럼을 함께 검토한다. 컬럼 순서는 실제 분포와 쿼리 패턴을 근거로 결정하며 하나의 예제만 보고 복합 인덱스를 추가하면 안 된다.

인덱스 추가 전후에는 접근 방식, 사용 인덱스, 추정 행 수, 실제 행 수, loops가 어떻게 바뀌는지 확인한다. 불필요한 인덱스는 쓰기 증폭, 버퍼 풀 점유, 통계 관리 비용을 유발한다. 성능 개선 여부뿐 아니라 INSERT와 UPDATE 비용도 함께 평가해야 한다.

실무 진단 우선순위

순서	확인 항목	판정 기준	대응
1	하위 접근 노드의 estimated rows와 actual rows	최초로 오차가 확대되는가	통계와 조건 선택도 확인
2	loops	내부 노드 실행이 과도하게 반복되는가	조인 순서와 조인 키 인덱스 확인
3	Filter 전후 행 수	읽은 행 대부분을 사후 제거하는가	SARGable 조건과 인덱스 재설계
4	actual time의 첫 행·완료 시간	초기 준비와 전체 처리 중 어디가 느린가	정렬, 집계, 임시 처리와 I/O 확인
5	통계 갱신 전후 계획	오차와 계획이 개선되는가	효과가 없으면 편향·상관관계 분석

결론적으로 EXPLAIN의 추정 행 수와 실제 처리 행 수가 다를 때는 하위 실행 노드의 rows와 actual rows부터 확인해야 한다. 이어서 loops를 반영해 비교 기준을 맞추고, 최초 오차 노드의 통계 노후화, 데이터 편향, 조건식 상관관계, 인덱스 적합성을 순서대로 검증한다.

성능 최적화의 핵심은 최상위 실행 시간만 보는 것이 아니라 카디널리티 오차가 시작된 지점을 제거하는 것이다. 컬럼 가공을 회피하고, 범위 검색과 인덱스를 활용하며, 통계 갱신 전후의 실행 계획과 실제 처리량을 같은 조건에서 확인해야 한다.