[Real MySQL 8.0 1권] 09. 옵티마이저와 힌트 (2)

유혁·2026년 5월 21일

CS Database Real MySQL 8.0

Real MySQL 8.0

목록 보기

17/22

본 포스트는 Real MySQL 8.0 1권을 읽은 뒤 정리하는 글입니다.

9.3 고급 최적화

9.3.1 옵티마이저 스위치 옵션

옵티마이저 스위치 옵션은 optimizer_switch 시스템 변수를 이용해 여러 개의 옵션을 세트로 묶어서 설정하는 방식으로 사용하며, 설정할 수 있는 최적화 옵션은 다음과 같다.

옵티마이저 스위치 이름	기본값	설명
batced_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 최적화 사용 여부 (Materialization 세미 조인 최적화 포함)
subquery_materialization_cost_based	on	비용 기반 Materialization 최적화 사용 여부

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

MRR은 Multi-Range Read를 줄여서 부르는 것으로, DS-MRR(Disk Sweep Multi-Range Read)이라고도 한다.

MySQL 서버에서 지금까지 지원하던 조인 방식은 드라이빙 테이블의 레코드를 한 건 읽어서 드리븐 테이블의 일치하는 레코드를 찾아 조인을 수행하는 네스티드 루프방식을 사용했다. 조인 처리는 MySQL 엔진이 처리하지만, 실제 레코드를 검색하고 읽는 부분은 스토리지 엔진이 담당하므로, 네스티드 루프 방식을 사용하면 레코드를 찾고 읽는 스토리지 엔진에서는 아무런 최적화를 수행할 수 없다는 단점이 있다.

이를 보완하기 위해 드라이빙 테이블의 레코드를 읽어서 드리븐 테이블과의 조인을 즉시 실행하지 않고 조인 대상을 버퍼링하고, 조인 버퍼에 레코드가 가득 차면 MySQL 엔진은 버퍼링된 레코드를 스토리지 엔진으로 한 번에 요청하도록 하였다.

이로 인해 스토리지 엔진은 읽어야 할 레코드들을 데이터 페이지에 정렬된 순서로 접근해 디스크의 데이터 페이지 읽기를 최소화 할 수 있다.

이러한 읽기 방식을 MRR이라고 하며, MRR을 응용해서 실행되는 조인 방식을 BKA(Batched Key Access) 조인이라고 한다. BKA 조인은 부가적인 정렬 작업이 필요해지며 성능에 안 좋은 영향을 미치는 경우가 있어 기본적으로 비활성화돼있다.

블록 네스티드 루프 조인

네스티드 루프 조인(Nested Loop Join)은 조인의 연결 조건이 되는 칼럼에 모두 인덱스가 있는 경우 사용되는 조인 방식으로, 프로그래밍 언어에서 마치 중첩된 반복 명령을 사용하는 것처럼 작동한다고 해서 이와 같이 부르게 되었다.

네스티드 루프 조인과 블록 네스티드 로프 조인의 가장 큰 차이는 조인 버퍼의 사용 여부와 조인에서 드라이빙 테이블과 드리븐 테이블이 어떤 순서로 조인되느냐이다.

조인 알고리즘에서 "Block"이라는 단어가 사용되면 조인용으로 별도의 버퍼를 사용됐다는 것을 의미한다.

조인은 드라이빙 테이블에서 일치하는 레코드의 건수만큼 드리븐 테이블을 검색하며 처리된다. 즉 드라이빙 테이블은 한 번에 쭉 읽지만, 드리븐 테이블은 여러 번 읽어야 한다.

따라서 드리븐 테이블을 검색할 때 인덱스를 사용할 수 없는 쿼리는 상당히 느려지며, 옵티마이저는 최대한 인덱스를 사용할 수 있게 실행 계획을 수립한다.

하지만 드리븐 테이블이 인덱스를 사용할 수 없는 경우, 옵티마이저는 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시한 후 드리븐 테이블과 메모리 캐시를 조인하는 형태로 처리하며, 이때 사용되는 메모리의 캐시를 조인 버퍼(Join buffer)라고 한다. 조인이 완료되면 조인 버퍼는 바로 해제된다.

아래 쿼리의 경우 각 테이블에 대한 조건은 있지만. 조인 조건은 없어 각 테이블의 조건을 만족하는 레코드의 카테시안 조인을 수행한다.

mysql> SELECT *
		FROM dept_emp de, employees e
        WHERE de.from_date>'1995-01-01' AND e.emp_no<109004;

