옵티마이저 2
Post

옵티마이저 2

18/04/2023 03/05/2023
Preview Image
7

고급 최적화

옵티마이저 옵션은 조인 관련된 옵티마이저 옵션과 옵티마이저 스위치로 구분

1
2
3
4
5

-- // 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,...';

SET_VAR 옵티마이저 힌트를 이용해 현재 쿼리에만 설정 가능하다.

1
2

mysql> SELECT /** SET_VAR(optimizer_switch='condition_fanout_filter=off') */
.... from ...

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

Multi-Range Read 이라고 함

네스티드 루프 조인 (Nested Loop Join) 드라이빙 테이블(조인에서 제일 먼저 읽는 테이블)의 레코드를 한 건 읽어서 드리븐 테이블 일치하는 레코드를 찾아서 조인을 수행

단점 : 조인은 MySQL 엔진이 처리하지만 드리븐 테이블에 데이터 조회는 스토리지 엔진이 담당함으로 아무런 최적화를 수행하지 못함

이런 단점을 보안을 위해 조인 대상 테이블 중 하나로 부터 레코드를 읽어서 버퍼링하고 조인 버퍼가 가득 차면 비로서 MySQL 엔진은 스토리지 엔진에 한 번 요청한다. 이런 방식을 MRR(Multi-Range Read) 라고 한다. 이를 응용한 BKA(Batched Key Access) 조인이 있지만 성능이 좋지 않음

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

네스티드 루프 조인과 블록 네스티드 루프 조인의 가장 큰 차이는 조인 버퍼를 사용되는지 여부와 조인 쿼리 실행 계획에 Extra 컬럼에 Using Join buffer 가 나오면 조인 버퍼를 쓴 것이다.

드라이빙 테이블은 한 번에 쭉 읽지만, 드리븐 테이블은 여러 번 읽는다 그래서 드리븐 테이블 조인 조건이 인덱스를 이용할 수 없다면 풀 스캔 하게 된다.

옵티마이저는 이렇게 풀 스캔을 할 경우 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시한 후 드리븐 테이블과 메모리 캐시를 조인하는 형태로 처리 이를 조인 버퍼라고 한다.

조인 버퍼가 사용되는 조인에서는 결과의 정렬 순서가 흐트러질 수 있다.

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

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

1

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

기존의 경우 first_name 조건은 스토리지 엔진으로 전달되지 않았지만 MySQL 5.6 버전 부터는 전달함

Acton 인 경우가 3건이고 fisrt_name 이 sal로 끝나는 것이 1건 이였다면 5.6 이하 버전은 3건 조회하지만 그 이상일 경우 1건만 조회함

인덱스 확장 (use_index_extensions)

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

모든 세컨더리 인덱스는 리프 노드에 프라이머리 키 값을 가진다.

인덱스 머지

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

  • index_merge_intersection
  • index_merge_sort_union
  • index_merge_union

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

2개 이상 인덱스를 사용하되 and 연산자로 묶임

1
2
3
4

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

first_name 컬럼과 emp_no 컬럼 모두 각각 인덱스 (ix_firstname, PRIMARY) 를 가진다면 실행계획의 Extra 컬럼에 Using intersect 표시 교집합만 반환을 의미

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

2개 이상 인덱스를 사용하되 or 연산자로 묶임

1
2
3
4

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

실행계획 Extra 컬럼에 Using union 표시 두 집합의 합집합을 가져옴 두 집합에 대해서 정렬하고 중복 레코드를 제거했다는 이야기인데 정렬 작업을 하지 않음 이유는 세컨더리 인덱스마다 PK를 가지고 있기 때문에 PK 값으로 제거되었음

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

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

1
2
3
4

mysql> EXPLAIN SELECT *
        FROM employees
        WHERE first_name = 'Matt' 
        or hire_date BETWEEN '1987-03-01' and '1987-03-31'

first_name 은 emp_no 로 정렬 되어있지만 hire_date는 hire_date로 정렬되어 있지 않다. 그래서 우선순위 큐를 사용하는 것이 불가능 중복제거를 위해 정렬이 필요하다.

세미조인(semijoin)

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

MYSQL
mysql 옵티마이저