실행 계획

옵티마이저가 관리자나 사용자의 개입 없이 항상 좋은 실행 계획을 만들어내는 것은 아니다. 이러한 문제에 대해 MySQL 서버에서 보여주는 실행 게획을 일고 이해하려면 MySQL 서버가 데이터를 처리하는 로직을 이해할 필요가 있다. MySQL 서버의 실행 계획에 가장 큰 영향을 미치는 통계 정보에 대해 간략히 살펴보고, MySQL 서버가 보여주는 실행 계획을 읽는 순서와 실행 계획에 출력되는 키워드, 그리고 알고리즘에 대해 살펴보겠다.

통계 정보

MySQL 8.0 버전부터 인덱스되지 않은 컬럼들에 대해서도 데이터 분포도를 수집해서 저장하는 히스토그램(Histogram) 정보가 도입됐다. 히스토그램이 도입됐다고 해서 기존의 테이블이나 인덱스의 통계 정보가 필요한 것은 아니다. 테이블 및 인덱스에 대한 통계 정보와 히스토그램을 나누어 살펴보자.

테이블 및 인덱스 통계 정보

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

MySQL 서버의 통계 정보

MySQL 5.6 버전부터는 InnoDB 스토리지 엔진을 사용하는 각 테이블에 대한 통계 정보를 MySQL 데이터베이스의 innodb_index_stats 테이블과 innodb_table_stats 테이블로 영구적으로(Persistent) 관리할 수 있게 개선되었다. 이렇게 통계 정보를 테이블로 관리함으로써 MySQL 서버가 재시작되어도 기존의 통계 정보를 유지할 수 있게 됐다.

MySQL 5.6에서 테이블을 생성할 때는 STATS_PERSISTENT 옵션을 설정할 수 있는데 이 설정값에 따라 테이블 단위로 영구적인 통계 정보를 보관할지 말지를 결정할 수 있다.

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

통계 정보의 각 컬럼은 다음과 같은 값을 저장하고 있다.

innodb_index_stats.stat_name='n_diff_pfx%': 인덱스가 가진 유니크한 값의 개수
innodb_index_stats.stat_name='n_leaf_pages': 인덱스의 리프 노드 페이지 개수
innodb_index_stats.stat_name='size': 인덱스 트리의 전체 페이지 개수
innodb_table_stats.n_rows: 테이블의 전체 레코드 건수
innodb_table_stats.clustered_index_size: 프라이머리 키의 크기(InnoDB 페이지 개수)
innodb_table_stats.sum_of_other_index_size: 프라이머리 키를 제외한 인덱스의 크기(InnoDB 페이지 개수)

히스토그램

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

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

MySQL 8.0 버전에서 히스토그램 정보는 컬럼 단위로 관리되는데, 이는 자동으로 수집되지 않고 ANALYZE TABLE ... UPDATE HISTOGRAM 명령을 실행해 수동으로 수집 및 관리된다. 수집된 히스토그램 정보는 시스템 딕셔너리와 함께 저장되고, MySQL 서버가 시작될 때 딕셔너리의 히스토그램 정보를 information_schema 데이터베이스의 column_statistics 테이블로 로드한다. 그래서 실제 히스토그램 정보를 조회하려면 column_statistics 테이블을 SELECT해서 참조할 수 있다.

MySQL 8.0 버전에서는 다음과 같이 2종류의 히스토그램 타입이 지원된다.

Singleton(싱글톤 히스토그램): 컬럼값 개별로 레코드 건수를 관리하는 히스토그램으로, Value-Based 히스토그램 또는 도수 분포라고도 불린다.
Equi-Height(높이 균형 히스토그램): 컬럼값의 범위를 균등한 개수로 구분해서 관리하는 히스토그램으로, Height-Balanced 히스토그램이라고도 불린다.

히스토그램은 버킷(Bucket) 단위로 구분되어 레코드 건수나 컬럼값의 범위가 관리되는데, 싱글톤 히스토그램은 컬럼이 가지는 값별로 버킷이 할당되며 높이 균형 히스토그램은 개수가 균등한 컬럼값의 범위로 하나의 버킷이 할당된다. 싱글톤 히스토그램은 각 버킷이 컬럼의 값과 발생 빈도의 비율의 2개 값을 가진다. 반면 높이 균형 히스토그램은 각 버킷이 범위 시작 값과 마지막 값, 그리고 발생 빈도율과 각 버킷에 포함된 유니크한 값의 개수 등 4개의 값을 가진다.

-- 글로벌로 변경하면 MySQL 서버의 모든 쿼리가 히스토그램 사용하지 않게 설정
mysql> SET GLOBAL optimizer_switch='condition_fanout_filter=off';

-- 현재 커넥션에서 실행되는 쿼리만 히스토그램을 사용하지 않게 설정
mysql> SET SESSION optimizer_switch='condition_fanout_filter=off';

-- 현재 쿼리만 히스토그램을 사용하지 않게 설정
mysql> SELECT /*+ SET_VAR(optimizer_switch='condition_fanout_filter=off') */ * FROM ....

히스토그램을 삭제하지 않고 MySQL 옵티마이저가 히스토그램을 사용하지 않게 하려면 위처럼 optimizer_switch 시스템 변수의 값을 변경하면 된다.

히스토그램의 용도

MySQL 서버에 히스토그램이 도입되기 이전에도 테이블과 인덱스에 대한 통계 정보는 존재했다. 하지만 테이블의 전체 레코드 건수와 인덱스된 컬럼이 가지는 유니크한 값의 개수 정도로 레코드 1000건이고 유니크한 값이 100개였다면 동등 비교 검색을 하면 대략 10개의 레코드가 일치할 것이라고 예측했다.

하지만 실제 응용 프로그램의 데이터는 항상 균등한 분포도를 가지지 않았기 때문에 기존 통계 정보는 이런 부분을 고려하지 못했다. 따라서 히스토그램이 도입되었고, 특정 컬럼이 가지는 모든 값에 대한 분포도 정보를 가지지는 않지만 각 범위(버킷)별로 레코드의 건수와 유니크한 값의 개수 정보를 가지기 때문에 훨씬 정확한 예측을 할 수 있다. employees 테이블의 birth_date 컬럼에 대해 히스토그램이 없을 때와 있을 때의 예측치가 얼마나 달라지는지 살펴보자.

위 결과를 보면 옵티마이저는 first_name='Zita' 조건에 일치하는 레코드가 224건 있고, 그중에서 대략 11.11% 24.8명 정도의 birth_date가 1950년대 출생일 것으로 예측했다.

birth_date 컬럼에 히스토그램 정보를 수집하고, 동일한 쿼리의 실행 계획을 살펴보면 대략 61.10%인 136.8명이 1950년대 출생일 것으로 예측했다. 실제 데이터를 조회하면 63.84%인 143명이 1950년대 출생인 것을 알 수 잇다. 단순 통계 정보만 이용한 경우와 히스토그램을 이용한 경우의 차이가 매우 큰 것을 확인할 수 있다.

즉, 히스토그램 정보가 없으면 옵티마이저는 데이터가 균등하게 분포되어 있을 것으로 예측하지만 히스토그램이 있으면 특정 범위의 데이터가 많고 적음을 식별할 수 있다.

히스토그램과 인덱스

MySQL 서버에서는 쿼리의 실행 계획을 수립할 때 사용 가능한 인덱스들로부터 조건절에 일치하는 레코드 건수를 대략 파악하고 최종적으로 가장 나은 실행 계획을 선택한다. 이때 조건절에 일치하는 레코드 건수를 예측하기 위해 옵티마이저는 실제 인덱스의 B-Tree를 샘플링해서 살펴보는데 이 작업을 인덱스 다이브(Index Dive)라고 한다.

SELECT * FROM WHERE first_name = 'Tonny' AND birth_date BETWEEN '1954-01-01' AND '1955-01-01';

일반적으로 쿼리의 검색 조건으로 많이 사용되는 컬럼에 대해서 인덱스를 생성한다. 그러면 인덱스된 컬럼에 대해 히스토그램 정보를 수집해 두는 것이 좋을지 고민스러울 수 있다. birth_date 컬럼은 인덱스가 없으므로 실행 계획에 큰 영향을 미치지 않을 것이고, first_name 컬럼에 히스토그램이 수집되어 있다고 하더라도 MySQL 8.0 서버에서는 인덱스된 컬럼을 검색 조건으로 사용하는 경우 그 컬럼의 히스토그램은 사용하지 않고, 실제 인덱스 다이브를 통해 직접 수집한 정보를 활용한다. 이는 실제 검색 조건의 대상값에 대한 샘플링을 실행하는 것이므로 항상 히스토그램보다 정확한 결과를 기대할 수 있기 때문이다. 그래서 MySQL 8.0 버전에서 히스토그램은 주로 인덱스되지 않은 컬럼에 대한 뎅터 분포도를 참조하는 용도로 사용된다.

코스트 모델(Cost Model)

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

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

MySQL 서버는 사용자의 쿼리에 대해 다양한 작업이 얼마나 필요한지 예측하고 전체 작업 비용을 계산한 결과를 바탕으로 최적의 실행 계획을 찾는데 전체 쿼리의 비용을 계산하는데 필요한 단위 작업들의 비용을 코스트 모델(Cost Model)이라고 한다.

MySQL 8.0 서버의 코스트 모델은 다음 2개 테이블에 저장되어 있는 설정값을 사용하는데, 두 테이블 모두 mysql DB에 존재한다.

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

server_cost 테이블과 engine_cost 테이블은 공통으로 다음 5개의 컬럼을 가지고 있고, engine_cost 테이블은 에추가로 2개 컬럼을 더 가지고 있다.

