MySQL5.6 GROUP BY 최적화

8.2.1.16 GROUP BY 최적화

GROUP BY 절을 만족시키는 가장 일반적인 방법은 전체 테이블을 스캔하고 각 그룹의 모든 행이 연속하는 새로운 임시 테이블을 만들 수 있으며, 그로 인하여이 임시 테이블을 사용하여 그룹을 찾아 밖으로 집계 함수 (있는 경우)을 적용 할 수 있습니다. 경우에 따라 MySQL은 인덱스 액세스를 사용하는 것으로, 그것보다 훨씬 적절하게 실행하고 임시 테이블 생성을 피할 수 있습니다.

GROUP BY 에 인덱스를 사용하기위한 가장 중요한 전제 조건은 모든 GROUP BY 컬럼이 동일한 인덱스에서 속성을 참조하는 것과 인덱스가 키를 올바른 순서로 포함하는 (예를 들어, 이것은 BTREE 인덱스 , HASH 인덱스가 없습니다) 것입니다. 임시 테이블의 사용을 인덱스 액세스에 대체되는지 여부는 쿼리에서 인덱스의 어느 부분을 사용하고 있는지 그 부분에 지정된 조건 및 선택된 집계 함수에 따라.

다음 섹션에서 자세히 설명하도록 인덱스 액세스에 의해 GROUP BY 쿼리를 실행하는 방법은 두 가지가 있습니다. 첫 번째 방법은 그룹화 작업은 모든 범위 술어 (있는 경우)와 함께 적용됩니다. 두 번째 방법으로는 먼저 범위 스캔을 수행 한 후 결과 튜플을 그룹화합니다.

MySQL은 GROUP BY 는 정렬에 사용되기 때문에 서버는 그룹화에 ORDER BY 최적화를 적용 할 수도 있습니다. 섹션 8.2.1.15 "ORDER BY 최적화" 를 참조하십시오.

8.2.1.16.1 루스 인덱스 스캔

GROUP BY 를 처리하는 가장 효율적인 방법은 인덱스를 사용하여 그룹화 할 컬럼을 직접 검색하는 것입니다. 이 접근 방법은 MySQL은 키가 정렬 된 인덱스 형의 특성을 사용합니다. (예를 들어, BTREE ). 이 등록 정보는 인덱스의 모든 WHERE 조건을 충족 키를 고려할 필요 없게 인덱스의 조회 그룹을 사용할 수 있습니다. 이 접근 방법은 인덱스 키의 일부만을 고려하기 때문에 느슨한 인덱스 스캔이라고합니다. WHERE 절이 없으면 루스 인덱스 스캔은 그룹의 수만큼 키를 읽지 만, 이것은 모든 키의 수보다 훨씬 적은 수 있습니다. WHERE 절에 범위 조건이 포함 된 경우 ( 섹션 8.8.1 "EXPLAIN으로 쿼리 최적화" 의 range 결합 형의 설명을 참조하십시오) 루스 인덱스 스캔은 범위 조건을 만족하는 각 그룹의 첫 번째 키를 조회하고 다시 최소한의 키를 읽습니다. 이것은 다음의 조건 하에서 가능합니다.

쿼리가 단일 테이블에 대한 것입니다.
GROUP BY 는 인덱스의 좌단의 프리픽스를 형성하는 컬럼 만 지정하고 다른 컬럼을 지정하지 않습니다. ( GROUP BY 대신 쿼리에 DISTINCT 절이있는 경우 개별 모든 속성이 인덱스의 좌단의 프리픽스를 형성하는 열을 참조합니다.) 예를 들어, 테이블 t1 의 (c1,c2,c3) 에 인덱스 이 경우 쿼리에 GROUP BY c1, c2, 이 경우 루스 인덱스 스캔을 적용 할 수 있습니다. 쿼리에 GROUP BY c2, c3 (컬럼은 왼쪽의 프리픽스가 아닌) 또는 GROUP BY c1, c2, c4 ( c4 는 인덱스에없는)이있는 경우에는 적용되지 않습니다.
선택 목록 (있는 경우)에 사용되는 집계 함수가 MIN() 와 MAX() 뿐이며, 그들은 모두 같은 컬럼을 참조합니다. 컬럼은 인덱스에있을 필요가있어, GROUP BY 에있는 컬럼을 추적해야합니다.
쿼리에서 참조 된 GROUP BY 에서 부분 이외의 인덱스 부분은 상수 여야합니다 (즉, 상수와 동등한 것으로 언급되어 있어야합니다)가 MIN() 또는 MAX() 함수 인수를 제외합니다.
인덱스 컬럼의 경우, 프리픽스뿐만 아니라 전체 컬럼 값에 인덱스가 설정되어 있어야합니다. 예를 들어, c1 VARCHAR(20), INDEX (c1(10)) 는 인덱스 루스 인덱스 스캔에 사용할 수 없습니다.

느슨한 인덱스 스캔을 쿼리에 적용 할 경우 EXPLAIN 출력에서 Extra 컬럼에 Using index for group-by 로 표시됩니다.

