[스터디] 17. 고급 최적화 및 쿼리 힌트

목차

9.3 고급 최적화

옵티마이저 옵션은 조인 관련된 옵티마이저 옵션과 옵티마이저 스위치로 구분할 수 있음.

9.3.1 옵티마이저 스위치 옵션

• 옵티마이저 스위치 옵션은 optimizer_switch 시스템 변수를 이용해서 제어함.
• optimizer_switch 시스템 변수에는 여러 개의 옵션을 세트로 묶어서 설정하는 방식으로 사용함.

옵티마이저 스위치 이름	기본값	설명
batched_key_access	off	BKA 조인 알고리즘을 사용할지 여부 설정
block_nested_loop	on	Block Nested Loop 조인 알고리즘을 사용할지 여부 설정
engine_condition_pushdown	on	Engine Condition Pushdown 기능을 사용할지 여부 설정
index_condition_pushdown	on	Index Condition Pushdown 기능을 사용할지 여부 설정
use_index_extensions	on	Index Extension 최적화를 사용할지 여부 설정
index_merge	on	Index Merge 최적화를 사용할지 여부 설정
index_merge_intersection	on	Index Merge Intersection 최적화를 사용할지 여부 설정
index_merge_sort_union	on	Index Merge Sort Union 최적화를 사용할지 여부 설정
index_merge_union	on	Index Merge Union 최적화를 사용할지 여부 설정
mrr	on	MRR 최적화를 사용할지 여부 설정
mrr_cost_based	on	비용 기반의 MRR 최적화를 사용할지 여부 설정
semijoin	on	세미 조인 최적화를 사용할지 여부 설정
firstmatch	on	FirstMatch 세미 조인 최적화를 사용할지 여부 설정
loosescan	on	LooseScan 세미 조인 최적화를 사용할지 여부 설정
subquery_materialization_cost_based	on	비용 기반의 Materialization 최적화를 사용할지 여부 설정

▲ 최적화 옵션 표

• 각각의 옵티마이저 스위치 옵션은 "default"와 "on", "off" 중에서 하나를 설정할 수 있음.
  • on: 해당 옵션을 활성화
  • off: 해당 옵션 비활성화
  • default: 기본값

9.3.1.1 MRR과 배치 키 액세스(mrr & batched_key_access)

• MRR(Multi-Range Read)은 매뉴얼에서는 DS-MRR(Disk Sweep Multi-Range Read)이라고도 함.
• MySQL 서버에서 지금까지 지원하던 조인 방식은 드라이빙 테이블(조인에서 제일 먼저 읽는 테이블)의 레코드를 한 건 읽어서 드리븐 테이블(조인되는 테이블에서 드라이빙이 아닌 테이블들)의 일치하는 레코드를 찾아서 조인을 수행하는 것이었음. → 네스티드 루프 조인(Nested Loop Join)
• 조인 처리는 MySQL 엔진이 처리 하지만, 실제 레코드를 검색하고 읽는 부분은 스토리지 엔진이 담당함. 이때 드라이빙 테이블의 레코드 건별로 드리븐 테이블의 레코드를 찾으면, 레코드를 찾고 읽는 스토리지 엔진에서는 아무런 최적화를 수행할 수가 없음.
• 단점을 보완하기 위해 MySQL 서버는 조인 대상 테이블 중 하나로부터 레코드를 읽어서 조인 버퍼에 버퍼링함. 드라이빙 테이블의 레코드를 읽어서 드리븐 테이블과의 조인을 즉시 실행하지 않고 조인 대상을 버퍼링하는 것임.
• 조인 버퍼에 레코드가 가득 차면 비로소 MySQL 엔진은 버퍼링된 레코드를 스토리지 엔진으로 한 번에 요청함. 스토리지 엔진은 읽어야 할 레코드들을 데이터 페이지에 정렬된 순서로 접근해서 디스크의 데이터 페이지 읽기를 최소화할 수 있음. → MRR(Multi-Range Read)
• MRR을 응용해서 실행되는 조인 방식을 BKA(Batched Key Access) 조인이라고 함. BKA 조인 최적화는 단점 때문에 기본적으로 비활성화되어 있음. 쿼리의 특성에 따라 BKA 조인이 큰 도움이 되는 경우도 있지만, 부가적인 정렬 작업이 필요해지면서 성능에 안 좋은 영향을 미치는 경우도 있음.

9.3.1.2 블록 네스티드 루프 조인(block_nested_loop)

• MySQL 서버에서 사용되는 대부분의 조인은 네스티드 루프 조인(Nested Loop Join)인데, 조인의 연결 조건이 되는 칼럼에 모두 인덱스가 있는 경우 사용되는 조인 방식임.
• 네스티드 루프 조인과 블록 네스티드 루프 조인(Block Nested Loop Join)의 가장 큰 차이는 조인 버퍼(join_buffer_size 시스템 설정으로 조정되는 조인을 위한 버퍼)가 사용되는지 여부와 조인에서 드라이빙 테이블과 드리븐 테이블이 어떤 순서로 조인되는지임.
• 조인 알고리즘에서 "Block"이라는 단어가 사용되면 조인용으로 별도의 버퍼가 사용됐다는 것을 의미함. 조인 쿼리의 실행 계획에서 Extra 칼럼에 "Using Join buffer"라는 문구가 표시되면 그 실행 계획은 조인 버퍼를 사용한다는 것임.
• 조인은 드라이빙 테이블에서 일치하는 레코드의 건수만큼 드리븐 테이블을 검색하면서 처리됨. 드라이빙 테이블은 한 번에 쭉 읽지만, 드리븐 테이블은 여러 번 잀음.
• 어떤 방식으로도 드리븐 테이블의 풀 테이블 스캔이나 인덱스 풀 스캔을 피할 수 없다면, 옵티마이저는 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시한 후 드리븐 테이블과 이 메모리 캐시를 조인하는 형태로 처리함. 이때 사용되는 메모리의 캐시를 조인 버퍼(Join buffer)라고 함. 조인 버퍼는 join_buffer_size라는 시스템 변수로 크기를 제한할 수 있으며, 조인이 완료되면 조인 버퍼는 바로 해제됨.

9.3.1.3 인덱스 컨디션 푸시다운(index_condition_pushdown)

• MySQL 5.6 버전부터는 인덱스 컨디션 푸시다운(Index Condition Pushdown)이라는 기능이 도입되었음.

-- 테스트를 위해 인덱스를 생성하고, 옵티마이저 스위치를 조정해서 인덱스 컨디션 푸시다운 기능을 비활성화함.
mysql> ALTER TABLE employees ADD INDEX ix_lastname_firstname (last_name, first_name);

mysql> SET optimizer_switch='index_condition_pushdown=off';
mysql> SHOW VARIABLES LIKE 'optimizer_switch' \G
Variable_name : optimizer_switch
	Value : ...,index_condition_pushdown=off,...

• 쿼리를 실행할 때 스토리지 엔진이 몇 건의 레코드를 읽는지를 한 번 살펴보면, 예제 쿼리에서 last_name='Acton' 조건은 위에서 생성한 ix_lastname_firstname 인덱스를 레인지 스캔으로 사용할 수 있음. 하지만 first_name LIKE '%sal' 조건은 인덱스 레인지 스캔으로는 검색해야할 인덱스의 범위를 좁힐 수가 없음.

