////
Search

10장 - 실행 계획

Created
2022/09/03 11:35
Tags
•
대부분의 DBMS는 많은 데이터를 안전하게 저장 및 관리하고 사용자가 원하는 데이터를 빠르게 조회할 수 있게 해주는게 주목적이다.
•
이런 목적 달성을 위해선 옵티마이저가 최적의 실행계획을 수립해야한다.
•
하지만 옵티마이저가 늘 좋은 실행 계획을 만들어낼 수 있는 것은 아니다.
•
이런 문제점을 해결할 수 있도록 EXPLAIN명령으로 옵티마이저가 수립한 계획을 확인할 수 있게 제공한다.

10.1 통계 정보

•
MySQL 5.7 버전까지 테이블과 인덱스에 대한 개괄적인 정보를 가지고 실행 계획을 수립했다.
◦
But. 테이블 칼럼의 값들이 실제 어떻게 분포돼 있는지에 대한 정보가 없어 실행계획의 정확도가 떨어지는 경우가 많았다.
•
MySQL 8.0 부타는 인덱스되지 않은 칼럼들에 대해서도 데이터 분포도를 수집해서 저장하는 히스토그램 정보가 도입됐다.

10.1.1 테이블 및 인덱스 통계 정보

•
비용 기반 최적화에서 가장 중요한 것은 통계 정보다.
•
통계가 정확하지 않다면 전혀 엉뚱한 방향으로 쿼리를 실행할 수 있기 때문이다.
•
MySQL 또한 다른 DBMS와 같이 비용 기반의 최적화를 사용하지만, 다른 DBMS보다 통계 정보의 정확도가 높지 않고 통계 정보의 휘발성이 강했다.
•
때문에 쿼리의 실행 계획을 수립할 때 실제 테이블의 데이터를 일부 분석해서 통계 정보를 보완해서 사용했다.

10.1.1.1 MySQL 서버의 통계 정보

•
MySQL 5.6 버전부터는 InnoDB 스토리지 엔진을 사용하는 테이블에 대한 통계 정보를 영구적으로(Persistent) 관리할 수 있게 개선됐다.
•
이전 버전까지는 각 테이블의 통계 정보가 메모리에서만 관리되고, SHOW INDEX 명령으로만 테이블의 인덱스 칼럼의 분포도를 볼 수 있었다.
◦
즉 서버가 재시작되면 통계 정보가 모두 사라졌다.
•
이후 버전 부터는 innodb_index_stats 테이블과 innodb_table_stats 테이블로 관리할 수 있게 개선됐다.
◦
서버가 재시작 되어도 통계가 유지된다.
•
테이블 생성시 STATS_PERSISTENT 옵션을 통해 통계 정보의 영구 저장을 설정할 수 있다.
◦
0 : 영구 저장하지 않음
◦
1 : 영구 저장함
◦
DEFAULT : innodb_stats_poersistent 변수로 결정된다.
▪
기본 값은 1이다.
◦
ALTER 명령으로 테이블의 저장 설정을 변경할 수 있다.
•
통계 테이블이 저장하고 있는 정보들
SELECT * FROM innodb_index_stats WHERE database_name='employees' AND TABLE_NAME='employees';
SQL
복사
◦
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_sizes: 프라이머리 키를 제외한 인덱스의 크기(InnoDB 페이지 개수)
•
메모리로만 통계정보를 관리할 경우 서버 재시작 뿐만 아니라 아래 상황에서도 갱신된다.
◦
테이블이 새로 오픈되는 경우
◦
테이블의 레코드가 대량으로 변경되는 경우
▪
테이블의 전체 레코드 중에서 1/16 정도의 UPDATE , INSERT , DELETE가 실행되는 경우
◦
ANALYZE TABLE 명령이 실행되는 경우
◦
SHOW TABLE STATUS 명령이나 SHOW INDEX FROM 명령이 실행되는 경우
◦
InnoDB 모니터가 활성화되는 경우
◦
innodb_stats_on_metadata 시스템 설정이 ON인 상태에서 SHOW TABLE STATUS 명령이 실행되는 경우
•
옵션을 통해서 갱신을 막을 수 있다. = STATS_AUTO_RECALC
◦
이 옵션은 0일 경우 ANALYZE TABLE 일때만 갱신된다.
◦
innodb_stats_auto_recalc 변수 값이 기본 값으로 이용되며 5.6 버전부터는 사라졌다.

10.1.2 히스토그램

•
MySQL 5.7 버전까지의 통계 정보는 단순히 인덱스된 칼럼의 유니크한 값의 개수 정도만 가지고 있었는데, 이는 옵티마이저가 최적의 실행 계획을 수립하기에는 많이 부족
•
옵티마이저는 이러한 부족함을 메우기 위해 실행 계획을 수립할 때 실제 인덱스의 일부 페이지를 랜덤으로 가져와 참조하는 방식을 사용
•
8.0 버전부터 칼럼의 분포도를 참고하는 히스토그램 도입

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

•
히스토그램 정보는 칼럼 단위로 관리됨
◦
자동으로 수집되지 않고 ANALYZE TABLE ... UPDATE HISTOGRAM 명령을 실행해 수동으로 수집 및 관리된다.
•
수집된 히스토그램 정보는 시스템 딕셔너리에 함께 저장됨
◦
MySQL 서버가 시작될 때 딕셔너리의 히스토그램 정보를 information_schema DB의 column_statistics 테이블을 로드한다.
◦
실제 히스토그램 정보를 조회하려면 column_statistics 테이블을 검색해 참조할 수 있다.
•
히스토그램은 버킷 단위로 구분되어 레코드 건수나 칼럼값의 범위가 관리된다.
•
히스토그램은 2가지 종류로 구분 가능하다.
◦
Singleton(싱글톤 히스토그램)
▪
칼럼값 개별로 레코드 건수를 관리하는 히스토그램으로, Value-Based 히스토그램 또는 도수 분포라고도 불린다.
▪
칼럼이 가지는 값별로 버킷이 할당
▪
각 버킷이 칼럼의 값과 발생 빈도의 비율의 2개 값을 가짐
▪
주로 코드 값과 같이 유니크한 값의 개수가 상대적으로 적은 경우 사용된다.
◦
Equi-Height(높이 균형 히스토그램)
▪
칼럼값의 범위를 균등한 개수로 구분해서 관리하는 히스토그램으로, Height Balanced 히스토그램이라고도 불린다.
▪
개수가 균등한 칼럼값의 범위별로 하나의 버킷이 할당
▪
각 버킷이 범위 시작 값과 마지막 값, 그리고 발생 빈도율과 버킷에 포함된 유니크한 값의 개수 등 4개 값을 가짐
▪
기울기가 일정한 것을 보면 각 범위가 비슷한 레코드 건수를 가진다는 것을 알수 있다.
ANALYZE TABLE employees.employees DROP HISTOGRAM ON gender, hire_date;
SQL
복사
information_schema.column_statistics 테이블의 HISTOGRAM 칼럼이 가진 나머지 필드들은 다음과 같은 의미를 가지고 있다.
•
생성된 히스토그램 삭제
•
히스토그램의 삭제 작업은 테이블의 데이터를 참조 하는 것이 아닌 딕셔너리의 내용만 삭제
◦
때문에 다른 쿼리 처리의 성능에 영향을 주지 않고 즉시 완료됨
◦
하지만 히스토그램이 사라지면 쿼리의 실행 계획이 달라질 수 있으므로 주의
•
히스토그램을 삭제하지 않고 MySQL 옵티마이저가 히스토그램을 사용하지 않게 하려면 다음과 같이 optimizer_switch 시스템 변수의 값을 변경하면 됨
◦
on, off 값으로 구분

10.1.2.2 히스토그램의 용도

