대부분의 SELECT 쿼리는 정렬이 필요한 요건으로 구성되어있다. DB엔진에서 정렬은 그 유무와 처리 방식에 따라 시간적 비용 + 공간적 비용 차이가 크게 나타난다. 그렇기 때문에 MySQL을 기준으로 어떤 상황에서 정렬 연산이 발생하는지 이해하고, 이를 튜닝하는 방법에 대해 알아보려고 한다.
MySQL의 정렬 처리 방식
MySQL은 다음 두 가지 방법을 통해서 정렬을 처리한다.
- 인덱스를 이용한 정렬 연산 생략
- Filesort
인덱스는 특유의 자료구조 특성에 의해서 정렬 연산 자체를 생략할 수 있다. MySQL 옵티마이저는 인덱스를 통한 정렬 연산 생략이 가능한 실행계획에 더 높은 점수를 부여해서 선택하도록 구성되어있다.
다만 모든 정렬 조건에 대해서 인덱스를 생성하는 것은 불가능하기 때문에, Filesort의 특성을 이해해야 정렬 연산에 대한 튜닝이 가능할 것이다. 이에 대한 특성과 원리를 이해하는 것이 이번 포스팅의 목표다.
Filesort
추가 메모리 공간을 할당 받아 정렬 연산을 수행하는 방식
Filesort로 정렬을 처리할 경우, MySQL은 정렬을 수행하기 위해 메모리 공간을 할당 받는다. 이 메모리 공간을 sort buffer라고 하며, sort_buffer_size라는 MySQL 서버의 시스템 변수 값을 할당 받을 수 있는 최대 메모리 크기로 두고, 가변적으로 메모리를 할당받는다.
sort_buffer_size
MySQL 서버 내 시스템 변수로 최대 4GB까지 할당이 가능하다. Filesort를 위해서 할당하는 크기를 설정하는데 사용되며, 일반적인 트랜잭션을 처리하는 MySQL 서버의 적절한 sort_buffer_size는 56KB에서 1MB 미만이 좋다고 한다. 이외의 크기를 할당하면 성능상 크게 차이가 없는 것으로 보여진다.
Filesort의 문제점
여기서 Filesort의 문제점이 발생한다. 정렬 대상 레코드들의 크기가 sort_buffer_size를 초과할 경우 어떤 일이 발생할까?
MySQL 공식문서에 따르면, 정렬에 할당된 메모리 공간보다 더 큰 공간이 필요할 경우 임시 디스크 파일을 사용하게 되는데, 이는 Filesort의 속도를 저하시키는 원인중 하나가 된다.
정렬 연산은 모두 sort buffer에서 처리되는데 대상 레코드가 할당된 sort buffer 크기를 넘을 경우, 정렬된 임시 결과 집합을 디스크에 저장해두고 그 다음 집합을 다시 정렬하여 디스크에 있는 임시 집합과 결합을 하는 과정을 반복하면서 모든 레코드에 대한 정렬을 처리한다.
디스크를 이용하기 때문에 임시 집합과 새로 정렬된 집합을 결합하는 과정에서 물리적 I/O가 발생하게 된다. (물리적 I/O가 논리적 I/O에 비해서 느린 이유는 해당 포스팅에서 설명한 바 있다.) 그렇기 때문에 정렬 연산의 대상 레코드 수가 많을수록 인덱스를 통해서 연산을 생략하는 경우와 비교해서 비용 차이가 커진다는 것을 짐작할 수 있다.
또한, 부분범위 처리가 불가능하다. 가령 LIMIT 10으로 정렬된 결과 집합의 상위 10개 레코드를 반환하도록 쿼리를 작성해도, 정작 정렬을 Filesort로 처리했을 때 모든 레코드를 읽고 정렬한 다음에야 원하는 10개의 레코드를 반환할 수 있다. 이는 인덱스를 통해서 정렬 연산을 생략할 경우 부분범위 처리를 할 수 있는 부분과 대비되는 점이다.
Filesort가 발생하는 경우
Filesort가 왜 문제가 되는지 알아보았다. 그렇다면, Filesort는 어떤 상황에서 발생하는 걸까? 앞서 MySQL 옵티마이저는 인덱스를 통해 정렬 연산을 생략하는 실행계획을 우선적으로 채택한다고 하였다. 그렇다면, Filesort가 발생하는 경우는 인덱스를 통한 정렬 연산의 생략이 불가능한 경우에 발생한다. 즉, 정렬 연산에 필요한 인덱스가 없거나, 모종의 이유로 활용이 불가능한 경우에 Filesort가 발생한다.
크게 다음과 같은 경우로 나눌 수 있다.
-
ORDER BY절 처리 시 조건 컬럼에 인덱스가 없을 경우 -
GROUP BY절 처리 시 결과 집합을 위해 -
DISTINCT절 처리 : 중복된 결과를 제거하기 위해서 정렬이 필요하게 된다. -
랜덤한 결과 집합 반환
-
ABS()와 같은 연산 결과에 대한ORDER BY: 함수 결과에 대한 정렬을 수행해야 하기 때문에 인덱스가 있어도 컬럼에 대한 가공처리가 되어 인덱스를 이용할 수 없게 된다. 이 경우 Filesort가 발생한다.
마무리
이번 포스팅에서는 정렬 연산 중에서 Filesort의 특성과 문제점, 그리고 발생하는 요인에 대해서 알아보았다. 모든 상황에 인덱스를 활용할 수 없기 때문에 Filesort에 대한 튜닝을 공부하는 것은 반드시 필요하다고 생각한다. 다만, 레코드가 적은 경우에는 Filesort와 인덱스를 이용한 정렬 연산을 생략 하는 것에 큰 차이가 없을 수도 있고, 인덱스는 구조 자체로 사이드 이펙트가 발생하기 때문에 상황에 따라서 적절하게 사용하는 것이 좋아보인다.
다음 포스팅에서는 정렬 연산이 포함된 쿼리를 튜닝하는 방법에 대해서 알아보겠다.
참고
