EXPLAIN ANALYZE 해석: 추정 rows와 실제 rows의 차이를 보는 법

1. 왜 EXPLAIN ANALYZE가 운영에서 중요한가

EXPLAIN은 MySQL optimizer가 선택한 실행 계획을 보여준다. 그러나 전통적인 EXPLAIN만으로는 “이 계획이 실제로 얼마나 잘 맞았는가”를 충분히 판단하기 어렵다. rows는 실제 읽은 행 수가 아니라 optimizer가 통계와 비용 모델을 바탕으로 추정한 값이며, filtered도 조건 통과 비율에 대한 추정이다. 통계가 낡았거나 데이터 분포가 한쪽으로 치우쳐 있거나, 컬럼 사이의 상관관계가 큰 경우에는 추정값과 실제 실행이 크게 어긋날 수 있다.

EXPLAIN ANALYZE는 이 간격을 좁히기 위한 도구다. MySQL 8.0.18 이상에서 사용할 수 있으며, 쿼리를 실제로 실행한 뒤 iterator 단위의 실제 시간, 실제 행 수, 반복 횟수를 함께 보여준다. 운영 DBA와 개발자는 이를 통해 다음 질문에 답할 수 있다.

optimizer가 예상한 행 수와 실제 행 수가 얼마나 다른가?
느린 지점은 테이블 접근, 인덱스 탐색, 정렬, 조인, 집계 중 어디인가?
잘못된 실행 계획이 통계 문제인지, 인덱스 부재인지, 쿼리 조건의 선택도 문제인지 구분할 수 있는가?
단순히 “인덱스를 추가하자”가 아니라 어떤 조건과 조인 순서가 비용 모델을 속였는지 설명할 수 있는가?

주의할 점도 있다. EXPLAIN ANALYZE는 쿼리를 실제로 실행한다. SELECT도 버퍼 풀과 디스크 I/O를 사용하며, 잠금을 기다릴 수 있고, 비싼 정렬·임시 테이블을 만들 수 있다. 운영 환경에서는 읽기 부하가 큰 쿼리에 무심코 적용하지 말고, 먼저 replica, 스테이징, 작은 범위 조건, LIMIT, 세션별 리소스 한도 등을 검토해야 한다.

2. 실행 계획에서 “추정”과 “실제”가 만들어지는 경로

MySQL optimizer는 SQL을 받으면 파싱, 의미 분석, 쿼리 변환, 후보 실행 계획 생성, 비용 평가를 거쳐 하나의 계획을 선택한다. 이때 EXPLAIN의 rows는 InnoDB가 실제로 행을 읽어 센 값이 아니라 다음 자료를 조합한 추정치다.

테이블과 인덱스의 cardinality 통계
조건절의 선택도 추정
조인 순서별 중간 결과 크기 추정
인덱스 range scan, ref lookup, table scan, sort, temporary table 비용
histogram이 있는 컬럼의 값 분포 정보

반면 EXPLAIN ANALYZE는 선택된 계획을 실제 실행하면서 iterator별 실제 값을 붙인다. MySQL의 tree 형식 출력에서는 보통 다음 표현을 함께 보게 된다.

(cost=... rows=...) (actual time=... rows=... loops=...)

여기서 앞쪽 rows는 optimizer 추정 행 수이고, 뒤쪽 actual ... rows는 해당 iterator가 실제로 반환한 평균 또는 누적 행 수의 관측값이다. loops는 그 iterator가 몇 번 반복 호출되었는지 보여준다. nested loop join에서는 안쪽 테이블 접근이 바깥쪽 행 수만큼 반복될 수 있으므로, actual rows만 보지 말고 loops와 함께 해석해야 한다.

flowchart TD
    A[SQL 수신] --> B[파싱과 의미 분석]
    B --> C[조건 변환과 후보 계획 생성]
    C --> D[통계·비용 기반 rows 추정]
    D --> E[실행 계획 선택]
    E --> F[EXPLAIN: 추정 계획만 표시]
    E --> G[EXPLAIN ANALYZE: 실제 실행]
    G --> H[actual time / actual rows / loops 표시]
    H --> I[추정 오차와 병목 해석]

