MySQL 실행 계획 : MySQL의 주요 처리 방식(3)
3. GROUP BY 처리
GROUP BY 또한 ORDER BY와 같이 쿼리가 스트리밍된 처리를 할 수 없게 하는 요소 중 하나다. GROUP BY 절이 있는 쿼리에서는 HAVING 절을 사용할 수 있는데, HAVING 절은 GROUP BY 결과에 대해 필터링 역할을 수행한다. 일반적으로 GROUP BY 처리 결과는 임시 테이블이나 버퍼에 존재하는 값을 필터링하는 역할을 수행한다. GROUP BY에 사용된 조건은 인덱스를 사용해서 처리될 수 없으므로 HAVING 절을 튜닝하려고 인덱스를 생성하거나 다른 방법을 고민할 필요는 없다.
GROUP BY 작업도 인덱스를 사용하는 경우와 그렇지 못한 경우로 나눠 볼 수 있다. 인덱스를 이용할 때는 인덱스를 차례대로 이용하는 인덱스 스캔 방법과 인덱스를 건너뛰면서 읽는 루스 인덱스 스캔이라는 방법으로 나뉜다. 그리고 인덱스를 사용하지 못하는 쿼리에서 GROUP BY 작업은 임시 테이블을 사용한다.
인덱스 스캔을 이용하는 GROUP BY(타이트 인덱스 스캔)
ORDER BY의 경우와 마찬가지로 조인의 드라이빙 테이블에 속한 칼럼만 이용해 그룹핑할 때 GROUP BY 칼럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그룹핑 작업을 수행하고 그 결과로 조인을 처리한다. GROUP BY가 인덱스를 사용해서 처리된다 하더라도 그룹 함수(Aggregation function) 등의 그룹값을 처리해야 해서 임시 테이블이 필요할 때도 있다. GROUP BY가 인덱스를 통해 처리되는 쿼리는 이미 정렬된 인덱스를 읽는 것이므로 추가적인 정렬 작업은 필요하지 않다. 이런 그룹핑 방식을 사용하는 쿼리를 실행 계획에서는 Extra 칼럼에 별도로 GROUP BY 관련 코멘트(Using index for group-by)나 임시 테이블이나 정렬 관련 코멘트(Using temporary, Using filesort)가 표시되지 않는다.
루스(loose) 인덱스 스캔을 이용하는 GROUP BY
루스 인덱스 스캔 방식은 인덱스의 레코드를 건너뛰면서 필요한 부분만 가져오는 것을 의미한다. 루스 인덱스 스캔을 사용하는 다음 예제를 한번 살펴보자.
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 처리까지 인덱스를 사용했다는 것을 알 수 있다.
| id | select_type | table | type | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | salaries | range | PRIMARY | 7 | 568914 | Using where; Using index for group-by |
MySQL 서버가 이 쿼리를 어떻게 실행했는지, 순서대로 하나씩 살펴보자.
- (emp_no + from_date) 인덱스를 차례대로 스캔하면서, emp_no의 첫 번째 유일한 값(그룹 키) '10001'을 찾아낸다.
- (emp_no + from_date) 인덱스에서 emp_no가 '10001'인 것 중에서 from_date 값이 '1985-03-01'인 레코드만 가져온다. 이 검색 방법을 1번 단계에서 알아낸 '10001' 값과 쿼리의 WHERE 절에 사용된 "from_date='1985-03-01" 조건을 합쳐서 "emp_no=10001 AND from_date='1985-03-01" 조건으로 (emp_no + from_date) 인덱스를 검색하는 것과 거의 흡사하다.
- (emp_no + from_date) 인덱스에서 emp_no의 그 다음 유니크한(그룹 키) 값을 가져온다.
- 3번 단계에서 결과가 더 없으며 처리를 종료하고, 결과가 있다면 2번 과정으로 돌아가서 반복 수행한다.
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 co1, 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;다음의 쿼리는 루스 인덱스 스캔을 사용할 수 없는 쿼리 패턴이다.
-- // MIN()과 MAX() 이외의 집합 함수가 사용됐기 때문에 루스 인덱스 스캔은 사용 불가
SELECT col1, SUM(col2) FROM tb_test GROUP BY col1;
-- // GROUP BY에 사용된 칼럼이 인덱스 구성 칼럼의 왼쪽부터 일치하지 않기 때문에 사용 불가
SELECT col1, col2 FROM tb_test GROUP BY col2, col3;
-- // SELECT 절의 칼럼이 GROUP BY와 일치하지 않기 때문에 사용 불가
SELECT col1, col3 FROM tb_test GROUP BY co1, col2;일반적으로 B-Tree 인덱스는 인덱스를 구성하는 칼럼이 왼쪽부터 일치하는 형태로 사용될 때만 사용할 수 있다. 하지만 루스 인덱스 스캔은 인덱스의 첫 번째 칼럼이 WHERE 조건이나 GROUP BY에 사용되지 않아도 B-Tree 인덱스를 사용할 수 있는 방식이기도 하다. 오라클과 같은 DBMS에서는 옵티마이저가 인덱스의 첫 번째 칼럼에 대한 조건을 마음대로 만들어서 추가하는 형태로 이런 기능이 구현돼 있다. 하지만 지금 출시되는 MySQL 5.0, 5.1, 그리고 5.5에서는 아직 루스 인덱스 스캔이 이렇게 인덱스를 사용할 수 잇는 방법은 지원되지 않는다. MySQL의 루스 인덱스 스캔의 최적화는 아직 초기 수준이라고 볼 수 있다.
임시 테이블을 사용하는 GROUP BY
GROUP BY의 기준 칼럼이 드라이빙 테이블에 있든 드리븐 테이블에 있든 관계없이 인덱스를 전혀 사용하지 못할 때는 이 방식으로 처리된다. 다음 쿼리를 잠깐 살펴보자.
EXPLAIN
SELECT e.last_name, AVG(s.salary)
FROM employees e, salaries s
WHERE s.emp_no=e.emp_no
GROUP BY e.last_name;이 쿼리의 실행 계획에서는 Extra 칼럼에 "Using temporary"와 "Using filesort" 메시지가 표시됐다. 이 실행 계획에서 임시 테이블이 사용된 것은 employees 테이블을 풀 스캔(ALL)하기 때문에 아니라 인덱스를 전혀 사용할 수 없는 GROUP BY이기 때문이다.
| id | select_type | table | type | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | e | ALL | 300584 | Using temporary; Using filesort |
|||
| 1 | SIMPLE | s | ref | PRIMARY | 4 | e.emp_no | 4 |
아래 그림은 이 실행 계획의 처리 절차를 표현해 둔 것이다.
- Employees 테이블을 풀 테이블 스캔 방식으로 읽는다.
- 1번 단계에서 읽은 employees 테이블의 emp_no 값을 이용해 salaries 테이블을 검색한다.
- 2번 단계에서 얻은 조인 결과 레코드를 임시 테이블에 저장한다. 이 단계에서 사용되는 임시 테이블은 원본 쿼리에서 GROUP BY 절에 사용된 칼럼과 SELECT하는 칼럼만 저장한다. 이 임시 테이블에서 중요한 것은 GROUP BY 절에 사용된 칼럼으로 유니크 키를 생성한다는 점이다. 즉, GROUP BY가 임시 테이블로 처리되는 경우 사용되는 임시 테이블은 항상 유니크 키를 가진다.
- 1번 단계부터 3번 단계를 조인이 완료될 때까지 반복한다. 조인이 완료되면 임시 테이블의 유니크 키 순서대로 읽어서 클라이언트로 전송된다. 만약, 쿼리의 ORDER BY 절에 명시된 칼럼과 GROUP BY 절에 명시된 칼럼이 같으면 별도의 정렬 작업을 수행하지 않는다. ORDER BY 절과 GROUP BY 절에 명시된 칼럼이 다르다면 Filesort 과정을 거치면서 다시 한번 정렬 작업을 수행한다.
4. DISTINCT 처리
특정 칼럼의 유니크한 값만을 조회하려면 SELECT 쿼리에 DISTINCT를 사용한다. DISTINCT는 MIN(), MAX() 또는 COUNT()와 같은 집합 함수와 함께 사용되는 경우와 집합 함수가 없는 경우로 두 가지로 구분해서 살펴보자. 이렇게 구분한 이유는 DISTINCT 키워드가 영향을 미치는 범위가 달라지기 때문이다. 그리고 집합 함수와 같이 DISTINCT가 사용되는 쿼리의 실행 계획에서 DISTINCT 처리가 인덱스를 사용하지 못할 때는 항상 임시 테이블이 필요하다. 하지만 실행 계획의 Extra 칼럼에는 "Using temporary" 메시지가 출력되지 않는다.
SELECT DISTINCT ...
단순히 SELECT 되는 레코드 중에서 유니크한 레코드만 가져오고자 하면 SELECT DISTINCT 형태의 쿼리 문장을 사용한다. 이 경우에는 GROUP BY와 거의 같은 방식으로 처리된다. 단지 차이는 SELECT DISTINCT의 경우에는 정렬이 보장되지 않는다는 것뿐이다. 다음의 두 쿼리는 정렬 관련 부분만 빼면 내부적으로 같은 작업을 수행한다. 그런데 사실 이 두 개의 쿼리는 모두 인덱스를 이용하기 때문에 부가적인 정렬 작업이 필요하지 않으며 완전히 같은 쿼리다. 하지만 인덱스를 이용하지 못하는 DISTINCT는 정렬을 보장하지 않는다.
SELECT DISTINCT emp_no FROM salaries;
SELECT emp_no FROM salaries GROUP BY emp_no;DISTINCT를 사용할 때 자주 실수하는 것이 있다. DISTINCT는 SELECT하는 레코드(튜플)를 유니크하게 SELECT하는 것이지 칼럼을 유니크하게 조회하는 것이 아니다. 즉, 다음 쿼리에서 SELECT 하는 결과는 first_name만 유니크한 것을 가져오는 것이 아니라 (fist_name + last_name) 전체가 유니크한 레코드를 가져오는 것이다.
SELECT DISTINCT first_name, last_name FROM employees;가끔 DISTINCT를 다음과 같이 사용할 때도 있다.
SELECT DISTINCT(first_name), last_name FROM employees;위의 쿼리는 얼핏 보면, first_name만 유니크하게 조회하고 last_name은 그냥 DISTINCT가 없을 때와 동일하게 조회하는 쿼리처럼 보인다. 그리고 실제로 상당히 그럴듯하게 아무런 에러 없이 실행되기 때문에 쉽게 실수할 수 있는 부분이다. 하지만 MySQL 서버는 DISTINCT 뒤에 괄호를 그냥 의미없이 사용된 괄호로 해석하고 제거해 버린다. DISTINCT는 함수가 아니므로 그 뒤의 괄호는 의미가 없는 것이다.
SELECT DISTINCT first_name, last_name FROM employees;SELECT 절에 사용된 DISTINCT 키워드는 조회되는 모든 칼럼에 영향을 미친다. 절대로 SELECT하는 여러 칼럼 중에서 일부 칼럼만 유니크하게 조회하는 방법은 없다. 단, 이어서 설명할 DISTINCT가 집합 함수 내에 사용된 경우는 조금 다르다.
집합 함수와 함께 사용된 DISTINCT
COUNT() 또는 MIN(), MAX()와 같은 집합 함수 내에서 DISTINCT 키워드가 사용될 수 있는데, 이 경우에는 일반적으로 SELECT DISTINCT와 다른 형태로 해석된다. 집합 함수가 없는 SELECT 쿼리에 서 DISTINCT는 조회하는 모든 칼럼의 조합이 유니크한 것들만 가져온다. 하지만 집합 함수 내에서 사용된 DISTINCT는 그 집합 함수의 인자로 전달된 칼럼 값이 유니크한 것들을 가져온다.
EXPLAIN
SELECT COUNT(DISTINCT s.salary)
FROM employees e, salaries s
WHERE e.emp_no=s.emp_no
AND e.emp_no BETWEEN 100001 AND 100100;이 쿼리는 내부적으로는 "COUNT(DISTINCT s.salary)"를 처리하기 위해 임시 테이블을 사용한다. 하지만 이 쿼리의 실행 계획에는 임시 테이블을 사용한다는 메시지는 표시되지 않는다. 이는 버그처럼 보이지만 MySQL 5.0, 5.1 그리고 5.5 모두 실행 계획의 Extra 칼럼에 "Using temporary"가 표시되지 않는다.
| id | select_type | table | type | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | e | range | PRIMARY | 4 | 100 | Using where; Using index |
|
| 1 | SIMPLE | s | ref | PRIMARY | 4 | e.emp_no | 4 |
위의 쿼리의 경우에는 employees 테이블과 salaries 테이블을 조인한 결과에서 salary 칼럼의 값만 저장하기 위한 임시 테이블을 만들어서 사용한다. 이때 임시 테이블의 salary 칼럼에는 유니크 인덱스가 생성되기 때문에 레코드 건수만 많아진다면 상당히 느려질 수 있는 형태의 쿼리다.
만약 이 쿼리에 COUNT(DISTINCT ...)를 하나 더 추가해서 다음과 같이 변경해보자. COUNT() 함수가 두 번 사용된 다음 쿼리의 실행 계획은 위의 쿼리와 똑같이 표시된다. 하지만 다음 쿼리를 처리하려면 s.salary 칼럼의 값을 저장하는 임시 테이블과 e.last_name 칼럼의 값을 저장하는 또 다른 임시 테이블이 필요하므로 전체적으로 2개의 임시 테이블을 사용한다.
SELECT COUNT(DISTINCT s.salary), COUNT(DISTINCT e.last_name)
FROM employees e, salaries s
WHERE e.emp_no=s.emp_no
AND e.emp_no BETWEEN 100001 AND 100100;위의 쿼리는 DISTINCT 처리를 위해 인덱스를 이용할 수 없어서 임시 테이블이 필요했다. 하지만 다음 쿼리와 같이 인덱스된 칼럼에 대해 DISTINCT 처리를 수행할 때는 인덱스를 풀 스캔하거나 레인지 스캔하면서 임시 테이블 없이 최적화된 처리를 수행할 수 있다.
SELECT COUNT(DISTINCT emp_no) FROM employees;
SELECT COUNT(DISTINCT emp_no) FROM dept_emp GROUP BY dept_no;| id | select_type | table | type | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | dept_ emp |
range | PRIMARY | 16 | 334242 | Using index |
DISTINCT가 집합 함수 없이 사용된 경우와 집합 함수 내에서 사용된 경우 쿼리의 결과가 조금씩 달라지기 때문에 그 차이가 정확하게 이해해야 한다. 다음 3개 쿼리의 차이를 잘 기억해 두자.
SELECT DISTINCT first_name, last_name FROM employees WHERE emp_no BETWEEN 10001 AND 10200;SELECT COUNT(DISTINCT first_name), COUNT(DISTINCT last_name) FROM employees WHERE emp_no BETWEEN 10001 AND 10200;SELECT COUNT(DISTINCT first_name, last_name) FROM employees WHERE emp_no BETWEEN 10001 AND 10200;
참고
- Real MySQL
