MySQL 서버의 옵티마이저가 실행 계획을 수립할 때 통계 정보와 옵티마이저 옵션을 결합하여 최적의 실행 계획을 수립하게 된다.

• 조인 관련 옵티마이저 옵션
  • MySQL 초기 버전부터 제공되었고 많은 사람들이 신경쓰지 않지만, 조인이 많이 사용되는 서비스에서는 알아야한다.
• 옵티마이저 스위치
  • MySQL 5.5 버전부터 제공되어 고급 최적화 기능들을 활성화할지 제어하는 용도로 사용된다.

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

각각의 옵티마이저 스위치 옵션은 default, on, off 중에서 하나를 설정할 수 있다. 또한 글로벌과 세션별 모두 설정할 수 있는 시스템 변수이므로 MySQL 서버 전체적으로 또는 현재 커넥션에 대해서만 다음과 같이 설정할 수 있고 옵티마이저 힌트를 이용해 현재 쿼리에만 설정할 수 있다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

// 글로벌
SET GLOBAL optimizer_switch='index_merge=on,index_merge_union=on,...';

// 로컬 세션
SET SESSION optimizer_switch='index_merge=on,index_merge_union=on,...';

// 옵티마이저 힌트
SELECT /*+ SET_VAR(optimizer_switch='condition_fanout_filter=off`) */
...
FROM ...

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

MRR이란?
MRR은 “Multi-Range Read"를 줄여서 부르는 이름인데, 메뉴얼에서는 DS-MRR(Disk Sweep Multi-Range Read)이라고도 한다.

네스티드 루프 조인
MySQL 서버에서 지금까지 지원하던 조인 방식은 드라이빙 테이블(조인에서 가장 먼저 읽는 테이블)의 레코드를 한 건 읽어서 드리븐 테이블의 일치하는 레코드를 찾아서 조인을 수행하는 네스티드 루프 조인이다.

MySQL 서버의 내부 구조상 조인 처리는 MySQL 엔진이 처리하지만, 실제 레코드를 검색하고 읽는 부분은 스토리지 엔진이 담당하기 때문에, 드라이빙 테이블의 레코드 건별로 테이블의 레코드를 찾으면 레코드를 찾고 읽는 스토리지 엔진에서는 아무런 최적화를 할 수 없다는 문제점이 있었다.

이러한 점을 보완하기 위해 MySQL 서버는 드라이빙 테이블의 레코드를 읽어서 드리븐 테이블과의 조인을 즉시 실행하지 않고 조인 대상을 버퍼링 한다. 조인 버퍼에 레코드가 가득 차면 MySQL 엔진은 버퍼링된 레코드를 스토리지 엔진으로 한 번에 요청하게 된다. 이러한 처리로 인해 스토리지 엔진은 읽어야할 레코드들을 데이터 페이지에 정렬된 순서로 접근해서 디스크의 데이터 페이지 읽기를 최소화 한다.

이러한 방식을 MRR이라고 하며, MRR을 응용해서 실행되는 조인 방식을 BKA(Batched Key Access) 조인이라고 한다. BKA 조인 최적화는 쿼리의 특성에 따라 BKA 조인이 큰 도움이 되는 경우도 있지만, 부가적인 정렬 작업이 필요해지면서 오히려 성능에 안좋은 영향을 미치는 경우가 있어 기본적으로 비활성화되어있다.

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

MySQL 서버에서 사용되는 대부분의 조인은 네스티드 루프 조인이며, 조인의 연결 조건이 되는 컬럼에 모두 인덱스가 있는 경우 사용되는 조인 방식이다.

1
2
3
4
5
6
7
8

EXPLAIN
SELECT *
FROM employees e
  INNER JOIN salaries s ON s.emp_no=e.emp_no
    AND s.from_date <= NOW()
    AND s.to_date >= NOW()
WHERE e.first_name = 'Amor'
;

위와 같은 형태의 조인은 프로그래밍 언어에서 마치 중첩된 반복 명령을 사용하는 것처럼 작동한다고 해서 네스티드 루프 조인이라고 한다.

1
2
3
4
5
6

for(row1 IN employees) {
  for(row2 IN salaries) {
    if(condition_matched) 
      return (row1, row2);
  }
}

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

조인 알고리즘에서 “Block"이라는 단어가 사용되면 별도의 버퍼가 사용되었음을 의미이다.
조인 쿼리의 실행 계획에서 Extra 컬럼에 “Using Join buffer"라는 문구가 표시되면 그 실행 계획은 조인 버퍼를 사용한다는 것을 의미한다.

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

예를 들어 드라이빙 테이블에서 일치하는 레코드가 1000건 이었는데, 드리븐 테이블의 조인 조건이 인덱스를 이용할 수 없었다면 드리븐 테이블에서 연결되는 레코드를 찾기 위해 1000번의 풀 테이블 스캔을 해야한다. 따라서 드리븐 테이블을 검색할 때 인덱스를 사용할 수 없는 쿼리는 상당히 느려지며, 옵티마이저는 최대한 드리븐 테이블의 검색이 인덱스를 사용할 수 있게 실행 계획을 수립한다.