mysql> SELECT * FROM employees WHERE last_name='Acton' AND first_name LIKE '%sal';

• 위 쿼리에서는 last_name 조건은 ix_lastname_firstname 인덱스의 특정 범위만 조회할 수 있는 조건이며, first_name LIKE '%sal' 조건은 데이터를 모두 읽은 후 사용자가 원하는 결과인지 하나씩 비교해보는 조건(체크 조건 또는 필터링 조건이라 함)으로만 사용됨.
• 쿼리의 실행 계획을 확인해 보면 Extra 칼럼에 "Using where"가 표시된 것을 확인할 수 있음. "Using where"는 InnoDB 스토리지 엔진이 읽어서 반환해준 레코드가 인덱스를 사용할 수 없는 WHERE 조건에 일치하는지 검사하는 과정을 의미함.

9.3.1.4 인덱스 확장(use_index_extensions)

• use_index_extensions 옵티마이저 옵션: InnoDB 스토리지 엔진을 사용하는 테이블에서 세컨더리 인덱스에 자동으로 추가된 프라이머리 키를 활용할 수 있게 할지를 결정하는 옵션
• 8.8절에서 살펴본 것처럼 InnoDB 스토리지 엔진은 프라이머리 키를 클러스터링 키로 생성함. 모든 세컨더리 인덱스는 리프 노드에 프라이머리 키 값을 가짐.

-- 프라이머리 키와 세컨더리 인덱스를 가진 테이블
mysql> CREATE TABLE dept_emp (
	emp_no INT NOT NULL, dept_no CHAR(4) NOT NULL,
	from_date DATE NOT NULL, to_date DATE NOT NULL,
	PRIMARY KEY (dept_no,emp_no),
	KEY ix_fromdate (from_date)
	) ENGINE=InnoDB;

• dept_emp 테이블에서 프라이머리 키는 (dept_no, emp_no)이며, 세컨더리 인덱스 ix_fromdate는 from_ date 칼럼만 포함함. 세컨더리 인덱스는 데이터 레코드를 찾아가기 위해 프라이머리 키인 dept_no와 emp_no 칼럼을 순서대로(프라이머리 키에 명시된 순서) 포함함. 최종적으로 ix_ fromdate 인덱스는 (from_date, dept_no, emp_no) 조합으로 인덱스를 생성한 것과 흡사하게 작동할 수 있음.
• 예전 MySQL 버전에서는 세컨더리 인덱스의 마지막에 자동 추가되는 프라이머리 키를 제대로 활용하지 못했지만, MySQL 서버가 업그레이드되면서 옵티마이저는 ix_fromdate 인덱스의 마지막에 (dept_no, emp_no) 칼럼이 숨어있다는 것을 인지하고 실행 계획을 수립하도록 개선되었음.

9.3.1.5 인덱스 머지(index_merge)

• 인덱스를 이용해 쿼리를 실행하는 경우, 대부분 옵티마이저는 테이블별로 하나의 인덱스만 사용하도록 실행 계획을 수립함.]
• 하지만 인덱스 머지 실행 계획을 사용하면 하나의 테이블에 대해 2개 이상의 인덱스를 이용해 쿼리를 처리함.
• 하나의 인덱스만 사용해서 작업 범위를 충분히 줄일 수 있는 경우라면 테이블별로 하나의 인덱스만 활용하는 것이 효율적임. 하지만 쿼리에 사용된 각각의 조건이 서로 다른 인덱스를 사용할 수 있고, 그 조건을 만족하는 레코드 건수가 많을 것으로 예상될 경우에 MySQL 서버는 인덱스 머지 실행 계획을 선택함.
• 인덱스 머지 실행 계획은 3개의 세부 실행 계획으로 나누어 볼 수 있음. 여러 개의 인덱스를 통해 결과를 가져온다는 것은 동일하지만 각각의 결과를 어떤 방식으로 병합할 지에 따라 구분됨
  1. index_merge_intersection
  2. index_merge_sort_union
  3. index_merge_union
• index_merge 옵티마이저 옵션은 위의 나열된 3개의 최적화 옵션을 한 번에 모두 제어할 수 있는 옵션임.

9.3.1.6 인덱스 머지 - 교집합(index_merge_intersection)

• 예제 쿼리는 2개의 WHERE 조건을 가지고 있는데, employees 테이블의 first_name 칼럼과 emp_no 칼럼 모두 각각의 인덱스(ix_firstname, PRIMARY)를 가지고 있음.
• 즉, 어떤 조건을 사용하더라도 인덱스를 사용할 수 있음. 따라서 옵티마이저는 ix_firstname과 PRIMARY 키를 모두 사용해서 쿼리를 처리하기로 결정함. 실행 계획의 Extra 칼럼에 "Using intersect"라고 표시되는 것은 이 쿼리가 여러 개의 인덱스를 각각 검색해서 그 결과의 교집합만 반환했다는 것을 의미함.

mysql> EXPLAIN SELECT *
	FROM employees
	WHERE first_name='Georgi' AND emp_no BETWEEN 10000 AND 20000;
    
+-------------+----------------------+---------+----------------------------------------------------+
| type        | key                  | key_len | Extra                                              |
+-------------+----------------------+---------+----------------------------------------------------+
| index_merge | ix_firstname,PRIMARY | 62,4    | Using intersect(ix_firstname,PRIMARY); Using where |
+-------------+----------------------+---------+----------------------------------------------------+

• first_name 칼럼의 조건과 emp_no 칼럼의 조건 중 하나라도 충분히 효율적으로 쿼리를 처리할 수 있다면, 옵티마이저는 2개의 인덱스를 모두 사용하는 실행 계획을 사용하지 않을 것임. 즉, 옵티마이저가 각각의 조건에 일치하는 레코드 건수를 예측해 본 결과, 두 조건 모두 상대적으로 많은 레코드를 가져와야 한다는 것을 알게 된 것.
• 인덱스 머지 실행 계획이 아니라면 다음 방식으로 처리해야 함.
  1. "first_name='Georgi'" 조건만 인덱스를 사용했다면, 일치하는 레코드 253건을 검색한 다음 데이터 페이지에서 레코드를 찾고 emp_no 칼럼의 조건에 일치하는 레코드들만 반환하는 형태로 처리되어야 함.
  2. "emp_no BETWEEN 10000 AND 20000" 조건만 인덱스를 사용했다면, 프라이머리 키를 이용해 10,000건을 읽어와서 "first_name='Georgi'" 조건에 일치하는 레코드만 반환하는 형태로 처리되어야 함.

9.3.1.7 인덱스 머지 - 합집합(index_merge_union)

• 인덱스 머지의 'Using union'은 WHERE 절에 사용된 2개 이상의 조건이 각각의 인덱스를 사용하되 OR 연산자로 연결된 경우에 사용되는 최적화임.

-- employees 테이블의 first_name 칼럼이 ‘Matt’이거나 hire_date 칼럼이 ‘1987-03-31’인 사원 정보를 조회하는 쿼리
mysql> SELECT *
FROM employees WHERE first_name='Matt' OR hire_date='1987-03-31';

