레코드 1~2건을 가져오는 쿼리를 제외하면 대부분의 SELECT쿼리에서 정렬은 필수적으로 사용된다. 정렬을 처리하는 방법은 인덱스를 이용하는 방법과 쿼리가 실행될 때 Filesort라는 별도의 처리를 이용하는 방법으로 나눌 수 있다.

레코드를 정렬하기 위해 항상 Filesort 정렬 작업을 거쳐야 하는 것은 아니나, 모든 정렬을 인덱스를 이용하도록 튜닝하는 것은 거의 불가능하다.

• 정렬 기준이 너무 많아 요건별로 모두 인덱스를 생성하는 것이 불가능한 경우
• GROUP BY의 결과 또는 DISTINT같은 처리의 겨로가를 정렬해야 하는 경우
• UNION의 결과와 같이 임시 테이블의 결과를 다시 정렬해야 하는 경우
• 랜덤하게 결과 레코드를 가져와야 하는 경우

MySQL 서버에서 인덱스를 이용하지 않고 별도의 정렬 처리를 수행했는지는 실행 계획의 Extra컬럼에 Using filesort 메시지가 표시되는지 여부로 판단할 수 있다.

MySQL의 정렬 특성을 이해하면 쿼리를 튜닝할 때 어떠헥 하면 조금이라도 더 빠른 쿼리가 될지 쉽게 판단할 수 있을것이다.

소트 버퍼

MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아 사용하는데, 이 메모리 공간을 소트 버퍼라고 한다.

소트 버퍼는 정렬이 필요한 경우에만 할당되며, 버퍼의 크기는 정렬해야 할 레코드의 크기에 따라 가변적으로 증가하지만 최대 사용 가능한 소트 버퍼의 공간은 sort_buffer_size 시스템 변수로 설정할 수 있다. 소트 버퍼를 위한 메모리 공간은 쿼리의 실행이 완료되면 즉시 시스템으로 반납된다.

정렬해야할 레코드가 적어 메모리에 할당된 소트 버퍼만으로 정렬할 수 있다면 빠르게 정렬이 처리되지만, 레코드 건수가 소트 버퍼로 할당된 공간보다 크면 정렬해야 할 레코드를 여러 조각으로 나누어 처리하게된다.

이 과정에서 메모리의 소트 버퍼에서 정렬을 수행하고 그 결과를 임시로 디스크에 기록하는 과정을 반복한다. 이처럼 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행해야 하며(멀티 머지, Multi-merge) 수행된 멸티 머지 횟수는 Sort_merge_passes라는 상태 변수에 누적해서 집계된다.

이러한 작업들이 모두 디스크의 쓰기와 읽기를 유발하게 되어 레코드 건수가 많을수록 반복 작업 횟수도 많아진다.

소트 버퍼를 크게 설정하면 디스크를 사용하지 않아 더 빨라질 것으로 생각할 수 있지만, 실제 벤치마크 결과로는 큰 차이를 보이지 않는다. 그리고 리눅스 계열의 운영체제에서는 너무 큰 소트 버퍼 크기를 사용하는 경우, 큰 메모리 공간 할당 때문에 성능이 훨씬 떨어질 수 있다.

또한 소트 버퍼는 세션 메모리 영역을 사용하여 여러 클라이언트가 공유해서 사용할 수 없다. 커넥션이 많을수록, 정렬 작업이 많을수록 소트 버퍼로 소비되는 메모리 공간이 커져 운영체제에서 메모리 부족 현상이 발생할 수 있다.

소트 버퍼를 설정해서 빠른 성능을 얻을 수는 없지만 디스크의 읽기와 쓰기 사용량은 줄일 수 있다. 따라서 MySQL 서버의 데이터가 많거나 디스크 I/O 성능이 낮은 장비라면 소트 버퍼의 크기를 더 크게 설정하는 것이 도움이 될 수도 있다.

정렬 알고리즘

