본문 바로가기
Book/Real MySQL 8.0 上

09. 옵티마이저와 힌트-2

kinggora 2022. 12. 25. 21:00

9.3 고급 최적화

MySQL 서버의 옵티마이저는 통계 정보와 옵티마이저 옵션을 결합해서 최적의 실행 계획을 수립한다.

 

옵티마이저 옵션

  • 조인 관련 옵티마이저 옵션: MySQL 서버 초기 버전부터 제공되던 옵션. 조인이 많이 사용되는 서비스에서 중요
  • 옵티마이저 스위치: 5.5 버전부터 지원. 고급 최적화 기능의 활성화 제어

옵티마이저 스위치 옵션

옵티마이저 스위치 옵션은 optimizer_switch 시스템 변수를 이용하여 제어한다.

 

optimizer_switch

  • 여러 개의 옵션을 세트로 묶어서 설정하는 방식으로 사용
  • 각 옵티마이저 스위치 옵션에 "default", "on", "off" 중에서 하나를 설정 가능 (defalut: 기본값 적용)

optimizer_switch 시스템 변수의 최적화 옵션

 

옵티마이저 스위치 옵션은 글로벌과 세션별로 모두 설정할 수 있다. 

 

 

다음과 같이 "SET_VAR" 옵티마이저 힌트를 이용해 현재 쿼리에만 설정할 수도 있다.

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

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

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

 

MRR (Multi-Range Read)

메뉴얼에서는 DS-MSS(Disk Sweep MMS) 라고도 한다.

  • 조인 대상 테이블 중 하나로부터 읽은 레코드를 조인 버퍼에 버퍼링하여 스토리지 엔진에 한 번에 요청하는 방법
  • 스토리지 엔진은 읽어야 할 레코드들을 데이터 페이지에 정렬된 순서로 접근해서 디스크의 데이터 페이지 읽기를 최소화 할 수 있다.
  • 데이터 페이지가 메모리(InnoDB 버퍼 풀)에 있더라도 버퍼 풀의 접근을 최소화 할 수 있다. 

 

MRR을 응용해서 실행되는 조인 방식은 BKA(Batched Key Access) 조인이라고 한다.

BKA 조인 최적화는 기본적으로 비활성화되어 있는데 BKA 조인의 단점이 있기 때문이다. 쿼리의 특성에 따라 BKA 조인이 큰 도움이 되는 경우도 있지만, 부가 정렬 작업이 필요하기 때문에 오히려 성능에 안 좋은 영향을 미칠 수 있다.

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

네스티드 루프 조인

  • MySQL 서버에서 사용되는 대부분의 조인
  • 조인의 연결 조건이 되는 컬럼에 모두 인덱스가 있는 경우 사용된다.
  • 레코드를 읽어서 다른 버퍼 공간에 저장하지 않고 즉시 드리븐 테이블의 레코드를 찾아서 반환한다.

 

위 쿼리는 employees 테이블에서 first_name 조건(WHERE)이 일치하는 레코드 1건마다 salaries 테이블의 결과 레코드를 순환하며 조인을 실행한다. 

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

 

위 쿼리의 의사 코드(Psuedo Code)

 

Nested Loop Join과 Block Nested Loop Join의 차이점

  • 조인 버퍼의 사용 여부
  • 조인에서 드라이빙 테이블과 드리븐 테이블이 조인되는 순서

조인 알고리즘에서 "Block"은 별도로 조인용 버퍼가 있음을 의미한다.

실행 계획 Extra: "Using join buffer"

 

조인은 드라이빙 테이블에서 일치하는 레코드 건수만큼 드리븐 테이블을 검색하면서 처리된다. 즉, 드라이빙 테이블은 한 번에 쭉 읽지만, 드리븐 테이블은 여러 번 읽는다. 특히 드리븐 테이블의 조인 조건이 인덱스를 이용할 수 없다면 드라이빙 테이블에서 일치하는 레코드 개수만큼 드리븐 테이블을 풀 테이블 스캔해야 한다.

따라서 옵티마이저는 최대한 드리븐 테이블의 검색이 인덱스를 사용할 수 있게 실행 계획을 수립한다.

 

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

 

블록 네스티드 루프 조인

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

 

  • 조인 조건이 없음 -> 카테시안 조인 수행
  • 모든 가능한 행들의 조합을 결과로 반환한다. (A x B)

 

 

  • dept_emp: 드라이빙 테이블, 조인 버퍼에 레코드를 캐시
  • employees: 드리븐 테이블, 조인 버퍼의 레코드와 결합(조인)
  • 둘 다 인덱스 레인지 스캔을 통해 WHERE 조건 검색
  • BNL 방식으로 수행될 때의 type은 ALL (Table Full Scan), index (Index Full Scan) 또는 range

실행 과정

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

조인 버퍼를 사용하는 조인(Block Nested Loop)

 

  • 조인 버퍼가 사용되는 쿼리에서는 조인의 순서가 거꾸로인 것 처럼 실행된다. 이는 4번 단계에서 employees 테이블의 결과를 기준으로 dept_emp 테이블의 결과를 결합하는 것을 의미한다.
  • 실행 계획상으로는 dept_emp 테이블이 드라이빙 테이블이 되고, employees 테이블이 드리븐 테이블이 되지만, 실제 드라이빙 테이블의 결과는 조인 버퍼에 담아두고 드리븐 테이블을 먼저 읽고 조인 버퍼에서 일치하는 레코드를 찾는 방식으로 처리된다.
  • 일반적으로 조인이 수행된 후 가져오는 결과는 드라이빙 테이블의 순서에 의해 결정되지만, 조인 버퍼가 사용되는 조인에서는 결과의 정렬 순서가 흐트러질 수 있다.

 

참고: 8.0.18 버전부터 해시 조인 알고리즘이 도입되고, 8.0.20 버전부터 블록 네스티드 루프 조인은 더이상 사용되지 않고 해시 조인 알고리즘으로 대체되었다. 그래서 8.0.20 이후의 버전이라면 실행 계획의 Extra 컬럼에 "Using Join Buffer (block nested loop)" 메세지가 표시되지 않을 수도 있다.

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

5,6 버전부터 도입된 기능

 

