9.3(3) 인덱스 머지
인덱스 머지(index_merge)
- 인덱스 머지 실행 계획을 사용하면 한 테이블에 대해 2개 이상의 인덱스를 이용해 쿼리를 처리한다.
- 쿼리에 사용된 각 조건이 서로 다른 인덱스를 사용할 수 있고 그 조건을 만족하는 레코드 건수가 많을 거라 예상될 때 인덱스 머지 실행 계획을 선택한다.
- 이 외에 대부분 옵티마이저는 테이블별로 하나의 인덱스만 사용하도록 실행 계획을 수립한다.
- 인덱스 머지 실행 계획은 다음과 같이 3개로 나눌 수 있고 각각 결과를 어떤 방식으로 병합할지에 따라 구분된다.
index_merge_intersection
index_merge_sort_union
index_merge_union
인덱스 머지 - 교집합(index_merge_intersection)
first_name
에 인덱스가 있고 emp_no
는 프라이머리 키인 테이블에 실행한 쿼리이다.
EXPLAIN SELECT *
FROM employees
WHERE fiirst_name='Georgii' AND emp_no BETWEEN 10000 AND 20000;
type |
key |
key_len |
Extra |
index_merge |
ix_firstname,PRIMARY |
62.4 |
Using intersection (ix_firstname,PRMARY); Using where |
- Using intersect를 통해 이 쿼리가 여러 인덱스를 각각 검색해 교집합만 반환했다는 것을 알 수 있다.
- 하나의 인덱스만 검색해서 충분히 레코드 건수를 좁힐 수 있었다면 그렇게 했겠지만 레코드 건수를 예측하니 많은 레코드를 읽어야 할 것을 옵티마이저가 판단한 결과이다.
- 인덱스 머지가 아니었다면 한 인덱스로 먼저 검색을해서 상대적으로 많은 레코드를 가져온 뒤 나머지 칼럼 조건에 일치하는 레코드들만 반환하는 형태로 처리되어야 한다. (버려지는 레코드가 많아짐)
- 이 경우에서
ix_firstname
인덱스는 프라이머리 키인 emp_no
칼럼을 자동으로 포함하고 있기에 ix_firstname
만 사용하는 것이 성능이 더 좋을 수도 있다. (인덱스 컨디션 푸시 다운)
- 다음과 같이 index_merge condition 최적화를 비활성화 하면 된다.
optimizer_switch=’index_merge_intersection=off’;
인덱스 머지 - 합집합(index_merge_unioin)
ix_firstname
인덱스와 ix_hiredate
인덱스가 존재하는 경우이다.
SELECT *
FROM employees
WHERE first_name='MATT' OR hire_date='1987-03-01';
type |
key |
key_len |
Extra |
index_merge |
ix_firstname,ix_hiredate |
58.3 |
Using intersection (ix_firstname,ix_hiredate); |
- 두
WHERE
조건이 OR
로 연결되어 있기에 Using union 최적화를 사용한다.
- 두 인덱스로 검색한 결과를 Union 알고리즘으로 병합했다는 의미
- 두 인덱스로 검색한 각 결과에는 서로 같은 중복 레코드가 포함되어 있기 때문에 중복을 제거할 필요가 있다.
- 중복을 제거하려면 각 결과가 프라이머리 키로 정렬되어 있어야 한다.
- 하지만 위 경우에는 이미 두 결과가 프라이머리 키로 정렬되어 있기에 따로 정렬 작업을 해주지 않아도 중복을 제거할 수 있다.
SQL 문장에서 두 조건이 AND
연사자로 연결된 경우 하나라도 인덱스를 사용할 수 있으면 인덱스 레인지 스캔으로 처리된다. 하지만 OR
연산자인 경우엔 하나라도 인덱스를 사용하지 못하면 항상 풀 테이블 스캔으로밖에 처리하지 못한다.
인덱스 - 머지 정렬 후 합집합(index_merge_sort_union)
- 만약 인덱스 머지 작업을 하는 경우에 정렬이 필요한 경우 Sort union 알고리즘을 사용한다.
- 한 결과라도 프라이머리 키로 정렬되어 있지 않은 경우
- Sort union 알고리즘을 사용한 경우 실행 계획 Extra에 Using_sort_union이 표시된다.