인덱스 머지

Index Merge

• 인덱스를 이용해 쿼리를 실행하는 경우, 대부분 옵티마이저는 테이블 별로 하나의 인덱스만 사용하도록 실행 계획을 수립한다.
  • 쿼리에서 한 테이블에 대한 WHERE 조건이 여러 개 있더라도 하나의 인덱스에 포함된 칼럼에 대한 조건만으로 인덱스를 검색하고, 나머지 조건은 읽어온 레코드에 대해서 체크하는 형태로만 사용되는 것이 일반적이다.
  • 하나의 인덱스만 사용해서 작업 범위를 충분히 줄일 수 있는 경우 테이블 별로 하나의 인덱스만 활용하는 것이 효율적이다.
• 인덱스 머지 실행 계획을 사용하면 하나의 테이블에 대해 2개 이상의 인덱스를 이용해 쿼리를 처리한다.
  • MySQL 서버는 쿼리에 사용된 각각의 조건이 서로 다른 인덱스를 사용할 수 있고 그 조건을 만족하는 레코드 건수가 많을 것으로 예상될 때 인덱스 머지 실행 계획을 선택한다.
• 인덱스 머지 실행 계획은 다음 3개의 세부 실행 계획으로 나누어 볼 수 있다.
  • index_merge_intersection
  • index_merge_union
  • index_merge_sort_union
  • 3가지 최적화 모두 여러 개의 인덱스를 통해 결과를 가져온다는 것은 동일하지만, 각각의 결과를 어떤 방식으로 병합할지에 따라 구분된다.

교집합 (index_merge_intersection)

• WHERE 조건에 사용된 조건이 모두 각각의 인덱스를 가지고 있을 경우
  • 여러 개의 인덱스를 각각 검색해서 그 결과의 교집합만 반환
• 실행 계획의 Extra 칼럼에 “Using intersect”라고 표시된다.
• 옵션 활성화 및 비활성화

  • 만약 사용하는 것이 더 비효율적이라면 비활성화하면 된다.

    -- MySQL 서버 전체적으로 비활성화
    SET GLOBAL optimizer_switch='index_merge_intersection=off';
    
    -- 현재 커넥션에 대해 비활성화
    SET SESSION optimizer_switch='index_merge_intersection=off';
    
    -- 현재 쿼리에서만 비활성화
    SELECT /*+SET_VAR(optimizer_switch='index_merge_intersection=off') */ *
    FROM employees
    WHERE ...;

예제

```sql
-- PRIMARY KEY (emp_no), INDEX ix_firstname(first_name)일 때
SELECT *
FROM employees
WHERE first_name='Georgi' AND emp_no BETWEEN 10000 AND 20000;
```

- 옵티마이저는 `ix_firstname` 과 `PRIMARY KEY` 를 모두 사용해서 쿼리를 처리한다.
- 즉, 옵티마이저가 각각의 조건에 일치하는 레코드 건수를 예측해본 결과, 두 조건 모두 상대적으로 많은 레코드를 가져와야 한다는 것을 알게 된 것이다.

• 인덱스 머지 실행 계획이 아니라면 다음 2가지 방식으로 처리해야 했다.
  • first_name='Georgi' 조건만 인덱스를 사용했다면, 일치하는 레코드 253건을 검색한 다음 데이터 페이지에서 레코드를 찾고 emp_no 칼럼의 조건에 일치하는 레코드들만 반환하는 형태로 처리
  • emp_no BETWEEN 10000 AND 20000 조건만 인덱스를 사용했다면, 프라이머리 키를 이용해 10000건을 읽어와서 first_name='Georgi' 조건에 일치하는 레코드만 반환하는 형태로 처리
  • 두 조건을 모두 만족하는 레코드 건수가 14건이라면, 위의 두 작업 모두 비효율이 매우 큰 상황이므로 옵티마이저는 각 인덱스를 검색해 두 결과의 교집합만 찾아서 반환한다.

합집합 (index_merge_union)

• WHERE 절에 사용된 2개 이상의 조건이 각각의 인덱스를 사용하되 OR 연산자로 연결된 경우 사용되는 최적화
• Extra 칼럼에 “Using union”라고 표시된다.
  • 각 인덱스의 검색 결과를 ‘Union’ 알고리즘으로 병합했다는 것을 의미
  • 여기서 병합은 두 집합의 합집합을 가져왔다는 것을 의미한다.

예제

```sql
-- PRIMARY KEY (emp_no)
-- INDEX ix_firstname(first_name), ix_hiredate(hire_date)
SELECT *
FROM employees
WHERE first_name='Matt' OR hire_date='1987-03-31';
```

정렬 없이 중복 제거

• 양쪽 집합(인덱스를 검색한 결과)에 중복이 있을 경우, MySQL 서버는 두 집합에서 하나씩 가져와서 서로 비교하면서 프라이머 키 칼럼값이 중복된 레코드들을 정렬 없이 걸러낼 수 있다.
  • 인덱스를 검색한 결과는 프라이머리 키로 이미 정렬되어 있다.
• 정렬된 두 집합의 결과를 하나씩 가져와 중복 제거를 수행할 때 사용한 알고리즘을 우선순위 큐(Priority Queue)라고 한다.

정렬 후 합집합 (index_merge_sort_union)

• MySQL 서버는 인덱스 머지 작업을 하는 도중에 결과의 정렬이 필요한 경우 인덱스 머지 최적화의 ‘Sort union’ 알고리즘을 사용한다.
  • 중복을 제거하기 위해 우선순위 큐를 사용하는 것이 불가능한 경우
• MySQL 서버는 집합의 결과에서 중복을 제거하기 위해 각 집합을 프라이머리 키 칼럼으로 정렬한 다음 중복 제거를 수행한다.
• 인덱스 머지 최적화에서 중복 제거를 위해 강제로 정렬을 수행해야 하는 경우, 실행 계획의 Extra 칼럼에 “Using sort_union” 문구가 표시된다.

예제

```sql
-- PRIMARY KEY (emp_no)
-- INDEX ix_firstname(first_name), ix_hiredate(hire_date)
SELECT *
FROM employees
WHERE first_name='Matt' OR hire_date BETWEEN '1987-03-01' AND '1987-03-31';
```

- 각 집합을 `emp_no` 칼럼으로 정렬한 다음 중복 제거 수행

Reference

참고 서적

📔 Real MySQL 8.0