인덱스 컨디션 푸시다운 기능 off

ix_lastname_firstname(last_name, first_name) 인덱스 생성

 

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

 

  • last_name 조건은 ix_lastname_firstname 인덱스를 레인지 스캔으로 사용 가능
  • first_name 조건은 검색해야 할 인덱스의 범위를 줄일 수 없으며, 데이터를 모두 읽은 후 Like '%sal' 뒷부분 일치를 통한 체크 조건으로만 사용 가능

위 쿼리는 last_name 조건의 인덱스 결과 수만큼 레코드를 읽어서 first_name 조건을 적용해야 한다.

 

 

실행 계획 Extra: "Using where"

=> MySQL 서버가 스토리지 엔진에서 값을 가져온 뒤 행을 필터링 한다는 것을 의미

=> 인덱스를 사용할 수 없는 WHERE 조건에 일치하는지 검사하는 과정 ( 이 쿼리에서는 first_name LIKE '%sal'이 검사 과정에 사용 )

 

인덱스 컨디션 푸시다운이 동작하지 않을 때

 

ix_lastname_firstname(last_name, first_name)

  1. last_name='Acton': 인덱스 레인지 스캔으로 3건 검색
  2. first_name LIKE '%sal': 인덱스 사용 불가. 1번의 수행 결과인 인덱스를 가진 레코드를 테이블에서 읽어서 비교

 

이때 InnoDB 스토리지 엔진은 인덱스를 비교하는 작업을 수행하고, MySQL 엔진은 테이블의 레코드에서 first_name 조건을 비교하는 작업을 수행한다. 

2번에서 ix_lastname_firstname 인덱스의 first_name 컬럼을 이용하지 않는 이유는 5.5 버전까지 인덱스를 범위 제한 조건으로 사용하지 못하는 first_name 조건은 MySQL 엔진이 스토리지 엔진으로 아예 전달하지 않았기 때문이다.

그래서 결과적으로 불필요한 테이블 읽기 2건을 수행했다.

 

5.6 버전부터는 이렇게 인덱스를 범위 제한 조건으로 사용하지 못한다고 하더라도 인덱스에 컬럼의 조건이 있다면 모두 같이 모아서 스토리지 엔진으로 전달할 수 있게 핸들러 API가 개선되었다.

 

인덱스 컨디션 푸시다운 기능 on

 

실행 계획 Extra: "Using index condition"

 

인덱스 컨디션 푸시다운

 

first_name LIKE '%sal' 조건을 ix_lastname_firstname 인덱스의 두 번째 컬럼을 통해 비교 후 해당하는 레코드 1개만을 읽는다.

인덱스 확장 (use_index_extensions)

use_index_extensions

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

 

InnoDB 스토리지 엔진은 프라이머리 키를 클러스터링 키로 생성하며 모든 세컨더리 인덱스는 리프 노드에 프라이머리 키 값을 가진다.

 

  • 프라이머리 키: (dept_no, emp_no)
  • 세컨더리 인덱스: ix_fromdate (from_date)
  • 세컨더리 인덱스는 데이터 레코드를 찾아가기 위해 프라이머리 키 컬럼을 순서대로 포함한다.
  • x_fromdate 인덱스는 (from_date, dept_no, emp_no) 조합으로 인덱스를 생성한 것과 흡사하게 작동할 수 있다.

MySQL 서버가 버전업 되면서 옵티마이저가 세컨더리 인덱스에 프라이머리 키가 숨어있다는 것을 인지하고 실행 계획을 수립하도록 개선됐다.

 

WHERE 절에 from_date와 dept_no 컬럼 사용

 

  • key 컬럼: 최종적으로 실행 계획에 사용된 인덱스 (type이 ALL로 인덱스를 사용하지 못하면 NULL로 표시됨)
  • key_len 컬럼: 쿼리가 인덱스를 구성하는 컬럼 중에 어느 컬럼까지 사용했는지 바이트 수로 표시함
    • 19바이트 = from_date(3) + dept_no(12) + emp_no(4)
    • UTF8 문자셋을 사용해 메모리 공간 할당하는 경우 고정적으로 3바이트로 계산 ( dept_no CHAR(4) )

WHERE 절에 from_date 컬럼만 사용

 

dept_no 컬럼을 사용하지 않으니 key_len 컬럼에 from_date 컬럼을 위한 3바이트만 표시되었다.

 

정렬에 세컨더리 인덱스 사용

 

  • InnoDB의 프라이머리 키가 세컨더리 인덱스에 포함되어 있으므로 위와 같은 정렬 작업도 인덱스를 활용해서 처리되는 장점이 있다.
  • 세컨더리 인덱스를 이용해 WHERE 조건을 처리하면서 프라이머리 키의 첫 번째 컬럼에 대한 정렬도 수행한다.
  • 실행 계획 Extra 컬럼에 "Using Filesort"가 표시되지 않는 이유는 MySQL 서버가 별도의 정렬 작업 없이 인덱스의 순서대로 레코드를 읽기만 하여 "ORDER BY dept_no"를 만족했다는 것을 의미한다.

인덱스 머지 (index_merge)

인덱스를 이용해 쿼리를 실행하는 경우, 대부분 옵티마이저는 테이블별로 하나의 인덱스만 사용하도록 실행 계획을 수립한다. 쿼리에서 한 테이블에 대한 WHERE 조건이 여러 개 있더라도 하나의 인덱스에 포함된 컬럼에 대한 조건만으로 인덱스를 검색하고 나머지 조건은 읽어온 레코드에 대해서 체크하는 형태로만 사용되는 것이 일반적이다. 이처럼 하나의 인덱스만 사용해서 작업 범위를 충분히 줄일 수 있는 경우라면 테이블별로 하나의 인덱스만 활용하는 것이 효율적이기 때문이다.

하지만 쿼리에 사용된 각각의 조건이 서로 다른 인덱스를 사용할 수 있고 그 조건을 만족하는 레코드의 건수가 많을 것으로 예상될 때 MySQL 서버는 인덱스 머지 실행 계획을 선택한다.

 

인덱스 머지 실행 계획

