10. 실행 계획-1

10.1 통계 정보

MySQL 5.7 버전까지 테이블과 인덱스에 대한 개괄적인 정보를 가지고 실행 계획을 수립했다. 하지만 실제 테이블 컬럼 값들의 분포에 대한 정보는 없어서 실행 계획의 정확도가 떨어지는 경우가 많았다.

그래서 8.0 버전부터는 인덱스 되지 않은 컬럼들에 대해서도 데이터 분포도를 수집해서 저장하는 히스토그램(Histogram) 정보가 도입됐다.

테이블 및 인덱스 통계 정보

MySQL 또한 다른 DBMS 와 같이 비용 기반의 최적화를 사용하지만, 다른 DBMS보다 통계 정보의 정확도가 높지 않고 휘발성이 강했다. 그래서 MySQL 서버에서는 쿼리의 실행 계획을 수립할 때 실제 테이블의 데이터를 일부 분석해서 통계 정보를 보완해서 사용했다.

MySQL 5.6 버전부터 통계 정보의 정확도를 높일 수 있는 방법이 제공되기 시작했지만 아직도 많은 사용자가 기존 방식을 그대로 사용한다.

MySQL 서버의 통계 정보

5.6 버전부터 InnoDB 스토리지 엔진을 사용하는 테이블에 대한 통계 정보를 영구적으로(Persistent) 관리할 수 있게 개선됐다.

5.5 버전까지

각 테이블의 통계 정보를 메모리에 관리 -> MySQL 서버가 재시작되면 수집됐던 통계 정보가 사라짐
SHOW INDEX 명령으로 테이블의 인덱스 컬럼의 분포도를 확인

5.6 버전부터

각 테이블의 통계 정보를 mysql 데이터 베이스의 테이블로 관리 (innodb_index_stats, innodb_table_stats)
서버가 재시작돼도 기존의 통계 정보 유지 가능

STATS_PERSISTENT

MySQL 5.6 에서 통계 정보 테이블을 생성할 때 테이블 단위로 영구적인 통계 정보를 보관할지 말지 결정

STATS_PERSISTENT=0: 테이블의 통계 정보를 5.6 이전의 방식대로 관리하며, mysql 데이터베이스의 innodb_index_stats와 innodb_table_stats 테이블에 저장하지 않음
STATS_PERSISTENT=1: 테이블의 통계 정보를 mysql 데이터베이스의 innodb_index_stats와 innodb_table_stats 테이블에 저장함
STATS_PERSISTENT=DEFALUT: 테이블을 생성할 때 별도로 STATS_PERSISTENT 옵션을 설정하지 않은 것과 동일하며, 테이블의 통계를 영구적으로 관리할지 말지를 innodb_stats_persistent 시스템 변수의 값으로 결정한다.

innodb_stats_persistent (기본값: ON(1))

STATS_PERSISTENT 옵션 없이 테이블을 생성하면 영구적인 통계 정보를 사용하면서 innodb_index_stats와 innodb_table_stats 테이블에 통계 정보를 저장한다.

tab_persistent 테이블: STATS_PERSISTENT=1
tab_transient: STATS_PERSISTENT=0
ALTER TABLE 명령으로 기존 통계 정보 테이블의 STATS_PERSISTENT 변경 가능 (영구적으로 저장할지(1), 단기적으로 저장할지(0))

innodb_index_stats

stat_name='n_diff_pfx%' : 인덱스가 가진 유니크한 값의 개수
stat_name='n_leaf_pages' : 인덱스의 리프 노드 페이지 개수
stat_name='size' : 인덱스 트리의 전체 페이지 개수

innodb_table_stats

n_rows: 테이블의 전체 레코드 건수
clustered_index_size: 프라이머리 키의 크기 (InnoDB 페이지 개수)
sum_of_other_index_sizes: 프라이머리 키를 제외한 인덱스의 크기 (InnoDB 페이지 개수)

참고: innodb_table_stats.sum_of_other_index_sizes 컬럼의 값은 테이블의 STATS_AUTO_RECALC 옵션에 따라 0으로 보일 수도 있는데, 그 경우 다음과 같이 테이블에 대해 ANALYZE 명령을 실행하면 통곗값이 저장된다.

mysql> ANALYZE TABLE employees.employees;

통계 정보를 갱신하는 이벤트

테이블이 새로 오픈되는 경우
테이블의 레코드가 대량으로 변경되는 경우 (테이블의 전체 레코드 중에서 1/16 정도의 UPDATE 또는 INSERT나 DELETE가 실행되는 경우)
ANALYZE TABLE 명령이 실행되는 경우
SHOW TABLE STATUS 명령이나 SHOW INDEX FROM 명령이 실행되는 경우
InnoDB 모니터가 활성화되는 경우
innodb_stats_on_metadata 시스템 설정이 ON 인 상태에서 SHOW TABLE STATUS 명령이 실행되는 경우

이렇게 자주 테이블의 통계 정보가 갱신되면 응용 프로그램의 쿼리를 인덱스 레인지 스캔으로 잘 처리하던 MySQL 서버가 갑자기 풀 테이블 스캔으로 실행하는 상황이 발생할 수 있다.

그러나 영구적인 통계 정보(MySQL 5.6~)가 도입되면서 이렇게 의도하지 않은 통계 정보 변경을 막을 수 있게 되었다.

innodb_stats_auto_recalc 시스템 설정 변수의 값을 OFF로 설정하면 통계 정보가 자동으로 갱신되지 않는다. (기본값: ON)

STATS_AUTO_RECALC

MySQL 5.6 에서 테이블을 생성할 때 통계 정보를 자동으로 수집할지 테이블 단위로 조정한다.

STATS_AUTO_RECALC=1: 테이블의 통계 정보를 5.6 이전의 방식대로 자동 수집한다.
STATS_AUTO_RECALC=0: 테이블의 통계 정보는 ANALYZE TABLE 명령을 실행할 때만 수집한다.
STATS_AUTO_RECALC=DEFALUT: 테이블을 생성할 때 별도로 STATS_AUTO_RECALC 옵션을 설정하지 않은 것과 동일하며, 테이블의 통계 정보 수집을 innodb_stats_auto_recalc 시스템 변수의 값으로 결정한다.

테이블 샘플링 관련 시스템 변수

innodb_stats_sample_pages (5.6부터 Deprecated) : 테이블의 통계 정보를 수집할 때 몇 개의 InnoDB 테이블 블록을 샘플링할지 결정
innodb_stats_transient_sample_pages (기본값: 8) : 자동으로 통계 정보 수집이 실행될 때 8개 페이지만 임의로 샘플링해서 분석하고 그 결과를 통계 정보로 활용한다.
innodb_stats_persistent_sample_pages (기본값: 20) : ANALYZE TABLE 명령이 실행되면 임의로 20개 페이지만 샘플링해서 분석하고 그 결과를 영구적인 통계 정보 테이블에 저장하고 활용함을 의미한다. 이 값을 높이면 더 정확한 통계 정보를 수집할 수 있지만 통계 정보 수집 시간이 길어지므로 주의해야 한다.