그런데 어떤 방식으로도 드리븐 테이블의 풀 테이블 스캔이나 인덱스 풀 스캔을 피할 수없다면 옵티마이저는 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시한 후 드리븐 테이블과 이 메모리 캐시를 조인하는 형태로 처리한다. 이때 사용되는 메모리의 캐시를 조인 버퍼라고 한다. 조인 버퍼는 join_buffer_size 시스템 변수로 크기를 제한할 수 있으며, 조인이 완료되면 조인 버퍼는 바로 해제된다.

1
2
3
4
5

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

위 쿼리의 실행 계획을 살펴보면 다음과 같이 dept_emp테이블이 드라이빙 테이블이며, employees 테입르을 읽을 때는 조인 버퍼를 이용해 블록 네스티드 루프 조인을 한다는 것을 알 수 있다.

1. dept_emp 테이블의 ix_fromdate인덱스를 이용해 조건을 만족하는 레코드를 검색한다.
2. 조인에 필요한 나머지 컬럼을 모두 dept_emp 테이블로부터 읽어서 조인 버퍼에 저장한다.
3. employees 테이블의 프라이머리 키를 이용해 조건을 만족하는 레코드를 검색한다.
4. 3번에서 검색된 결과(employees)에 2번의 캐시된 조인 버퍼의 레코드를 결합해서 반환한다.

4번 단계가 employees 테이블의 결과를 기준으로 dept_emp 테이블의 결과를 병합하는 것 처럼 조인 버퍼가 사용되는 쿼리에서는 조인의 순서가 거꾸로인 것처럼 실행된다. 실제 이 쿼리의 실행계획 상으로는 dept_emp 테이블이 드라이빙 테이블, employees 테이블이 드리븐 테이블이지만, 실제 드라이빙 테이블의 결과를 조인 버퍼에 담아두고 드리븐 테이블을 읽어 조인 버퍼에서 일치하는 레코드를 찾는 방식으로 처리된다.

일반적으로 조인이 수행된 후 가져오는 결과는 드라이빙 테이블의 순서에 의해 결정되지만, 조인 버퍼가 사용되는 조인에서는 결과의 정렬 순서가 흐트러 질 수 있다.

MySQL 8.0.18 버전부터는 해시 조인 알고리즘이 도입돼었고, 8.0.20 버전부터는 블록 네스티드 루프 조인은 더이상 사용되지 않고 해시 조인 알고리즘이 대체되어 사용된다.

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

1
2
3
4
5

// 인덱스 추가
ALTER TABEL emplyees ADD INDEX ix_lastname_firstname (last_name, first_name);

// index_condition_pushdown 비활성화
SET optimizer_swith='index_condition_pushdown=off';

1
2
3
4
5

SELECT *
FROM employees
WHERE last_name='Action'
  AND first_name LIKE '%sal'
;

인덱스 컨디션 푸시다운이 비활성화 된 후 실행된 조회 쿼리는 이 first_name LIKE %sal 조건으로 인해 인덱스 레인지 스캔으로는 검색 해야할 인덱스의 범위를 좁힐 수 없으므로 ix_lastname_firstname 인덱스에 함께 저장되어있는 fisrt_name 값을 활용하지 못한다. 따라서 last_name을 이용하여 데이터를 모두 읽은 후 first_name 조건을 사용자가 원하는 결과인지 하나씩 비교해보는 조건(체크조건, 필터링 조건)으로만 사용하게 되어 실행 계획의 Extra 컬럼에 Using where로 표시된다.

따라서 해당 쿼리의 last_name='Action'을 만족하는 레코드가 100,000건이고, 그 중 first_name LIKE %sal 조건에 해당하는 레코드가 1건 뿐이라면 999,999건의 레코드 읽기 작업은 불필요한 작업이 되어버린다.

인덱스를 비교하는 작업은 실제 InnoDB 스토리지 엔진에서 수행하지만 테이블의 레코드에서 조건을 비교하는 작업은 MySQL 엔진이 수행하게 된다. MySQL 5.5 버전까지는 인덱스를 범위 제한 조건으로 사용하지 못하기 때문에 MySQL 엔진이 스토리지 엔진으로 해당 조건을 전달하지 않았다.

이는 MySQL 5.6 버전부터 개선되어 인덱스를 이용해 최대한 필터링까지 완료하여 꼭 필요한 레코드에 대해서만 테이블 읽기를 수행하게 되었다. 인덱스 컨디션 푸시다운을 활성화하고 실행 계획을 확인하면 아래와 같이 출력된다.

인덱스 확장(use_index_extensions)

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

1
2
3
4
5
6
7
8

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;

InnoDB 스토리지 엔진은 프라이머리 키를 클러스터링 키로 생성한다. 따라서 모든 세컨더리 인덱스는 리프 노드에 프라이머리 키 값을 가진다. dept_emp테이블에서 프라이머리 키는 (dept_no, emp_no)이며, 세컨더리 인덱스 ix_fromdate는 from_date 컬럼만 포함한다. 따라서 세컨더리 인덱스는 데이터 레코드를 찾아가기 위해 프라이머리 키인 (dept_no, emp_no) 컬럼을 순서대로 포함한다. 최종적으로 ix_fromdate인덱스는 (dept_no, emp_no) 조합으로 인덱스를 생성한 것과 흡사하게 작동할 수 있다.

