옵티마이저와 힌트 (1/2)

옵티마이저와 힌트

어떤 DBMS든지 쿼리의 실행 계획을 수립하는 옵티마이저는 가장 복잡한 부분으로 알려져 있으며, 옵티마이저가 만들어 내는 실행 계획을 이해하는 것 또한 상당히 어려운 부분이다. 하지만 실행 계획을 이해할 수 있어야만 실행 계획의 불합리한 부분을 찾아내고, 더 최적화된 방법으로 실행 계획을 수립하도록 유도할 수 있다. 실행 계획을 살펴보기 전에 먼저 알고 있어야 할 몇 가지 사항을 살펴보자.

쿼리 실행 절차

MySQL 서버에서 쿼리가 실행되는 과정은 크게 세 단계로 나눌 수 있다.

사용자로부터 요청된 SQL 문장을 자게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리(파스 트리)한다.
SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
두 번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.

두 번째 단계는 첫 번째 단계에서 만들어진 SQL 파스 트리를 참조하면서 다음과 같은 내용을 처리한다.

불필요한 조건 제거 및 복잡한 연산의 단순화
여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정
각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스를 결정
가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정

이 밖에도 수 많은 처리를 하지만 대표적으로 이러한 작업을 들 수 있다. 두 번째 단계는 "최적화 및 실행 계획 수립" 단계이며, MySQL 서버의 옵티마이저에서 처리한다. 또한 두 번째 단계가 완료되면 쿼리의 "실행 계획'이 만들어진다. 세 번째 단계는 수립된 실행 계획대로 스토리지 엔진에 레코드를 읽어오도록 요청하고, MySQL 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행한다.

옵티마이저의 종류

옵티마이저는 데이터베이스 서버에서 두뇌와 같은 역할을 담당한다. 옵티마이저는 현재 대부분의 DBMS가 선택하고 있는 비용 기반 최적화(Cost-based optimizer, CBO) 방법과 예전 초기 버전의 오라클 DBMS에서 많이 사용했던 규칙 기반 최적화 방법(Rule-based optimizer, PBO)으로 크게 나눌 수 있다.

규칙 기반 최적화는 기본적으로 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 실행 계획을 수립하는 방식을 말한다. 사용자의 데이터는 분포도가 매우 다양하기 때문에 규칙 기반의 최적화는 이미 오래전부터 많은 DBMS에서 거의 사용되지 않는다.
비용 기반 최적화는 쿼리를 처리하기 위한 여러 가지 가능한 방법을 만들고, 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출한다. 이렇게 산출된 실행 방법별로 비용이 최소로 소요되는 처리 방식을 선택해 최종적으로 쿼리를 실행한다.

기본 데이터 처리

풀 테이블 스캔과 풀 인덱스 스캔

풀 테이블 스캔은 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 요청된 작업을 처리하는 작업을 의미한다. MySQL 옵티마이저는 다음과 같은 조건이 일치할 때 주로 풀 테이블 스캔을 사용한다.

테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우(일반적으로 테이블이 페이지 1개로 구성된 경우)
WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
인덱스 레인지 스캔을 사용할 수 있는 쿼리라고 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우(인덱스의 B-Tree를 샘플링해서 조사한 통계 정보 기준)

일반적으로 테이블의 전체 크기는 인덱스보다 훨씬 크기 때문에 테이블을 처음부터 끝까지 읽는 작업은 상당히 많은 디스크 읽기가 필요하기 때문에 여러 개의 블록이나 페이지를 읽어오는 기능을 내장하고 있다. MySQL 서버에서는 innodb_read_ahead_threhold 시스템 변수를 이용해 포그라운드 스레드에 의해 설정된 개수만큼의 연속된 데이터 페이지가 읽히면 InnoDB 스토리지 엔진은 백그라운드 스레드를 이용해 대량으로 그다음 페이지들을 읽어서 버퍼 풀로 적재한다. 그러면 포그라운드 스레드는 미리 버퍼 풀에 준비된 데이터를 가져다 사용하기만 하면 되므로 쿼리가 상당히 빨리 처리되는 것이다. 이러한 리드 어헤드는 풀 테이블 스캔에서만 사용되는 것이 아니라 풀 인덱스 스캔에서도 동일하게 사용된다.

mysql> SELECT COUNT(*) FROM employees;

mysql> SELECT * FROM employees;

위 첫 번째 쿼리는 아무런 조건없이 employees 테이블의 레코드 건수를 조회하고 있으므로 당연히 풀 테이블 스캔을 할 것처럼 보이지만 일반적으로 인덱스는 테이블의 2~3개 컬럼만으로 구성되기 때문에 테이블 자체보다는 용량이 작아서 훨씬 빠른 처리가 가능한 풀 인덱스 스캔 사용한다.

하지만 두 번째 쿼리는 레코드에만 있는 컬럼이 필요하기 때문에 풀 인덱스 스캔을 활용하지 못하고 풀 테이블 스캔을 한다.

병렬 처리

mysql> SET SESSION innodb_parallel_read_thread=1;
mysql> SELECT COUNT(*) FROM salaries; -- 0.32sec

mysql> SET SESSION innodb_parallel_read_thread=2;
mysql> SELECT COUNT(*) FROM salaries; -- 0.20sec

mysql> SET SESSION innodb_parallel_read_thread=4;
mysql> SELECT COUNT(*) FROM salaries; -- 0.18sec

mysql> SET SESSION innodb_parallel_read_thread=8;
mysql> SELECT COUNT(*) FROM salaries; -- 0.13sec

MySQL 8.0 버전부터는 용도가 한정되어 있긴 하지만 처음으로 MySQL 서버에서도 innodb_parallel_read_threads라는 시스템 변수를 이용해 하나의 쿼리를 최대 몇 개의 스레드를 이용해 처리할 지 변경하는 병렬 처리가 가능해졌다. 아직 쿼리를 여러 개의 스레드를 이용해 병렬로 처리하게 하는 힌트나 옵션은 없고, 아무런 WHERE 조건 없이 단순히 테이블의 전체 건수를 가져오는 쿼리만 병렬로 처리할 수 있다. 하지만 병렬 처리용 스레드 개수를 아무리 늘릴더라도 서버에 장착된 CPU의 코어 개수를 넘어서는 경우에는 오히려 성능이 떨어질 수 있다.

ORDER BY 처리(Using filesort)

레코드 1~2건을 가져오는 쿼리를 제외하면 대부분의 SELECT 쿼리에서 정렬은 필수적으로 사용된다. 정렬을 처리하는 방법은 인덱스를 이용하는 방법과 쿼리가 실행될 때 "Filesort"라는 별도의 처리를 이용하는 방법으로 나눌 수 있다. ORDER BY/GROUP BY 처리에 인덱스를 사용하지 못하는 경우, MySQL은 Filesort 알고리즘을 통해 데이터를 정렬한다.

	장점	단점
인덱스 이용	INSERT, UPDATE, DELETE 쿼리가 실행될 때 이미 인덱스가 정렬돼 있어서 순서대로 읽기만 하면 되므로 매우 빠르다.	INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가/삭제 작업이 필요하므로 느리다. 인덱스 때문에 디스크 공간이 더 많이 필요하다. 인덱스의 개수가 늘어날수록 InnoDB의 버퍼 풀을 위한 메모리가 많이 필요하다.
Filesort 이용	인덱스를 생성하지 않아도 되므로 인덱스를 이용할 때의 단점이 장점으로 바뀐다. 정렬해야 할 레코드가 많지 않으면 메모리에서 Filesort가 처리되므로 충분히 빠르다.	정렬 작업이 쿼리 실행 시 처리되므로 레코드 대상 건수가 많아질수록 쿼리의 응답 속도가 느리다.

MySQL 서버에서 인덱스를 이용하지 않고 별도의 정렬 처리를 수행했는지는 실행 계획의 Extra 컬럼에 "Using filesort" 메시지가 표시되는지 여부로 판단할 수 있다.

소트 버퍼

MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용하는데, 이 메모리 공간을 소트 버퍼(Sort buffer)라고 한다. 소트 버퍼는 정렬이 필요한 경우에만 할당되며, 버퍼의 크기는 정렬해야 할 레코드의 크기에 따라 가변적으로 증가하지만 최대 사용 가능한 소트 버퍼의 공간은 sort_buffer_size라는 시스템 변수로 설정할 수 있다.

메모리의 소트 버퍼에서 정렬을 수행하고, 그 결과를 임시로 디스크에 기록해 둔다. 그리고 다음 레코드르 가져와서 다시 정렬해서 반복적으로 디스크에 임시 저장한다. 이처럼 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행해야 한다. 이 병합 작업을 멀티 머지(Multi-merge)라고 표현하며, 이 작업들이 모두 디스크의 쓰기와 읽기를 유발하고 레코드 건수가 많을수록 이 반복 작업의 횟수가 많아지는 문제가 발생한다.