히스토그램

MySQL 5.7 버전까지의 통계 정보는 단순히 인덱스된 컬럼의 유니크한 값의 개수 정도만 가지고 있었는데, 옵티마이저가 최적의 실행 계획을 수립하기에는 많이 부족했다. 그래서 옵티마이저는 실행 계획을 수립할 때 실제 인덱스의 일부 페이지를 랜덤으로 가져와 참조하는 방식을 사용했다.

8.0 버전으로 업그레이드되면서 MySQL 서버도 드디어 컬럼의 데이터 분포도를 참조할 수 있는 히스토그램(Histogram) 정보를 활용할 수 있게 되었다.

히스토그램 정보 수집 및 삭제

히스토그램 정보는 컬럼 단위로 ANALYZE TABLE ... UPDATE HISTOGRAM 명령을 실행하여 수동으로 수집 및 관리된다.

수집된 히스토그램 정보는 시스템 딕셔너리에 저장되고, MySQL 서버가 시작될 때 딕셔너리의 히스토그램 정보를 information_schema 데이터베이스의 column_statistics 테이블로 로드한다.

실제 히스토그램 정보를 조회하려면 column_statistics 테이블을 SELECT 해서 참조할 수 있다.

-- // 히스토그램 정보 수집
mysql> ANALYZE TABLE employees.employees
	UPDATE HISTOGRAM ON gender, hire_date;

-- // 히스토그램 정보 조회
mysql> SELECT *
	FROM COLUMN_STATISTICS
	WHERE SCHEMA_NAME='employees'
	  AND TABLE_NAME='employees'\G

MySQL 8.0 버전에서 지원하는 히스토그램 타입

히스토그램은 버킷(Bucket) 단위로 레코드 건수나 컬럼 값의 범위가 관리된다.

Singleton(싱글톤 히스토그램)

컬럼 값별 레코드 건수를 관리하는 히스토그램으로, Value-Based 히스토그램 또는 도수 분포라고도 불린다.
컬럼 값별로 버킷이 할당되며, 각 버킷은 컬럼 값과 발생 빈도의 비율의 2개 값을 가진다.

Equi-Height(높이 균형 히스토그램)

컬럼 값 범위를 균등한 개수로 구분해서 관리하는 히스토그램으로, Height-Balanced 히스토그램이라고도 불린다.
개수가 균등한 컬럼 값의 범위별로 하나의 버킷이 할당되며, 각 버킷이 범위 시작 값과 마지막 값, 발생 빈도 비율과 각 버킷에 포함된 유니크한 값의 개수 등 4개의 값을 가진다.

예) employees 테이블의 히스토그램 데이터 그래프

싱글톤 히스토그램은 ENUM('M', 'F') 타입인 gender 컬럼이 가질 수 있는 2개의 값에 대해 누적된 레코드 건수의 비율을 가지고 있다.
gender 컬럼 값이 'M' 인 값의 비율 약 0.5998
gender 컬럼 값이 'F'인 값의 비율 1 -> 히스토그램의 모든 레코드 건수 비율은 누적으로 표시되므로 실제 비율은 (1-0.5998)
싱글톤 히스토그램은 주로 코드 값처럼 유니크한 값의 개수가 상대적으로 적은(히스토그램의 버킷 수보다 적은) 경우 사용된다.

높이 균형 히스토그램은 컬럼 값의 각 범위에 대해 레코드 건수 비율이 누적으로 표시된다.
히스토그램의 버킷 범위가 뒤로 갈수록 비율이 높아지는 것처럼 보이지만, 범위 별로 비율이 같은 수준에서 hire_date 컬럼의 범위가 선택된 것이다.
그래프의 기울기가 일정한 것을 보면 각 범위가 비슷한 값(레코드 건수)을 가진다는 것을 알 수 있다.

information_schema.column_statistics.HISTOGRAM 컬럼이 가지는 나머지 필드

sampling-rate: 히스토그램 정보를 수집하기 위해 스캔한 페이지의 비율을 저장. 샘플링 비율이 0.35라면 전체 데이터 페이지의 35%를 스캔해서 정보를 수집했음을 의미한다. MySQL 서버는 histogram_generation_max_mem_size 시스템 변수에 설정된 메모리 크기에 맞게 적절히 샘플링한다.
histogram-type: 히스토그램의 종류를 저장
number-of-buckets-specified: 히스토그램을 생성할 때 설정했던 버킷의 개수를 저장. 히스토그램을 생성할 때 별도로 버킷의 개수를 지정하지 않았다면 기본으로 100개의 버킷이 사용된다. 최대 1024개를 설정할 수 있지만, 일반적으로 100개의 버킷이면 충분한 것으로 알려져 있다.

참고: MySQL 8.0.19 미만의 버전까지는 히스토그램 생성 시 샘플링 비율(Sampling-rate)과 histogram_generation_max_mem_size 시스템 변수의 크기에 관계없이 MySQL 서버는 풀 스캔을 통해 데이터 페이지를 샘플링해서 히스토그램을 생성했다. 하지만 8.0.19 버전부터 InnoDB 스토리지 엔진 자체적으로 샘플링 알고리즘을 구현했으며, 더이상 히스토그램 수집 시 풀 테이블 스캔이 필요하지 않게 됐다.

히스토그램 삭제

히스토그램의 삭제 작업은 테이블의 데이터를 참조하는 것이 아니라 딕셔너리의 내용만 삭제하기 때문에 다른 쿼리 처리의 성능에 영향을 주지 않고 즉시 완료된다.

하지만 히스토그램이 사라지면 쿼리의 실행 계획이 달라질 수 있으므로 주의하자.

ANALYZE TABLE ... DROP HISTOGRAM

mysql> ANALYZE TABLE employees.employees
	DROP HISTOGRAM ON gender, hire_date;

옵티마이저의 히스토그램 사용 비활성화

optimizer_switch 시스템 변수의 condition_fanout_filter 옵션 OFF

-- // 글로벌 설정
mysql> SET GLOBAL optimizer_switch='condition_fanout_filter=0ff';

히스토그램의 용도

MySQL 서버에 히스토그램이 도입되기 전의 통계 정보는 전체 레코드 건수와 인덱스된 컬럼이 가지는 유니크한 값의 개수 정도로, 컬럼 값이 항상 균등한 분포도를 가진다는 가정을 전제로 실행 계획을 수립했다.

하지만 실제 응용 프로그램의 데이터는 분포도가 항상 균등한 것은 아니다.

히스토그램은 특정 컬럼이 가지는 모든 값에 대한 분포도 정보를 가지지는 않지만 각 범위(버킷) 별로 레코드의 건수와 유니크한 값의 개수 정보를 가지기 때문에 더 정확한 예측이 가능하다.