1
2
3
4
5
6

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

예전 MySQL 버전에서는 위와 같은 쿼리가 세컨더리 인덱스의 마지막에 자동 추가되는 프라이머리 키를 제대로 활용하지 못했지만, 업그레이드 되면서 옵티마이저는 ix_fromdate인덱스의 마지막에 (dept_no, emp_no)가 있다는 것을 인지하고 실행 계획을 수립하도록 개선되었다.

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

1
2
3
4
5

EXPLAIN
SELECT COUNT(*)
FROM dept_emp
WHERE from_date='1987-07-25'
;

dept_no='d001' 조건을 제거하면 from_date컬럼을 위한 3 바이트만 표시된다.

1
2
3
4
5
6

EXPLAIN
SELECT COUNT(*)
FROM dept_emp
WHERE from_date='1987-07-25'
ORDER BY dept_no
;

InnoDB의 프라이머리 키가 세컨더리 인덱스에 포함돼 있으므로 위와 같은 정렬 작업도 인덱스로 처리되어 Extra 컬럼에 Using Filesort가 표시되지 않게 된다.

인덱스 머지(index_merge)

인덱스를 이용해 쿼리를 실행하는 경우, 대부분 옵티마이저는 테이블별로 하나의 인덱스만 사용하다록 실행 계획을 수립하지만 인덱스 머지 실행 계획을 사용하면 하나의 테이블에 2개 이상의 인덱스를 이용해 쿼리를 처리한다.

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

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

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

1
2
3
4
5
6

EXPLAIN
SELECT (
FROM employees
WHERE first_name='Georgi'
  AND emp_no BETWEEN 10000 AND 20000
;

위 쿼리는 2개의 WHERE조건을 가지고 있는데, employees 테이블의 first_name, emp_no 모두 인덱스를 가지고 있다. 즉 2개 중에서 어떤 조건을 사용하더라도 인덱스를 모두 사용할 수 있으므로 옵티마이저는 ix_firstname, PRIMARY 키를 모두 사용해서 쿼리를 처리하도록 결정했다.

실행 계획의 Extra 컬럼에 Using intersect라고 표시된 것은 이 쿠러가 여러개의 인덱스를 각각 검색해서 그 결과의 교집합만 반환했다는 것을 의미한다.

인덱스 머지 합집합(index_merge_union)

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

1
2
3
4
5

SELECT *
FROM employees
WHERE first_name='Matt'
  OR hire='1987-03-31'
;

employees 테이블에는 ix_firstname, ix_hiredate 인덱스가 준비되어 있으므로 모든 조건이 인덱스를 활용할 수 있다. 쿼리의 실행 계획의 Extra 컬럼에 Using union(…, …)라고 표시된다면 인덱스 머지 최적화가 각각의 인덱스 검색 결과를 Union 알고리즘으로 병합했다는 것을 의미한다.

Union 알고리즘
인덱스의 결과가 이미 프라이머리 키로 정렬되어 있을 때, 각각의 인덱스로 조회한 결과를 하나씩 가져와 비교하면서 emp_no 컬럼의 값이 중복된 레코드들을 우선 순위 큐를 이용해 정렬 없이 걸러낼 수 있다.

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

Union 알고리즘을 사용할 수 없는데 결과의 정렬이 필요한 경우 MySQL 서버는 인덱스 머지 최적화의 Sort union 알고리즘을 사용한다.

1
2
3
4
5

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

ix_hiredate 같은 경우 날짜 순서대로 프라이머리 키와 일치하지 않을 수 있다. 따라서 위 쿼리에서 중복 제거를 위해 우선순위 큐를 사용할 수 없게 된다. 따라서 MySQL 서버는 두 집합의 결과에서 중복을 제거하기 위해 각 집합을 emp_no컬럼으로 정렬한 다음 중복 제거를 수행한다.

인덱스 머지 최적화에서 중복 제러를 위해 강제로 정렬을 수행해야 하는 경우에는 실행 계획의 Extra 컬럼에 “Using sort_union” 문구가 표시된다.

세미 조인(semijoin)

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

1
2
3
4
5
6
7

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

일반적인 RDBMS는 dept_emp 테이블을 조회하는 서브쿼리 부분이 먼저 실행되고 그다음 employees 테이블에서 일치하는 레코드만 검색 하지만, MySQL 5.7 까지는 세미 조인 형태의 최적화가 부족하여 employees 테이블을 풀 스캔 하면서 한 건 한 건 서브쿼리의 조건에 일치하는지 비교한다. 이에따라 57건만 읽어도 될 쿼리를 30만 건 넘게 읽어 처리된다.

세미조인 형태 쿼리와 안티 세미조인 형태의 쿼리는 최적화 방법이 차이가 있다.

• 세미조인: = (subquery), IN (subquery)
  • 세미 조인 최적화
  • IN-to-EXISTS 최적화
  • MATERIALIZATION 최적화
• 안티세미조인: <> (subquery), NOT IN (subquery)
  • IN-to-EXISTS 최적화
  • MATERIALIZATION 최적화

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

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

Table Pull-out 최적화는 사용 가능하면 항상 세미조인보다는 좋은 성능을 내기 때문에 별도로 제어하는 옵티마이저 옵션을 제공하지 않는다.

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

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

1
2
3
4
5
6
7

EXPLAIN
SELECT * FROM employees e
WHERE 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 최적화는 별도로 실행 계획의 Extra 컬럼에 특별한 문구를 표시하지 않는다. 그렇기 때문에 Table pullout 최적화가 사용됐는지는 실행 계획에서 해당 테이블들의 id 컬럼 값이 일치하는지 비교해보는 것이 가장 간단하다.

Table pullout 최적화가 사용됐는지 더 정확하게 확인하는 방법은 EXPLAIN 명령을 실행한 직후 SHOW WARNINGS 명령으로 MySQL 옵티마이저가 재작성한 쿼리를 살펴보는 것이다.

Table pullout 최적화는 모든 형태의 서브쿼리에서 사용될 수 있는 것은 아니다.

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

퍼스트 매치(firstmatch)

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

1
2
3
4
5
6
7
8
9

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로 표시된 것으로 봐서 서브쿼리 패턴이 아닌 조인으로 처리되었음을 알 수 있다.

“FirstMatch(e)” 문구는 employees 테이블의 레코드에 대해 titles 테이블에 일치하는 레코드 1건만 찾으면 더이상의 titles 테이블 검색을 하지 않는다는 것을 의미한다. 실제 의미론적으로 EXISTS (subquery)와 동일하게 처리된 것이다. 하지만 FirstMatch는 서브쿼라 아니라 조인으로 풀어서 실행하면서 일치하는 첫번째 레코드만 검색하는 최적화를 실행한 것이다.

First Match 최적화는 MySQL 5.5에서 수행했던 최적화 방법은 IN-to-EXISTS 변환과 거의 비슷한 처리 로직을 수행하지만 First Match 최적화 전략은 다음과 같은 장점이 있다.

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

FirstMatch 최적화 또한 특정 형태의 서브쿼리에서 자주 사용되는 최적화이다.

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

루스 스캔(loosescan)

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

1
2
3
4
5
6
7

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도 동일하게 작동한다.

LooseScan 최적화는 다음과 같은 특성을 지닌다.

• 루스 인덱스 스캔으로 서브쿼리 테이블을 읽고, 그다음으로 아우터 테이블을 드리븐으로 사용해서 조인을 수행한다. 그래서 서브쿼리 부분이 루스 인덱스 스캔을 사용할 수 있는 조건이 갖춰져야 사용할 수 있는 최적화다.
  • 다음과 같은 형태의 서브쿼리들에서 사용할 수 있다.

    1 2	SELECT .. FROM .. WHERE expr IN (SELECT keypart1 FROM tab WHERE ...) SELECT .. FROM .. WHERE expr IN (SELECT keypart2 FROM tab WHERE keypart1='상수')

구체화(Materialization)

Materialization 최적화는 세미 조인에 사용된 서브쿼리를 통째로 구체화해서 쿼리를 최적화한다는 의미다. 구체화는 쉽게 표현하면 내부 임시 테이블을 생성한다는 것을 의미한다.

1
2
3
4
5
6
7
8

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'
);