다음 이미지는 해당 쿼리에서 조인 버퍼가 어떻게 사용되는지 보여준다. 여기서 중요한 점은 조인 버퍼가 사용되는 쿼리에서는 조인의 순서가 거꾸로인 것처럼 실행된다는 것이다.

드라이빙 테이블의 결과는 조인 버퍼에 담아두고, 드리븐 테이블을 먼저 읽고 조인 버퍼에서 일치하는 레코드를 찾는 방식으로 처리되기 때문에 결과의 정렬 순서가 흐트러질 수 있다.

MySQL 8.0.20 버전부터는 해시 조인 알고리즘으로 대체되어 사용되고 있다.

인덱스 컨디션 푸시다운 (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> SELECT * FROM employees 
		WHERE last_name='Action' AND first_name LIKE '%sal';

위 쿼리에서 last_name='Action' 조건은 ix_lastname_firstname 인덱스를 레인지 스캔으로 사용할 수 있으나, first_name LIKE '%sal' 조건은 인덱스 레인지 스캔으로는 검색해야 할 인덱스의 범위를 좁힐 수 없어, 해당 조건은 데이터를 모두 읽은 후 필터링 조건으로만 사용된다.

실행 계획을 확인해보면, Extra 칼럼에 "Using Where"가 표시된 것을 확인할 수 있으며, 이는 InnoDB 스토리지 엔진이 읽어서 반환해준 레코드가 인덱스를 사용할 수 없는 WHERE 조건에 일치하는지 검사하는 과정을 의미한다.

다음 이미지는 인덱스 컨디션 푸시다운이 작동하지 않을 때의 처리 과정을 나타내고 있다. 여기서 중요한 점은, last_name='Action' 조건으로 인덱스 레인지 스캔을 하고 테이블의 레코드를 잃은 뒤, first_name LIKE '%sal' 조건으로 필터링 하는데, 테이블을 읽어 3건의 레코드를 읽었지만 일치하는 것은 단 1건이라는 것으로, 불필요한 레코드 읽기가 발생하게 된다는 것이다.

ix_lastname_firstname 인덱스의 first_name 칼럼을 이용하지 않고 다시 테이블의 레코드를 읽고 있는데, 인덱스를 비교하는 작업은 InnoDB 스토리지 엔진이 수행하지만 테이블의 레코드에서 first_name을 비교하는 작업은 MySQL 엔진이 수행하는 작업이다.

MySQL 5.0 버전까지는 인덱스를 범위 제한 조건으로 사용하지 못하는 first_name 조건은 MySQL 엔진이 스토리지 엔진으로 아예 전달해주지 않아 불필요한 테이블 읽기 작업이 발생한 것이다.

MySQL 5.6 버전부터는 인덱스를 범위 제한 조건으로 사용하지 못하더라도 인덱스에 포함된 칼럼의 조건이 있다면 모두 같이 모아서 스토리지 엔진으로 전달할 수 있게 핸들러 API가 개선되어, 인덱스를 이용해 최대한 필터링까지 완료해 꼭 필요한 레코드에 대해서만 테이블 읽기를 수행할 수 있게 되었다.

다음은 옵티마이저 옵션을 원래대로 되돌린 후의 실행 계획 및 인덱스 컨디션 푸시다운이 적용된 쿼리 처리 과정이다.

인덱스 확장 (use_index_extensions)

use_index_extension 옵티마이저 옵션은 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 테이블의 세컨더리 인덱스인 ix_fromdate는 프라이머리 키인 dept_no와 emp_no 칼럼을 프라이머리 키에 명시된 순서대로 포함하여, 최종적으로는 (from_date, dept_no, emp_no) 조합으로 인덱스를 생성한 것과 흡사하게 작동한다.

다음은 두 개의 쿼리 및 각각의 실행 계획을 보여준다.

mysql> SELECT COUNT(*) FROM dept_emp 
		WHERE from_date='1987-07-25' AND dept_no='d001';
   
mysql> SELECT COUNT(*) FROM dept_emp 
		WHERE from_date='1987-07-25';

dept_no='d001' 조건을 포함한 경우 실행 계획
dept_no='d001' 조건을 포함하지 않는 경우 실행 계획

실행 계획의 key_len 칼럼은 쿼리가 인덱스를 구성하는 칼럼 중에서 어느 부분까지 사용했는지를 바이트 수로 보여준다. dept_no 칼럼의 조건을 포함하는 경우, from_date(3바이트), dept_no(16바이트) 까지 사용했으며, 조건이 제거된 경우 from_date(3바이트)만을 사용한 것을 확인할 수 있다.

또한 정렬 작업도 인덱스를 활용해서 처리되는 장점도 존재한다. 아래 쿼리의 실행 계획에서 Extra 칼럼에 "Using Filesort"가 표시되지 않았다는 것은 별도의 정렬 작업 없이 인덱스 순서대로 레코드를 읽기만 하여 ORDER BY dept_no를 만족했다는 것을 의미한다.

mysql> SELECT * FROM dept_emp 
		WHERE from_date='1987-07-25' ORDER BY dept_no;

인덱스 머지 (index_merge)

인덱스 머지 실행 계획을 사용하면 하나의 테이블에 대해 2개 이상의 인덱스를 이용해 쿼리를 처리한다. 쿼리의 사용된 각각의 WHERE 조건이 서로 다른 인덱스를 사용할 수 있고, 그 조건을 만족하는 레코드 건수가 많을 것으로 예상될 때 MySQL 서버는 인덱스 머지 실행 계획을 실행한다.

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

다음 쿼리의 2개의 WHERE 조건은 모두 각각의 인덱스(ix_firstname, PRIMARY)를 가지고 있어, 어떤 조건을 사용하더라도 인덱스를 사용할 수 있다. 그에 따라 옵티마이저는 두 키를 모두 사용해 쿼리를 처리하기로 결정한다.

실행 계획의 Extra 칼럼에 "Using intersect"는 여러 개의 인덱스를 각각 검색해 그 결과의 교집합만을 반환했음을 의미한다.

mysql> EXPLAIN SELECT *
		FROM employees
        WHERE first_name='Georgi' AND emp_no BETWEEN 10000 AND 20000;

한 칼럼의 조건만으로도 충분히 효율적으로 쿼리를 처리할 수 있다면 옵티마이저는 2개의 인덱스를 모두 사용하지 않았을 것이며, 이는 각각의 조건에 일치하는 레코드 건수를 예측해 본 결과, 두 조건 모두 상대적으로 많은 레코드를 가져와야 한다는 것을 알게 된 것이다.

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

인덱스 머지의 "Using union"은 WHERE 절에 사용된 2개 이상의 조건이 각각의 인덱스를 사용하되 OR 연산자로 연결된 경우에 사용되는 최적화로, 두 인덱스의 검색 결과의 합집합을 반환한다.

mysql> SELECT *
		FROM employees
        WHERE first_name='Matt' OR hire_date='1987-03-31';

위 쿼리는 2개의 조건이 OR로 연결돼 있으며, 각 조건의 칼럼에 해당하는 인덱스(ix_firstname, ix_hiredate)가 존재하여 각각의 인덱스를 사용할 수 있다. 이 쿼리의 실행 계획은 다음과 같이 "Using union" 최적화를 사용한다.

예제의 쿼리에서 두 개의 조건에 모두 해당하는 경우가 존재할 수 있는데, 해당 쿼리의 결과는 두 번 출력되지 않는다.

이는 MySQL 서버는 두 조건의 검색 결과가 프라이머리 키(emp_no)로 정렬되어 있음을 알고 있어, 두 집합에서 하나씩 가져와 서로 비교하며 프라이머리 키인 emp_no 칼럼의 값이 중복된 레코드들을 정렬 없이 걸러낼 수 있다.

이렇게 정렬된 두 집합의 결과를 하나씩 가져와 중복 제거를 수행할 때 사용된 알고리즘은 우선순위 큐라고 한다.

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

인덱스 머지 작업을 하는 도중에 결과의 정렬이 필요한 경우 인덱스 머지 최적화의 'Sort Unoion' 알고리즘을 사용한다.

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

위 예제 쿼리를 다음과 같이 2개의 쿼리로 분리해서 생각해보자.

mysql> SELECT * FROM employees WHERE first_name='Matt';

mysql> SELECT * FROM employees
		WHERE hire_date BETWEEN '1987-03-01' AND '1987-03-31';

첫 번째 쿼리의 경우 emp_no로 정렬되어 출력되지만, 두 번째 쿼리의 결과는 정렬돼있지 않아, 중복을 제거하기 위해 우선순위 큐를 사용하는 것이 불가능하다.

따라서 MySQL 서버는 중복을 제거하기 위해 각 집합을 emp_no 칼럼으로 정렬한 다음 중복 제거를 수행한다. 실행 계획은 다음과 같다.

세미 조인 (semijoin)

세미 조인(Semi-Join)이란 다른 테이블과 실제 조인을 수행하지는 않고, 다른 테이블에 조건에 일치하는 레코드의 유무만을 체크하는 형태의 쿼리이다.

MySQL 서버 8.0 버전부터는 세미 조인 쿼리의 성능을 개선하기 위한 다음과 같은 세미 조인 최적화 전략이 있다.

Table Pull-out
Duplicate Weed-out
First Match
Loose scan
Materialization

optimizer_switch 시스템 변수의 semijoin 옵션은 firstmatch, loosescan, materialization 옵션을 한 번에 설정할 때 사용한다.

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

Table pullout 최적화는 세미 조인의 서브쿼리에 사용된 테이블을 아우터 쿼리로 끄집어낸 후에 쿼리를 조인 쿼리로 재작성하는 형태의 최적화다.

다음 예제 쿼리는 IN (subquery) 형태의 세미 조인이 사용된 쿼리이다.

mysql> EXPLAIN
		SELECT * FROM employees e
        WHERE e.emp_no IN 
        	(SELECT de.emp_no FROM dept_emp de WHERE de.dept_no='d009');

MySQL 8.0 버전에서 실행 계획은 다음과 같다. 여기서 dept_emp 테이블과 employees 테이블이 순서대로 표시돼 있으며, id 칼럼의 값이 모두 1이다. 이는 두 테이블이 서브쿼리 형태가 아닌 조인으로 처리됐음을 의미한다.

Table pullout 최적화의 몇 가지 제한 사항과 특성은 아래와 같으니 유의하도록 하자.

세미 조인 서브쿼리에서만 사용 가능하다.
서브쿼리 부분이 UNIQUE 인덱스나 프라이머리 키 룩업으로 결과가 1건인 경우에만 사용 가능하다.
Table pullout이 적용된다고 하더라도 기존 쿼리에서 가능했던 최적화 방법이 사용 불가능한 것은 아니므로 MySQL에서는 가능하다면 Table pullout 최적화를 최대한 이용한다.
서브쿼리의 모든 테이블이 아우터 쿼리로 끄집어 낼 수 있다면 서브쿼리 자체는 없어진다.
MySQL에서 "최대한 서브쿼리를 조인으로 풀어서 사용해라"라는 튜닝 가이드를 그대로 실행하는 것이다.

퍼스트 매치 (firstmatch)

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

다음은 예제 쿼리 및 실행 계획이다.

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 테이블이 서브쿼리 패턴이 아닌 조인으로 처리됐음을 알 수 있다. Extar 칼럼의 "FirstMatch(e)" 문구는 employees 테이블의 레코드에 대해 titles 테이블에 일치하는 레코드 1건만 찾으면 더이상 titles 테이블 검색을 하지 않는다는 것을 의미한다.

실제 의미론적으로는 EXISTS (subquery)와 동일하게 처리된 것이지만, 서브쿼리가 아닌 조인으로 출어서 일치하는 첫번째 레코드만 검색하는 최적화를 실행한 것이다. 다음은 FirstMatch 최적화를 사용해 쿼리가 처리된 방식을 이미지로 표현한 것이다.

First Match 최적화는 MySQL 5.5의 최적화 방법인 IN-to_EXISTS 변환과 거의 비슷한 처리 로직을 수행하나, 다음과 같은 추가적인 장점이 있다.

가끔은 여러 테이블이 조인되는 경우 원래 쿼리에는 없던 동등 조건을 옵티마이저가 자동으로 추가하는 형태의 최적화가 실행되기도 한다. 기존의 IN-to-EXISTS 최적화에서는 이러한 동등 조건 전파가 서브쿼리 내에서만 가능했지만 FirstMatch에서는 조인 형태로 처리됨으로 아우터 쿼리의 테이블까지 전파될 수 있다. 최종적으로 더 많은 조건이 주어지는 것이므로 더 나은 실행 계획을 수립할 수 있다.
IN-to-EXISTS 변환 최적화 전략에서는 아무런 조건 없이 변환이 가능한 경우 무조건 그 최적화를 수행했으나, FirstMatch 최적화는 서브쿼리의 모든 테이블에 대해 최적화를 수행할지 일부 테이블만 수행할지 선택할 수 있다.

다음은 FirtsMatch 최적화의 몇 가지 제한 사항과 특성이다.

서브쿼리에서 하나의 레코드만 검색되면 더이상 검색을 멈추는 단축 실행 경로이기 때문에 서브쿼리는 그 서브쿼리가 참조하는 모든 아우터 테이블이 먼저 조회딘 이후에 실행된다.
실행 계획의 Extra 칼럼에는 "FirstMatch(table-N)" 문구가 표시된다.
상관 서브쿼리에서도 사용될 수 있다.
GROUP BY나 집합 함수가 사용된 서브쿼리 최적화에는 사용될 수 없다.