into MySQL: GROUP BY

레이블이 GROUP BY인 게시물을 표시합니다. 모든 게시물 표시

2011년 1월 3일 월요일

DISTINCT 와 GROUP BY의 차이

DISTINCT는 주로 UNIQUE한 컬럼이나 튜플(레코드)을 조회하는 경우 사용되며,
GROUP BY는 데이터를 그룹핑해서 그 결과를 가져오는 경우 사용되는 쿼리 형태이다.

하지만 두 작업은 조금만 생각해보면 동일한 형태의 작업이라는 것을 쉽게 알 수 있으며,
일부 작업의 경우 DISTINCT로 동시에 GROUP BY로도 처리될 수 있는 쿼리들이 있다.
그래서 DISTINCT를 사용해야 할지, GROUP BY를 사용해서 데이터를 조회하는 것이
좋을지 고민되는 경우들이 가끔 있다.

간단하게 아래 예를 살펴 보자
1. SELECT DISTINCT fd1 FROM tab;
2. SELECT DISTINCT fd1, fd2 FROM tab;

위의 두개 쿼리는 간단히 GROUP BY로 바꿔서 실행할 수 있다.
1. SELECT fd1 FROM tab GROUP BY fd1;
2. SELECT fd1, fd2 FROM tab GROUP BY fd1, fd2;

그렇다면 이 예제의 쿼리에서 DISTINCT와 GROUP BY 는 어떤 부분이 다를까 ?
사실 이런 형태의 DISTINCT는 내부적으로 GROUP BY와 동일한 코드를 사용한다.
즉, 동일한 처리를 하게 된다는 것이다.

하지만 더 중요한 차이가 있다.
DISTINCT의 결과를 정렬된 결과가 아니지만, GROUP BY는 정렬된 결과를 보내준다.
GROUP BY의 작업을 크게 "그룹핑" + "정렬"로 나누어서 본다면, DISTINCT는 "그룹핑" 작업만
수행하고 "정렬" 작업은 수행하지 않는 것이다.
그런데, 여기서 "정렬"은 "그룹핑" 과정의 산물이 아닌 부가적인 작업이다.

최종적으로, 이 예제의 DISTINCT와 GROUP BY는 일부 작업은 동일하지만 GROUP BY는
"정렬"을 하기 위한 부가적인 작업을 더 하게 된다.

만약 "정렬"이 필요하지 않다면 DISTINCT를 사용하는 것이 성능상 더 빠르다고 볼 수 있다.
하지만, GROUP BY를 사용하는 경우에는 정렬을 하지 않도록 유도할 수 있다.
(자세한 내용은 "GROUP BY의 Filesort 작업 제거"를 참조)

참고로
GROUP BY와 DISTINCT는 각자 고유의 기능이 있다.

DISTINCT로만 가능한 기능
1. SELECT COUNT(DISTINCT fd1) FROM tab;
-- // 이런 형태의 쿼리는 서브 쿼리를 사용하지 않으면 GROUP BY로는 작성하기 어렵다.

GROUP BY로만 가능한 기능
1. SELECT fd1, MIN(fd2), MAX(fd2) FROM tab GROUP BY fd1;
-- // 이렇게 집합함수(Aggregation)가 필요한 경우에는 GROUP BY를 사용해야 한다.

<<주의사항>>
가끔 어떤 사용자는 DISTINCT가 마치 함수인 것처럼 (괄호를 사용하여) 아래와 같이 사용을 하는데
만약 fd1 컬럼은 unique 값, fd2는 전체 값을 원한다면 절대 그 결과를 얻을 수 없다.

SELECT DISTINCT(fd1), fd2 FROM tab;

SELECT 문장에 DISTINCT라는 키워드가 있으면, MySQL은 SELECT되는 모든 컬럼(튜플)들에 대해서 DISTINCT를 적용해서 결과를 보내주게 된다.
위와 같은 요건을 처리하기 위해서도 아래와 같이 GROUP BY로만 해결할 수 있다.

SELECT fd1, fd2 FROM tab GROUP BY fd1;

2010년 12월 25일 토요일

GROUP BY 결과의 Roll up 기능

MySQL 에서는 GROUP BY 결과에 대한 RollUp을 처리해주는 기능이 있다.

+-------+-----------------------+------+-----+---------+----------------+

+-------+-----------------------+------+-----+---------+----------------+

| id | mediumint(9) | NO | PRI | NULL | auto_increment |

| age | tinyint(3) unsigned | YES | MUL | NULL | |

| sex | enum('MALE','FEMALE') | YES | MUL | NULL | |

+-------+-----------------------+------+-----+---------+----------------+

예를 들어서, 위 테이블에서 sex와 age 컬럼으로 GROUP BY를 실행하고,

