3️⃣

고급 최적화

•

옵티마이저의 옵션은 조인 관련된 옵티마이저 옵션과 옵티마이저 스위치로 구분할 수 있는데, 이들은 MySQL 서버의 고급 최적화 기능을 활성화할지를 제어하는 용도로 사용된다.

1. 옵티마이저 스위치 옵션

•

옵티마이저 스위치 옵션은 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 세미 조인 최적화를 사용할지 여부
materialization	on	Materialization 최적화를 사용할지 여부
subquery_materialization_cost_based	on	비용 기반의 Materialization 최적화를 사용할지 여부

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

: MRR은 Multi Range Read를 줄여서 부르는 말로 MySQL 서버는 조인 대상 테이블 중 하나로부터 레코드를 읽어서 조인 버퍼에 버퍼링함. 즉, 드라이빙 테이블의 레코드를 읽어서 드리븐 테이블과의 조인을

즉시 실행하지 않고 조인 대상을 버퍼링하는 것 그리고 조인 버퍼에 레코드가 가득 차면 그때 한 번에 스토리지 엔진으로 요청, 이렇게 하면 읽어야 할 레코드 들을 데이터 데이터 페이지에 정렬된 순서로

접근해서 디스크의 데이터 페이지 읽기를 최소화 할 수 있다. 이러한 읽기 방식을 MRR이라 하고 이를 응용해서 실행하는 조인 방식을 BKA

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

: MySQL 서버에서 사용되는 대부분의 조인은 네스티드 루프 조인이다.

: 프로그래밍 언어에서 마치 중첩된 반복 명령을 사용하는 것처럼 작동한다고 해서 Nested Loop Join욿 블록 네스티드 루프 조인은 조인 버퍼가 사용되는 것과 드라이빙 테이블, 드리븐 테이블의 처리 순서에

따라서 구분

: MySQL 8.0.18 버전부터는 해시 조인 알고리즘이 도입되어 이 방식은 사용되지 않음

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

ALTER TABLE
	employeesadd 
INDEX
	ix_lastname_firstname (last_name, first_name);

SELECT
	*
FROM
	employeeswhere 
WHERE
	last_name = 'Acton'
and 
	first_namelike '%sal';
SQL
복사

: 위의 SELECT 쿼리에서 index_condition_pushdown을 off 로 설정하면 last_name을 기준으로 인덱스를 타고 first_name like '%sal' 조건은 레코드에 직접 접근해서 검사, 그러나 index_condition_pushdown을

on으로 설정하면 index 내에서 비교를 수행하고 비교한 대상 레코드에만 접근해서 데이터를 가져올 수 있음

인덱스 확장(use_index_extensions)

: 세컨더리 인덱스에 자동으로 추가된 프라이머리 키를 활용할 수 있게 할지를 결정하는 옵션

: InnoDB 스토리지 엔진에서 세컨더리 인덱스는 데이터 레코드를 찾아가기 위해 프라이머리 키를 포함

: 프라이머리 키가 a 이고 세컨더리 인덱스가 b 로 되어있으면 실제로 세컨더리 인덱스는 (b, a)로 작동

인덱스 머지(index_merge)

: 인덱스 머지 실행 계획을 사용하면 하나의 테이블에 대해 2개 이상의 인덱스를 이용해 쿼리를 처리

: 하나의 인덱스를 사용해서 작업 범위를 충분히 줄일 수 있는 경우라면 하나의 인덱스만 활용하는 것이 효율적, 하지만 쿼리에 사용된 각각의 조건이 서로 다른 인덱스를 사용할 수 있고 그 조건을

만족하는 레코드 건수가 많을 것으로 예상 될 때 MySQL 서버는 인덱스 머지 실행 계획을 선택

: 인덱스 머지는 다음의 3개의 세부 실행 계획으로 나누어 볼 수 있다.

•

index_merge_intersection

•

index_merge_union

•

index_merge_sort_union

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

: 이 실행 계획은 여러 개의 인덱스를 각각 검색해서 그 결과의 교집합을 반환하며 실행계획의 Extra 칼럼에 Using intersect로 표시됨을 확인 가능

SELECT 
	*
FROM 
	employeeswhere 
WHERE 
	first_name='George'
AND
	emp_no
	BETWEEN 
		10000 AND 20000;
SQL
복사

: 위의 쿼리에서 두 조건에 대한 인덱스가 모두 존재하고 모두 상대적으로 많은 레코드를 가져와서 처리해야 한다면 인덱스를 각각 검색해서 교집합하는 방식이 더 효율적일 수 있음

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

: 이 실행 계획은 여러 개의 인덱스를 각각 검색해서 그 결과의 합집합을 반환함, 실행계획의 Extra 칼럼에 Using union로 표시됨을 확인할 수 있음

SELECT 
	*
FROM 
	employeeswhere 
WHERE
	first_name='Matt'
