그루핑 GROUP BY

1. GROUP BY 처리

GROUP BY 절이 있는 쿼리에서는 HAVING 절을 사용할 수 있는데 HAVING 절은 GROUP BY 결과에 대한 필터링 역할을 수행한다. GROUP BY에 사용된 조건은 인덱스를 사용할 수 없기 때문에 HAVING 절을 튜닝하려고 고민할 필요는 없다.

GROUP BY 작업 구분

인덱스 사용 여부	작업 방법
인덱스 사용 가능	인덱스 스캔 루스 인덱스 스캔
인덱스 사용 불가능	임시 테이블

 

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

ORDER BY 처리와 마찬가지로 조인의 드라이빙 테이블에 속한 컬럼만 이용해 그루핑할 때 인덱스가 있다면 그 인덱스를 차레대로 읽으면서 그루핑 작업을 수행하고 그 결과와 조인을 처리한다. 이 경우라도 그룹 함수 등의 그룹값을 처리해야 하는 경우는 임시 테이블이 필요할 때도 있다. 이 방식이 사용될 때는 임시 테이블을 사용했더라도 실행 계획의 Extra 컬럼에 아무런 코멘트가 표시되지 않는다.

 

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

루스(Loose) 인덱스 스캔은 인덱스의 레코드를 필요한 부분만 뛰엄뛰엄 읽어서 가져오는 것을 의미하는데 실행 계획의 Extra 컬럼에 "Using index for group-by" 코멘트가 표시된다. 다음 쿼리를 살펴보자.

EXPLAIN 
 SELECT emp_no
   FROM salaries
  WHERE from_date='1985-03-01'
  GROUP BY emp_no

salaries 테이블은 PK (emp_no, from_date), INDEX(salary) 이렇게 2개의 인덱스가 생성되어 있다. 위의 쿼리에서 WHERE 조건은 인덱스 레인지 스캔을 이용할 수 없는 쿼리지만 아래 실행 계획을 살펴보면 인덱스 레인지 스캔을 이용하였고 GROUP BY 처리까지 루스 인덱스 스캔을 사용한 것을 알 수 있다.

MySQL 서버가 위 쿼리를 실행한 순서를 살펴보자.

1. (emp_no, from_date) PK 인덱스를 차례대로 스캔하면서 emp_no의 첫 번째 유일한 값(그룹 키) '10001'을 찾아낸다.
2. PK 인덱스에서 emp_no가 '10001'인 것 중 from_date 값이 '1985-03-01'만 가져온다. 이 검색 방법은 WHERE emp_no = '10001' AND from_date = '1985-03-01' 조건으로 PK 인덱스를 검색한 것과 흡사하다.
3. PK 인덱스에서 emp_no의 다음 유일한 값(그룹 키) 값을 가져온다.
4. 3번 과정에서 다음 유일한 값이 있다면 2번 과정으로 돌아가서 반복 수행하고 없다면 탐색을 종료한다.

인덱스 레인지 스캔에서는 유니크한 값의 수가 많을수록(기수성이 높을수록) 성능이 향상되지만 루스 인덱스 스캔에서는 인덱스의 유니크한 값의 수가 적을수록 성능이 향상된다. (띄엄띄엄 읽는 횟수가 줄어들기 때문) 즉, 루스 인덱스 스캔은 분포도가 좋지 않은 인덱스일수록 더 빠른 결과를 만들어내고 임시테이블이 필요하지 않다.

루스 인덱스 스캔이 사용될 수 있을지 없을지 판단하는 것은 어렵다. 여러 패턴의 쿼리를 살펴보고 루스 인덱스 스캔을 사용할 수 있는지 판별하는 연습을 해보자. 아래 쿼리로 테이블을 생성하고 더미 데이터가 1,000 건이 있다고 가정하자. 아래 SELECT 쿼리들은 루스 인덱스 스캔을 사용할 수 있는 쿼리와 안 되는 쿼리다. 쿼리의 패턴을 유심히 보길 바란다.

CREATE TABLE `group_by_test` (
      `col1` BIGINT(19) NOT NULL
    , `col2` BIGINT(19) NOT NULL
    , `col3` BIGINT(19) NOT NULL
    , PRIMARY KEY (`col1`, `col2`, `col3`)
);

-- 루스 인덱스 스캔을 사용할 수 있는 쿼리
SELECT col1, col2 FROM group_by_test GROUP BY col1, col2;
SELECT col1, col2 FROM group_by_test WHERE col2 = const GROUP BY col1, col2;
SELECT col1, col2 FROM group_by_test WHERE col3 = const GROUP BY col1, col2;
SELECT DISTINCT col1, col2 FROM group_by_test;
SELECT col1, MIN(col2) FROM group_by_test GROUP BY col1;
SELECT col1, col2 FROM group_by_test WHERE col1 < const GROUP BY col1, col2;
SELECT MIN(col3), MAX(col3) FROM group_by_test WHERE col2 > const GROUP BY col1, col2;
SELECT MIN(col3), MAX(col3) FROM group_by_test WHERE col3 > const GROUP BY col1, col2;
SELECT col1, col2 FROM group_by_test WHERE col1 < 3 GROUP BY col1, col2;


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

-- GROUP BY에 사용된 컬럼의 순서가 인덱스 컬럼의 왼쪽부터 일치하지 않음
SELECT col2, col3 FROM group_by_test GROUP BY col2, col3;

-- SELECT 절의 컬럼이 GROUP BY와 일치하지 않기 때문에 사용 불가
SELECT col1, col3 FROM group_by_test GROUP BY col1, col2;

 

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