위 쿼리를 FirstMatch 최적화를 사용하면 employees 테이블에 대한 조건이 서브쿼리 이외에는 아무것도 없기 때문에 employees 테이블을 풀 스캔해야 하므로 성능 향상에 도움이 되지 않는다.

MySQL 서버 옵티마이저는 이런 형태의 쿼리를 위해 서브쿼리 구체화(Subquery Materialization)라는 최적화를 도입했다. 실행 계획 마지막 라인의 select_type 컬럼에 “MATERIALIZED"라고 출력된다.

이 쿼리에서 사용하는 테이블은 2개이지만 임시 테이블()이 생성되어 3개 라인이 출력된다. dept_emp 테이블을 읽는 서브퀄가 먼저 실행되어 그결과로 임시 테이블을 만들고, 서브쿼라 구체화된 임시 테이블과 employees 테이블을 조인해서 결과를 반환한다.

Materialization 최적화는 다른 서브쿼리와 달리, 서브 쿼리 내에 GROUP BY절이 있어도 사용할 수 있다.

1
2
3
4
5
6
7
8
9

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'
  GROUP BY de.dept_no
);

Materialization 최적화가 사용될 수 있는 형태의 쿼리에도 역시 몇 가지 제한사항과 특성이 있다.

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

세미조인이 아닌 서브쿼리의 최적화에서도 구최화를 이용한 최적화가 사용될 수 있다. 그러나 해당 옵션이 비활성화된다면 세미 조인이 아닌 서브쿼리 최적화에서도 구체화를 이용한 최적화는 사용되지 못한다.

중복 제거(Duplicated Weed-out)

Duplicated Weed-out은 세미 조인 서브쿼리를 일반적인 INNER JOIN 쿼리로 바꿔 실행하고 마지막에 중복된 레코드를 제거하는 방법으로 처리되는 최적화 알고리즘이다.