그렇다고 소트 버퍼를 크게 설정하면 innodb_buffer_size 시스템 변수는 세션 변수로 커넥션이 많으면 많을수록, 정렬 작업이 많으면 많을수록 소트 버퍼로 소비되는 메모리 공간이 커지기 때문에 운영체제 단에서 메모리 부족 현상(OOM)이 발생할 수 있어 주의해야 한다. 일반적으로 트랜잭션 처리용 MySQL 서버의 소트 버퍼 크기는 56KB에서 1MB 미만이 적절하다.

정렬 알고리즘

레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담을지 또는 정렬 기준 컬럼만 소트 버퍼에 담을지에 따라 싱글 패스(Single-pass)와 투 패스(Two-pass) 2가지 정렬 모드로 나눌 수 있다. 더 정확히는 MySQL 서버의 정렬 방식은 다음과 같이 3가지가 있다.

<sort_key, rowid>: 정렬 키와 레코드의 로우 아이디(Row ID)만 가져와서 정렬하는 방식
<sort_key, additional_fields>: 정렬 키와 레코드 전체를 가져와서 정렬하는 방식으로, 레코드의 컬럼들은 고정 사이즈로 메모리 저장
<sort_key, packed_additional_fields>: 정렬 키와 레코드 전체를 가져와서 정렬하는 방식으로, 레코드의 컬럼들은 가변 사이즈로 메모리 저장

여기서는 첫 번째 방식이 투 패스 정렬 방식에 해당하고, 두 번째, 세 번째 방식이 싱글 패스 정렬 방식에 해당한다고 볼 수 있다.

싱글 패스 정렬 방식

mysql> SELECT emp_no, first_name, last_name FROM employees ORDER BY first_name;

소트 버퍼에 정렬 기준 컬럼을 포함해 SELECT 대상이 되는 컬럼 전부를 담아서 정렬을 수행하는 정렬 방식이다. 위 쿼리처럼 first_name으로 정렬해서 emp_no, first_name, last_name을 SELECT하는 쿼리를 싱글 패스(Single-pass) 정렬 방식으로 처리하는 절차는 위와 같다.

그림에서 알 수 있듯이, 처음 employees 테이블을 읽을 때 정렬에 필요하지 않은 last_name 컬럼까지 전부 읽어서 소트 버퍼에 담고 정렬을 수행한다. 그리고 정렬이 완료되면 정렬 버퍼의 내용 그대로 클라이언트로 넘겨주는 과정을 볼 수 있다.

투 패스 정렬 방식

정렬 대상 컬럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행하고, 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT할 컬럼을 가져오는 정렬 방식으로, 싱글 패스 정렬 방식 되기 이전부터 사용하던 방식이다. 위 그림처럼 처음 정렬에 필요한 first_name 컬럼과 프라이머리 키인 emp_no만 읽어서 정렬하고 순서대로 employees 테이블을 한 번 더 읽어서 last_name을 가져오고 그 결과를 클라이언트 쪽으로 넘기는 과정을 확인할 수 있다.

투 패스 방식은 테이블을 두 번 읽어야 하기 때문에 상당히 불합리하지만, 싱글 패스는 이러한 불합리가 없는 대신 더 많은 소트 버퍼 공간이 필요하다. 대략 128KB의 정렬 버퍼를 사용한다면 이 쿼리는 투 패스 정렬 방식에서는 대략 7,000건의 레코드를 정렬할 수 있지만 싱글 패스 정렬 방식에서는 그것의 반 정도밖에 정렬할 수 없다.

최신 버전에서는 일반적으로 싱글 패스 정렬 방식을 주로 사용하지만 다음의 경우, 싱글 패스 정렬 방식을 사용하지 못하고 투 패스 정렬 방식을 사용한다.

레코드의 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 클 때
BLOB이나 TEXT 타입의 컬럼이 SELECT 대상에 포함될 때

싱글 패스 방식은 정렬 대상 레코드의 크기나 건수가 작은 경우 빠른 성능을 보이며, 투 패스 방식은 정렬 대상 레코드의 크기나 건수가 상당히 많은 경우 효율적이라고 볼 수 있다.

또한, SELECT 쿼리에서 꼭 필요한 컬럼만 조회하지 않고, 모든 컬럼(*)을 가져오도록 작성하면 정렬 버퍼를 몇 배에서 몇십 배까지 비효율적으로 사용할 가능성이 크다. 또한 임시 테이블이 필요한 쿼리에서도 영향을 미치기 때문에 필요한 컬럼만 조회하는 것이 좋다.

정렬 처리 방법

쿼리에 ORDER BY가 사용되면 반드시 다음 3가지 처리 방법 중 하나로 정렬이 처리된다. 일반적으로 아래쪽으로 갈수록 처리 속도는 떨어진다.

정렬 처리 방법	실행 계획의 Extra 컬럼 내용
인덱스를 사용한 정렬	별도 표기 없음
조인에서 드라이빙 테이블만 정렬	"Using filesort" 메시지가 표시됨
조인에서 조인 결과를 임시 테이블로 저장 후 정렬	"Using temporary; Using filesort" 메시지가 표시됨

옵티마이저는 인덱스를 이용할 수 있다면 별도의 Filesort 과정 없이 인덱스를 순서대로 읽어서 결과를 반환한다. 인덱스를 사용할 수 없다면 WHERE 조건에 일치하는 레코드를 검색해 정렬 버퍼에 저장하면서 정렬을 처리(Filesort)할 것이다. 옵티마이저는 정렬 대상 레코드를 최소화하기 위해 다음 2가지 방법 중 하나를 선택한다.

조인의 드라이빙 테이블만 정렬한 다음 조인을 수행
조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬을 수행

조인이 수행되면 보통 레코드 건수와 크기가 배로 늘어나기 때문에 첫 번째 방법을 수행하는 것이 효율적이다. 또한 인덱스를 사용한 정렬 방식만 스트리밍 형태의 처리이며, 나머지는 모두 버퍼링된 후에 정렬된다. 즉, 인덱스를 사용한 정렬 방식은 LIMIT으로 제한된 건수만큼만 읽으면서 바로바로 클라이언트로 결과를 전송할 수 있지만 인덱스를 사용하지 못하는 방식은 필요한 모든 레코드를 디스크로부터 읽어서 정렬한 후 비로소 LIMIT으로 제한된 건수만큼 잘라서 클라이언트로 전송할 수 있다.

인덱스를 이용한 정렬

mysql> SELECT *
FROM employees e, salaries s
WHERE s.emp_no = e.emp_no
AND e.emp_no BETWEEN 10002 AND 100020
ORDER BY e.emp_no; -- // emp_no 컬럼으로 정릴이 필요한데, 인덱스를 사용하면서 자동 정렬된다고
-- // 일부러 ORDER BY emp_no를 제거하는 것은 좋지 않은 선택이다.

인덱스를 이용한 정렬을 위해서는 반드시 ORDER BY에 명시된 컬럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 한다. 또한 WHERE 절에 첫 번째로 읽는 테이블의 컬럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 한다.

인덱스를 이용해 정렬이 처리되는 경우에는 실제 인덱스의 값이 정렬돼 있기 때문에 인덱스의 순서대로 읽기만 하면 된다. 이는 B-Tree 인덱스가 키 값으로 정렬되어 있고, 조인이 네스티드-루프 방식으로 실행되기 때뭉네 드라이빙 테이블의 인덱스 읽기 순서가 흐트러지지 않는다. 따라서 MySQL 엔진에서는 별도의 정렬을 위한 추가 작업을 수행하지 않는다.

조인의 드라이빙 테이블만 정렬

mysql> SELECT *
FROM employees e, salaries s
WHERE s.emp_no = e.emp_no
AND e.emp_no BETWEEN 10002 AND 100010
ORDER BY e.last_name;

일반적으로 조인이 수행되면 결과 레코드의 건수가 몇 배로 불어나고, 레코드 하나하나의 크기도 늘어난다. 그래서 조인을 실행하기 전에 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행하는 것이 정렬의 차선책이 될 것이다. 이 방법으로 정렬이 처리되려면 위 쿼리처럼 조인에서 첫 번째로 읽히는 테이블(드라이빙 테이블)의 컬럼만으로 ORDER BY 절을 작성해야 한다. 그러면 옵티마이저는 드라이빙 테이블(employees)만 검색해서 정렬을 먼저 수행하고, 그 결과와 salaries 테이블을 조인한다.

인덱스를 이용해 "emp_no BETWEEN 100001 AND 100010" 조건을 만족하는 9건을 검색
검색 결과를 last_name 컬럼으로 정렬을 수행(Filesort)
정렬된 결과를 순서대로 읽으면서 salaries 테이블과 조인을 수행해 86건의 최종 결과를 가져옴

임시 테이블을 이용한 정렬

mysql> SELECT *
FROM employees e, salaries s
WHERE s.emp_no = e.emp_no
AND e.emp_no BETWEEN 10002 AND 100010
ORDER BY s.salary;

