MySQL5.6 range 최적화

8.2.1.3 range 최적화

range 접근 방식은 하나의 인덱스를 사용하여 하나 이상의 인덱스 값 간격 안에 포함 된 테이블 행의 부분 집합을 가져옵니다. 이것은 단일 부분 또는 여러 부분 인덱스에 사용할 수 있습니다. 다음 섹션에서는 WHERE 절에서 간격을 추출하는 방법에 대해 자세히 설명합니다.

8.2.1.3.1 단일 부분 인덱스에 range 접근 방법

단일 부분 인덱스는 인덱스 값 간격은 WHERE 절에서 해당 조건에 따라 편리하게 나타낼 수 있기 때문에 "간격"보다 범위 조건을 설명합니다.

단일 부분 인덱스 범위 조건의 정의는 다음과 같습니다.

BTREE 와 HASH 모두 인덱스에서 = , <=> , IN() , IS NULL 또는 IS NOT NULL 연산자를 사용하면 키 부분을 상수 값의 비교는 범위 조건입니다.
또한 BTREE 인덱스는 > , < , > >= , <= , BETWEEN ! != 또는 <> 연산자 또는 LIKE 의 인수가 와일드 카드 문자로 시작하지 않는 상수 문자열 인 경우 LIKE 비교 를 사용한 경우 키 부분을 상수 값의 비교는 범위 조건입니다.
모든 종류의 인덱스로, OR 또는 AND 로 결합 된 다중 범위 조건은 1 개의 범위 조건을 형성합니다.

앞서의 "상수"이란 다음 중 하나를 의미합니다.

쿼리 문자열에서 정수
같은 결합에서 const 또는 system 테이블의 컬럼
비 상관 서브 쿼리의 결과
이전 형태의 부분 식에서만 구성된 식

다음에 WHERE 절에서 범위 조건을 사용한 쿼리의 몇 가지 예를 보여줍니다.

 SELECT * FROM t1
   WHERE key_col > 1
   AND key_col <10;

 SELECT * FROM t1
   WHERE key_col = 1
   OR key_col IN (15,18,20);

 SELECT * FROM t1
   WHERE key_col LIKE 'ab %'
   OR key_col BETWEEN 'bar'AND 'foo';

상수 전파 단계에서 일부 비 상수 값이 정수로 변환 될 수 있습니다.

MySQL은 가능한 인덱스에 대해 WHERE 절에서 범위 조건을 추출하려고합니다. 추출 과정에서 범위 조건의 구축에 사용할 수없는 조건이 삭제됩니다 중복 영역을 생성하는 조건은 결합되어 하늘의 범위를 생성하는 조건은 삭제됩니다.

key1 이 인덱싱 된 열에서 nonkey 이 인덱싱되지 않은 다음 문을 고려합니다.

 SELECT * FROM t1 WHERE
   (key1 < 'abc'AND (key1 LIKE 'abcde %'OR key1 LIKE '% b')) OR
   (key1 < 'bar'AND nonkey = 4) OR
   (key1 < 'uux'AND key1> 'z');

키 key1 추출 과정은 다음과 같습니다.

원래 WHERE 절에서 시작합니다.

 (key1 < 'abc'AND (key1 LIKE 'abcde %'OR key1 LIKE '% b')) OR
 (key1 < 'bar'AND nonkey = 4) OR
 (key1 < 'uux'AND key1> 'z')

nonkey = 4 와 key1 LIKE '%b' 는 범위 스캔에 사용할 수 없기 때문에 삭제합니다. 그들을 제거하는 올바른 방법은 범위 스캔을 수행 할 때 일치하는 행을 간과하지 않도록 그들을 TRUE 로 대체하는 것이다. TRUE 로 변경하면 다음과 같이됩니다.
```
 (key1 < 'abc'AND (key1 LIKE 'abcde %'OR TRUE)) OR
 (key1 < 'bar'AND TRUE) OR
 (key1 < 'uux'AND key1> 'z')
```
항상 true 또는 false 인 조건을 축소합니다.
- (key1 LIKE 'abcde%' OR TRUE) 는 항상 true입니다
- (key1 < 'uux' AND key1 > 'z') 은 항상 false입니다
이러한 조건을 상수로 대체하면 다음과 같이됩니다.
```
 (key1 < 'abc'AND TRUE) OR (key1 < 'bar'AND TRUE) OR (FALSE)
```
불필요한 TRUE 및 FALSE 상수를 제거하면 다음과 같이됩니다.
```
 (key1 < 'abc') OR (key1 < 'bar')
```
중복 간격을 하나로 결합하여 범위 스캔에 사용되는 최종 조건이 생성됩니다.
```
 (key1 < 'bar')
```