•
히스토그램이 도입되기 이전에도 테이블과 인덱스에 대한 통계 정보는 존재했다.
◦
기존 서버가 가지고 있던 통계 정보는 테이블의 전체 레코드 건수와 인덱스된 칼럼이 가지는 유니크한 값의 개수 정도였다.
◦
테이블의 레코드가 1000건, 유니크한 값이 100개, 동등 비교 검색시 10개의 레코드를 예상
•
하지만 실제 응용 프로그램의 데이터는 항상 균등한 분포도를 가지지 않는다.
◦
어떤 사용자는 주문 레코드를 많이 가지고 있고 또 다른 사용자들은 주문 정보가 하나도 없을 수 있다.
◦
이전의 통계 정보는 이런 부분을 고려하지 못했다.
◦
이러한 단점을 보완하기 위해 히스토그램이 도입됐다.
•
히스토그램은 특정 칼럼이 가지는 모든 값에 대한 분포도 정보를 가지지는 않지만 각 범위(버킷)별로 레코드의 건수와 유니크한 값의 개수 정보를 가지기 때문에 훨씬 정확한 예측 가능
•
히스토그램 정보가 없으면 옵티마이저는 데티어가 균등하게 분포돼 있을 것으로 예측한다.
◦
히스토그램이 있다면 특정 범위의 데이터가 많고 적음을 식별할 수 있음
◦
조인시의 드라이브 테이블과 드리븐 테이블이 변경될 수 있을 만큼 성능에 영향을 미침
◦
10배 정도의 성능 차이를 보일 수 있음
•
각 칼럼에 대해 히스토그램 정보가 있으면 어느 테이블을 먼저 읽어야 조인의 횟수를 줄일 수 있을지 옵티마이저가 더 정확히 판단할 수 있다.

10.1.2.3 히스토그램과 인덱스

•
히스토그램과 인덱스는 완전히 다른 객체이기 때문에 서로 비교할 대상은 아님
◦
하지만 MySQL 서버에서 인덱스는 부족한 통계 정보를 수집하기 위해 사용된다는 측면에서 어느 정도 공통점을 가진다고 볼 수 있다.
•
쿼리의 실행 계획을 수립할 때 사용 가능한 인덱스들로부터 조건절에 일치하는 레코드 건수를 대략 파악하고 최종적으로 가장 나은 실행 계획을 선택한다.
◦
조건절에 일치하는 레코드 건수를 예측하기 위해 옵티마이저는 실제 인덱스의 B-Tree를 샘플링해서 살펴본다.
▪
이 작업을 매뉴얼에서는 “인덱스 다이브(Index Dive)”라고 표현 함
•
쿼리의 검색 조건으로 많이 사용되는 칼럼에 대해서는 일반적으로 인덱스를 생성한다.
◦
인덱스된 칼럼에 대해 히스토그램 정보를 수집해 두는 것이 좋을지 고민스러울 수 있다.
SELECT * FROM employees WHERE first_name='Tonny' AND birth_date BETWEEN '1954-01-01' AND '1955-01-01';
SQL
복사
•
옵티마이저는 테이블 풀 스캔을 할지 first_name 칼럼의 인덱스를 이용할지 고민할 것
◦
first_name의 히스토그램이 수집되어 있다면 인덱스 다이브를 실행할 것이다.
▪
하지만 어느정도 비용이 필요함
▪
때로는 실행계획 수립만으로 상당한 인덱스 다이브를 실행하고 비용이 커짐

10.1.3 코스트 모델 (Cost Model)

•
쿼리를 처리하기 위한 작업들
◦
디스크로부터 데이터 페이지 읽기
◦
메모리(InnoDB 버퍼 풀)로부터 데이터 페이지 읽기
◦
인덱스 키 비교
◦
레코드 평가
◦
메모리 임시 테이블 작업
◦
디스크 임시 테이블 작업
•
MySQL은 사용자의 쿼리에 대해 이러한 다양한 작업이 얼마나 필요한지 예측하고 전체 작업 비용을 계산한 결과를 바탕으로 최적의 실행 계획을 찾는다.
•
전체 쿼리의 비용을 계산하는 데 필요한 단위 작업들의 비용을 코스트 모델(Cost Model)이라고 한다.
•
5.7 이전 버전까지는 이런 작업들의 비용을 MySQL 소스 코드에 상수화해서 사용함
•
MySQL이 사용하는 하드웨어에 따라 달라질 수 있기 때문에 예전 버전처럼 고정된 비용을 일률적으로 적용하는 것은 최적의 실행 계획 수립에 있어서 방해 요소
◦
이런점을 보완하기 위해 별도의 시스템 변수로 개선함
•
8.0 버전으로 업그레이드되면서 비로소 칼럼의 데이터 분포를 위한 히스토그램과 각 인덱스별 메모리에 적재된 페이지의 비율이 관리되고 옵티마이저의 실행 계획수립에 사용되기 시작
•
코스트 모델은 다음 2개 테이블에 저장돼 있는 설정값을 사용, mysql DB 에 존재함
◦
server_cost - 인덱스를 찾고 레코드를 비교하고 임시 테이블 처리에 대한 비용 관리
◦
engine_cost - 레코드를 가진 데이터 페이지를 가져오는 데 필요한 비용 관리
•
server_cost 테이블과 engine_cost 테이블은 공통으로 다음 5개의 칼럼을 가지고 있다.
◦
cost_name - 코스트 모델의 각 단위 작업
◦
default_value - 각 단위 작업의 비용
▪
기본값이며, 이 값은 MySQL 소스 코드에 설정된 값
◦
cost_value - DBMS 관리자가 설정한 값
▪
이 값이 NULL이면 MySQL은 default_value 칼럼의 비용 사용
◦
last_updated - 단위 작업의 비용이 변경된 시점
◦
comment - 비용에 대한 추가 설명
◦
engine_name - 비용이 적용된 스토리지 엔진
▪
이 값은 engine_cost만 가지고 있음
▪
스토리지 엔진별로 각 단위 작업의 비용을 설정할 수 있음
▪
기본값은 default로 특정 스토리지 엔진의 비용이 설정되지 않았다면 해당 스토리지 엔진의 비용으로 이 값을 적용한다는 의미
◦
device_type - 디스크 타입
▪
이 값은 engine_cost만 가지고 있음
MySQL 8.0 버전의 코스트 모델 지원 단위
•
row_evaluate_cost는 스토리지 엔진이 반환한 레코드가 쿼리의 조건에 일치하는지를 평가하는 단위 작업을 의미
◦
값이 증가할수록 풀 테이블 스캔과 같이 많은 레코드를 처리하는 쿼리의 비용이 높아지고, 레인지 스캔과 같이 상대적으로 적은 수의 레코드를 처리하는 쿼리의 비용이 낮아진다.
◦
key_compare_cost는 키 값의 비교 작업에 필요한 비용을 의미하는데, 값이 증가할수록 레코드 정렬과 같이 키 값 비교 처리가 많은 경우 쿼리의 비용이 높아진다.
•
MySQL 실행 계획 비용을 직접 계산하는건 여러 정보가 사용자에게 직접 노출되지 않기때문에 직접 계산은 어렵다.
•
코스트 모델의 비용이 변경되면 예상할 수 있는 결과
◦
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 비용이 높아지면 InnoDB 버퍼 풀에 적재된 데이터 페이지가 상대적으로 적다고 하더라도 그 인덱스를 사용할 가능성이 높아진다.

10.2 실행 계획 확인

10.2.1 실행 계획 출력 포맷