하나의 테이블에 대해 2개 이상의 인덱스를 이용해 쿼리를 처리

  • index_merge_intersection
  • index_merge_sort_union
  • index_merge_union

3가지 최적화는 모두 여러 개의 인덱스를 통해 결과를 가져오지만 결과의 병합 방식이 다르다.

index_merge 옵션은 위의 최적화 옵션들을 한 번에 모두 제어할 수 있는 옵션이다.

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

employees 테이블

  • PRIMARY( emp_no )
  • ix_firstname( first_name )
mysql> EXPLAIN SELECT *
	FROM employees
	WHERE first_name='Georgi'AND emp_no BETWEEN 10000 AND 20000;

 

WHERE 절의 두 조건 모두 인덱스를 사용할 수 있기 때문에 옵티마이저는 위 쿼리를 처리하기 위해 ix_firstname 과 PRIMARY 키를 모두 사용하기로 결정한다.

 

 

실행 계획 Extra: "Using intersect"

=> 여러 개의 인덱스를 각각 검색해서 그 결과의 교집합을 반환했음을 의미

=> WHERE 절에 사용된 2개 이상의 조건이 각각의 인덱스를 사용하면서 AND 연산자로 연결된 경우 사용

 

first_name 컬럼의 조건과 emp_no 컬럼의 조건 중 하나라도 충분히 효율적으로 쿼리를 처리할 수 있었다면 옵티마이저는 2개의 인덱스를 모두 사용하는 실행 계획을 사용하지 않았을 것이다. 

즉, 옵티마이저는 각각의 조건에 일치하는 레코드 건수를 예측해 본 결과, 두 조건 모두 인덱스를 사용하더라도 많은 레코드를 가져와야 한다는 것을 알게 된 것이다.

 

 

인덱스 머지 실행 계획이 아니었다면 다음 둘 중 하나 선택

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

 

 

실제 두 조건을 모두 만족하는 레코드 건수가 14건 밖에 안된다.

  • 1번의 경우, 253번의 페이지 읽기 중 14번만 의미 있는 작업
  • 2번의 경우, 10,000번의 페이지 읽기 중 14번만 의미 있는 작업

두 작업 모두 비효율이 매우 큰 상황이어서 옵티마이저는 각 인덱스를 검색해 두 결과의 교집합(intersection)만 반환한 것이다.

 

index_merge_intersection 설정

ix_firstname 인덱스는 프라이머리 키인 emp_no 컬럼을 자동으로 포함하고 있어서 ix_firstname 만 사용하는 것이 더 성능이 좋을 것이라 생각할 수도 있다. 그러면 index_merge_intersection 최적화를 비활성화 하면 된다.

 

글로벌, 세션 모두 설정 가능

 

 

index_merge_intersection 최적화를 off 하여 세컨더리 인덱스만을 사용하였다.

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

실행 계획 Extra: "Using union"

=> 여러 개의 인덱스를 각각 검색해서 그 결과의 합집합(병합)을 반환했음을 의미

=> WHERE 절에 사용된 2개 이상의 조건이 각각의 인덱스를 사용하면서 OR 연산자로 연결된 경우 사용

 

employees 테이블

  • ix_firstname ( first_name )
  • ix_hiredate ( hire_date )
  • PRIMARY ( emp_no )
mysql> SELECT *
	FROM employees
	WHERE first_name='Matt' OR hire_date='1987-03-31';

 

위 쿼리는 2개의 조건이 OR 로 연결되어 있고 각각의 인덱스를 사용할 수 있다.

-> index_merge_union 최적화 사용 가능

 

 

만약 두 조건을 동시에 만족하는 사원이 있다면 ix_firstname, ix_hiredate 각 인덱스를 검색한 결과에 모두 포함되어 있을 것이다. 하지만 쿼리의 결과에 같은 레코드가 2번 출력되지는 않는다. 

중복을 제거하기 위해서는 정렬 작업이 필요했을텐데 실행 계획에 정렬했다는 표시는 없다.

 

인덱스 머지 최적화의 'UNION' 알고리즘 작동 방식

 

MySQL 서버는 first_name 조건을 검색한 결과와 hire_date 조건을 검색한 결과가 프라이머리 키로 이미 정렬되어 있다는 사실을 알고 있다. 그래서 두 집합에서 하나씩 가져와서 서로 비교하며 프라이머리 키 값이 중복된 레코드를 정렬 없이 걸러낼 수 있다.

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

 

참고: SQL 문장의 AND 연산자와 OR 연산자

  • AND로 연결된 경우: 두 조건 중 하나라도 인덱스를 사용할 수 있으면 인덱스 레인지 스캔으로 처리
  • OR로 연결된 경우: 두 조건 중 하나라도 제대로 인덱스를 사용하지 못하면 항상 풀 테이블 스캔으로만 처리

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

인덱스 머지 최적화의 'Union' 알고리즘은 두 결과 집합의 중복을 제거하기 위해 정렬이 필요한데 MySQL 서버가 별도로 정렬을 수행하지 않는다.

 

만약 인덱스 머지 작업 중에 결과의 정렬이 필요한 경우 'Sort union' 알고리즘을 사용한다.

 

employees 테이블

  • ix_firstname ( first_name )
  • ix_hiredate ( hire_date )
  • PRIMARY ( emp_no )
mysql> EXPLAIN
	SELECT * FROM employees
	WHERE first_name='Matt'
	  OR hire_date BETWEEN '1987-03-01' AND '1987-03-31';

 

 

첫 번째 쿼리는 결과가 emp_no로 정렬되어 출력되지만, 두 번째 쿼리의 결과는 emp_no 컬럼으로 정렬되어 있지 않다.

따라서 두 결과에 OR 연산을 적용할 때 중복 제거를 위해 우선순위 큐를 사용할 수 없다.

그래서 MySQL 서버는 각 집합을 emp_no 컬럼으로 정렬한 다음 중복 제거를 수행한다.

 

 

실행 계획 Extra: "Using sort_union"

=> Using union과 같은 작업을 수행하지만 중복 제거를 위해 강제로 정렬을 수행했음을 의미

세미 조인 (semijoin)

서브쿼리를 사용하여 서브쿼리에 존재하는 데이터만 메인쿼리에서 추출하는 조인