특히 테이블의 조인 순서에 따라 쿼리의 성능은 천차만별인데 옵티마이저가 테이블의 전체 레코드 건수나 크기 등 단순한 정보만으로 조인의 드라이빙 테이블을 결정한다면, 상황에 따라 어떤 테이블이라도 드라이빙 테이블이 될 수 있다.

InnoDB 버퍼 풀에 데이터가 존재하지 않아서 디스크에서 데이터를 읽어야 하는 경우라면 그 차이가 더 커진다. 각 컬럼에 대해 히스토그램 정보가 있으면 어느 테이블을 먼저 읽어야 조인 횟수를 줄일 수 있을지 옵티마이저가 더 정확하게 판단할 수 있다.

히스토그램과 인덱스

MySQL 서버에서는 쿼리의 실행 계획을 수립할 때 사용 가능한 인덱스들로부터 조건절에 일치하는 레코드 건수를 대략 파악하고 최종적으로 가장 나은 실행 계획을 선택한다.

이때 조건절에 일치하는 레코드 건수를 예측하기 위해 옵티마이저가 실제 인덱스의 B-Tree를 샘플링하는 작업을 매뉴얼에서는 인덱스 다이브(Index Dive) 라고 한다.

옵티마이저는 위 쿼리를 처리하기 위해 테이블 풀 스캔을 할지 first_name 컬럼의 인덱스를 이용할지 고민할 것이다.

birth_date 컬럼에는 인덱스가 없으므로 이 쿼리에서 birth_date 컬럼은 실행 계획에 큰 영향을 미치지 않을 것이다.

쿼리의 검색 조건으로 많이 사용되는 컬럼에 대해서는 일반적으로 인덱스를 생성한다. 이렇게 인덱스된 컬럼에 대해 히스토그램 정보를 수집해 두는 것이 좋을까?

8.0 버전에서 인덱스된 컬럼을 검색 조건으로 사용하는 경우, 그 컬럼의 히스토그램은 사용하지 않고 실제 인덱스 다이브를 통해 직접 수집한 정보를 활용한다. 실제 검색 조건의 대상 값에 대한 샘플링을 실행하는 것이므로 항상 히스토그램보다 정확한 결과를 기대할 수 있기 때문이다.

히스토그램은 주로 인덱스되지 않은 컬럼에 대한 데이터 분포도를 참조하는 용도로 사용된다.

하지만 인덱스 다이브 작업은 어느 정도의 비용이 필요하며, 때로는 (IN 절에 값이 많이 명시된 경우) 실행 계획 수립만으로도 상당한 인덱스 다이브를 실행하고 비용도 그만큼 커진다. 아마 실제 인덱스 다이브를 실행하기 보다 히스토그램을 활용하는 최적화 기능도 MySQL 서버에 추가되지 않을까.

코스트 모델 (Cost Model)

MySQL 서버가 쿼리를 처리하려면 다음과 같은 다양한 작업을 필요로 한다.

디스크로부터 데이터 페이지 읽기
메모리(InnoDB 버퍼 풀)로부터 데이터 페이지 읽기
인덱스 키 비교
레코드 평가
메모리 임시 테이블 작업
디스크 임시 테이블 작업

MySQL 서버는 사용자의 쿼리에 대해 이런 다양한 작업이 얼마나 필요한지 예측하고 전체 작업 비용을 계산한 결과를 바탕으로 최적의 실행 계획을 찾는다.

코스트 모델

전체 쿼리의 비용을 계산하는 데 필요한 단위 작업들의 비용

5.7 이전 버전: 단위 작업들의 비용을 MySQL 서버 소스 코드에 상수화하여 사용. 비용은 하드웨어에 따라 달라질 수 있기 때문에 고정된 비용을 일률적으로 적용하는 것은 최적의 실행 계획 수립에 있어 방해 요소였다.
5.7 버전: 각 단위 작업의 비용을 DBMS 관리자가 조정할 수 있게 개선됨. 하지만 인덱스되지 않은 컬럼의 데이터 분포(히스토그램)나 메모리에 상주 중인 페이지의 비율 등 비용 계산과 연관된 부분의 정보가 부족했다.
8.0 버전: 컬럼의 데이터 분포를 위한 히스토그램과 각 인덱스별 메모리에 적재된 페이지의 비율이 관리되고 옵티마이저의 실행 계획에 사용되기 시작

MySQL 8.0 의 코스트 모델

다음 2개 테이블에 저장되어 있는 설정 값을 사용하며, 두 테이블 모두 mysql DB에 존재한다.

server_cost: 인덱스를 찾고 레코드를 비교하고 임시 테이블 처리에 대한 비용 관리
engine_cost: 레코드를 가진 데이터 페이지를 가져오는 데 필요한 비용 관리

server_cost 테이블과 engine_cost 테이블이 가진 컬럼

cost_name: 코스트 모델의 각 단위 작업
default_value: 각 단위 작업의 비용 (기본값이며 MySQL 서버 소스 코드에 설정됨)
cost_value: DBMS 관리자가 설정한 값 (이 값이 NULL이면 default_value 컬럼 값을 사용)
last_updated: 단위 작업의 비용이 변경된 시점
comment: 비용에 대한 추가 설명

engine_cost 테이블은 위 5개 컬럼에 추가로 다음 2개 컬럼을 더 가진다.

engine_name: 비용이 적용된 스토리지 엔진. 스토리지 엔진 별로 단위 작업의 비용을 달리 설정하고자 할 때 사용
device_type: 디스크 타입

row_evaluate_cost: 스토리지 엔진이 반환한 레코드가 쿼리의 조건에 일치하는지 평가하는 작업의 비용. 이 값이 증가할수록 풀 테이블 스캔과 같이 많은 레코드를 처리하는 쿼리의 비용이 높아지고 레인지 스캔과 같이 상대적으로 적은 레코드를 처리하는 쿼리의 비용이 낮아진다.
key_compare_cost: 키 값의 비교 작업의 비용. 이 값이 증가할수록 레코드 정렬과 같이 키 값 비교 처리가 많은 경우 쿼리의 비용이 높아진다.

실행 계획의 Cost 확인

코스트 모델에서 중요한 것은 각 단위 작업에 설정되는 비용 값이 커지면 어떤 실행 계획들이 고비용으로 바뀌고, 저비용으로 바뀌는지를 파악하는 것이다.

key_compare_cost 비용을 높이면 옵티마이저가 가능하면 정렬을 수행하지 않는 방향의 실행 계획을 선택할 가능성이 높아진다.
row_evaluate_cost 비용을 높이면 풀 스캔을 실행하는 쿼리의 비용이 높아지고, 옵티마이저는 가능하면 인덱스 레인지 스캔을 사용하는 실행 계획을 선택할 가능성이 높아진다.
disk_temptable_create_cost와 disk_temptable_row_cost 비용을 높이면 옵티마이저는 디스크에 임시 테이블을 만들지 않는 방향의 실행 계획을 선택할 가능성이 높아진다.
memory_temptable_create_cost와 memory_temptable_row_cost 비용을 높이면 옵티마이저는 메모리 임시 테이블을 만들지 않는 방향의 실행 계획을 선택할 가능성이 높아진다.
io_block_read_cost 비용이 높아지면 옵티마이저는 가능하면 InnoDB 버퍼 풀에 데이터 페이지가 많이 적재돼 있는 인덱스를 사용하는 실행 계획을 선택할 가능성이 높아진다.
memory_block_read_cost 비용이 높아지면 MySQL 서버는 InnoDB 버퍼 풀에 적재된 데이터 페이지가 상대적으로 적다고 하더라도 그 인덱스를 사용할 가능성이 높아진다.