2개 이상의 테이블을 조인해서 그 결과를 정렬해야 한다면 임시 테이블이 필요할 수 있다. 위 '조인의 드라이빙 테이블만 정렬"은 2개 이상의 테이블이 조인되면서 정렬이 실행되지만 임시 테이블을 사용하지 않는다. 하지만 그 외 패턴의 쿼리에서는 항상 조인의 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬하는 과정을 거친다. 위 쿼리는 보면 정렬이 수행되기 전 드리븐 테이블(salaries 테이블)을 읽어야 하므로 이 쿼리는 조인된 데이터를 가지고 정렬할 수밖에 없다.

만약 쿼리의 실행 계획을 보면 Extra 컬럼에 "Using temporary; Using filesort"라는 코멘트가 표시될 것이다. 이는 조인결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬 처리했음을 의미한다.

정렬 처리 방법의 성능 비교

쿼리에서 인덱스를 사용하지 못하는 정렬이나 그루핑 작업이 왜 느리게 작동할 수 밖에 없는지 한번 살펴보자. 이를 위해 쿼리가 처리되는 방법을 "스트리밍 처리"와 "버퍼링 처리"라는 2가지 방식으로 구분해 보자.

스트리밍 방식

서버 쪽에서 처리할 데이터가 얼마인지에 관계없이 조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트로 전송해주는 방식을 의미한다. 이 방식으로 쿼리를 처리할 경우 클라이언트는 쿼리를 요청하고 곧바로 원했던첫 번째 레코드를 전달받는다. 따라서 풀 테이블 스캔의 결과가 아무런 버퍼링 처리나 필터링 과정 없이 바로 클라이언트로 스트리밍되기 때문에 바른 응답 시간을 보장해준다.

버퍼링 방식

ORDER BY나 GROUP BY 같은 처리는 쿼리의 결과가 스트리밍되는 것을 불가능하게 한다. 우선 WHERE 조건에 일치하는 모든 레코드를 가져온 후, 정렬하거나 그루핑해서 차례대로 보내야 하기 때문이다. 따라서 클라이언트는 그 동안 아무것도 하지 않고 기다려야 하기 때문에 응답 속도가 느려진다. 그렇기 때문에 이 방식을 스트리밍의 반대 표현으로 버퍼링(Buffering)이라고 표현한 것이다.

스트리밍 처리는 어떤 클라이언트 도구나 API를 사용하느냐에 따라 그 방식에 차이가 있을 수 있다. 대표적으로 JDBC 라이브러리를 이용히 "SELECT * FROM bigtable"같은 쿼리를 실행하면 MySQL 서버는 레코드를 읽자마자 클라이언트로 그 결과를 전달하지만 마지막 레코드가 전달될 때까지 내부 버퍼에 모두 담아뒀다 클라이언트의 애플리케이션에 반환한다. 이는 대화형으로 주고받는 것이 아니라 MySQL 서버는 데이터의 크기에 관계없이 무조건 보내고, JDBC MySQL 서버로부터 전송되는 데이터를 받아서 저장만 하므로 불필요한 네트워크 요청이 최소화되기 때문에 전체 처리량이 뛰어나다.

하지만 JDBC의 버퍼링 처리 방식은 기본 작동 방식이며, 아주 대량의 데이터를 가져와야 할 때는 MySQL 서버와 JDBC 간의 전송 방식을 스트리밍 방식으로 변경할 수 있다.

GROUP BY 처리

GROUP BY 또한 ORDER BY와 같이 쿼리가 스트리밍 처리가 안되는 처리 중 하나이다. GROUP BY 절이 있는 쿼리에서는 HAVING 절을 사용할 수 있는데, HAVING 절은 GROUP BY 결과에 대해 필터링 역할을 수행한다. GROUP BY 작업도 인덱스를 이용할 때는 인덱스를 차례대로 읽는 인덱스 스캔 방법과 인덱스를 건너뛰면서 읽는 루스 인덱스 스캔이라는 방법으로 나눌 수 있다. 그리고 인덱스를 사용하지 못하는 쿼리에서 GROUP BY 작업은 임시 테이블을 사용한다.

인덱스 스캔을 이용하는 GROUP BY(타이트 인덱스 스캔)

ORDER BY의 경우와 마찬가지로 조인의 드라이빙 테이블에 속한 컬럼만 이용해 그루핑할 때 GROUP BY 컬럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고 그 결과로 조인을 처리한다.

루스 인덱스 스캔을 이용하는 GROUP BY

mysql> EXPLAIN
SELECT emp_no
FROM salaries
WHERE from_date='1985-03-01'
GROUP BY emp_no;

id table type key Extra
1 salaries range PRIMIARY Using where; Using index for group-by

루스(Loose) 인덱스 스캔 방식은 인덱스의 레코드를 건너뛰면서 필요한 부분만 읽어서 가져오는 것을 의미하는데, 옵티마이저가 루스 인덱스 스캔을 사용할 때는 실행 계획의 Extra 컬럼에 "Using idnex for group-by" 코멘트가 표시된다.

salaries 테이블의 인덱스는 (emp_no, from_date)로 생성되어 있으므로 위의 쿼리 문장에서 WHERE 조건은 인덱스 레인지 스캔 접근 방식으로 이용할 수 없는 쿼리이다. 하지만 실행 계획을 보면 인덱스 레인지 스캔(range 타입)을 이용했으며, GROUP BY 처리까지 인덱스를 사용한 것을 확인할 수 있다.

(emp_no, from_date) 인덱스를 차례대로 스캔하면서 emp_no의 첫 번째 유일한 값(그룹 키) "10001"을 찾아낸다.
(emp_no, from_date) 인덱스에서 emp_no가 '10001'인 것 중에서 from_date 값이 '1985-03-01'인 레코드만 가져온다. 이 검색 방법은 1번 단계에서 알아낸 '10001' 값과 쿼리의 WHERE 절에 사용된 "from_date='1985-03-01'" 조건을 합쳐서 "emp_no=10001 AND from_date='1985-03-01'" 조건으로 (emp_no, from_date) 인덱스를 검색하는 것과 거의 흡사하다.
(emp_no, from_date) 인덱스에서 emp_no의 그다음 유니크한(그룹 키) 값을 가져온다.
3번 단계에서 결과가 더 없으면 처리를 종료하고, 결과가 있다면 2번 과정으로 돌아가서 반복 수행한다.

임시 테이블을 사용하는 GROUP BY

mysql> EXPLAIN
SELECT e.last_name, AVG(s.salary)
FROM employees e, salaries s
WHERE s.emp_no = e.emp_no
GROUP BY e.last_name;

id table type key rows Extra
1 e ALL NULL 300584 Using temporary
1 s ref PRIMIARY 10 NULL

GROUP BY의 기준 컬럼이 드라이빙 테이블에 있든 관계없이 인덱스를 전혀 사용하지 못할 때는 이 방식으로 처리된다. 여기서 주의 깊게 살펴봐야 할 부분은 실행 계획의 Extra 컬럼에 "Using filesort"는 표시되지 않고 "Using temporary"만 표시됐다는 것이다.

CREATE TEMPORARY TABLE ... (
last_name VARCHAR(16),
salary INT,
UNIQUE INDEX ux_lastname (last_name)
);

MySQL 8.0에서는 GROUP BY가 필요한 경우 내부적으로 GROUP BY 절의 컬럼들로 구성된 유니크 인덱스를 가진 임시 테이블을 만들어서 중복 제거와 집합 함수 연산을 수행한다. 그리고 조인 결과를 한 건씩 가져와 임시 테이블에서 중복 체크를 하면서 INSERT 또는 UPDATE를 실행한다. 즉, 별도의 정렬 작업 없이 GROUP BY가 처리된다.

mysql> EXPLAIN
SELECT e.last_name, AVG(s.salary)
FROM employees e, salaries s
WHERE s.emp_no = e.emp_no
GROUP BY e.last_name
ORDER BY e.last_name;

id table type key rows Extra
1 e ALL NULL 300584 Using temporary; Using filesort
1 s ref PRIMIARY 10 NULL

하지만 GROUP BY와 ORDER BY를 같이 사용하면 명시적으로 정렬 작업을 실행한다. 쿼리에 ORDER BY가 추가됨으로써 Using temporary와 함께 Using filesort가 표시된 것을 확인할 수 있다.

DISTINCT 처리

특정 컬럼의 유니크한 값만 조회하려면 SELECT 쿼리에 DISTINCT를 사용한다.

SELECT DISTINCT ...

mysql> SELECT DISTINCT emp_no FROM salaries;
mysql> SELECT emp_no FROM salaries GROUP BY emp_no;