운영 해석의 핵심은 “실제 rows가 많다/적다” 자체가 아니라 “optimizer가 그 정도 규모를 예상했는가”이다. 실제로 100만 행을 읽어도 optimizer가 100만 행을 예상했고 그것이 최선의 계획이라면 문제의 성격은 인덱스 설계나 쿼리 요구사항일 수 있다. 반대로 실제 10행만 반환하는데 optimizer가 수십만 행을 예상해 조인 순서를 바꿨다면 통계와 선택도 추정이 문제일 가능성이 높다.

3. 재현 예제: 추정 rows와 실제 rows를 나란히 보기

아래 예제는 작은 테이블을 만들고 EXPLAIN FORMAT=TREE와 EXPLAIN ANALYZE를 비교한다. 테스트 컨테이너에서 검증 가능한 축소 예제이며, 실제 운영 데이터의 분포와 비용은 다를 수 있다.

DROP TABLE IF EXISTS explain_analyze_demo;

CREATE TABLE explain_analyze_demo (
  id INT NOT NULL AUTO_INCREMENT,
  customer_id INT NOT NULL,
  status VARCHAR(16) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_status_created (status, created_at),
  KEY ix_customer (customer_id)
) ENGINE=InnoDB;

INSERT INTO explain_analyze_demo (customer_id, status, amount, created_at)
SELECT n,
       CASE WHEN MOD(n, 10) = 0 THEN 'CANCELLED' ELSE 'PAID' END,
       10 + MOD(n, 37),
       TIMESTAMP('2026-01-01') + INTERVAL n HOUR
