기본 데이터 처리: GROUP BY 처리

공부하는 감자·2024년 3월 17일
0

MySQL

목록 보기
23/74
post-thumbnail

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

  • 루스(Loose) 인덱스 스캔 방식은 인덱스의 레코드를 건너뛰면서 필요한 부분만 읽어서 가져오는 것을 의미한다.
    -- // 인덱스는 (emp_no, from_date)
    SELECT emp_no
    FROM salaries
    WHERE from_date = '1985-03-01'
    GROUP BY emp_no;
    1. (emp_no, from_date) 인덱스를 차례대로 스캔하면서 emp_no의 첫 번째 유일한 값(그룹 키)를 찾아낸다.
    2. 인덱스에서 emp_no가 그룹키인 것 중에서 from_date가 ‘1985-03-01’인 레코드만 가져온다. 이는 emp_no=그룹키 AND from_date='1985-03-01' 조건으로 검색하는 것과 흡사하다.
    3. 인덱스에서 emp_no의 다음 유니크한(그룹키) 값을 가져온다.
    4. 결과가 더 없으면 처리를 종료하고, 2번 과정으로 돌아가서 반복 수행한다.
  • 옵티마이저가 루스 인덱스 스캔을 사용할 때는 실행 계획의 Extra 칼럼에 “Using index for group-by” 코멘트가 표시된다.
  • MySQL의 루스 인덱스 스캔 방식은 단일 테이블에 대해 수행되는 GROUP BY 처리에만 사용할 수 있다.
  • 프리픽스 인덱스는 루스 인덱스 스캔을 사용할 수 없다.
    • Prefix index: 칼럼값의 앞쪽 일부만으로 생성된 인덱스
  • 인덱스 레인지 스캔에서는 유니크한 값의 수가 많을수록 성능이 향상되는 반면, 루스 인덱스 스캔은 분포도가 좋지 않은 인덱스일수록 더 빠른 결과를 만들어낸다.
  • 루스 인덱스 스캔으로 처리되는 쿼리에서는 별도의 임시 테이블이 필요하지 않다.
  • 루스 인덱스 스캔이 사용될 수 있을지 없을지 판단하는 것은 WHERE 절의 조건이나 ORDER BY 절이 인덱스를 사용할 수 있을지 없을지 판단하는 것보다는 더 어렵다.
-- // (col1, col2, col3) 칼럼으로 생성된 tb_test 테이블

-- 루스 인덱스 스캔을 사용할 수 있는 쿼리 패턴
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;

-- 루스 인덱스 스캔을 사용할 수 없는 쿼리 패턴
-- 1. MIN()과 MAX()이외의 집합 함수 사용
SELECT col1, SUM(col2) FROM tb_test GROUP BY col1;

-- 2. GROYP BY에 사용된 칼럼이 인덱스 구성 칼럼의 왼쪽부터 일치하지 않음
SELECT col1, col2 FROM tb_test GROUP BY col2, col3;

-- 3. SELECT 절의 칼럼이 GROUP BY와 일치하지 않음
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

profile
책을 읽거나 강의를 들으며 공부한 내용을 정리합니다. 가끔 개발하는데 있었던 이슈도 올립니다.

0개의 댓글