10.2 실행 계획 확인

MySQL 서버의 실행 계획은 DESC 또는 EXPLAIN 명령으로 확인할 수 있다.

EXPLAIN 명령에 사용할 수 있는 옵션 (8.0 버전)

FORMAT: 실행 계획의 출력 포맷
EXPLAIN ANALYZE: 실제 쿼리의 실행 결과 확인

실행 계획 출력 포맷

이전 버전에서는 EXPLAIN EXTENDED 또는 EXPLAIN PARTITIONS 명령이 구분되어 있었지만, 8.0 버전부터 EXPLAIN 으로 통합되었다.

8.0 버전부터 FORMAT 옵션을 사용하여 실행 계획의 표시 방법을 JSON 이나 TREE, 단순 테이블 형태로 선택할 수 있다.

테이블 포맷

트리 포맷

JSON 포맷

쿼리의 실행 시간 확인

SHOW PROFILE: 어떤 부분에서 시간이 많이 소요되는지 확인할 수 있지만 실행 계획의 단계별로 소요된 시간 정보는 표시하지 않는다.
EXPLAIN ANALYZE: 쿼리의 실행 계획과 단계별 소요된 시간 정보를 확인할 수 있다. 항상 결과를 TREE 포맷으로 출력한다. (FORMAT 옵션 사용 불가)

TREE 포맷의 실행 계획에서 들여쓰기는 호출 순서를 의미하며, 실제 실행 순서는 다음 기준으로 읽는다.

들여쓰기가 같은 레벨에서는 상단에 위치한 라인이 먼저 실행
들여쓰기가 다른 레벨에서는 가장 안쪽에 위치한 라인이 먼저 실행

위 쿼리의 실행 순서

실행 단계별 필드

actual time: 실제 소요된 시간. loops 필드의 값이 1 이상이면 평균 시간
rows: 처리한 레코드 건수. loops 필드의 값이 1 이상이면 평균 레코드 건수
loops: 반복 횟수

ex) 실행 계획 F 열 : Index lookup on s using PRIMARY (emp_no=e.emp_no)

actual time=0.007..0.009

employees 테이블에서 읽은 emp_no 값을 기준으로 salaries 테이블에서 일치하는 레코드를 검색하는데 걸린 시간
첫 번째 레코드를 가져오는 데 걸린 평균 시간: 0.007 밀리초
마지막 레코드를 가져오는 데 걸린 평균 시간: 0.009 밀리초 -> rows 필드 값이 10, salaries 테이블에서 emp_no를 검색해서 10개의 레코드를 모두 가져오는 데 걸린 평균 시간

rows=10

employees 테이블에서 읽은 emp_no에 일치하는 salaries 테이블의 평균 레코드 건수

loops=233

employees 테이블에서 읽은 emp_no를 이용해 salaries 테이블의 레코드를 찾는 작업이 반복된 횟수
employees 테이블에서 읽은 emp_no의 개수

EXPLAIN ANALYZE 명령은 EXPLAIN 명령과 달리 실행 계획만 추출하는 것이 아니라 실제 쿼리를 실행하고 사용된 실행 계획과 소요 시간을 보여준다.

10.3 실행 계획 분석

실행 계획이 어떤 접근 방법을 사용해서 어떤 최적화를 수행하는지, 어떤 인덱스를 사용하는지를 이해하는 것은 중요하다.

EXPLAIN 명령

표의 각 라인(레코드)은 쿼리 문장에서 사용된 테이블(서브쿼리로 생성한 임시 테이블 포함)의 개수만큼 출력
실행 순서는 위에서 아래 순 (UNION 이나 상관 서브쿼리의 경우 순서대로 표시되지 않을 수 있다)
id 컬럼의 값이 작을수록 쿼리의 바깥(Outer) 부분이거나 먼저 접근한 테이블이고, id 컬럼의 값이 클수록 쿼리의 안쪽(Inner) 부분 또는 나중에 접근한 테이블에 해당

참고: 설명의 편의를 위해 설명에 필요치 않거나 중요하지 않은 컬럼들은 일부 제거하거나 축약하여 나타낸다. 그래서 사용자의 화면에 표시되는 결과와 포맷, 그리고 컬럼 개수가 일치하지 않을 수 있다.

id 컬럼

하나의 SELECT 문장은 다시 1개 이상의 하위(SUB) SELECT 문장을 포함할 수 있다.

mysql> SELECT ...
	FROM (SELECT ... FROM tb_test1) tb1, tb_test2 tb2
	WHERE tb1.id=tb2.id;

위 쿼리를 다음과 같이 "단위(SELECT) 쿼리"로 분리할 수 있다.

mysql> SELECT ... FROM tb_test1;
mysql> SELECT ... FROM tb1, tb_test2 tb2 WHERE tb1.id=tb2.id;

실행 계획에서 가장 왼쪽에 표시되는 id 컬럼

단위 SELECT 쿼리 별로 부여되는 식별자 값이다.
테이블의 접근 순서를 의미하지는 않는다.

하나의 SELECT 문장 안에서 여러 개의 테이블을 조인하면 조인되는 테이블의 개수만큼 실행 계획 레코드가 출력되지만 같은 id 값이 부여된다. (WHERE e.emp_no=s.emp_no)

쿼리 문장이 3개의 단위 SELECT 쿼리로 구성돼 있으므로 실행 계획의 각 레코드가 다른 id 값을 가진다.

dept_emp 의 id 값=1, employees 의 id 값=2
실제 실행 순서: employees -> dept_emp 순으로 읽는다.

select_type 컬럼

단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시하는 컬럼

SIMPLE
PRIMARY
UNION, DEPENDENT UNION, UNION RESULT
SUBQUERY, DEPENDENT SUBQUERY
DERIVED, DEPENDENT DERIVED
UNCACHEABLE SUBQUERY, UNCACHEABLE UNION
MATERIALIZED

SIMPLE

UNION 이나 서브쿼리를 사용하지 않는 단순한 SELECT 쿼리 (조인이 포함된 경우)

쿼리 문장이 아무리 복잡해도 실행 계획에서 select_type 이 SIMPLE 인 쿼리는 하나만 존재한다.

일반적으로 제일 바깥 SELECT 쿼리의 select_type 이 SIMPLE 로 표시된다.

PRIMARY

