MySQL ANALYZE TABLE 전후 실행계획: 27행으로 예상한 조회가 6만 행을 읽는 이유
데이터를 대량으로 변경한 뒤 기존 조회가 느려졌다면, 인덱스가 있는지만 확인해서는 원인을 찾기 어렵습니다. 같은 인덱스를 사용하더라도 옵티마이저가 예상한 처리량과 실제 처리량이 크게 다를 수 있기 때문입니다.
MySQL 8.4.11의 테스트 테이블에서 전체 10만 행은 그대로 두고 컬럼 값의 분포만 바꿨습니다. 통계를 갱신하기 전에는 27행으로 예상한 인덱스 조회가 실제로 60,000행을 처리했고, 집계 완료까지 약 202ms가 걸렸습니다. ANALYZE TABLE로 통계를 갱신하자 실행계획이 테이블 스캔으로 바뀌었고 같은 조회가 29.2ms에 끝났습니다.
이 글에서는 통계 테이블과 EXPLAIN ANALYZE 결과를 단계별로 비교하면서 추정치가 어디서 어긋났는지 확인합니다. 실험에서 사용한 eq_range_index_dive_limit 설정이 결과에 어떤 영향을 주는지, 운영 환경의 기본 설정에서는 오래된 통계가 언제 문제가 되는지도 함께 정리합니다.
실험 구성: 행 수는 그대로 두고 값의 분포만 바꿨습니다
테스트 환경
| 항목 | 테스트 조건 |
|---|---|
| MySQL 버전 | 8.4.11 |
| 실행 환경 | Windows 로컬 테스트 환경, Workbench |
| 스키마 / 테이블 | lab_analyze16 / orders_stats |
| 전체 행 수 | 변경 전후 모두 100,000행 |
| 컬럼 | id, bucket_id, amount, padding |
| padding 데이터 | 행마다 150자 |
| 기본 키 | PRIMARY KEY (id) |
| 보조 인덱스 | KEY idx_bucket (bucket_id) |
| 테이블 통계 옵션 | STATS_PERSISTENT=1, STATS_AUTO_RECALC=0, STATS_SAMPLE_PAGES=128 |
| 초기 분포 | bucket_id 1~10,000, 값마다 10행 |
| 변경 후 분포 | bucket_id 1~5, 값마다 20,000행 |
조회 SQL은 bucket_id IN (1, 2, 3) 조건으로 amount의 합계를 구합니다. amount는 보조 인덱스에 포함되어 있지 않으므로, idx_bucket으로 대상을 찾더라도 합계를 계산하려면 각 행의 테이블 데이터를 읽어야 합니다. 이 구조가 뒤에서 실행계획 선택에 영향을 줍니다.
STATS_AUTO_RECALC=0은 이 테이블의 자동 통계 갱신을 끈 실험용 설정입니다. 데이터가 바뀌어도 통계가 이전 상태로 남아 있는 상황을 의도적으로 만들기 위해 사용했습니다.
재현용 예시 준비 스크립트
아래는 본문의 조건을 다시 만들 수 있도록 정리한 예시 스크립트입니다. 컬럼 자료형과 amount 값은 위 표의 조건을 만족하도록 정한 값이므로, 실행계획의 cost나 시간은 본문과 조금 다르게 나올 수 있습니다. 반드시 별도 테스트 스키마에서 실행합니다.
CREATE DATABASE IF NOT EXISTS lab_analyze16;
CREATE TABLE lab_analyze16.orders_stats (
id INT UNSIGNED NOT NULL,
bucket_id INT UNSIGNED NOT NULL,
amount DECIMAL(12,2) NOT NULL,
padding VARCHAR(150) NOT NULL,
PRIMARY KEY (id),
KEY idx_bucket (bucket_id)
) ENGINE=InnoDB
STATS_PERSISTENT=1
STATS_AUTO_RECALC=0
STATS_SAMPLE_PAGES=128;
INSERT INTO lab_analyze16.orders_stats (id, bucket_id, amount, padding)
WITH digits AS (
SELECT 0 AS d 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
),
numbers AS (
SELECT a.d + b.d * 10 + c.d * 100
+ d.d * 1000 + e.d * 10000 AS n
FROM digits a
CROSS JOIN digits b
CROSS JOIN digits c
CROSS JOIN digits d
CROSS JOIN digits e
)
SELECT
n + 1,
MOD(n, 10000) + 1,
MOD(n, 1000) + 1,
REPEAT('x', 150)
FROM numbers
ORDER BY n;
COMMIT;
ANALYZE TABLE lab_analyze16.orders_stats;자동 갱신을 끈 테이블이므로 데이터를 넣은 뒤 ANALYZE TABLE을 한 번 실행해 초기 분포를 통계에 반영합니다. 이후 실험 순서는 다음과 같습니다.
- 초기 상태에서 진단 SQL과 EXPLAIN ANALYZE를 실행합니다.
- bucket_id를 1~5로 일괄 변경하고 커밋합니다.
- 현재 세션의
eq_range_index_dive_limit을 1로 설정합니다. - 통계 갱신 전 상태에서 진단 SQL과 EXPLAIN ANALYZE를 실행합니다.
- ANALYZE TABLE을 실행한 뒤 같은 진단 SQL과 EXPLAIN ANALYZE를 다시 실행합니다.
실제 데이터와 통계를 한 번에 비교하는 진단 SQL
각 단계에서 같은 진단 SQL을 사용했습니다. 실제 데이터에서 계산한 값과 통계 테이블에 저장된 값을 한 행으로 나란히 보여 주므로, 둘 사이의 불일치를 바로 확인할 수 있습니다.
SELECT
VERSION() AS mysql_version,
@@SESSION.eq_range_index_dive_limit AS dive_limit,
a.actual_rows,
a.actual_ndv,
a.matched_rows,
s.stat_value AS stats_ndv,
s.last_update AS stats_updated
FROM (
SELECT
COUNT(*) AS actual_rows,
COUNT(DISTINCT bucket_id) AS actual_ndv,
SUM(bucket_id IN (1, 2, 3)) AS matched_rows
FROM lab_analyze16.orders_stats
) AS a
LEFT JOIN mysql.innodb_index_stats AS s
ON s.database_name = 'lab_analyze16'
AND s.table_name = 'orders_stats'
AND s.index_name = 'idx_bucket'
AND s.stat_name = 'n_diff_pfx01';| 결과 컬럼 | 의미 |
|---|---|
| dive_limit | 현재 세션의 eq_range_index_dive_limit 값 |
| actual_rows | 실제 전체 행 수 |
| actual_ndv | 실제 bucket_id 고유값 수 |
| matched_rows | IN (1, 2, 3) 조건에 실제로 해당하는 행 수 |
| stats_ndv | 통계에 저장된 bucket_id 고유값 수 (idx_bucket의 n_diff_pfx01) |
| stats_updated | 해당 통계가 마지막으로 갱신된 시점 |
n_diff_pfx01은 인덱스의 첫 번째 컬럼까지를 기준으로 한 고유값 수 추정치입니다. idx_bucket은 bucket_id 하나로 구성되어 있으므로, 이 값이 곧 통계가 알고 있는 bucket_id의 고유값 수입니다. 초기 상태의 진단 결과는 다음과 같습니다.
변경 전 실행계획: 30행으로 예상하고 30행을 읽었습니다
초기 상태에서 조회 SQL의 실행계획을 확인했습니다.
EXPLAIN ANALYZE
SELECT SUM(amount) AS total_amount
FROM lab_analyze16.orders_stats
WHERE bucket_id IN (1, 2, 3);
| 노드 | 추정 cost | 추정 rows | 실제 rows | actual time(ms) |
|---|---|---|---|---|
| Index range scan (idx_bucket) | 39 | 30 | 30 | 2.22..2.28 |
| Aggregate: sum(amount) | 42 | 1 | 1 | 2.29..2.29 |
스캔 노드에는 over (bucket_id = 1) OR (bucket_id = 2) OR (bucket_id = 3)이 표시됩니다. IN 목록의 값 세 개가 각각 하나의 동등 범위로 처리되었다는 뜻입니다. 데이터 분포가 고르고 대상이 30행뿐이므로 인덱스로 찾아 읽는 계획이 자연스럽습니다.
이 단계의 dive_limit은 기본값 200입니다. 동등 범위가 3개로 200보다 적으므로 옵티마이저는 인덱스를 직접 조사하는 인덱스 다이브로 행 수를 추정했고, 추정치와 실제 값이 30행으로 정확히 일치했습니다.
데이터 분포 변경: 같은 조건의 대상이 30행에서 60,000행으로 늘었습니다
전체 행 수는 그대로 두고 bucket_id를 1부터 5까지의 값으로 일괄 변경했습니다.
UPDATE lab_analyze16.orders_stats
SET bucket_id = MOD(id - 1, 5) + 1;
COMMIT;값마다 20,000행씩 분포하므로 IN (1, 2, 3) 조건의 대상은 30행에서 60,000행으로 늘어납니다. 테이블 건수는 한 행도 늘지 않았지만 특정 조건의 조회 대상은 2,000배가 되었습니다. 운영에서는 상태 코드 통합, 분류값 정리, 데이터 이관 후 일괄 보정 같은 작업이 이런 변화를 만들 수 있습니다.
변경 후에는 현재 세션의 설정을 바꿨습니다.
SET SESSION eq_range_index_dive_limit = 1;eq_range_index_dive_limit을 1로 낮춘 이유
MySQL은 IN 목록 같은 동등 범위 조건의 행 수를 두 가지 방법으로 추정합니다. 하나는 인덱스를 직접 조사하는 인덱스 다이브이고, 다른 하나는 저장된 인덱스 통계를 이용한 계산입니다. 인덱스 다이브는 현재 데이터를 반영하므로 정확하지만 범위가 많을수록 추정 비용이 커집니다.
eq_range_index_dive_limit은 두 방법을 나누는 기준입니다. 동등 범위의 개수가 이 값 이상이면 옵티마이저는 인덱스 다이브 대신 통계를 사용합니다. 기본값 200에서는 값 3개짜리 IN 조건에 인덱스 다이브가 사용되므로, 통계가 오래되어도 추정치가 크게 틀리지 않을 수 있습니다.
이번 실험의 목적은 오래된 통계가 추정치에 미치는 영향을 관찰하는 것이므로, 값을 1로 낮춰 3개 범위에도 통계가 사용되도록 했습니다. 따라서 이 설정은 실험 조건이며 운영 권장값이 아닙니다. 기본 설정에서 같은 문제가 생기는 경우는 뒤에서 따로 정리합니다.
실제 고유값은 5개, 통계는 여전히 10,000개입니다
변경 후 같은 진단 SQL을 실행했습니다.
전체 행 수는 100,000행으로 같지만 actual_ndv는 5, stats_ndv는 10,000입니다. 통계만 보면 bucket_id 값 하나에 약 10행이 있는 테이블이고, 실제로는 값 하나에 20,000행이 있는 테이블입니다. 자동 통계 갱신을 끈 상태이므로 10만 행 전체를 바꾼 UPDATE도 통계에 반영되지 않았습니다.
캡처의 stats_updated는 날짜까지만 표시되어 세부 갱신 시각을 비교할 수 없습니다. 그래서 갱신 시각을 추측하지 않고, actual_ndv와 stats_ndv의 실제 값 차이를 불일치의 근거로 삼았습니다.
통계 갱신 전 실행계획: 27행으로 예상하고 60,000행을 처리했습니다
조회 SQL은 바꾸지 않고 같은 EXPLAIN ANALYZE를 실행했습니다.
EXPLAIN ANALYZE
SELECT SUM(amount) AS total_amount
FROM lab_analyze16.orders_stats
WHERE bucket_id IN (1, 2, 3);
| 노드 | 추정 cost | 추정 rows | 실제 rows | actual time(ms) |
|---|---|---|---|---|
| Index range scan (idx_bucket) | 12.9 | 27 | 60,000 | 0.181..193 |
| Aggregate: sum(amount) | 15.6 | 1 | 1 | 202..202 |
실제 처리량은 추정치의 약 2,222배입니다. 실행계획의 형태는 초기 상태와 같은 idx_bucket 범위 스캔이므로, 인덱스 이름과 접근 방식만 보면 이상이 없어 보입니다. 차이는 추정 rows와 실제 rows를 나란히 놓았을 때 비로소 드러납니다.
27행이라는 추정치는 어디서 나왔을까요
27은 IN 목록의 값 3개에 값당 약 9행을 곱한 수와 같습니다. 통계상 고유값이 10,000개이므로, 옵티마이저는 통계상 전체 행 수를 10,000으로 나눈 값당 행 수를 사용해 계산한 것으로 해석할 수 있습니다. 통계상 전체 행 수(n_rows)는 이번 캡처에서 확인하지 않았으므로, 값당 9행이라는 수치 자체는 추정치에서 역산한 값입니다.
핵심은 계산 방식이 아니라 계산의 재료입니다. 고유값 10,000개라는 이전 통계를 그대로 사용했기 때문에, 실제로는 값당 20,000행인 데이터를 값당 10행 안팎으로 계산했습니다.
60,000행을 보조 인덱스로 읽는 비용
InnoDB의 보조 인덱스 항목에는 bucket_id와 기본 키 id만 있습니다. amount의 합계를 구하려면 보조 인덱스에서 찾은 id로 기본 키 인덱스를 다시 탐색해 행 데이터를 읽어야 합니다. 대상이 30행이면 이 왕복이 30번이지만, 60,000행이면 60,000번입니다.
27행을 예상한 옵티마이저에게는 이 비용이 작아 보였고, 추정 cost도 12.9에 불과했습니다. 다만 이번 캡처로 물리 디스크 읽기 횟수까지 확인한 것은 아닙니다. 여기서 검증한 것은 통계 불일치, 행 수 추정 오류, 그리고 그 결과로 선택된 접근 방식입니다.
ANALYZE TABLE 이후: 실행계획이 테이블 스캔으로 바뀌었습니다
데이터 변경과 커밋이 끝난 상태에서 통계를 갱신했습니다.
ANALYZE TABLE lab_analyze16.orders_stats;
세 단계의 진단 결과를 한 표로 정리하면 통계가 실제 분포를 따라가는 과정이 보입니다.
| 단계 | dive_limit | 전체 행 | 실제 고유값 | 조건 일치 | 통계 고유값 |
|---|---|---|---|---|---|
| 초기 상태 (그림 1) | 200 | 100,000 | 10,000 | 30 | 10,000 |
| 변경 후, 갱신 전 (그림 3) | 1 | 100,000 | 5 | 60,000 | 10,000 |
| ANALYZE TABLE 후 (그림 5) | 1 | 100,000 | 5 | 60,000 | 5 |
이어서 조회 SQL을 바꾸지 않고 실행계획을 다시 확인했습니다.
| 노드 | 추정 cost | 추정 rows | 실제 rows | actual time(ms) |
|---|---|---|---|---|
| Table scan | 10,391 | 98,376 | 100,000 | 1.48..16.6 |
Filter: bucket_id in (1,2,3) | 10,391 | 59,025 | 60,000 | 1.48..24.3 |
| Aggregate: sum(amount) | 16,293 | 1 | 1 | 29.2..29.2 |
테이블 스캔 노드의 추정 98,376행은 통계상 전체 행 수입니다. 128개 페이지를 표본으로 추정한 값이라 실제 100,000행과 약 1.6% 차이가 있습니다. Filter 노드의 추정 59,025행은 98,376행에 3/5를 곱한 값(59,025.6)과 일치합니다. 고유값 5개 중 3개를 조회한다는 갱신된 통계가 그대로 추정에 쓰였다는 것을 확인할 수 있습니다.
Filter 결과의 추정치와 실제값의 차이는 975행으로, 실제 60,000행 기준 약 1.63%입니다. 갱신 전의 2,222배 차이와 비교하면 옵티마이저가 처리량을 거의 정확하게 파악하게 되었습니다. 이때 스캔 노드의 입력 100,000행과 Filter 노드의 출력 60,000행을 구분해서 읽어야 실행계획을 정확하게 해석할 수 있습니다.
통계 갱신 전후 비교
| 비교 항목 | 통계 갱신 전 | 통계 갱신 후 |
|---|---|---|
| 통계상 고유값 수 | 10,000 | 5 |
| 진단 시 dive_limit | 1 | 1 |
| 접근 방식 | 인덱스 범위 스캔 | 테이블 스캔 후 필터 |
| 조건 결과의 추정 행 수 | 27 | 59,025 |
| 조건 결과의 실제 행 수 | 60,000 | 60,000 |
| 추정 오차 | 약 2,222배 과소 추정 | 약 1.63% |
| 최상위 추정 cost | 15.6 | 16,293 |
| 집계 완료 시간(1회 관측) | 약 202ms | 약 29.2ms |
인덱스를 쓰지 않았는데 빨라진 이유
기록된 집계 완료 시간은 약 202ms에서 29.2ms로 약 85.5% 줄었습니다. 인덱스를 포기한 계획이 더 빨랐다는 점이 이번 결과에서 가장 눈여겨볼 부분입니다.
대상이 전체의 60%라면 보조 인덱스로 60,000개의 id를 찾고 기본 키 인덱스를 60,000번 탐색하는 것보다, 테이블을 처음부터 순서대로 읽으면서 조건을 적용하는 쪽이 유리할 수 있습니다. 갱신된 통계로 약 59,000행이 대상이라는 것을 알게 된 옵티마이저가 테이블 스캔을 선택한 것도 이 판단과 같습니다. 인덱스 사용 여부가 아니라 대상 비율에 맞는 접근 방식이 선택되었는지가 중요합니다.
cost는 커졌는데 시간은 줄어든 이유
최상위 집계 노드의 추정 cost는 15.6에서 16,293으로 1,000배 넘게 커졌지만 실제 시간은 줄었습니다. cost는 밀리초 단위의 실행 시간이 아니라, 옵티마이저가 같은 시점의 통계로 여러 계획을 비교하기 위한 상대 값입니다.
갱신 전의 15.6은 27행이라는 잘못된 추정으로 계산한 값이므로 실제 부담을 반영하지 못했습니다. 서로 다른 통계로 계산한 cost를 비교하면 오히려 “통계를 갱신하자 성능이 나빠졌다”는 잘못된 결론에 이를 수 있습니다. 통계가 바뀐 전후를 비교할 때는 추정 행 수가 실제에 가까워졌는지, 접근 방식과 실제 시간이 어떻게 달라졌는지를 함께 봐야 합니다.
측정값을 해석하는 범위
시간 값은 EXPLAIN ANALYZE에 기록된 관측값입니다. 측정 절차는 같은 쿼리를 두 번 실행해 두 번째 결과를 기록하는 방식으로 계획했지만, 캡처만으로 반복 횟수와 캐시 상태까지 검증할 수는 없습니다. 따라서 85.5%라는 개선율은 이번 실행에서 관측한 값으로 한정합니다.
초기 상태(2.29ms)와 이후 단계의 시간도 직접 비교하지 않습니다. 초기 상태는 dive_limit이 200이고 대상이 30행이었으므로 데이터와 설정이 모두 다릅니다. 통계 갱신의 효과는 데이터와 설정이 같은 갱신 전후 두 단계로만 판단했습니다.
또한 이번 실험은 고르게 분포한 값의 종류가 줄어드는 상황입니다. 특정 값 하나에만 데이터가 몰리는 불균등 분포는 고유값 수만으로 표현되지 않으므로, 통계를 갱신해도 값마다 추정이 어긋날 수 있습니다. 이 경우는 이번 실험에서 검증하지 않았습니다.
기본 설정의 운영 환경에서는 언제 문제가 될까요
이번 실험은 자동 갱신을 끄고 dive_limit을 1로 낮춰 문제를 의도적으로 드러냈습니다. 그렇다면 기본 설정의 운영 환경에서는 이런 일이 생기지 않을까요. 아래 내용은 MySQL의 일반적인 동작에 대한 설명이며, 이번 실험에서 별도로 측정하지는 않았습니다.
자동 갱신이 켜져 있어도 공백이 생깁니다
InnoDB의 영구 통계는 기본적으로 자동 갱신(innodb_stats_auto_recalc)이 켜져 있으며, 테이블 행의 약 10% 이상이 변경되면 백그라운드에서 다시 계산됩니다. 하지만 재계산은 비동기로 이뤄지므로 대량 변경 직후에는 잠시 이전 통계가 사용될 수 있습니다. 변경량이 10%에 못 미쳐도 특정 값의 비율이 크게 바뀌는 경우도 있습니다. 테이블 단위로 STATS_AUTO_RECALC=0이 지정되어 있다면 이번 실험처럼 통계가 계속 남아 있습니다.
기본 설정에서도 통계에 의존하는 추정이 있습니다
기본값 200에서도 IN 목록의 값이 200개 이상이면 인덱스 다이브 대신 통계가 사용됩니다. 조인에서 앞 테이블의 값으로 뒤 테이블의 인덱스를 조회하는 경우에도, 옵티마이저는 조회 시점의 값을 미리 알 수 없으므로 값당 행 수 통계로 추정합니다. 대량 변경한 컬럼이 조인 키나 긴 IN 목록에 쓰인다면, 이번 실험과 같은 추정 오류가 기본 설정에서도 나타날 수 있습니다.
테이블 단위 통계도 함께 확인합니다
인덱스 통계와 함께 테이블 통계의 전체 행 수 추정치와 마지막 갱신 시점도 확인할 수 있습니다. mysql 스키마 조회 권한이 필요하며, 이번 실험에서는 캡처하지 않은 참고용 쿼리입니다.
SELECT database_name,
table_name,
n_rows,
last_update
FROM mysql.innodb_table_stats
WHERE database_name = 'lab_analyze16'
AND table_name = 'orders_stats';n_rows는 표본으로 계산한 추정치이므로 COUNT(*)와 정확히 같지 않은 것이 정상입니다. 판단 기준은 값의 일치가 아니라, 실제 분포와 통계 사이에 실행계획을 바꿀 만큼 큰 차이가 있는지입니다.
ANALYZE TABLE을 실행하기 전에 판단할 것
| 확인할 상황 | 판단 기준 |
|---|---|
| 대량 변경 후 특정 조회가 느려졌습니다 | 변경한 컬럼이 WHERE 조건이나 조인 키에 쓰이는지부터 확인합니다 |
| 전체 행 수는 그대로입니다 | 건수가 아니라 고유값 수와 조건에 해당하는 비율이 바뀌었는지 봅니다 |
| 추정 rows와 실제 rows가 크게 다릅니다 | 통계와 실제 분포의 불일치를 우선 의심할 근거가 됩니다 |
| 추정 rows가 실제와 비슷한데도 느립니다 | 조회량 자체, 인덱스 구성, 잠금 대기 같은 다른 원인을 살핍니다 |
| 통계를 갱신하려 합니다 | 같은 테이블을 쓰는 대표 SQL의 실행계획이 함께 바뀔 수 있으므로 갱신 전후로 비교합니다 |
| 쓰기 부하가 높은 테이블입니다 | MySQL 공식 문서는 InnoDB 분석 중 읽기 잠금을 설명하므로, 실행 시간과 잠금 영향을 고려해 작업 시점을 정합니다 |
| 실험 설정을 운영에 적용하려 합니다 | STATS_AUTO_RECALC=0과 dive_limit=1은 관찰용 조건이므로 운영 권장값으로 쓰지 않습니다 |
이번 실험에서 실행계획의 모양은 갱신 전후 모두 평범했습니다. 갱신 전에는 인덱스를 썼고, 갱신 후에는 인덱스를 쓰지 않았습니다. 성능을 가른 것은 인덱스 사용 여부가 아니라 옵티마이저가 60,000행이라는 실제 처리량을 알고 있었는지였습니다. 대량 변경 뒤 조회가 느려졌다면 실행계획의 추정 rows와 실제 rows부터 나란히 놓고 보시기 바랍니다.
댓글 0
첫 댓글을 남겨보세요.