•
이전 버전에서는 EXPLAIN EXTENDED 또는 EXPLAIN PARTITIONS 명령이 구분돼 있었다.
•
8.0 버전 부터는 모든 내용이 통합되어 보이도록 개선되면서 PARTITIONS나 EXTENDED 옵션은 문법에서 제거되었다.
•
그리고 MySQL 8.0 버전부터는 FORMAT 옵션을 사용해 실행 계획의 표시 방법을 JSON이나 TREE, 단순 테이블 형태로 선택할 수 있다.
/* 테이블 포맷 */ EXPLAIN SELECT * FROM employees e INNER JOIN salaries s ON s.emp_no=e.emp_no WHERE first_name='ABC'; /* TREE 포맷 */ EXPLAIN FORMAT=TREE SELECT * FROM employees e INNER JOIN salaries s ON s.emp_no=e.emp_no WHERE first_name='ABC'\G /* JSON 포맷 */ EXPLAIN FORMAT=JSON SELECT * FROM employees e INNER JOIN salaries s ON s.emp_no=e.emp_no WHERE first_name='ABC'\G
SQL
복사

10.2.2 쿼리의 실행 시간 확인

•
8.0.18 버전부터는 쿼리의 실행 계획과 단계별 소요된 시간 정보를 확인할 수 있는 EXPLAIN ANALYZE 기능이 추가됐다.
•
SHOW PROFILE 명령으로 어떤 부분에서 시간이 많이 소요되는지 확인 할 수 있지만 SHOW PROFILE 명령의 결과는 실행 계획의 단계별로 소요된 시간 정보를 보여주진 않는다.
EXPLAIN ANALYZE SELECT e.emp_no, avg(s.salary) FROM employees e INNER JOIN salaries s ON s.emp_no=e.emp_no AND s.salary>50000 AND s.from_date<='1990-01-01' AND s.to_date>'1990-01-01' WHERE e.first_name='Matt' GROUP BY e.hire_date \G
SQL
복사
•
TREE 형태로 출력되며 실제 실행 순서는 다음 기준으로 읽으면 됨
◦
들여쓰기가 같은 레벨에서는 상단에 위치한 라인이 먼저 실행
◦
들여쓰기가 다른 레벨에서는 가장 안쪽에 위치한 라인이 먼저 실행
실행계획 해석
•
때문에 실제로 읽는 방법은 D→F→E→C→B→A
•
F라인에 나열된 필드들의 의미
◦
actual time=0.007..0.009
▪
employees 테이블에서 읽은 emp_no 값을 기준으로 salaries 테이블에서 일치하는 레코드를 검색하는 데 걸린 시간(밀리초)을 의미
▪
숫자 값이 2개가 표시되는데, 첫 번째 숫자 값은 첫 번 째 레코드를 가져오는 데 걸린 평균 시간(밀리초)을 의미
▪
두 번째 숫자 값은 마지막 레코드를 가져오는 데 걸린 평균 시간(밀리초)을 의미한다.
◦
rows=10: employees
▪
테이블에서 읽은 emp_no에 일치하는 salaries 테이블의 평균 레코드 건수를 의미한다.
◦
loops=233: employees
▪
테이블에서 읽은 emp_no를 이용해 salaries 테이블의 레코드를 찾는 작업이 반복된 횟수 를 의미
▪
여기서는 employees 테이블에서 읽은 emp_no의 개수가 233개임을 의미한다.

10.3 실행 계획 분석

•
실행 계획이 어떤 접근 방법을 사용해서 어떤 최적화를 수행하는지, 그리고 어떤 인덱스를 사용하는지 등을 이해하는 것이 중요 하다.
EXPLAIN SELECT * FROM employees e INNER JOIN salaries s ON s.emp_no=e.emp_no WHERE first_name='ABC';
SQL
복사
•
표의 각 라인(레코드)은 쿼리 문장에서 사용된 테이블의 개수만큼 출력된다.
◦
서브쿼리로 임시 테이블을 생성한 경우 그 임시 테이블까지 포함
•
실행 순서는 위에서 아래로 순서대로 표시된다
◦
UNION이나 상관 서브쿼리와 같은 경우 순서대로 표시되지 않을 수도 있다.

10.3.1 id 칼럼

SELECT ... FROM (SELECT ... FROM tb_test1) tb1, tb_test2 tb2 WHERE tb1.id=tb2.id;
SQL
복사
•
쿼리 문장에 있는 각 SELECT를 다음과 같이 분리해서 생각해볼 수 있다.
◦
이렇게 SELECT 키워드 단 위로 구분한 것을 단위(SELECT) 쿼리라고 표현한다.
SELECT ... FROM tb_test_1; SELECT ... FROM tb1, tb_test2 tb2 WHERE tb1.id=tb2.id;
SQL
복사
•
하나의 SELECT문 안에서 여러 개의 테이블을 조인하면 조인되는 테이블의 개수만큼 실행 계획 레코드가 출력되지만 같은 id 값이 부여된다.
EXPLAIN SELECT e.emp_no, e.first_name, s.from_date, s.salary FROM employees e, salaries s WHERE e.emp_no=s.emp_no LIMIT 10;
SQL
복사
SELECT 문 안에 여러 개의 테이블이 조인
EXPLAIN SELECT ( (SELECT COUNT(*) FROM employees) + (SELECT COUNT(*) FROM departments) ) AS total_ count;
SQL
복사
3개의 단위 SELECT로 구성된 쿼리
EXPLAIN FORMAT=TREE SELECT * FROM dept_emp de WHERE de.emp_no=( SELECT e.emp_no FROM employees e WHERE e.first_name='Georgi' AND e.last_name='Facello' LIMIT 1 );
SQL
복사
•
이 쿼리는 employees 테이블이 가장 먼저 조회되고, 그 결과를 이용해 dept_emp 테이블이 조회된 것이다.

10.3.2 select_type 칼럼

•
각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼

10.3.2.1 SIMPLE

•
UNION이나 서브쿼리를 사용하지 않는 단순한 SELECT 쿼리인 경우 해당 쿼리 문장의 select_type은 SIMPLE로 표시된다.
◦
쿼리에 조인이 포함된 경우에도 마찬가지다
•
쿼리 문장이 아무리 복잡하더라도 실행 계획에서 select_type이 SIMPLE인 단위 쿼리는 하나만 존재한다.

10.3.2.2 PRIMARY

•
UNION이나 서브쿼리를 가지는 SELECT 쿼리의 실행 계획에서 가장 바깥쪽(Outer)에 있는 단위 쿼리는 select_type이 PRIMARY로 표시된다.
•
SIMPLE과 마찬가지로 select_type이 PRIMARY인 단위 SELECT 쿼리는 하나만 존재하며, 쿼리의 제일 바깥쪽에 있는 SELECT 단위 쿼리가 PRIMARY로 표시된다.

10.3.2.3 UNION

•
UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리의 select_type은 UNION으로 표시된다.
•
UNION의 첫 번째 단위 SELECT는 select_type이 UNION이 아니라 UNION되는 쿼리 결과들을 모아서 저장하는 임시 테이블(DERIVED)이 select_type으로 표시된다.
•
세 개의 서브쿼리로 조회된 결과를 UNION ALL로 결합해 임시 테이블을 만들어서 사용하고 있으므로 UNION ALL의 첫 번째 쿼리는 DERIVED라는 select_type을 가진다.

10.3.2.4 DEPENDENT UNION

•
UNION select_type과 같이 UNION이나 UNION ALL로 집합을 결합하는 쿼리에서 표시된다.
•
UNION이나 UNION ALL로 결합된 단위 쿼리가 외부 쿼리에 의해 영향을 받는 것을 의미한다.

10.3.2.5 UNION RESULT

•
UNION RESULT는 UNION 결과를 담아두는 테이블을 의미한다.
•
8.0 이전 버전에서는 UNION ALL이나 UNION(또는 UNION DISTINCT) 쿼리는 모두 UNION의 결과를 임시 테이블로 생성했었다.
◦
8.0 버전부터는 UNION ALL의 경우 임시 테이블을 사용하지 않도록 기능이 개선됐다.
◦
하지만 UNION은 MySQL 8.0 버전에서도 여전히 임시 테이블에 결과를 버퍼링한다
•
UNION RESULT는 실제 쿼리에서 단위 쿼리가 아니기 때문에 별도의 id 값은 부여되지 않는다.
EXPLAIN SELECT emp_no FROM salaries WHERE salary>100000 UNION DISTINCT SELECT emp_no FROM dept_emp WHERE from_date>'2001-01-01';
SQL
복사
•
마지막 실행계획은 id 값이 1인 단위 쿼리의 조회 결과와 id 값이 2인 단위 쿼리의 조회 결과를 UNION 했다는 것을 의미