cost_name: 코스트 모델의 각 단위 작업
default_value: 각 단위 작업의 비용(기본값이며, 이 값은 MySQL 서버 소스 코드에 설정된 값)
cost_value: DBMS 관리자가 설정한 값(이 값이 NULL이면 MySQL 서버는 default_value 컬럼의 비용 사용)
last_updated: 단위 작업의 비용이 변경된 시점
comment: 비용에 대한 추가 설명
engine_name(engine_cost 테이블만): 비용이 적용된 스토리지 엔진
device_type(engine_cost 테이블만): 디스크 타입으로 MySQL 8.0에서는 아직 이 컬럼의 값을 활용 X

server_cost

disk_temptable_create_cost: 디스크 임시 테이블 생성
dist_temptable_row_cost: 디스크 임시 테이블의 레코드 읽기
key_compare_cost: 인덱스 키 비교
memory_temptable_create_cost: 메모리 임시 테이블 생성
memory_temptable_row_cost: 메모리 임시 테이블의 레코드 읽기
row_evaluate_cost: 레코드 비교(스토리지 엔진이 반환한 레코드가 쿼리의 조건에 일치하는지 평가하는 단위 작업)

engine_cost

io_block_read_cost: 디스크 데이터 페이지 읽기
memory_block_read_cost: 메모리 데이터 페이지 읽기

실행 계획 출력 포맷

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

쿼리의 실행 시간 확인

MySQL 8.0.18 버전부터는 쿼리의 실행 계획과 단계별 소요된 시간 정보를 확인할 수 있는 EXPLAIN ANALYZE 기능이 추가됐다. EXPLAIN ANALYZE 명령은 항상 결과를 TREE 포맷으로 보여준다.

위 쿼리의 실행 계획을 봤을 때 들여쓰기가 같은 레벨에서는 상단에 위치한 라인이 먼저, 다른 레벨에서는 가장 안쪽에 위치한 라인이 먼저 실행되기 때문에 다음과 같은 실행 순서를 가진다.

Index lookup on e using ix_firstname
Index lookup on s using PRIMARY
Filter
Nested loop inner join
Aggregate using temporary table
Table scan on <temporary>

위의 상세한 실행 계획과 순서를 묶어서 다음과 같이 한글로 간단하게 실행 계획을 풀어 쓸 수 있다.

employees 테이블의 ix_firstname 인덱스를 통해 first_name='Matt' 조건에 일치하는 레코드를 찾고
salaries 테이블의 PRIMARY 키를 통해 emp_no가 (1)번 결과의 emp_no와 동일한 레코드를 찾아서
((s.salary > 50000) and (s.from_date <= DATE'1990-01-01') and (s.to_date > DATE'1990-01-01')) 조건에 일치하는 건만 가져와
1번과 3번의 결과를 조인해서
임시 테이블에 결과를 저장하면서 GROUP BY 집계를 실행하고
임시 테이블의 결과를 읽어서 결과를 반환한다.

EXPLAIN ANALYZE 명령의 결과에는 단계별로 실제 소요된 시간(actual time)과 처리한 레코드 건수(rows), 반복 횟수(loops)가 표시된다. 실행 계획에서 2번에 나열된 필드들의 의미를 한 번 살펴보자.

actual time=0.00535..0.00747: employees 테이블에서 읽은 emp_no 값을 기준으로 salaries 테이블에서 일치하는 레코드를 검색하는데 걸린 시간(밀리초)을 의미한다. 첫 번째는 평균 시간, 두 번째는 마지막 레코드를 가져오는데 걸린 평균 시간를 의미한다.
rows=9.53: employees 테이블에서 읽은 emp_no에 일치하는 salaries 테이블의 평균 레코드 건수를 의미한다.
loops=233: employees 테이블에서 읽은 emp_no를 이용해 salaries 테이블의 레코드를 찾는 작업이 반복된 횟수를 의미한다.

이렇게 쿼리의 실행 계획이 아주나쁜 경우라면 EXPLAIN 명령으로 먼저 실행 계획만 확인해서 어느 정도 튜닝한 후 EXPLAIN ANALYZE 명령을 실행하는 것이 좋다.

실행 계획 분석

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

