한 줄 정의
비용 기반 옵티마이저가 실행 계획을 세울 때 참고하는 입력값으로, 테이블·인덱스 통계(행 수·유니크 값 개수), 히스토그램(인덱스 없는 칼럼의 값 분포), 코스트 모델(단위 작업의 비용) 세 가지가 비용 계산의 근거입니다.
쉽게 말하면
통계 정보는 옵티마이저에게 주어지는 지도와 교통 정보 입니다.
- 테이블·인덱스 통계: “이 테이블에 행이 몇 개 있고, 인덱스 값이 얼마나 다양한가”
- 히스토그램: “어느 구간에 데이터가 몰려 있는가” — 인덱스가 없어 평소엔 보이지 않던 분포까지 알려줍니다
- 코스트 모델: “디스크 읽기 1번, 정렬 비교 1번이 각각 얼마짜리 작업인가”
지도가 낡으면(통계가 갱신되지 않으면) 1억 건짜리 테이블을 10건짜리로 착각해 풀 테이블 스캔을 선택할 수 있습니다. 0.1초에 끝날 쿼리가 1시간 걸리는 사고가 여기서 납니다.
왜 중요한가?
EXPLAIN 결과의 rows, filtered 값은 전부 이 통계에서 나옵니다. 통계를 모르면 다음을 설명할 수 없습니다.
- “데이터는 그대로인데 왜 어느 날 갑자기 인덱스 스캔이 풀 스캔으로 바뀌었나?” → 통계 자동 갱신
- “인덱스 없는 칼럼인데 옵티마이저가 어떻게 일치 건수를 예측하나?” → 히스토그램
- “왜 같은 쿼리가 서버마다 다른 실행 계획을 타나?” → 통계·코스트 모델 차이
통계는 옵티마이저의 입력값 이므로, 입력이 틀리면 아무리 똑똑한 옵티마이저도 엉뚱한 길을 고릅니다.
핵심 내용
10.1.1 테이블 및 인덱스 통계 정보
비용 기반 최적화에서 가장 중요한 것이 통계 정보입니다. MySQL은 다른 DBMS에 비해 통계의 정확도가 높지 않고 휘발성이 강했기 때문에, 실행 계획을 세울 때 실제 데이터를 일부 분석해 통계를 보완해 왔습니다.
영구적(Persistent) 통계 정보
MySQL 5.5 버전까지 는 통계가 메모리에만 관리됐습니다. SHOW INDEX 로만 인덱스 칼럼 분포를 볼 수 있었고, 서버가 재시작되면 통계가 모두 사라져 다시 수집해야 했습니다.
MySQL 5.6 버전부터 InnoDB 테이블의 통계를 mysql 데이터베이스의 두 테이블에 영구 저장합니다. 덕분에 서버를 재시작해도 통계가 유지됩니다.
| 시스템 테이블 | stat_name / 칼럼 | 의미 |
|---|---|---|
mysql.innodb_index_stats | n_diff_pfx% | 인덱스가 가진 유니크한 값의 개수 |
n_leaf_pages | 인덱스의 리프 노드 페이지 개수 | |
size | 인덱스 트리의 전체 페이지 개수 | |
mysql.innodb_table_stats | n_rows | 테이블의 전체 레코드 건수 |
clustered_index_size | 프라이머리 키의 크기(InnoDB 페이지 개수) | |
sum_of_other_index_sizes | PK를 제외한 인덱스의 크기(페이지 개수) |
STATS_PERSISTENT — 영구 통계 사용 여부
테이블 단위로 영구 통계 보관 여부를 지정합니다.
CREATE TABLE tab_test (fd1 INT, fd2 VARCHAR(20), PRIMARY KEY(fd1))
ENGINE=InnoDB
STATS_PERSISTENT = { DEFAULT | 0 | 1 };STATS_PERSISTENT=0: 5.5 이전 방식(메모리)으로 관리. 시스템 테이블에 저장하지 않음STATS_PERSISTENT=1:innodb_*_stats테이블에 영구 저장STATS_PERSISTENT=DEFAULT: 시스템 변수innodb_stats_persistent(기본 ON)의 값을 따름
STATS_AUTO_RECALC — 자동 갱신 제어
5.5까지는 사용자가 모르는 사이 다음 이벤트에서 통계가 자동 갱신됐고, 그 결과 잘 돌던 인덱스 레인지 스캔이 어느 날 갑자기 풀 테이블 스캔으로 바뀌는 사고가 났습니다.
- 테이블이 새로 오픈될 때
- 테이블 전체 레코드의 약 1/16 이 변경될 때
ANALYZE TABLE실행 시SHOW TABLE STATUS/SHOW INDEX FROM실행 시- InnoDB 모니터 활성화 시
innodb_stats_on_metadata=ON상태에서SHOW TABLE STATUS실행 시
영구 통계는 이런 의도하지 않은 변경을 막아 실행 계획을 안정시킵니다. 자동 갱신은 innodb_stats_auto_recalc(기본 ON) 또는 테이블 옵션 STATS_AUTO_RECALC 로 끌 수 있습니다.
STATS_AUTO_RECALC=1: 5.5 이전처럼 자동 수집STATS_AUTO_RECALC=0:ANALYZE TABLE을 실행할 때만 수집STATS_AUTO_RECALC=DEFAULT: 시스템 변수 값을 따름
샘플링 페이지 수
5.6부터 통계 수집 시 샘플링할 페이지 수가 용도별로 분리됐습니다.
| 시스템 변수 | 기본값 | 사용 시점 |
|---|---|---|
innodb_stats_transient_sample_pages | 8 | 자동 통계 수집 시 |
innodb_stats_persistent_sample_pages | 20 | ANALYZE TABLE 실행 시 |
점검 시간 등 한가한 때에 innodb_stats_persistent_sample_pages 를 높여 더 정확한 통계를 수집할 수 있습니다. 단 값이 너무 크면 수집 시간이 길어집니다.
10.1.2 히스토그램
5.7까지의 통계는 인덱스된 칼럼의 유니크 값 개수 정도뿐이라, 옵티마이저는 부족함을 메우려 실행 시점에 인덱스 일부 페이지를 랜덤 참조했습니다. 8.0부터 칼럼의 데이터 분포도 를 담는 히스토그램이 도입됐습니다.
10.1.2.1 수집·조회·삭제
히스토그램은 칼럼 단위 로 관리되며 자동 수집되지 않습니다. ANALYZE TABLE ... UPDATE HISTOGRAM 으로 수동 수집합니다. 정보는 시스템 딕셔너리에 저장되고, 서버 시작 시 information_schema.column_statistics 테이블로 로드됩니다.
-- 수집 (버킷 수 지정 가능, 기본 100, 최대 1024)
ANALYZE TABLE employees UPDATE HISTOGRAM ON gender, hire_date;
-- 조회 (JSON 형태)
SELECT * FROM information_schema.column_statistics
WHERE SCHEMA_NAME='employees' AND TABLE_NAME='employees' \G
-- 삭제 (딕셔너리만 삭제 → 즉시 완료, 단 실행 계획이 달라질 수 있음)
ANALYZE TABLE employees DROP HISTOGRAM ON gender, hire_date;두 가지 히스토그램 타입
flowchart TD H["히스토그램 (버킷 단위로 관리)"] H --> S["싱글톤 Singleton<br/>(Value-Based / 도수 분포)"] H --> E["높이 균형 Equi-Height<br/>(Height-Balanced)"] S --> S1["값마다 버킷 1개<br/>버킷당 값 2개:<br/>칼럼값, 누적 빈도율<br/>유니크 값이 버킷 수보다 적을 때"] E --> E1["균등한 건수의 범위마다 버킷 1개<br/>버킷당 값 4개:<br/>범위 시작·끝, 누적 빈도율,<br/>버킷 내 유니크 값 개수"]
| 타입 | 버킷 구성 | 버킷이 담는 값 | 적합한 경우 |
|---|---|---|---|
| 싱글톤(Singleton) | 칼럼값 하나당 버킷 하나 | 칼럼값, 누적 빈도율 (2개) | 유니크 값이 적은 칼럼 (예: gender, 코드값) |
| 높이 균형(Equi-Height) | 건수가 균등한 값 범위마다 버킷 하나 | 범위 시작값, 끝값, 누적 빈도율, 유니크 값 개수 (4개) | 유니크 값이 많은 칼럼 (예: hire_date) |
빈도율은 누적값 으로 표시됩니다. 예를 들어 gender 의 M 버킷이 0.5998, F 버킷이 1.0 이면, 실제 F 의 비율은 1 - 0.5998 입니다.
수집 관련 설정과 버전 주의
sampling-rate: 수집을 위해 스캔한 페이지 비율 (0.35면 전체의 35% 스캔)histogram_generation_max_mem_size: 수집에 쓸 메모리 한도 (기본 20MB). 이 한도에 맞춰 샘플링 비율이 결정됨number-of-buckets-specified: 지정한 버킷 수 (기본 100, 최대 1024 — 보통 100이면 충분)- 8.0.19 미만: 샘플링 비율과 무관하게 풀 스캔 으로 수집 → 부하 주의
- 8.0.19 이상: InnoDB가 자체 샘플링 알고리즘을 구현해 풀 스캔 불필요
히스토그램을 끄는 법
삭제하지 않고 옵티마이저가 히스토그램을 사용하지 않게 하려면 condition_fanout_filter 옵션을 끕니다. 단 이 옵션에 영향받는 다른 최적화도 함께 꺼질 수 있어 주의합니다.
SET GLOBAL optimizer_switch='condition_fanout_filter=off'; -- 전역
SET SESSION optimizer_switch='condition_fanout_filter=off'; -- 현재 커넥션
SELECT /*+ SET_VAR(optimizer_switch='condition_fanout_filter=off') */ ... -- 현재 쿼리만10.1.2.2 히스토그램의 용도
기존 통계는 균등 분포를 가정 했습니다. 레코드 1000건·유니크 값 100개면 동등 비교 시 무조건 “약 10건”으로 추정합니다. 하지만 실제 데이터는 한 사용자가 주문을 잔뜩 가지고 다른 사용자는 하나도 없는 식으로 편향 돼 있습니다.
employees 테이블에서 first_name='Zita'(224건) 중 birth_date 가 1950년대인 비율 예측을 비교해 보면 그 차이가 극명합니다.
| 방식 | 예측 비율 | 예측 건수 |
|---|---|---|
| 히스토그램 없음 (균등 분포 가정) | 11.11% | 24.8명 |
| 히스토그램 있음 | 60.82% | 136.2명 |
| 실제 데이터 | 63.84% | 143명 |
이 정확도가 특히 빛나는 곳이 조인 순서(드라이빙 테이블) 결정 입니다. 책의 예제에서 JOIN_ORDER 힌트로 순서만 바꿨는데 0.13초 vs 1.29초 로 갈립니다.
SELECT /*+ JOIN_ORDER(e, s) */ * FROM salaries s
INNER JOIN employees e ON e.emp_no=s.emp_no
AND e.birth_date BETWEEN '1950-01-01' AND '1950-02-01'
WHERE s.salary BETWEEN 40000 AND 70000; -- 0.13 sec
-- JOIN_ORDER(s, e) 로 바꾸면 1.29 secbirth_date·salary 는 인덱스가 없는 칼럼이라 히스토그램이 없으면 옵티마이저는 분포를 전혀 모른 채 테이블 크기 같은 단순 정보만으로 드라이빙 테이블을 정합니다. 그 결과 성능이 10배 까지, 버퍼 풀에 데이터가 없어 디스크를 읽어야 하면 그 이상으로 벌어질 수 있습니다.
10.1.2.3 히스토그램과 인덱스
옵티마이저는 실행 계획 수립 시 인덱스 B-Tree를 샘플링해 조건 일치 건수를 예측하는데, 이를 인덱스 다이브(Index Dive) 라고 합니다.
핵심 질문: 인덱스가 있는 칼럼에 히스토그램도 만들면 어느 쪽을 쓸까? MySQL 8.0은 인덱스된 칼럼은 히스토그램을 무시하고 인덱스 다이브를 사용합니다. 실제 검색 대상 값을 직접 샘플링하므로 히스토그램보다 항상 정확하기 때문입니다.
| 구분 | 인덱스 다이브 | 히스토그램 |
|---|---|---|
| 계산 시점 | 쿼리 실행 시점에 실시간 B-Tree 탐색 | 미리 수집된 분포를 조회 |
| 정확도 | 더 높음 (실제 값 샘플링) | 근사치 |
| 비용 | 탐색 비용 발생 (특히 IN 절 값이 많으면 큼) | 거의 없음 |
따라서 히스토그램은 인덱스가 없는 칼럼 에 만들 때 가장 가치가 큽니다.
10.1.3 코스트 모델 (Cost Model)
쿼리 처리에 필요한 단위 작업들 — 디스크/메모리 페이지 읽기, 인덱스 키 비교, 레코드 평가, 메모리/디스크 임시 테이블 작업 — 각각의 비용 집합 이 코스트 모델입니다. 5.7 이전엔 소스 코드에 상수로 박혀 있었지만, 하드웨어마다 실제 비용이 달라 5.7부터 관리자가 조정할 수 있게 됐습니다. 8.0에서는 히스토그램과 인덱스별 메모리 적재 페이지 비율 까지 반영되기 시작했습니다.
SELECT * FROM mysql.server_cost; -- 인덱스 탐색·레코드 비교·임시 테이블 비용
SELECT * FROM mysql.engine_cost; -- 데이터 페이지를 가져오는 비용테이블 칼럼
두 테이블 공통: cost_name, default_value(소스 기본값), cost_value(관리자 설정값, NULL이면 기본값 사용), last_updated, comment. engine_cost 는 추가로 engine_name(기본 default), device_type(8.0은 0만 가능, 미사용)을 가집니다.
8개 단위 작업 (책 표 기준)
| 소속 | cost_name | 기본값 | 설명 |
|---|---|---|---|
| engine_cost | io_block_read_cost | 1.00 | 디스크 데이터 페이지 읽기 |
| engine_cost | memory_block_read_cost | 0.25 | 메모리 데이터 페이지 읽기 |
| engine_cost | disk_temptable_create_cost | 20.00 | 디스크 임시 테이블 생성 |
| engine_cost | disk_temptable_row_cost | 0.50 | 디스크 임시 테이블 레코드 읽기 |
| server_cost | key_compare_cost | 0.05 | 인덱스 키 비교 |
| server_cost | memory_temptable_create_cost | 1.00 | 메모리 임시 테이블 생성 |
| server_cost | memory_temptable_row_cost | 0.10 | 메모리 임시 테이블 레코드 읽기 |
| server_cost | row_evaluate_cost | 0.10 | 레코드 비교(평가) |
row_evaluate_cost: 반환된 레코드가 조건에 맞는지 평가하는 비용. 높이면 풀 스캔처럼 많은 레코드를 처리하는 쿼리 비용이 올라가 → 레인지 스캔 선호key_compare_cost: 키 비교 비용. 높이면 정렬처럼 키 비교가 많은 쿼리 비용이 올라가 → 정렬 회피 선호
단위 비용을 높였을 때의 방향
| 비용을 ↑ | 옵티마이저의 경향 |
|---|---|
key_compare_cost | 정렬을 수행하지 않는 실행 계획 선호 |
row_evaluate_cost | 풀 스캔 회피, 인덱스 레인지 스캔 선호 |
disk_temptable_* | 디스크 임시 테이블을 만들지 않는 방향 |
memory_temptable_* | 메모리 임시 테이블을 만들지 않는 방향 |
io_block_read_cost | 버퍼 풀에 많이 적재된 인덱스 선호 |
memory_block_read_cost | 버퍼 풀 적재가 적은 인덱스도 사용 가능성 ↑ |
비용 확인
EXPLAIN FORMAT=TREE SELECT * FROM employees WHERE first_name='Matt' \G
-- -> Index lookup on employees using ix_firstname (first_name='Matt') (cost=256.10 rows=233)
EXPLAIN FORMAT=JSON ... -- query_cost, read_cost, eval_cost, prefix_cost 등 상세 비용비용을 바꿀 수 있다고 해서 바꿔야 하는 건 아닙니다. 코스트 모델은 하드웨어와 MySQL 내부 처리에 대한 깊은 지식을 요구하며, 기본값으로 20년 넘게 잘 작동 해 왔습니다. 함부로 변경하지 않는 게 정답입니다.
정리
세 가지 통계의 역할 분담
| 통계 | 무엇을 알려주나 | 갱신 방법 | 저장 위치 |
|---|---|---|---|
| 테이블·인덱스 통계 | 행 수, 유니크 값 개수 | ANALYZE TABLE / 자동 | mysql.innodb_*_stats |
| 히스토그램 | 칼럼 값 분포 (인덱스 없는 칼럼) | ANALYZE TABLE ... UPDATE HISTOGRAM (수동) | I_S.column_statistics |
| 코스트 모델 | 단위 작업 비용 | mysql.server_cost/engine_cost (수정 비권장) | 시스템 테이블 |
히스토그램 vs 인덱스 다이브 선택
flowchart TD Q["칼럼 조건의 일치 건수 추정"] --> IDX{"인덱스가<br/>있는가?"} IDX -->|있음| DIVE["인덱스 다이브 사용<br/>(히스토그램 무시, 더 정확)"] IDX -->|없음| HIST["히스토그램 사용<br/>(없으면 균등 분포로 가정)"]
내 생각
-
통계의 본질 가치는 정확도보다 안정성 입니다. 5.6의 영구 통계 도입이 중요한 건 정밀해져서가 아니라, “전체의 1/16 변경”이나 재시작 같은 보이지 않는 이벤트로 실행 계획이 멋대로 바뀌던 사고를 막아 주기 때문입니다. 운영에서 “어제 멀쩡하던 쿼리가 오늘 풀 스캔”의 원인이 정확히 이것이었습니다.
-
히스토그램은 인덱스 다이브에 밀리므로, “인덱스 없는 비대칭 칼럼”이라는 자리만 기억하면 됩니다. 인덱스 있는 칼럼에 히스토그램을 만드는 건 8.0에선 헛수고입니다. 반대로
status·birth_date·salary처럼 인덱스를 만들기 곤란하면서 조인·필터에 자주 쓰이는 칼럼이 정확히 히스토그램의 자리입니다. 책 예제의 0.13초 vs 1.29초가 이걸 증명합니다. -
코스트 모델 숫자를 외울 필요는 없지만 상대 비율은 직관으로 새겨둘 가치가 있습니다. 디스크(1.0)가 메모리(0.25)의 4배, 디스크 임시 테이블 생성(20.0)이 메모리(1.0)의 20배. 이 감각이 있으면 옵티마이저가 왜 굳이 인덱스를 타려 하는지, 왜
Using temporary; Using filesort조합이 그렇게 치명적인지가 숫자로 납득됩니다.
관련 개념
- Ch10-2 실행 계획 확인 — 이 통계로 만들어진 실행 계획을 EXPLAIN으로 확인
- Ch10-3 실행 계획 분석 —
rows·filtered칼럼이 곧 이 통계의 산출물 - Ch09-1 옵티마이저의 역할 — 통계를 입력으로 비용을 계산하는 주체
- Ch08-3 B-Tree 인덱스 — 인덱스 다이브가 탐색하는 자료구조
출처
- Real MySQL 8.0 (1권), 10.1 통계 정보