일반적으로 (앞의 예에서 나타낸 것처럼) 범위 스캔에 사용되는 조건은 WHERE 절보다 제한이 엄격합니다. MySQL은 범위 조건을 만족하지만 완전한 WHERE 절하지 않은 행을 필터링하는 추가 검사를 수행합니다.

범위 조건 추출 알고리즘은 임의의 깊이 중첩 AND / OR 구조를 처리하고 그 출력은 WHERE 절의 조건이 존재하는 순서에 의존하지 않습니다.

현재 MySQL에서는 공간 인덱스에 대해 range 접근 방식의 여러 범위의 병합을 지원하지 않습니다. 이 문제를 해결하려면 같은 SELECT 문에 UNION 을 사용할 수 있지만, 그러나 각 공간 술어는 다른 SELECT 에 넣습니다.

8.2.1.3.2 다중 인덱스 range 접근 방법

다중 인덱스 범위 조건은 단일 부분 인덱스 범위 조건의 확장입니다. 다중 인덱스 범위 조건은 인덱스 행을 하나 이상의 키타뿌루 간격 내에 들어가도록 제한합니다. 키타뿌루 간격은 인덱스에서 순서를 사용하여 키타뿌루 세트에 정의됩니다.

예를 들어, key1( key_part1 、 key_part2 、 key_part3 ) 로 정의 된 다중 인덱스 키 순서로 나타난 다음 키타뿌루 세트를 고려합니다.

 key_part1 key_part2 key_part3
   NULL 1 'abc'
   NULL 1 'xyz'
   NULL 2 'foo'
    1 'abc'
    1 'xyz'
    1 2 'abc'
    2 1 'aaa'

조건 key_part1 = 1 은 다음의 간격을 정의합니다.

 (1, -inf, -inf) <= ( key_part1 , key_part2 , key_part3 ) <(1 + inf + inf)

간격은 이전 데이터 세트의 4,5,6 번째 튜플을 커버하고 range 접근 방식에 사용할 수 있습니다.

대조적으로, 조건 key_part3 = 'abc' 는 단일 간격을 정의하지 않고 range 접근 방식에 사용할 수 없습니다.

다음의 설명에서는 다중 인덱스에 대해 범위 조건이 어떻게 작용 하는지를 자세히 설명합니다.

HASH 인덱스는 동일한 값을 포함한 각 간격을 사용할 수 있습니다. 이것은 다음과 같은 형식의 조건에 대해서만 간격을 생성 할 수있는 것을 의미합니다.
```
     key_part1 cmp const1
 AND key_part2 cmp const2
 AND ...
 AND key_partN cmp constN ;
```
여기에서 const1 , const2 ...는 정수로, cmp 는 = , <=> 또는 IS NULL 비교 연산자 중 하나에서 조건은 모든 인덱스 부분을 커버합니다. (즉, N 파트 인덱스의 각 파트에 하나 N 조건이 있습니다.) 예를 들어, 다음은 3 부 HASH 인덱스 범위 조건입니다.
```
 key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'
```
무엇을 상수로 간주 가지 정의는 " 섹션 8.2.1.3.1 "단일 부분 인덱스에 range 접근 방식" "을 참조하십시오.
BTREE 인덱스는 각 조건에서 = , <=> , IS NULL , > , < , >= = , <= , != = , <> , BETWEEN 또는 LIKE ' pattern ' (여기서 ' pattern ' 은 와일드 카드로 시작 없는)를 사용하여 키 부분을 상수 값과 비교하는 AND 로 결합 된 조건에 간격을 사용할 수 있습니다. 조건에 일치하는 모든 행을 포함하는 단일 키타뿌루를 판단 할 수있는 경우에 한해, 1 개의 간격을 사용할 수 있습니다 (또는 <> 또는 != 를 사용하는 경우 2 개의 간격).

