TIL

9.2(3) GROUP BY와 DISTINCT

9.2.4 GROUP BY 처리

• GROUP BY도 ORDER BY와 마찬가지로 스트리밍 처리를 할 수 없다.
• GROUP BY 작업도 인덱스를 사용하는 경우와 못하는 경우로 나눌 수 있다.
  • 인덱스 스캔 방법
  • 루스 인덱스 스캔 방법
  • 인덱스를 사용하지 못하면 임시 테이블 사용

인덱스 스캔을 이용하는 GROUP BY (타이트 인덱스 스캔)

• 조인의 드라이빙 테이블에 속한 칼럼만 이용해 그루핑할 때 GROUP BY 칼럼으로 인덱스가 존재한다면 인덱스를 차례로 읽으며 그루핑을 수행하고 그 결과로 조인을 처리한다.
• 인덱스를 사용한다 하더라도 그룹 함수 등을 처리해야 해서 임시 테이블이 필요할 때도 있다.
• 위 경우 실행 계획 Extra 칼럼에 별도의 코멘트가 표시되지 않는다.

루스 인덱스 스캔을 이용하는 GROUP BY

• 루스 인덱스 스캔이란 인덱스 레코드를 건너뛰면서 필요한 부분만 읽어 가져오는 것을 의미한다.
• 루스 인덱스 스캔을 하면 실행 계획 Extra 칼럼에 Using index for group-by가 표시된다.

• salaries 테이블에 (emp_no, from_date)로 인덱스가 생성된 경우 아래 쿼리를 살펴보자.

    EXPLAIN
    	SELECT emp_no
    	FROM salaries
    	WHERE from_date='1995-03-01'
    	GROUP BY emp_no;
      
    --|--------|-----|-------|-------------------------------------
    id|table   |type |key    |Extra
    1 |salaries|range|PRIMARY|Using where; Using index for group-by
    --|--------|-----|-------|-------------------------------------
  

  • 원래라면 검색 조건으로 인덱스 두 번째 칼럼(from_date)을 사용했기에 레인지 스캔을 할 수 없지만 루스 인덱스 스캔을 한 것을 확인할 수 있다.
• 루스 인덱스 스캔 처리 과정
  1. (emp_no, from_date) 인덱스를 스캔하며 emp_no의 첫 번째 유일 값(10001) 탐색
  2. emp_no=10001 중 from_date=’1995-03-01’레코드를 인덱스를 사용해 가져온다.
  3. (emp_no, from_date) 인덱스에서 emp_no의 그 다음 유일 값을 가져온다.
  4. 3단계에서 결과가 더 없으면 처리를 종료하고 더 있다면 2번으로 돌아가 반복 수행한다.
• 인덱스 레인지 스캔에선 인덱스의 유니크 값 수가 많을수록 성능이 향상되지만 루스 인덱스 스캔에서는 유니크 값 수가 적을수록 성능이 향상된다.
• 루스 인덱스 스캔으로 처리되면 임시 테이블이 필요하지 않다.

MySQL 8.0부터는 루스 인덱스 스캔과 동일하게 동작하는 인덱스 스킵 스캔 최적화도 도입됐다. 8.0 이전에는 GROUP BY 절 처리를 위해서만 루스 인덱스 스캔이 사용됐지만 8.0 이후부터는 단순히 WHERE 절로만 검색하더라도 인덱스 스킵 스캔이 동작하면서 최적화가 가능해졌다.

임시 테이블을 사용하는 GROUP BY

• GROUP BY 기준 칼럼이 드라이밍 테이블이 있든 없든 인덱스를 사용하지 못할 때는 임시 테이블을 사용해 처리된다.
• 임시 테이블을 이용해 그루핑하면 실행 계획 Extra 칼럼에 Using temporary가 표시된다.

9.2.5 DISTINCT 처리

• DISTINCT 처리가 MIN(), MAX(), COUNT() 같은 집합 함수와 함께 사용하는 경우와 아닌 경우에 DISTINCT가 영향을 미치는 범위가 달라진다.
• DISTINCT가 사용될 때 인덱스를 사용하지 못하면 항상 임시 테이블이 필요하다.
  • 하지만 실행 계획 Extra 칼럼에 Using temporary가 출력되지 않는다.

SELECT DISTINCT …

• 단순히 SELECT되는 레코드 중 유니크한 것만 가져오고자 하면 GROUP BY와 동일한 방식으로 처리된다.
  • SELECT DISTINCT emp_no FROM salaries;
  • SELECT emp_no FROM salaries GROUP BY emp_no;
  • 위 두 쿼리는 내부적으로 같은 방식으로 처리된다.
• DISTINCT는 조회하는 레코드(튜플)를 유니크하게 조회하는 것이니 특정 칼럼만 유니크하게 조회할 수는 없다.

집합 함수와 함께 사용된 DISTINCT

• DISTINCT가 집합 함수와 함께 사용되면 집합 함수 인자로 전달된 칼럼값이 유니크한 것들을 가져온다.

SELECT COUNT(DISTINCT s.salary)
FROM employees e, salaries s
WHERE e.emp_no=s.emp_no
AND e.emp_no BETWEEN 10001 AND 10100;

• 위 쿼리의 경우 두 테이블을 조인한 결과에서 salary 칼럼 값만 저장하기 위한 임시 테이블을 만들어 사용한다.
  • Using temporary를 표시하지 않지만 말이다.
  • 이 임시 테이블의 salary 칼럼에는 유니크 인덱스가 생기기에 레코드 건수가 많아진다면 상당히 느려질 수 있다.
• 하지만 인덱스가 존재하는 칼럼에 대해 DISTINCT를 처리할 때는 인덱스 풀 스캔 또는 레인지 스캔하면서 임시 테이블 없이 최적화를 수행할 수 있다.

SELECT COUNT(DISTINCT emp_no) FROM employees;