FROM (
  SELECT ones.n + tens.n * 10 + 1 AS n
  FROM (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
        UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS ones
  CROSS JOIN (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
              UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS tens
) AS seq;

ANALYZE TABLE explain_analyze_demo;

EXPLAIN FORMAT=TREE
SELECT customer_id, amount
FROM explain_analyze_demo
WHERE status = 'CANCELLED'
  AND created_at >= '2026-01-01'
ORDER BY created_at;

EXPLAIN ANALYZE
SELECT customer_id, amount
FROM explain_analyze_demo
WHERE status = 'CANCELLED'
  AND created_at >= '2026-01-01'
ORDER BY created_at;

DROP TABLE explain_analyze_demo;

실행 결과(MySQL 8.0.x):

mysql> CREATE TABLE explain_analyze_demo (...);
Query OK, 0 rows affected

mysql> INSERT INTO explain_analyze_demo (...)
    -> SELECT ...;
Query OK, 100 rows affected
Records: 100  Duplicates: 0  Warnings: 0

mysql> ANALYZE TABLE explain_analyze_demo;
+--------------------------------------+---------+----------+----------+
| Table                                | Op      | Msg_type | Msg_text |
+--------------------------------------+---------+----------+----------+
| mysql_tech_note.explain_analyze_demo | analyze | status   | OK       |
+--------------------------------------+---------+----------+----------+

mysql> EXPLAIN FORMAT=TREE SELECT ...;
+---------------------------------------------------------------------+
| EXPLAIN                                                             |
+---------------------------------------------------------------------+
| -> Index range scan on explain_analyze_demo using ix_status_created
|    over (status = 'CANCELLED' AND '2026-01-01 00:00:00' <= created_at)
|    (cost=4.76 rows=10)                                              |
+---------------------------------------------------------------------+

mysql> EXPLAIN ANALYZE SELECT ...;
+---------------------------------------------------------------------+
| EXPLAIN                                                             |
+---------------------------------------------------------------------+
| -> Index range scan on explain_analyze_demo using ix_status_created
|    over (status = 'CANCELLED' AND '2026-01-01 00:00:00' <= created_at)
|    (cost=4.76 rows=10)
|    (actual time=0.0247..0.0275 rows=10 loops=1)                     |
+---------------------------------------------------------------------+

위 출력에서 EXPLAIN FORMAT=TREE는 실행하지 않은 추정 계획을 보여준다. EXPLAIN ANALYZE는 같은 계획을 실행하고, 실제 반환 행 수와 시간을 붙인다. 이 예제에서는 100행 중 10행이 CANCELLED이므로, 인덱스 ix_status_created를 사용하는 range scan의 추정 rows와 실제 rows가 대체로 같은 방향을 가리켜야 한다.

4. rows 차이를 읽는 방법

EXPLAIN ANALYZE를 볼 때는 한 줄씩 독립적으로 읽기보다 부모·자식 iterator 관계를 따라 읽어야 한다. 특히 다음 세 값의 조합이 중요하다.

항목	의미	운영 해석
`cost=... rows=...`	optimizer가 계획 선택 전에 예상한 비용과 행 수	통계·선택도·비용 모델의 판단
`actual time=a..b`	첫 행 반환 시점과 마지막 행 반환 시점의 실제 시간	latency가 어디서 발생했는지 추적
`actual rows=n loops=m`	iterator가 실제 반환한 행 수와 반복 횟수	조인 내부 반복, 중간 결과 증폭 여부 확인

실무에서는 다음 순서로 해석하면 실수를 줄일 수 있다.

가장 아래쪽 테이블 접근부터 본다. table scan, index range scan, ref lookup 중 무엇이 실제로 실행되었는지 확인한다.
rows 추정과 actual rows가 10배, 100배 이상 차이 나는 지점을 찾는다.
그 지점이 조인 안쪽이면 loops를 곱한 총 작업량을 생각한다.
정렬, materialize, temporary table, aggregate가 큰 actual time을 보이는지 확인한다.
추정 오차가 발생한 컬럼에 통계 갱신, histogram, 복합 인덱스, 조건 재작성 중 무엇이 적합한지 판단한다.

예를 들어 바깥쪽 iterator가 실제 50,000행을 반환했는데 optimizer가 50행으로 추정했다면, 그 안쪽 ref lookup은 예상보다 1,000배 더 반복될 수 있다. 이 경우 느린 지점은 안쪽 테이블처럼 보이지만, 원인은 바깥쪽 조건의 선택도 오판일 수 있다.

5. 추정 오차가 발생하는 대표 원인

5.1 통계가 오래되었거나 표본이 부족한 경우

InnoDB 통계는 실제 데이터 전체를 매번 완전 스캔해서 만들지 않는다. 데이터 변경이 많거나 분포가 급격히 바뀌면 cardinality 추정이 현실과 어긋날 수 있다. 이런 경우 ANALYZE TABLE이 첫 번째 점검 수단이다.

DROP TABLE IF EXISTS stats_check_demo;

CREATE TABLE stats_check_demo (
  id INT NOT NULL AUTO_INCREMENT,
  category VARCHAR(20) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_category_created (category, created_at)
) ENGINE=InnoDB;

INSERT INTO stats_check_demo (category, created_at)
VALUES ('hot', '2026-01-01'), ('hot', '2026-01-02'), ('cold', '2026-01-03');

ANALYZE TABLE stats_check_demo;

SHOW INDEX FROM stats_check_demo WHERE Key_name = 'ix_category_created';

DROP TABLE stats_check_demo;

실행 결과(MySQL 8.0.x):

mysql> CREATE TABLE stats_check_demo (...);
Query OK, 0 rows affected

mysql> INSERT INTO stats_check_demo (category, created_at)
    -> VALUES ('hot', '2026-01-01'), ('hot', '2026-01-02'), ('cold', '2026-01-03');
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

mysql> ANALYZE TABLE stats_check_demo;
+----------------------------------+---------+----------+----------+
| Table                            | Op      | Msg_type | Msg_text |
+----------------------------------+---------+----------+----------+
| mysql_tech_note.stats_check_demo | analyze | status   | OK       |
+----------------------------------+---------+----------+----------+

mysql> SHOW INDEX FROM stats_check_demo WHERE Key_name = 'ix_category_created';
+------------------+------------+---------------------+--------------+-------------+-------------+
| Table            | Non_unique | Key_name            | Seq_in_index | Column_name | Cardinality |
+------------------+------------+---------------------+--------------+-------------+-------------+
| stats_check_demo |          1 | ix_category_created |            1 | category    |           2 |
| stats_check_demo |          1 | ix_category_created |            2 | created_at  |           3 |
+------------------+------------+---------------------+--------------+-------------+-------------+
2 rows in set

SHOW INDEX의 Cardinality는 정확한 distinct count가 아니라 통계 기반 추정치다. 작은 예제에서는 값이 직관과 다르게 보일 수 있으며, 큰 운영 테이블에서는 sampling과 데이터 분포의 영향을 받는다. 따라서 이 값을 절대적인 진실로 보지 말고, optimizer가 어떤 정보를 근거로 판단하는지 보는 단서로 사용해야 한다.

5.2 데이터 분포가 균등하지 않은 경우

status, type, region처럼 값 종류는 적지만 특정 값에 데이터가 몰리는 컬럼은 추정 오차를 만들기 쉽다. 예를 들어 status='PAID'가 전체의 95%이고 status='CANCELLED'가 1%라면, 같은 인덱스를 사용해도 조건값에 따라 최적 계획이 달라질 수 있다. MySQL 8.0의 histogram은 이런 단일 컬럼 분포를 optimizer에게 알려주는 데 도움이 될 수 있다.

DROP TABLE IF EXISTS histogram_demo;

CREATE TABLE histogram_demo (
  id INT NOT NULL AUTO_INCREMENT,
  status VARCHAR(16) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_created (created_at)
) ENGINE=InnoDB;

INSERT INTO histogram_demo (status, created_at)
SELECT CASE WHEN n <= 90 THEN 'PAID' ELSE 'CANCELLED' END,
       TIMESTAMP('2026-01-01') + INTERVAL n MINUTE
FROM (
  SELECT ones.n + tens.n * 10 + 1 AS n
  FROM (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
        UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS ones
  CROSS JOIN (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
              UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS tens
) AS seq;

ANALYZE TABLE histogram_demo UPDATE HISTOGRAM ON status WITH 2 BUCKETS;

SELECT SCHEMA_NAME,
       TABLE_NAME,
       COLUMN_NAME,
       JSON_UNQUOTE(JSON_EXTRACT(HISTOGRAM, '$."histogram-type"')) AS histogram_type
FROM information_schema.COLUMN_STATISTICS
WHERE SCHEMA_NAME = DATABASE()
  AND TABLE_NAME = 'histogram_demo'
  AND COLUMN_NAME = 'status';

ANALYZE TABLE histogram_demo DROP HISTOGRAM ON status;
DROP TABLE histogram_demo;

실행 결과(MySQL 8.0.x):

mysql> CREATE TABLE histogram_demo (...);
Query OK, 0 rows affected

mysql> INSERT INTO histogram_demo (...)
    -> SELECT ...;
Query OK, 100 rows affected
Records: 100  Duplicates: 0  Warnings: 0

mysql> ANALYZE TABLE histogram_demo UPDATE HISTOGRAM ON status WITH 2 BUCKETS;
+--------------------------------+-----------+----------+---------------------------------------------------+
| Table                          | Op        | Msg_type | Msg_text                                          |
+--------------------------------+-----------+----------+---------------------------------------------------+
| mysql_tech_note.histogram_demo | histogram | status   | Histogram statistics created for column 'status'. |
+--------------------------------+-----------+----------+---------------------------------------------------+

mysql> SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, ... FROM information_schema.COLUMN_STATISTICS ...;
+-----------------+----------------+-------------+----------------+
| SCHEMA_NAME     | TABLE_NAME     | COLUMN_NAME | histogram_type |
+-----------------+----------------+-------------+----------------+
| mysql_tech_note | histogram_demo | status      | singleton      |
+-----------------+----------------+-------------+----------------+
1 row in set

mysql> ANALYZE TABLE histogram_demo DROP HISTOGRAM ON status;
+--------------------------------+-----------+----------+---------------------------------------------------+
| Table                          | Op        | Msg_type | Msg_text                                          |
+--------------------------------+-----------+----------+---------------------------------------------------+
| mysql_tech_note.histogram_demo | histogram | status   | Histogram statistics removed for column 'status'. |
+--------------------------------+-----------+----------+---------------------------------------------------+

Histogram은 모든 문제의 해답이 아니다. 복합 조건에서 컬럼 사이의 상관관계가 강한 경우, 단일 컬럼 histogram만으로는 부족할 수 있다. 또한 histogram 생성과 유지 대상은 신중히 골라야 한다. 너무 많은 컬럼에 무분별하게 적용하면 운영 복잡도만 늘고, 실제 계획 안정성에는 도움이 되지 않을 수 있다.

5.3 복합 인덱스의 선두 컬럼과 조건 형태가 맞지 않는 경우

복합 인덱스는 왼쪽부터 정렬된 구조다. KEY (status, created_at)가 있을 때 status='CANCELLED' AND created_at >= ... 조건은 range scan에 적합하지만, created_at >= ...만 있는 쿼리는 같은 방식으로 효율적으로 좁히기 어렵다. 추정 rows가 많게 나오거나 실제로 많은 leaf page를 훑는다면 인덱스 컬럼 순서를 다시 검토해야 한다.

이 판단은 단순히 “인덱스를 더 만들자”로 끝나면 안 된다. 쓰기 비용, 버퍼 풀 점유, 변경 버퍼, 복제 지연, 백업 크기, 온라인 DDL 시간까지 함께 고려해야 한다. 특히 Aurora MySQL에서는 스토리지 계층이 다르더라도 보조 인덱스의 유지 비용과 writer 부하는 여전히 중요하다.

6. 운영 환경에서 EXPLAIN ANALYZE를 안전하게 쓰는 기준

EXPLAIN ANALYZE는 읽기 전용 진단처럼 보이지만, 실제 실행이라는 점 때문에 운영 절차가 필요하다.

먼저 일반 EXPLAIN FORMAT=TREE 또는 EXPLAIN FORMAT=JSON으로 예상 계획을 확인한다.
쿼리가 큰 범위를 읽거나 정렬·집계를 수행하면 운영 primary에서 바로 EXPLAIN ANALYZE를 실행하지 않는다.
가능한 경우 replica, Aurora reader, 스테이징, 샘플 데이터에서 먼저 실행한다.
운영에서 실행해야 한다면 조건 범위를 줄이거나 LIMIT을 붙인 별도 진단 쿼리를 만든다. 단, LIMIT은 계획 자체를 바꿀 수 있으므로 결과 해석에 주석을 단다.
세션 수준에서 max_execution_time을 설정해 장시간 실행을 제한하는 방안을 검토한다.
이미 장애 상황이라면 EXPLAIN ANALYZE보다 performance_schema, slow query log, SHOW PROCESSLIST, lock wait 진단이 우선일 수 있다.

Aurora MySQL에서는 reader 인스턴스를 활용해 분석 부하를 primary에서 분리하기 쉽다. 그러나 Aurora reader도 실제 서비스 읽기 트래픽을 처리한다면 무거운 분석 쿼리가 reader CPU와 buffer cache에 영향을 줄 수 있다. 또한 Aurora의 Performance Insights는 대기 이벤트와 SQL digest를 보는 데 강하지만, optimizer의 추정 rows와 실제 rows 차이를 직접 설명해 주지는 않는다. 두 도구는 경쟁 관계가 아니라 상호 보완 관계다.

7. 진단 결과를 개선 작업으로 연결하기

추정 오차를 발견한 뒤에는 원인별로 처방을 분리해야 한다.

관찰	가능한 원인	우선 조치
추정 rows가 실제보다 훨씬 작다	선택도 과소평가, 통계 부정확, 상관관계 누락	`ANALYZE TABLE`, histogram 검토, 조건 재작성
추정 rows가 실제보다 훨씬 크다	선택도 과대평가, stale statistics	통계 갱신, 인덱스 cardinality 확인
actual time은 큰데 rows는 작다	random I/O, lock wait, 함수 연산, 네트워크 반환, filesort	대기 이벤트·락·정렬 여부 추가 확인
안쪽 iterator loops가 매우 크다	조인 순서 문제, 바깥쪽 결과 과다	조인 조건·인덱스·통계·쿼리 분해 검토
정렬/집계에서 시간이 집중된다	인덱스 순서 미활용, 큰 temporary table	covering/index order, 집계 선처리, 메모리 한도 검토

개선은 한 번에 여러 개를 넣지 않는 것이 좋다. ANALYZE TABLE, histogram, 인덱스 추가, 쿼리 재작성, optimizer hint를 동시에 적용하면 무엇이 효과를 냈는지 알 수 없다. 운영 변경은 다음 순서로 기록한다.

변경 전 SQL digest, 실행 시간, rows examined, 실행 계획을 저장한다.
단일 변경을 적용한다.
같은 조건에서 EXPLAIN, 가능하면 EXPLAIN ANALYZE를 비교한다.
애플리케이션 전체 지표와 복제 지연, CPU, I/O, lock wait 변화를 확인한다.
효과가 불충분하면 다음 변경으로 넘어간다.

8. 흔한 오해와 주의사항

첫째, EXPLAIN ANALYZE의 actual time은 절대적인 성능 벤치마크가 아니다. 버퍼 풀 warm/cold 상태, 동시 부하, CPU scheduling, I/O 상태에 따라 달라진다. 같은 쿼리를 여러 번 실행하면 시간이 달라질 수 있다.

둘째, actual rows가 작다고 항상 좋은 계획은 아니다. 한 행을 찾기 위해 많은 페이지를 읽었거나, 앞 단계에서 큰 정렬을 수행한 뒤 마지막에 적은 행만 반환했을 수 있다. tree 전체를 읽어야 한다.

셋째, 추정이 틀렸다고 항상 optimizer가 “나쁜” 것은 아니다. 통계로 알 수 없는 상관관계, 애플리케이션의 특정 값 편중, 최근 급격한 데이터 변화는 비용 모델이 알기 어렵다. DBA의 역할은 optimizer를 비난하는 것이 아니라 optimizer가 더 좋은 결정을 내릴 수 있는 정보와 구조를 제공하는 것이다.

넷째, optimizer hint는 마지막 수단에 가깝다. hint는 빠른 우회로가 될 수 있지만 데이터 분포가 바뀌면 오히려 장기적인 장애 요인이 된다. 통계, 인덱스, 쿼리 형태를 먼저 바로잡고, hint를 사용한다면 적용 이유와 제거 조건을 문서화해야 한다.

9. 점검 체크리스트

10. 결론

EXPLAIN ANALYZE는 MySQL 성능 분석에서 추정과 실제 사이의 간격을 드러내는 중요한 도구다. 단순히 실행 계획을 보는 단계에서 한 걸음 더 나아가, optimizer가 어떤 규모의 작업을 예상했고 실제 실행이 어디에서 달라졌는지를 확인하게 해 준다.

운영에서 중요한 것은 특정 출력 형식을 외우는 것이 아니라, 추정 오차를 통계·데이터 분포·인덱스 설계·조인 순서·정렬 비용으로 연결해 해석하는 능력이다. 다음 단계에서는 histogram, 복합 인덱스 설계, optimizer trace, slow query log와 Performance Schema를 함께 사용해 계획 선택의 원인을 더 깊게 추적할 수 있다.