1
2
3
4
5
6
7
8

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가 발생할 수 있다.

1
2
3
4
5
6
7

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

위와 같은 방식으로 GROUP BY를 활용하면 세미 조인 서브쿼리와 동일한 결과를 얻을 수 있다. 실제로 Duplicate Weedout 최적화 알고리즘은 원본 쿼리를 위와 같이 INNER JOIN + GROUP BY 절로 바꿔 실행하는 것과 동일한 작업으로 쿼리를 처리한다.

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

최적화를 이용한 예제 쿼리의 실행 계획은 다음과 같으며, 이 실행 계획에서는 “Duplicate Weedout"이라는 문구가 별도로 표시되지 않는다. 하지만 Extra 컬럼에 “Start temporary”, “End temporary” 문구가 별도로 표시된다.

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

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

컨디션 팬아웃(condition_fanout_filter)

조인을 실행할 때 드라이빙 테이블의 레코드를 기준으로 드리븐 테이블을 읽기 때문에 테이블의 순서는 쿼리의 성능에 매우 큰 영향을 미친다. 그래서 MySQL 옵티마이저는 여러 테이블이 조인되는 경우 가능하면 일치하는 레코드 건수가 적은 순서대로 조인을 실행한다.

1
2
3
4
5
6

SELECT *
FROM employees e
  INNER JOIN salaries s ON s.emp_no=e.emp_no
WHERE e.first_name='Matt'
  AND e.hire_date BETWEEN '1985-11-21' AND '1986-11-21'
;

Condition_fanout_filter 비활성화 실행 계획

1. employees 테이블에서 ix_firstname 인덱스를 이용해 first_name='Matt' 조건에 일치하는 233건의 레코드를 검색
2. 검색된 레코드 중에서 hire_date가 ‘1985-11-21’ 부터 ‘1986-11-21’일 사이인 레코드만 걸러냄.
   • 이 실행계획에서는 filtered 컬럼의 컬럼 값이 100인 것은 옵티마이저가 233건 모두 조건을 만족할 것으로 예측했다는 의미
3. employees 테이블을 읽은 결과에 대해 salaries 테이블의 프라이머리 키를 이용해 salaries 테이블의 레코드를 읽는다.
   • MySQL 옵티마이저는 employees 테이블의 레코드 한 건당 salaries 테이블의 레코드 10건이 일치할 것으로 예상함.

Condition_fanout_filter 비활성화 실행 계획

rows 컬럼의 값은 233으로 동일하지만 filtered 컬럼의 값이 23.20로 바뀌었다. MySQL 옵티마이저는 인덱스를 사용할 수 있는 first_name 컬럼 조건 이외의 나머지 조건(hire_date)에 대해서도 얼마나 조건을 충족할지를 고려했다는 의미이다.

즉 condition_fanout_filter 최적화가 비활성화 된 경우에는 employees 테이블에서 모든 조건을 충족하는 레코드가 233건일 것으로 예측했지만, 활성화시 54건만 조건을 충족할 것이라고 예측한것이다. MySQL 옵티마이저가 조건을 만족하는 레코드 건수를 정확하게 예측할 수 있다면 더 빠른 실행 계획을 만들어 낼 수 있다.

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

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

예제 쿼리가 실제 실행되는 경우에는 ix_firstname 인덱스만 사용한다. 하지만 실행 계획을 수립하는 경우 해당 인덱스를 통해 조건이 일치하는 레코드를 233건 정도라는 것을 알아내고, hire_date 컬럼의 조건을 만족하는 레코드의 비율을 대략 23.2%일 것으로 예측한다. hire_date 컬럼의 인덱스가 없다면 MySQL 옵티마이저는 first_name 컬럼의 인덱스를 이용해 hire_date 컬럼의 분포도를 살펴보고 filterd컬럼의 값을 예측한다.

MySQL 옵티마이저가 실행 계획을 수립할 때 테이블이나 인덱스의 통계 정보만 사용하는 것이 아니라 다음의 순서대로 사용 가능한 방식을 선택한다.

1. 옵티마이저(Range optimizer)를 이용한 예측
2. 히스토그램을 이용한 예측
3. 인덱스 통계를 이용한 예측
4. 추측에 기반한 예측(Guesstimates)

레인지 옵티마이저는 실제 인덱스의 데이터를 살펴보고 레코드 건수를 예측하는 방식인데, 실제 쿼리 실행 전 실행 계획 수립 단계에서 빠르게 소량의 데이터를 읽어본다. (인덱스를 이용해서 쿼리가 실행될 수 있을때만)

condition_fanout_filter 최적화 기능을 활성화하면 MySQL 옵티마이저는 더 정교한 계산을 거쳐 실행 계획을 수립한다.

그에 따라 쿼리의 실행 계획 수립에 더 많은 시간과 컴퓨팅 자원을 사용하게 되므로, MySQL 8.0 이전 버전에서도 쿼리 실행 계획이 잘못된 선택을 한 적이 별로 없다면 성능 향상에 크게 도움이 되지 않을수도 있고, 실행 계획 수립의 오버헤드가 더 크게 보일 수 있으므로 업그레이드 전 확인이 필요하다.

