이전 포스팅에서 filesort와 filesort 발생시 문제점에 대해서 알아보았다. 이번 포스팅에서는 정렬 연산을 생략하기 위해 인덱스를 활용하는 방법을 알아보려고 한다.
정렬 연산 생략 및 부분 범위 처리 원리
결론부터 말하자면, ORDER BY 조건 컬럼에 인덱스를 생성해서 정렬 연산을 생략할 경우, 부분 범위 처리가 가능해진다. 인덱스 트리의 자료구조를 이해하고 있다면 이는 당연한 현상으로 받아들여질 테지만, 이로인해 어느 정도의 성능 개선을 이루어낼 수 있는지 체감하기 위해 예시를 통해 확인해보려 한다.
예시 쿼리
SELECT * FROM USER
ORDER BY USER.age DESC
LIMIT 10;온라인 서비스에서 발생하는 트랜잭션에서 위와 같은 쿼리를 처리한다고 가정했을 때, USER 테이블의 age 컬럼에 인덱스가 있는 경우와 없는 경우를 나누어 분석해보겠다.
age 컬럼에 인덱스가 없는 경우
age 컬럼에 인덱스가 없는 경우의 실행계획이다.
Extra 컬럼에서 Using filesort라는 키워드를 확인할 수 있다. 이를 통해 sort_buffer에서 정렬 연산이 수행되었다는 것을 알 수 있다.
전체 비용과 실행 시간의 대부분이 Sort 과정에서 발생하고 있다. 또한, 10개의 레코드를 반환하기 위해서 USER테이블 전체를 스캔하고 있다.
age 컬럼에 인덱스가 있는 경우
예상대로 Extra 컬럼에서 인덱스 트리를 Backward index scan한 것을 확인할 수 있다.
앞서 인덱스가 없는 경우에 확인할 수 있었던 Sort 키워드 또한 생략 되었으며, age_index를 딱 10개만 스캔하여 부분범위 처리또한 하고 있는 것을 알 수 있다.
age 컬럼의 값에 따라 정렬된 인덱스 트리를 내림차순으로 순회하였으며, LIMIT 10으로 결과 집합 개수를 제한했기 때문에 10개의 row만 읽고 바로 결과를 반환하고 있다.
페이징의 중요성
ORDER BY 조건절의 컬럼에 인덱스를 생성했을 때, 정렬 연산을 생략할 수 있는 것을 확인했다. 정렬 연산을 생략함으로 인해 명확한 성능 개선이 이루어졌다. 또한 위 실험 계획의 결과 차이는 한 가지 더 중요한 점을 시사한다.
부분 범위 처리로 인한 성능 개선이다. 첫번째 실행계획과 두 번째 실행 계획의 레코드 스캔 방식에서 차이가 있는데, 10개의 레코드를 얻기 위해 Table full scan을 하는 것과 인덱스 트리를 탐색해서 10개의 레코드만 탐색하는 것은 당연히 차이가 날 수 밖에 없다. 이 부분의 비용차이가 실행 계획에서도 확연히 나타난다.
결론
age컬럼에 인덱스가 있는 경우와 없는 경우를 비교해보았을 때. cost는 약 36,066배, 실행 속도는 약 104배 개선된 결과가 나왔다.
물론, 인덱스가 없는 상황에서의 쿼리도 11ms의 빠른 실행속도를 보이기 때문에 인덱스를 생성하는게 굳이? 라는 생각이 들 수 는있다. 하지만 비교적 소규모의 테이블에서 이루어진 실행 계획이기 때문에 대규모의 테이블일 경우에는 더 효과적인 개선이 이루어질 것이라고 예상된다.
이처럼,ORDER BY 절의 조건절에 해당하는 컬럼에 인덱스가 생성되어있을 경우 정렬 연산자체를 생략할 수 있으며, 온라인 서비스의 트랜잭션에서 자주 발생하는 LIMIT 10과 같은 쿼리에 대해서 부분범위처리가 가능해진다. 이는 명백히 성능상의 이점을 가져다 줄 것이다.