단순히 SELECT되는 레코드 중에서 유니크한 레코드만 가져올 때 사용하는데 이 경우에는 GROUP BY와 동일한 방식으로 처리된다. 즉, ORDER BY 절이 없으면 정렬을 사용하지 않기 때문에 위 두 쿼리는 내부적으로 같은 작업을 수행한다.

mysql> SELECT DISTINCT(first_name), last_name FROM employees;
mysql> SELECT DISTINCT first_name, last_name FROM employees;

DISTINCT는 SELECT하는 레코드(튜플)를 유니크하게 SELECT하는 것이지, 특정 컬럼만 유니크하게 조회하는 것이 아니라. 위 쿼리처럼 함수처럼 사용하더라도 MySQL 서버는 아무 의미 없는 괄호로 해석하고 제거해 버린다. SELECT 절에 사용된 DISTINCT 키워드는 일부가 아닌 조회되는 모든 컬럼에 영향을 미치기 때문에 first_name만 유니크한 것을 가져오는 것이 아니라 (first_name, last_name) 조합 전체가 유니크한 레코드를 가져온다.

집합 함수와 함계 사용된 DISTINCT

mysql> EXPLAIN
SELECT COUNT(DISTINCT s.salary)
FROM employees e, salaries s
WHERE e.emp_no = s.emp_no
AND e.emp_no BETWEEN 100001 AND 100100;

id table type key rows Extra
1 e range PRIMARY 100 Using where; Using index
1 s ref PRIMIARY 10 NULL

집합 함수 내에서 사용된 DISTINCT는 그 집합 함수의 인자로 전달된 컬럼값이 유니크한 것들을 가져온다. 위 쿼리는 내부적으로 COUNT(DISTINCT s.salary)를 처리하기 위해 임시 테이블을 사용한다. 하지만 실행 계획에는 임시 테이블을 사용한다는 메시지는 표시되지 않는다.

employees 테이블과 salaries 테이블을 조인한 결과에서 salary 컬럼의 값만 저장하기 위한 임시 테이블을 만들어서 사용하고 salary 컬럼에는 유니크 인덱스가 생성되기 때문에 레코드 건수가 많아진다면 상당히 느려질 수 있는 형태의 쿼리이다.

내부 임시 테이블 활용

MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그루핑할 때는 내부적인 임시 테이블(Internal temporary table)을 사용한다. MySQL 엔진이 내부적인 가공을 위해 생성하는 임시 테이블은 다른 세션이나 다른 쿼리에서 볼 수 없으며 사용하는 것도 불가능하다. 사용자가 생성한 임시 테이블(CREATE TEMPORARY TABLE)과는 달리 내부적인 임시 테이블은 쿼리의 처리가 완료되면 자동으로 삭제된다.

메모리 임시 테이블과 디스크 임시 테이블

MySQL 8.0 버전에서는 임시 테이블이 메모리를 사용할 때는 TempTable이라는 스토리지 엔진을 사용하고, 디스크에 저장되는 임시 테이블은 InnoDB 스토리지 엔진을 사용한다. TempTable이 최대로 사용 가능한 메모리 공간의 크기는 temptable_max_ram 시스템 변수로 제어할 수 있는데, 기본값은 1GB로 설정되어 있다. 임시 테이블의 크기가 1GB보다 커지는 경우 MySQL 서버는 메모리의 임시 테이블을 디스크로 기록하게 되는데, 이때 MySQL 서버는 temp_user_mmap 시스템 변수로 설정하여 다음의 2가지 디스크 저장 방식 중 하나를 선택한다.

MMAP 파일로 디스크에 기록
InnoDB 테이블로 기록

임시 테이블이 필요한 쿼리

ORDER BY와 GROUP BY에 명시된 컬럼이 다른 쿼리
ORDER BY와 GROUP BY에 명시된 컬럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
UNION이나 UNION DISTINCT가 사용된 쿼리(select_type 컬럼이 UNION RESULT인 경우)
쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리

위 패턴의 쿼리는 MySQL 엔진에서 별도의 데이터 가공 작업을 필요로 하므로 대표적으로 내부 임시 테이블을 생성하는 케이스이다. 어떤 쿼리의 실행 계획에서 임시 테이블을 사용하는지는 Extra 컬럼에 "Using temporary"라는 메시지가 표시되는지 확인하면 된다. "Using temporary"가 표시되지 않을 때도 임시 테이블을 사용하는데 마지막 3개의 패턴이 그러한 예다. 첫 번째부터 네 번째까지의 쿼리 패턴은 유니크 인덱스를 가지는 내부 임시 테이블을 만들고, 마지막 쿼리 패턴은 유니크 인덱스가 없는 내부 임시 테이블이 생성된다. 일반적으로 유니크 인덱스가 있는 내부 임시 테이블은 그렇지 않은 쿼리보다 처리 성능이 상당히 느리다.

임시 테이블이 디스크에 생성되는 경우

내부 임시 테이블은 기본적으로 메모리상에 만들어지지만 다음 조건을 만족하면 메모리 임시 테이블을 사용할 수 없어 디스크 기반의 임시 테이블을 사용한다.

UNION이나 UNION ALL에서 SELECT되는 컬럼 중에서 길이가 512바이트 이상인 크기의 컬럼이 있는 경우
GROUP BY나 DISTINCT 컬럼에서 512바이트 이상인 크기의 컬럼이 있는 경우
메모리 임시 테이블의 크기가 (MEMORY 스토리지 엔진에서) tmp_table_size 또는 max_heap_table_size 시스템 변수보다 크거나 (TempTable 스토리지 엔진에서) temptable_max_ram 시스템 변수 값보다 큰 경우

임시 테이블 관련 상태 변수

실행 계획상에서 "Using temporary"가 표시됐다고 해서 임시 테이블을 하나만 사용했다는 것을 의미하지 않는다. 임시 테이블이 디스크에 생성됐는지 메모리에 생성됐는지 확인하려면 MySQL 서버의 상태 변수(SHOW SESSION STATUS LIKE 'Created_tmp%';)를 확인해 보면 된다.

mysql> FLUSH STATUS; -- // 현재 세션의 상태값 초기화

mysql> SELECT first_name, last_name
FROM employees
GROUP BY first_name, last_name;

mysql> SHOW SESSION STATUS LIKE 'Created_tmp%';

Variable name Value
Created_tmp_disk_tables 1
Created_tmp_tables 1

Created_tmp_tables: 쿼리의 처리를 위해 만들어진 내부 임시 테이블의 개수를 누적하는 상태값이다. 이 값은 내부 임시 테이블에 메모리에 만들어졌는지 디스크에 만들어졌는지 구분하지 않고 모두 누적한다.
Created_tmp_disk_tables: 디스크에 내부 임시 테이블이 만들어진 개수만 누적해서 가지고 있는 상태값이다.

고급 최적화

MySQL 서버의 옵티마이저가 실행 계획을 수립할 때 통계 정보와 옵티마이저 옵션을 결합해서 최적의 실행 계획을 수립하게 된다. 옵티아미저 옵션은 크게 조인 관련된 옵티마이저 옵션과 옵티마이저 스위치로 구분할 수 있다.

옵티마이저 스위치 옵션

옵티마이저 스위치 이름	기본값	설명
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 최적화를 사용할지 여부 설정
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 최적화를 사용할지 여부 설정

옵티마이저 스위치 옵션은 optimizer_switch 시스템 변수를 이용해서 제어하는데, optimizer_switch 시스템 변수에는 여러 개의 옵션을 세트로 묶어서 설정하는 방식으로 사용된다. 옵티마이저 스위치 옵션은 글로벌과 세션별 모두 설정할 수 있는 시스템 변수이므로 MySQL 서버 전체적으로 또는 현재 커넥션에 대해서만 다음과 같이 설정할 수 있다.

-- // MySQL 서버 전체적으로 옵티마이저 스위치 설정
mysql> SET GLOBAL optimizer_switch='index_merge=on,index_merge_union=on,...';

-- // 현재 커넥션의 옵티마이저 스위치 설정
mysql> SET SESSION optimizer_switch='index_merge=on,index_merge_union=on,...';

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

MRR은 "Multi-Range Read"를 줄여서 부르는 이름인데, 매뉴얼에서는 DS-MRR(Disk-Range Read)이라고도 한다. MySQL 서버에서 지금까지 지원하던 조인 방식은 드라이빙 테이블의 레코드를 한 건 읽어서 드리븐 테이블의 일치하는 레코드를 찾아서 수행하는 네스티드 루프(Nested Loop Join)을 수행했다. MySQL 서버의 내부 구조상 조인 처리는 MySQL 엔진이 실제 레코드를 검색하고 읽는 부분은 스토리지 엔진이 담당하기 때문에 스토리지 엔진에서는 아무런 최적화를 수행할 수 없다.