파생 테이블 머지(derived_merge)

1
2
3
4
5
6
7
8
9

EXPLAIN
SELECT *
FROM (
  SELECT *
  FROM employees
  WHERE first_name='Matt'
) derived_table
WHERE derived_table.hire_date='1986-04-03'
;

이전 버전의 MySQL 서버 처리 방식

employees 테이블을 읽는 라인의 select_type 컬럼 값이 DREIVED라고 표시되어 있는데, 이는 employees 테이블에서 first_name 컬럼 값이 ‘Matt’인 레코드들만 읽어 임시 테이블을 생성하고, 이 임시 테이블을 다시 읽어 hire_date 컬럼 값이 ‘1986-04-03’인 레코드만 걸러내어 봔환한 것이다. MySQL 서버에서는 FROM 절에 사용된 서브쿼리를 파생 테이블이라고 부른다.

이 실행 계획은 경우 MySQL 서버는 내부적으로 임시 테이블을 생성하고 first_name='Matt 인 레코드를 employees 테이블에서 읽어 임시 테이블로 INSERT 한다. 그 후 다시 임시 테이블을 읽으므로 MySQL 서버는 레코드를 복사하고 읽는 오버헤드가 더 추가된다.

내부적으로 생성되는 임시 테이블은 처음에는 메모리에 생성되지만, 임시 테이블에 저장될 레코드 건수가 많아지면 결국 디스크로 다시 기록되므로 임시 테이블이 메모리에 상주할 만큼 크기가 작다면 성능에 큰 영향을 미치지 않지만 레코드가 많아진다면 임시 테이블로 레코드를 복사하고 읽는 오버헤드로 인해 쿼리의 성능은 많이 느려진다.

MySQL 5.7 이후 처리 방식

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

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

1
2
3
4
5
6

SELECT employees.employees.emp_no
  , ...
FROM employees.employees
WHERE employees.employees.hire_date = DATE'1986-04-03'
  AND employees.employees.first_name = 'Matt'
'

예전 버전의 MySQL 서버에서는 서브쿼리로 작성된 쿼리를 외부 쿼리로 병합하는 작업을 DBA가 수작업으로 많이 처리했으나, 옵티마이저가 처리할 수 있어 굳이 쿼리를 새로 작성할 필요는 없어졌다.

하지만 모든 쿼리에 대해 옵티마이저가 서브쿼리를 외부 쿼리로 병합할 수 있는것은 아니며, 다음과 같은 조건에서는 옵티마이저가 자동으로 서브쿼리를 외부 쿼리로 병합할 수 없게 된다. 따라서 가능하다면 서브쿼리는 외부 쿼리로 수동으로 병합해서 작성하는 것이 쿼리의 성능 향상에 도움이 된다.

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

인비저블 인덱스(use_invisivble_indexes)

MySQL 8.0 이전 버전까지는 인덱스가 존재하면 항상 옵티마이저가 실행 계획을 수립할 때 해당 인덱스를 검토하고 사용했으나, 인덱스를 삭제하지 않고, 해당 인덱스를 사용하지 못하게 제어하는 기능을 제공한다.

ALTER TABLE ... ALTER INDEX ... [ VISIBLE | INVISIBLE ] 명령으로 인덱스의 가용 상태를 변경할 수 있다.

해당 옵션을 이용하면 INVISIBLE로 설정된 인덱스라 하더라도 옵티마이저가 사용하게 제어할 수 있다.

1

SET optimizer_switch='use_invisible_indexes=on';

스킵 스캔(skip_scan)

인덱스의 핵심은 값이 정렬되어 있다는 것이며, 이로 인해 인덱스를 구성하는 컬럼의 순서가 매우 종요하다. (A, B, C) 컬럼으로 구성된 인덱스가 있을 때의 WHERE 절에 B와 C 컬럼에 대한 조건이 있다면 쿼리는 인덱스를 사용할 수 없다. 인덱스 스킵 스캔은 제한적이긴 하지만 인덱스의 이런 제약 사항을 뛰어넘을 수 있는 최적화 기법이다.

1
2

ALTER TABLE employees
  ADD INDEX ix_gender_birthdate (gender, birth_date);

위와 같은 테이블이 있다면 WHERE 조건절에 gender 컬럼에 대한 비교 조건이 필수적이다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

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

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

위 쿼리에서 처음 쿼리는 인덱스를 사용할 수 없어 birth_date 컬럼부터 시작하는 인덱스를 새로 생성해야했으나, MySQL 8.0 버전부터는 인덱스 스킵 스캔 최적화가 도입되어 인덱스의 선행 컬럼이 조건절에 사용되지 않더라도 후행 컬럼만의 조건으로도 인덱스를 이용한 쿼리 성능 개선이 가능하다.

첫 번째 쿼리를 실행할 때 옵티마이저는 테이블에 존재하는 모든 gender 컬럼의 값을 가져와 두 번째 쿼리와 같이 gender 컬럼의 조건이 있는 것처럼 쿼리를 최적화한다. 따라서 인덱스의 선행 컬럼이 매우 다양한 값을 가지는 경우 인덱스 스킵 스캔 최적화가 비효율적일 수 있게된다. 따라서 인덱스의 선행 컬럼이 소수의 유니크한 값을 가질때만 인덱스 스킵 스캔 최적화를 사용한다.