+-------------+--------------------------+---------+---------------------------------------+
| type        | key                      | key_len | Extra                                 |
+-------------+--------------------------+---------+---------------------------------------+
| index_merge | ix_firstname,ix_hiredate | 58,3    | Using union(ix_firstname,ix_hiredate);|
+-------------+--------------------------+---------+---------------------------------------+

• 예제 쿼리는 2개의 조건이 OR로 연결되어 있음. employees 테이블에는 first_ name 칼럼과 hire_date 칼럼에 각각 ix_firstname 인덱스와 ix_hiredate가 준비됨. 그래서 first_name='Matt'인 조건과 hire_date='1987-03-31' 조건이 각각의 인덱스를 사용할 수 있음.
• 이 쿼리의 실행 계획은 'Using union' 최적화를 사용함. 쿼리의 실행 계획에서 Extra 칼럼에 "Using union(ix_firstname, ix_hiredate)"은 인덱스 머지 최적화가 ix_firstname 인덱스의 검색 결과와 ix_hiredate 인덱스 검색 결과를 'Union' 알고리즘으로 병합했다는 것을 의미함. 여기서 병합은 두 집합의 합집합을 가져왔다는 의미임.
• MySQL 서버는 두 집합에서 하나씩 가져와서 서로 비교하면서 프라이머리 키의 값이 중복된 레코드들을 정렬 없이 걸러낼 수 있음. 정렬된 두 집합의 결과를 하나씩 가져와 중복 제거를 수행할 때 사용된 알고리즘을 우선순위 큐(Priority Queue)라고 함.

9.3.1.8 인덱스 머지 - 정렬 후 합집합(index_merge_sort_union)

• 인덱스 머지 최적화의 'Union' 알고리즘은 두 결과 집합의 중복을 제거하기 위해 정렬된 결과를 필요로 함. 하지만 MySQL 서버는 별도의 정렬을 수행하지 않고, 별도의 정렬이 필요하지도 않음.
• 인덱스 머지 작업을 하는 도중에 결과의 정렬이 필요한 경우, MySQL 서버는 인덱스 머지 최적화의 'Sort union' 알고리즘을 사용함.

mysql> EXPLAIN
SELECT * FROM employees WHERE first_name='Matt' OR hire_date BETWEEN '1987-03-01' AND '1987-03-31'

-- ‘Union’ 알고리즘을 사용해 2개의 쿼리로 분리함.
mysql> SELECT * FROM employees WHERE first_name='Matt';	-- 쿼리는 결과가 emp_no로 정렬되어 출력됨.
mysql> SELECT * FROM employees WHERE hire_date BETWEEN '1987-03-01' AND '1987-03-31';	-- 쿼리의 결과가 emp_no 칼럼으로 정렬되어 있지 않음.

-- 결론: 중복을 제거하기 위해 우선순위 큐를 사용하는 것이 불가능함. MySQL 서버는 두 집합의 결과에서 중복을 제거하기 위해 각 집합을 emp_no 칼럼으로 정렬한 다음 중복 제거를 수행함.

9.3.1.9 세미 조인(semijoin)

• 세미 조인(Semi-Join): 다른 테이블과 실제 조인을 수행하지는 않고, 단지 다른 테이블에서 조건에 일치하는 레코드가 있는지 없는지만 체크하는 형태의 쿼리
• MySQL 5.7 서버는 세미 조인 형태의 쿼리를 최적화하는 부분이 상당히 취약함.

mysql> SELECT *
FROM employees e
WHERE e.emp_no IN
(SELECT de.emp_no FROM dept_emp de WHERE de.from_date='1995-01-01');

• 위 예제에 대해 MySQL 서버에 세미 조인 최적화가 도입되기 전 MySQL 서버는 employees 테이블을 풀 스캔하면서 한 건 한 건 서브쿼리의 조건에 일치하는지 비교했음. 대략 57건만 읽으면 될 쿼리를 30만 건 넘게 읽어서 처리됨.
• 세미 조인(Semi-join) 형태의 쿼리와 안티 세미 조인(Anti Semi-join) 형태의 쿼리는 최적화 방법에 차이가 있음.
• "= (subquery)" 형태와 "IN (subquery)" 형태의 세미 조인 쿼리에 대해 3가지 최적화 방법을 적용할 수 있음.
  1. 세미 조인 최적화
  2. IN-to-EXISTS 최적화
  3. MATERIALIZATION 최적화
• "<> (subquery)" 형태와 "NOT IN (subquery)" 형태의 안티 세미 조인 쿼리에 대해서는 2가지의 최적화 방법이 있음.
  1. IN-to-EXISTS 최적화
  2. MATERIALIZATION 최적화
• MySQL 서버 8.0 버전부터는 세미 조인 쿼리의 성능을 개선하기 위한 최적화 전략이 있음. MySQL 서버 매뉴얼에서는 아래 최적화 전략들을 모아서 세미 조인 최적화라고 부름.
  • Table Pull-out
  • Duplicate Weed-out
  • First Match
  • Loose Scan
  • Materialization
• 쿼리에 사용되는 테이블과 조인 조건의 특성에 따라 MySQL 옵티마이저는 사용 가능한 전략들을 선별적으로 사용함.
  • Table pull-out 최적화 전략은 사용 가능하면 항상 세미 조인보다는 좋은 성능을 내기 때문에 별도로 제어하는 옵티마이저 옵션을 제공하지 않음.
  • First Match와 Loose Scan 최적화 전략은 각각 firstmatch와 loosescan 옵티마이저 옵션으로 사용 여부를 결정할 수 있음.
  • Duplicate Weed-out과 Materialization 최적화 전략은 materialization 옵티마이저 스위치로 사용 여부를 선택할 수 있음.
• optimizer_switch 시스템 변수의 semijoin 옵티마이저 옵션은 firstmatch와 loosescan, materialization 옵티마이저 옵션을 한 번에 활성화하거나 비활성화할 때 사용함.

9.3.1.10 테이블 풀-아웃(Table Pull-out)

• Table pullout: 세미 조인의 서브쿼리에 사용된 테이블을 아우터 쿼리로 끄집어낸 후에 쿼리를 조인 쿼리로 재작성하는 형태의 최적화. 서브쿼리 최적화가 도입되기 이전에 수동으로 쿼리를 튜닝하던 대표적인 방법.

-- 부서 번호가 'd009'인 부서에 소속된 모든 사원을 조회하는 쿼리
mysql> EXPLAIN
	SELECT * FROM employees e
	WHERE e.emp_no IN (SELECT de.emp_no FROM dept_emp de WHERE de.dept_no='d009');