다른 테이블과 실제 조인을 수행하지는 않고, 다른 테이블에서 조건에 일치하는 레코드가 있는지 없는지만 체크

 

세미 조인 최적화 도입 전

mysql> SET SESSION optimizer_switch='semijoi=off';

 

 

위 쿼리를 보면 dept_emp 테이블을 조회하는 서브쿼리가 먼저 실행되고 메인쿼리의 employees 테이블에서 일치하는 레코드만 검색할 것으로 기대할 것이다.

하지만 MySQL 서버는 employees 테이블을 풀 스캔하면서 한 건 한 건 서브쿼리의 조건에 일치하는지 비교하는 방식으로 동작한다. 실행 계획을 보면 서브쿼리 결과인 57건만 읽으면 될 쿼리를 전체 레코드 30만 건을 읽어서 처리할 것을 알 수 있다.

 

 

세미 조인(Semi-join) 형태 쿼리에 대한 최적화

"= (subquery)", "IN (subquery)"

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

안티 세미 조인(Anti Semi-join) 형태 쿼리에 대한 최적화

"<> (subquery)", "NOT IN (subquery)"

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

 

세미 조인 최적화 전략 (8.0 버전부터)

  • Table Pull-out
  • Duplicate Weed-out ( materialization )
  • First Match ( firstmatch )
  • Loose Scan ( loosescan )
  • Materialization ( materialization  )

쿼리에 사용되는 테이블과 조인 조건의 특성에 따라 선별적으로 사용된다.

괄호 안의 옵티마이저 옵션을 통해 사용 여부를 결정할 수 있다.

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

 

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

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

서브쿼리 최적화가 도입되기 전에 수동으로 쿼리를 튜닝하던 대표적인 방법

세미 조인의 서브쿼리에 사용된 테이블을 아우터 쿼리로 끄집어낸 후에 조인 쿼리로 재작성한다.

  • 세미 조인 서브쿼리에서만 사용 가능
  • 서브쿼리 부분이 UNIQUE 인덱스나 프라이머리 키 룩업으로 결과가 1건인 경우에만 사용 가능
  • Table pullout 이 적용되도 재작성 전의 쿼리에서 가능했던 최적화 방법 사용 가능하기 때문에 Table pullout 최적화를 최대한 적용하자.
  • 만약 서브쿼리의 모든 테이블을 아우터 쿼리로 끄집어 낼 수 있다면 서브쿼리 자체가 없어진다.
  • MySQL에서는 최대한 서브쿼리를 풀어서 사용하라는 튜닝 가이드가 많은데 Table pullout 최적화가 가이드대로 실행하는 방법이다. 이제부터는 직접 서브쿼리를 조인으로 풀어서 사용할 필요가 없다.

 

Table pullout 최적화 사용 여부 확인

  • 실행 계획에서 해당 테이블들의 id 컬럼이 같은지 다른지 (+Extra 컬럼에 아무것도 출력되지 않는 경우)
  • EXPLAIN 명령을 실행한 직후 SHOW WARNINGS 명령으로 옵티마이저가 재작성(Re-Write)한 쿼리 확인

 

예) 부서 번호가 'd009'인 부서에 소속된 모든 사원을 조회하는 쿼리

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

 

실행 계획에서 확인

 

  • id 컬럼 값이 모두 1 => 두 테이블이 서브쿼리 형태가 아닌 조인으로 처리됐음을 의미
  • Table pullout 최적화는 별도로 실행 계획의 Extra 컬럼에 "Using table pullout" 같은 문구가 출력되지 않는다.

 

SHOW WARNINGS 명령으로 확인

IN (subquery) 형태가 사라지고 JOIN 으로 쿼리가 재작성된 것을 확인할 수 있다.

퍼스트 매치 (firstmatch)

IN (subquery) 형태의 세미 조인을 EXISTS (subquery) 형태[IN-to-EXISTS 변환]로 튜닝한 것과 비슷한 방법으로 실행

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

 

예) 이름이 'Matt' 인 사원 중에서 1995년 1월 1일부터 30일 사이에 직급이 변경된 적 있는 사원을 조회하는 쿼리

 

 

  • id 컬럼 값이 모두 1 => 두 테이블이 서브쿼리 형태가 아닌 조인으로 처리됐음을 의미
  • Extra 컬럼: "FirstMatch(e)" => employees 테이블의 레코드에 대해 titles 테이블에 일치하는 레코드 1건만 찾으면 더이상의 titles 테이블 검색을 하지 않음. 의미상으로 EXISTS (subquery) 와 동일하게 처리
  • FisstMatch는 서브쿼리가 아니라 조인으로 풀어서 실행하면서 일치하는 첫번째 레코드만 검색하는 최적화를 실행한다.

 

FirstMatch 최적화 작동 방식

  1. employees 테이블에서 first_name 컬럼 값이 'Matt' 인 사원의 정보를 ix_firstname 인덱스를 이용해 레인지 스캔으로 읽는다. (결과: 왼쪽의 employees 테이블)
  2. 1번에서 찾은 사원의 emp_no 컬럼을 기준으로 첫 번째 레코드부터 titles 테이블과 조인해서 "from_date BETWEEN '1995-01-01' AND '1995-01-30'" 조건을 만족하는 레코드를 검색한다.
  3. 243075번 사원이 from_date 조건을 만족하는 첫 번째 레코드이므로 243075번 사원에 대해서는 더이상 titles 테이블을 검색하지 않고 사원 번호가 243075인 레코드를 즉시 결과로 반환한다. ( from_date 컬럼 기준으로 오른쪽, title 컬럼은 읽지 않음)
  4. 같은 방식으로 1번 결과 테이블 끝까지 순차적으로 탐색

 

IN-to-EXISTS 변환과 비교하여 First Match 최적화의 장점

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

루스 스캔 (loosescan)

세미 조인 서브쿼리를 처리할 때, 인덱스를 사용하는 GROUP BY 최적화 방법인 루스 인덱스 스캔("Using index for group-by")과 비슷한 읽기 방식을 사용한다.

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

루스 스캔 최적화를 사용할 수 있는 서브쿼리

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

INDEX ( keypart1, keypart2 )

 