해시 조인

MySQL 8.0.18 버전부터는 해시 조인이 추가로 지원되기 시작했다. 해시 조인 기능은 기존 네스티드 루프 조인보다 해시 조인이 빠르다고 생각하지만 항상 참은 아니다.

해시 조인은 첫 번째 레코드를 찾는 데는 시간이 많이 걸리지만 최종 레코드를 찾는 데 까지는 시간이 많이 걸리지 않는다. 네스티드 루프 조인은 마지막 레코드를 찾는 데 까지는 시간이 많이 걸리지만 첫 번째 레코드를 찾는것은 상대적으로 빠르다.

즉, 해시 조인 쿼리는 최고 스루풋(Best Throughput) 전략에 적합하고, 네스티드 루프 조인은 최고 응답 속도(Best Response-time) 전략에 적합하다.

• 일반적인 웹 서비스는 온라인 트랜잭션 서비스이기 때문에 스루풋도 중요하지만 응답 속도가 더 중요하다.
• 분석과 같은 서비스는 사용자의 응답 시간보다는 전체적으로 처리 소요시간이 중요하기 때문에 응답속도보다는 전체 스루풋이 중요하다.

MySQL 서버는 온라인 트랜잭션 처리를 위한 범용 RDBMS이므로 서버의 응답 속도가 더 중요할 수 있다. 이러한 이유로 MySQL 서버는 주로 조인 조건의 컬럼이 인덱스가 없다거나 조인 대상 테이블 중 일부의 레코드 건수가 매우 적은 경우에 대해서만 해시 조인 알고리즘을 사용하도록 설계되어있다.

해시 조인 최적화는 네스티드 루프 조인의 차선책 같은 기능이므로 옵티마이저 힌트를 이용해서 강제로 쿼리 실행 계획을 해시 조인으로 유도하는 것은 좋지 않다.

해시 조인 기능이 없었을때는 조인 조건이 인덱스를 제대로 활용할 수 없는 경우 블록 네스티드 루프 조인이라는 조인 알고리즘을 사용했다. 따라서 인덱스가 잘 설계된 데이터베이스에서는 블록 네스티드 루프 조인 실행 계획은 거의 볼 수 없었다. 블록 기반의 네스티드 루프 조인에서 블록은 join_buffer_size 시스템 변수로 크기를 조정할 수 있는 메모리 공간을 의하는데, 조인 버퍼를 무한정 크게 설정할 수 없고, 조인 대상 테이블의 레코드 크기가 조인 버퍼보다 큰 경우 드라이빙 테이블을 여러 번 반복해서 스캔해야 하는 문제가 있었다.

그래서 MySQL 8.0.18 ~ 8.0.19 버전에서는 동등 조인(Equi-Join)을 위해서는 해시 조인이 사용됐지만 안티 조인이나 세미 조인을 위해서는 블록 네스티드 루프 조인이 사용됐다. MySQL 8.0.20 버번부터는 block_nested_loop 같은 optimizer_switch 또는 BNL과 NO_BNL과 같은 힌트들도 블록 네스티드 루프가 아닌 해시 조인을 유도하는 목적으로 사용된다.

1
2
3
4
5
6

EXPLAIN
SELECT *
FROM employees e IGNORE INDEX(PRIMARY, ix_hiredate)
  INNER JOIN dept_emp de IGNORE INDEX(ix_empno_fromdate, ix_fromdate)
    ON de.emp_no=e.emp_no AND de.from_date=e.hire_Date
;

IGNORE로 인해 통해 인덱스를 활용하지 못하여 옵티마이저는 적절한 인덱스를 찾을수 없어 해시 조인을 활용하게 된다.

일반적으로 해시 조인은 빌드 단계(Build-phase) 와 프로브 단계(Probe-phase)로 나뉘어 처리된다.

• 빌드단계
  • 조인 대상 테이블 중에서 레코드 건수가 적어 해시 테이블로 만들기에 용이한 테이블을 골라 메모리에 해시 테이블을 생성(빌드)하는 작업을 수행한다.
  • 해시 테이블을 만들 때 사용되는 원본 테이블을 빌드 테이블이라고도 한다.
• 프로브 단계
  • 나머지 해시 테이블의 레코드를 읽어 해시 테이블의 일치 레코드를 찾는 과정을 의미한다.
  • 읽는 나머지 테이블을 프로브 테이블이라고도 한다.

위의 실행계획에서는 어느 테이블이 빌드 테이블이고 어느 테이블이 프로브 테이블인지 식별하기 어렵다. 이러한 경우에는 EXPLAIN FORMAT=TREE 명령 또는 EXPLAIN ANALYZE 명령을 사용하면 조금 더 쉽게 구분할 수 있다.

1
2
3
4
5
6