• 예제 쿼리는 아마도 IN(subquery) 형태의 세미 조인이 가장 빈번하게 사용되는 형태의 쿼리일 것임.
• MySQL 8.0 버전에서 이 쿼리의 실행 계획에 dept_emp 테이블과 employees 테이블이 순서대로 표시되어 있는데, 가장 중요한 부분은 id 칼럼의 값이 모두 1이라는 것임. 이 값이 동일한 값을 가진다는 것은 두 테이블이 서브쿼리 형태가 아니라 조인으로 처리됐음을 의미함.
• Table pullout 최적화는 모든 형태의 서브쿼리에서 사용될 수 있는 것은 아님.
• Table pullout 최적화의 제한 사항과 특성
  • Table pullout 최적화는 세미 조인 서브쿼리에서만 사용 가능함.
  • Table pullout 최적화는 서브쿼리 부분이 UNIQUE 인덱스나 프라이머리 키 룩업으로 결과가 1건인 경우에만 사용 가능함.
  • Table pullout이 적용된다고 하더라도 기존 쿼리에서 가능했던 최적화 방법이 사용 불가능한 것은 아니므로 MySQL에서는 가능하다면 Table pullout 최적화를 최대한 적용함.
  • Table pullout 최적화는 서브쿼리의 테이블을 아우터 쿼리로 가져와서 조인으로 풀어쓰는 최적화를 수행하는데, 만약 서브쿼리의 모든 테이블이 아우터 쿼리로 끄집어 낼 수 있다면 서브쿼리 자체는 없어짐.
  • MySQL에서는 "최대한 서브쿼리를 조인으로 풀어서 사용해라"라는 튜닝 가이드가 많은데, Table pullout 최적화는 이 가이드를 그대로 실행하는 것임. 서브쿼리를 조인으로 풀어서 사용할 필요가 없음.

9.3.1.11 퍼스트 매치(firstmatch)

• First Match 최적화 전략은 IN(subquery) 형태의 세미 조인을 EXISTS(subquery) 형태로 튜닝한 것과 비슷한 방법으로 실행됨.

-- 이름이 ‘Matt’인 사원 중에서 1995년 1월 1일부터 30일 사이에 직급이 변경된 적이 있는 사원을 조회하는 쿼리
mysql> EXPLAIN SELECT *
	FROM employees e
	WHERE e.first_name='Matt' AND e.emp_no IN ( SELECT t.emp_no FROM titles t WHERE t.from_date BETWEEN '1995-01-01' AND '1995-01-30' );

• 위 쿼리의 실행 계획에서 id 칼럼의 값이 모두 "1"로 표시되어 있으므로 titles 테이블이 서브쿼리 패턴으로 실행되지 않고, 조인으로 처리됐다는 것을 알 수 있음. "FirstMatch(e)" 문구는 employees 테이블의 레코드에 대해 titles 테이블에 일치하는 레코드 1건만 찾으면 더이상의 titles 테이블 검색을 하지 않는다는 것을 의미함.
• FirstMatch는 서브쿼리가 아니라 조인으로 풀어서 실행하면서 일치하는 첫번째 레코드만 검색하는 최적화를 실행한 것임. First Match 최적화는 MySQL 5.5에서 수행했던 최적화 방법인 IN-toEXISTS 변환과 거의 비슷한 처리 로직을 수행함.
• MySQL 5.5의 IN-to-EXISTS 변환과 First Match 최적화 전략을 비교하면 다음과 같음.
  • 여러 테이블이 조인되는 경우, 원래 쿼리에는 없던 동등 조건을 옵티마이저가 자동으로 추가하는 형태의 최적화가 실행되기도 함. 기존의 IN-to-EXISTS 최적화에서는 이러한 동등 조건 전파(Equality propagation)가 서브쿼리 내에서만 가능했지만, FirstMatch에서는 조인 형태로 처리되기 때문에 서브쿼리뿐만 아니라 아우터 쿼리의 테이블까지 전파될 수 있음. 최종적으로는 FirstMatch 최적화로 실행되면 더 많은 조건이 주어지는 것이므로 더 나은 실행 계획을 수립할 수 있음.
  • IN-to-EXISTS 변환 최적화 전략에서는 아무런 조건 없이 변환이 가능한 경우에는 무조건 그 최적화를 수행했음. 하지만 FirstMatch 최적화에서는 서브쿼리의 모든 테이블에 대해 FirstMatch 최적화를 수행할지 아니면 일부 테이블에 대해서만 수행할지 취사선택할 수 있다는 것이 장점임.
• FirstMatch 최적화의 제한 사항과 특성
  • FirstMatch는 서브쿼리에서 하나의 레코드만 검색되면 더이상의 검색을 멈추는 단축 실행 경로(Short-cut path)이기 때문에, FirstMatch 최적화에서 서브쿼리는 그 서브쿼리가 참조하는 모든 아우터 테이블이 먼저 조회된 이후에 실행됨.
  • FirstMatch 최적화가 사용되면 실행 계획의 Extra 칼럼에는 "FirstMatch(table-N)" 문구가 표시됨.
  • FirstMatch 최적화는 상관 서브쿼리(Correlated subquery)에서도 사용될 수 있음.
  • FirstMatch 최적화는 GROUP BY나 집합 함수가 사용된 서브쿼리의 최적화에는 사용될 수 없음.
• FirstMatch 최적화는 optimizer_switch 시스템 변수에서 semijoin 옵션과 firstmatch 옵션이 모두 ON으로 활성화된 경우에만 사용할 수 있음. firstmatch 최적화만 비활성화하려면 semijoin 옵티마이저 옵션은 ON으로 활성화하되, firstmatch 옵티마이저 옵션만 OFF로 비활성화하면 됨.

9.3.1.12 루스 스캔(loosescan)

• 세미 조인 서브쿼리 최적화의 LooseScan은 인덱스를 사용하는 GROUP BY 최적화 방법에서 살펴본 "Using index for group-by"의 루스 인덱스 스캔(Loose Index Scan)과 비슷한 읽기 방식을 사용함.

-- dept_emp 테이블에 존재하는 모든 부서 번호에 대해 부서 정보를 읽어 오기 위한 쿼리
mysql> EXPLAIN
	SELECT * FROM departments d
	WHERE d.dept_no IN ( SELECT de.dept_no FROM dept_emp de );

• departments 테이블의 레코드 건수는 9건밖에 되지 않지만, dept_emp 테이블의 레코드 건수는 무려 33만 건 가까이 저장됨.  그런데 dept_emp 테이블에는 (dept_no + emp_no) 칼럼의 조합으로 프라이머리키 인덱스가 만들어져 있음. 이 프라이머리 키는 전체 레코드 수는 33만 건 정도 있지만, dept_no 만으로 그루핑해서 보면 결국 9건밖에 없다는 것을 알 수 있음.
• dept_emp 테이블의 프라이머리 키를 루스 인덱스 스캔으로 유니크한 dept_no만 읽으면 중복된 레코드까지 제거하면서 효율적으로 서브쿼리 부분을 실행할 수 있음.
• 서브쿼리에 사용된 dept_emp 테이블이 드라이빙 테이블로 실행되며, dept_emp 테이블의 프라이머리 키를 dept_no 부분에서 유니크하게 한 건씩만 읽고 있음. 루스 인덱스 스캔의 "Using index for group-by"도 dept_emp 테이블의 프라이머리 키를 읽는 방식과 동일하게 작동함.
• 예제 쿼리의 실행 계획으로 Extra 칼럼에 "LooseScan"이라는 문구가 표시되어 있음. 실행 계획의 각 테이블에 할당된 id 칼럼의 값이 동일하게 1이라는 것도 MySQL 내부적으로는 조인처럼 처리됐다는 것임.
• LooseScan 최적화는 루스 인덱스 스캔으로 서브쿼리 테이블을 읽고, 그 다음으로 아우터 테이블을 드리븐으로 사용해서 조인을 수행함. 서브쿼리 부분이 루스 인덱스 스캔을 사용할 수 있는 조건이 갖춰져야 사용할 수 있는 최적화임.