레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담을지 또는 정렬 기준 컬럼만 소트 버퍼에 담을지에 따라 싱글 패스, Single-pass와 투 패스, Two-pass 2가지 정렬 모드로 나눌 수 있다.

정렬을 수행하는 쿼리가 어떤 정렬 모드를 사용하는지는 다음과 같이 옵티마이저 트레이스 기능으로 확인할 수 있다.

1
2
3
4
5
6

SET OPTIMIZER_TRACE="enabled=on", END_MARKERS_IN_JSON=on;
SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;

SELECT * FROM employees ORDER BY last_name LIMIT 100000, 1;

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE \G

마지막 쿼리 실행 후 출력된 내용에서 “filesort_summary” 섹션의 “sort_algorithm” 필드에 정렬 알고리즘이 표시되고, “sort_mode” 필드에는 3가지 정렬 방식중 하나가 표시된다.

• <sort_key, rowid>: 정렬 키와 레코드의 로우 아이드만 가져와서 정렬하는 방식
• <sort_key, additional_fields>: 정렬 키와 레코드 전체를 가져와서 정렬하는 방식으로, 레코드의 컬럼들은 고정 사이즈로 메모리에 저장
• <sort_key, packed_additional_fields>: 정렬 키와 레코드 전체를 가져와서 정렬하는 방식으로, 레코드의 컬럼들은 가변 사이즈로 메모리 저장

여기서는 첫 번째 방식을 투 패스정렬 방식이라 명명하고, 나머지 방식을 싱글 패스정렬 방식이라고 명명하겠다. 세 번째 방식은 MySQL 5.7 버전부터 도입됐는데, 이는 정렬을 위한 메모리 공간의 효율적인 사용을 위해 추가 도입된 방식이다.

싱글 패스 정렬 방식

소트 버퍼에 정렬 기준 컬럼을 포함해 SELECT 대상이 되는 컬럼 전부를 담아 정렬을 수행하는 방식이다.

1
2
3

SELECT emp_no, first_name, last_name
FROM employees
ORDER BY first_name;

위 쿼리와 같이 first_name으로 정렬해서 emp_no, first_name, last_name을 SELECT하는 쿼리를 싱글 패스 정렬 방식으로 처리하게 되면, 처음 employees 테이블을 읽을 때 정렬에 필요하지 않는 last_name 컬럼까지 전부 읽어 소트 버퍼에 담고 정렬을 수행한다. 그리고 정렬이 완료되면 정렬 버퍼의 내용을 그대로 클라이언트로 넘겨준다.

투 패스 정렬 방식

정렬 대상 컬럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행하고, 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT할 컬럼을 가져오는 정렬 방식으로, 싱글 패스 정렬 방식이 도입되기 이전부터 사용하던 방식이다.

처음 employees 테이블을 읽을 때는 정렬에 필요한 first_name컬럼과 프라이머리 키인 emp_no만 읽어서 정렬을 수행한다. 이 정렬이 완료되면 그 결과 순서대로 employees 테이블을 한번 더 읽어서 last_name을 가져오고, 최종적으로 그 결과를 클라이언트 쪽으로 넘기는 과정을 확인할 수 있다.

정리

MySQL 예전 정렬 방식인 투 패스 방식은 테이블을 두 번 읽어야 하기 때문에 상당히 불합히하지만, 새로운 정렬 방식인 싱글 패스는 이러한 불합리가 없다. 하지만 싱글 패스 정렬 방식은 더 많은 소트 버퍼 공간이 필요하다.

최신 버전에서는 일반적으로 싱글 패스 정렬 방식을 주로 사용하지만 경우에 따라 투 패스 정렬 방식을 이용할 수 있다.

• 레코드의 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 클 때
• BLOB이나 TEXT 타입의 컬럼이 SELECT 대상에 포함할 때