예) dept_emp 테이블에 존재하는 모든 부서 번호에 대한 부서 정보를 읽어오는 쿼리

 

departments 테이블

  • 전체 레코드 건수: 9건

dept_emp 테이블

  • PRIMARY (dept_no, emp_no)
  • 전체 레코드 건수: 33만 건
mysql> EXPLAIN
	SELECT * FROM departments d 
	WHERE d.dept_no IN (SELECT de.dept_no FROM dept_emp de);

 

 

dept_emp 테이블을 dept_no 컬럼으로 그루핑하면 최대 9건 밖에 없기 때문에 프라이머리 키를 루스 인덱스 스캔으로 유니크한 dept_no만 읽으면 중복까지 제거하면서 효율적으로 서브쿼리 부분을 처리할 수 있다. 

 

LooseScan 최적화 작동 방식

 

  1. 서브쿼리의 dept_emp 테이블이 드라이빙 테이블로 실행되며 프라이머리 키의 첫 번째 컬럼 dept_no를 유니크하게 한 건씩 읽는다.
  2. 반환된 dept_no 컬럼 값을 가진 departments 레코드가 결과 값이 된다. (IN 연산)

 

  • id 컬럼 값이 모두 1 => 두 테이블이 서브쿼리 형태가 아닌 조인으로 처리됐음을 의미
  • Extra 컬럼: "Using index; LooseScan"

 

참고: 위 예제 쿼리는 LooseScan 최적화 방식으로 처리하기 좋은 케이스인데 MySQL 8.0에서는 Semi-join Materialization 알고리즘으로 실행하려고 한다. 그래서 옵티마이저가 루스 스캔 최적화를 사용하도록 하기 위해 일시적으로 옵티마이저 스위치를 변경한 후 테스트 했다.

구체화(Materialization)

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

  • IN(subquery)에서 서브 쿼리는 상관 서브쿼리(Correlated subquery)가 아니어야 한다.
  • 서브쿼리가 GROUP BY나 집합 함수를 사용해도 구체화를 사용할 수 있다.

 

예) 1995년 1월 1일 조직이 변경된 사원들의 목록을 조회하는 쿼리

IN (subquery) 포맷의 세미 조인 사용

 

FirstMatch 최적화를 사용하면 employees 테이블에 대한 조건이 서브쿼리 이외에는 아무것도 없기 때문에 employees 테이블을 풀 스캔해야 할 것이다. (작업 범위 결정 조건이 없음) -> 성능 향상에 도움 X

 

이런 형태의 쿼리를 위해 서브쿼리 구체화(Subquery Materialization) 최적화가 도입되었다.

 

 

  • 쿼리에서 사용하는 테이블은 2개인데 실행 계획은 3개 라인 출력 -> 어디선가 임시 테이블 생성
  • dept_emp 테이블을 읽는 서브쿼리가 먼저 실행되어 그 결과로 임시 테이블(<subquery2>)이 생성된다.
  • 최종적으로 임시 테이블(<subquery2>)과 employees 테이블을 조인해서 결과를 반환한다.

 

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

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

 

참고: 세미 조인이 아닌 서브쿼리의 최적화에서도 구체화를 이용한 최적화가 사용될 수 있다. 그러나 optimizer_switch 시스템 변수에서 materialization 옵션이 OFF로 비활성화된다면 세미 조인이 아닌 서브쿼리 최적화에서도 구체화를 이용한 최적화를 적용될 수 없다.

중복 제거 (Duplicated Weed-out)

세미 조인 서브쿼리를 일반적인 INNER JOIN 쿼리로 바꿔서 실행하고 마지막에 중복된 레코드를 제거하는 방법

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

 

예) 급여가 150,000 이상인 사원들의 정보를 조회하는 쿼리

 

salaries 테이블

  • PRIMARY ( emp_no, from_date)
  • emp_no: 사원 번호
  • from_date: 급여가 변경된 날
  • salary: 급여

세미 조인 서브쿼리

 

salaries 테이블의 프라이머리 키가 (emp_no, from_date) 이므로 salary가 150000 이상인 레코드를 조회하면 서브쿼리 결과에 중복된 emp_no가 발생할 수 있다. 

 

GROUP BY 추가

 

위 쿼리는 세미 조인 서브쿼리와 동일한 결과를 얻을 수 있다.

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

 

Duplicate Weedout 최적화 작동 방식

 

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

 

  • id 컬럼 값이 모두 1 => 두 테이블이 서브쿼리 형태가 아닌 조인으로 처리됐음을 의미
  • Extra 컬럼에 "Duplicate Weedout" 라는 문구 대신 "Start temporary"와 "End temporary" 가 표시된다.
  • 조인을 수행하는 작업과 임시 테이블로 저장하는 작업은 반복적으로 실행되며, 반복 과정이 시작되고 끝나는 실행 라인에 Start / End temporary 문구가 표시된다. => Duplicate Weedout 최적화

컨디션 팬아웃(condition_fanout_filter)

조인에서 테이블의 순서는 쿼리의 성능에 매우 큰 영향을 미친다.

옵티마이저는 여러 테이블이 조인되는 경우 가능하다면 일치하는 레코드 건수가 적은 순서대로 조인을 실행한다.

  • condition_fanout_filter 최적화 기능을 활성화하면 옵티마이저는 더 정교한 계산을 거쳐서 실행 계획을 수립한다. 그에 따라 쿼리의 실행 계획 수립에 더 많은 시간과 컴퓨팅 자원을 사용하게 된다.
  • 쿼리가 간단하고 8.0 이전 버전에서도 쿼리 실행 계획이 잘못된 선택을 한 적이 별로 없다면 크게 도움이 되지 않을 수 있다.
  • MySQL 서버가 처리하는 쿼리의 빈도가 매우 높다면 실행 계획 수립에 추가되는 오버헤드가 크게 보일 수 있으므로 업데이트 실행 전에 성능 테스트를 진행할 것

 

 

condition_fanout_filter = OFF

 

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

 