-- 루스 인덱스 스캔 최적화를 사용할 수 있는 서브쿼리들
SELECT .. FROM .. WHERE expr IN (SELECT keypart1 FROM tab WHERE ...)
SELECT .. FROM .. WHERE expr IN (SELECT keypart2 FROM tab WHERE keypart1='상수' ...)

-- 옵티마이저가 LooseScan 최적화를 사용하지 못하게 비활성화하는 방법
mysql> SET optimizer_switch='loosescan=off';

9.3.1.13 구체화(Materialization)

• Materialization 최적화는 세미 조인에 사용된 서브쿼리를 통째로 구체화해서 쿼리를 최적화한다는 의미임.
• 구체화(Materialization)는 내부 임시 테이블을 생성한다는 것을 의미함.

-- 1995년 1월 1일 조직이 변경된 사원들의 목록을 조회하는 쿼리
-- IN (subquery) 포맷의 세미 조인을 사용하는 예제
mysql> EXPLAIN SELECT *
	FROM employees e
	WHERE e.emp_no IN
		(SELECT de.emp_no FROM dept_emp de
		WHERE de.from_date='1995-01-01');
        
+----+--------------+-------------+--------+-------------+--------------------+
| id | select_type  | table       | type   | key         | ref                |
+----+--------------+-------------+--------+-------------+--------------------+
| 1  | SIMPLE       | <subquery2> | ALL    | NULL        | NULL               |
| 1  | SIMPLE       | e           | eq_ref | PRIMARY     | <subquery2>.emp_no |
| 2  | MATERIALIZED | de          | ref    | ix_fromdate | const              |
+----+--------------+-------------+--------+-------------+--------------------+

• 위 쿼리는 FirstMatch 최적화를 사용하면 employees 테이블에 대한 조건이 서브쿼리 이외에는 아무것도 없기 때문에 employees 테이블을 풀 스캔해야 할 것임. 이런 형태의 세미 조인에서는 First Match 최적화가 성능 향상에 별로 도움이 되지 않음.
• MySQL 서버 옵티마이저는 이런 형태의 쿼리를 위해 서브쿼리 구체화(Subquery Materialization)라는 최적화를 도입함. 실행 계획에서는 예제 쿼리의 서브쿼리가 "서브쿼리 구체화" 최적화를 사용하는 형태로 수립되는 것을 볼 수 있음.
• Materialization 최적화의 제한 사항과 특성
  • IN(subquery)에서 서브쿼리는 상관 서브쿼리(Correlated subquery)가 아니어야 함.
  • 서브쿼리는 GROUP BY나 집합 함수들이 사용돼도 구체화를 사용할 수 있음.
  • 구체화가 사용된 경우에는 내부 임시 테이블이 사용됨.
• Materialization 최적화는 optimizer_switch 시스템 변수에서 semijoin 옵션과 materialization 옵션이 모두 ON으로 활성화된 경우에만 사용됨. MySQL 8.0 버전에서는 기본적으로 이 두 옵션은 ON으로 활성화되어 있음. Materialization 최적화만 비활성화하려면 semijoin 옵티마이저 옵션은 ON으로 활성화하되, materialization 옵티마이저 옵션만 OFF로 비활성화하면 됨.

9.3.1.14 중복 제거(Duplicated Weed-out)

• Duplicate Weedout: 세미 조인 서브쿼리를 일반적인 INNER JOIN 쿼리로 바꿔서 실행하고 마지막에 중복된 레코드를 제거하는 방법으로 처리되는 최적화 알고리즘

-- 급여가 150000 이상인 사원들의 정보를 조회하는 쿼리
mysql> EXPLAIN
	SELECT * FROM employees e
	WHERE e.emp_no IN (SELECT s.emp_no FROM salaries s WHERE s.salary>150000);

• salaries 테이블의 프라이머리 키가 (emp_no + from_date)이므로 salary가 150000 이상인 레코드를 salaries 테이블에서 조회하면 그 결과에는 중복된 emp_no가 발생할 수 있음. 그래서 GROUP BY 절을 넣어 주면 위의 세미 조인 서브쿼리와 동일한 결과를 얻을 수 있음.

mysql> SELECT e.*
	FROM employees e, salaries s
	WHERE e.emp_no=s.emp_no AND s.salary>150000
	GROUP BY e.emp_no;
    
+----+-------------+-------+--------+-----------+-------------------------------------------+
| id | select_type | table | type   | key       | Extra                                     |
+----+-------------+-------+--------+-----------+-------------------------------------------+
| 1  | SIMPLE      | s     | range  | ix_salary | Using where; Using index; Start temporary |
| 1  | SIMPLE      | e     | eq_ref | PRIMARY   | End temporary                             |
+----+-------------+-------+--------+-----------+-------------------------------------------+

• Duplicate Weedout 최적화 알고리즘은 원본 쿼리를 위와 같이 INNER JOIN + GROUP BY 절로 바꿔서 실행하는 것과 동일한 작업으로 쿼리를 처리함.
  1. salaries 테이블의 ix_salary 인덱스를 스캔해서 salary가 150000보다 큰 사원을 검색해 employees 테이블 조인을 실행
  2. 조인된 결과를 임시 테이블에 저장
  3. 임시 테이블에 저장된 결과에서 emp_no 기준으로 중복 제거
  4. 중복을 제거하고 남은 레코드를 최종적으로 반환
• 실행 계획에서는 "Duplicate Weedout"이라는 문구가 별도로 표시되지 않음. 하지만 Extra 칼럼에 "Start temporary"와 "End temporary" 문구가 별도로 표기됨. 1번에서 조인과 2번에서 저장하는 작업은 반복적으로 실행되는 과정임. 반복 과정이 시작되는 테이블의 실행 계획 라인에는 "Start temporary" 문구가, 반복 과정이 끝나는 테이블의 실행 계획 라인에는 "End temporary" 문구가 표시됨. Start / End temporary 문구의 구간이 Duplicate Weedout 최적화의 처리 과정이라고 볼 수 있음.
• Duplicate Weedout 최적화의 장점과 제약 사항
  • 서브쿼리가 상관 서브쿼리라고 하더라도 사용할 수 있는 최적화임.
  • 서브쿼리가 GROUP BY나 집합 함수가 사용된 경우에는 사용될 수 없음.
  • Duplicate Weedout은 서브쿼리의 테이블을 조인으로 처리하기 때문에 최적화할 수 있는 방법이 많음.

9.3.1.15 컨디션 팬아웃(condition_fanout_filter)

