본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.
GROUP BY 처리
GROUP BY 또한 ORDER BY와 같이 쿼리가 스트리밍된 처리를 할 수 없게 하는 요소 중 하나다. GROUP BY 절이 있는 쿼리에서는 HAVING 절을 사용할 수 있는데 HAVING 절은 GROUP BY 결과에 대해 필터링 역할을 수행한다. 일반적으로 GROUP BY 처리 결과는 임시 테이블이나 버퍼에 존재하는 값을 필터링하는 역할을 수행한다. GROUP BY 에 사용된 조건은 인덱스를 사용해서 처리될 수 없으므로 HAVING 절을 튜닝하려고 인덱스를 생성하거나 다른 방법을 고민할 필요가 없다.
GROUP BY 또한 인덱스를 사용하는 경우와 그렇지 않은 경우로 나눠 볼 수 있다. 인덱스를 이용할 경우 인덱스를 차례대로 이용하는 인덱스 스캔 방법과 인덱스를 건너뛰면서 읽는 루스 인덱스 스캔이라는 방법으로 나뉜다. 인덱스를 사용하지 못하는 쿼리에서 GROUP BY 작업은 임시 테이블을 사용한다.
인덱스 스캔을 이용하는 GROUP BY(타이트 인덱스 스캔)
조인의 드라이빙 테이블에 속한 칼럼만 이용해 그룹핑할 때 GROUP BY 칼럼으로 이미 인덱스가 있다면 인덱스를 차례대로 읽으면서 그룹핑 작업을 수행하고 그 결과로 조인을 처리한다. GROUP BY가 인덱스를 사용해서 처리된다 하더라도 그룹 함수 (Aggregation function) 등의 그룹값을 처리해야 해서 임시 테이블이 필요할 때도 있다.
루스(loose) 인덱스 스캔을 이용하는 GROUP BY
루스 인덱스 스캔 방식은 인덱스의 레코드를 건너뛰면서 필요한 부분만 가져오는 것을 의미한다. 실행 계획의 "Using index for group-by" 가 Extra에 표시된다.
EXPLAIN
SELECT emp_no
FROM salaries
WHERE from_date='1985-03-01'
GROUP BY emp_no;
salaries 테이블의 인덱스는 (emp_no + from_date)로 생성돼 있으므로 위의 쿼리 문장에서 WHERE 조건은 인덱스 레인지 스캔 접근 방식으로 이용할 수 없는 쿼리다. 하지만 쿼리의 실행계획은 인덱스 레인지 스캔(range 타입)을 이용했으며, Extra 칼럼의 메시지를 보면 GROUP BY 처리까지 인덱스를 사용했다는 것을 알 수 있다.
MySQL 서버가 쿼리를 어떻게 실행했는지 순서대로 살펴보자.
1. (emp_no + from_date) 인덱스를 차례대로 스캔하면서, emp_no 의 첫번째 유일한 값(그룹 키) "10001"을 찾아낸다.
2. (emp_no + from_date) 인덱스에서 emp_no가 "10001"인 것 중에서 from_date 값이 "1985-03-01" 인 레코드만 가져온다. 이 검색 방법은 1번 단계에서 알아낸 "10001" 값과 쿼리의 WHERE 절에 사용된 "from_date='1985-03-01'" 조건을 합쳐서 AND 조건으로 인덱스를 검색하는 것과 거의 흡사하다.
3. emp_no + from_date 인덱스에서 emp_no 의 그 다음 유니크한 (그룹 키) 값을 가져온다.
4. 3번 단계에서 결과가 더 없으면 처리를 종료하고, 결과가 있으면 2번 과정으로 돌아가서 반복 수행한다.
인덱스 레인지 스캔에서는 유니크한 값의 수가 많을수록 성능이 향상되는 반면 루스 인덱스 스캔에서는 인덱스의 유니크한 값이 적을수록 성능이 향상된다. 즉, 루스 인덱스 스캔은 분포도가 좋지 않은 인덱스일수록 더 빠른 결과를 만들어낸다. 루스 인덱스 스캔으로 처리되는 쿼리에서는 별도의 임시 테이블이 필요하지 않다.
임시 테이블을 사용하는 GROUP BY
GROUP BY의 기준 칼럼이 드라이빙 테이블에 있든 드리븐 테이블에 있든 관계없이 인덱스를 전혀 사용하지 못할 때는 이 방식으로 처리된다.
EXPLAIN
SELECT e.last_name, AVG(s.salary)
FROM employees e, salaries s
WHERE s.emp_name=e.emp_no
GROUP BY e.last_name;
이 쿼리의 실행 계획에서 Extra 칼럼에 Using temporary 와 Using filesort 메시지가 표시됐다. 이 실행 계획에서 임시 테이블이 사용된 것은 employees 테이블을 풀 스캔(ALL)하기 때문이 아니라 인덱스를 전혀 사용할 수 없는 GROUP BY 때문이다.
1. Employees 테이블을 풀 테이블 스캔 방식으로 읽는다.
2. 1번 단계에서 읽은 employees 테이블의 emp_no 값을 이용해 salaries 테이블을 검색한다.
3. 2번 단계에서 얻은 조인 결과 레코드를 임시 테이블에 저장한다. 이 임시 테이블에서 중요한 것은 GROUP BY 절에 사용된 칼럼으로 유니크 키를 생성한다는 점이다.
4. 1번 단계부터 3번 단계까지 조인이 완료될 때까지 반복한다. 임시 테이블의 유니크 키 순서대로 읽어서 클라이언트로 전송된다. ORDER BY 절에 명시된 칼럼과 GROUP BY 절에 명시된 칼럼이 같으면 별도의 정렬 작업을 수행하지 않는다. ORDER BY 절과 GROUP BY 절에 명시된 칼럼이 다르면 Filesort 과정을 거치면서 다시 한번 정렬 작업을 수행한다.