condition_fanout_filter = ON

 

  • employees 테이블의 rows 컬럼 값은 233으로 동일하지만 filtered 컬럼의 값이 100%가 아닌 23.2%
  • condition_fanout_filter 최적화가 활성화되면서 옵티마이저는 인덱스를 사용할 수 있는 first_name 조건 외에 나머지 조건(hire_date 조건)에 대해서도 얼마나 조건을 충족할지를 고려했다는 뜻이다.
  • condition_fanout_filter 최적화가 비활성화 된 경우에는 employees 테이블에서 모든 조건을 충족하는 데이터가 233건일 것으로 예측한 반면, condition_fanout_filter 최적화가 활성화된 경우에는 54건(233 * 0.2320)만 충족할 것이라고 예측했다.

filtered 컬럼 값 예측

MySQL 8.0 에서 condition_fanout_filter 최적화가 활성화되면 다음과 같은 쿼리에서 WHERE 조건을 만족하는 레코드의 비율을 계산할 수 있다.

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

예제 쿼리가 실제 실행될 때는 first_name 조건만을 위해 ix_firstname 인덱스를 사용한다.

하지만 실행 계획을 수립할 때는 first_name 컬럼의 인덱스를 이용해 first_name 조건에 일치하는 레코드 건수(rows)와 hire_date 컬럼의 조건을 만족하는 레코드의 비율(filtered)까지 예측한다.

hire_date 컬럼의 인덱스가 없다면 다른 컬럼의 인덱스(ix_firstname)를 이용해 hire_date 컬럼 값의 분포도를 살펴보고 filtered 컬럼 값을 예측하는 것이다.

 

참고: 옵티마이저가 실행 계획을 수립할 때 다음의 순서대로 사용 가능한 방식을 선택한다.

실행 계획에는 우선순위가 높은 방법을 통한 예측이 표시된다.

  1. 레인지 옵티마이저(Range optimizer): 실제 인덱스 데이터를 소량 읽고 레코드 건수를 예측하는 방식. 인덱스를 이용해서 쿼리가 실행될 수 있을 때만 사용 가능
  2. 히스토그램
  3. 인덱스 통계
  4. 추측(Guesstimates3)

파생 테이블 머지 (derived_merge)

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

 

 

  • FROM 절에 사용된 서브쿼리 => 파생 테이블(Derived Table)
  • employees 테이블의 select_type 컬럼 값: DERIVED

실행 과정

  1. employees 테이블에서 first_name 컬럼 값이 'Matt'인 레코드만 읽어서 임시 테이블(<derived2>) 생성
  2. 그 임시 테이블을 다시 읽어 hired_date 컬럼 값이 '1986-04-03'인 레코드만 반환

 

MySQL 서버가 내부적으로 임시 테이블을 생성하고 서브쿼리의 결과를 INSERT 한다. 그리고 다시 임시 테이블을 읽으므로 레코드를 복사하고 읽는 오버헤드가 발생한다.

내부 임시 테이블은 처음에는 메모리에 생성되지만, 레코드 건수가 많아지면 디스크로 기록돼야 하기 때문에 쿼리의 성능이 느려질 것이다.

 

파생 테이블 머지 (derived_merge)

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

 

derived_merge 최적화 옵션 활성화

 

select_type 컬럼이 DERIVED 였던 라인이 없어지고, 서브쿼리 없이 employees 테이블을 조회하는 형태의 단순 실행 계획으로 바뀌었다.

 

병합된 쿼리 확인 (SHOW WARNING 명령)

 

원래 DBA가 수작업으로 처리하던 작업을 옵티마이저가 처리할 수 있게 되었다.

그러나 다음의 경우 derived_merge 옵션을 적용할 수 없기 때문에 수동으로 서브쿼리를 외부 쿼리로 병합하여 작성해야 한다.

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

인비저블 인덱스 (use_invisible_indexes)

인덱스의 가용 상태를 제어할 수 있는 기능

8.0 이전 버전까지는 인덱스가 존재하면 옵티마이저는 항상 실행 계획을 수립할 때 해당 인덱스를 검토하고 사용했다.

8.0 버전부터는 인덱스를 삭제하지 않으면서 해당 인덱스를 사용하지 못하게 제어하는 기능을 제공한다.

 

1. 인덱스 설정

ALTER TABLE ... ALTER INDEX ... [ VISIBLE | INVISIBLE ]

 

 

2. 옵티마이저 설정

use_invisible_indexes 옵티마이저 옵션이 on일 경우, 옵티마이저가 INVISIBLE 상태인 인덱스도 볼 수 있다. (기본값:off)

 

mysql> SET optimizer_switch='use_invisible_indexes=on';

스킵 스캔 (skip_scan)

인덱스의 핵심은 값이 정렬되어 있다는 것이며, 이로 인해 인덱스를 구성하는 컬럼의 순서가 매우 중요하다.

쿼리의 WHERE 절에 인덱스 첫 번째 컬럼에 대한 조건이 없다면 인덱스를 활용할 수 없다.

인덱스 스킵 스캔은 제한적이긴 하지만 인덱스의 이런 제약을 뛰어넘을 수 있는 최적화 기법이다.

 

employees 테이블

  • ix_gender_birthdate (gender, birthdate)

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

 

 

인덱스를 사용할 수 없는 첫 번째 쿼리를 위해 주로 birth_date 컬럼부터 시작하는 인덱스를 새로 생성해왔다.

8.0 버전부터 인덱스 스킵 스캔 최적화가 도입되면서 인덱스의 선행 컬럼이 조건절에 사용되지 않더라도 후행 컬럼의 조건만으로도 인덱스를 이용한 쿼리 성능 개선이 가능하다.

 

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

 

해시 조인 (hash_join)

8.0.18 버전부터 지원된 기능

해시 조인은 네스티드 루프 조인(Nested Loop Join)보다 항상 빠른 것은 아니다.

 

중첩(네스티드) 루프 조인과 해시 조인의 처리 성능

 

  • 화살표의 길이: 전체 쿼리의 실행 시간
  • A: MySQL 서버가 첫 번째 레코드를 찾아낸 시점
  • B: MySQL 서버가 마지막 레코드를 찾아낸 시점

네스티드 루프 조인과 해시 조인은 똑같은 시점에 시작했지만 해시 조인이 먼저 끝난 것을 확인할 수 있다.

 

