[MySQL] Group by 와 Distinct 의 성능 (Using filesort)

점검하면서 쿼리를 작성 중 재밌는 일이 생겨서 공유해 본다.

select gender from employees group by gender;

select distinct(gender) from employees;

둘 다 gender의 상태(??갑자기 설명이 안되네;;;) 를 확인하기 위해서 동료가 group by로 작성하는 것을 보았다.

순간...왜 저렇게 해야되는 걸까? 간단하게 distinct 를 하면 되지 않느냐로 내가 질문하면서 궁금해서 찾아보게 되었다.

난 성능적인 차이가 더 궁금해서 찾아본 결과 다음과 같이 확인할 수 있었다.

동일한 로직을 이용하기에 성능의 차이는 없을 거지만,

group by는 정렬를 하기에 distinct가 조금 더 성능적으로 빠르다.

라는 결과를 얻을 수 있었다.

하지만 이거는 어디까지나 사람들의 의견만 보았다.

그래서 간단하게 테스트를 아래와 같이 진행 하였다.

아직 MySQL 의 쿼리 튜닝할 정도의 실력이 아니기에..간단하게 explain 으로 확인 하였다.

보는바와 같이 동일한 건수를 참조하며, 마지막에 Using filesort 라는 것을 확인할 수 있다.

Using filesort

쿼리에 ORDER BY가 포함되어있는 경우 DBMS 내부적으로 sort를 한다.(MySQL의 경우 퀵소트 알고리즘을 사용)

소트를 처리하기 위해서 메모리공간을 사용하며, 메모리가 부족할 경우 임시파일을 사용한다.

(임시파일은 HDD등의 저장장치에 생성되며 PC에서 가장 느린 성능을 보여주는 부분이다. 

-> 결과적으로 EXPLAIN시 Extra필드에 Using filesort가 표시된다면 이는 파일을 사용할 가능성이 크다는 것이며, 

가장 느린성능을 보여줄것이다.)

하지만 인덱스 순으로 행을 fetch할수 있으면 Filesort는 필요없게된다. 

이런경우 당연히 Extra필드에도 Using Filesort가 표시되지 않는다.

ORDER BY 구문에 인덱스가 사용되도록 쿼리나 테이블을 튜닝하자.

여기까지는 하나의 테이블에서 ORDER BY를 사용한 경우이다.

2개 이상의 테이블을 JOIN해야 하며 결과는 정렬되어 있어야 하는경우엔 어떻게 될까?

이경우엔 3가지 유형이 있다.

1. 첫 테이블을 인덱스를 이용하여 정렬하고, JOIN하는 경우

2. 첫 테이블을 FileSort해서 Join하는 경우 -> Using filesort

3. JOIN후 Filesort하는 경우(JOIN한 결과는 인덱스가 없으므로 JOIN후 Sort할때는 항상 Filesort이다.) -> Using Temporary, Using filesort

 

뒤로 갈수록 처리가 무거워 지므로 1,2번의 실행계획이 잡히도록 유도하는 것이 좋다.

1번을 유도하는 방법 -> WHERE절의 검색조건과 ORDER BY절의 소트조건을 하나의 테이블에 집중시킨다.

출처 : http://blog.daum.net/billbo/11

비록 적은 중복값에 대해서는 차이가 없을지 모르겠으나 그 값이 많아 진다면 group by로 해서 확인하는 것은 부하를 줄 수도 있을 것 같다.

자세한 Filesort 내용은 아래의 위치에서 확인해 보자

http://www.mysqlkorea.com/sub.html?mcode=manual&scode=01_1&m_no=22505&cat1=827&cat2=963&cat3=980&lang=k

sort를 하는 부분은 tmep를 사용하기에 가급적 sort할 필요가 없다면 distinct 를 사용해서 확인하는 것이 좋을 듯 싶다.

오랜만에 아키텍처까지 한번 더 볼 수 있는 좋은 기회였다.