UNION 이나 서브쿼리를 가지는 SELECT 쿼리의 실행 계획에서 가장 바깥쪽(Outer)에 있는 단위 쿼리

SIMPLE 과 마찬가지로 select_type 이 PRIMARY 인 단위 SELECT 쿼리는 하나만 존재하며, 쿼리의 제일 바깥쪽에 있는 SELECT 단위 쿼리가 PRIMARY 로 표시된다.

UNION

UNION 으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리

UNION 의 첫 번째 단위 SELECT 는 select_type 이 UNION 이 아닌 UNION 되는 쿼리 결과들을 모아서 저장하는 임시 테이블(DIRIVED) 로 표시된다.

UNION 이 되는 단위 SELECT 쿼리 3개 중에서 첫 번째(e1 테이블) 외 나머지 2개는 모두 UNION
UNION 의 첫 번째 쿼리는 전체 UNION 의 결과를 대표하는 select_type으로 설정 -> 세 개의 서브쿼리로 조회된 결과를 UNION ALL 로 결합하여 임시 테이블을 생성하므로 "DERIVED"

DEPENDENT UNION

select_type UNION 처럼 UNION이나 UNION ALL로 집합을 결합하는 쿼리

DEPENDENT는 UNION이나 UNION ALL로 결합된 단위 쿼리가 외부 쿼리에 의해 영향을 받는 것을 의미한다.

두 개의 SELECT 쿼리가 UNION 으로 결합된 형태 -> select_type: UNION
내부적으로 UNION 이 사용된 SELECT 쿼리는 WHERE 조건에 "e2.emp_no=e1.emp_no" 와 "e3.emp_no=e1.emp_no" 조건이 자동으로 추가되어 실행된다.
옵티마이저는 IN 내부의 서브쿼리를 먼저 처리하지 않고, 외부의 employees 테이블을 먼저 읽고 나서 서브쿼리를 실행하는데 이때 employees 테이블의 컬럼 값(e1.emp_no)이 서브쿼리에 사용됨 -> 내부 쿼리가 외부의 값을 참조해서 처리될 때 DEPENDENT 키워드 표시

UNION RESULT

UNION 결과를 담아두는 임시 테이블

UNION RESULT는 실제 쿼리에서 단위 쿼리가 아니기 때문에 별도의 id 값은 부여되지 않는다. (id = NULL)

8.0 이전 버전

UNION ALL, UNION(또는 UNION DISTINCT) -> UNION의 결과를 임시 테이블로 생성

8.0 버전

UNION ALL -> 임시 테이블을 사용하지 않음
UNION(또는 UNION DISTINCT) -> 여전히 임시 테이블에 결과를 버퍼링

select_type: UNION RESULT 라인의 "table: <union1,2>" 은 id=1 과 id=2인 단위 쿼리의 조회 결과를 UNION 했음을 의미한다.

UNION ALL 을 사용하면 임시 테이블에 버퍼링하지 않기 때문에 UNION RESULT 라인이 사라진다.

SUBQUERY

select_type 의 SUBQUERY는 FROM 절 이외에서 사용되는 서브쿼리만을 의미

FROM 절에 사용된 서브쿼리 -> select_type: DERIVED (파생 테이블)
그 외의 위치에서 사용된 서브쿼리 -> select_type: SUBQUERY

참고: 서브쿼리의 분류

사용 위치에 따라

중첩된 쿼리(Nested Query): SELECT 되는 컬럼에 사용된 서브쿼리
서브쿼리(Subquery): WHERE 절에 사용된 경우
파생 테이블(Derived Table): FROM 절에 사용된 서브쿼리 (일반적인 DBMS에서는 Inline View 또는 Sub Select)

반환하는 값의 특성에 따라

스칼라 서브쿼리(Scalar Subquery): 하나의 값(컬럼이 단 하나인 레코드 1건만) 반환하는 쿼리
로우 서브쿼리(Row Subquery): 컬럼의 개수와 관계없이 하나의 레코드만 반환하는 쿼리

DEPENDENT SUBQUERY

서브쿼리가 바깥쪽(Outer) SELECT 쿼리에서 정의된 컬럼을 사용하는 경우

위 쿼리는 안쪽(Inner)의 서브쿼리 결과가 바깥쪽(Outer) SELECT 쿼리의 컬럼(e.emp_no)에 의존적이기 때문에 DEPENDENT 키워드가 붙는다.
DEPENDENT UNION 처럼 DEPENDENT SUBQUERY 도 외부 쿼리가 먼저 수행되고 내부 쿼리(서브쿼리)가 실행돼야 하므로 (DEPENDENT 키워드가 없는) 일반 쿼리보다 처리 속도가 느리다.

DERIVED

단위 SELECT 쿼리의 실행 결과로 메모리나 디스크에 임시테이블을 생성하는 것

파생 테이블: select_type 이 DERIVED 인 서브쿼리의 결과가 저장되는 임시 테이블

5.5 버전까지

서브쿼리가 FROM 절에 사용된 경우 항상 select_type 이 DERIVED 인 실행 계획 수립
파생 테이블에는 인덱스가 없으므로 다른 테이블과 조인할 때 불리

5.6 버전부터

옵티마이저 옵션(optimizer_switch)에 따라 FROM 절의 서브쿼리를 외부 쿼리와 통합하는 최적화 수행
쿼리의 특성에 맞게 임시 테이블에도 인덱스 추가 가능

위 쿼리는 FROM 절의 서브쿼리를 제거하고 조인으로 처리할 수 있는 형태지만, 이전 MySQL 서버는 실행 계획처럼 FROM 절의 서브쿼리를 임시 테이블로 만들어서 처리한다. (파생 테이블: <derived2>)
파생 테이블에 대한 최적화가 부족한 버전의 MySQL 서버를 사용 중이라면, DERIVED 형태의 실행 계획을 조인으로 해결할 수 있게 쿼리를 바꾸는 것이 좋다.
8.0 버전부터는 FROM 절의 불필요한 서브쿼리는 조인으로 재작성하여 처리한다.

참고: 쿼리를 튜닝하기 위해 실행 계획을 확인할 때 가장 먼저 select_type 컬럼의 값이 DERIVED 인 것이 있는지 확인해야 한다. 서브쿼리를 조인으로 해결할 수 있는 경우라면 서브쿼리보다 조인을 사용할 것을 매우 권장한다.

DEPENDENT DERIVED

서브쿼리에서는 메인쿼리의 컬럼을 참조하지 못한다. 따라서 서브쿼리와의 조인 조건은 메인쿼리의 WHERE 절에 기술해야 한다.

8.0 버전부터는 래터럴 조인(LATERAL JOIN) 기능이 추가되면서 서브쿼리 안에서도 메인쿼리의 FROM 절에 있는 테이블의 컬럼을 참조할 수 있다.

래터럴 조인

LATERAL 키워드를 사용해야 서브쿼리에서 외부 컬럼을 참조할 수 있다.