• 조인을 실행할 때 테이블의 순서는 쿼리의 성능에 매우 큰 영향을 미침.
• 예) A 테이블과 B 테이블을 조인할 때 A 테이블에는 조건에 일치하는 레코드가 1만 건이고 B 테이블에는 일치하는 레코드
  건수가 10건이라고 가정함. A 테이블을 조인의 드라이빙 테이블로 결정하면 B 테이블을 1만번 읽어야 함. 이때, B 테이블의 인덱스를 이용해 조인을 실행하더라도 레코드를 읽을 때마다 B 테이블의 인덱스를 구성하는 B-Tree의 루트 노드부터 검색을 실행해야 함. 그래서 MySQL 옵티마이저는 여러 테이블이 조인되는 경우 가능하다면 일치하는 레코드 건수가 적은 순서대로 조인을 실행함.

9.3.1.16 파생 테이블 머지(derived_merge)

• 예전 버전의 MySQL 서버에서는 FROM 절에 사용된 서브쿼리는 먼저 실행해서 그 결과를 임시 테이블로 만든 다음 외부 쿼리 부분을 처리함.

mysql> EXPLAIN
	SELECT * FROM (
	SELECT * FROM employees WHERE first_name='Matt'
	) derived_table
	WHERE derived_table.hire_date='1986-04-03';
    
+----+-------------+------------+------+--------------+
| id | select_type | table      | type | key          |
+----+-------------+------------+------+--------------+
| 1  | PRIMARY     | <derived2> | ref  | <auto_key0>  |
| 2  | DERIVED     | employees  | ref  | ix_firstname |
+----+-------------+------------+------+--------------+

• 쿼리의 실행 계획에서 employees 테이블을 읽는 라인의 select_type 칼럼의 값이 DERIVED라고 표시됨. emplyees 테이블에서 first_name 칼럼의 값이 ‘Matt’인 레코드들만 읽어서 임시 테이블을 생성하고, 이 임시 테이블을 다시 읽어서 hire_date 칼럼의 값이 ‘1986-04-03’인 레코드만 걸러내어 반환한 것임. 그래서 MySQL 서버에서는 이렇게 FROM 절에 사용된 서브쿼리를 파생 테이블 (Derived Table)이라고 부름.
• MySQL 서버는 내부적으로 임시 테이블을 생성하고 first_name='Matt'인 레코드를 employees 테이블에서 읽어서 임시 테이블로 INSERT함. 다시 임시 테이블을 읽으므로 MySQL 서버는 레코드를 복사하고 읽는 오버헤드가 더 추가됨. 내부적으로 생성되는 임시 테이블은 처음에는 메모리에 생성되지만, 임시 테이블에 저장될 레코드 건수가 많아지면 결국 디스크로 다시 기록되어야 함. 임시 테이블이 메모리에 상주할 만큼 크기가 작다면 성능에 큰 영향을 미치지 않겠지만, 레코드가 많아진다면 임시 테이블로 레코드를 복사하고 읽는 오버헤드로 인해 쿼리의 성능은 많이 느려짐.
• MySQL 5.7 버전부터는 파생 테이블로 만들어지는 서브쿼리를 외부 쿼리와 병합해서 서브쿼리 부분을 제거하는 최적화가 도입됨. derived_merge 최적화 옵션은 이러한 임시 테이블 최적화를 활성화할지 여부를 결정함. 위의 쿼리에서 임시 테이블이 외부 쿼리로 병합된 경우의 실행 계획은 다음과 같음.

+----+-------------+-----------+-------------+--------------------------+
| id | select_type | table     | type        | key                      |
+----+-------------+-----------+-------------+--------------------------+
| 1  | SIMPLE      | employees | index_merge | ix_hiredate,ix_firstname |
+----+-------------+-----------+-------------+--------------------------+

• 이 실행 계획에서는 select_type 칼럼이 DERIVED였던 라인이 없어지고, 서브쿼리 없이 employees 테이블을 조회하던 형태의 단순 실행 계획으로 바뀜. SHOW WARNINGS 명령으로 MySQL 옵티마이저가 새로 작성한 쿼리를 보면, 서브쿼리 부분이 어떻게 외부 쿼리로 병합됐는지 확인할 수 있음.
• 예전 버전의 MySQL 서버에서는 이렇게 서브쿼리로 작성된 쿼리를 외부 쿼리로 병합하는 작업을 DBA 가 수작업으로 처리했음. 이제는 MySQL 옵티마이저가 처리할 수 있음. 하지만 모든 쿼리에 대해 옵티마이저가 서브쿼리를 외부 쿼리로 병합할 수 있는 것은 아님.
• 옵티마이저가 자동으로 서브쿼리를 외부 쿼리로 병합할 수 없는 조건
  • SUM() 또는 MIN(), MAX() 같은 집계 함수와 윈도우 함수(Window Function)가 사용된 서브쿼리
  • DISTINCT가 사용된 서브쿼리
  • GROUP BY나 HAVING이 사용된 서브쿼리
  • LIMIT이 사용된 서브쿼리
  • UNION 또는 UNION ALL을 포함하는 서브쿼리
  • SELECT 절에 사용된 서브쿼리
  • 값이 변경되는 사용자 변수가 사용된 서브쿼리

9.3.1.17 인비저블 인덱스(use_invisible_indexes)

• MySQL 8.0 버전부터는 인덱스의 가용 상태를 제어할 수 있는 기능이 추가됨.
• MySQL 8.0 이전 버전까지는 인덱스가 존재하면 항상 옵티마이저가 실행 계획을 수립할 때 해당 인덱스를 검토하고 사용했음. MySQL 8.0 버전부터는 인덱스를 삭제하지 않고, 해당 인덱스를 사용하지 못하게 제어하는 기능을 제공함. ALTER TABLE ... ALTER INDEX ... [ VISIBLE | INVISIBLE ] 명령으로 인덱스의 가용 상태를 변경할 수 있음.

-- // 옵티마이저가 ix_hiredate 인덱스를 사용하지 못하게 변경
mysql> ALTER TABLE employees ALTER INDEX ix_hiredate INVISIBLE;

-- // 옵티마이저가 ix_hiredate 인덱스를 사용할 수 있게 변경
mysql> ALTER TABLE employees ALTER INDEX ix_hiredate VISIBLE;

-- 옵티마이저가 INVISIBLE 상태의 인덱스도 볼 수 있게 설정 (기본값은 off)
mysql> SET optimizer_switch='use_invisible_indexes=on';

9.3.1.18 스킵 스캔(skip_scan)

• 인덱스의 핵심은 값이 정렬돼 있다는 것임. 이로 인해 인덱스를 구성하는 칼럼의 순서가 매우 중요함.
• 예) (A, B, C) 칼럼으로 구성된 인덱스가 있을 때, 쿼리의 WHERE 절에 A와 B 칼럼에 대한 조건이 있다면 이 쿼리는 A 칼럼과 B 칼럼까지만 인덱스를 활용할 수 있음. WHERE 절에 A 칼럼에 대한 조건만 가지고 있다면 A 칼럼까지만 인덱스를 활용할 수 있음. WHERE 절에 B와 C 칼럼에 대한 조건을 가지고 있다면 이 쿼리는 인덱스를 활용할 수 없음.
• 인덱스 스킵 스캔은 이러한 인덱스의 제약 사항을 뛰어넘는 최적화 기법임.

-- employees 테이블의 인덱스
mysql> ALTER TABLE employees
	ADD INDEX ix_gender_birthdate (gender, birth_date);