GROUP BY의 기준 컬럼이 인덱스를 전혀 사용할 수 없을 때 이 방식으로 처리된다. 아래 쿼리를 살펴보자.

EXPLAIN 
 SELECT E.last_name, AVG(S.salary)
   FROM employees E
  INNER JOIN salaries S
     ON E.emp_no = S.emp_no
  GROUP BY E.last_name;

이 쿼리의 실행 계획에서 Extra 컬럼에 "Using temporary" 메시지가 표시됐다. 인덱스를 사용할 수 없는 GROUP BY이기 때문에 임시 테이블이 사용되었다. MySQL 8.0 이전 버전까지는 GROUP BY가 사용된 쿼리는 그루핑 컬럼 기준으로 묵시적인 정렬까지 함께 수행했지만 8.0부터는 정렬하지 않는다. 그래서 이전 버전은 Extra 컬럼에 "Using temporary; Using filesort" 이렇게 메시지가 표시되었다. 그래서 이전 버전에서 그루핑 후 정렬이 필요없다면 'ORDER BY NULL' 쿼리를 추가하면 약간의 성능 향상을 기대할 수 있다.

MySQL8.0 서버는 내부적으로 GROUP BY 절의 칼럼들로 구성된 유니크 인덱스를 가진 임시 테이블을 만들어 중복 제거와 집합 함수 연산을 수행한다. 즉 위의 쿼리를 처리하기 위해 MySQL 서버는 다음과 같은 임시 테이블을 생성한다. 그리고 조인의 결과를 한 건씩 가져와 임시 테이블에서 중복 체크를 하면서 INSERT 또는 UPDATE를 실행한다. 즉 별도의 정렬 작업 없이 GROUP BY가 처리되지만 ORDER BY가 함께 사용되면 명시적으로 정렬 작업을 실행한다. 

CREATE TEMPORARY TABLE 임시테이블명 (
      last_name varchar(16)
    , salary int
    , UNIQUE INDEX ux_lastname(last_name)
);

 

5. DISTINCT 처리

5.1 SELECT DISTINCT

단순히 SELECT되는 레코드 중에서 유니크한 레코드만 가져올 때는 SELECT DISTINCT 형태의 쿼리 문장을 사용한다. 이 경우에는 GROUP BY와 동일한 방식으로 처리된다. 특히 MySQL 8.0 버전부터는 GROUP BY를 수행하는 쿼리에 ORDER BY 절이 없으면 정렬을 사용하지 않기 때문에 아래 두 쿼리는 내부적으로 같은 작업을 수행한다.

SELECT DISTINCT emp_no FROM salaries;

SELECT emp_no FROM salaries GROUP BY emp_no;

그리고 DISTINCT는 SELECT에 작성한 컬럼 기준으로 유니크한 레코드를 가져온다. 아래 쿼리를 살펴보자.

-- SELECT (1)
SELECT DISTINCT first_name FROM employees;

-- SELECT (2)
SELECT DISTINCT first_name, last_name FROM employees;

SELECT (1)은 first_name 컬럼 기준으로 중복을 제거한 레코드를 조회하고, SELECT (2)는 first_name, last_name 컬럼 기준으로 중복을 제거한 레코드를 조회한다.

 

5.2 집합 함수와 함께 사용된 DISTINCT

집합 함수가 없는 SELECT 쿼리에서 DISTINCT는 조회하는 모든 칼럼의 조합이 유니크한 레코드만 가져온다. 하지만 집합 함수 내에서 사용된 DISTINCT는 그 집합 함수의 인자로 전달된 칼럼을 중복제거를 한 결과값을 사용하게 된다.

-- 찾은 행: 942
SELECT COUNT(s.salary)
  FROM employees e
 INNER JOIN salaries s
    ON e.emp_no = s.emp_no
 WHERE e.emp_no BETWEEN 100001 AND 100100;


-- 찾은 행: 904
SELECT COUNT(DISTINCT s.salary)
  FROM employees e
 INNER JOIN salaries s
    ON e.emp_no = s.emp_no
 WHERE e.emp_no BETWEEN 100001 AND 100100;    

-- 실행계획 (1)
EXPLAIN 
SELECT COUNT(DISTINCT s.salary)
  FROM employees e
 INNER JOIN salaries s
    ON e.emp_no = s.emp_no
 WHERE e.emp_no BETWEEN 100001 AND 100100;

위 실행계획의 쿼리를 보면 내부적으로 "COUNT(DISTINCT s.salary)"를 처리하기 위해 임시 테이블을 사용할 수 밖에 없다. 하지만 지금까지 모든 MySQL 버전에서 이와같은 실행계획에서 임시 테이블을 사용한다는 "Using temporary" 메시지를 Extra 컬럼에 표시하지는 않았다. 특이한 경우라도 생각된다. 

SELECT COUNT(DISTINCT s.salary)
     , COUNT(DISTINCT e.last_name)
  FROM employees e
 INNER JOIN salaries s
    ON e.emp_no = s.emp_no
 WHERE e.emp_no BETWEEN 100001 AND 100100;

위 쿼리의 실행계획도 실행계획(1)과 결과가 같다. COUNT함수가 두 번 사용되었고 s.salary, e.last_name 칼럼은 인덱스가 없기 때문에 임시 테이블이 각각 2개 만들어 사용하게 된다.

SELECT COUNT(DISTINCT emp_no) 
  FROM employees;
  
SELECT COUNT(DISTINCT emp_no) 
  FROM dept_emp
 GROUP BY dept_no;

위 쿼리는 DISTINCT 처리를 위해 인덱스를 활용할 수 있기 때문에 인덱스 풀 스캔하거나 인덱스 레인지 스캔해서 임시 테이블 없이 최적화된 처리를 수행한다.