RealMySQL 8.0 — 10. 실행 계획
10.1 통계 정보
10.1.1 테이블 및 인덱스 통계 정보
- MySQL 옵티마이저는 통계 정보를 기반으로 최적의 실행 계획을 수립한다. 테이블의 레코드 수나 인덱스의 분포 등에 대한 정보가 부정확하면 엉뚱한 실행 계획이 선택되어 쿼리 성능이 크게 저하될 수 있음을 유의해야 한다.
- 과거 MySQL 5.5 버전까지는 이러한 통계 정보가 메모리에만 유지되었기 때문에 서버 재시작 시 모두 사라졌다.
- MySQL 5.6부터는 통계 정보를 영구적으로 저장할 수 있게 개선되어, 각 InnoDB 테이블의 통계가 mysql 데이터베이스 산하의 innodb_table_stats(테이블 통계)와 innodb_index_stats(인덱스 통계) 테이블에 보관된다.
- 이를 통해 MySQL 서버를 재시작해도 이전에 수집된 통계 정보가 유지되어 일관된 실행 계획을 기대할 수 있었다.
- 특정 테이블에 대해 영구 통계를 사용하고 싶지 않다면 테이블 생성 시 STATS_PERSISTENT=0 옵션을 주어 메모리 기반으로만 관리할 수도 있다.
- 지정하지 않을 경우 전역 시스템 변수 innodb_stats_persistent 값에 따르며, 해당 변수 기본값은 ON이므로 기본적으로 영구 통계를 사용했음.
InnoDB 엔진은 통계 정보를 별도로 저장하고 활용하기 때문에, 통계 정보가 오래되거나 부정확해지면 옵티마이저가 잘못된 결정을 내릴 수 있다. 통계 정보는 아래 이벤트에서 자동 갱신됨:
- 테이블을 처음 열 때 (테이블이 캐시에서 내려갔다 다시 올라올 때)
- 테이블의 데이터가 대량으로 변경되어 통계가 오래된 경우 (약 10% 이상의 데이터 변경 시 자동 재분석, innodb_stats_auto_recalc=ON일 때)
- 사용자가 ANALYZE TABLE 명령을 수동 실행한 경우
- SHOW TABLE STATUS 또는 SHOW INDEX 명령으로 해당 테이블 정보를 조회한 경우
자동 갱신으로 인해 예기치 않게 통계 정보가 바뀌면, 실행 계획도 이에 따라 갑자기 바뀔 수 있었다. 예측 불가능성을 피하려면:
- innodb_stats_auto_recalc 변수를 OFF로 설정해 통계 정보가 자동으로 변하지 않게 할 수 있음.
- 대신 필요시에만 ANALYZE TABLE을 수동 실행하여 통계를 업데이트하면 됨.
- 테이블 단위로 통계 자동갱신 여부(STATS_AUTO_RECALC)를 설정할 수도 있었음.
통계 정확도를 높이기 위해 샘플링하는 페이지 수를 조정할 수도 있다.
- InnoDB는 테이블의 각 인덱스에서 일부 페이지를 임의로 추출하여(random dive 방식으로) 카디널리티 등을 추정했음.
- 샘플 페이지 수가 많을수록 통계 정확도가 올라가지만 분석 비용이 증가했음.
- 전역 변수:
- innodb_stats_persistent_sample_pages(기본값 20): 영구 통계 수집 시(ANALYZE TABLE 등) 샘플링할 인덱스 페이지 수
- innodb_stats_transient_sample_pages(기본값 8): 일시 통계 수집 시(자동 갱신 등) 샘플링할 인덱스 페이지 수
- 필요 시 값을 올려 더 많은 페이지를 샘플링해 통계 신뢰도를 높일 수 있지만, 너무 크게 올리면 통계 수집 시간이 오래 걸리므로 주의해야 한다.
mysql.innodb_table_stats(테이블 통계)에 저장되는 정보:
- n_rows: 테이블의 전체 레코드 건수(대략적인 추정치)
- clustered_index_size: 클러스터드 인덱스(주 키)의 페이지 수(테이블 크기)
- sum_of_other_index_sizes: 나머지 보조 인덱스들의 페이지 수 합계
mysql.innodb_index_stats(인덱스 통계)에 저장되는 정보:
- 인덱스별 여러 행의 통계가 기록되며, 주로 유니크한 값의 수(카디널리티)와 인덱스 크기 정보를 담았음.
- 예: stat_name이 n_diff_pfx01, n_diff_pfx02 ... 인 경우
- 각각 인덱스 선두 N개 컬럼 조합의 유니크 값 개수(N=1,2,...)를 의미하며 다중 컬럼 인덱스 분포도를 추정했음.
- 그 외 n_leaf_pages(리프 노드 페이지 수), size(인덱스 전체 페이지 수) 등의 정보가 저장된다.
- 이러한 통계 값들은 WHERE 조건과 인덱스 선택도(selectivity) 분석에 활용되며, 인덱스 사용/풀스캔 판단에 결정적인 역할을 한다.
10.1.2 히스토그램
- MySQL 5.7까지는 기본 통계 정보만으로 쿼리 계획을 세웠다. 그러나 인덱스 카디널리티만으로는 컬럼 값 분포 상태까지 파악하기 어렵기 때문에 한계가 분명했다.
- 이를 보완하고자 MySQL 8.0부터 히스토그램(histogram)을 도입했다.
- 히스토그램은 컬럼 값이 어떤 범위에 얼마나 몰려있는지를 나타내며, 특히 인덱스가 없는 컬럼 조건(selectivity)을 정확히 예측하는 데 유용하다.
- 히스토그램은 컬럼 단위로 수집/관리됐음.
- 자동 생성되지 않으며 DBA가 수동으로 아래 명령을 실행해야 했음:
- ANALYZE TABLE ... UPDATE HISTOGRAM ON <컬럼명>
- 수집된 히스토그램 데이터는 내부적으로 데이터 딕셔너리에 저장되고, 서버 기동 시 information_schema.column_statistics 테이블로 로드되어 조회할 수 있었음.
- 조회 예: SELECT * FROM information_schema.column_statistics WHERE ...
MySQL 8.0 히스토그램 타입 2가지였음:
- 싱글턴 히스토그램(Singleton Histogram)
- 컬럼의 각 개별 값에 대한 빈도수를 버킷별로 관리함.
- 각 버킷이 하나의 특정 값을 대표하며, 해당 값과 전체 대비 빈도 비율이 저장됨.
- 유니크 값 개수가 버킷 수보다 작거나 같을 때 생성되며, 모든 고유값을 1:1로 기록하므로 Value-Based 히스토그램(도수분포표)이라고도 함.
- 높이 균형 히스토그램(Height-Balanced, Equi-Height Histogram)
- 컬럼 값의 전체 범위를 일정한 빈도로 나눈 범위 단위로 관리함.
- 각 버킷은 값 하나가 아니라 값의 범위(range)를 나타내며, 가능한 한 동일 레코드 수를 포함하도록 범위가 조정됨.
- 각 버킷에는 최소값/최대값/빈도 비율/버킷 내 유니크 값 개수 등이 저장됨.
- 고유값 종류가 매우 많아 싱글턴으로 만들기 어려울 때 사용되며, 데이터가 치우친(skew) 분포도 일부 버킷에서 포착할 수 있었음.
- 히스토그램을 수집해 두면 옵티마이저가 조건절을 평가할 때 더 정확한 선택도 추정을 할 수 있게 됐음.
- 예: 날짜 컬럼 값 분포가 특정 기간에 몰려 있고 인덱스가 없어 기본적으로 균등 분포로 가정했다면, 히스토그램으로 실제로는 일치 레코드가 거의 없음을 파악해 대용량 풀스캔을 피할 수 있었음.
- 특히 비인덱스 컬럼이나 분포 편차가 큰 컬럼에 유용했음.
- 단, 히스토그램 수집은 테이블을 한 번 풀스캔하여 통계를 계산하므로 큰 테이블에 자주 수행하는 것은 바람직하지 않음.
- 필요에 따라 생성하고 ANALYZE ... DROP HISTOGRAM으로 삭제도 가능했음.
10.1.3 코스트 모델
- MySQL은 비용 기반 최적화기(cost-based optimizer)를 사용하여 여러 실행 계획 중 예상 비용(cost)이 가장 낮은 것을 선택했음.
- 비용 계산은 작업 연산(페이지 읽기, 레코드 평가 등)에 대해 고정된 비용 상수를 곱해 합산하는 방식이었음. 이 단위 작업별 비용 설정을 코스트 모델(cost model)이라고 부름.
- MySQL 8.0에서는 코스트 모델 설정값이 mysql DB의 두 테이블에 저장됨:
- server_cost: DB 엔진과 무관한 전역 연산 비용(정렬, 임시테이블 등)
- engine_cost: 스토리지 엔진 특화 저장 연산 비용(페이지 읽기 등)
두 테이블 공통 컬럼:
- cost_name: 단위 작업명
- default_value: 기본 비용 값(소스코드 내 디폴트)
- cost_value: 현재 적용 비용 값(NULL이면 기본값 사용)
- last_update: 마지막 변경 시각
- comment: 설명
- DBA가 cost_value를 조정할 수 있었음. 다만:
- 서버는 기동 시 테이블 값을 메모리로 읽어 사용하며 세션 단위로 고정됐음.
- 값을 변경한 뒤 즉시 반영하려면 FLUSH OPTIMIZER_COSTS를 실행해야 했음.
- 변경하지 않은 항목은 default_value를 그대로 사용함.
engine_cost 추가 컬럼:
- engine_name(스토리지 엔진 이름)
- device_type(디스크 종류, 현재는 0만 허용)
- InnoDB 비용 상수는 engine_name='InnoDB', device_type=0인 행에 저장됨.
MySQL 비용 항목 8가지 및 기본값:
- io_block_read_cost(engine_cost): 디스크 페이지 1개 읽기 비용 = 1.0
- memory_block_read_cost(engine_cost): 메모리 내 페이지 1개 읽기 비용 = 0.25
- disk_temptable_create_cost(server_cost): 디스크 임시테이블 생성 비용 = 20.0
- disk_temptable_row_cost(server_cost): 디스크 임시테이블 레코드 1건 읽기 비용 = 0.5
- key_compare_cost(server_cost): 인덱스 키 1건 비교 비용 = 0.05
- memory_temptable_create_cost(server_cost): 메모리 임시테이블 생성 비용 = 1.0
- memory_temptable_row_cost(server_cost): 메모리 임시테이블 레코드 1건 읽기 비용 = 0.1
- row_evaluate_cost(server_cost): 레코드 1건 평가(필터링) 비용 = 0.1
- 비용 상수는 인덱스 사용 vs 테이블스캔, 정렬/임시테이블 여부 등에 가중치로 작용했음.
- 예: disk_temptable_create_cost나 ..._row_cost를 높이면 디스크 임시테이블 계획 비용이 올라가 회피하려 했음.
- 예: row_evaluate_cost를 높이면 많은 행을 읽는 풀스캔/넓은 범위 스캔의 상대 비용이 커져 인덱스 활용 쪽을 선호하게 됐음.
- 기본값은 대체로 균형 잡혀 있으므로 특별한 경우가 아니라면 변경하지 않는 것이 권장됨.
- 잘못 조정하면 비합리적인 실행 계획이 선택될 수 있음.
- 기본값 복원은 해당 cost_value를 NULL로 설정하고 FLUSH OPTIMIZER_COSTS를 수행하면 됨.
참고)MySQL 공식 문서: 옵티마이저 비용 모델 (Optimizer Cost Model)
MySQL 옵티마이저는 여러 실행 계획 중 어떤 계획이 가장 효율적인지 판단하기 위해 비용(cost) 모델을 사용한다.
이 비용 모델은 쿼리 실행 중 발생하는 다양한 작업의 예상 비용을 수치화한 것이다.
옵티마이저는 다음 두 가지 비용 정보를 함께 사용해 실행 계획을 생성한다.
- 컴파일 시 내장된 기본 비용 상수 (compiled-in defaults)
- 시스템 테이블에 저장된 비용 값 (server_cost, engine_cost)
시스템 테이블의 목적은 DBA가 옵티마이저 판단 기준을 직접 조정할 수 있도록 하는 것이다.
1. 비용 모델의 동작 방식 (Cost Model General Operation)
옵티마이저 비용 모델은 다음과 같은 방식으로 동작한다.
1) 서버 시작 시 비용 테이블 로딩
- MySQL 서버는 시작 시 mysql.server_cost, mysql.engine_cost 테이블을 메모리로 읽는다
- 테이블의 cost_value가 NULL이 아닌 경우, 해당 값이 기본값보다 우선된다
- cost_value가 NULL이면, 컴파일된 기본 비용을 사용한다
2) 런타임 재로딩 조건
다음 상황에서 비용 테이블을 다시 읽는다.
- 스토리지 엔진이 동적으로 로드될 때
- FLUSH OPTIMIZER_COSTS 실행 시
단, 이미 시작된 세션에는 영향이 없다.
3) 세션 단위 적용
- 비용 모델은 세션 시작 시점 기준으로 고정
- 비용 테이블이 변경되어도 새로 시작한 세션에만 적용
- 기존 세션의 실행 계획에는 영향 없음
4) 복제(Replication)와 무관
- 비용 테이블 변경 사항은 리플리카로 전파되지 않는다
- 즉, 서버 인스턴스 단위 설정
2. 비용 모델 데이터베이스 구조
옵티마이저 비용 모델은 mysql 시스템 DB의 두 테이블로 구성된다.
- 1) server_cost
- 서버 공통 비용
- 스토리지 엔진과 무관한 연산 비용
- 2) engine_cost
- 스토리지 엔진별 비용
- InnoDB, MyISAM 등 엔진 특화 비용
3. server_cost 테이블 상세
주요 컬럼
cost_value가 0 이하이면 경고 로그 발생
server_cost에서 인식되는 비용 항목
- 1) disk_temptable_create_cost
- 2) disk_temptable_row_cost
- 디스크 기반 내부 임시 테이블 생성/행 처리 비용
- 값 증가 → 옵티마이저가 임시 테이블 사용을 기피
- MEMORY 임시 테이블보다 기본값이 큼
→ 디스크 I/O 비용 반영
- 3) memory_temptable_create_cost
- 4) memory_temptable_row_cost
- MEMORY 엔진 임시 테이블 비용
- 값 증가 → 임시 테이블 사용이 불리해짐
- 디스크 기반보다 기본값이 작음
- 5) key_compare_cost
- 인덱스 키 비교 비용
- 값 증가 시:
정렬을 피하고 인덱스를 사용하는 플랜이 유리해짐
- 6) row_evaluate_cost
- 행 조건 평가 비용
- 값 증가 시:
Range Scan, Index Scan이 상대적으로 유리
4. engine_cost 테이블 상세
주요 컬럼
Primary Key:
(cost_name, engine_name, device_type)
engine_cost에서 인식되는 비용 항목
- 1) io_block_read_cost
- 디스크에서 블록을 읽는 비용
- 값 증가 시:
적은 블록을 읽는 Range Scan 선호
- 2) memory_block_read_cost
- 버퍼 풀(메모리)에서 블록을 읽는 비용
- 일반적으로 io_block_read_cost보다 작음
디스크/메모리 비용 차이가 크면
서버 기동 직후와 캐시 이후의 실행 계획이 달라질 수 있음
5. 비용 모델 수정 가이드 (Making Changes)
기본 원칙
- 처음부터 큰 변경 ❌
- 2배 또는 1/2 수준으로 조정 후 효과 측정
가장 효과적인 튜닝 대상
- io_block_read_cost
- memory_block_read_cost
이 두 값은 디스크 vs 메모리 접근 비용 차이를 옵티마이저가 인식하게 만든다.
예제 1) 전체 엔진에 io_block_read_cost 적용
UPDATE mysql.engine_cost
SET cost_value = 2.0
WHERE cost_name = 'io_block_read_cost';
FLUSH OPTIMIZER_COSTS;
예제 2) InnoDB 전용 설정
INSERT INTO mysql.engine_cost
VALUES (
'InnoDB',
0,
'io_block_read_cost',
3.0,
CURRENT_TIMESTAMP,
'Using a slower disk for InnoDB'
);
FLUSH OPTIMIZER_COSTS;
→ InnoDB가 느린 디스크를 사용한다고 가정하여
디스크 접근이 많은 실행 계획을 피하도록 유도
핵심 요약
- 옵티마이저 비용 모델은 실행 계획 선택의 기준
- 기본값 + DBA 조정값을 함께 사용
- 비용 변경은 세션 단위로 적용
- io_block_read_cost, memory_block_read_cost가 가장 영향 큼
- 잘못 조정하면 실행 계획이 오히려 악화될 수 있음
10.2 실행 계획 확인
10.2.1 실행 계획 출력 포맷
- 실행 계획 확인은 EXPLAIN 키워드를 사용했음.
- EXPLAIN <SQL문>
- 약어로 DESC <SQL문>(또는 DESCRIBE)도 사용 가능했음.
예시 쿼리:
EXPLAIN
SELECT *
FROM 사원
WHERE 사원번호 BETWEEN 100001 AND 200000;
- 기본 출력은 Table format이며 예시는 다음과 같았음:
+----+-------------+--------+------------+-------+---------------+--------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | 사원 | NULL | range | PRIMARY | PRIMARY| 4 | NULL | 20080 | 100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+-------+----------+-------------+
- MySQL 8.0부터 FORMAT= 옵션으로 JSON/TREE 출력도 가능했음.
- EXPLAIN FORMAT=JSON SELECT ...;
JSON 문자열로 더 상세한 실행 계획 정보를 제공함(서브쿼리 중첩 구조, 추가 속성 등). - EXPLAIN FORMAT=TREE SELECT ...;
트리 구조 들여쓰기로 실행 흐름을 사람이 읽기 좋게 보여줌.
- EXPLAIN FORMAT=JSON SELECT ...;
- TREE 포맷 들여쓰기 규칙:
- 동일 들여쓰기 레벨에서는 위에 있는 행이 먼저 실행됨.
- 더 안쪽으로 들여쓴 연산이 선행되고, 바깥 레벨이 이후 수행됨.
- 참고:
- JSON 포맷은 머신 리더블하여 외부 툴/프로그래밍에 유용했음.
- TREE 포맷은 실행 흐름 이해/운영DB에서 실시간으로 PLAN 파악에 선호됨.
- 둘 다 기본 테이블 포맷에 없는 추가 정보를 제공하지만 핵심 해석 요소는 동일했음.
10.2.2 쿼리의 실행 시간 확인
- 일반 EXPLAIN은 예측 실행 계획만 보여주며 실제 실행은 하지 않는다. 따라서 실제 수행 시간/실제 처리 행 수는 알 수 없었다.
- MySQL 8.0의 EXPLAIN ANALYZE는 쿼리를 실제로 실행하여 단계별 실측 정보까지 제공했음.
- EXPLAIN ANALYZE 출력에는 TREE 형태에 아래 정보가 추가됨:
- actual time
- rows
- loops
- 예시(일부 발췌):
-> Index lookup on e using ix_firstname (first_name='Matt') (cost=215.08 rows=233)
(actual time=0.348..1.046 rows=233 loops=1)
-> Index lookup on s using PRIMARY (emp_no=e.emp_no) (cost=0.98 rows=10)
(actual time=0.007..0.009 rows=10 loops=233)
-> Filter: (s.salary > 50000 AND ... ) (cost=0.98 rows=1)
(actual time=0.009..0.011 rows=0 loops=233)
지표 의미:
- actual time=a..b
- 첫 결과 행 반환까지 걸린 시간 a와 전체 처리 완료까지 걸린 시간 b를 ms 단위로 보여줌.
- rows=X
- loop 1회당 평균 처리 행 수를 의미함(평균값이며 loop마다 달라도 평균으로 보고됨).
- loops=Y
- 실제 실행된 횟수이며, 조인에서 외부 테이블 행 수에 대응한다고 볼 수 있음.
- EXPLAIN ANALYZE는 쿼리를 실제로 수행하므로 전체 시간이 소비됨.
- 다만 SELECT는 클라이언트로 결과를 보내지 않고 내부에서 버려 네트워크 전송 시간 등은 제외된 순수 실행 시간이라고 볼 수 있음.
- 예측치와 실제 실행 차이를 비교해 통계 신뢰도/튜닝 포인트를 확인할 수 있었음.
- 예측 행수 vs 실제 처리 행수가 크게 차이 나면 통계 부정확 또는 히스토그램 미수집 등의 이슈일 수 있음.
10.3 실행 계획 분석
- EXPLAIN 테이블 포맷의 각 컬럼 의미를 이해하면 실행 흐름을 파악하고 튜닝 포인트를 찾을 수 있었음.
- EXPLAIN 출력에서 각 행이 위에서 아래로 실행되는 것이 아니며, id, select_type, Extra를 종합해 실행 순서를 파악해야 했음.
10.3.1 id 칼럼
- 각 SELECT 단위에 부여된 식별자였음.
- 하나의 SELECT문에서 조인 테이블 수만큼 행이 표시되며, 같은 SELECT에 속한 행들은 같은 id 값을 가졌음.
- 예: 한 SELECT에서 3개 테이블 조인이라면 id=1 행이 3개 나타났고, 같은 쿼리 블록에 속함을 의미했음.
- id가 같으면 조인 관계이며 SQL 왼쪽→오른쪽(또는 옵티마이저 순서)로 실행됨.
- id가 다르면 서브쿼리/UNION 등 다른 쿼리 블록을 의미했음.
- 메인 쿼리는 PRIMARY 또는 SIMPLE로 표시되며 id=1을 갖고, 내부 서브쿼리는 id=2,3…로 나타남.
- MySQL은 큰 id를 먼저 실행하는 경향이 있었음(예: id=3 실행 후 id=1에 전달).
- JSON 포맷에서는 select_id로 표현됨.
- 정리: id로 SELECT 구성 단위와 실행 순서를 파악할 수 있었음.
10.3.2 select_type 칼럼
- 각 SELECT 쿼리 단위의 종류를 나타냈음.
- 주요 값:
- SIMPLE: UNION/서브쿼리 없는 단순 SELECT이며 한 SQL문에 SIMPLE은 하나만 존재함.
- PRIMARY: 서브쿼리/UNION 포함 SQL에서 가장 바깥 SELECT를 의미함.
- UNION: UNION의 두 번째 이후 SELECT에 표시됨.
- DEPENDENT UNION: 외부 쿼리에 종속적인 UNION임(상위 값에 따라 매번 재평가).
- UNION RESULT: UNION 결과를 버퍼링하는 임시 테이블이며 <union M,N>로 표시됨.
- SUBQUERY: FROM절 이외 위치(SELECT 리스트/WHERE절 등)의 서브쿼리임.
- DEPENDENT SUBQUERY: 상위 컬럼 참조 상관 서브쿼리이며 row별 재평가됨.
- DERIVED: FROM절 파생 테이블이며 실행 시 임시 테이블로 물리화됨(UNION 첫 쿼리도 DERIVED가 될 수 있음).
- DEPENDENT DERIVED: LATERAL/JSON_TABLE 등에서 외부 행마다 파생 테이블을 다시 생성하는 경우임.
- MATERIALIZED: 서브쿼리를 한 번 실행해 임시테이블로 구체화하여 재사용하는 경우이며 <subquery N>로 표시됨.
- UNCACHEABLE SUBQUERY: 캐시 불가(예: NOW() 등으로 결과가 매번 달라짐)로 상위 행마다 매번 실행됨.
- UNCACHEABLE UNION: 캐시 불가 서브쿼리 내 UNION의 두 번째 이후 SELECT 표시임.
- INSERT ... SELECT, DELETE, UPDATE도 select_type에 DELETE/UPDATE 등으로 표시될 수 있었음.
10.3.3 table 칼럼
- 접근 대상 이름(테이블명 또는 별칭)이 표시됐음.
- 임시 객체 표기:
- <union M,N>: id=M과 id=N 결과를 UNION한 임시 테이블
- <derived N>: id=N 서브쿼리(파생 테이블) 결과 임시 테이블
- <subquery N>: 물리화된 서브쿼리 결과 임시 테이블(MATERIALIZED)
- < >로 둘러싸이면 실제 테이블이 아닌 임시 객체였음.
- 파티션 테이블이면 table은 테이블명을 보여주고 어떤 파티션을 읽었는지는 partitions 컬럼에 나타남.
10.3.4 partitions 칼럼
- 접근하는 파티션 이름이 표시됐음.
- 파티션 프루닝으로 일부 파티션만 읽히면 해당 목록이 콤마로 표시됨.
- NULL이면 비파티션이거나(또는) 모든 파티션을 검색한다는 뜻임.
- 프루닝이 기대와 다르게 동작하면 조건절/파티션 키 사용/인덱스 설계를 재검토해야 했음.
10.3.5 type 칼럼
- 테이블 접근 방법(조인 유형)을 나타냈음. ALL이 가장 비효율적이었음.
- 주요 타입(효율 좋은 쪽 → 나쁜 쪽) 개념 정리:
- system: 레코드 0~1건 수준(실무 드묾, InnoDB에서는 나오지 않음)
- const: PK/UNIQUE를 동등 비교해 최대 1건 매칭, 한 번만 읽고 상수 취급됨
- eq_ref: 조인에서 PK/UNIQUE를 동등 비교로 1건 보장
- ref: 비유일 인덱스 동등 조건, 여러 건 가능
- fulltext: FULLTEXT 인덱스
- ref_or_null: ref + NULL 포함 조건 최적화
- index_merge: 여러 인덱스 결과를 교집합/합집합으로 병합(대체로 효율이 썩 좋진 않음)
- unique_subquery / index_subquery: IN 서브쿼리 최적화용 접근 방식(8.0 세미조인으로 드묾)
- range: 인덱스 범위 스캔(BETWEEN 등), 흔한 좋은 접근
- index: 인덱스 풀스캔(커버링 인덱스에서 자주), 여전히 전 행 스캔이므로 필터링이 크면 비효율적
- ALL: 풀 테이블 스캔(비용 큼), 보통 튜닝 검토 신호
- 일반적으로 system > const > eq_ref > ref > range > index > ALL 순으로 효율적이었음.
- type=index는 Extra에 Using index가 동반되어 상황에 따라 ALL보다 나을 수도 있었음.
10.3.6 possible_keys 칼럼
- 옵티마이저가 고려 가능한 인덱스 후보 목록이 표시됨.
- 여기에 있다고 실제 사용된 것은 아니며, 실제 사용 인덱스는 key 컬럼에 나타남.
- NULL이면 해당 조건절에 활용할 인덱스가 전혀 없었다는 뜻이었음.
- possible_keys는 참고용이며 결국 key에서 무엇이 선택됐는지를 보면 됨.
10.3.7 key 칼럼
- 실제 선택된 인덱스 이름이 표시됨.
- NULL이면 인덱스를 사용하지 않았음을 의미했음(풀스캔 또는 join buffer 등).
- possible_keys에는 없는데 key에 나타날 수도 있었음(커버링 인덱스 상황).
- 이때 Extra에 Using index가 함께 나타날 수 있음.
- 인덱스 힌트(FORCE INDEX 등)를 쓰면 강제로 key가 잡힐 수도 있었음.
- 강제가 아니라면 cost 계산을 거쳐 선택된 인덱스였음.
10.3.8 key_len 칼럼
- 실제 사용한 인덱스 키 길이(bytes)를 의미했음.
- 멀티 컬럼 인덱스에서 몇 개 컬럼까지 사용됐는지 추정할 수 있었음.
- 예: key_len=4이고 col1이 INT(4 bytes)라면 col1까지 사용한 것으로 추정 가능했음.
- Nullable 컬럼은 NULL 여부 비트(1 byte)가 추가되므로 포함 시 +1 될 수 있음.
- key가 NULL이면 key_len도 NULL이었음.
- 인덱스 활용이 일부만 된 경우 튜닝 여지를 판단할 단서가 됐음.
10.3.9 ref 칼럼
- 인덱스와 비교한 값(매칭 대상)이 무엇인지 표시됐음.
- 대표 값:
- const: 상수와 비교(예: col=5)
- <table>.<column>: 다른 테이블 컬럼과 비교(조인)
- NULL: 참조 대상이 없을 때(인덱스 풀스캔 또는 범위 조건 자체 등)
- func: 함수/연산 결과로 비교(어떤 함수인지는 SHOW WARNINGS의 Extended EXPLAIN 정보로 확인 가능)
- 조인에서는 이전 테이블 컬럼명이 나타나는 경우가 많았고, 단순 WHERE에서는 const로 표시되곤 했음.
10.3.10 rows 칼럼
- 옵티마이저가 읽어야 한다고 예측한 레코드 건수(추정치)였음.
- InnoDB는 샘플링 기반이라 실제와 다를 수 있었음.
- rows는 누적이 아니라 “해당 단계 자체의 예측량”이었음.
- 인덱스를 타면 조건 일치 건수 예측, 풀스캔이면 테이블 총 건수 기반이었음.
- rows와 filtered를 함께 보면 실제 조인 투입 건수를 추정할 수 있다고 MySQL 공식 문서에서 설명함.
10.3.11 filtered 칼럼
- 읽은 행 중 조건을 통과하는 비율(%)을 의미했음.
- 실제 조인 투입 행 수는 rows × (filtered/100)로 계산할 수 있었음.
- 예: rows=1000, filtered=50.00 → 약 500건
- filtered=100.00이 항상 좋은 것은 아니며, 큰 테이블에서 100이면 인덱스 조건을 못 걸고 풀스캔일 수도 있어 맥락 해석이 필요했음.
- JSON 포맷에서는 별도 필드가 없고 조건식이 attached_condition으로 표시되므로 전통 EXPLAIN에서만 보는 참고 지표였음.
10.3.12 Extra 칼럼
- 실행 계획의 추가 정보가 텍스트로 표시됐음. 튜닝 힌트가 되는 경우가 많았음.
- 대표 메시지들(의미 그대로 유지했음):
- Using where
- Using index
- Using index condition (ICP)
- Using filesort
- Using temporary
- Distinct
- Not exists
- Range checked for each record (index map: N)
- Recursive
- Rematerialize
- Select tables optimized away
- Start temporary / End temporary
- unique row not found
- Using join buffer (<Algorithm>) (Block Nested Loop / Batched Key Access / hash join)
- Using MRR
- Using sort_union(...), Using union(...), Using intersect(...)
- Impossible WHERE / Impossible HAVING
- no matching row in const table
- No matching min/max row
- No matching rows after partition pruning
- Plan isn't ready yet
- 특히 Using filesort, Using temporary처럼 비용 유발 항목이나 Not exists, Start/End temporary 등 고급 최적화 표시는 유심히 볼 필요가 있었음.
- Extra 메시지들을 줄여나가는 것이 튜닝 목표가 되기도 했음.
'책 > Real MySQL 8.0' 카테고리의 다른 글
| [Real MySQL 8.0] 11장. 쿼리 작성 및 최적화(2) (1) | 2026.01.25 |
|---|---|
| [Real MySQL 8.0] 11장. 쿼리 작성 및 최적화(1) (1) | 2026.01.18 |
| [Real MySQL 8.0] 9장. 옵티마이저와 힌트 (1) | 2025.12.30 |
| [Real MySQL 8.0] 6장. 데이터 압축 & 7장. 데이터 암호화 (2) | 2025.12.18 |
| [Real MySQL 8.0] 8장. 인덱스 (1) | 2025.12.09 |