-- 인덱스를 사용하기 위해서 WHERE 조건절에 gender 칼럼에 대한 비교 조건이 필수적임.

-- // ix_gender_birthdate 인덱스를 사용하지 못하는 쿼리
mysql> SELECT * FROM employees WHERE birth_date>='1965-02-01';

-- // ix_gender_birthdate 인덱스를 사용할 수 있는 쿼리
mysql> SELECT * FROM employees WHERE gender='M' AND birth_date>='1965-02-01';

• gender 칼럼과 birth_date 칼럼의 조건을 모두 가진 두 번째 쿼리는 인덱스를 효율적으로 사용할 수 있지만, gender 칼럼에 대한 비교 조건이 없는 첫 번째 쿼리는 인덱스를 사용할 수가 없음. 이런 경우에는 birth_date 칼럼부터 시작하는 인덱스를 새로 생성해야 함.
• MySQL 8.0 버전부터는 인덱스 스킵 스캔 최적화가 도입되어, 인덱스의 선행 칼럼이 조건절에 사용되지 않더라도 후행 칼럼의 조건만으로도 인덱스를 이용한 쿼리 성능 개선이 가능함. 예제의 첫 번째 쿼리를 실행할 때 MySQL 8.0 버전의 옵티마이저는 테이블에 존재하는 모든 gender 칼럼을 값을 가져와 두 번째 쿼리와 같이 gender 칼럼의 조건이 있는 것처럼 쿼리를 최적화함.
• 인덱스의 선행 칼럼이 매우 다양한 값을 가지는 경우에는 인덱스 스킵 스캔 최적화가 비효율적일 수 있음. MySQL 8.0 옵티마이저는 인덱스의 선행 칼럼이 소수의 유니크한 값을 가질 때만 인덱스 스킵 스캔 최적화를 사용함.
• 옵티마이저의 인덱스 스킵 스캔 최적화 기능은 다음과 같이 활성화 여부를 제어할 수 있음.

-- // 현재 세션에서 인덱스 스킵 스캔 최적화를 활성화
mysql> SET optimizer_switch='skip_scan=on';

-- // 현재 세션에서 인덱스 스킵 스캔 최적화를 비활성화
mysql> SET optimizer_switch='skip_scan=off';

-- // 특정 테이블에 대해 인덱스 스킵 스캔을 사용하도록 힌트를 사용
mysql> SELECT /*+ SKIP_SCAN(employees)*/ COUNT(*)
	FROM employees
	WHERE birth_date>='1965-02-01';

-- // 특정 테이블과 인덱스에 대해 인덱스 스킵 스캔을 사용하도록 힌트를 사용
mysql> SELECT /*+ SKIP_SCAN(employees ix_gender_birthdate)*/ COUNT(*)
	FROM employees
	WHERE birth_date>='1965-02-01';

-- // 특정 테이블에 대해 인덱스 스킵 스캔을 사용하지 않도록 힌트를 사용
mysql> SELECT /*+ NO_SKIP_SCAN(employees)*/ COUNT(*)
	FROM employees
	WHERE birth_date>='1965-02-01';

9.3.1.19 해시 조인(hash_join)

• MySQL 8.0.18 버전부터는 해시 조인이 지원됨.
• 네스티드 루프 조인과 해시 조인은 똑같은 시점에 시작했지만, 해시 조인이 먼저 끝남. 해시 조인은 첫 번째 레코드를 찾는 데는 시간이 많이 걸리지만, 최종 레코드를 찾는 데까지는 시간이 많이 걸리지 않음. 네스티드 루프 조인은 마지막 레코드를 찾는 데까지는 시간이 많이 걸리지만, 첫 번째 레코드를 찾는 것은 상대적으로 훨씬 빠름. 즉, 해시 조인 쿼리는 최고 스루풋(Best Throughput) 전략에 적합하며, 네스티드 루프 조인은 최고 응답 속도(Best Response-time) 전략에 적합하다는 것을 알 수 있음.
• 일반적인 웹 서비스는 온라인 트랜잭션(OLTP) 서비스이기 때문에 스루풋도 중요하지만 응답 속도가 더 중요함. 분석과 같은 서비스는 사용자의 응답 시간보다는 전체적으로 처리 소요 시간이 중요하기 때문에 응답 속도보다는 전체 스루풋이 중요함.
• MySQL 서버는 범용 RDBMS이며, 여기에서 범용은 온라인 트랜잭션 처리를 위한 데이터베이스 서버를 지칭하는 것임. MySQL 서버는 조인 조건의 칼럼이 인덱스가 없다거나 조인 대상 테이블중 일부의 레코드 건수가 매우 적은 경우 등에 대해서만 해시 조인 알고리즘을 사용하도록 설계됨. 그래서 해시 조인이 빠르다고 하니까 옵티마이저 힌트를 사용해서 강제로 쿼리의 실행 계획을 해시 조인으로 유도하는 것은 좋지 않음.
• MySQL 8.0.17 버전까지는 해시 조인 기능이 없었기 때문에 조인 조건이 좋지 않은 경우 블록 네스티드 루프 조인(Block Nested Loop)이라는 조인 알고리즘을 사용함. 블록 네스티드 루프 조인 또한 쿼리의 조인 조건이 인덱스를 제대로 활용할 수 없는 경우에만 사용되는 최적화 방법 수준으로 사용됨. 그래서 인덱스가 잘 설계된 데이터베이스에서는 블록 네스티드 루프 조인 실행 계획은 거의 볼 수 없음.
• MySQL 8.0.18과 8.0.19 버전에서는 동등 조인(Equi-Join)을 위해서는 해시 조인이 사용됐지만 안티 조인이나 세미 조인을 위해서는 블록 네스티드 루프 조인이 사용됨.
• MySQL 8.0.20 버전부터는 블록 네스티드 루프 조인은 더이상 사용되지 않고, 네스티드 루프 조인을 사용할 수 없는 경우에는 항상 해시 조인이 사용되도록 바뀜. 그리고 block_nested_loop 같은 optimizer_switch 또는 BNL과 NO_BNL과 같은 힌트들도 블록 네스티드 루프가 아닌 해시 조인을 유도하는 목적으로 사용됨.
• 일반적으로 해시 조인은 빌드 단계(Build-phase)와 프로브 단계(Probe-phase)로 나뉘어 처리됨.
  • 빌드 단계: 조인 대상 테이블 중에서 레코드 건수가 적어서 해시 테이블로 만들기에 용이한 테이블을 골라서 메모리에 해시 테이블을 생성(빌드)하는 작업을 수행함. 해시 테이블을 만들 때 사용되는 원본 테이블을 빌드 테이블이라고도 함.
  • 프로브 단계: 나머지 테이블의 레코드를 읽어서 해시 테이블의 일치 레코드를 찾는 과정. 이때 읽는 나머지 테이블을 프로브 테이블이라고도 함.
  • 어느 테이블이 빌드 테이블이고 어느 테이블이 프로브 테이블인지 식별하려면 EXPLAIN FORMAT=TREE 명령 또는 EXPLAIN ANALYZE 명령을 사용함.