이 같은 단점을 보완하기 위해 MySQL 서버는 조인 대상 테이블 중 하나로부터 레코드를 읽어서 조인 버퍼에 버퍼링한다. 즉, 드라이빙 테이블의 레코드를 읽어서 드리븐 테이블의 조인을 즉시 실행하지 않고 조인 대상을 버퍼링하는 것이다. 조인 버퍼가 레코드가 가득 차면 비로소 MySQL 엔진은 버퍼링된 레코드를 스토리지 엔진으로 한 번에 요청하여 데이터 페이지에 정렬된 순서로 접근해 디스크의 데이터 페이지 읽기를 최소화할 수 있다. 이러한 읽기 방식을 MRR이라고 한다.

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

MySQL 서버에서 사용되는 대부분의 조인은 네스티드 루프 조인(Nested Loop Join)인데, 조인의 연결 조건이 되는 컬럼에 모두 인덱스가 있는 경우 사용되는 조인 방식이다. 위 쿼리는 employees 테이블에서 first_name 조건에 일치하는 레코드 1건을 찾아서 salaries 테이블의 일치하는 레코드를 찾는 형태의 조인을 실행한다. 이러한 형태의 조인은 마치 중첩된 반복 명령을 사용하는 것처럼 작동한다고 해서 네스티드 루프 조인(Nested Loop Join)이라고 한다.

네스티드 루프 조인과 블록 네스티드 루프 조인(Block Nested Loop Join)의 가장 큰 차이는 조인 버퍼(join_buffer_size 시스템 설정으로 조정되는 조인을 위한 버퍼)가 사용되는지 여부와 조인에서 드라이빙 테이블과 드리븐 테이블이 어떤 순서로 조인되느냐이다. 조인 쿼리의 실행 계획에서 Extra 컬럼에 "Using Join buffer"라는 문구가 표시되면 그 실행 계획은 조인 버퍼를 사용한다는 것을 의미한다.

예를 들어, 드라이빙 테이블에서 일치하는 레코드가 1,000건이고, 드리븐 테이블의 조인 조건이 인덱스를 이용할 수 없다면 드리븐 테이블에서 연결되는 레코드를 찾기 위해 1,000번의 풀 테이블 스캔을 해야 하기 때문에 쿼리가 상당히 느려진다. 따라서 드리븐 테이블의 풀 테이블 스캔이나 인덱스 풀 스캔을 피할 수 없다면 옵티마이저는 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시한 후 드리븐 테이블과 이 메모리 캐시를 조인하는 형태로 처리하는데 메모리의 캐시를 조인 버퍼(Join Buffer)라고 한다.

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

위 쿼리의 실행 계획을 확인해 보면 Extra 컬럼에 "Using where"가 표시된 것을 확인할 수 있다. "Using where"는 InnoDB 스토리지 엔진이 읽어서 반환해준 레코드가 인덱스를 사용할 수 없는 WHERE 조건에 일치하는지 검사하는 과정을 의미한다. 즉, last_name 조건은 ix_lastname_firstname 인덱스를 레인지 스캔으로 사용하여 특정 범위만 조회할 수 있지만, first_name LIKE '%sal' 조건은 데이터를 모두 읽은 후 사용자가 원하는 결과인지 하나씩 비교해보는 조건(체크 조건 또는 필터링 조건이라고 함)으로 사용된다. 만약 last_name='Action' 조건에 일치하는 레코드가 10만 건이나 되는데, 그중에서 단 1건만 first_name LIKE '%sal' 조건에 일치했다면 99,999건의 레코드 읽기가 불필요한 작업이 되어버린다.

하지만 first_name LIKE '%sal' 조건을 처리하기 위해 다시 테이블의 레코드를 읽어서 처리하기 보다는 인덱스의 first_name 컬럼을 이용해서 불필요한 레코드는 테이블에서 읽지 않도록 하는 것이 더 좋다. 즉, 인덱스에 포함된 first_name 컬럼을 이용할지 또는 테이블의 first_name 컬럼을 이용할지가 관건인데 인덱스를 비교하는 작업은 실제 InnoDB 스토리지 엔진에서, 테이블의 레코드에서 first_name 조건을 비교하는 작업은 MySQL 엔진이 수행하는 작업이다.

따라서, 인덱스를 범위 제한 조건으로 사용하지 못한다고 하더라도 인덱스에 포함된 컬럼의 조건이 있다면 모두 같이 모아서 스토리지 엔진으로 전달할 수 있게 핸들러 API가 개선되어 인덱스를 이용해 최대한 필터링까지 완료해서 꼭 필요한 레코드 1건에 대해서만 테이블 읽기를 수행할 수 있게 됐다. 옵티마이저 스위치 옵션을 통해 인덱스 컨디션 푸시다운 기능을 설정할 수 있는데 고도의 기술력을 필요로 하는 기능은 아니지만 쿼리의 성능이 몇 배에서 몇십 배로 향상될 수도 있는 중요한 기능이다.

인덱스 확장(use_index_extensions)

use_index_extensions 옵티마이저 옵션은 InnoDB 스토리지 엔진을 사용하는 테이블에서 세컨더리 인덱스에 자동으로 추가된 프라이머리 키를 활용할 수 있게 할지를 결정하는 옵션이다.

dept_emp 테이블에서 프라이머리 키는 (dept_no, emp_no)이며, 세컨더리 인덱스 ix_fromdate는 from_date 컬럼만 포함한다. 세컨더리 인덱스는 데이터 레코드를 찾아가기 위해 프라이머리 키인 dept_no와 emp_no 컬럼을 순서대로 포함하여 최종적으로 ix_fromdate 인덱스는 (from_date, dept_no, emp_no) 조합으로 인덱스를 생성한 것과 흡사하게 작동하여 이를 인지하고 실행 계획을 수립한다.

위 쿼리의 실행 계획을 보면 key_len 컬럼은 이 쿼리가 인덱스를 구성하는 컬럼 중에서 어느 부분(어느 컬럼)까지 사용했는지를 바이트 수로 보여주는데 19바이트가 표시된 것을 보면 from_date 컬럼(3바이트)과 dept_emp 컬럼(16바이트)까지 사용했다는 것을 알 수 있다.

WHERE 조건에 dept_no 컬럼을 사용하지 않으니 from_date 컬럼을 위한 3바이트만 표시된 것을 확인할 수 있다.

또한 InnoDB의 프라이머리 키가 세컨더리 인덱스에 포함되어 있으므로 다음과 같이 정렬 작업도 인덱스를 활용해서 처리한다는 장점이 있다. Extra 컬럼에 'Using Filesort"가 표시되지 않았다는 것은 MySQL 서버가 별도의 정렬 작업 없이 인덱스 순서대로 레코드를 읽기만 함으로써 "ORDER BY dept_no"를 만족했다는 것을 의미한다.

인덱스 머지(index_merge)

한 테이블에 대한 WHERE 조건이 여러 개 있더라도 하나의 인덱스만 사용해서 작업 범위를 충분히 줄일 수 있는 경우라면 나머지 조건은 체크하는 형태로만 사용되는 것이 일반적이다. 하지만 쿼리에 사용된 각각의 조건이 서로 다른 인덱스를 사용할 수 있고 그 조건을 만족하는 레코드 건수가 많을 것으로 예상될 때 MySQL 서버는 인덱스 머지 실행 계획을 선택해서 2개 이상의 인덱스를 이용해 쿼리를 처리한다.

인덱스 머지 실행 계획은 다음과 같이 3개의 세부 실행 계획으로 나누어 볼 수 있다. 3가지 최적화 모두 여러 개의 인덱스를 통해 결과를 가져온다는 것은 동일하지만 각각의 결과를 어떤 방식으로 병합할지에 따라 구분된다.

index_merge_intersection
index_merge_union
index_merge_union

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

위 쿼리는 2개의 WHERE 조건을 가지고 있는데, employees 테이블의 first_name 컬럼과 emp_no 컬럼 모두 각각의 인덱스(ix_firstname, PRIMARY)를 가지고 있다. 즉, 2개 중에서 어떤 조건을 사용하더라도 인덱스를 사용할 수 있다. 2개의 인덱스 중 하나라도 충분히 효율적으로 쿼리를 처리할 수 있었다면 옵티마이저는 2개 모두를 사용하지 않지만 각각의 조건에 일치하는 레코드 건수를 예측해 본 결과, 두 조건 모두 상대적으로 많은 레코드를 가져와야 한다고 인식하여 ix_firstname과 PRIMARY 키 모두를 사용해서 쿼리를 처리하기로 결정한다. 실행 계획의 Extra 컬럼에 "Using intersect"라고 표시된 것은 이 쿼리가 여러 개의 인덱스를 각각 검색해서 그 결과의 교집합만 반환했다는 것을 의미한다.

실제 두 조건을 만족하는 레코드 건수는 14건밖에 안 된다. 즉, ix_firstname 인덱스만 사용했다면 253번의 데이터 페이지 읽기를 하지만 그 중 겨우 14번만 의미 있는 작업이었고, PRIMARY 키만 사용했다면 10,0000건을 읽어서 9,986건은 버리고 겨우 14건만 반환하는 작업이 됐을 것이다. 두 작업 모두 비효율이 매우 큰 상황이어서 옵티마이저는 각 인덱스를 검색해 두 결과의 교집합만 찾아서 반환한다.