OR
	hire_date='2022-07-13';
SQL
복사

: 이 경우 인덱스 머지 방식을 사용할 때 중복을 제거해주어야 함

: 각각의 인덱스로 조회 시에 emp_no(primary key)를 기준으로 정렬이 되어있으므로 인덱스 머지를 수행할 때 각 집합에서 하나씩 가져와 비교하며 죽복된 레코드를 걸러낼 수 있다.(우선순위 큐)

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

: 위의 Union 알고리즘에서 정렬된 결과로 중복제거를 하는데 정렬이 이미 되어있으므로 필요하지 않음 하지만 도중에 정렬이 필요한 경우에는 Sort union 알고리즘을 사용

세미 조인

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

: 다른 테이블과 실제 조인을 수행하지는 않고, 단지 다른 테이블에서 조건에 일치하는 레코드가 있는지 없는지만 체크하는 형태의 쿼리를 세미 조인이라 한다.

•

Table Pull-out

•

Duplicate Weed-out

•

First Match

•

Loose Scan

•

Materialization

10.

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

: 세미 조인의 서브쿼리에 사용된 테이블을 아우터 쿼리로 끄집어낸 후에 쿼리를 조인 쿼리로 재작성하는 형태의 최적화 ⇒ 뭔소린규..? 아우터 쿼리..?

⇒ IN(subquery) 형태의 세미 조인이 가장 빈번하게 사용되는 형태의 쿼리

: 실행계획의 Extra 칼럼에 특별한 문구가 출력되지 않는다. 따라서, table pullout 최적화가 사용됐는지 확인하려면 Show warings 명령으로 MySQL 옵티마이저가 재작성한 쿼리를 살펴봐야함

⇒ 이때, IN(subquery) 형태는 사라지고 join으로 재작성된 것을 확인할 수 있다.

11.

퍼스트 매치(firstmatch)

: IN(subquery) 형태의 세미 조인을 EXISTS(subquery) 형태로 튜닝한 것과 비슷한 방법으로 실행되며 Extra 칼럼에 firstmatch() 문구가 출력

12.

루스 스캔(loosescan)

: 조인 시 Loose Index Scan과 비슷한 읽기 방식을 사용하는 것

13.

구체화(Materialization)

: Mataerialization 최적화는 세미 조인에 사용된 서브쿼리를 통째로 구체화해서 쿼리를 최적화, 여기서 구체화는 내부 임시 테이블을 만드는 것을 의미한다.

: optimizer_switch 시스템 변수에서 semijoin, materialization 옵션이 모두 on인 경우에 활성화

14.

중복 제거(Duplicated Weed-out)

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

SELECT 
	*
FROM 
	employees e
WHERE
	e.emp_no
IN (
		SELECT
			s.emp_no
		FROM
			salaries s
		WHERE 
			s.salary > 150000
	);
SQL
복사

SELECT 
	e.*
FROM 
	employees e, salaries s
WHERE
	e.emp_no = s.emp_no
AND
	s.salary > 150000
GROUP BY 
	e.emp_no;
SQL
복사

15.

컨디션 팬아웃(condition_fanout_filter)

: 조인을 실행할 때 테이블의 순서는 쿼리의 성능에 매우 큰 영향을 미치므로 MySQL 옵티마이저는 여러 테이블이 조인되는 경우 가능하다면 일치하는 레코드 건수가 적은 순서대로 조인을 실행

: 실행계획에는 쿼리 실행시 읽게 될 rows의 갯수와 실행 결과 rows의 비율인 filtered 칼럼이 존재, rows * filtered / 100이 쿼리 실행 결과 나오게 될 rows 수

: 옵티마이저는 condition_fanout_filter 최적화 기능을 활성화하여 보다 정교한 계산을 할 수 있음

•

WHERE 조건절에 사용된 칼럼에 인덱스가 있는 경우

•

WHERE 조건절에 사용된 칼럼에 히스토그램이 존재하는 경우

16.

파생 테이블 머지(derived_merge)

: MySQL 5.7버전부터는 파생 테이블로 만들어지는 서브쿼리를 외부 쿼리와 병합해서 서브쿼리 부분을 제거하는 최적화가 도입

: derived_merge 최적화 옵션을 통해 이러한 최적화를 활성화할지 여부를 결정

SELECT
	*
FROM (
	SELECT
		*
	FROM
		employeeswhere 
	WHERE 
		first_name='Matt'
	) dervied_table
WHERE
	derived_table.hire_date = '2022-07-01'
SQL
복사

: 위의 쿼리 실행 계획을 보면 from 절에 사용된 서브쿼리를 파생 테이블이라 부르며 이러한 임시 테이블이 외부 쿼리로 병합된 경우 옵티마이저가 작성한 쿼리는 다음과 같다.

SELECT 
	*
FROM 
	employees
WHERE 
	employees.hiredate = '2022-07-01'