employees 테이블의 레코드 1건당 salaries 테이블의 레코드를 최근 순서대로 최대 2건까지 조인하는 쿼리
서브쿼리에서 외부 컬럼(e.emp_no)을 사용하고 있다.
select_type: DEPENDENT DERIVED 는 해당 테이블이 래터럴 조인으로 사용된 것을 의미한다.

UNCACHEABLE SUBQUERY

하나의 쿼리 문장에 서브쿼리가 하나만 있더라도 실제 그 서브쿼리가 한 번만 실행되는 것은 아니다.

그런데 조건이 똑같은 서브쿼리가 실행될 때 다시 실행하지 않고 이전의 실행 결과를 그대로 사용할 수 있게 서브쿼리의 결과를 내부 캐시 공간에 담아둔다. (여기서의 서브쿼리 캐시는 쿼리 캐시나 파생 테이블과는 무관하다)

SUBQUERY 는 바깥쪽(Outer)의 영향을 받지 않으므로 처음 한 번만 실행해서 그 결과를 캐시한다.

DEPENDENT SUBQUERY 는 참조하는 바깥쪽(Outer) 쿼리의 컬럼 값 단위로 캐시한다.

서브쿼리에 포함된 요소에 의해 캐시 자체가 불가능할 수 있는데, 이때 select_type 이 UNCACHEABLE SUBQUERY 로 표시된다. 이 경우 참조하는 외부 쿼리의 컬럼 값이 같은 값이더라도 매번 새로 실행한다.

캐시를 사용하지 못하게 하는 요소

서브쿼리에 사용자 변수가 사용된 경우
NOT-DETERMINISTIC 속성의 스토어드 루틴이 서브쿼리 내에 사용된 경우
UUID() 나 RAND() 와 같이 결과값이 호출할 때마다 달라지는 함수가 서브쿼리에 사용된 경우

UNCACHEABLE UNION

UNION 에 포함된 요소의 의해 캐시가 불가능한 경우

MATERIALIZED

5.6 버전부터 도입된 select_type

주로 FROM 절이나 IN (subquery) 형태의 쿼리에 사용된 서브쿼리의 최적화를 위해 사용된다.

5.6 버전까지는 외부 테이블의 레코드(e.emp_no)마다 서브쿼리가 실행되는 형태로 처리됐다.
5.7 버전부터는 서브쿼리를 먼저 처리하여 임시 테이블로 구체화(Materialization) 한 후, 임시 테이블과 employees 테이블을 조인하는 형태로 최적화되어 처리된다.

select_type 컬럼의 MATERIALIZED 키워드는 DERIVED 와 비슷하게 쿼리의 내용을 임시 테이블로 생성하는 것을 의미한다.

table 컬럼

MySQL 서버의 실행 계획은 단위 SELECT 쿼리 기준이 아닌 테이블 기준으로 표시된다.

테이블의 별칭이 부여된 경우 별칭이 표시된다.

mysql> EXPLAIN SELECT NOW();
mysql> EXPLAIN SELECT NOW() FROM DUAL;

두 번째 쿼리에서 FROM 절에 사용된 "DUAL" 테이블이 실제로 존재하지 않아도 오류를 발생시키지 않는다.

FROM 절이 없으면 오류가 발생하는 오라클 RDBMS 사용자를 위해 DUAL 이라는 테이블이 있는 것처럼 작동한다.

실제로 MySQL 옵티마이저는 "FROM DUAL" 부분을 제거하고 첫 번째 쿼리와 동일하게 변형해서 처리한다.

table: NULL

별도의 테이블을 사용하지 않는 SELECT 쿼리

table: <derived N> 또는 <union M,N> 또는 <subquery N>

"<>"로 둘러싸인 테이블 => 임시 테이블
M, N => 단위 SELECT 쿼리의 id 값
<derived N>: id가 N인 실행 계획(select_type:DERIVED)에서 만들어진 파생 테이블
<union M, N>: id가 M과 N인 실행 계획을 UNION 한 결과를 저장하는 임시 테이블 (select_type:UNION RESULT)
<subquery N>: id가 N인 실행 계획(select_type: MATERIALIZED)에서 서브쿼리를 구체화한 임시 테이블

실행 계획 분석

첫 번째 라인의 테이블이 <derived2> -> id=2 인 라인이 먼저 실행되고 그 결과를 파생 테이블에 저장
세 번째 라인의 select_type 이 DERIVED -> table 컬럼의 dept_emp 테이블을 읽어서 파생 테이블을 생성
첫 번째 라인과 두 번째 라인은 같은 id 값을 가짐 -> <derived2> 테이블과 e 테이블이 조인되는 쿼리. <derived2>가 먼저 표시됐으므로 드라이빙 테이블, e가 드리븐 테이블

patitions 컬럼

5.7 버전까지 EXPLAIN PARTITION 명령을 통해 옵티마이저가 사용하는 파티션의 목록 확인

8.0 버전부터 EXPLAIN 명령으로 파티션 관련 실행 계획까지 모두 확인 가능

예) 파티션 테이블 employees_2 (hire_date 컬럼 기준 5년 단위)

파티션 키로 사용되는 컬럼(hire_date)은 프라이머리 키를 포함한 모든 유니크 인덱스의 일부여야 한다.

=> PRIMARY KEY(emp_no, hire_date)

위 쿼리에서 조회하려는 데이터는 p1996_2000과 p2001_2005 파티션에 저장돼 있다.
파티션 프루닝(Partition pruning): 파티션이 여러 개인 테이블에서 불필요한 파티션을 빼고 쿼리를 수행하기 위해 실제 접근할 테이블만 골라내는 과정

파티션을 참조하는 쿼리는 옵티마이저가 쿼리 처리를 위해 필요한 파티션의 목록만 모아서 실행 계획의 partitions 컬럼에 표시해준다.

type: ALL -> 풀 테이블 스캔
파티션은 물리적으로 개별 테이블처럼 별도의 저장 공간을 가지기 때문에 partitions 컬럼의 파티션만 풀 스캔한다.

type 컬럼

type 이후의 컬럼: 각 테이블에 대한 접근 방법(Access Type). 인덱스 사용 여부 등 -> 쿼리 튜닝에 있어 중요한 정보

type 컬럼에 표시될 수 있는 값

system, const, eq_ref, ref, fulltext, ref_or_null, unique_subquery, index_subquery, range, index_merge, index, ALL

(뒤로 갈수록 성능이 느려짐)

ALL을 제외한 나머지는 모두 인덱스를 사용하는 접근 방법 (ALL: 풀 테이블 스캔)
index_merge을 제외한 나머지는 모두 하나의 인덱스만 사용
하나의 단위 SELECT 쿼리는 하나의 접근 방법만을 사용할 수 있다.

system

레코드가 1건만 존재하는 테이블 또는 레코드가 없는 테이블을 참조하는 형태의 접근 방법

MyISAM이나 MEMORY 테이블에서만 사용된다.

const