인덱스 머지 실행 계획이 아닌 경우의 처리 방식

"first_name='Georgi'" 조건만 인덱스를 사용했다면 일치하는 레코드 253건을 검색한 다음 데이터 페이지에서 레코드를 찾고 emp_no 컬럼의 조건에 일치하는 레코드들만 반환하는 형태로 처리되어야 한다.
"emp_no BETWEEN 10000 AND 20000" 조건만 인덱스를 사용했다면 프라이머리 키를 이용해 10,000건을 읽어와서 "first_name='Georgi'" 조건에 일치하는 레코드만 반환하는 형태로 처리되어야 한다.

-- // MySQL 서버 전체적으로 index_merge_intersection 최적화 비활성화
mysql> SET GLOBAL/SESSIOn optimizer_switch='index_merge_intersection=off';

하지만 ix_firstname 인덱스는 프라이머리 키인 emp_no 컬럼을 자동으로 포함하고 있기 때문에 그냥 ix_firstname 인덱스만 사용하는 것이 더 성능이 좋을 것으로 생각할 수도 있다.

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

인덱스 머지의 "Using union"은 WHERE 절에 사용된 2개 이상의 조건이 각각의 인덱스를 사용하되 OR 연산자로 연결된 경우에 사용되는 최적화다. 쿼리의 실행 계획에서 Extra 컬럼에 "Using union(ix_firstname, ix_hiredate)"이라고 표시됐는데, 이는 인덱스 머지 최적화가 ix_firstname 인덱스의 검색 결과와 ix_hiredate 인덱스 검색 결과를 'Union' 알고리즘으로 병합 즉, 두 집합의 합집합을 가져왔다는 것을 의미한다.

'Union' 알고리즘의 숨은 비밀을 하나 살펴보자. first_name='Matt'이면서 hire_date='1987-03-31'인 사원이 있었다면 두 인덱스 결과에 포함되는데 해당 사원의 정보는 두 번 출력되지 않을 뿐더러 중복을 제거하기 위해 정렬 작업이 필요했을 것이다. 하지만 실제 실행 계획에는 정렬했다는 표시가 없다. MySQL 서버는 이 같은 중복 제거를 위해 내부적으로 어떤 작업을 수행했을까?

MySQL 서버는 first_name 조건을 검색한 결과와 hire_date 컬럼을 검색한 결과가 프라이머리 키로 이미 각각 정렬되어 있다는 것을 알고 있다. 그래서 MySQL 서버는 우선순위 큐(Priority Queue)를 사용하여 두 집합의 결과를 하나씩 가져와 서로 비교하면서 프라이머리 키인 emp_no 컬럼의 값이 중복된 레코드들을 정렬 없이 걸러낼 수 있는 것이다.

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

위 합집합은 정렬이 필요하지 않는데 만약 인덱스 머지 작업을 하는 도중에 결과의 정렬이 필요한 경우 MySQL 서버는 인덱스 머지 최적화의 'Sort union' 알고리즘을 사용한다. 위 쿼리의 실행 계획을 보면 "Usgin sort_union"인 것을 확인할 수 있는데 이해하기 위해 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로 정렬되어 출력되지만, 두 번째 쿼리는 emp_no 컬럼으로 정렬되어 있지 않다. 따라서 우선순위 큐를 사용하는 것은 불가능하기 때문에 MySQL 서버는 각 집합을 emp_no 컬럼으로 정렬한 다음 중복 제거를 수행한다.

세미 조인(semijoin)

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

MySQL 서버에서 세미 조인 최적화 기능이 없었을 때는 위의 세미 조인 쿼리의 실행 계획은 위와 같다. dept_no 테이블을 조회하는 서브쿼리 부분이 먼저 실행되고 그 다음 employees 테이블에서 일치하는 레코드만 검색할 것으로 기대했지만 MySQL 서버는 employees 테이블을 풀 스캔하면서 한 건 한 건 서브쿼리의 조건에 일치하는지 비교했다. 실행 계획만 보더라도 약 57건만 읽으면 되는 쿼리를 30만 건 넘게 읽어서 처리된다는 것을 알 수 있다.

세미 조인(Semi-join) 형태의 쿼리와 안티 세미 조인(Anti Semi-join) 형태의 쿼리는 최적화 방법이 조금 차이가 있다. "= (subquey)" 형태와 "IN (subquery)" 형태의 세미 조인 쿼리에 대해 다음과 같이 3가지 최적화 방법을 적용할 수 있다.

세미 조인 최적화
IN-to-EXISTS 최적화
MATERIALIZATION 최적화

그리고 "<> (subquery)" 형태와 "NOT IN (subquery)" 형태의 안티 세미 조인 쿼리에 대해서는 다음 2가지의 최적화 방법이 있다.

IN-to-EXISTS 최적화
MATERIALIZATION 최적화

서브쿼리 최적화 중에서 최근 도입된 세미 조인 최적화에 대해서만 살펴보고 세미 조인 쿼리의 성능을 개선하기 위한 다음과 같은 최적화 전략이 있고 아래 최적화 전략들을 모아서 세미 조인 최적화라고 부른다.

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

Table pull-out 최적화 전략은 사용 가능하면 항상 세미 조인보다는 좋은 성능을 내기 때문에 별도로 제어하는 옵티마이저 옵션을 제공하지 않는다. 그리고 First Match와 Loose Scan 최적화 전략은 각각 firstmatch와 loosescan 옵티마이저 옵션으로 사용 여부를 결정할 수 있고, Duplicate Weed-out과 Materialization 최적화 전략은 materialization 옵티마이저 스위치로 사용여부를 선택할 수 있다.

optimizer_switch 시스템 변수의 semijoin 옵티마이저 옵션은 firstmatch와 loosescan, materialization 옵티마이저 옵션을 한 번에 활성화하거나 비활성화할 때 사용한다.

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

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

위 실행 계획을 확인해보면 dept_emp 테이블, employees 테이블 순서대로 표시되어 있고, 가장 중요한 부분은 id 컬럼의 값이 모두 1로 동일한 값을 가지는 것인데 이는 두 테이블이 서브쿼리 형태가 아니라 조인으로 처리됐음을 의미한다. SHOW WARNINGS 명령으로 MySQL 옵티마이저가 재작성(Re-Write)한 쿼리를 살펴보면 dept_demp 테이블이 드라이빙 테이블 employees 테이블이 드리븐 테이블이 되어 재작성된 것을 확인할 수 있다.

Table pullout 최적화는 모든 형태의 서브쿼리에서 사용될 수 있는 것은 아니다. Table pullout 최적화의 몇 가지 제한 사항과 특성을 살펴보자.

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

퍼스트 매치(firstmatch)

First Match 최적화 전략 IN(subquery) 형태의 세미 조인을 EXISTS(subquery) 형태로 튜닝한 것과 비슷한 방법으로 실행된다. 위 쿼리는 이름이 'Matt'인 사원 중에서 1995년 1월 1일부터 30일 사이에 직급이 변경된 적이 있는 사원을 조회하는 용도의 쿼리이다.

쿼리의 실행 계획을 보면 id 컬럼의 값이 모두 1로 표시되었고, Extra 컬럼에는 "FirstMatch(e)"라는 문구가 출력됐다. id 컬럼의 값이 모두 1로 표시된 것은 titles 테이블이 서브쿼리 패턴으로 실행되지 않고, 조인으로 처리됐다는 것을 알 수 있다. FirstMatch(e) 문구는 employees 테이블의 레코드에 대해 titles 테이블에 일치하는 레코드 1건만 찾으면 더이상의 titles 테이블 검색을 하지 않는 방식으로 최적화를 실행한 것이다. 즉, 의미론적으로 EXISTS(subquery)와 동일하게 처리된 것이다.

FirstMatch 최적화는 특정 형태의 서브쿼리에서 자주 사용되는 최적화로 다음과 같은 몇 가지 제한 사항과 특성이 있다.

FirstMatch는 서브쿼리에서 하나의 레코드만 검색되면 더 이상의 검색을 멈추는 단축 실행 경로(Short-cut path)이기 때문에 FirstMatch 최적화에서 서브쿼리는 그 서브쿼리가 참조하는 모든 아우터 테이블이 먼저 조회된 이후에 실행된다.
FirstMatch 최적화가 사용되면 실행 계획의 Extra 컬럼에 FirstMatch(table-N) 문구가 표시된다.
FirstMatch 최적화는 상관 서브쿼리(Correlated subquery)에서도 사용될 수 있다.
FirstMatch 최적화는 GROUP BY나 집합 함수가 사용된 서브쿼리의 최적화에는 사용될 수 없다.
FirstMatch 최적화는 opimizer_switch 시스템 변수에서 semijoin 옵션과 firstmatch 옵션이 모두 ON으로 활성화되어 있는 경우에만 사용할 수 있다.