싱글 패스 방식은 정렬 대상 레코드의 크기나 건수가 작은 경우 빠른 성능을 보이며, 투 패스 방식은 정렬 대상 레코드의 크기나 건수가 상당히 많은 경우 효율적이라고 볼 수 있다.

SELECT 쿼리에서 꼭 필요한 컬럼만 조회하지 않고, 모든 컬럼을 가져오도록 개발하는 경우가 많은데, 이는 정렬 버퍼를 몇 배에서 몇십 배 까지 비효율 적으로 사용할 가능성이 크므로 정렬이 필요한 SELECT는 불필요한 컬럼을 포함하지 않게 쿼리를 작성하는 것이 효율적이다.

정렬 처리 방법

쿼리에 ORDER BY가 사용되면 반드시 다음 3가지 처리 방법 중 하나로 정렬이 처리된다. 일반적으로 아래쪽에 있는 정렬 방법으로 갈수록 처리 속도는 떨어진다.

옵티마이저는 정렬 처리를 위해 인덱스를 이용할 수 있을지 검토하는데, 사용할 수 있다면 “filesort” 과정 없이 인덱스를 순서대로 읽어 반환한다. 하지만 인덱스를 사용할 수 없다면 WHERE 조건에 일치하는 레코드를 검색해 정렬 버퍼에 저장하면서 정렬을 처리한다.

이때 MySQL 옵티마이저는 정렬 대상 레코드를 최소화 하기 위해 다음 2가지 방법 중 하나를 선택한다.

• 조인의 드라이빙 테이블만 정렬한 다음 조인을 수행
• 조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬을 수행

일반적으로 조인이 수행되면서 레코드 건수와 레코드의 크기는 거의 배수로 불어나기 때문에 가능하다면 드라이빙 테이블만 정렬한 다음 조인을 수행하는 방법이 효율적이다. 그래서 두 번째 방법보다는 첫 번째 방법이 더 효율적으로 처리된다.

인덱스를 이용한 정렬

• 인덱스를 이용한 정렬을 위해서는 반드시 ORDER BY에 명시된 컬럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 한다.
• WHERE절에 첫 번째로 읽는 테이블의 컬럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 한다.
• B-Tree 계열의 인덱스가 아닌 해시 인덱스나 전문 검색 인덱스 등에서는 인덱스를 이용한 정렬을 사용할 수 없다. (R-Tree 인덱스 불가)
• 여러 테이블이 조인되는 경우에는 네스티드-루프(Nested-loop) 방식의 조인에서만 사용할 수 있다.

인덱스를 이용해 정렬이 처리되는 경우에는 실제 인덱스의 값이 정렬돼 있기 때문에 인덱스의 순서대로 읽기만 하면 되어 MySQL 엔진에서 별도의 정렬을 위한 추가 작업을 수행하지는 않는다.

하지만 조인이 사용된 쿼리의 실행 계획에 조인 버퍼가 사용되면 순서가 흐트러 질 수 있으므로 주의해야 한다.

ORDER BY 절을 넣지 않아도 자동으로 정렬되므로 ORDER BY 자체를 쿼리에서 제거하기도 하지만, MySQL 서버는 정렬을 인덱스로 처리할 수 있는 경우 부가적으로 불필요한 정렬 작업을 수행하지 않기 때문에 ORDER BY가 쿼리에 명시된다고 해서 작업량이 늘지는 않는다. 그리고 알 수 없는 이유로 실행 계획이 조금 변경된다면, 기대했던 순서대로 결과를 가져오지 못해 버그로 연결될 수 있으므로 ORDER BY 절을 명시하는 것이 좋다.

조인의 드라이빙 테이블만 정렬

일반적으로 조인이 수행되면 결과 레코드의 건수가 몇 배로 불어나고, 레코드 하나의 크기도 늘어나기 때문에 조인을 실행하기 전 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행하는 것이 정렬의 차선책이다. 이러한 방법으로 정렬이 처리되려면 조인에서 첫 번째로 읽히는 테이블(드라이빙 테이블)의 컬럼만으로 ORDER BY 절을 작성해야 한다.