10.3.2.6 SUBQUERY

•
일반적으로 서브쿼리라고 하면 여러 가지를 통틀어서 이야기할 때가 많은데, select_type의 SUBQUERY는 FROM 절 이외에서 사용되는 서브쿼리만을 의미한다.
EXPLAIN SELECT e.first_name, ( SELECT COUNT(*) FROM dept_emp de, dept_manager dm WHERE dm.dept_no=de.dept_no ) AS cnt FROM employees e WHERE e.emp_no=10001;
SQL
복사
•
실행 계획에서 FROM 절에 사용된 서브쿼리는 select_type이 DERIVED로 표시되고, 그 밖의 위치에서 사용된 서브쿼리는 전부 SUBQUERY라고 표시된다.
◦
파생 테이블이라는 단어는 DERIVED와 같은 의미로 이해하면 된다.

10.3.2.7 DEPENDENT SUBQUERY

•
서브쿼리가 바깥쪽(Outer) SELECT 쿼리에서 정의된 칼럼을 사용하는 경우, select_type에 DEPENDENT SUBQUERY라고 표시된다.
EXPLAIN SELECT e.first_name, ( SELECT COUNT(*) FROM dept_emp de, dept_manager dm WHERE dm.dept_no=de.dept_no AND de.emp_no=e.emp_no ) AS cnt FROM employees e WHERE e.first_name='Matt';
SQL
복사
•
이럴 때는 안쪽(Inner)의 서브쿼리 결과가 바깥쪽(Outer) SELECT 쿼리의 칼럼에 의존적이기 때문에 DEPENDENT라는 키워드가 붙는다.
•
DEPENDENT UNION과 같이 DEPENDENT SUBQUERY 또한 외부 쿼리가 먼저 수행된 후 서브쿼리가 실행돼야 하므로 DEPENDENT 키워드가 없는 서브쿼리보다는 처리 속도가 느릴 때가 많다.

10.3.2.8 DERIVED

•
5.5 버전까지는 서브쿼리가 FROM 절에 사용된 경우 항상 select_type이 DERIVED인 실행 계획을 만든다.
◦
5.6 버전부터는 옵티마이저 옵션(optimizer_switch 시스템 변수)에 따라 FROM 절의 서브쿼리를 외부 쿼리와 통합하는 형태의 최적화가 수행되기도 한다.
•
DERIVED는 단위 SELECT 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블을 생성하는 것을 의미한다.
•
select_type이 DERIVED인 경우에 생성되는 임시 테이블을 파생 테이블이라고도 한다.
•
5.5 버전까지는 파생 테이블에는 인덱스가 전혀 없으므로 다른 테이블과 조인할 때 성능상 불리할 때가 많다.
◦
5.6 버전부터는 옵티마이저 옵션에 따라 쿼리의 특성에 맞게 임시 테이블에도 인덱스를 추가해서 만들 수 있게 최적화됐다.
•
MySQL 서버는 버전이 업그레이드되면서 조인 쿼리에 대한 최적화는 많이 성숙된 상태다.
•
가능하다면 DERIVED 형태의 실행 계획을 조인으로 해결할 수 있게 쿼리를 바꿔주는 것이 좋다.
•
8.0 버전부터는 FROM 절의 서브쿼리에 대한 최적화도 많이 개선되어 가능하다면 불필요한 서브쿼리는 조인으로 쿼리를 재작성해서 처리한다.
•
옵티마이저가 처리할 수 있는 것은 한계가 있으므로 최적화된 쿼리를 작성하는 것은 중요

10.3.2.9 DEPENDENT DERIVED

•
8.0 이전 버전에서는 FROM 절의 서브쿼리는 외부 칼럼을 사용할 수가 없었다.
•
8.0 버전부터는 래터럴 조인(LATERAL JOIN) 기능이 추가되면서 FROM 절의 서브쿼리에서도 외부 칼럼을 참조할 수 있게 됐다.

10.3.2.10 UNCACHEABLE SUBQUERY

•
하나의 쿼리 문장에 서브쿼리가 하나만 있더라도 실제 그 서브쿼리가 한 번만 실행되는 것은 아니다.
•
조건이 똑같은 서브쿼리가 실행될 때는 다시 실행하지 않고 이전의 실행 결과를 그대로 사용할 수 있게 서브쿼리의 결과를 내부적인 캐시 공간에 담아둔다.
◦
서브쿼리 캐시는 쿼리 캐시나 파생 테이블과는 전혀 무관한 기능이므로 혼동하지 않도록 주의
•
SUBQUERY는 Outer의 영향을 받지 않으므로 처음 한 번만 실행해서 그 결과를 캐시하고 필요할 때 캐시 된 결과를 이용한다.
•
DEPENDENT SUBQUERY는 의존하는 Outer쿼리의 칼럼의 값 단위로 캐시해두고 사용한다.

10.3.2.11 UNCACHEABLE UNION

•
UNCACHEABLE UNION이란 UNION과 UNCACHEABLE키워드의 속성이 혼합된 select_type을 의미한다.
•
DEPENDENT SUBQUERY는 서브쿼리 결과가 캐시는 되지만, 딱 한 번만 캐시되는 것이 아니라 Outer쿼리의 값 단위로 캐시가 만들어지는 방식으로 처리된다.
•
서브쿼리에 포함된 요소에 의해 캐시 자체가 불가능할 수가 있는데, 그럴 경우 select_type이 “UNCACHEABLE SUBQUERY”로 표시된다.
◦
사용자 변수가 서브쿼리에 사용된 경우
◦
NOT-DETERMINISTIC 속성의 스토어드 루틴이 서브쿼리 내에 사용된 경우
◦
UUID()나 RAND()와 같이 결괏값이 호출할 때마다 달라지는 함수가 서브쿼리에 사용된 경우

10.3.2.12 MATERIALIZED

•
5.6 버전부터 도입된 select_type으로, 주로 FROM 절이나 IN(subquery) 형태의 쿼리에 사용된 서브쿼리의 최적화를 위해 사용된다.

10.3.3 table 칼럼

•
MySQL 서버의 실행 계획은 단위 SELECT 쿼리 기준이 아니라 테이블 기준으로 표시된다. 테이블의 이름에 별칭이 부여된 경우에는 별칭이 표시된다.
•
table 칼럼에 <derived N> 또는 <union M,N>과 같이 “<>”로 둘러싸인 이름이 명시되는 경우가 많은데, 이 테이블은 임시 테이블을 의미한다.
◦
또한 “<>” 안에 항상 표시되는 숫자는 단위 SELECT 쿼리의 id 값을 지칭한다.

10.3.4 partitions 칼럼