테이블의 레코드 건수과 관계없이 쿼리가 프라이머리 키나 유니크 키 컬럼을 동등 조건으로 조회하는 WHERE 조건절을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리 방식 (-> 유니크 인덱스 스캔(UNIQUE INDEX SCAN))

=> 중복을 허용하지 않기 때문에 동등 조건을 만족하는 레코드는 1건만 보장

다중 컬럼으로 구성된 프라이머리 키나 유니크 키 중에서 인덱스의 일부 컬럼만 조건으로 사용할 때는 const 타입의 접근 방법을 사용할 수 없다. (또는 유니크 키가 NULL을 허용하는 경우)

데이터를 실제로 읽어보기 전까지는 레코드가 1건이라는 것을 알 수 없기 때문이다.

프라이머리 키나 유니크 인덱스의 모든 컬럼을 동등 조건으로 WHERE 절에 명시하면 const 접근 방법을 사용한다.

참고: 실행 계획의 type 컬럼이 const 인 실행 계획은 옵티마이저가 쿼리를 최적화하는 단계에서 쿼리를 먼저 실행해서 통째로 상수화한다. (상수 값을 쿼리에 대입하는 식으로 최적화)

eq_ref

여러 테이블이 조인되는 쿼리의 실행 계획에서 표시된다.

처음 읽은 테이블의 컬럼 값을 그 다음 읽어야 할 테이블의 프라이머리 키나 유니크 키 컬럼의 동등 조건 검색에 사용할 때 두 번째부터 읽는 테이블의 type 컬럼에 표시된다.

조인에서 두 번째부터 읽는 테이블의 컬럼 값이 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있다.

즉, const 접근 방식의 제약 조건을 만족해야 한다.

첫 번째 라인의 dept_emp 테이블을 먼저 읽고 프라이머리 키(de.emp_no)를 employees 테이블의 프라이머리 키(e.emp_no)의 검색 조건에 사용하고 있다.
두 번째로 읽는 employees 테이블의 type: eq_ref

ref

eq_ref와 달리 조인의 순서와 관계 없이 사용되고, 프라이머리 키나 유니크 키 등의 제약 조건도 없다.

인덱스의 종류와 관계없이 동등(Equal) 조건으로 검색할 때 사용되는 접근 방식

반환되는 레코드가 반드시 1건이라는 보장이 없으므로 const나 eq_ref 보다는 느리다.

type: ref -> PRIMARY KEY(dept_no, emp_no) 컬럼 중에 dept_no 만 동등 조건으로 WHERE 절에 명시됨
ref: const -> ref 접근 방법에서 값 비교에 사용된 입력 값이 상수('d005') 였음을 의미

const, eq_ref, ref 모두 좋은 접근 방법으로 쿼리를 튜닝할 필요가 없다.

fulltext

전문 검색(Full-text Search) 인덱스를 사용해 레코드를 읽는 접근 방법

전문 검색 인덱스는 통계 정보가 관리되지 않으며, 전문 검색 인덱스를 사용하려면 전혀 다른 SQL 문법을 사용해야 한다.

테이블에 전문 검색용 인덱스가 있다면 "MATCH (...) AGAINST (...)" 구문을 사용해서 전문 검색을 실행할 수 있다.

mysql> EXPLAIN
	SELECT *
	FROM employee_name
	WHERE emp_no=10001
	  AND emp_no BETWEEN 10001 AND 10005
	  AND MATCH(first_name, last_name) AGAINST('Facello' IN BOOLEAN MODE);

WHERE 절의 3개의 조건

"emp_no=10001" 조건은 프라이머리 키를 1건만 조회하는 const 타입
"emp_no BETWEEN 10001 AND 10005" 조건은 range 타입
"MATCH(first_name, last_name) AGAINST('Facello' IN BOOLEAN MODE)" 조건은 fulltext 타입

옵티마이저는 실행 계획에서 const 타입의 조건을 선택한다.

우선순위: const > fulltext > range

경험적으로 보면 fulltext 보다 range 접근 방법이 더 빨리 처리되는 경우가 많으므로 전문 검색 쿼리를 사용할 때는 조건별로 성능을 확인하는 편이 좋다.

ref_of_null

ref 접근 방식에서 NULL 비교가 추가된 형태

unique_subquery

WHERE 조건절의 IN (subquery) 형태의 쿼리를 위한 접근 방법

서브쿼리에서 중복되지 않는 유니크한 값만 반환할 때 사용

참고: unique_subquery와 index_subquery 설명에서 사용된 예제는 semijoin 최적화 옵션을 비활성화한 상태에서 만들어진 실행 계획이다.

mysql> SET optimizer_switch='semijoin=off';

index_subquery

IN 연산자 특성상 IN (subquery) 또는 IN (상수 나열) 형태의 조건은 괄호 안에서 중복된 값을 먼저 제거해야 한다.

서브쿼리 결과의 중복된 값을 인덱스를 이용하여 제거할 수 있을 때 사용되는 접근 방법

unique_subquery vs index_subquery

unique_subquery: IN (subquery) 조건에서 subquery의 반환 값에 중복이 없으므로 별도의 중복 제거 과정이 필요하지 않음
index_subquery: IN (subquery) 조건에서 subquery의 반환 값에 중복된 값이 있을 수 있지만 인덱스를 이용해 중복을 제거할 수 있음

range

인덱스 레인지 스캔 형태의 접근 방법

인덱스를 하나의 값이 아니라 범위로 검색하는 경우

주로 <, >, IS NULL, BETWEEN, IN, LIKE 등의 연산자를 이용해 인덱스를 검색할 때 사용

참고: 이 책에서 인덱스 레인지 스캔이라고 하면 const, ref, range를 모두 묶어서 지칭한다.

인덱스를 효율적으로 사용한다
작업 범위 결정 조건으로 인덱스를 사용한다

index_merge

2개 이상의 인덱스를 이용해 각각의 검색 결과를 병합해서 처리하는 방식

Extra 컬럼에 보완적인 내용이 표시된다.

여러 인덱스를 읽어야 하므로 일반적으로 range 접근 방법보다 효율성이 떨어진다.
전문 검색 인덱스를 사용하는 쿼리에서는 index_merge가 적용되지 않는다.
index_merge 접근 방법으로 처리된 결과는 항상 2개 이상의 집합이 되기 때문에 그 두 집합의 교집합이나 합집합, 또는 중복 제거와 같은 부가 작업이 필요하다.

OR 연산자로 연결된 조건이 각각 다른 인덱스(PRIMARY, ix_firstname)를 최적으로 사용할 수 있는 조건

index

인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미하는 접근 방법

range 접근 방식처럼 효율적으로 인덱스의 필요한 부분만 읽는 것이 아니다.

range나 const, ref 같은 접근 방법으로 인덱스를 사용하지 못하는 경우
인덱스에 포함된 컬럼만으로 처리할 수 있는 쿼리인 경우 (데이터 파일을 읽지 않아도 되는 경우)
인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우 (별도의 정렬 작업을 피할 수 있는 경우)