루스 스캔(loosescan)

세미 조인 서브쿼리 최적화인 LooseScan은 인덱스를 사용하는 GROUP BY 최적화 방법에서 살펴본 "Using index for group-by'"의 루스 인덱스 스캔(Loose Index Scan)과 비슷한 읽기 방식을 사용한다. 위 쿼리에서 departments 테이블의 레코드 건수는 9건밖에 되지 않지만 dept_emp 테이블의 레코드 건수는 무려 33만 건 가까이 저장되어 있다. 따라서 LooseScan을 사용하면 dept_emp 테이블의 프라이머리 키를 루스 인덱스 스캔으로 유니크한 dept_no만 읽어 아주 효율적으로 서브쿼리 부분을 실행할 수 있도록 수행한다.

서브쿼리에서 사용된 dept_emp 테이블이 드라이빙 테이블로 실행되며, dept_emp 테이블의 프라이머리 키를 dept_nop 부분에서 유니크하게 한 건만 읽는데 이는 "Using index for group-by"도 dept_emp 테이블의 프라이머리 키를 읽는 방식과 동일하게 작동한다.

구체화(Materialization)

Materialization 최적화는 세미 조인에 사용된 서브쿼리를 통째로 구체화해서 쿼리를 최적화한다는 의미다. 여기서 구체화는 쉽게 표현하면 내부 임시 테이블을 생성한다는 것을 의미한다. 위 쿼리를 FirstMatch 최적화를 사용하려면 employees 테이블에 대한 조건이 서브쿼리 이외에는 아무것도 없기 때문에 employees 테이블을 풀 스캔해야 한다. 따라서 MySQL 서버 옵티마이저는 이런 형태의 쿼리를 위해 서브쿼리 구체화(Subquery Materialization)라는 최적화를 도입했다. 위 쿼리의 서브쿼리가 "서브쿼리 구체화" 최적화를 사용하는 형태로 수립되는 것을 확인할 수 있다.

실행 계획 마지막 라인의 select_type 컬럼에는 간단하게 "MATERIALIZED"라고만 표시되어있고, 사용하는 테이블은 2개인데 실행 계획은 3개 라인이 출력되는 것을 봐서 실행 계획 어디선가 임시 테이블이 생성됐다는 것을 짐작할 수 있다.

Materialization 최적화는 다른 서브쿼리 최적화와는 달리, 다음 쿼리와 같이 서브쿼리 내에 GROUP BY 절이 있어도 해당 최전화 전략을 사용할 수 있다. Materialization 최적화는 optimizer_switch 시스템 변수에서 semijoin 옵션과 materialization 옵션이 모두 ON으로 활성화된 경우에만 사용되고, 다음과 같은 제한 사항과 특성이 있다.

IN (subquery)에서 서브쿼리는 상관 서브쿼리(Correlated subquery)가 아니어야 한다.
서브쿼리는 GROUP BY나 집합 함수들이 사용돼도 구체화를 사용할 수 있다.
구체화가 사용된 경우에는 내부 임시 테이블이 사용된다.

중복 제거(Duplicated Weed-out)

Duplicated Weedout은 세미 조인 서브쿼리를 일반적인 INNER JOIN 쿼리로 바꿔서 실행하고 마지막에 중복된 레코드를 제거하는 방법으로 처리되는 최적화 알고리즘이다. salaries 테이블의 프라이머리 키가 (emp_no + from_date)이므로 중복된 emp_no가 발생할 수 있다. 따라서 GROUP BY 절을 넣어주면 세미 조인 서브쿼리와 동일한 결과를 얻을 수 있다.

실제로 Dupliate Weedout 최적화 알고리즘은 원본 쿼리를 위와 같이 INNER JOIN + GROUP BY 절로 바꿔서 실행하는 것과 동일한 작업으로 쿼리를 실행한다.

salaries 테이블의 ix_salary 인덱스를 스캔해서 salary가 150000보다 큰 사원을 검색해 employees 테이블 조인을 실행
조인된 결과를 임시 테이블에 저장
임시 테이블에 저장된 결과에 emp_no 기준으로 중복 제거
중복을 제거하고 남은 레코드를 최종적으로 반환

Dupliate Weedout 최적화는 다음과 같은 장점과 제약 사항이 있다.

서브쿼리가 상관 서브쿼리라고 하더라도 사용할 수 있는 최적화다.
서브쿼리가 GROUP BY나 집합 함수가 사용된 경우에는 사용될 수 없다.
Duplicate Weedout은 서브쿼리의 테이블을 조인으로 처리하기 때문에 최적화할 수 있는 방법이 많다.

컨디션 팬아웃(condition_fanout_filter)

조인을 실행할 때 테이블의 순서는 쿼리의 성능에 매우 큰 영향을 미친다. 위 쿼리는 다음과 같은 과정을 거친다.

employees 테이블에서 ix_firstname 인덱스를 이용해 first_name = 'Matt' 조건에 일치하는 233건의 레코드를 검색한다.
condition_fanout_filter 최적화가 활성되어 검색된 233건의 레코드 중에 실행 계획에서 filtered 컬럼의 값이 26.03인 것은 옵티마이저가 60건(233 * 0.2603)만 조건을 충족할 것이라고 예측했다.
만약, condition_fanout_filter이 비활성화되면 100이 되어 233건 모두 hire_date 컬럼의 조건을 만족할 것이라 예측한다.
employees 테이블을 읽은 결과 233건에 대해 salaries 테이블의 프라이머리 키를 이용해 salaries 테이블의 레코드를 읽는다. 이때 MySQL 옵티마이저는 employees 테이블의 레코드 한 건당 salaries 테이블의 레코드 9건이 일치할 것으로 예상했다.

MySQL 8.0 버전에서는 condition_fanout_filter 최적화가 활성화되면 다음과 같은 조건을 만족하는 컬럼의 조건들에 대해 조건을 만족하는 레코드의 비율을 계산할 수 있다.

WHERE 조건절에 사용된 컬럼에 대해 인덱스가 있는 경우
WHERE 조건절에 사용된 컬럼에 대해 히스토그램이 존재하는 경우

파생 테이블 머지(derived_merge)

예전 버전의 MySQL 서버에서는 실행 계획을 보는 것처럼 FROM 절에 사용된 서브쿼리는 먼저 실행해서 그 결과를 임시 테이블로 만든 다음 외부 쿼리 부분을 처리했다. 이는 first_name='Matt'인 레코드를 employees 테이블에서 읽어 임시 테이블로 INSERT하고 다시 임시 테이블을 읽는 오버헤드가 발생하고 임시 테이블이 처음에는 메모리에 생성되지만, 저장될 레코드 건수가 많아지면 결국 디스크로 다시 기록되기 때문에 쿼리의 성능은 많이 느려질 수 있다. 위 실행 계획에서 select_type이 DERIVED라고 표시된 것이 FROM 절에 사용된 서브쿼리를 파생 테이블(Derived Table)을 의미한다.

MySQL 6.7 버전부터는 이렇게 파생 테이블로 만들어지는 서브쿼리를 외부 쿼리와 병합해서 서브쿼리 부분을 제거하는 최적화가 도입됐는데, derived_merge 최적화 옵션은 이러한 임시 테이블 최적화를 활성화할지 여부를 결정한다.

위 실행 계획을 보면 DERIVED였던 라인이 없엊고, 서브쿼리 없이 employees 테이블을 조회하던 형태의 단순 실행 계획으로 바뀌었다. 위처럼 SHOW WARNINGS 명령으로 MySQL 옵티마이저가 새로 작성한 쿼리를 살펴보면 서브쿼리 부분이 어떻게 외부 쿼리로 병합됐는지 확인할 수 있다.

MySQL 옵티마이저가 자동으로 서브쿼리를 외부 쿼리로 병합하는 작업을 지원하지만 다음과 같은 조건에서는 외부 쿼리로 병합할 수 없기 때문에 가능하다면 서브 쿼리를 외부 쿼리로 수동으로 병합해서 작성하는 것이 좋다.

SUM() 또는 MIN(), MAX() 같은 집계 함수와 윈도우 함수(Window Function)가 사용된 서브쿼리
DISTINCT가 사용된 서브쿼리
GROUP BY나 HAVING이 사용된 서브쿼리
LIMIT이 사용된 서브쿼리
UNION 또는 UNION ALL을 포함하는 서브쿼리
SELECT 절에 사용된 서브쿼리
값이 변경되는 사용자 변수가 사용된 서브쿼리

인비저블 인덱스(use_invisible_indexes)

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

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

MySQL 버전부터는 인덱스의 가용 상태를 제어할 수 있는 기능이 추가됐다. use_invisible_indexes 옵티마이저 옵션을 이용하면 INVISIBLE로 설정된 인덱스라 하더라도 옵티마이저가 사용하게 제어할 수 있다. 기본값은 off로 on으로 바꿔 설정하면 된다.