해시 조인은 첫 번째 레코드를 찾는데는 네스티드 루프 조인보다 느리고, 최종 레코드를 찾는데는 네스티드 루프 조인보다 빠르다.

즉, 해시 조인 쿼리는 Best Throughput 전략에 적합하며, 네스티드 루프 조인은 Best Response-time 전략에 적합하다.

  • 일반적인 웹 서비스: 온라인 트랜잭션(ONTP) 서비스. 응답 속도가 더 중요 (네스티드 루프 조인)
  • 데이터 분석 서비스: 사용자 응답 시간보다 전체적인 처리 소요 시간이 중요 (해시 조인)

MySQL 서버는 범용 RDBMS로, 여기서 범용이란 온라인 트랜잭션 처리를 위한 데이터베이스 서버를 지칭한다.

대용량 데이터 분석을 위해 MySQL 서버를 거의 사용하지 않을 것이므로 MySQL 서버는 응답 속도와 스루풋 중 응답 속도에 집중해서 최적화를 실행한다.

MySQL 서버는 주로 조인 조건의 컬럼이 인덱스가 없거나, 조인 대상 테이블 중 일부의 레코드 건수가 매우 적은 경우 등에 대해서만 해시 조인 알고리즘을 사용하도록 설계돼 있다.

즉, MySQL 서버의 해시 조인 최적화는 네스티드 루프 조인이 사용되기에 적합하지 않은 경우를 위한 차선책(Fallback strategy) 같은 기능으로 생각하는 것이 좋다.

 

해시 조인과 블록 네스티드 루프 조인

해시 조인 기능이 없을 때는 조인 조건이 좋지 않은 경우 블록 네스티드 루프 조인(Block Nested Loop Join)을 사용했다.

  • block: join_buffer_size 시스템 변수로 크기를 조정할 수 있는 메모리 공간
  • 문제점: 조인 대상 테이블의 레코드 크기가 조인 버퍼보다 큰 경우 드라이빙 테이블을 여러 번 반복해서 스캔해야 한다.

8.0.18,19 버전

  • 동등 조인(Equi-Join) -> 해시 조인 사용
  • 안티조인이나 세미 조인 -> 블록 네스티드 루프 조인

8.0.20 버전부터

  • 네스티드 루프 조인을 사용할 수 없는 경우, 항상 해시 조인 사용
  • block_nested_loop 같은 optimizer_switch 또는 BNL 과 NO_BNL 같은 힌트들도 블록 네스티드 루프가 아닌 해시 조인을 유도하는 목적으로 사용됨

 

 

  • employees 테이블 Extra 컬럼: "Using join buffer (hash join)"
  • IGNORE INDEX 힌트는 네스티드 루프 조인이 사용되지 못하게 하기 위해 사용되었다.
  • 결과적으로 옵티마이저는 적절한 인덱스가 없어 해시 조인을 사용했다.

해시 조인 처리 과정

  1. 빌드 단계 (Build-phase): 조인 대상 테이블 중 레코드 건수가 적어서 해시 테이블로 만들기 용이한 테이블(빌드 테이블)에 대한 해시 테이블을 메모리에 생성. 
  2. 프로브 단계 (Probe-phase): 나머지 테이블(프로브 테이블)의 레코드를 읽어서 해시 테이블의 일치 레코드를 찾는 과정

실행 계획에서는 빌드 테이블과 프로브 테이블을 식별하기 어렵다.

=> EXPLAIN FORMAT=TREE 명령 또는 EXPLAIN ANALYZE 명령

 

 

해시 조인을 위한

  • 빌드 테이블: dept_emp (Hash -> Table scan on de)
  • 프로브 테이블: employees

 

해시 조인을 메모리에서 모두 처리 가능한 경우

해시 조인

 

  1. dept_emp 테이블의 레코드를 읽어 메모리에 해시 테이블 생성
  2. employees 테이블을 스캔하면서 해시 테이블에서 레코드를 찾아서 조인

 

해시 테이블이 조인 버퍼 메모리보다 큰 경우

해시 테이블을 메모리에 저장할 때 MySQL 서버는 join_buffer_size 시스템 변수로 크기를 제어할 수 있는 조인 버퍼를 사용한다.

조인 버퍼의 기본 크기는 256KB인데 해시 테이블의 레코드 건수가 많아 조인 버퍼의 공간이 부족한 경우, 빌드 테이블과 프로브 테이블을 적당한 크기의 청크로 분리한 다음, 청크별로 같은 방식으로 해시 조인을 처리한다.

 

해시 조인 1차 처리

 

  • 빌드 테이블 청크: dept_emp 테이블의 레코드를 저장해둔 공간
  • 프로브 테이블 청크: employees 테이블의 레코드를 저장해둔 공간

해시 조인 2차 처리

 

디스크에 저장된 청크 개수만큼 이 과정을 반복 처리하여 완성된 조인 결과를 만들어낸다.

청크 단위로 조인을 수행하기 위해 MySQL 서버는 2차 해시 함수를 이용해 "빌드 테이블"과 "프로브 테이블"을 동일 개수의 청크로 쪼개어 디스크로 저장한다.

 

해시 조인 알고리즘

옵티마이저는 빌드 테이블의 크기에 따라

  • 메모리에서 모두 처리 가능한 경우 -> 클래식 해시 조인 (Classic hash join) 알고리즘
  • 해시 테이블이 조인 버퍼 메모리보다 큰 경우 -> 그레이스 해시 조인 (Grace hash join) 알고리즘

을 사용한다.

해시 조인에서 해시 키를 만들 때는 xxHash64 해시 함수를 사용한다.

인덱스 정렬 선호 (prefer_ordering_index)

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

 

 

이 쿼리는 대표적으로 다음 2가지 실행 계획을 선택할 수 있다.

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

일반적으로는 hire_date 컬럼의 조건에 부합되는 레코드 건수가 많지 않다면 1번이 효율적일 것이다.

그런데 가끔 옵티마이저가 예제 쿼리의 실행 계획처럼 2번을 선택하는 경우가 있다. 

실행 계획에서는 PRIMARY 키를 풀 스캔하면서 hire_date 컬럼의 조건을 체크하고 있다. 체크해야 하는 레코드 건수가 상당히 많음에도 정렬된 인덱스 활용으로 실행 계획이 수립되는 것은 옵티마이저의 실수일 가능성이 높다.

 