1+2번 조건을 충족하거나 1+3번 조건을 충족하는 쿼리에서 index 방식이 사용된다.

WHERE 조건이 없으므로 인덱스 레인지 스캔(range, const, ref)을 사용할 수 없다.

정렬하려는 컬럼은 인덱스(ux_deptname)가 있으므로 별도의 정렬 처리를 피하기 위해 index 접근 방법 사용

ALL

풀 테이블 스캔을 의미하는 접근 방법

테이블을 처음부터 끝까지 읽어서 불필요한 레코드를 제거(체크 조건)하고 반환한다.

지금까지의 접근 방법으로 처리할 수 없을 때 마지막에 선택하는 가장 비효율적인 방법이다.

대용량의 레코드를 처리하는 쿼리에서 억지로 인덱스를 사용하게 튜닝된 쿼리보다 더 나은 접근 방법이기도 하다.

쿼리 튜닝이 무조건 인덱스 풀 스캔이나 테이블 풀 스캔을 사용하지 못하게 하는 것은 아니다.

일반적으로 index와 ALL 접근 방식은 작업 범위를 제한하는 조건이 아니므로 빠른 응답을 사용자에게 보내야 하는 웹 서비스 같은 온라인 트랜잭션 처리 환경에는 적합하지 않다.

참고: 리드 어헤드(Read Ahead)

한 번에 여러 페이지를 디스크로부터 읽어 들이는 InnoDB 기능

풀 테이블 스캔이나 인덱스 풀 스캔과 같은 대량의 디스크 I/O를 유발하는 작업을 위해 제공된다.

possible_keys 컬럼

옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 접근 방법에서 사용되는 인덱스 목록

쿼리 튜닝에는 크게 도움이 되지 않는다.

key 컬럼

최종 선택된 실행 계획에서 사용하는 인덱스

쿼리를 튜닝할 때 key 컬럼에 의도했던 인덱스가 표시되는지 확인하는 것이 중요하다.

index_merge -> 2개 이상의 인덱스가 표시됨
ALL -> NULL로 표시됨

참고: MySQL 서버에서 프라이머리 키는 별도의 이름을 부여할 수 없으며, 기본적으로 PRIMARY라는 이름을 가진다. 실행 계획 뿐만 아니라 쿼리의 힌트를 사용할 때도 프라이머리 키를 지칭하고 싶다면 "PRIMARY"라는 키워드를 사용하면 된다.

key_len 컬럼

쿼리를 처리하기 위해 인덱스에서 사용한 컬럼의 개수에 대한 정보

인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려준다.

PRIMARY (dept_no CHAR(4)) -> 16바이트
key_len:16 -> 프라이머리 키에서 앞쪽 16바이트만 유효하게 사용했다는 의미

참고: 이 테이블에서 사용하는 CHAR 타입은 utf8mb4 문자 집합을 사용한다. utf8mb4 문자 집합에서는 문자 하나가 차지하는 공간이 1바이트에서 4바이트까지 가변적이다. 하지만 MySQL 서버에서는 utf8mb4 문자를 위해 메모리 공간을 할당할 때 고정적으로 4바이트로 계산한다.

PRIMARY(dept_no CHAR(4), emp_no INTEGER)
key_len:20 -> dept_no 컬럼과 emp_no 컬럼 길이의 합 (16+4)

참고: MySQL에서는 NULL 이 저장될 수 있는(NULLABLE) 컬럼으로 정의하면 NULL 여부를 저장하기 위해 1바이트를 추가로 사용한다.

NOT NULL n바이트, NULL n+1바이트

ref 컬럼

참조 조건(Equal 비교 조건)으로 제공된 값

상수 -> ref: const
다른 테이블의 컬럼 값 -> ref: 테이블명.컬럼명
콜레이션 변환이나 연산을 거친 참조값 -> ref: func

사용자가 명시적으로 값을 변환할 때 뿐만 아니라 MySQL 서버가 내부적으로 값을 변환해야 할 때도 ref 컬럼에 func가 출력된다. ex) 서로 다른 타입의 컬럼을 조인, 문자 집합이 일치하지 않는 문자열 컬럼을 조인

가급적 이런 변환을 거치지 않도록 조인 컬럼의 타입은 일치시키는 것이 좋다.

rows 컬럼

실행 계획의 효율성(비용) 판단을 위해 예측한 레코드 건수

반환하는 레코드가 아닌 쿼리를 처리하기 위해 디스크로부터 읽고 체크해야 하는 레코드 건수이다.

스토리지 엔진별로 가지고 있는 통계 정보를 참조하여 옵티마이저가 산출한 예상 값 (정확하지 않음)

dept_emp 테이블의 ix_fromdate(from_date) 인덱스를 사용해서 처리할 수 있지만 풀 테이블 스캔(ALL)을 선택했다.
rows 컬럼을 확인하면 옵티마이저는 이 쿼리를 처리하기 위해 대략 331,143건의 레코드를 읽어야 할 것이라고 예측했다.
dept_emp 테이블의 전체 레코드가가 331,143건이므로 테이블의 모든 레코드를 비교해봐야 한다고 판단한 것이다.

범위 축소

대략 292건의 레코드만 읽고 체크하면 쿼리를 처리할 수 있을 것이라고 예측했으므로 인덱스 레인지 스캔(range)을 사용한다.

filtered 컬럼

rows 컬럼은 인덱스를 사용하는 조건에만 일치하는 레코드 건수를 예측한 것이다.

filtered 컬럼은 rows 레코드에 대해 나머지 조건(인덱스를 사용하지 못하는 조건)에 일치하는 비율을 예측한 것이다.

조인이 사용되는 경우 WHERE 조건에 최종적으로 일치하는 레코드 건수가 적은 테이블이 드라이빙 테이블로 선정될 가능성이 높기 때문에 filered 컬럼이 중요하다.

employees 테이블의 first_name 조건은 인덱스를 사용할 수 있다.
salaries 테이블의 salary 조건은 인덱스를 사용할 수 있다.

employees 테이블에서 인덱스 조건에 일치하는 레코드 건수(rows) 대략 233건
이 중 16.03%만 인덱스를 사용하지 못하는 hire_date 조건에 일치한다.
따라서 salaries 테이블과 조인될 레코드 건수는 대략 37건(233 * 0.1603) 정도가 된다.

employees 테이블과 조인될 salaries 테이블의 레코드 건수는 대략 368건(3314 * 0.1111)이 된다.

filtered 컬럼의 표시되는 값이 얼마나 정확한지에 따라 조인의 성능이 달라지기 때문에 8.0 부터는 히스토그램 기능이 도입됐다.

'Book > Real MySQL 8.0 上' 카테고리의 다른 글

10. 실행 계획-2 (0)	2023.01.05
09. 옵티마이저와 힌트-3 (0)	2022.12.29
09. 옵티마이저와 힌트-2 (0)	2022.12.25
09. 옵티마이저와 힌트-1 (0)	2022.12.18
08. 인덱스-2 (0)	2022.12.14