GROUP BY 처리
GROUP BY 사용
- ORDER BY와 같이 쿼리가 스트리밍된 처리를 할 수 없게 하는 처리 중 하나다.
- GROUP BY 절이 있는 쿼리에서는 GROURP BY 결과에 대해 필터링 역할을 수행하는 HAVING 절을 사용할 수 있다.
- GROUP BY에 사용된 조건은 인덱스를 사용해서 처리될 수 없다.
- 따라서 HAVING 절을 튜닝하려고 인덱스를 생성하거나 다른 방법을 고민할 필요는 없다.
GROUP BY 작업 방법
- 인덱스를 사용하는 경우
- 인덱스를 차례대로 읽는 인덱스 스캔 방법
- 인덱스를 건너뛰면서 읽는 루스 인덱스 스캔 방법
- 인덱스를 사용하지 못한 경우
- GROUP BY 작업은 임시 테이블을 사용한다.
인덱스 스캔을 이용하는 GROUP BY
- 타이트 인덱스 스캔
- ORDER BY의 경우와 마찬가지로 조인의 드라이빙 테이블에 속한 칼럼만 이용해 그루핑할 때,
- GROUP BY 칼럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고
- 그 결과로 조인을 처리한다.
- GROUP BY가 인덱스를 통해 처리되는 쿼리는 이미 정렬된 인덱스를 읽는 것이다.
- 쿼리 실행 시점에 추가적인 정렬 작업이나 내부 임시 테이블은 필요하지 않다.
- 그룹 함수(Aggregation function) 등의 그룹값을 처리해야 해서 임시 테이블이 필요할 때도 있다.
- 이러한 그루핑 방식을 사용하는 쿼리의 실행 계획에서는 Extra 칼럼에 별도로 다음 코멘트가 표시되지 않는다.
- GROUP BY 관련 코멘트: “Using index for group-by”
- 임시 테이블 사용 또는 정렬 관련 코멘트: “Using temporary, Using filesort”
루스 인덱스 스캔을 이용하는 GROUP BY
SELECT col1, col2 FROM tb_test GROUP BY col1, col2;
SELECT DIStinCT col1, col2 FROM tb_test;
SELECT col1, MIN(col2) FROM tb_test GROUP BY col1;
SELECT col1, col2 FROM tb_test WHERE col1 < const GROUP BY col1, col2;
SELECT MAX(col3), MIN(col3), col1, col2 FROM tb_test WHERE col2 > const GROUP BY col1, col2;
SELECT col2 FROM tb_test where col1 < const GROUP BY col1, col2;
SELECT col1, col2 FROM tb_test WHERE col3 = const GROUP BY col1, col2;
SELECT col1, SUM(col2) FROM tb_test GROUP BY col1;
SELECT col1, col2 FROM tb_test GROUP BY col2, col3;
SELECT col1, col3 FROM tb_test GROUP BY col1, col2;
인덱스 스킵 스캔 (Index Skip Scan)
- MySQL 8.0 버전부터 루스 인덱스 스캔과 동일한 방식으로 작동하는 인덱스 스킵 스캔 최적가 도입됐다.
- 옵티마이저가 쿼리에서 필요로 하는 레코드를 검색하는 부분까지 루스 인덱스 스캔 방식으로 최적화가 가능해졌다.
- 루스 인덱스 스캔과 마찬가지로 조건이 누락된 인덱스의 선행 칼럼이 유니크한 값을 많이 가질수록 쿼리 처리 성능이 떨어지게 된다.
- 그래서 인덱스 스킵 스캔에서도 선행 칼럼의 유니크한 값으 개수가 많으면 인덱스 스킵 스캔 최적화를 사용하지 않게 된다.
임시 테이블을 사용하는 GROUP BY
- GROUP BY의 기준 칼럼이 드라이빙 테이블에 있든 드리븐 테이블에 있든 관계없이 인덱스를 전혀 사용하지 못할 때 이 방식으로 처리된다.
- 실행 계획에서는 Extra 칼럼에 “Using temporary” 메시지가 표시된다.
- MySQL 8.0 이전 버전까지는 GROUP BY가 사용된 쿼리는 그루핑되는 칼럼을 기준으로 묵시적인 정렬까지 함께 수행했다.
- GROUP BY는 있지만 ORDER BY 절이 없는 쿼리에 대해서는 기본적으로 그루핑 칼럼으로 정렬된 결과를 반환
- 정렬이 필요치 않은 경우
ORDER BY NULL
을 추가로 사용하여 불필요한 추가 정렬 작업을 수행하지 않도록 권장했다.
- MySQL 8.0 버전부터 묵시적인 정렬은 더 이상 실행되지 않게 바뀌었다.
- MySQL 8.0 에서는 GROUP BY가 필요한 경우 내부적으로 GROUP BY의 칼럼들로 구성된 유니크 인덱스를 가진 임시 테이블을 만들어서 중복 제거와 집합 함수 연산을 수행한다.
- 임시 테이블 생성 후, 조인의 결과를 한 건씩 가져와 임시 테이블에서 중복 체크를 하면서 INSERT와 UPDATE를 실행한다.
- 별도의 정렬 작업 없이 GROUP BY가 처리된다.
- MySQL 8.0에서도 GROUP BY와 ORDER BY가 같이 사용되면 명시적으로 정렬 작업을 실행한다.
- Extra 칼럼에 “Using temporary; Using filesort” 표시
Reference
참고 서적
📔 Real MySQL 8.0