테이블 t1(c1,c2,c3,c4) 에 인덱스 idx(c1,c2,c3) 가 있다고 가정합니다. 느슨한 인덱스 스캔 액세스 메소드는 다음의 쿼리에 사용할 수 있습니다.

 SELECT c1, c2 FROM t1 GROUP BY c1, c2;
 SELECT DISTINCT c1, c2 FROM t1;
 SELECT c1, MIN (c2) FROM t1 GROUP BY c1;
 SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
 SELECT MAX (c3), MIN (c3), c1, c2 FROM t1 WHERE c2> const GROUP BY c1, c2;
 SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
 SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

다음 이유로 다음의 쿼리는이 빠른 선택 메소드에서 실행 할 수 없습니다.

MIN() 또는 MAX() 이외의 집계 함수가 있습니다.
```
 SELECT c1, SUM (c2) FROM t1 GROUP BY c1;
```
GROUP BY 절의 컬럼이 인덱스의 좌단의 프리픽스를 형성하지 않습니다.
```
 SELECT c1, c2 FROM t1 GROUP BY c2, c3;
```
쿼리는 GROUP BY 부분에 계속되는 키 부분을 참조하고 거기에 상수와 동등한 것이 없습니다.
```
 SELECT c1, c3 FROM t1 GROUP BY c1, c2;
```
쿼리에 WHERE c3 = const 가 포함 된 경우 느슨한 인덱스 스캔을 사용할 수 있습니다.

느슨한 인덱스 스캔 액세스 메소드는 선택 목록에서 이미 지원되는 MIN() 및 MAX() 참조 이외에 다른 형식의 집계 함수 참조에 적용 할 수 있습니다.

AVG(DISTINCT) , SUM(DISTINCT) 및 COUNT(DISTINCT) 를 지원합니다. AVG(DISTINCT) 와 SUM(DISTINCT) 는 1 개의 인수를 취합니다. COUNT(DISTINCT) 는 여러 컬럼 인수를 지정할 수 있습니다.
쿼리에 GROUP BY 또는 DISTINCT 절되어서는 안됩니다.
여기에서도 전술 한 루스 스캔 제한이 적용됩니다.

테이블 t1(c1,c2,c3,c4) 에 인덱스 idx(c1,c2,c3) 가 있다고 가정합니다. 느슨한 인덱스 스캔 액세스 메소드는 다음의 쿼리에 사용할 수 있습니다.

 SELECT COUNT (DISTINCT c1), SUM (DISTINCT c1) FROM t1;

 SELECT COUNT (DISTINCT c1, c2), COUNT (DISTINCT c2, c1) FROM t1;

느슨한 인덱스 스캔은 다음의 쿼리에 적용 할 수 없습니다.

 SELECT DISTINCT COUNT (DISTINCT c1) FROM t1;

 SELECT COUNT (DISTINCT c1) FROM t1 GROUP BY c1;

8.2.1.16.2 꽉 인덱스 스캔

꽉 인덱스 스캔은 쿼리 조건에 따라 풀 인덱스 스캔 또는 범위 인덱스 스캔 중 하나입니다.

느슨한 인덱스 스캔의 조건이 충족되지 않더라도 GROUP BY 쿼리의 임시 테이블 생성을 방지 할 수 있습니다. WHERE 절에 범위 조건이있는 경우,이 메소드는 이러한 조건을 충족 키만을 읽습니다. 그렇지 않은 경우는 인덱스 스캔을 실행합니다. 이 메소드는 WHERE 절에 의해 정의 된 각 범위 내의 모든 키를 읽거나 범위 제한이 없으면 전체 인덱스를 스캔하기 위해 꽉 인덱스 스캔라고 부르고 있습니다. 꽉 인덱스 스캔은 범위 조건을 만족하는 모든 키가 발견 한 뒤에게만 그룹화 작업이 수행됩니다.

이 메소드가 기능하기 위해서는 쿼리에있는 모든 컬럼에 GROUP BY 키 앞에 오거나 사이의 부분에있는 키 부분을 참조하는 정수 동등한 조건이 있으면 충분합니다. 동등한 조건에서 상수는 인덱스의 전체 프리픽스를 형성 할 수 있도록 검색 키의 「갭」을 채 웁니다. 이러한 인덱스 프리픽스는 인덱스 조회에 사용할 수 있습니다. GROUP BY 결과의 정렬이 필요 인덱스 프리픽스 인 검색 키를 형성 할 경우 정렬 된 인덱스의 접두어에 의한 검색에서 이미 모든 키가 차례로 취득되어 있기 때문에, MySQL은 여분의 정렬 조작도 피할 수 있습니다.

테이블 t1(c1,c2,c3,c4) 에 인덱스 idx(c1,c2,c3) 가 있다고 가정합니다. 다음의 쿼리는 위의 느슨한 인덱스 스캔 액세스 메소드에서는 작동하지 않지만, 꽉 인덱스 스캔 액세스 메소드에서 작동합니다.

GROUP BY 에는 차이가 있지만, 조건 c2 = 'a' 에 의해 커버됩니다.
```
 SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a'GROUP BY c1, c3;
```
GROUP BY 는 키의 첫 부분에서 시작되지 않습니다 만, 그 부분에 대해 상수를주는 조건이 있습니다.
```
 SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a'GROUP BY c2, c3;
```