•
5.7 버전까지는 옵티마이저가 사용하는 파티션들의 목록은 EXPLAIN PARTITION 명령을 이용해 확인 가능했음
•
8.0 버전부터는 EXPLAIN 명령으로 파티션 관련 실행 계획까지 모두 확인할 수 있게 변경됨
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'))
SQL
복사
파티션 테이블 생성
EXPLAIN SELECT * FROM employees_2 WHERE hire_date BETWEEN '1999-11-15' AND '2000-01-15';
SQL
복사
기간 검색 쿼리
•
이 쿼리에서 필요로 하는 데이터는 p1996_2000과 p2001_2005 파티션에만 있다는 것을 알아내게 된다.
•
그래서 실행 계획에서도 나머지 파티션에 대해서는 어떻게 접근할지 데이터 분포가 어떠한지 등의 분석을 실행하지 않는다.
•
이처럼 파티션이 여러 개인 테이블에서 불필요한 파티션을 빼고 쿼리를 수행 하기 위해 접근해야 할 것으로 판단되는 테이블만 골라내는 과정을 파티션 프루닝(Partition pruning)이라고 한다.
•
위와 같이 파티션을 참조하는 쿼리(파티션 키 칼럼을 WHERE 조건으로 가진)의 경우 옵티마이저가 쿼리 처리를 위해 필요한 파티션들의 목록만 모아서 실행 계획의 partitions 칼럼에 표시해준 다.
•
해당 실행계획은 타입은 ALL로 풀 테이블 스캔으로 쿼리가 처리된다는 것을 의미
•
풀 스캔으로 일부만 읽을 수 있는 이유는 MySQL을 포함한 대부분의 RDBMS에서 지원하는 파티션은 물리적으로 개별 테이블처럼 별도의 저장 공간을 가지기 때문이다.
◦
employees_2 테이블의 모든 파티션이 아니라 p1996_2000 파티션과 p2001_2005 파티션만 풀 스캔을 실행하게 된다.

10.3.5 type 칼럼

•
쿼리의 실행 계획에서 type 이후의 칼럼은 MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 나타낸다.
•
여기서 방식이라 함은 인덱스를 사용해 레코드를 읽었는지, 아니면 테이블을 처음부터 끝까지 읽는 풀 테이블 스캔으로 레코드를 읽었는지 등을 의미한다.
•
일반적으로 쿼리를 튜닝할 때 인덱스를 효율적으로 사용하는지 확인하는 것이 중요하므로 실행 계획에서 type 칼럼은 반드시 체크해야 할 중요한 정보다.
•
MySQL에서 하나의 테이블로 레코드를 읽는 작업도 조인처럼 처리해서 SELECT쿼리의 테이블 갯수와 무관하게 조인타입이라고 명시한다.
◦
type 칼럼의 값은 조인과 직접 연관지어 생각말고 각 테이블의 접근 방법으로 해석하면 됨
•
type 칼럼에서 표시될 수 있는 값들
◦
system, const, eq_ref, ref, fulltext, ref_or_null, unique_subquery, index_subquery, range, index_merge, index, ALL
◦
ALL을 제외한 나머지는 모두 인덱스를 사용하는 접근 방법이다.
•
하나의 단위 SELECT 쿼리는 위의 접근 방법 중에서 단 하나만 사용할 수 있다.
•
또한 index_merge를 제외한 나머지 접근 방법은 하나의 인덱스만 사용한다.
•
실행계획 각 라인에 2개 이상 접근 방법이 표기되지 않는다.

10.3.5.1 system

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

10.3.5.2 const

•
테이블의 레코드 건수와 관계없이 쿼리가 프라이머리 키나 유니크 키 칼럼을 이용하는 WHERE 조건절을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리 방식을 const라고 한다.
•
다른 DBMS에서는 이를 유니크 인덱스 스캔(UNIQUE INDEX SCAN)이라고도 표현한다.
•
실행 계획의 type 칼럼이 const인 실행 계획은 옵티마이저가 쿼리를 최적화하는 단계에서 쿼리를 먼저 실행해서 통째로 상수화한다.

10.3.5.3 eq_ref

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

10.3.5.4 ref

•
ref 접근 방법은 eq_ref와는 달리 조인의 순서와 관계없이 사용되며, 또한 프라이머리 키나 유니크 키 등의 제약 조건도 없다.
•
인덱스의 종류와 관계없이 동등조건으로 검색할 때는 ref 접근 방법이 사용된다.
•
ref 타입은 반환되는 레코드가 반드시 1건이라는 보장이 없으므로 const나 eq_ref보다는 빠르지 않다.
•
하지만 동등한 조건으로만 비교되므로 매우 빠른 레코드 조회 방법의 하나다.
•
다른 실행 계획과의 차이점
◦
const - 반드시 한건의 레코드만 반환
◦
eq_ref - 두 번째 테이블은 반드시 한건의 레코드만 반환
•
위 접근방법 모두 WHERE 조건절에 사용하는 비교 연산자는 동등 비교 연산자여야 한다는 공통점이 존재
◦
=, <=>연산자가 동등 연산자로 사용됨

10.3.5.5 fulltext

•
fulltext 접근 방법은 전문 검색 인덱스를 사용해 레코드를 읽는 접근 방법을 의미
•
지금 살펴보는 type의 순서가 일반적인 처리 성능의 순서이긴 하지만 실제로 데이터의 분포나 레코드의 건수에 따라 빠른 순서는 달라질 수 있다.
•
이는 비용 기반의 옵티마이저에서 통계 정보를 이용해 비용을 계산하는 이유이기도 하다.
•
하지만 전문 검색 인덱스는 통계 정보가 관리되지 않으며, 전문 검색 인덱스를 사용하려면 전혀 다른 SQL 문법을 사용해야 한다.
* MATCH (...) AGAINST (...)

10.3.5.6 ref_or_null

•
이 접근 방법은 ref 접근 방법과 같은데, NULL 비교가 추가된 형태다.
•
접근 방법의 이름 그대로 ref 방식 또는 NULL 비교(IS NULL) 접근 방법을 의미한다.
•
실제 업무에서 많이 활용되지 않지만, 만약 사용된다면 나쁘지 않은 접근 방법 정도로 기억해 두면 충분하다.

10.3.5.7 unique_subquery

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

10.3.5.8 index_subquery

•
IN 연산자의 특성상 IN(subquery)/IN(상수 나열) 형태의 조건은 괄호 안에 있는 값의 목록에서 중 복된 값이 먼저 제거돼야 한다.
•
unique_subquery 접근 방법은 IN(subquery) 조건의 subquery가 중복된 값을 만들어내지 않는다는 보장이 있으므로 별도의 중복을 제거할 필요가 없었다.
•
하지만 업무 특성상 IN(subquery)에서 subquery가 중복된 값을 반환할 수도 있다.
◦
이때 서브쿼리 결과의 중복된 값을 인덱스를 이용해서 제거할 수 있을 때 index_subquery 접근 방법이 사용된다.

10.3.5.9 range

•
range는 우리가 익히 알고 있는 인덱스 레인지 스캔 형태의 접근 방법
•
인덱스를 하나의 값이 아니라 범위로 검색하는 경우를 의미하는데, 주로 "<, >, IS NULL, BETWEEN, IN, LIKE" 등의 연산자를 이용해 인덱스를 검색할 때 사용된다.
•
접근 방법들 중 상당히 우선순위가 낮음
•
range도 상당히 빠른 접근방법이며, 최적의 성능이 보장된다.

10.3.5.10 index_merge

•
index_merge 접근 방법은 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후, 그 결과를 병합해서 처리하는 방식
•
해당 접근 방법이 사용되는 경우를 생각해보면 이름만큼 그렇게 효율적으로 작동하는 것은 아니다.
•
index_merge 접근 방법의 특징
◦
여러 인덱스를 읽어야 하므로 일반적으로 range 접근 방법보다 효율성이 떨어진다.
◦
전문 검색 인덱스를 사용하는 쿼리에서는 index_merge가 적용되지 않는다.
◦
Index_merge 접근 방법으로 처리된 결과는 항상 2개 이상의 집합이 되기 때문에 그 두 집합의 교집합이나 합집합 또는 중복 제거와 같은 부가적인 작업이 더 필요하다.

10.3.5.11 index