• 해시 테이블을 메모리에 저장할 때 MySQL 서버는 join_buffer_size 시스템 변수로 크기를 제어할 수있는 조인 버퍼를 사용함. 조인 버퍼의 기본 크기는 256KB인데, 해시 테이블의 레코드 건수가 많아서 조인 버퍼의 공간이 부족할 수도 있음. 이러한 경우 MySQL 서버는 빌드 테이블과 프로브 테이블을 적당한 크기(하나의 청크가 조인 버퍼보다 작도록)의 청크로 분리한 다음, 청크별로 해시 조인을 처리함.
• MySQL 옵티마이저는 빌드 테이블의 크기에 따라 메모리에서 모두 처리 가능한 경우에 클래식 해시 조인(Classic hash join) 알고리즘을 사용하고, 해시 테이블이 조인 버퍼 메모리보다 큰 경우에 그레이스 해시 조인(Grace hash join) 알고리즘을 하이브리드(Hybrid)하게 활용하도록 구현됨.
• MySQL 서버의 해시 조인에서 해시 키를 만들 때 xxHash64 해시 함수를 사용하는데, xxHash64 해시 함수는 매우 빠르고 해시된 값의 분포도가 훌륭한 해시 알고리즘임.

9.3.1.20 인덱스 정렬 선호(prefer_ordering_index)

• MySQL 옵티마이저는 ORDER BY 또는 GROUP BY를 인덱스를 사용해 처리 가능한 경우, 쿼리의 실행 계획에서 이 인덱스의 가중치를 높이 설정해서 실행됨.

mysql> EXPLAIN
	SELECT *
	FROM employees
	WHERE hire_date BETWEEN '1985-01-01' AND '1985-02-01'
	ORDER BY emp_no;
    
+----+-----------+-------+---------+--------+-------------+
| id | table     | type  | key     | rows   | Extra       |
+----+-----------+-------+---------+--------+-------------+
| 1  | employees | index | PRIMARY | 300252 | Using where |
+----+-----------+-------+---------+--------+-------------+

• 이 쿼리는 다음 2가지 실행 계획을 선택할 수 있음.
  1. ix_hiredate 인덱스를 이용해 "hire_date BETWEEN '1985-01-01' AND '1985-02-01'" 조건에 일치하는 레코 드를 찾은 다음, emp_no로 정렬해서 결과를 반환
  2. employees 테이블의 프라이머리 키가 emp_no이므로 프라이머리 키를 정순으로 읽으면서 hire_date 칼럼의 조건에 일치하는지 비교 후 결과를 반환
• 상황에 따라 1번이 효율적일 수도 있고 2번이 효율적일 수도 있음. 일반적으로는 hire_date 칼럼의 조건에 부합되는 레코드 건수가 많지 않다면 1번이 효율적일 것임.
• MySQL 8.0.20 버전까지는 옵티마이저의 실수가 발생하면 다른 실행 계획을 사용하게 하기 위해 특정 인덱스(ORDER BY를 위한 인덱스)를 사용하지 못하도록 "IGNORE INDEX" 힌트를 사용함. MySQL 8.0.21 버전부터는 MySQL 서버 옵티마이저가 ORDER BY를 위한 인덱스에 너무 가중치를 부여하지 않도록 prefer_ordering_index 옵티마이저 옵션이 추가됨.

-- // 현재 커넥션에서만 prefer_ordering_index 옵션을 비활성화 (기본값 ON)
mysql> SET SESSION optimizer_switch='prefer_ordering_index=OFF';

-- // 현재 쿼리에 대해서만 prefer_ordering_index 옵션을 비활성화
mysql> SELECT /*+ SET_VAR(optimizer_switch='prefer_ordering_index=OFF') */
	...
	FROM
	...

9.3.2 조인 최적화 알고리즘

• MySQL 5.0 버전부터 조인 쿼리의 실행 계획 최적화를 위한 알고리즘이 2개 있음.
• 테이블의 개수가 많아지면 최적화된 실행 계획을 찾는 것이 상당히 어려워지고, 하나의 쿼리에서 조인되는 테이블의 개수가 많아지면 실행 계획을 수립하는 데만 몇 분이 걸릴 수 있음. 테이블의 개수가 특정 한계를 넘어서면 그때 부터는 실행 계획 수립에 소요되는 시간만 몇 시간이나 며칠로 늘어날 수 있음.
• 다음과 같이 간단히 4개의 테이블을 조인하는 쿼리 문장이 조인 옵티마이저 알고리즘에 따라 어떻게 처리되는지 살펴봄.

mysql> SELECT *
	FROM t1, t2, t3, t4
	WHERE ...

9.3.2.1 Exhaustive 검색 알고리즘

• Exhaustive 검색 알고리즘: MySQL 5.0과 그 이전 버전에서 사용되던 조인 최적화 기법. FROM 절에 명시된 모든 테이블의 조합에 대해 실행 계획의 비용을 계산해서 최적의 조합 1개를 찾는 방법.

9.3.2.2 Greedy 검색 알고리즘

• Greedy 검색 알고리즘: Exhaustive 검색 알고리즘의 시간 소모적인 문제점을 해결하기 위해 MySQL 5.0부터 도입된 조인 최적화 기법.
• Greedy는 Exhaustive 검색 알고리즘보다는 조금 복잡한 형태로 최적의 조인 순서를 결정함.
• MySQL에서는 조인 최적화를 위한 시스템 변수로 optimizer_prune_level과 optimizer_search_depth가 제공됨.
  • optimizer_search_depth 시스템 변수는 Greedy 검색 알고리즘과 Exhaustive 검색 알고리즘 중에서 어떤 알고리즘을 사용할지 결정하는 시스템 변수임. optimizer_search_depth는 0~62까지의 정숫값을 설정할 수 있는데, 1~62까지의 정숫값이 설정되면 Greedy 검색 대상을 지정된 개수로 한정해서 최적의 실행 계획을 산출함. optimizer_search_depth의 기본값은 62인데, 많은 테이블이 조인되는 쿼리에서는 상당히 부담이 될 수도 있음. optimizer_prune_level 시스템 변수가 0으로 설정된 경우에는 optimizer_search_depth의 설정값이 쿼리의 성능에 심각한 영향을 미칠 수 있으니 optimizer_ search_depth를 4~5 정도로 설정하는 것이 좋음.
  • optimizer_prune_level 시스템 변수는 MySQL 5.0부터 추가된 Heuristic 검색이 작동하는 방식을 제어함. Heuristic 검색의 가장 핵심적인 내용은 다양한 조인 순서의 비용을 계산하는 도중 이미 계산했던 조인 순서의 비용보다 큰 경우에는 언제든지 중간에 포기할 수 있다는 것임. optimizer_ prune_level이 "1"로 설정되면 옵티마이저는 조인 순서 최적화에 경험 기반의 Heuristic 알고리즘을 사용함. 그리고 이 값이 "0"으로 설정되면 경험 기반의 Heuristic 최적화가 적용되지 않음. Heuristic 조인 최적화는 조인 대상 테이블이 몇 개 되지 않더라도 상당한 성능 차이를 내므로 특별한 요건이 없다면 optimizer_ prune_level을 "0"으로 설정하지 않음.

 

이 글은 『Real MySQL 8.0 (1권)』 책을 학습한 내용을 정리한 것입니다.