EXPLAIN FORMAT=TREE
SELECT *
FROM employees e IGNORE INDEX(PRIMARY, ix_hiredate)
  INNER JOIN dept_epq de IGNORE INDEX(ix_empno_fromdate, ix_fromdate)
    ON de.emp_no=e.emp_no
      AND de.from_date=e.hire_date \G

1
2
3
4
5

-> Inner hash join (e.hire_date = de.from_date), (e.emp_no = de.emp_no)
                (cost=9942694661.05 rows=331143)
  -> Table scan on e  (cost=0.08 rows=300252)
  -> Hash
    -> Table scan on de  (cost=33979.30 rows=331143) 

Tree 포맷 실행 계획에서 최하단 제일 안쪽의 dept_emp 테이블이 빌드 테이블로 선정되었다. MySQL 옵티마이저는 해시 조인을 위해 빌드 테이블인 dept_emp 테이블의 레코드를 읽어서 메모리에 해시 테이블을 생성했다. 그리고 프로브 테이블로 선택된 employees 테이블을 스캔하면서 메모리에 생성된 해시 테이블에서 레코드를 찾아 결과를 사용자에게 반환한다.

해시 테입르을 메모리에 저장할 때 MySQL 서버는 join_buffer_size 시스템 변수로 크기를 제어할 수 있는 조인 버퍼를 사용한다. 해시 테이블의 레코드 건수가 많아 공간이 부족할 경우 MySQL 서버는 빌드 테입르과 프로브 테이블을 적당한 크기의 청크로 분리 후 청크별로 동일 방식으로 해시 조인을 처리한다.

만들어질 해시 테이블이 설정된 메모리 크기보다 큰지 알 수 없기 때문에 해시 조인의 처리 방법이 복잡해진다. MySQL 서버는 dept_emp 테이블을 읽으며 메모리의 해시 테이블을 준비하다가 지정된 메모리 크기를 넘어서면 dept_emp 테이블의 나머지 레코드를 디스크에 청크로 구분하여 저장하고 employees 테이블의 emp_no 값을 이용해 메모리의 해시 테이블을 검색하여 1차 조인 결과를 생성한다.

동시에 employees 테이블에서 읽은 레코드를 디스크에 청크로 구분해 저장한다. 이때 “빌드 테이블 청크"는 dept_emp 테이블의 레코드들을 저장해둔 공간이고, “프로브 테이블 청크"는 employees 테이블의 레코드들을 저장해둔 공간이다.

1차 조인이 완료되면 MySQL 서버는 디스크에 저장된 “빌드 테이블 청크"에서 첫 번째 청크를 읽어 다시 “메모리 해시 테이블"을 구축한다. 그리고 “프로브 테이블 청크"에서 첫 번째 청크를 읽으면서 새로 구축된 “메모리 해시 테이블"과 조인을 수행해 2차 결과를 가져온다.

이후 디스크에 저장된 청크 개수만큼 반복 처리해서 완성된 조인 결과를 만들어내고, 청크 단위로 조인을 수행하기 위해 2차 해시 함수를 이용해 “빌드 테이블"과 “프로브 테이블"을 동일 개수의 청크로 쪼개 디스크에 저장한다.

MySQL 옵티마이저는 빌드 테이블의 크기에 따라 메모리에서 모두 처리 가능한 해시 조인의 경우 클래식 해시 조인 알고리즘을 사용하고, 해시 테이블이 조인 버퍼 메모리보다 큰 경우 그레이스 해시 조인 알고리즘을 하이브리드하게 활용하도록 구현되어있다.

인덱스 정렬 선호(prefer_ordering_index)

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

1
2
3
4
5
6

EXPLAIN
SELECT *
FROM employees
WHERE hiring_date BETWEEN '1985-01-01' AND '1985-02-01'
ORDER BY emp_no
;

선택될 수 있는 대표적인 실행계획

1. ix_hiredate 인덱스를 이용해 조건에 일치하는 레코드를 찾은 다음 emp_no로 정렬해서 결과를 반환
2. employees 테이블의 프라이머리 키가 emp_no이므로 프라이머리 키를 정순으로 읽으며 hire_date 컬럼의 조건에 일치하는지 비교 후 결과를 반환

상황에 따라 다르지만 일반적으로 hire_date 컬럼의 조건에 부합되는 레코드 건수가 많지 않다면 1번으로 실행될 것 같지만, 실행 계획에서는 체므해야 하는 레코드 건수가 상당히 많음에도 불구하고 PRIMARY 키를 풀 스캔하면서 hire_date 컬럼을 필터링하도록 쿼리를 처리하고 있다. 이는 정렬된 인덱스를 활용하도록 하기 위해 잘못된 실행계획을 선택했을 확률이 높다.

MySQL 8.0.20 버전까지는 이러한 옵티마이저의 실수가 자주 발생하면 IGNORE INDEX 힌트를 사용하여 ORDER BY를 위한 특정 인덱스를 사용하지 못하도록 했으나, MySQL 8.0.21 버전부터는 옵티마이저가 ORDER BY를 위한 인덱스에 너무 가중치를 부여하지 않도록 prefer_ordering_index 옵티마이저 옵션이 추가되었다.

기본값은 ON 이지만 비효율적인 선택을 자주 한다면 OFF로 변경할 수 있다.