1
2
3
4
5
6

SELECT *
FROM employees e, salaries s 
WHERE s.emp_no = e.emp_no
    AND e.emp_no BETWEEN 100002 AND 100010
ORDER BY e.last_name
;

WHERE 절의 조건으로 인해 옵티마이저는 employees 테이블을 드라이빙 테이블로 선택한다.

• WHERE 절의 검색조건 emp_no는 employees 테이블의 파리이머리 키를 이용해 검색하면 작업량을 줄일 수 있다.
• 드리븐 테이블의 조인 컬럼인 emp_no 컬럼에 인덱스가 있다.

검색은 인덱스 레인지 스캔으로 처리할 수 있지만 ORDER BY 절에 명시된 컬럼은 employees 테이블의 프라이머리 키와 전혀 연관이 없으므로 인덱스를 이용한 정렬은 불가능하지만, 정렬 기준 컬럼이 드라이빙 테이블에 포함된 컬럼이므로 옵티마이저는 드라이빙 테이블만 검색해서 정렬을 먼저 수행하고, 그 결과와 salaries 테이블을 조인하는 방식을 선택한다.

1. 인덱스를 이용해 BETWEEN 조건을 만족하는 데이터 검색
2. 검색 결과를 last_name 컬럼으로 정렬을 수행(Filesort)
3. 정렬된 결과를 순서대로 읽으며 sqlaries 테이블과 조인을 수행해 최종 결과를 가져옴

임시 테이블을 이용한 정렬

쿼리가 여러 테이블을 조인하지 않고, 하나의 테이블로 부터 SELECT해서 정렬하는 경우라면 임시 테이블이 필요하지 않지만, 2개 이상의 테이블을 조인해서 그 결과를 정렬해야 한다면 임시 테이블이 필요할 수 있다.

“조인 드라이빙 테이블만 정렬"하는 경우라면 2개 이상의 테이블이 조인되면서 정렬이 실행되지만 임시 테이블을 사용하지 않는다. 하지만 그 외 패턴의 쿼리에서는 항상 조인의 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬하는 과정을 거친다. 따라서 이 방법은 정렬 3가지 방식 중 정렬해야 할 레코드 건수가 가장 많기 때문에 가장 느리다.

1
2
3
4
5
6

SELECT *
FROM employees e, salaries s
WHERE s.emp_no = e.emp_no
  AND e.emp_no BETWEEN 100002 AND 100010
ORDER BY s.salary
;

쿼리 실행 계획 “Extra"컬럼에 “Using where; Using temporary; Using filesort"코멘트가 표시되는데, 이는 조인의 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬 처리했음을 의미한다.

정렬 처리 방법의 성능 비교

주로 웹 서비스용 쿼리에서는 ORDER BY와 함께 LIMIT이 거의 필수로 사용되는 경우가 많은데, 일반적으로 LIMIT은 테이블이나 처리 결과의 일부만 가져오기 때문에 MySQL 서버가 처리해야 할 작업량을 줄이는 역할을 한다.

하지만 ORDER BY, GROUP BY 같은 작업은 WHERE 족너을 만족하는 레코드를 LIMIT 건수 만큼만 가져와서는 처리할 수 없고, 우선 조건을 만족하는 레코드를 모두 가져와서 정렬을 수행하거나 그루핑 작업을 실행해야만 처리할 수 있다. 이로 인해 WHERE 조건이 아무리 인덱스를 잘 활용하도록 튜닝해도 잘못된 ORDER BY, GROUP BY 때문에 쿼리가 느려지는 경우가 많이 발생한다.

스트리밍 방식

서버 쪽에서 처리할 데이터가 얼마인지에 관계 없이 조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트로 전성해주는 방식으로, 클라이언트는 쿼리를 요청하고 곧바로 원했던 첫 번째 레코드를 전달받는다. (마지막 레코드는 언제 받을지 알 수 없다.)