스킵 스캔(skip_scan)

인덱스의 핵심은 값이 정렬되어 있다는 것이며, 이로 인해 인덱스를 구성하는 컬럼의 순서가 매우 중요하다. employees에 (gender, birth_date) 인덱스가 있을 때 WHERE 조건절에 gender 컬럼에 대한 비교 조건이 필수적이다. 하지만 MySQL 8.0 버전부터는 인덱스 스킵 스캔 최적화가 도입되어 후행 컬럼인 birth_date 컬럼의 조건만 가지고도 쿼리를 최적화한다.

인덱스의 선행 컬럼이 매우 다양한 값을 가지는 경우에는 인덱스 스킵 스캔 최적화가 비효율적일 수 있기 때문에 인덱스의 선행 컬럼이 소수의 유니크한 값을 가질 때만 인덱스 스킵 스캔 최적화를 사용한다.

옵티마이저의 인덱스 스킵 스캔 최적화 기능은 다음과 같이 활성화 여부를 제어할 수 있다.

-- // 특정 테이블에 대해 인덱스 스킵 스캔을 사용하도록 힌트를 사용
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';

해시 조인(hash_join)

해시 조인은 첫 번째 레코드를 찾는 데는 시간이 많이 걸리지만 최종 레코드를 찾는 데까지는 시간이 많이 걸리지 않음을 알 수 있다. 네스티드 루프 조인은 마지막 레코드를 찾는 데까지는 시간이 많이 걸리지만 첫 번쨰 레코드를 찾는 것은 상대적으로 훨씬 빠르다. 즉, 해시 조인 쿼리는 최고 스루풋(Best Throughput) 전략에 적합하며, 네스티드 루프 조인은 최고 응답 속도(Best Response-time) 전략에 적합하다. 일반적인 웹 서비스는 OLTP 서비스이기 때문에 스루풋도 중요하지만 응답 속도가 더 중요하고, 분석과 같은 서비스는 사용자의 응답 시간보다는 전체적으로 처리 소요 시간이 중요하기 때문에 전체 스루풋이 더 중요하다.

인덱스 정렬 선호(prefer_ordering_index)

MySQL 옵티마이저는 ORDER BY 또는 GROUP BY를 인덱스를 사용해 처리 가능한 경우 쿼리의 실행 계획에서 이 인덱스의 가중치를 높이 설정해서 실행된다. 위 쿼리는 대표적으로 다음 2가지 실행 계획을 선택할 수 있다.

ix_hiredate 인덱스를 이용해 "hire_date BETWEEN '1985-01-01' AND '1985-02-01'" 조건에 일치하는 레코드를 찾은 다음, emp_no로 정렬해서 결과를 반환
employees 테이블의 프라이머리 키가 emp_no이므로 프라이머리 키를 정순으로 읽으면서 hire_date 컬럼의 조건에 일치하는지 비교 후 결과를 반환

상황에 따라 1번이 효율적일 수도 있고, 2번이 효율적일 수도 있다. 일반적으로 hire_date 컬럼의 조건에 부합되는 레코드 건수가 많지 않다면 1번이 효율적인데 가끔 MySQL 옵티마이저가 잘못된 실행 계획인 2번 실행 계획을 선택하는 경우가 있다.

-- // 현재 커넥션에서만 prefer_ordering_index 옵션 비활성화
mysql> SET SESSION optimizer_switch='prefer_ordering_index=OFF';

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

MySQL 8.0.21 버전부터는 MySQL 서버 옵티마이저가 ORDER BY를 위한 인덱스에 너무 가중치를 부여하지 않도록 prefer_ordering_index 옵티마이저 옵션이 추가됐다. 기본값은 ON으로 설정되어 있어 옵티마이저가 자주 실수를 한다면 OFF로 변경하면 된다.

조인 최적화 알고리즘

MySQL에는 조인 쿼리의 실행 계획 최적화를 위한 알고리즘 2개가 있다. 하나의 쿼리에서 조인되는 테이블의 개수가 많아지면 최적화된 실행 계획을 찾는 것이 상당히 어려워지고, 실행 계획을 수립하는 데만 오랜 시간이 걸릴 수 있다. 왜 그런 현상이 발생하고, 어떻게 그런 현상을 피할 수 있을지 살펴보자.

Exhaustive 검색(완전 탐색) 알고리즘

MySQL 5.0과 그 이전 버전에 사용되던 조인 최적화 기법으로, FROM 절에 명시된 모든 테이블의 조합에 대해 실행 계획의 비용을 계산해서 최적의 조합 1개를 찾는 방법인데 테이블이 20개라면 이 방법으로 처리해씅ㄹ 때 가능한 조인 조합은 모두 20!(Factorial, 3628800)개가 된다. 10개만 해도 실행 계획을 수립하는데 몇 분이 걸리는데 11개로 늘어나면 시간은 11배의 시간이 더 걸린다.

Greedy 검색 알고리즘

Greedy 검색 알고리즘은 Exhaustive 검색 알고리즘의 시간 소모적인 문제점을 해결하기 위해 MySQL 5.0부터 도입된 조인 최적화 기법이다. Greedy는 Exhaustive 검색 알고리즘보다는 조금 복잡한 형태로 최적의 조인 순서를 결정한다. 순서는 간단히 다음과 같다.

전체 N개의 테이블 중에서 optimizer_search_depth 시스템 설정 변수에 정의된 개수의 테이블로 가능한 조인 조합을 생성
1번에서 생성된 조인 조합 중에서 최소 비용의 실행 계획 하나를 선정
2번에서 선정된 실행 계획의 첫 번째 테이블을 "부분 실행 계획"의 첫 번째 테이블로 선정
전체 N-1개의 테이블 중(3번에서 선택된 제이블 제외)에서 optimizer_search_depth 시스템 설정 변수에 정의된 개수의 테이블로 가능한 조인 조합 생성
4번에서 생성된 조인 조합들을 하나씩 3번에서 생성된 "부분 실행 계획"에 대입해 실행 비용을 계산
5번의 비용 계산 결과, 최적의 실행 계획에서 두 번째 테이블을 3번에서 생성된 "부분 실행 계획"의 두 번째 테이블로 선정
최종적으로 "부분 실행 계획"이 테이블의 조인 순서로 결정됨

Greedy 검색 알고리즘은 optimizer_search_depth 시스템 변수에 설정된 값에 따라 조인 최적화의 비용이 상당히 줄어들 수 있는데 기본값은 62이다. 0으로 설정되면 Greedy 검색을 위한 최적의 조인 검색 테이블의 개수를 MySQL 옵티마이저가 자동으로 결정하고, optimizer_search_depth 설정 값보다 크면 그만큼만 Exhaustive 검색을, 나머지 테이블은 Greedy 검색을 사용한다. optimizer_search_depth의 기본값은 62인데, 많은 테이블이 조인되는 쿼리에서는 상당히 부담이 될 수 있다. 특히 optimizer_prune_level 시스템 변수가 0으로 설정된 경우에는 optimizer_search_depth의 설정값이 쿼리의 성능에 심각한 영향을 미칠 수 있으니 optimizer_serach_depth를 4~5 정도로 설정하는 것이 좋다.

optimizer_prune_level 시스템 변수는 Heuristic 검색이 작동하는 방식을 제어한다. Heuristic 검색의 가장 핵심적인 내용은 다양한 조인 순서의 비용을 계산하던 도중 이미 계산했던 조인 순서의 비용보다 큰 경우에는 언제든지 중간에 포기할 수 있다는 것이다. 그리고 아우터 조인으로 연결되는 테이블은 우선순위에서 제거하는 등 경험 기반의 최적화도 Heuristic 검색 최적화에는 포함되어 있다. 1로 설정하면 경험 기반의 Heuristic 알고리즘을 사용하고, 0으로 설정하면 적용되지 않는다. 실제 Heuristic 조인 최적화는 조인 대상 테이블이 몇 개 되지 않더라도 상당한 성능 차이를 내기 때문에 0으로 설정하지 말자.

쿼리 힌트

옵티마이저와 힌트 (2/2)

쿼리 힌트 MySQL 서버는 우리가 서비스하는 비즈니스를 100% 이해하지 못하기 때문에 서비스 개발자나 DBA보다 MySQL 서버가 부족한 실행 계획을 수립할 때가 있을 수 있다. 이런 경우 옵티마이저에

oneny.tistory.com

출처

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

저작자표시

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

실행 계획 (0)	2023.11.30
옵티마이저와 힌트 (2/2) (1)	2023.11.13
인덱스 (1)	2023.11.06
InnoDB 스토리지 엔진 아키텍처 (1)	2023.11.02
MySQL 서버 설정과 사용자 및 권한 (1)	2023.10.21

옵티마이저와 힌트 (1/2)