실행 결과를 아래 3가지 케이스로 모두 다 조회하고자 하는 경우에는 Rollup 기능을 사용하면 쉽게 구할 수 있다.

성별 및 나이별 합계
성별 합계
전체 합계

select sex, truncate(age/10,0), count(*) from user group by sex, truncate(age/10,0) with rollup;

-- // truncate 함수는 단순히 나이를 10 단위로 자르기 위해서 사용한 것이지, Rollup 기능과는 무관함

+--------+--------------------+----------+

| sex | truncate(age/10,0) | count(*) |

+--------+--------------------+----------+

| MALE | 0 | 198 |

| MALE | 1 | 232 |

| MALE | 2 | 282 |

| MALE | 3 | 267 |

| MALE | 4 | 242 |

| MALE | 5 | 193 |

| MALE | 6 | 273 |

| MALE | 7 | 228 |

| MALE | 8 | 210 |

| MALE | 9 | 246 |

| MALE | 10 | 42 |

| MALE | NULL | 2413 | <== 성별(남자) 합계

| FEMALE | 0 | 233 |

| FEMALE | 1 | 237 |

| FEMALE | 2 | 310 |

| FEMALE | 3 | 258 |

| FEMALE | 4 | 232 |

| FEMALE | 5 | 198 |

| FEMALE | 6 | 265 |

| FEMALE | 7 | 270 |

| FEMALE | 8 | 218 |

| FEMALE | 9 | 228 |

| FEMALE | 10 | 54 |

| FEMALE | NULL | 2503 | <== 성별(여자) 합계

| NULL | NULL | 4916 | <== 전체 합계

+--------+--------------------+----------+

위의 결과를 보면,

빨간색으로 된 결과들이 Rollup된 결과 레코드들이며,

파란색의 NULL 값들을 보면 알 수 있겠지만, Rollup 된 컬럼의 필드값은 NULL로 표기된다.

단, Rollup의 사용은 아래와 같은 제한 사항을 가지고 있다.

Rollup이 ORDER BY와 함께 사용될 수는 없으며,
Rollup이 완료된 이후 LIMIT 절이 수행이 되므로, LIMIT절이 같이 사용될 경우 결과 해석이 상당히 어려울 수 있다.

GROUP BY의 Filesort 작업 제거

다른 DBMS와는 달리,

MySQL은 GROUP BY 를 실행하면 GROUP BY 대상 컬럼을 기준으로

GROUP BY를 실행한 후, 정렬 작업까지 같이 수행하도록 구현되어 있다.

이러한 자동 정렬 기능이 때로는 필요할 수도 있고, 필요치 않은 경우도 많이 있지만

특별히 이를 제어할 수 있는 방법이 공유되지 않았기 때문에 정렬 작업이 필요치 않은 경우에도

불필요한 정렬 작업을 같이 실행시키고 있는 경우가 많았다.

간단히 GROUP BY 작업에 대한 실행 계획을 확인해보자.

root@localhost:test>explain select * from user group by name ;

+----+-------------+-------+------+..------+..------+---------------------------------+

+----+-------------+-------+------+..------+..------+---------------------------------+

| 1 | SIMPLE | user | ALL |.. NULL |.. 5288 | Using temporary; Using filesort |

+----+-------------+-------+------+..------+..------+---------------------------------+

user 테이블의 name 컬럼에는 인덱스가 없으므로 GROUP BY 시에 Temporary 테이블을 생성하는 것은 피할 수 없다.

하지만, Filesort 작업은 ... 어떻게 하면 이 정렬 작업을 빼고 Grouping 작업만 할 수 있을까?

해결 방법은 GROUP BY 절 뒤에 ORDER BY NULL을 붙혀주면 된다.

아래 쿼리 문장의 실행 계획을 확인해보자

root@localhost:test>explain select * from user group by name order by null;

+----+-------------+-------+------+..------+..------+-----------------+

+----+-------------+-------+------+..------+..------+-----------------+

| 1 | SIMPLE | user | ALL |.. NULL |.. 5288 | Using temporary |

+----+-------------+-------+------+..------+..------+-----------------+

실행 계획의 Extra 컬럼에서 Filesort 조작이 실행되지 않았음을 알 수 있다.

이는 SQL 표준이라기 보다는 MySQL에서만 특이하게 작동되는 방식으로,

MySQL 개발사에 사용자들의 의견을 수렵하여 추가로 구현한 회피책 같은 것으로 생각하면 될 것 같다.

실제로 정렬 작업까지 수행하는 GROUP BY 와 정렬을 수행하지 않고 GROUPING만 하는 작업은

업무 성격에 따라서 매우 큰 성능 차이를 보이는 경우도 많다.

업무적으로 GROUPING만 필요한 것인지, 정렬도 필요한 것인지를 명확히 따져서

필요한 작업만 수행하도록 하는 것이 최선으로 보인다.