GROUP BY, ORDER BY 인덱스 튜닝

GROUP BY 인덱스 태우기

• GROUP BY 절에 명시된 칼럼이 인덱스 칼럼의 순서와 위치가 같아야 한다.
• 인덱스를 구성하는 칼럼 중에서 뒷쪽에 있는 칼럼은 GROUP BY 절에 명시되지 않아도 인덱스를 사용할 수 있지만 인덱스의 앞쪽에 있는 칼럼이 GROUP BY 절에 명시되지 않으면 인덱스를 사용할 수 없다.
• GROUP BY 절에 명시된 칼럼이 하나라도 인덱스에 없으면 GROUP BY 절은 전혀 인덱스를 사용하지 못한다.

예시

• 인덱스 칼럼 순서 : col1, col2, col3, col4
• WHERE 조건은 없다

... GROUP BY col1 # 사용 가능
... GROUP BY col1, col2 # 사용 가능
... GROUP BY col1, col2, col3 # 사용 가능
... GROUP BY col1, col2, col3, col4 # 사용가능

• WHERE 조건은 없다

... GROUP BY col2, col1 # 순서 불일치, 사용 불가능
... GROUP BY col1, col3, col2 # 순서 불일치, 사용 불가능
... GROUP BY col1, col3 # 순서는 일치하나, col2가 누락되서 사용 불가능
... GROUP BY col1, col2, col3, col4, col5 # col5는 인덱스에 들어있지 않아서 사용 불가능

• WHERE 조건 하나 - 아래 경우는 인덱스 앞의 칼럼으로 한 번 걸려졌기 때문에 뒤의 칼럼부터 GROUP BY 절에 있어도 인덱스를 사용할 수 있다.

... WHERE col1 = '상수' GROUP BY col2, col3 # 사용 가능
... WHERE col1 = '상수', col2 = '상수' GROUP BY col3, col4 # 사용 가능

ORDER BY 인덱스 태우기

ORDER BY 절의 인덱스 사용 여부는 GROUP BY 요건과 거의 흡사하다. 하지만 ORDER BY는 조건이 하나 더 있는데, 정렬되는 각 칼럼의 오름차순(ASC) 및 내림차순(DESC) 옵션이 인덱스와 같거나 또는 정반대인 경우에만 사용할 수 있다.

예시

• 인덱스 칼럼 순서 : col1, col2, col3, col4
• 아래는 모두 인덱스를 사용할 수 없는 예시이다.

ORDER BY col2, col3 # col1이 누락
ORDER BY col1, col3, col2 # 순서 불일치
ORDER BY col1, col2 DESC, col3 # 중간에 DESC가 껴서 불가
ORDER BY col1, col3 # col2 누락
ORDER BY col1, col2, col3, col4, col5 # col5는 인덱스에 없어서 불가

WHERE 조건과 ORDER BY || WHERE 조건과 GROUP BY

아래의 ORDER BY를 GROUP BY로 바꿔봐도 동일하다.

WHERE절과 ORDER BY 절이 동시에 같은 인덱스 이용

• 아래 두 조건보다 훨씬 빠르다
• 만약 다른 인덱스를 각각 쓴다면, 아래 두 방법 중 하나로 처리될 것이다 (실행계획 확인 필요)

WHERE 절만 인덱스를 사용

• ORDER BY 절은 인덱스를 쓸 수 없고 WHERE 절은 인덱스를 쓸 수 있을 때
• 인덱스를 통해 검색된 결과 레코드를 별도의 정렬 처리 과정(FileSort)을 거쳐 정렬을 수행한다. 주로 이 방법은 WHERE 절의 조건에 일치하는 레코드의 건수가 많지 않을때 효과적이다

ORDER BY 절만 인덱스를 사용

• ORDER BY 절은 인덱스를 쓸 수 있지만, WHERE 절은 인덱스를 못 쓸때
• ORDER BY 절의 순서대로 인덱스를 읽으며, 레코드 한 건씩을 WHERE 절의 조건에 일치하는지 비교해 일치하지 않을때 버리는 형태로 처리. 아주 많은 레코드를 조회해서 정렬해야 할 때는 이런 형태로 튜닝하기도 한다.

GROUP BY + ORDER BY

GROUP BY 절에 명시된 칼럼과 ORDER BY에 명시된 칼럼이 순서와 내용이 모두 같아야 한다. GROUP BY와 ORDER BY가 같이 사용된 쿼리에서는 둘 중 하나라도 인덱스를 이용할 수 없을때는 둘다 인덱스를 사용하지 못한다.

MySQL의 GROUP BY는 ORDER BY 칼럼에 대한 정렬까지 함께 수행하는 것이 기본 작동 방식이므로 GROUP BY와 ORDER BY 칼럼이 내용과 순서가 같은 쿼리에서는 ORDER BY 절을 생략해도 같은 결과를 얻게 된다.

최종조합 : WHERE + GROUP BY + ORDER BY

출처 : Real MySQL, 거의 MySQL의 교과서...?