본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.
ORDER BY 절의 인덱스 사용
MySQL에서 GROUP_BY와 ORDER BY는 처리 방법이 상당히 비슷하여 인덱스 사용 여부 요건도 거의 흡사하다. 하지만 ORDER BY는 조건이 하나 더 있는데, 정렬되는 각 칼럼의 오름차순 및 내림차순 옵션이 인덱스와 같거나 정반대의 경우에만 사용할 수 있다.
위와 같은 인덱스에서 다음 예제의 ORDER BY는 인덱스를 사용할 수 없다. ( ORDER BY 절에 ASC나 DESC와 같이 정렬 순서가 생략되면 오름차순(ASC)로 해석한다. )
... ORDER BY COL2, COL3
> 인덱스 제일 앞쪽 컬럼인 COL1이 ORDER BY에 명시 되지 않음
... ORDER BY COL1, COL3, COL2
> 인덱스와 ORDER BY 절의 칼럼 순서가 일치하지 않음
... ORDER BY COL1, COL2 DESC, COL3
> 다른 칼럼은 모두 오름차순인데, 두 번째 칼럼만 정렬 순서가 내림차순
... ORDER BY COL1, COL3
> COL2 칼럼이 명시 되지 않음
... ORDER BY COL1, COL2, COL3, COL4, COL5
> 인덱스에 존재하지 않는 COL5가 명시됨.
WHERE 조건과 ORDER BY(또는 GROUP BY) 절의 인덱스 사용
쿼리에 WHERE 절을 가지고 있으면서 선택적으로 ORDER BY나 GROUP BY 절을 포함시킬 수 있다. 쿼리에 WHERE 절만 또는 GROUP BY나 ORDER BY 절만 포함돼 있다면 사용된 절 하나에만 인덱스 사용할 수 있도록 튜닝하면 된다.
SQL 문장이 WHERE 절과 ORDER BY 절을 가진다면, WHER 조건은 A 인덱스를 사용하고 ORDER BY는 B인덱스를 사용하는 것은 불가능하다. 이는 WHERE 절과 GROUP BY 절이 같이 사용된 경우 GROUP BY와 RODER BY가 같이 사용된 쿼리에서도 마찬가지다.
WHERE 절과 ORDER BY 절이 같이 사용된 쿼리 문장은 다음 3가지중 한가지 방법으로만 인덱스를 이용한다.
1) WHERE 절과 ORDER BY 절이 동시에 같은 인덱스를 이용
> WHERE 절의 비교 조건에 사용하는 칼럼과 ORDER BY 절의 정렬 대상 칼럼이 모두 하나의 인덱스에 연속해서 포함되어 있을 때 이 방식으로 인덱스를 사용할 수 있다. 나머지 2가지 방법보다 빠른 성능을 보이기 때문에 이 방식으로 처리할 수 있도록 쿼리를 튜닝하거나 인덱스를 생성하는 것이 좋다.
2) WHERE 절만 인덱스를 이용
> ORDER BY 절은 인덱스를 이용한 정렬이 불가능하며, 인덱스를 통해 검색된 결과 레코드를 정렬 처리 과정(Filesort) 를 거쳐 정렬을 수행한다. 보통 WHERE 절의 조건에 일치하는 레코드 건수가 많지 않을 때 효율적인 방식이다.
3) ORDER BY 절만 인덱스를 이용
> ORDER BY 절은 인덱스를 이용해 처리하지만 WHERE 절은 인덱스를 이용하지 못한다. 이 방식은 ORDER BY 절의 순서대로 인덱스를 읽으면서, 레코드 한 건씩을 WHERE 절의 조건에 일치하는지 비교해 일치하지 않을 때는 버리는 형태로 처리한다.
WHERE 절에서 동등 비교 조건으로 비교된 칼럼과 ORDER BY 절에 명시된 칼럼이 순서대로 빠짐없이 인덱스 칼럼의 왼쪽부터 일치해야 한다. 중간에 빠지는 칼럼이 있드면 WHERE 절이나 ORDER BY 절 모두 인덱스를 이용할 수 없다. 이때는 주로 WHERE 절만 인덱스를 이용할 수 있다.
위 그림은 WHERE 과 ORDER BY 절이 결합된 두 가지 패턴의 쿼리를 표현한 것이다. 그림 오른쪽과 같이 ORDER BY 절에 해당 칼럼이 사용되고 있다면 WHERE 절에 동등 비교 이외의 연산자로 비교돼도 WHERE 조건과 ORDER BY 조건이 모두 인덱스를 이용할 수 있다. 위 그림의 왼쪽 패턴 쿼리 예제는 다음과 같다.
SELECT *
FROM tb_test
WHERE col1=10 ORDER BY col2, col3;
얼핏 보면 ORDER BY 절의 칼럼 순서가 인덱스 칼럼 순서와 달라 정렬할 때 인덱스를 이용하지 못할 것 같아 보인다. 이럴 때는 ORDER BY에 인덱스의 첫번째 칼럼인 COL1 칼럼을 포함해서 쿼리를 작성해보자.
SELECT *
FROM tb_test
WHERE col1=10 ORDER BY col1, col2, col3;
위 쿼리는 WHERE 조건이 상수로 동등 비교를 하고 있어서 ORDER BY 절에 col1 칼럼이 추가되어도 정렬 순서에는 변화가 없다. 즉 변경되기 이전의 쿼리와 같지만 WHERE 절과 ORDER BY 절이 동시에 인덱스를 이용할 수 있는지 여부를 더 쉽게 판단할 수 있다.
GROUP BY나 ORDER BY가 인덱스를 사용할 수 있을지 없을지 모호할 때는 쿼리를 변경한 쿼리와 원본 쿼리가 같은 순서나 결과를 보장하는지 확인해 보면 된다.
WHERE 조건절에서 범위 조건의 비교가 사용되는 쿼리를 살펴보자
SELECT * FROM tb_test WHERE col1 > 10 ORDER BY co1, co2, col3;
SELECT * FROM tb_test WHERE col1 > 10 ORDER BY co2, col3;
두 번째 쿼리의 경우 ORDER BY 절에 col1 이 명시되지 않아 정렬할 때 인덱스를 이용할 수 없다.
GROUP BY 절과 ORDER BY 절의 인덱스 사용
GROUP BY와 ORDER BY 절이 동시에 사용된 쿼리에서 두 절이 모두 하나의 인덱스를 사용해서 처리되려면 GROUP BY 절에 명시된 칼럼과 ORDER BY에 명시된 칼럼이 순서와 내용이 모두 같아야 한다. GROUP BY와 ORDER BY가 같이 사용된 쿼리에서는 둘 중 하나라도 인덱스를 이용할 수 없을 때는 둘다 인덱스를 사용하지 못한다. 두 절중 하나라도 인덱스를 이용할 수 없을 때 모두 인덱스를 이용하지 못한다. 그 반대의 경우도 마찬가지다.
... GROUP BY COL1, COL2 ORDER BY COL2
... GROUP BY COL1, COL2 ORDER BY COL1, COL3
WHERE 조건과 ORDER BY 절, 그리고 GROUP BY 절의 인덱스 사용
WHERE, GROUP BY, ORDER BY 절이 모두 포함된 쿼리가 인덱스를 사용하는지 판단하는 방법은 아래의 그림을 적용해 보면 된다.
- WHERE 절이 인덱스를 사용할 수 있는가?
- GROUP BY 절이 인덱스를 사용할 수 있는가?
- GROUP BY 절과 ORDER BY 절이 동시에 인덱스를 사용할 수 있는가?