쿼리가 스트리빙 방식으로 처리될 수 있다면 클라이언트는 MySQL 서버가 일치하는 레코드를 찾는 즉시 전달받기 때문에 동시에 데이터의 가공 작업을 시작할 수 있다. 웹서비스 같은 OLTP 환경에서는 쿼리의 요청에서 첫 번째 레코드를 전달받게 되기까지의 응답 시간이 중요한 경우가 많은데, 스트리빙 방식으로 처리되는 쿼리는 쿼리가 얼마나 많은 레코드를 조회하느냐에 상관없이 빠른 응답 시간을 보장해준다.

또한 스트리밍 방식으로 처리되는 쿼리에서 LIMIT 처럼 결과 건수를 제한하는 조건들은 풀 테이블 스캔의 결과가 아무런 버퍼링 처리나 필터링 과정 없이 바로 클라이언트로 스트리밍 되기 때문에 쿼리의 전체 실행 시간을 상당히 줄여줄 수 있다.

버퍼링 방식

ORDER BY, GROUP BY 같은 처리는 WHERE 조건에 일치하는 모든 레코드를 가져온 후, 정렬하거나 그루핑해서 차례대로 보내야 하므로 쿼리의 결과가 스트리밍되는 것이 불가능하다.

또한 버퍼링 방식으로 처리되는 쿼리는 먼저 결과를 모아서 MySQL 서버에서 일괄 가옹해야 하므로 모든 결과를 스토리지 엔진으로부터 가져올 때가지 기다려야 한다. 따라서 LIMIT 처럼 결과 건수를 제한하는 조건이 있어도 성능 향상에 별로 도움이 되지 않는다.

정렬 처리 방버에서 소개한 ORDER BY의 3가지 처리 방법 가운데 인덱스를 사용한 정렬 방식만 스트리밍 형태의 처리이며, 나머지는 모두 버퍼링된 후에 정렬된다. 즉 인덱스를 사용한 정렬 방식은 LIMIT로 제한된 건수만큼만 읽으면서 바로 클라이언트로 결과를 전송해 줄 수 있다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

/*
 tb_test1의 레코드는 100건
 tb_test2 테이블의 레코드가 1000건
    (tb_test1의 레코드 1건당 tb_test2의 레코드가 10건씩 존재함)
 */
SELECT *
FROM tb_test1 t1, tb_test2 t2 
WHERE t1.col1=t2.col1
ORDER BY t1.col2
LIMIT 10
;

tb_test1가 드라이빙되는 경우

tb_test2가 드라이빙되는 경우

어느 테이블이 먼저 조인되는지도 중요하지만 어떤 정렬 방식으로 처리되는지는 더 큰 성능 차이를 만든다. 가능하다면 인덱스를 사용한 정렬로 유도하고, 그렇지 못하다면 최소한 드라이빙 테이블만 정렬해도 되는 수준으로 유도하는 것도 좋은 튜닝 방법이라고 할 수 있다.

정렬 관련 상태 변수

MySQL 서버는 처리하는 주요 작업에 대해서는 해당 작업의 실행 횟수를 상태 변수로 저장한다. 정려로가 관련해서도 지금까지 몇 건의 레코드나 정렬 처리를 수행했는지, 소트 버퍼 간의 병합 작업(멀티 머지)은 몇 번이나 발생했는지 등을 다음과 같은 명령으로 확인해 볼 수 있다.

1
2
3

FLUSH STATUS;
SHOW STATUS LIKE 'Sort%'
;

• Sort_merge_passes: 멀티 머지 처리 횟수
• Sort_range: 인덱스 레인지 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수(누적)
• Sort_rows: 풀 테이블 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수(누적)
• Sort_rows: 지금까지 정렬한 전체 레코드 건수