[Real MySQL 8.0] 10장. 실행 계획

RealMySQL 8.0 Chapter 10 Execution Plan

RealMySQL 8.0 — 10. 실행 계획

목차

• 10.1 통계 정보
  • 10.1.1 테이블 및 인덱스 통계 정보
  • 10.1.2 히스토그램
  • 10.1.3 코스트 모델
  • MySQL 공식 문서: 옵티마이저 비용 모델
• 10.2 실행 계획 확인
  • 10.2.1 실행 계획 출력 포맷
  • 10.2.2 쿼리의 실행 시간 확인
• 10.3 실행 계획 분석
  • 10.3.1 id 칼럼
  • 10.3.2 select_type 칼럼
  • 10.3.3 table 칼럼
  • 10.3.4 partitions 칼럼
  • 10.3.5 type 칼럼
  • 10.3.6 possible_keys 칼럼
  • 10.3.7 key 칼럼
  • 10.3.8 key_len 칼럼
  • 10.3.9 ref 칼럼
  • 10.3.10 rows 칼럼
  • 10.3.11 filtered 칼럼
  • 10.3.12 Extra 칼럼

---

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_name 비용 항목 이름 (대소문자 무관)

cost_value 실제 적용 비용 (NULL이면 기본값 사용)

default_value 컴파일된 기본 비용 (읽기 전용)

last_update 마지막 수정 시각

comment DBA용 설명

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

• 인덱스 키 비교 비용
• 값 증가 시:

filesort 비용 증가
정렬을 피하고 인덱스를 사용하는 플랜이 유리해짐

• 6) row_evaluate_cost

• 행 조건 평가 비용
• 값 증가 시:

Full Table Scan 비용 증가
Range Scan, Index Scan이 상대적으로 유리

4. engine_cost 테이블 상세

주요 컬럼

engine_name 스토리지 엔진 이름 (default 가능)

device_type 장치 유형 (현재는 0만 사용)

cost_name 비용 항목 이름

cost_value 적용 비용

default_value 기본값

comment 설명

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 ...;
    트리 구조 들여쓰기로 실행 흐름을 사람이 읽기 좋게 보여줌.
• 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 메시지들을 줄여나가는 것이 튜닝 목표가 되기도 했음.

---