EXPLAIN 명령을 아무런 옵션 없이 실행하면 쿼리 문장의 특성에 따라 표 형태로 된 1줄 이상의 결과가 표시된다. 표의 각 라인(레코드)은 서브쿼리로 임시 테이블을 생성한 경우 그 임시 테이블가지 포함한 쿼리 문장에서 사용된 테이블의 개수만큼 출력된다. 실행 순서는 위에서 아래로, id 컬럼의 값이 작을수록 쿼리의 바깥(Outer0 부분이거나 먼저 접근한 테이블이고, id 컬럼의 값이 클수록 쿼리의 안쪽(Inner) 부분 또는 나중에 접근한 테이블에 해당한다.

각 컬럼이 어떤 것을 의미하고, 각 컬럼에 어떤 값들이 출력될 수 있는지 확인해보자.

id 컬럼

실행 계획에서 가장 왼쪽에 표시되는 id 컬럼은 단위 SELECT 쿼리별로 부여되는 식별자 값이다. 위 예제처럼 SELECT 문장은 하나인데, 여러 개의 테이블이 조인되는 경우에는 id 값이 증가하지 않고 같은 id 값이 부여된다.

위 쿼리 문장은 3개의 단위 SELECT 쿼리로 구성되어 각기 다른 id 값을 지닌 것을 확인할 수 있지 여기서 한 가지 주의해야 할 것은 실행 계획의 id 컬럼이 테이블의 접근 순서를 의미하지는 않는다.

select_type 컬럼

각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 컬럼이다. select_type 컬럼에 표시될 수 있는 값은 다음과 같다.

SIMPLE

UNION이나 서브쿼리를 사용하지 않는 단순한 SELECT 쿼리인 경우 해당 쿼리 문장의 select_type은 SIMPLE로 표시된다.

PRIMARY

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

UNION

UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번쨰를 제외한 두 번쨰 이후 단위 SELECT 쿼리의 select_type은 UNION으로 표시된다. UNION의 첫 번째 단위 SELECT는 select_type이 UNION이 아니라 UNION되는 쿼리 결과들을 모아서 저장하는 임시 테이블(DERIVED)이 select_type으로 표시된다. 위 쿼리의 실행 계획을 보면 첫 번째(e1 테이블)만 UNION이 아니고, 나머지 2개는 모두 UNION으로 표시되어 있다.

DEPENDENT UNION

DEPENDENT UNION 또한 UNION select_type과 같이 UNION이나 UNION ALL로 집합을 결합하는 쿼리에서 표시된다.

UNION RESULT

UNION RESULT는 UNION 결과를 담아두는 테이블을 의미한다. MySQL 8.0 버전부터 UNION ALL의 경우 임시 테이블을 사용하지 않도록 기능이 개선되었지만, UNION 또는 UNION DISTINCT는 여전히 임시 테이블에 결과를 버퍼링한다. 실행 계획상에서 임시 테이블을 가리키는 라인의 select_type이 UNION RESULT다. UNION RESULT는 실제 쿼리에서 단위 쿼리가 아니기 때문에 별도의 id 값은 부여되지 않는다.

SUBQUERY

select_type의 SUBQUERY는 FROM절 이외에서 사용되는 서브쿼리만을 의미한다. MySQL 서버의 실행 계획에서 FROM 절에 사용된 서브쿼리는 select_type이 DERIVED로 표시되고, 그 밖의 위치에서 사용된 서브쿼리는 전부 SUBQUERY라고 표시된다.

DEPENDENT SUBQUERY

서브쿼리가 바깥쪽(Outer) SELECT 쿼리에서 정의된 컬럼을 사용하는 경우, select_type에 DEPENDENT SUBQUERY라고 표시된다. 위 쿼리는 안쪽(Inner)의 서브쿼리 결과가 바깥쪽(Outer) SELECT 쿼리의 컬럼에 의존적이기 때문에 DEPENDENT라는 키워드가 붙는다. 또한 DEPENDENT UNION과 같이 DEPENDENT SUBQUERY 또한 외부 쿼리가 먼저 수행된 후 내부 쿼리(서브쿼리)가 실행되어야 하므로 (DEPENDENT 키워드가 없는) 일반 서브쿼리보다는 처리 속도가 느릴 때가 많다.

DERIVED

DERIVED는 단위 SELECT 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블을 생성하는 것을 의미한다. MySQL 5.6 버전부터는 옵티마이저 옵션(optimizer_switch 시스템 변수)에 따라 FROM 절의 서브쿼리를 외부 쿼리와 통합하는 형태의 최적화가 수행되기도 한다. 파생 테이블에는 인덱스가 전혀 없었어서 다른 테이블과 조인할 때 성능상 불리할 때가 많았는데 MySQL 5.6 버전부터는 옵티마이저 옵션에 따라 쿼리의 특성에 맞게 임시 테이블에도 인덱스를 추가해서 만들 수 있게 최적화되었다.

MySQL 서버는 버전이 업그레이드되면서 조인 쿼리에 대한 최적화가 많이 성숙해진 상태이기 때문에 가능하다면 DERIVED 형태의 실행 계획을 조인으로 해결할 수 있게 쿼리를 바꿔주는 것이 좋다. MySQL 8.0 버전부터는 FROM 절의 서브쿼리에 대한 최적화도 많이 개선되어 가능하다면 불필요한 서브쿼리는 조인으로 쿼리를 재작성해서 처리한다.

DEPENDENT DERIVED

MySQL 8.0 버전부터는 래터럴 조인(LATERAL JOIN) 기능이 추가되면서 FROM 절의 서브쿼리에서도 외부 컬럼을 참조할 수 있게 되었다. 위 쿼리가 래터럴 조인을 활용한 예시로, employees 테이블의 레코드 1건당 salaries 테이블의 레코드를 최근 순서대로 최대 2건까지만 가져와서 조인을 실행한다.

UNCACHEABLE SUBQUERY

하나의 쿼리 문장에 서브쿼리가 하나만 있더라도 실제 그 서브쿼리가 한 번만 실행되는 것은 아니다. 그런데 조건이 똑같은 서브쿼리가 실행될 때는 다시 실행하지 않고 이전의 실행 결과를 그대로 사용할 수 있게 서브쿼리의 결과를 내부적인 캐시 공간에 담아둔다.

SUBQUERY는 바깥쪽(Outer)의 영향을 받지 않으므로 처음 한 번만 실행해서 그 결과를 캐시하고 필요할 때 캐시된 결과를 이용한다.
DEPENDENT SUBQUERY는 의존하는 바깥쪽(Outer) 쿼리의 컬럼의 값 단위로 캐시해두고 사용한다.

select_type이 SUBQUERY인 경우와 UNCACHEABLE SUBQUERY는 이 캐시를 사용할 수 있느냐 없느냐의 차이인데 캐시 자체를 사용하지 못하는 경우에 UNCACHEABLE SUBQUERY가 표시되고 다음과 같은 것들이 있다.

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

위는 사용자 변수(@status)가 사용된 쿼리인데 WHERE 절에 사용된 단위 쿼리의 select_type이 UNCACHEABLE SUBQUERY로 표시된 것을 확인할 수 있다.

UNCACHEABLE UNION

UNCACHEABLE UNION이란 UNION과 UNCACHEABLE 키워드의 속성이 혼합된 select_type을 의미한다.

MATERIALIZED

주로 FROM 절이나 IN(subquery) 형태의 쿼리에 사용된 서브쿼리의 최적화를 위해 사용된다. MySQL 5.6 버전까지는 employees 테이블을 읽어 employees 테이블의 레코드마다 salaries 테이블을 읽는 서브쿼리가 실행되는 형태로 처리했지만 MySQL 5.7 버전부터는 서브쿼리의 내용을 임시 테이블로 구체화(Materialization)한 후, 임시 테이블과 employees 테이블을 조인하는 형태로 최적화되어 처리된다.

table 컬럼

table 컬럼에 <derived N> 또는 <UNION M,N>과 같이 <>로 둘러싸인 이름이 명시되는 경우가 많은데, 이 테이블은 임시 테이블을 의미한다. 또한 <> 안에 항상 표시되는 숫자는 단위 SELECT 쿼리의 id 값을 지칭한다.

실행 계획의 id 컬럼과 select_type, table 컬럼을 살펴봤다. 이 3개의 컬럼은 실행 계획의 각 라인에 명시된 테이블이 어떤 순서로 실행되는지를 판단하는 근거를 표시해준다. 그러면 이 3개의 컬럼만으로 위의 실행 계획을 분석해보자.

첫 번째 라인의 테이블이 <derived2>라는 것으로 보아 이 라인보다 id 값이 2인 라인이 먼저 실행되고 그 결과가 파생 테이블로 준비되어야 한다는 것을 알 수 있다.
세 번째 라인(id 값이 2인 라인)을 보면 select_type 컬럼의 값이 DERIVED로 표시되어 있다. 즉, 이 라인은 table 컬럼에 표시된 dept_emp 테이블을 읽어서 파생 테이블을 생성하는 것을 알 수 있다.
세 번째 라인의 분석이 끝났으므로 다시 실행 계획의 첫 번째 라인으로 돌아가자
첫 번째 라인과 두 번째 라인은 같은 id 값을 가지고 있는 것으로 2개 테이블(첫 번째 라인의 <derived2>와 두 번째 라인의 e 테이블)이 조인되는 쿼리라는 사실을 알 수 있다. 그런데 <derived2> 테이블이 e 테이블보다 먼저(윗라인에) 표시됐기 떄문에 <derived2>가 드라이빙 테이블이 되고, e 테이블이 드리븐 테이블이 된다. 즉, <derived2> 테이블을 먼저 읽어서 e 테이블로 조인을 실행했다는 것을 알 수 있다.

partitions 컬럼

CREATE TABLE employees_2 (
	emp_no int NOT NULL,
    birth_date DATE NOT NULL,
    first_name VARCHAR(14) NOT NULL,
    last_name VARCHAR(16) NOT NULL,
    gender ENUM('M', 'F') NOT NULL,
    hire_date DATE NOT NULL,
    PRIMARY KEY (emp_no, hire_date)
) PARTITION BY RANGE COLUMNS(hire_date)
(PARTITION p1986_1990 VALUES LESS THAN ('1990-01-01'),
 PARTITION p1991_1995 VALUES LESS THAN ('1996-01-01'),
 PARTITION p1996_2000 VALUES LESS THAN ('2000-01-01'),
 PARTITION p2001_2005 VALUES LESS THAN ('2006-01-01'));
 
 INSERT INTO employees_2 SEELCT * FROM employees;

hire_date 컬럼의 값이 1999년 11월 15일과 2000년 1월 15일 사이인 레코드를 검색하면 위 실행 결과가 나오는 것을 확인할 수 있고, 이 쿼리에서 필요로 하는 데이터는 p1996_2000과 p2001_2005 파티션에만 있다는 것을 알 수 있다. 그래서 실행 계획에서도 나머지 파티션에 대해서는 어떻게 접근할지 데이터 분포가 어떠한지 등의 분석을 실행하지 않는다. 이처럼 파티션이 여러 개인 테이블에서 불필요한 파티션을 빼고 쿼리를 수행하기 위해 접근해야 할 것으로 판단되는 테이블만 골라내는 과정을 파티션 프루닝(Partition pruning)이라고 한다.

이 실행 계획에서 한 가지 재미있는 부분은 type 컬럼의 값이 ALL이라는 것이다. 이는 풀 테이블 스캔으로 쿼리가 처리된다는 것인데 어떻게 풀 테이블 스캔으로 테이블의 일부만 읽을 수 있는 것일까? 그 이유는 MySQL을 포함한 대부분의 RDBMS에서 지원하는 파티션은 물리적으로 개별 테이블처럼 별도의 저장 공간을 가지기 때문에 일부만 풀 스캔을 실행하게 된다.

type 컬럼

쿼리의 실행 계획에서 type 이후의 컬럼은 MySQL 서버가 각 레코드를 어떤 방식으로 읽었는지를 나타낸다. 여기서 방식은 인덱스를 사용해 레코드를 읽었는지, 아니면 테이블을 처음부터 끝까지 풀 테이블 스캔으로 읽었는지 등을 의미한다. type 컬럼의 값은 각 테이블의 접근 방법(Access type)으로 해석하면 되고, 쿼리를 튜닝할 때 인덱스를 효율적으로 사용하는지 확인하는 것이 중요하므로 반드시 체크해야할 중요한 정보이다. type 컬럼은 다음과 같은 값을 가진다.

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

위 12개 접근 방법 중 ALL을 제외한 나머지는 모두 인덱스를 사용하는 접근 방법이다. ALL은 테이블을 처음부터 끝까지 읽어서 레코드를 가져오는 풀 테이블 스캔 접근 방법을 의미한다. 또한 index_merge를 제외한 나머지 접근 방법은 하나의 인덱스만 사용하고 위 표시된 각 접근 방법은 성능이 빠른 순서대로 나열된 것이다.

system

레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블을 참조하는 형태의 접근 방법을 system이라고 한다. 이 접근 방법은 InnoDB 스토리지 엔진을 사용하는 테이블에서는 나타나지 않고, MyISAM이나 MEMORY 테이블에서만 사용되는 접근 방법이다. InnoDB 테이블에서는 ALL이나 index로 표시될 가능성이 크다.

const

테이블의 레코드 건수와 관계없이 쿼리가 프라이머리 키나 유니크 키 컬럼을 이용하는 WHERE 조건절을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리 방식을 const라고 한다. 다른 DBMS에서는 이를 유니크 인덱스 스캔(UNIQUE INDEX SCAN)이라고도 표현한다.

eq_ref

eq_ref 접근 방법은 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시된다. 조인에서 처음 읽은 테이블의 컬럼값을, 그다음 읽어야 할 테이블의 프라이머리 키나 유니크 컬럼의 검색 조건에 사용할 때를 가리켜 eq_ref라고 한다. 이때 두 번째 이후에 읽는 테이블의 type 컬럼에 eq_ref가 표시된다. 또한 두 번쨰 이후에 읽히는 테이블을 유니크 키로 검색할 때 그 유니크 인덱스는 NOT NULL이어야 하며, 다중 컬럼으로 만들어진 프라이머리 키나 유니크 인덱스라면 인덱스의 모든 컬럼이 비교 조건에 사용되어야만 eq_ref 접근 방법이 사용될 수 있다. 즉, 조인에서 두 번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있는 접근 방법이다.

ref

ref 접근 방법은 eq_ref와 달리 조인의 순서와 관계없이 사용되며, 또한 프라이머리 키나 유니크 키 등의 제약 조건도 없다. 인덱스의 종류와 관계없이 동등(Equal) 조건으로 검색할 때는 ref 접근 방법이 사용된다. ref 타입은 반환되는 레코드가 1건이라는 보장이 없으므로 const나 eq_ref보다는 빠르지 않다. 하지만 동등 조건으로만 비교되므로 매우 빠른 레코드 조회 방법이다. const, eq_ref, ref에 대해서 다시 살펴보면 다음과 같고, 공통점으로 비교 연산자는 동등 비교 연산자이어야 한다는 것이다.

const: 조인의 순서와 관계없이 프라이머리 키나 유니크 키의 모든 컬럼에 대해 동등(Equal) 조건으로 검색(반드시 1건의 레코드만 반환)
eq_req: 조인에서 첫 번째 읽은 테이블의 컬럼값을 이용해 두 번째 테이블을 프라이머리 키나 유니크 키로 동등(Equal) 조건 검색(두 번째 테이블은 반드시 1건의 레코드만 반환)
ref: 조인의 순서와 인덱스의 종류에 관계없이 동등(Equal) 조건으로 검색(1건의 레코드만 반환된다는 보장이 없어도 됨)

fulltext

fulltext 방법은 MySQL 서버의 전문 검색(Full-text Search) 인덱스를 사용해 레코드를 읽는 접근 방법을 의미한다. 지금 살펴보는 type의 순서가 일반적인 처리 성능의 순서이긴 하지만 실제로 데이터의 분포나 레코드의 건수에 따라 빠른 순서는 달라질 수 있다.

MySQL 서버에서 전문 검색 조건은 우선순위가 상당히 높다. 전문 검색은 MATCH (...) AGAINST (...) 구문을 사용해서 실행하는데, 이때 반드시 해당 테이블에 전문 검색용 인덱스가 준비되어 있어야만 한다. 위 쿼리의 실행 계획을 보면 첫 번째 조건이 아니라 전문 검색 조건인 두 번째 조건을 선택했다. 일반적으로 쿼리에 전문 검색 조건을 사용하면 MySQL 서버는 주저 없이 fulltext 접근 방법을 사용한다. 하지만 전문 검색 인덱스를 이용하는 fulltext보다 일반 인덱스를 이용하는 range 접근 방법이 더 빨리 처리되는 경우가 많기 때문에 전문 검색 쿼리를 사용할 때는 조건별로 성능을 확인해보는 것이 좋다.

ref_or_null

이 접근 방법은 ref 접근 방법과 같은데, NULL 비교가 추가된 형태다. 접근 방법의 이름 그대로 ref 방식 또는 NULL 비교 접근 방법을 의미한다.

unique_subquery

WHERE 조건절에서 사용될 수 있는 IN (subquery) 형태의 쿼리를 위한 접근 방법이다. unique_subquery의 의미 그대로 서브쿼리에서 중복되지 않는 유니크한 값만 반환할 때 이 접근 방법을 사용한다.

index_subquery

IN 연산자의 특성상 IN(subquery) 또는 IN(상수 나열) 형태의 조건은 괄호 안에 있는 값의 목록에서 중복된 값이 먼저 제거되어야 한다. 업무 특성상 IN(subquery)에서 subquery가 중복된 값을 반환할 수도 있다. 이때 서브쿼리 결과의 중복된 값을 인덱스를 이용해서 제거할 수 있을 때 index_subquery 접근 방법이 사용된다. index_subquery와 unique_subquery 접근 방법의 차이는 다음과 같다.

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

range

range는 인덱스 레인지 스캔 형태의 접근 방법이다. range는 인덱스를 하나의 값이 아니라 범위로 검색하는 경우를 의미하는데, 주로 "<, >, IS NULL, BETWEEN, IN, LIKE" 등의 연산자를 이용해 인덱스를 검색할 때 사용된다. 일반적으로 애플리케이션의 쿼리가 가장 많이 사용하는 접근 방법인데, 위 순서를 보면 상당히 우선순위가 낮다. 하지만 얼마나 많은 레코드를 필요로 하느냐에 따라 차이는 있겠지만 range 접근 방법도 상당히 빠르며, 모든 쿼리가 이 접근 방법만 사용해도 최적의 성능이 보장된다고 볼 수 있다.

index_merge

index_merge 접근 방법은 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후, 그 결과를 병합해서 처리하는 방식이다. 하지만 index_merge 접근 방법이 사용되는 경우를 생각해보면 그렇게 효율적으로 작동하는 것은 아니고, 다음과 같은 특징이 있다.

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

두 개의 조건이 OR 연산자로 연결된 쿼리이다. 그런데 OR로 연결된 두 개 조건이 모두 각각 다른 인덱스를 최적으로 사용할 수 있는 조건이다. 그래서 MySQL 옵티마이저는 "emp_no BETWEEN 10001 AND 10004" 조건은 employees 테이블의 프라이머리 키를 이용해 조회하고, "first_name='Smith'" 조건은 ix_firstname 인덱스를 이용해 조회한 후 두 결과를 병합하는 형태로 처리하는 실행 계획을 만들어 낸다.

index

index 접근 방법은 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미한다. range 접근 방법과 같이 효율적으로 인덱스의 필요한 부분만 읽는 것을 의미하는 것은 아니라는 점을 주의해야 한다. index 접근 방법은 테이블을 처음부터 끝까지 읽는 풀 테이블 스캔 방식과 비교했을 때 비교하는 레코드 건수는 같다. 하지만 인덱스는 일반적으로 데이터 파일 전체보다 크기가 작으므로 인덱스 풀 스캔 시 풀 테이블 스캔보다 빠르게 처리되며, 쿼리의 내용에 따라 정렬된 인덱스의 장점을 이용할 수 있으므로 훨씬 효율적이라 할 수 있다. index 접근 방법은 다음 조건 가운데 (첫 번째 + 두 번째) 조건을 충족하거나 (첫 번째 + 세 번째) 조건을 충족하는 쿼리에서 사용되는 읽기 방식이다.

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

ALL

ALL 접근 방법은 풀 테이블 스캔을 의미하는 접근 방법이다. 테이블을 처음부터 끝까지 전부 읽어서 불필요한 레코드를 제거(체크 조건이 존재할 때)하고 반환한다. 다른 DBMS와 같이 InnoDB도 풀 테이블 스캔이나 인덱스 풀 스캔과 같은 대량의 디스크 I/O를 유발하는 작업을 위해 한꺼번에 많은 페이지를 읽어 들이는 기능을 제공한다. 일반적으로 index와 ALL 접근 방법은 작업 범위를 제한하는 조건이 아니므로 빠른 응답을 사용자에게 보내야 하는 웹 서비스 등과 같은 온라인 트랜잭션 처리 환경에는 적합하지 않다. 테이블이 매우 작지 않다면 실제 테이블에 데이터를 어느 정도 저장한 상태에서 쿼리의 성능을 확인해 보고 적용하는 것이 좋다.

possible_keys 컬럼

MySQL 옵티마이저는 쿼리를 처리하기 위해 여러 가지 처리 방법을 고려하고 그중에서 비용이 가장 낮을 것으로 예상하는 실행 계획을 선택해 쿼리를 실행한다. 하지만 possible_keys 컬럼에 있는 내용은 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 접근 방법에서 사용되는 인덱스의 목록이기 때문에 쿼리 튜닝하는 데 크게 도움이 되지는 않는다. 따라서 실행 계획을 확인할 때는 possible_keys 컬럼은 특별한 경우를 제외하고는 그냥 무시해도 된다. 절대 possible_keys 컬럼에 인덱스 이름이 나열되었다고 해서 그 인덱스를 사용한다고 판단하지 않도록 주의해야 한다.

key 컬럼

possible_keys 컬럼의 인덱스가 사용 후보였던 반면, key 컬럼에 표시되는 인덱스는 최종 선택된 실행 계획에서 사용하는 인덱스를 의미한다. 그러므로 쿼리를 튜닝할 때는 key 컬럼에 의도했던 인덱스가 표시되는지 확인하는 것이 중요하다. type 컬럼이 index_merge가 아닌 경우에는 반드시 테이블 하나당 하나의 인덱스만 이용할 수 있고, index_merge 실행 계획인 경우에는 2개 이상의 인덱스가 ","로 구분되어 표시된다. 그리고 type이 ALL일 떄는 인덱스를 전혀 사용하지 못하기 때문에 key 컬럼은 NULL로 표시된다.

key_len 컬럼

key_len 컬럼은 매우 중요한 정보 중 하나다. 실행 계획의 key_len 컬럼의 값은 쿼리를 처리하기 위해 다중 컬럼으로 구성된 인덱스에서 몇 개의 컬럼까지 사용했는지 알려준다. 더 정확하게는 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려주는 값이다. 그래서 다중 컬럼 인덱스뿐만 아니라 단일 컬럼으로 만들어진 인덱스에서도 같은 지표를 제공한다.

위 key_len 컬럼의 값을 보면 16으로 표시되어 있는 것을 확인할 수 있다. dept_emp 테이블은 (dept_no, emp_no)으로 구성된 프라이머리 키를 가지는 테이블로 dept_no 컬럼의 타입이 CHAR(4)이기 때문에 프라이머리 키에서 앞쪽 16바이트만 유효하게 사용했다는 의미다. 이 테이블의 dept_no 컬럼은 utf8mb4 문자 집합을 사용하고, 문자 하나가 차지하는 공간이 1바이트에서 4바이트까지 가변적이다. 하지만 MySQL 서버가 utf8mb4 문자를 위해 메모리 공간을 할당해야 할 때는 문자와 관계없이 고정적으로 4바이트로 계산한다. 그래서 위의 실행 계획에서 ken_len 컬럼의 값으로 16바이트(4*4 바이트)가 표시된 것이다.

emp_no의 컬럼 타입은 INTEGER이며, INTEGER 타입은 4바이트를 차지한다. 따라서 key_len 컬럼이 dept_no 컬럼의 길이와 emp_no 컬럼의 길이의 합인 20으로 표시된 것을 확인할 수 있다.

만약 NULLABLE이 가능한 컬럼의 타입을 인덱스를 통해서 조회하는 경우에는 예를 들어 DATE 타입은 3바이트를 사용하지만 NOT NULL이 아닌 컬럼에서는 컬럼의 값이 NULL인지 아닌지를 저장하기 위해 1바이트를 추가로 더 사용한다. 그래서 위 실행 계획을 보면 to_date 컬럼을 인덱스로 사용했지만 ken_len 컬럼의 값이 3이 아닌 4인 이유이다.

ref 컬럼

접근 방법이 ref면 참조 조건(Equal 비교 조건)으로 어떤 값이 제공됐는지 보여준다. 상수값을 지정했다면 ref 컬럼의 값은 const로 표시되고, 다른 테이블의 컬럼값이면 그 테이블명과 컬럼명이 표시된다. 가끔 쿼리의 실행 계획에서 ref 컬럼의 값이 func라고 표시될 때가 있는데 이는 참조용으로 사용되는 값을 그대로 사용한 것이 아니라 콜레이션 변환이나 값 자체의 연산을 거쳐서 참조됐다는 것을 의미한다.

위 쿼리는 employees 테이블과 dept_emp 테이블을 조인하는데, 조인 조건에서 사용된 emp_no 컬럼의 값에 대해 아무런 변환이나 가공도 수행하지 않아 ref 컬럼에 조인 대상 컬럼의 이름이 그대로 표시된 것을 확인할 수 있다.

위 쿼리는 dept_emp 테이블을 읽어서 emp_no 값에서 1을 뺀 값으로 employees 테이블과 조인하기 때문에 ref 값이 조인 컬럼의 이름이 아닌 func라고 표시되는 것을 확인할 수 있다. 이렇게 사용자가 명시적으로 값을 변환할 때뿐만 아니라 MySQL 서버가 내부적으로 값을 변환해야 할 때도 ref 컬럼에는 func가 출력된다. 문자집합이 일치하지 않는 두 문자열 컬럼을 조인한다거나 숫자 타입의 컬럼과 문자열 타입의 컬럼을 조인할 때가 대표적인 예이다. 가능하다면 MySQL 서버가 이런 변환을 하지 않아도 되게 조인 컬럼의 타입은 일치시키는 편이 좋다.

rows 컬럼

MySQL 옵티마이저는 각 조건에 대해 가능한 처리 방식을 나열하고, 각 처리 방식의 비용을 비교해 최종적으로 하나의 실행 계획을 수립한다. 이때 각 처리 방식이 얼마나 많은 레코드를 읽고 비교해야 하는지 예측해서 비용을 산정한다. 대상 테이블에 얼마나 많은 레코드가 포함되어 있는지 또는 각 인덱스 값의 분포도가 어떤지를 통계 정보를 기준으로 조사해서 예측한다.

MySQL 실행 계획의 rows 컬럼값은 실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여준다. 즉, 이 값은 각 스토리지 엔진별로 가지고 있는 통계 정보를 참조해 MySQL 옵티마이저가 산출해 낸 예상값이라서 정확하지는 않다. 그리고 rows 컬럼에 표시되는 값은 반환하는 레코드의 예측치가 아니라 쿼리를 처리하기 위해 얼마나 많은 레코드를 읽고 체크해야 하는지를 의미하기 때문에 rows 컬럼에 출력되는 값과 실제 쿼리 결과 반환된 레코드 건수는 일치하지 않는 경우가 많다.

위 쿼리의 실행 계획을 보면 type이 range로 인덱스 레인지 스캔을 사용한다. 즉, 옵티마이저는 from_date 컬럼의 값이 '2002-07-01'보다 크거나 같은 레코드가 292건만 존재할 것으로 예측했고, 이는 전체 테이블 건수와 비교하면 8.8%밖에 되지 않는다. 그래서 최종적으로 ix_fromdate 인덱스를 range 방식(인덱스 레인지 스캔)으로 처리한 것이다. 또한 인덱스에 포함된 from_date 컬럼이 DATE 타입이므로 key_len에는 3바이트로 표시된 것을 확인할 수 있다.

filtered 컬럼

옵티마이저는 각 테이블에서 일치하는 레코드 개수를 가능하면 정확히 파악해야 좀 더 효율적인 실행 계획을 수립할 수 있다. 실행 계획에서 rows 컬럼의 값은 인덱스를 사용하는 조건에만 일치하는 레코드 건수를 예측한 것이다. 하지만 대부분 쿼리에서 WHERE 절에 사용되는 조건이 모두 인덱스를 사용할 수 있는 것은 아니다.

위 쿼리는 employees 테이블과 salaries 테이블을 조인한다. employees 테이블의 "e.first_name='Matt'" 조건은 인덱스를 사용할 수 있으며, salaries 테이블은 's.salary BETWEEN 50000 AND 60000" 조건이 인덱스를 사용할 수 있따. 이 경우에은 employees 테이블과 salaries 테이블 중에서 나머지 조건들까지 합쳐서 최종적으로 일치하는 레코드 건수가 적은 테이블이 드라이빙 테이블로 선정될 가능성이 높다.

employees 테이블에서 인덱스 조건에만 일치하는 레코드는 대략 233건이며, 이 중에서 11.11%만 인덱스를 사용하지 못하는 "e.hire_date BETWEEN '1990-01-01' AND '1991-01-01'" 조건에 일치한다는 것을 알 수 있다. 이렇게 filtered 컬럼의 값은 필터링되어 버려지는 레코드의 비율이 아니라 필터링되고 남은 레코드의 비율을 의미한다. 그래서 employees 테이블에서 salaries 테이블로 조인을 수행할 레코드 건수는 대략 26(233 * 0.1111)건 정도였다는 것을 알 수 있다.

물론 MySQL 서버 옵티마이저는 레코드 건수뿐만 아니라 다른 요소들도 충분히 감안해서 실행 계획을 수립하겠지만 조인의 횟수를 줄이고 그 과정에서 읽어온 데이터를 저장해둘 메모리 사용량을 낮추기 위해 대상 건수가 적은 테이블을 선행 테이블로 선택할 가능성이 높다. 그래서 filtered 컬럼에 표시된 값이 얼마나 정확히 예측될 수 있느냐에 따라 조인의 성능이 달라진다.

Extra 컬럼

Extra 컬럼에는 주로 내부적인 처리 알고리즘에 대해 조금 더 깊이있는 내용을 보여주는 경우가 많다. 따라서 Extra 컬럼에 표시될 수 있는 문장을 하나씩 자세히 살펴보자.

const row not found

쿼리의 실행 계획에서 const 접근 방법으로 테이블을 읽었지만 실제로 해당 테이블에 레코드가 1건도 존재하지 않으면 Extra 컬럼에 이 내용이 표시된다.

Deleting all rows

Deleting all rows 문구는 WHERE 조건절이 없는 DELETE 문장의 실행 계획에서 자주 표시되며, MyISAM 스토리지 엔진과 같이 스토리지 엔진의 핸들러 차원에서 테이블의 모든 레코드를 삭제하는 기능을 제공하는 스토리지 엔진 테이블인 경우에 테이블의 모든 레코드를 삭제하는 핸들러 기능(API)을 한번 호출함으로써 처리됐다는 것을 의미한다.

MySQL 8.0 버전에서는 InnoDB 스토리지 엔진과 MyISAM 엔진 모두 더 이상 실행 계획에 Deleting all rows 최적화는 표시되지 않는다. 테이블의 모든 레코드를 삭제하고자 한다면 WHERE 조건절이 없는 DELETE 보다 TRUNCATE TABLE 명령을 사용할 것을 권장한다.

Distinct

위 쿼리에서 실제 조회하려는 값은 dept_no인데, departments 테이블과 dept_emp 테이블에 모두 존재하는 dept_no만 중복 없이 유니크하게 가져오기 위한 쿼리다. 그래서 두 테이블을 조인해서 그 결과에 다시 DISTINCT 처리를 넣은 것이다. 쿼리의 DISTINCT를 처리하기 위해 조인하지 않아도 되는 항목은 모두 무시하고 꼭 필요한 것만 조인하며, dept_emp 테이블에서는 꼭 필요한 레코드만 읽는다.

FirstMatch

세미 조인의 여러 최적화 중에서 FirstMatch 전략이 사용되면 MySQL 옵티마이저는 실행 계획의 Extra 컬럼에 FirstMatch(table_name) 메시지를 출력한다. FirstMatch 메시지에 함께 표시되는 테이블명은 기준 테이블을 의미하는데, 위 실행 계획의 경우 employees 테이블을 기준으로 titles 테이블에서 첫 번째로 일치하는 한 건만 검색한다는 것을 의미한다.

Full scan on NULL key

이 처리는 'col1 IN (SELECT col2 FROM ...)'과 같은 조건을 가진 쿼리에서 자주 발생할 수 있는데 col1의 값이 NULL이 된다면 결과적으로 조건은 NULL IN (SELECT col2 FROM ...)과 같이 바뀐다. SQL 표준에서는 NULL을 "알 수 없는 값"으로 정의하고 있으며, NULL에 대한 연산의 규칙까지 정의하고 있다. 그 정의대로 연산을 수행하기 위해 이 조건은 다음과 같이 비교되어야 한다.

서브쿼리가 1건이라도 결과 레코드를 가진다면 최종 비교 결과는 NUL
서브쿼리가 1건도 결과 레코드를 가지지 않는다면 최종 비교 결과는 FALSE

이 비교 과정에서 col1이 NULL이면서 서브쿼리에 사용된 테이블에 대해서 풀 테이블 스캔을 해야만 결과를 알아낼 수 있다. Extra 컬럼의 "Full scan on Null key"는 MySQL 서버가 쿼리를 실행하는 중 col1이 NULL을 만나면 차선책으로 서브쿼리 테이블에 대해 풀 테이블 스캔을 사용할 것이라는 사실을 알려주는 키워드다. col1 IN (SELECT col2 FROM ...) 조건에서 col1이 NOT NULL로 정의된 컬럼이라면 이러한 차선책은 사용되지 않고 Extra 컬럼에도 표시되지 않을 것이다.

SELECT * FROM tb_test1
WHERE col1 IS NOT NULL AND col1 IN (SELECT col2 FROM tb_test2);

컬럼이 NOT NULL로 정의되지는 않았지만 NULL 비교 규칙을 무시해도 된다면 col1이 절대 NULL은 될 수 없다는 것을 MySQL 옵티마이저에게 알려주면 된다. 가장 대표적인 방법은 이 쿼리의 조건에 "col1 IS NOT NULL"이라는 조건을 지정하는 것이다. 그러면 col1이 NULL이면 "col1 IS NOT NULL" 조건이 FALSE가 되기 때문에 후속 조건인 "col1 IN (SELECT col2 FROM tb_test2)" 조건은 실행하지 않는다.

Impossible HAVING

쿼리에 사용된 HAVING 절의 조건을 만족하는 레코드가 없을 때 실행 계획의 Extra 컬럼에는 Impossible HAVING 키워드가 표시된다. 어플리케이션의 쿼리 중에서 실행 계획의 Extra 컬럼에 Impossible HAVING 메시지가 출력된다면 쿼리가 제대로 작성되지 못한 경우가 대부분이므로 쿼리의 내용을 다시 점검하는 것이 좋다.

Impossible WHERE

Impossible HAVING과 비슷하며, WHERE 조건이 항상 FALSE가 될 수밖에 없는 경우 Impossible WHERE가 표시된다. 위 쿼리에서 emp_no IS NULL 조건은 항상 FALSE가 되기 때문에 Extra 컬럼에 불가능한 WHERE 조건을 의미하는 문구가 출력된 것을 확인할 수 있다.

LooseScan

세미 조건 최적화 중에서 LooseScan 최적화 전략이 사용되면 실행 계획의 Extra 컬럼에는 LooseScan 문구가 표시된다.

No matching min/max row

쿼리의 WHERE 조건절을 만족하는 레코드가 한 건도 없는 경우 일반적으로 Impossible WHERE ... 문장이 Extra 컬럼에 표시된다. MIN()이나 MAX()와 같은 집합 함수가 있는 쿼리의 조건절에 일치하는 레코드가 한 건도 없을 때는 Extra 컬럼에 "No matching min/max row" 메시지가 출력된다.

no matching row in const table

위 쿼리처럼 조인에 사용된 테이블에 const 방법으로 접근할 때 일치하는 레코드가 없다면 "no matching row in const table"이라는 메시지를 출력한다.

no matching rows after partition pruning

No matching rows after partition pruning 메시지는 파티션된 테이블에 대한 UPDATE 또는 DELETE 명령의 실행 계획에서 표시될 수 있는데, 해당 파티션에서 UPDATE하거나 DELETE할 대상 레코드가 없을 때 표시된다. 위처럼 hire_date 컬럼으로 파티션된 employees_parted 테이블을 한 번 가정해보자.

employees_parted 테이블의 hire_date 컬럼의 값이 2006년 1월 1일 이전까지만 파티션이 정의되어 있다. 그래서 위 쿼리의 실행 계획을 보면 "No matching rows after partition pruning" 메시지가 출력된 것을 확인할 수 있다. 이 메시지는 단순히 삭제할 레코드가 없음을 의미하는 것이 아니라 대상 파티션이 없다는 것을 의미한다.

만약 실제 삭제할 레코드는 없지만 대상 파티션은 있다면 실행 계획에서 partitions 컬럼이 비어있지 않으며, Extra 컬럼에도 해당 메시지가 표시되지 않는 것을 확인할 수 있다.

No table used

FROM 절이 없는 쿼리 문장이나 FROM DUAL 형태의 쿼리 실행 계획에서는 Extra 컬럼에 No tables used라는 메시지가 출력된다. 다른 DBMS와 달리 MySQL 서버는 FROM 절이 없는 쿼리도 허용된다. 이처럼 FROM 절 자체가 없거나 FROM 절에 상수 테이블을 의미하는 DUAL(컬럼과 레코드를 각각 1개씩만 가지는 가상의 상수 테이블)이 사용될 때는 Extra 컬럼에 "No tables used"라는 메시지가 표시된다.

Not exists

프로그램을 개발하다 보면 A 테이블에는 존재하지만 B 테이블에는 없는 값을 조회해야 하는 쿼리가 자주 사용된다. 이러한 형태의 조인을 안티-조인(Anti-JOIN)이라고 한다. 똑같은 처리를 아우터 조인(LEFT OUTER JOIN)을 이용해서도 구현할 수 있다. 일반적으로 NOT IN(subquery) 또는 NOT EXISTS 등의 연산자를 사용하는 안티-조인으로 처리해야 하지만 레코드의 건수가 많을 때는 아우터 조인을 이용하면 빠른 성능을 낼 수 있다.

"Not exists" 메시지는 옵티마이저가 dept_emp 테이블의 레코드를 이용해 departments 테이블을 조인할 때 departments 테이블의 레코드가 존재하는지 아닌지만 판단한다는 것을 의미한다. 즉, departments 테이블에 조인 조건에 일치하는 레코드가 여러 건이 있다고 하더라도 딱 1건만 조회해보고 처리를 완료하는 최적화를 의미한다.

Plan isn't ready yet

EXPLAIN FOR CONNECTION id(프로세스 번호) 명령을 실행하면 실제 커넥션 id가 16인 커넥션(실행 쿼리: SELECT * FROM employees WHERE SLEEP(1))에서 실행하고 있는 쿼리의 실행 계획을 살펴볼 수 있다. 현재 풀 테이블 스캔을 실행하고 있고, EXPLAIN FOR CONNECTION 명령은 MySQL 옵티마이저가 의도된 인덱스를 사용하지 못해서 풀 스캔을 한다거나 잘못된 실행 계획을 선택한 것이 아닌지 확인할 때 유용하게 사용할 수 있는 명령이다.

이렇게 EXPLAIN FOR CONNECTION 명령을 실행했을 때 Extra 컬럼에 "Plan is not ready yet" 메시지가 표시될 때가 있는데, 이 경우는 해당 커넥션에서 아직 쿼리의 실행 계획을 수립하지 못한 상태에서 EXPLAIN FOR CONNECTION 명령이 실행된 것을 의미한다. 이 경우에는 대상 커넥션의 쿼리가 실행 계획을 수립할 여유 시간을 좀 더 주고, 다시 EXPLAIN FOR CONNECTION 명령을 실행하면 된다.

Range checked for each record(index map: N)

위 쿼리를 보면 조인 조건에 상수가 없고 둘 다 변수(e1.emp_no와 e2.emp_no)인 경우 MySQL 옵티마이저는 e1 테이블을 먼저 읽고 조인을 위해 e2를 읽을 때 인덱스 레인지 스캔과 풀 테이블 스캔 중에서 어느 것이 효율적일지 판단할 수 없게 된다. 즉, e1 테이블의 레코드를 하나씩 읽을 때마다 e1.emp_no 값이 계속 바뀌므로 쿼리의 비용 계산을 위한 기준값이 계속 변하는 것이다. 그래서 어떤 접근 방법으로 e2 테이블을 읽는 것이 좋을지 판단할 수 없다.

예를 들어, 사번이 1번부터 1억 번까지 있다고 가정해 보자. 그러면 e1 테이블을 처음부터 끝까지 스캔하면서 e2 테이블에서 "e2.emp_no >= e1.emp_no" 조건을 만족하는 레코드를 찾아야 하는데, 문제는 e1.emp_no = 1인 경우에는 e2 테이블의 1억 건 전부를 읽어야 한다는 것이다. 하지만 e1.emp_no=100000000인 경우에는 e2 테이블을 한 건만 읽으면 된다.

그래서 e1 테이블의 emp_no가 작을 때는 e2 테이블을 풀 테이블 스캔으로 접근하고, e1 테이블의 emp_no가 큰 값일 때는 e2 테이블을 인덱스 레인지 스캔으로 접근하는 형태를 수행하는 것이 최적의 조인 방법이다. 지금까지 설명한 내용을 줄여서 표현하면 "레코드마다 인덱스 레인지 스캔을 체크한다"라고 할 수 있는데, 이것이 Extra 컬럼에 표시되는 Range checked for each record의 의미다.

Extra 컬럼의 출력 내용 중에서 "(index map: 0x1)"은 사용할지 말지를 판단하는 후보 인덱스의 순번을 나타낸다. "index map"은 16진수로 표시되는데 0x1은 이진수로 바꿔도 1이다. 그래서 이 쿼리는 e2(employees) 테이블의 첫 번째 인덱스를 사용할지 아니면 테이블을 풀 스캔할지를 매 레코드 단위로 결정하면서 처리된다. 여기서 테이블의 첫 번째 인덱스란 "SHOW CREATE TABLE employees" 명령으로 테이블의 구조를 조회했을 때 제일 먼저 출력되는 인덱스를 의미한다.

type 컬럼의 값이 ALL로 표시되어 있는 이유는 index map에 표시된 후보 인덱스를 사용할지 여부를 검토해서 이 후보 인덱스가 별로 도움이 되지 않는다면 최종적으로 풀 테이블 스캔을 사용하기 때문에 ALL로 표시된 것이다.

Recursive

MySQL 8.9 버전부터는 CTE(Common Table Expression)를 이용해 재귀 쿼리를 작성할 수 있게 됐다. MySQL 서버에 재귀 쿼리는 위처럼 WITH 구문을 이용해 CTE를 사용하면 된다. 위 쿼리의 WITH 절에서 실행하는 작업은 다음과 같다.

"n"이라는 컬럼 하나를 가진 cte라는 이름의 내부 임시 테이블을 생성
"n" 컬럼의 값이 1부터 5까지 1씩 증가하게 해서 레코드 5건을 만들어서 cte 내부 임시 테이블에 저장

그리고 WITH 절 다음의 SELECT 쿼리에서는 WITH 절에서 생성된 내부 임시 테이블을 (WHERE 절이 없으므로) 풀 스캔해서 결과를 반환한다. 이렇게 CTE를 이용한 재귀 쿼리의 실행 계획은 Extra 컬럼에 "Recursive" 구문이 표시된다.

Rematerialize

MySQL 8.0 버전부터는 래터럴 조인(LATERAL JOIN) 기능이 추가됐는데, 이 경우 래터럴로 조인되는 테이블은 선행 테이블의 레코드별로 서브쿼리를 실행해서 그 결과를 임시 테이블에 저장한다. 이 과정을 "Rematerializing"이라고 한다.

위 실행 계획을 보면 employees 테이블의 레코드마다 salaries 테이블에서 emp_no가 일치하는 레코드 중에서 from_date 컬럼의 역순으로 2건만 가져와 임시 테이블 derived2로 저장했다. 그리고 employees 테이블과 derived2 테이블을 조인한다. 그런데 여기서 derived2 임시 테이블은 employees 테이블의 레코드마다 새로 내부 임시 테이블이 생성된다. 이렇게 매번 임시 테이블이 새로 생성되는 경우 Extra 컬럼에는 Rematerialize 문구가 표시된다.

Select tables optimized away

MIN() 또는 MAX()만 SELECT 절에 사용되거나 GROUP BY로 MIN(), MAX()를 조회하는 쿼리가 인덱스를 오름차순 또는 내림차순으로 1건만 읽는 형태의 최적화가 적용된다면, Extra 컬럼에 "Select tables optimized away"가 표시된다. 위 첫 번째 쿼리는 employees 테이블에 있는 emp_no 컬럼에 인덱스가 생성되어 있으므로 "Select tables optimized away" 최적화가 가능하다. 두 번째 쿼리는 salaries 테이블에 (emp_no, from_date)로 인덱스가 생성되어 있으므로 인덱스가 emp_no = 10002인 레코드를 검색하고, 검색된 결과 중에서 오름차순 또는 내림차순으로 하나만 조회하면 되기 때문에 최저고하가 가능하다.

Start temporary, End temporary

위 쿼리를 실행하는 과정에서 optimizer_switch 시스템 변수의 값을 변경해서 Materialization과 FirstMatch, LooseScan 최적화가 사용되지 못하게 한 상태에서 테스트했다. 세미 조인 최적화 중에서 Duplicate Weed-out 최적화 전략이 사용되면 MySQL 옵티마이저는 위 쿼리의 실행 계획 Extra 컬럼에 Start temporary와 End temporary 문구를 표시하게 된다.

Duplicate Weed-out 최적화 전략은 불필요한 중복 건을 제거하기 위해 내부 임시 테이블을 사용하는데, 이때 조인되어 내부 임시 테이블에 저장되는 테이블을 식별할 수 있게 조인의 첫 번째 테이블에 "Start temporary" 문구를 보여주고 조인이 끝나는 부분에 "End temporary" 문구를 표시해준다. 즉, salaries 테이블부터 시작해서 employees 테이블까지의 내용을 임시 테이블에 저장한다는 의미다.

unique row not found

이 쿼리가 실행되면 tb_test2 테이블에는 fdpk = 2인 레코드가 없으므로 다음처럼 "unique row not found"라는 코멘트가 표시된다.

Using filesort

ORDER BY를 처리하기 위해 인덱스를 이용할 수도 있지만 적절한 인덱스를 사용하지 못할 때는 MySQL 서버가 조회된 레코드를 다시 한 번 정렬해야 한다. ORDER BY 처리가 인덱스를 사용하지 못할 때만 실행 계획의 Extra 컬럼에 "Using filesort" 코멘트가 표시되며, 이는 조회된 레코드를 정렬용 메모리 버퍼에 복사해 퀵 소트 또는 힙 소트 알고리즘을 이용해 정렬을 수행하게 된다는 의미다.

위 쿼리에서 last_name 컬럼에는 인덱스가 없으므로 이 쿼리의 정렬 작업을 처리하기 위해 인덱스를 이용하는 것은 불가능하다. MySQL 옵티아미저는 레코드를 읽어서 소트 버퍼(Sort buffer)에 복사하고, 정렬해서 그 결과를 클라이언트에 보낸다. 실행 계획의 Extra 컬럼에 Using filesort가 출력되는 쿼리는 많은 부하가 일으키므로 가능하다면 쿼리를 튜닝하거나 인덱스를 생성하는 것이 좋다.

Using index(커버링 인덱스)

데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때 Extra 컬럼에 "Using index"가 표시된다. 이렇게 인덱스만으로 처리되는 것을 "커버링 인덱스( Covering index)"라고 한다. 인덱스를 이용해 처리하는 쿼리에서 가장 큰 부하를 차지하는 부분은 인덱스 검색에서 일치하는 키 값들의 레코드를 읽기 위해 데이터 파일을 검색하는 작업이다. 최악의 경우에는 인덱스를 통해 검색된 결과 레코드 한 건 한 건마다 디스크를 한 번씩 읽어야 할 수도 있다.

위 쿼리는 type이 ALL인 것으로 보아 실제 실행 계획은 풀 테이블 스캔을 사용한다는 것을 알 수 있다. first_name 컬럼에 생성된 인덱스를 사용하여 인덱스 레인지 스캔 접근 방법을 사용하면 일치하는 5만여 건을 검색하고, 각 레코드의 birth_date 컬럼의 값을 읽기 위해 각 레코드가 저장된 데이터 페이지를 5만여 번을 읽어야 한다. 따라서 MySQL 옵티마이저가 만들어낸 실행 계획을 보면 인덱스를 사용하는 것보다 풀 테이블 스캔으로 처리하는 편이 더 효율적이라고 판단한 것이다.

birth_date 컬럼은 빼고 first_name 컬럼만 SELECT하는 쿼리를 살펴보자. 이 쿼리는 풀 테이블 스캔이 아니라 인덱스를 통해서 필요한 레코드를 검색하고 필요한 컬럼(first_name)까지 인덱스에서 가져올 수 있으므로 데이터 파일을 읽어 올 필요가 없기 때문에 인덱스 레인지 스캔으로 처리된다는 것을 알 수 있다. 이 쿼리는 디스크에서 30~40개의 페이지만 읽으면 되기 때문에 매우 빠른 속도로 처리된다. 실제로 두 쿼리의 처리된 시간을 확인하면 대략 0.1초 정도 차이나는 것을 확인할 수 있고, InnoDB 테이블의 경우 클러스터링 인덱스로 구성되어 있기 때문에 first_name 컬럼으로 인덱스를 만들었지만 커버링 인덱스로 처리될 가능성이 높아 빠르게 first_name 컬럼만으로 조회한 것과 비슷한 처리속도를 보여준다.

Using index condition

MySQL 옵티마이저가 인덱스 컨디션 푸시 다운(Index condition pushdown) 최적화를 사용하면 위 실행계획 Extra 컬럼에 "Using index condition" 메시지가 표시된다.

Using index for group-by

GROUP BY 처리를 위해 MySQL 서버는 그루핑 기준 컬럼을 이용해 정렬 작업을 수행하고 다시 정렬된 결과를 그루핑하는 형태의 고부하 작업을 필요로 한다. 하지만 GROUP BY 처리가 인덱스(B-Tree 인덱스에 한해서)를 이용하면 (별도의 추가 정렬 작업 없이) 정렬된 인덱스 컬럼을 순서대로 읽으면서 그루핑 작업만 수행하기 때문에 상당히 효율적이고 빠르게 처리된다. GROUP BY 처리가 인덱스를 이용할 때 쿼리의 실행 계획에서는 Extra 컬럼에 "Using index for group-by" 메시지가 표시된다.

타이트 인덱스 스캔(인덱스 스캔)을 통한 GROUP BY 처리

인덱스를 이용해 GROUP BY 절을 처리할 수 있더라도 AVG(), SUM(), COUNT()처럼 조회하려는 값이 모든 인덱스를 다 읽어야 할 때는 필요한 레코드만 듬성듬성 읽을 수 없다. 이러한 쿼리는 단순히 GROUP BY를 위해 인덱스를 사용하기는 하지만, 이를 루스 인덱스 스캔이라고 하지는 않는다. 또한 이러한 쿼리의 실행 계획에는 "Using index for group-by" 메시지가 출력되지 않는다.

루스 인덱스 스캔을 통한 GROUP BY 처리

단일 컬럼으로 구성된 인덱스에서는 그루핑 컬럼 말고는 아무것도 조회하지 않는 쿼리에서 루스 인덱스 스캔을 사용할 수 있다. 그리고 다중 컬럼으로 만들어진 인덱스에서는 GROUP BY 절이 인덱스를 사용할 수 있어야 함은 물론이고 MIN() 또는 MAX() 같이 조회하는 값이 인덱스의 첫 번째 또는 마지막 레코드만 읽어도 되는 쿼리는 "루스 인덱스 스캔"이 사용될 수 있다. 이때는 인덱스를 듬성듬성하게 필요한 부분만 읽는다.

위 쿼리는 salaries 테이블의 (emp_no, from_date) 컬럼으로 만들어진 인덱스에서 emp_no 그룹별로 첫 번째 from_date 값(최솟값)과 마지막 from_date 값(최댓값)을 인덱스로부터 읽으면 되기 때문에 루스 인덱스 스캔 방식으로 처리할 수 있다. 그리고 GROUP BY에서 인덱스를 사용하려면 우선 GROUP BY 조건에서 인덱스를 사용할 수 있는 요건이 갖춰져야 한다. 하지만 그 이전에 WHERE 절에서 사용하는 인덱스에서도 GROUP BY 절의 인덱스 사용 여부가 영향을 받는다는 사실이 중요하다.

WHERE 조건절이 없는 경우
WHERE 절의 조건이 전혀 없는 쿼리는 GROUP BY 절의 컬럼과 SELECT로 가져오는 컬럼이 "루스 인덱스 스캔"을 사용할 수 있는 조건만 갖추면 된다. 그렇지 못한 쿼리는 타이트 인덱스 스캔(인덱스 스캔)이나 별도의 정렬 과정을 통해 처리된다.
WHERE 조건절이 있지만 검색을 위해 인덱스를 사용하지 못하는 경우
GROUP BY 절은 인덱스를 사용할 수 있지만 WHERE 조건절이 인덱스를 사용하지 못할 때는 먼저 GROUP BY를 위해 인덱스를 읽은 후, WHERE 조건의 비교를 위해 데이터 레코드를 읽어야만 한다. 그래서 이 경우도 "루스 인덱스 스캔"을 이용할 수 없으며, 타이트 인덱스 스캔(인덱스 스캔) 과정을 통해 GROUP BY가 처리된다.
WHERE 절의 조건이 있고, 검색을 위해 인덱스를 사용하는 경우
하나의 단위 쿼리가 실행되는 경우에 index_merge 이외의 접근 방법에서는 단 하나의 인덱스만 사용할 수 있다. 그래서 WHERE 절의 조건이 인덱스를 사용할 수 있으면 GROUP BY가 인덱스를 사용할 수 있는 조건이 더 까다로워진다. 즉, WHERE 절의 조건과 GROUP BY 처리가 똑같은 인덱스를 공통으로 사용할 수 있을 때만 루스 인덱스 스캔을 사용할 수 있다. WHERE 조건절이 사용할 수 있는 인덱스와 GROUP BY 절이 사용할 수 있는 인덱스가 다른 경우 일반적으로 옵티마이저는 WHERE 조건절이 인덱스를 사용하도록 실행 계획을 수립하는 경향이 있다.

WHERE 절의 조건이 검색을 위해 인덱스를 사용하고, GROUP BY가 같은 인덱스를 사용할 수 있는 쿼리라고 하더라도 루스 인덱스 스캔을 사용하지 않을 수 있다. 즉, WHERE 조건에 의해 검색된 레코드 건수가 적으면 루스 인덱스 스캔을 사용하지 않아도 매우 빠르게 처리될 수 있기 때문이다. 루스 인덱스 스캔은 주로 대량의 레코드를 GROUP BY하는 경우 성능 향상 효과가 있을 수 있기 떄문에 옵티마이저가 적절히 손익 분기점을 판단하는 것이다.

Using index for skip scan

MySQL 옵티마이저가 인덱스 스킵 스캔 최적화를 사용하면 Extra 컬럼에 "Using index for skip scan" 메시지를 표시한다.

Using join buffer(Block Nested Loop), Using join buffer(Batched Key Access), Using join buffer(hash join)

일반적으로 빠른 쿼리 실행을 위해 조인되는 컬럼은 인덱스를 생성한다. MySQL 옵티마이저는 조인되는 두 테이블에 각 컬럼에서 인덱스를 조사하고, 인덱스가 없는 테이블이 있으면 그 테이블을 먼저 읽어서 조인을 실행한다. 뒤에 읽는 테이블(드리븐 테이블)은 검색 위주로 사용되기 때문에 인덱스가 없으면 성능에 미치는 영향이 매우 크기 때문이다.

하지만 조인이 수행될 때 드리븐 테이블에 검색을 위한 적절한 인덱스가 없다면 MySQL 서버는 블록 네스티드 루프 조인이나 해시 조인을 사용한다. 블록 네스티드 루프 조인이나 해시 조인을 사용하면 MySQL 서버는 조인 버퍼를 사용한다. 실행 계획에서 조인 버퍼가 사용되는 실행 계획의 Extra 컬럼에는 "Using join buffer"라는 메시지가 표시된다.

사용자는 join_buffer_size라는 시스템 변수에 최대로 할당 가능한 조인 버퍼 크기를 설정할 수 있고, 일반적인 온라인 웹 서비스용 MySQL 서버라면 조인 버퍼 크기는 1MB 정도도 충분하다. 하지만 MySQL 8.0 버전부터는 해시 조인이 도입됐는데, 해시 조인 또한 조인 버퍼를 이용하도록 구현되었다. 그래서 데이터 웨어하우스처럼 대용량의 쿼리들을 실행해야 한다면 조인 버퍼를 더 크게 설정하는 것이 좋다.

위 쿼리는 조인 조건이 없는 카테시안 조인을 수행하는 쿼리로 카테시안 조인을 수행하는 쿼리는 항상 조인 버퍼를 사용한다.

Using MRR

MySQL 엔진은 실행 계획을 수립하고 그 실행 계획에 맞게 스토리지 엔진의 API를 호출해서 쿼리를 처리한다. InnoDB를 포함한 스토리지 엔진 레벨에서는 쿼리 실행의 전체적인 부분을 알지 못하기 때문에 최적화에 한계가 있다. 이러한 이유로 아무리 많은 레코드를 읽는 과정이라 하더라도 스토리지 엔진은 MySQL 엔진이 넘겨주는 키 값을 기준으로 레코드를 한 건 한 건 읽어서 반환하는 방식으로 작동하지 못하는 한계점이 있다.

MySQL 서버에서는 이 같은 단점을 보완하기 위해 MRR(Multi Range Red)이라는 최적화를 도입했다. MySQL 엔진은 여러 개의 키 값을 한 번에 스토리지 엔진으로 전달하고, 스토리지 엔진은 넘겨받은 키 값들을 정렬해서 최소한의 페이지 접근만으로 필요한 레코드를 읽을 수 있게 최적화한다.

Using sort_union(...), Using union(...), Using intersect(...)

쿼리가 index_merge 접근 방법(실행 계획의 type 컬럼의 값이 index_merge)으로 실행되는 경우에는 2개 이상의 인덱스가 동시에 사용될 수 있다. 이때 실행 계획의 Extra 컬럼에는 두 인덱스로부터 읽은 결과를 어떻게 병합했는지 조금 더 상세하게 설명하게 위해 다음 3개 중에서 하나의 메시지를 선택적으로 출력한다.

Unsing intersect(...): 각각의 인덱스를 사용할 수 있는 조건이 AND로 연결된 경우 각 처리 결과에서 교집합을 추출해내는 작업을 수행했다는 의미다.
Using union(...): 각 인덱스를 사용할 수 있는 조건이 OR로 연결된 경우 각 처리 결과에서 합집합을 추출해내는 작업을 수행했다는 의미다.
Using sort_union(...): Using union과 같은 작업을 수행하지만 Using union으로 처리될 수 없는 경우(OR로 연결된 상대적으로 대량의 range 조건들) 이 방식으로 처리된다. Using sort_union과 Using union의 차이점은 Using sort_union은 프라이머리 키만 먼저 읽어서 정렬하고 병합한 이후 비로소 레코드를 읽어서 반환할 수 있다는 것이다.

Using temporary

MySQL 서버에서 쿼리를 처리하는 동안 중간 결과를 담아 두기 위해 임시 테이블(Temporary table)을 사용한다. 임시 테이블은 메모리 상에 생성될 수도 있고 디스크상에서 생성될 수도 있다. 쿼리의 실행 계획에서 Extra 컬럼에 "Using temporary" 키워드가 표시되면 임시 테이블을 사용한 것이다.

Using where

MySQL 엔진은 스토리지 엔진으로부터 받은 레코드를 가공 또는 연산하는 작업을 수행한다. MySQL 엔진 레이어에서 별도의 가공을 해서 필터링(여과) 작업을 처리한 경우에만 Extra 컬럼에 Using where 코멘트가 표시된다. 위 쿼리에서 작업 범위 결정 조건은 "emp_no BETWEEN 10001 AND 10100"이며 "gender = 'F'"가 체크 조건임을 알 수 있다. 그런데 처음의 emp_no 조건만 만족하는 레코드 건수는 100건이지만, 두 조건을 모두 만족하는 레코드는 37건밖에 안 된다. 이는 스토리지 엔진은 100개를 읽어서 MySQL 엔진에 넘겨줬지만 MySQL 엔진은 그 중에서 63건의 레코드를 그냥 필터링해서 버렸다는 의미다. 여기서 "Using where"는 63건의 레코드를 버리는 처리를 의미한다.

때로는 프라이머리 키로 한 건의 레코드만 조회해도 "Using where"가 출력되는 문제점도 있다. 그래서 실행 계획의 Extra 컬럼에 표시되는 "Using where"가 성능상의 문제를 일으킬지 아닐지를 적절히 선별하는 능력이 필요한데, MySQL 8.0에서는 실행 계획에 filtered 컬럼이 같이 표시되므로 쉽게 성능상의 이슈가 있는지 없는지 알아낼 수 있다. 이 실행 계획에서는 filtered 컬럼의 값이 50%인 것을 보면 옵티마이저는 100건 중에서 50건은 버려지고 최종 남은 50건이 반환될 것으로 예측했다는 것을 알 수 있다.

Zero limit

때로는 MySQL 서버에서 데이터 값이 아닌 쿼리 결괏값의 메타데이터만 필요한 경우도 있다. 즉, 쿼리의 결과ㅓ가 몇 개의 컬럼을 가지고, 각 컬럼의 타입은 무엇인지 등의 정보만 필요한 경우가 있다. 이런 경우에는 쿼리의 마지막에 "LIMIT 0"을 사용하면 되는데, 이때 MySQL 옵티마이저는 사용자의 의도(메타 정보만 조회하고자 하는 의도)를 알아채고 실제 테이블의 레코드는 전혀 읽지 않고 결괏값의 메타 정보만 반환한다. 이 경우 실행 계획의 Extra 컬럼에는 "Zero limit" 메시지가 출력된다.

출처

Real MySQL 8.0 1권 : 개발자와 DBA를 위한 MySQL 실전 가이드

저작자표시

'교육 및 책 > Real MySQL' 카테고리의 다른 글

쿼리 성능과 최적화 (2/2) (1)	2023.12.28
쿼리 작성과 최적화 (1/2) (0)	2023.12.13
옵티마이저와 힌트 (2/2) (1)	2023.11.13
옵티마이저와 힌트 (1/2) (0)	2023.11.10
인덱스 (1)	2023.11.06