AND 
	employees.first_name = 'Matt'
SQL
복사

17.

인비저블 인덱스(use_invisible_indexes)

: MySQL 8.0 버전부터는 인덱스의 가용 상태를 제어할 수 있는 기능이 추가됐으며, 이를 통해 인덱스를 삭제하지 않고, 해당 인덱스를 사용하지 못하게 제어 가능

ALTER TABLE 
	...
ALTER INDEX 
	... [ VISIBLE | INVISIBILE ]
SQL
복사

18.

스킵 스캔(skip_scan)

: 인덱스의 핵심은 값이 정렬돼 있다는 것이며, 이로 인해 인덱스를 구성하는 칼럼의 순서가 매우 중요, (A, B, C)로 구성된 인덱스가 있을 때 B와 C 칼럼에 대한 조건을 가지고 있다면 인덱스 활용 불가

: 인덱스 스킵 스캔은 제한적이긴 하지만 인덱스의 이러한 제약을 해소하는 최적화 기법

ALTER TABLE 
	EMPLOYEES
ADD INDEX 
	IX_GENDER_BIRTHDATE (GENDER, BIRTH_DATE);
SQL
복사

: 위의 인덱스에서 이 경우 select * from employees where birth_date >= '1992-01-11'; 쿼리를 실행할 때 인덱스를 사용할 수 있을까?

⇒ MySQL 8.0 버전 부터는 인덱스 스킵 스캔 최적화가 도입되어 후행 칼럼만으로 인덱스를 이용한 쿼리 성능 개선이 가능하다.

•

단, 선행 칼럼이 소수의 유니크한 값을 가질 때

19.

해시 조인(hash_join)

: 많은 사용자들이 해시 조인 기능을 기대하는 이유가 기존의 네스티드 루프 조인보다 해시 조인이 빠르다고 생각해서이다. 하지만 항상 그렇지는 않음

: 해시 조인 쿼리는 최고 스루풋 전략에 적합하고, 네스티드 루프 조인은 최고 응답 속도 전략에 적합

•

최고 스루풋 전략 : 첫 번째 레코드를 찾는 데는 시간이 많이 걸리지만 최종 레코드를 찾는 데까지는 많이 걸리지 않음

•

네스티드 루프 조인 : 첫 번째 레코드를 찾는 것은 빠르지만 마지막 레코드를 찾는 데에 시간이 많이 걸림

: 해시 조인 절차

•

빌드 단계 : 조인 대상 테이블 중에서 레코드 건수가 적은 테이블을 골라서 해시 테이블을 생성하는 작업 수행

•

프로브 단계 : 나머지 테이블의 레코드를 읽어서 해시 테이블의 일치 레코드를 찾는 과정

20.

인덱스 정렬 선호(prefer_ordering_index)

: MySQL 옵티마이저는 ORDER BY 또는 GROUP BY를 인덱스를 사용해 처리 가능한 경우 쿼리의 실행 계획에서 이 인덱스의 가중치를 높이 설정해서 실행하는데 이런 가중치 부여를 방지하기 위해

prefer_ordering_index 옵션이 추가되었다. 이를 off 로 설정하면 된다.

2. 조인 최적화 알고리즘

: 5.0부터 존재했던 조인 쿼리의 실행 계획 최적화를 위한 알고리즘

Exhaustive 검색 알고리즘

: FROM 절에 명시된 모든 테이블의 조합에 대해 실행 계획의 비용을 계산해서 최적의 조합 1개를 찾는 방법

Greedy 검색 알고리즘

: Exhaustive보다 어려운 검색 알고리즘

전체 N개의테이블 중에서 optimizer_search_depth 시스템 설정 변수에 정의된 개수의 테이블로 가능한 조인 조합을 생성

1번에서 생성된 조인 조합 중에서 최소 비용의 실행 계획 하나를 선정

2번에서 선정된 실행 계획의 첫 테이블을 부분 실행 계획의 첫 번째 테이블로 선정

전체 N-1개의 테이블 중에서 optimizer_search_depth 시스템 설정 변수에 정의된 개수의 테이블로 가능한 조인 조합을 생성

4번에서 생성된 조인 조합들을 하나씩 3번에서 생성된 부분 실행 계획에 대입해 실행 비용을 계산

5번의 비용 계산 결과 최적의 실행 계획에서 두 번째 테이블을 3번에서 새성된 부분 실행 계획의 두 번째 테이블로 선정

남은 테이블이 모두 없어질 때까지 4~6번까지의 과정을 반복

최종적으로 부분 실행 계획이 테이블의 조인 순서로 결정됨

: 시스템 변수

•

optimizer_search_depth : Greedy 검색 알고리즘과 Exhaustive 검색 알고리즘 중에서 어떤 알고리즘을 사용할지 결정

•

optimizer_prune_level : Heuristic 검색이 작동하는 방식 제어