•
index 접근 방법은 많은 사람이 자주 오해하는 접근 방법이다.
•
index 접근 방법은 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미한다.
•
인덱스는 일반적인 풀 스캔보다 효율적이며, 쿼리의 내용에 따라 정렬된 인덱스의 장점을 이용할 수 있으므로 훨씬 효율적이다.
•
다음 첫 번째+두 번째, 첫 번째+세 번째 조건을 충족하는 쿼리에서 사용되는 읽기 방식이다.
◦
range나 const, ref 같은 접근 방법으로 인덱스를 사용하지 못하는 경우
◦
인덱스에 포함된 칼럼만으로 처리할 수 있는 쿼리인 경우
▪
즉, 데이터 파일을 읽지 않아도 되는 경우
◦
인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우
▪
즉, 별도의 정렬 작업을 피할 수 있는 경우

10.3.5.11

•
풀 테이블 스캔을 의미하는 접근 방법이다.
•
테이블을 처음부터 끝까지 전부 읽어서 불필요한 레코드를 제거하고 반환한다.
•
풀 테이블 스캔은 지금까지 설명한 접근 방법으로는 처리할 수 없을 때 가장 마지막에 선택하는 가장 비효율적인 방법이다.
•
다른 DBMS와 같이 InnoDB도 풀 테이블 스캔이나 인덱스 풀 스캔과 같은 대량의 디스크 I/O를 유발하는 작업을 위해 한꺼번에 많은 페이지를 읽어 들이는 기능을 제공한다.
◦
리드 어헤드라 불리는 작업으로, 한 번에 여러 페이지를 읽어 처리 가능
•
테이블이 매우 작지 않다면 실제로 테이블에 데이터를 어느 정도 저장한 상태에서 쿼리의 성능을 확인해 보고 적용하는 것 이 좋다.

10.3.6 possible_key 칼럼

•
옵티마이저는 쿼리를 처리하기 위해 여러 가지 처리 방법을 고려하고 그중에서 비용이 가장 낮을 것으로 예상하는 실행 계획을 선택해 쿼리를 실행한다.
•
possible_keys 칼럼에 있는 내용은 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 접근 방법에서 사용되는 인덱스의 목록일 뿐이다.
◦
즉, 말 그대로 “사용될 법했던 인덱스의 목록”인 것이다.
•
실행 계획을 확 인할 때는 possible_keys 칼럼은 특별한 경우를 제외하고는 그냥 무시해도 된다.
•
해당 칼럼에 인덱스 이름이 나열됐다고 해서 그 인덱스를 사용한다고 판단하지 않도록 주의하자.

10.3.7 key 칼럼

•
possible_keys 칼럼의 인덱스가 사용 후보였던 반면, key 칼럼에 표시되는 인덱스는 최종 선택된 실행 계획에서 사용하는 인덱스를 의미한다.
•
쿼리를 튜닝할 때는 key 칼럼에 의도했던 인덱스가 표시되는지 확인하는 것이 중요하다.
•
칼럼에 표시되는 값이 PRIMARY인 경우에는 프라이머리 키를 사용한다는 의미이며, 그 이외의 값은 모두 테이블이나 인덱스를 생성할 때 부여했던 고유 이름이다.

10.3.8 key_len 칼럼

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

10.3.9 ref 칼럼

•
접근 방법이 ref면 참조 조건으로 어떤 값이 제공됐는지 보여준다.
•
상숫값을 지정했다면 ref 칼럼의 값은 const로 표시되고, 다른 테이블의 칼럼값이면 그 테이블명과 칼럼명이 표시된다.
•
이 칼럼에 출력되는 내용은 크게 신경 쓰지 않아도 무방한데, 다음과 같은 케이스는 조금 주의해서 볼 필요가 있다.
◦
ref 칼럼의 값이 func로 표시될 때 콜레이션 변환이나 값 자체의 연산을 거쳐 참조된걸 의미

10.3.10 rows 칼럼

•
각 처리 방식이 얼마나 많은 레코드를 읽고 비교해야 하는지 예측해서 비용을 산정한다.
•
대상 테이블에 얼마나 많은 레코드가 포함돼 있는지 또는 각 인덱스 값의 분포도가 어떤지를 통계 정보를 기준으로 조사해서 예측한다.
•
rows 칼럼값은 실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여준다.
•
이 값은 각 스토리지 엔진별로 가지고 있는 통계 정보를 참조해 MySQL 옵티마이저가 산출해 낸 예상값이라서 정확하지는 않다.
◦
실행 계획의 rows 칼럼에 출력되는 값과 실제 쿼리 결과 반환된 레코드 건수는 일치하지 않는 경우가 많다.

10.3.11 filtered 칼럼

•
실행 계획에서 rows 칼럼의 값은 인덱스를 사용하는 조건에만 일치하는 레코드 건수를 예측한 것이다.
•
하지만 대부분 쿼리에서 WHERE 절에 사용되는 조건이 모두 인덱스를 사용할 수 있는 것은 아니다.
•
특히 조인이 사용되는 경우에는 WHERE 절에서 인덱스를 사용할 수 있는 조건도 중요하지만 인덱스를 사용하지 못하는 조건에 일치하는 레코드 건수를 파악하는 것도 매우 중요하다.
•
8.0에서는 filtered 칼럼의 값을 더 정확히 예측할 수 있도록 히스토그램 기능이 도입됐다.

10.3.12 Extra 칼럼

•
쿼리의 실행 계획에서 성능에 관련된 중요한 내용이 Extra 칼럼에 자주 표시된다.
•
Extra 칼럼에는 고정된 몇 개의 문장이 표시되는데, 일반적으로 2~3개씩 함께 표시된다.
•
Extra 칼럼에는 주로 내부적인 처리 알고리즘에 대해 조금 더 깊이 있는 내용을 보여주는 경우가 많다.

10.3.12.1 const row not found

•
쿼리의 실행 계획에서 const 접근 방법으로 테이블을 읽었지만 실제로 해당 테이블에 레코드가 1건도 존재하지 않으면 Extra 칼럼에 이 내용이 표시된다.
•
테스트용 데이터를 저장하고 다시 한번 쿼리의 실행 계획을 확인해 보는 것이 좋다.

10.3.12.2 Deleting all rows

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

10.3.12.3 Distinct

•
DISTINCT를 처리하기 위해 조인하지 않아도 되는 항목은 모두 무시하고 꼭 필요한 것만 조인했으며, dept_emp 테이블에서는 꼭 필요한 레코드만 읽었다는 것을 표현하고 있다.

10.3.12.4 FirstMatch

•
세미 조인의 여러 최적화 중에서 FirstMatch 전략이 사용되면 MySQL 옵티마이저는 실행 계획의 Extra 칼럼에 “FirstMatch(table_name)” 메시지를 출력한다.

10.3.12.5 Full sacn on NULL key

•
col1 IN (SELECT col2 FROM ...) 조건을 가진 쿼리에서 자주 발생할 수 있는데, 칼럼의 값이 NULL이 된다면 결과적으로 조건은 NULL IN (SELECT col2 FROM ...)과 같이 바뀐다.
•
연산을 수행하기 위해 이 조건은 다음과 같이 비교돼야 한다.
◦
서브쿼리가 1건이라도 결과 레코드를 가진다면 최종 비교 결과는 NULL
◦
서브쿼리가 1건도 결과 레코드를 가지지 않는다면 최종 비교 결과는 FALSE
•
쿼리를 실행하는 중 col1이 NULL을 만나면 차선책으로 서브쿼리 테이블에 대해서 풀 테이블 스캔을 사용할 것이 라는 사실을 알려주는 키워드다.
•
칼럼이 NOT NULL로 정의되지는 않았지만 이러한 NULL 비교 규칙을 무시해도 된다면 col1이 절대 NULL은 될 수 없다는 것을 MySQL 옵티마이저에게 알려주면 된다.
◦
col1 IS NOT NULL이라는 조건을 지정하는 것

10.3.12.6 Impossible HAVING

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

10.3.12.7 Impossible WHERE

•
“Impossible HAVING”과 비슷하며, WHERE 조건이 항상 FALSE가 될 수밖에 없는 경우 “Impossible WHERE”가 표시된다.