8.0.20 버전까지는 옵티마이저의 이런 실수를 방지하기 위해 특정 인덱스(ORDER BY를 위한 인덱스)를 사용하지 못하도록 "IGNORE INDEX" 힌트를 사용하기도 했다.

8.0.21 버전부터는 옵티마이저가 ORDER BY를 위한 인덱스에 너무 가중치를 부여하지 않도록 prefer_ordering_index 옵티마이저 옵션(기본값:ON)이 추가되었다.

옵티마이저가 자주 위 같은 실수를 한다면 OFF로 변경하면 된다.

 

조인 최적화 알고리즘

테이블의 개수가 많아지면 최적화된 실행 계획을 찾는 것이 상당히 어려워지고, 하나의 쿼리에서 조인되는 테이블의 개수가 많아지면 실행 계획을 수립하는 데만 몇 분이 걸릴 수도 있다.

 

조인 쿼리의 실행 계획 최적화를 위한 알고리즘

  • Exhaustive 검색 알고리즘
  • Greedy 검색 알고리즘

다음과 같이 간단히 4개의 테이블을 조인하는 쿼리 문장이 조인 옵티마이저 알고리즘에 따라 어떻게 처리되는지 알아보자.

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

Exhaustive 검색 알고리즘

MySQL 5.0과 그 이전 버전에서 사용하던 조인 최적화 기법

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

 

 

FROM 절에 명시된 테이블이 20개라면 이 방법으로 처리했을 때 가능한 조인 조합은 모두 20! 개가 된다.

Exhaustive 검색 알고리즘에서는 테이블이 10개만 넘어도 실행 계획을 수립하는 데만 몇 분이 걸린다.

Greedy 검색 알고리즘

Exhaustive 검색 알고리즘의 시간 소모적인 문제점을 해결하기 위해 MySQL 5.0부터 도입된 최적화 기법

 

optimizer_search_dept=2 로 가정

 

  1. 전체 N개의 테이블 중에서 optimizer_search_depth 시스템 변수에 정의된 개수의 테이블로 가능한 조인 조합을 생성
  2. 1번에 생성된 조인 조합 중에서 최소 비용의 실행 계획 하나를 선정
  3. 2번에서 선정된 실행 계획의 첫 번째 테이블을 "부분 실행 계획(그림에서는 실행 계획 완료 대상으로 표시)의 첫 번째 테이블로 선정
  4. 3번에서 선택된 테이블을 제외하고 N-1개의 테이블로 1번과 같은 과정 수행
  5. 4번에서 생성된 조인 조합들을 하나씩 3번에서 생성된 "부분 실행 계획"에 대입해 실행 비용을 계산
  6. 5번의 비용 계산 결과, 최적의 실행 계획에서 두 번째 테이블을 3번에서 생성된 "부분 실행 계획"의 두 번째 테이블로 선정
  7. 남은 테이블이 모두 없어질 때 까지 4~6번 과정을 반복 실행하면서 "부분 실행 계획"에 테이블의 조인 순서를 기록
  8. 최종적으로 "부분 실행 계획"이 테이블의 조인 순서로 결정됨

Greedy 검색 알고리즘은 optimizer_search_depth 시스템 변수에 설정된 값에 따라 조인 최적화의 비용이 상당히 줄어들 수 있다. 

 

조인 최적화를 위한 시스템 변수

  • optimizer_prune_level: Heuristic 검색이 작동하는 방식을 제어하는 시스템 변수 (기본값: 1)
    • 어떤 검색 알고리즘을 사용해도 옵티마이저는 여러 테이블의 조인 순서를 결정하기 위해 많은 조인 경로를 비교한다.
    • Heuristic 검색은 다양한 조인 순서의 비용을 계산하는 도중 이미 계산했던 조인 순서의 비용보다 큰 경우에는 끝까지 비교할 필요없이 그만둔다.
    • 0: Heuristic 최적화를 사용하지 않음
    • 1: 조인 순서 최적화에 경험 기반의 Heuristic 알고리즘 사용 (권장)
  • optimizer_search_depth: Greedy 검색 알고리즘과 Exhaustive 검색 알고리즘 중 어떤 알고리즘을 사용할지 결정하는 시스템 변수 (기본값: 62)
    • 0: Greedy 검색을 위한 최적의 조인 검색 테이블의 개수를 옵티마이저가 자동으로 결정
    • 1~62: Greedy 검색 대상을 지정된 개수로 한정해서 최적의 실행 계획 산출
    • optimizer_search_depth 설정 값과 쿼리의 조인 테이블 개수에 따라 Exhaustive 검색만 사용되거나 Greedy 검색과 Exhaustive 검색이 동시에 사용된다. 

 

MySQL 조인 최적화나 딕셔너리 정보 검색 성능이 많이 개선되면서 Heuristic 검색이 작동할 때 optimizer_search_depth 변수 값은 실행 계획을 수립하는 시간에 큰 영향을 주지 않는다.

 

optimizer_search_depth 값 변화에 따른 실행 계획 수립 소요 시간

 

  • optimizer_prune_level 세션 변수는 0으로 고정 (Heuristic 최적화 OFF)
  • optimizer_search_depth=15일 때부터는 너무 많은 시간이 소요되어 표기하지 않음
  • optimizer_prune_level 을 0으로 설정하면 optimizer_search_depth 값 변화에 따라 실행 계획 수립에 소요되는 시간이 급증하고 있다.
  • optimizer_prune_level 시스템 변수의 기본 값이 1이므로 조인 관련된 시스템 변수의 조정은 더이상 필요하지 않아 보인다.

'Book > Real MySQL 8.0 上' 카테고리의 다른 글

10. 실행 계획-1  (0) 2023.01.02
09. 옵티마이저와 힌트-3  (0) 2022.12.29
09. 옵티마이저와 힌트-1  (0) 2022.12.18
08. 인덱스-2  (0) 2022.12.14
08. 인덱스-1  (0) 2022.12.11

'Book/Real MySQL 8.0 上' Related Articles

티스토리툴바