최적화는 비교 연산자 = , <=> 또는 IS NULL 인 경우에 한해 추가 키 부분을 사용하여 간격을 확인하려고합니다. 연산자가 > , < , > >= , <= , ! != , <> , BETWEEN 또는 LIKE 의 경우, 최적화 그것을 사용하지만 추가 키 부분은 고려하지 않습니다. 다음 식은 최적화 첫 번째 비교에서 = 을 사용합니다. 두 번째 비교에서 >= 도 사용하지만, 그 이상의 키 부분을 고려하지 않고 간격의 구축에 3 번째 비교를 사용하지 않습니다.
```
 key_part1 = 'foo'AND key_part2 > = 10 AND key_part3 > 10
```
단일 간격은 다음과 같습니다.
```
 ( 'foo', 10, -inf) <( key_part1 , key_part2 , key_part3 ) <( 'foo'+ inf + inf)
```
작성된 간격에 초기 조건보다 많은 행이 포함될 수 있습니다. 예를 들어, 이전 간격 값 ('foo', 11, 0) 을 포함하지만 이것은 원래의 조건을 만족하지 않습니다.
간격에 포함 된 행 집합을 충족하는 조건이 OR 로 결합되어있는 경우, 그들은 그 간격의 합집합에 포함 된 행 집합을 충족하는 조건을 형성합니다. 조건이 AND 로 결합되어있는 경우, 그들은 간격의 공통 집합에 포함 된 행 집합을 대상으로하는 조건을 형성합니다. 예를 들어, 2 부 인덱스에서이 조건의 경우 :
```
 ( key_part1 = 1 AND key_part2 <2) OR ( key_part1 > 5)
```
간격은 다음과 같습니다.
```
 (1, -inf) <( key_part1 , key_part2 ) <(1,2)
 (5 -inf) <( key_part1 , key_part2 )
```
이 예에서 첫 번째 줄의 간격은 왼쪽 경계에 하나의 키 부분을 사용하고 오른쪽 경계에 2 개의 키 부분을 사용하고 있습니다. 두 번째 줄 간격은 하나의 키 부분만을 사용하고 있습니다. EXPLAIN 출력의 key_len 컬럼은 사용 된 키 프리픽스의 최대 길이를 보여줍니다.

경우에 따라 key_len 는 키 파트가 사용 된 것을 나타냅니다 만, 그것이 예상 한 것은 아니다 수 있습니다. key_part1 과 key_part2 이 NULL 이 될 수 있다고합니다. 다음으로 key_len 컬럼에 다음 조건 두 개의 키 부분이 표시됩니다.
```
 key_part1 > = 1 AND key_part2 <2
```
그러나 실제로는 조건이 다음 변환됩니다.
```
 key_part1 > = 1 AND key_part2 IS NOT NULL
```

' 섹션 8.2.1.3.1 "단일 부분 인덱스에 range 접근 방식" "는 단일 부분 인덱스 범위 조건의 간격을 조합하거나 제거하는 데에 얼마나 최적화가 실행되는지 을 설명하고 있습니다. 다중 부분 인덱스 범위 조건에도 유사한 절차가 실행됩니다.

8.2.1.3.3 다중 값 비교의 등가 범위의 최적화

col_name 이 인덱싱 된 컬럼 인 다음 식을 고려합니다.

 col_name IN ( val1 , ..., valN )
 col_name = val1 OR ... OR col_name = valN

col_name 이 여러 값 중 하나와 동일한 경우에 각 식은 true가됩니다. 이러한 비교는 등가 범위 비교입니다 (여기서 "범위"는 단일 값입니다). 최적화 프로그램은 다음과 같이 등가 범위 비교의 대상이되는 행 읽기의 비용을 추정합니다.

col_name 에 고유 인덱스가있는 경우 지정한 값을 가질 수 행 많아도 하나이므로 각 범위의 행의 견적은 1입니다.
그렇지 않은 경우, 옵티마이 저는 인덱스 다이빙 또는 인덱스 통계를 사용하여 각 범위의 행 수를 추정 할 수 있습니다.

인덱스 다이브는 최적화 범위의 끝에서 다이빙을 만들고 범위 내의 행수를 견적으로 사용합니다. 예를 들어, 식 col_name IN (10, 20, 30) 에는 3 개의 등가 범위가 최적화 프로그램은 범위 당 2 개의 다이빙을 만들어 줄 견적을 생성합니다. 다이브 쌍에 대해 지정된 값을 가진 행 수의 견적을 생성합니다.

인덱스 다이브는 정확한 행 견적을 제공하지만 식의 비교 값의 수가 많을수록 최적화 줄 견적 생성에 시간이 걸릴 수 있습니다. 인덱스 통계의 사용은 인덱스 다이브 더 정확하지는 않지만 큰 값 목록의 경우 행 견적이 빨라집니다.

eq_range_index_dive_limit 시스템 변수를 사용하여 최적화가 행의 가정 전략을 다른 전략으로 전환 값의 수를 구성 할 수 있습니다. 통계의 사용을 해제하여 항상 인덱스 다이브를 사용하려면 eq_range_index_dive_limit 를 0으로 설정합니다. 최대 N 개의 등가 범위의 비교에 인덱스 다이브의 사용을 허용하려면 eq_range_index_dive_limit 를 N + 1으로 설정합니다.

eq_range_index_dive_limit 은 MySQL 5.6.5 이상에서 사용할 수 있습니다. 5.6.5 이전에서는 최적화 eq_range_index_dive_limit=0 과 동등한 인덱스 다이브를 사용합니다.

최적의 추정을 위해 테이블 인덱스 통계를 업데이트하려면 ANALYZE TABLE 을 사용합니다.