10.3.12.8 LooseScan

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

10.3.12.9 No matching min/max row

•
쿼리의 WHERE 조건절을 만족하는 레코드가 한 건도 없는 경우 일반적으로 Impossible WHERE ... 문장이 Extra 칼럼에 표시된다.
•
MIN이나 MAX와 같은 집함 함수가 있는 쿼리의 조건절에 일치하는 레코드가 한 건도 없을 때는 EXTRA 칼럼에 `No matching min/max row라는 메시지가 풀력된다.
•
그리고 MIN이나 MAX의 결과로 NULL이 반환된다.

10.3.12.10 no matching row in const table

•
const 방법으로 접근할 때 일치하는 레코드가 없다면 no matching row in const table이라는 메시지를 표시한다.
EXPLAIN SELECT * FROM dept_emp de, ( SELECT emp_no FROM employees WHERE emp_no=0 ) tb1 WHERE tb1.emp_no=de.emp_no AND de.dept_no='d005';
SQL
복사
•
이 메시지 또한 mpossible WHERE ...와 같은 종류로, 실행 계획을 만들기 위한 기초 자료가 없음을 의미한다.

10.3.12.11 No matching rows after partition pruning

•
해당 메시지는 파티션된 테이블에 대한 UPDATE 또는 DELETE 명령의 실행 계획에서 표시될 수 있는데, 해당 파티션에서 UPDATE하거나 DELETE할 대상 레코드가 없을 때 표시된다.

10.3.12.12 No tables used

•
FROM 절이 없는 쿼리 문장이나 FROM DUAL 형태의 쿼리 실행 계획에서는 Extra 칼럼에 해당 메시지가 출력된다.
•
다른 DBMS와는 달리 MySQL 서버는 FROM 절이 없는 쿼리도 허용된다.
•
이처럼 FROM 절 자체가 없거나 FROM 절에 상수 테이블을 의미하는 DUAL이 사용될 때는 Extra 칼럼에 해당 메시지가 표시된다.

10.3.12.13 Not exists

•
A테이블에는 존재하지만 B테이블에는 없는 값을 조회해야 하는 쿼리를 사용해야할 경우가 있다.
◦
이럴 때는 주로 NOT IN(subquery) 형태나 NOT EXISTS 연산자를 주로 사용한다.
◦
이러한 형태의 조인을 안티조인(Anti-JOIN)이라고 한다.
◦
똑같은 처리를 아우터 조인(LEFT OUTER JOIN)을 이용해서도 구현할 수 있다.
•
아우터 조인을 이용해 안티조인을 수행하는 쿼리에서 해당 메시지가 표시된다.

10.3.12.14 Plan isn’t ready yet

•
8.0 버전에서는 다음과 같이 다른 커넥션에서 실행 중인 쿼리의 실행 계획을 살펴볼 수 있다.
•
EXPLAIN FOR CONNECTION 명령을 실행했을 때 Extra 칼럼에 해당 메시지가 표시될 때도 있다
•
이 경우는 해당 커넥션에서 아직 쿼리의 실행 계획을 수립하지 못한 상태에서 EXPLAIN FOR CONNECTION 명령이 실행된 것을 의미한다.
•
이 경우에는 대상 커넥션의 쿼리가 실행 계획을 수립할 여유 시간을 좀 더 주고, 다시 EXPLAIN FOR CONNECTION 명령을 실행하면 된다.

10.3.12.15 Range checked for each record(index map: N)

•
조인 조건에 상수가 없고 둘 다 변수인 경우 옵티마이저는 어떤 테이블을 먼저 읽고 조인을 위해 다른 테이블을 읽을 때 인덱스 레인지 스캔과 풀 테이블 스캔 중에서 어느 것이 효율적일지 판단할 수 없게 된다.
•
테이블의 레코드를 하나씩 읽을 때마다 값이 계속 바뀌므로 쿼리의 비용 계산을 위한 기준 값이 계속 변하는 것이다.
•
그래서 어떤 접근 방법으로 다른 테이블을 읽는 것이 좋을지 판단할 수 없다.
•
해당 메세지는 레코드마다 인덱스 레인지 스캔을 체크한다는 의미라고 말할 수 있다.
EXPLAIN SELECT * FROM employees e1, employees e2 WHERE e2.emp_no >= e1.emp_no;
SQL
복사
실행 계획 처리 시나리오
실행 계획
•
Extra 칼럼의 출력 내용 중에서 “(index map: 0x1)”은 사용할지 말지를 판단하는 후보 인덱스의 순번을 나타낸다.
◦
0x1은 16진수로 해석을 위해서는 우선 이진수로 표현을 바꿔야 한다.
◦
위의 실행 계획에서는 0x1이 표시됐는데, 이는 이진수로 바꿔도 1이다.
•
이 쿼리는 e2(employees) 테이블의 첫 번째 인덱스를 사용할지 아니면 테이블을 풀 스캔할지를 매 레코드 단위로 결정하면서 처리된다.
•
쿼리 실행 계획의 type 칼럼의 값이 ALL로 표시되어 풀 테이블 스캔으로 처리된 것으로 해석하기 쉽다.
•
Extra 칼럼에 “Range checked for each record”가 표시되면 type 칼럼에는 ALL로 표시된다.
◦
즉 “index map”에 표시된 후보 인덱스를 사용할지 여부를 검토해서 이 후보 인덱스가 별로 도움이 되지 않는다면 최종적으로 풀 테이블 스캔을 사용하기 때문에 ALL로 표시된 것이다.

10.3.12.16 Recursive

•
8.0 버전부터는 CTE(Common Table Expression)를 이용해 재귀 쿼리를 작성할 수 있게 됐다.
•
MySQL에서 재귀 쿼리는 다음 예제와 같이 WITH 구문을 이용해 CTE를 사용하면 된다.
WITH RECURSIVE cte (n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 5 ) SELECT * FROM cte;
SQL
복사
•
WITH절 실행 작업
◦
"n"이라는 칼럼 하나를 가진 cte라는 이름의 내부 임시 테이블을 생성
◦
"n" 칼럼의 값이 1부터 5까지 1씩 증가하게 해서 레코드 5건을 만들어서 cte 내부 임시 테이블에 저장
•
WITH 절 다음의 SELECT 쿼리에서는 WITH 절에서 생성된 내부 임시 테이블을 WHERE 절이 없으 므로 풀 스캔해서 결과를 반환한다.

10.3.12.17 Rematerialize

•
8.0 버전부터는 래터럴 조인(LATERAL JOIN) 기능이 추가됐는데, 이 경우 래터럴로 조인되는 테이블은 선행 테이블의 레코드별로 서브쿼리를 실행해서 그 결과를 임시 테이블에 저장한다.
•
매번 임시 테이블을 새로 생성하는 과정 실행시 Rematerialize 메세지가 표시된다.

10.3.12.18 Select tables optimized away

•
MIN() 또는 MAX()만 SELECT 절에 사용되거나 GROUP BY로 MIN(), MAX()를 조회하는 쿼리가 인덱스를 오름차순/내림차순으로 1건만 읽는 형태의 최적화가 적용된다면, Extra 칼럼에 “Select tables optimized away”가 표시된다.
•
MyISAM 테이블에 대해서는 GROUP BY 없이 COUNT(*)만 SELECT할 때도 이런 형태의 최적화가 적용된다.
◦
MyISAM 테이블은 전체 레코드 건수를 별도로 관리하기 때문에 인덱스나 데이터를 읽지 않고도 전체 건수를 빠르게 조회할 수 있다.

10.3.12.19 Start temporary, End temporary

•
세미 조인 최적화 중에서 Duplicate Weed-out 최적화 전략이 사용되면 옵티마이저는 실행 계획의 Extra 칼럼에 “Start temporary”와 “End temporary” 문구를 표시하게 된다.
•
Duplicate Weed-out 최적화 전략은 불필요한 중복 건을 제거하기 위해서 내부 임시 테이블을 사용한다.
•
이때 조인되어 내부 임시 테이블에 저장되는 테이블을 식별할 수 있게 조인의 첫 번째 테이블에 “Start temporary” 문구를 보여주고 조인이 끝나는 부분에 “End temporary” 문구를 표시해준다.

10.3.12.20 unique row not found

•
두 개의 테이블이 각각 유니크 칼럼으로 아우터 조인을 수행하는 쿼리에서 아우터 테이블에 일치하는 레코드가 존재하지 않을 때 Extra 칼럼에 해당 메세지가 표시된다.

10.3.12.21 Using filesort

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

10.3.12.22 Using index (커버링 인덱스)

•
데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때 Extra 칼럼에 “Using index”가 표시된다.
•
인덱스를 이용해 처리하는 쿼리에서 가장 큰 부하를 차지하는 부분은 인덱스 검색에서 일치하는 키 값들의 레코드를 읽기 위해 데이터 파일을 검색하는 작업이다.
◦
최악의 경우에는 인덱스를 통해 검색된 결과 레코드 한 건 한 건마다 디스크를 한 번씩 읽어야 할 수도 있다.
•
레코드 건수에 따라 차이가 있겠지만 쿼리를 커버링 인덱스로 처리할 수 있을 때와 그렇지 못할 때의 성능 차이는 수십 배에서 수백 배까지 날 수 있다.
•
하지만 무조건 커버링 인덱스로 처리하려고 인덱스에 많은 칼럼을 추가하면 더 위험한 상황이 초래될 수도 있다.
•
너무 과도하게 인덱스의 칼럼이 많아지면 인덱스의 크기가 커져서 메모리 낭비가 심해지고 레코드를 저장하거나 변경하는 작업이 매우 느려질 수 있기 때문

10.3.12.23 Using index condition

•
옵티마이저가 인덱스 컨디션 푸시 다운(Index condition pushdown) 최적화를 사용하면 Extra 칼럼에 “Using index condition” 메시지가 표시된다.

10.3.12.24 Using index for group-by

•
GROUP BY 처리를 위해 MySQL 서버는 그루핑 기준 칼럼을 이용해 정렬 작업을 수행하고 다시 정렬된 결과를 그루핑하는 형태의 고부하 작업을 필요로 한다.
•
하지만 GROUP BY 처리가 인덱스를 이용하면 별도의 추가 정렬 작업 없이 정렬된 인덱스 칼럼을 순서대로 읽으면서 그루핑 작업만 수행한다.
•
이렇게 GROUP BY 처리에 인덱스를 이용하면 레코드의 정렬이 필요하지 않고 인덱스의 필요한 부분만 읽으면 되기 때문에 상당히 효율적이고 빠르게 처리된다.
10.3.12.24.1 타이트 인덱스 스캔(인덱스 스캔)을 통한 GROUP BY 처리
•
인덱스를 이용해 GROUP BY 절을 처리할 수 있더라도 AVG, SUM, COUNT처럼 조회하려는 값이 모든인 덱스를 다 읽어야 할 때는 필요한 레코드만 듬성듬성 읽을 수가 없다.
•
인덱스를 사용하기는 하지만, 이를 루스 인덱스 스캔이라고 하지는 않는다.
•
이러한 쿼리의 실행 계획에는 “Using index for group-by” 메시지가 출력되지 않는다.
10.3.12.24.2 루스 인덱스 스캔을 통한 GROUP BY 처리
•
단일 칼럼으로 구성된 인덱스에서는 그루핑 칼럼 말고는 아무것도 조회하지 않는 쿼리에서 루스 인덱스 스캔을 사용할 수 있다.
•
다중 칼럼으로 만들어진 인덱스에서는 GROUP BY 절이 인덱스를 사용 할 수 있어야 함은 물론이고 MIN이나 MAX 같이 조회하는 값이 인덱스의 첫 번째 또는 마지막 레코드만 읽어도 되는 쿼리는 루스 인덱스 스캔이 사용될 수 있다.

10.3.12.25 Using index for skip scan

•
인덱스 스킵 스캔 최적화를 사용하면 Extra 칼럼에 “Using index for skip scan” 메시지를 표시한다.

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

•
일반적으로 빠른 쿼리 실행을 위해 조인되는 칼럼은 인덱스를 생성한다.
•
실제로 조인에 필요한 인덱스는 조인되는 양쪽 테이블 칼럼 모두가 필요한 것이 아니라 조인에서 뒤에 읽는 테이블의 칼럼에만 필요하다.
•
옵티마이저도 조인되는 두 테이블에 있는 각 칼럼에서 인덱스를 조사하고, 인덱스가 없는 테이블이 있으면 그 테이블을 먼저 읽어서 조인을 실행한다.
•
뒤에 읽는 테이블(드리븐 테이블)은 검색 위주로 사용되기 때문에 인덱스가 없으면 성능에 미치는 영향이 매우 크기 때문이다.
•
조인이 수행될 때 드리븐 테이블의 조인 칼럼에 적절한 인덱스가 있다면 아무런 문제가 되지 않는다.
◦
하지만 드리븐 테이블에 검색을 위한 적절한 인덱스가 없다면 MySQL은 블록 네스티드 루프 조인이나 해시 조인을 사용한다.
•
조인되는 칼럼에 인덱스가 적절하게 준비돼 있다면 조인 버퍼는 크게 신경 쓰지 않아도 된다.
•
만약 그렇지 않다면 조인 버퍼를 너무 부족하거나 너무 과다하게 사용되지 않게 적절히 설정하는 것이 좋다.

10.3.12.27 Using MRR

•
InnoDB를 포함한 스토리지 엔진 레벨에서는 쿼리 실행의 전체적인 부분을 알지 못하기 때문에 최적화에 한계가 있다.
•
아무리 많은 레코드를 읽는 과정이라 하더라도 스토리지 엔진은 MySQL이 넘겨주는 키 값을 기준으로 레코드를 한 건 한 건 읽어서 반환하는 방식으로밖에 작동하지 못하는 한계점이 있다.
•
실제 매번 읽어서 반환하는 레코드가 동일 페이지에 있다고 하더라도 레코드 단위로 API의 호출이 필요한 것이다.
•
이 같은 단점을 보완하기 위해 MRR(Multi Range Read)이라는 최적화를 도입했다.
•
여러 개의 키 값을 한 번에 스토리지 엔진으로 전달하고, 스토리지 엔진은 넘겨받은 키 값들을 정렬해서 최소한의 페이지 접근만으로 필요한 레코드를 읽을 수 있게 최적화한다.
•
MRR이 도입되면서 각 스토리지 엔진은 디스크 접근을 최소화할 수 있게 된다

10.3.12.28 Using sort_union(), Using union(), Using intersect()

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

10.3.12.29 Using temporary

•
쿼리를 처리하는 동안 중간 결과를 담아 두기 위해 임시 테이블을 사용한다.
•
임시 테이블은 메모리상에 생성될 수도 있고 디스크상에 생성될 수도 있다. 쿼리의 실행 계획에서 Extra 칼럼에 “Using temporary” 키워드가 표시되면 임시 테이블을 사용한 것이다.
•
디스크를 사용할지, 메모리를 사용할지는 실행계획으로는 판단이 어렵다.

10.3.12.30 Using where

•
MySQL 엔진 레이어에서 별도의 가공을 해서 필터링 작업을 처리한 경우에만 Extra 칼럼에 “Using where” 코멘트가 표시된다.

10.3.12.31 Zero limit

•
메타데이터만 읽고자 할경우 아래 쿼리를 사용하며, 이 경우에는 해당 메세지가 출력된다.
EXPLAIN SELECT * FROM employees LIMIT 0;
SQL
복사
Made with