[MySQL] 인덱스 병합 최적화

■ 인덱스 병합 최적화란?

인덱스 병합 액세스 방법은 여러 범위 스캔으로 행을 검색하고 결과를 하나로 병합합니다. 이 액세스 방법은 단일 테이블의 인덱스 스캔만 병합하고 여러 테이블의 스캔은 병합하지 않습니다. 병합은 기본 스캔의 결합, 교차 또는 교차 결합을 생성할 수 있습니다.

 

인덱스 병합을 사용할 수있는 쿼리 예 :

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name
  WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;

SELECT * FROM t1, t2
  WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
  AND t2.key1 = t1.some_col;

SELECT * FROM t1, t2
  WHERE t1.key1 = 1
  AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

 

노트

인덱스 병합 최적화 알고리즘에는 다음과 같은 알려진 제한이 있습니다.

+ 쿼리에 AND/OR 중첩이 많은 복잡한 WHERE 절이 있고 MySQL이 최적의 계획을 선택하지 않으면 다음 ID 변환을 사용하여 쿼리를 수행합니다.

(x AND y) OR z => (x OR z) AND (y OR z)

(x OR y) AND z => (x AND z) OR (y AND z)

+ 인덱스 병합은 전체 텍스트 인덱스에 적용 할 수 없습니다.

 

EXPLAIN 출력에서 인덱스 병합 방법은 유형 열에 index_merge로 나타납니다. 이 경우 키 열에는 사용된 인덱스 목록이 포함되고 key_len에는 해당 인덱스에 대한 가장 긴 키부분 목록이 포함됩니다.

 

인덱스 병합 액세스 방법에는 EXPLAIN 출력의 추가 필드에 표시되는 여러 알고리즘이 있습니다.

+ Using intersect(...)

+ Using union(...)

+ Using sort_union(...)

 

다음 섹션에서는 이러한 알고리즘에 대해 자세히 설명합니다. 옵티마이저는 사용 가능한 다양한 옵션의 비용 추정치에 따라 가능한 다른 Index Merge 알고리즘과 기타 액세스 방법 중에서 선택합니다.

 

인덱스 병합 사용에는 optimizer_switch 시스템 변수의 index_merge, index_merge_intersection, index_merge_union 및 index_merge_sort_union 플래그 값이 적용됩니다. 기본적으로 모든 플래그가 켜져 있습니다. 특정 알고리즘 만 활성화하려면 index_merge를 off로 설정하고 허용되는 다른 알고리즘 만 활성화합니다.

+ 인덱스 병합 교차 액세스 알고리즘

+ 인덱스 병합 전체(Union) 액세스 알고리즘

+ 인덱스 병합 정렬 조합(Sort-Union) 액세스 알고리즘

 

■ 인덱스 병합 교차 액세스 알고리즘

이 액세스 알고리즘은 WHERE 절이 AND와 결합된 다른 키에서 여러 범위 조건으로 변환될 때 적용 가능하며 각 조건은 다음 중 하나입니다.

+ 이 형식의 N-부분 표현식. 인덱스에 정확히 N 부분이 있음(즉, 모든 인덱스 부분이 포함됨):

key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN

+ InnoDB 테이블의 기본 키에 대한 모든 범위 조건.

 

예제 :

SELECT * FROM innodb_table
  WHERE primary_key < 10 AND key_col1 = 20;

SELECT * FROM tbl_name
  WHERE key1_part1 = 1 AND key1_part2 = 2 AND key2 = 2;

인덱스 병합 교차 알고리즘은 사용된 모든 인덱스에서 동시 스캔을 수행하고 병합된 인덱스 스캔에서 수신 한 행 시퀀스의 교차를 생성합니다.

 

쿼리에 사용된 모든 열이 사용된 인덱스에 의해 커버되는 경우 전체 테이블 행이 검색되지 않습니다 (이 경우 EXPLAIN 출력에는 추가 필드에 인덱스 사용이 포함됨). 다음은 이러한 쿼리의 예입니다.

SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1;

 

사용된 인덱스가 쿼리에 사용된 모든 열을 다루지 않으면 사용된 모든 키의 범위 조건이 충족될 때만 전체 행이 검색됩니다.

 

병합된 조건중 하나가 InnoDB 테이블의 기본키에 대한 조건인 경우 행 검색에는 사용되지 않지만 다른 조건을 사용하여 검색된 행을 필터링하는 데 사용됩니다.

 

■ 인덱스 병합 전체(Union) 액세스 알고리즘

이 알고리즘의 기준은 인덱스 병합 교차 알고리즘의 기준과 유사합니다. 알고리즘은 테이블의 WHERE 절이 OR과 결합된 다른 키에서 여러 범위 조건으로 변환될 때 적용 가능하며 각 조건은 다음 중 하나입니다.

+이 형식의 N-부분 표현식. 인덱스에는 정확히 N 개의 부분이 있습니다 (즉, 모든 인덱스 부분이 포함됨).

key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN

+ InnoDB 테이블의 기본 키에 대한 모든 범위 조건.

+ 인덱스 병합 교차 알고리즘이 적용되는 조건.

 

예제 :

SELECT * FROM t1
  WHERE key1 = 1 OR key2 = 2 OR key3 = 3;

SELECT * FROM innodb_table
  WHERE (key1 = 1 AND key2 = 2)
     OR (key3 = 'foo' AND key4 = 'bar') AND key5 = 5;

■ 인덱스 병합 정렬 조합(Sort-union) 액세스 알고리즘

이 액세스 알고리즘은 WHERE 절이 OR로 결합된 여러 범위 조건으로 변환될 때 적용 가능하지만 인덱스 병합 통합 알고리즘은 적용 할 수 없습니다.

 

예제 :

SELECT * FROM tbl_name
  WHERE key_col1 < 10 OR key_col2 < 20;

SELECT * FROM tbl_name
  WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col = 30;

 

sort-union 알고리즘과 공용체 알고리즘의 차이점은 sort-union 알고리즘은 모든 행의 행 ID를 먼저 가져 와서 행을 리턴하기 전에 정렬해야한다는 것입니다.