MySQL은 ORDER BY를 어떻게 처리할까 — 정렬 세 가지 경로와 Sort Buffer

ORDER BY 하나 붙였을 뿐인데 쿼리가 갑자기 느려진 경험, 한 번쯤 있으셨을 겁니다. 실행 계획에는 Using filesort가 보이고, 어디부터 손대야 할지 막막해지죠. 이 글은 MySQL 옵티마이저가 정렬을 처리하는 경로를 실무 관점에서 정리한 내용입니다. 같은 ORDER BY라도 어떤 경로를 타느냐에 따라 비용이 크게 달라지므로, 구조를 이해하면 “왜 느린가”와 “어떻게 고칠까”를 함께 설명할 수 있습니다.

정렬을 아예 안 하는 게 가장 빠르다

가장 먼저 알아야 할 사실은, MySQL이 가장 선호하는 정렬은 “정렬을 생략하는 것”이라는 점입니다.

인덱스는 이미 정렬된 자료구조입니다. B-Tree 인덱스는 키 순서대로 정렬되어 저장되므로, ORDER BY 대상 컬럼에 적절한 인덱스가 있으면 MySQL은 인덱스를 순서대로 읽기만 하면 됩니다. 별도의 정렬 작업이 전혀 필요 없습니다.

-- team_id, created_at 순서의 인덱스가 있다면
SELECT * FROM members
WHERE team_id = 5
ORDER BY created_at;

이 경우 실행 계획에 Using filesort가 나타나지 않습니다. 정렬 연산 자체가 실행 계획에서 사라지는 것이죠. 이것이 정렬 처리의 세 경로 중 첫 번째, 인덱스를 이용한 정렬입니다. 세 방식 중 일반적으로 가장 빠릅니다.

반대로 실행 계획에서 Using filesort가 보인다면, MySQL이 인덱스로 정렬 순서를 만들지 못해 별도 정렬 단계를 수행한다는 신호입니다. 이름 때문에 “디스크 정렬”로 오해하기 쉽지만, filesort는 정렬 알고리즘의 이름에 가깝고 실제 수행 위치는 메모리일 수도, 디스크를 동반할 수도 있습니다.

인덱스로 안 될 때: 나머지 두 경로

인덱스로 정렬 순서를 만족시킬 수 없을 때, MySQL은 filesort를 수행합니다. 이때 실무에서 자주 보는 경로는 아래 두 가지입니다.

드라이빙 테이블만 정렬하는 경로. 조인 쿼리에서 정렬 기준 컬럼이 조인의 첫 번째 테이블(드라이빙 테이블)에만 속하면, MySQL은 이 드라이빙 테이블을 먼저 정렬한 뒤 그 순서대로 조인을 이어갑니다. 정렬 대상이 한 테이블로 한정되므로 상대적으로 저렴합니다.

임시 테이블을 만들어 정렬하는 경로. 정렬 기준이 조인 결과 전체에 걸쳐 있거나, GROUP BY/DISTINCT로 중간 집계를 먼저 만들어야 하는 경우입니다. MySQL은 조인과 가공을 끝낸 결과를 임시 테이블에 담고 그 테이블을 정렬합니다. 정렬 대상이 가장 커지기 쉬워 세 경로 중 비용이 가장 높아지는 경우가 많습니다.

정리하면, 일반적으로 정렬 비용은 아래 순서로 커집니다.

1. 인덱스를 이용한 정렬 (정렬 작업 없음)
2. 드라이빙 테이블만 정렬
3. 임시 테이블을 만들어 정렬

튜닝의 방향은 명확합니다. 가능한 한 아래 경로를 위 경로로 끌어올리고, 최종적으로는 인덱스에서 정렬을 흡수시키는 것입니다.

정렬은 어디서 일어나는가 — Sort Buffer

실제 정렬 단계는 Sort Buffer라는 세션 메모리 영역을 활용해 수행됩니다. 정렬 대상이 이 버퍼에 수용되면 메모리에서 정렬을 끝낼 수 있고, 버퍼 크기는 sort_buffer_size로 제어합니다.

여기서 성능이 크게 갈립니다. 정렬 대상이 Sort Buffer 안에 다 들어가면 메모리에서 한 번에 정렬하고 끝납니다.

반대로 데이터가 Sort Buffer보다 크면, MySQL은 버퍼 단위로 나눠 정렬한 조각(run)을 만들고 이를 병합합니다. 이 과정에서 임시 파일 I/O가 늘어나면 체감 성능이 크게 떨어집니다. 즉, 느린 정렬의 본질은 Using filesort 자체보다 외부 병합(merge pass) 횟수 증가에 가깝습니다.

그래서 sort_buffer_size를 키우고 싶어지지만, 함정이 있습니다. Sort Buffer는 정렬 작업을 수행하는 세션별로 할당됩니다. 전역 값을 과도하게 키우면 동시성 높은 시간대에 메모리 사용량이 급증할 수 있습니다. 따라서 전역 값은 보수적으로 두고, 필요한 세션에서만 조정하는 편이 안전합니다. 근본 해법은 버퍼 확장보다 정렬 대상 자체를 줄이는 것(인덱스 활용, WHERE 조건 강화, 필요한 컬럼만 조회)입니다.

무엇을 버퍼에 담는가 — 싱글 패스 vs 투 패스

Sort Buffer와 관련해 한 겹 더 들어가면, MySQL에는 버퍼에 무엇을 담을지에 대한 두 가지 정렬 모드가 있습니다.

투 패스(two-pass) 방식. 정렬 키와 원본 행을 가리키는 값(rowid)만 버퍼에 담아 정렬합니다. <정렬 키, rowid> 형태죠. 정렬이 끝난 뒤, 정렬된 순서대로 rowid를 따라가 원본 테이블에서 나머지 컬럼을 다시 읽어옵니다. 버퍼에 적은 데이터만 담으니 더 많은 행을 메모리에서 정렬할 수 있지만, 원본을 다시 읽는 랜덤 I/O가 추가됩니다.

싱글 패스(single-pass) 방식. 정렬 키뿐 아니라 SELECT 결과에 필요한 컬럼을 통째로 버퍼에 담아 정렬합니다. <정렬 키, 나머지 컬럼 전부> 형태입니다. 정렬이 끝나면 버퍼 내용을 그대로 반환하면 되므로 원본을 다시 읽지 않습니다. 대신 버퍼에 담는 데이터가 커져서, 같은 버퍼 크기라면 정렬할 수 있는 행 수가 줄어듭니다.

MySQL 8.0 환경에서는 싱글 패스가 선택되는 경우가 많습니다. 원본을 다시 읽는 랜덤 I/O 비용이 큰 환경이 많기 때문입니다. 다만 정렬 대상 행이 매우 크거나(BLOB/TEXT 등 포함), 버퍼 부담이 큰 조건에서는 투 패스 성격의 동작이 나타날 수 있습니다.

여기서 실무 교훈이 나옵니다. SELECT *로 불필요한 컬럼까지 가져오면, 싱글 패스에서 Sort Buffer에 담기는 레코드가 커집니다. 그 결과 버퍼당 처리 가능한 행 수가 줄고, 외부 병합으로 넘어갈 확률이 올라갑니다. 정렬 쿼리에서 필요한 컬럼만 명시하는 습관이 성능에 직접 연결되는 이유입니다.

실행 계획에서 바로 보는 체크포인트

정렬 이슈를 만났을 때는 아래 항목을 함께 확인하면 원인 분리가 빨라집니다.

• EXPLAIN의 Extra에 Using filesort가 있는지 확인합니다.
• Using temporary가 함께 보이면 임시 테이블 경로 가능성이 높습니다.
• EXPLAIN ANALYZE로 정렬 단계의 실제 실행 시간과 처리 행 수를 봅니다.
• 세션 상태값(Sort_merge_passes, Sort_rows)을 비교해 외부 병합 부담을 추정합니다.

EXPLAIN ANALYZE
SELECT ...
FROM ...
WHERE ...
ORDER BY ...;

SHOW SESSION STATUS LIKE 'Sort%';

실무로 정리하면

MySQL의 정렬 경로를 이해하면, ORDER BY가 느릴 때 점검 순서가 명확해집니다.

• Using filesort는 “인덱스 정렬 미사용” 신호로 해석합니다.
• Using temporary 동반 여부를 보고 임시 테이블 경로인지 먼저 구분합니다.
• 1순위 개선은 인덱스로 정렬 흡수입니다. (WHERE + ORDER BY를 함께 고려한 인덱스 설계)
• 정렬을 피할 수 없다면 행 수와 행 폭을 동시에 축소합니다. (WHERE로 대상 축소, 필요한 컬럼만 조회)
• sort_buffer_size는 세션 단위로 신중하게 조정하고 전역 과대 설정은 피합니다.

ORDER BY 하나에도 옵티마이저는 여러 경로 중 하나를 고르고, Sort Buffer 내부 처리 방식까지 달라집니다. 이 선택들의 조합이 정렬 성능을 결정합니다. 다음에 느린 정렬 쿼리를 만나면 Using filesort 유무만 보는 데서 멈추지 말고, Using temporary와 실제 merge 부담까지 함께 확인해 보세요.

---

📚 시리즈: Database

1. 인덱스를 만들었는데 왜 안 빨라질까 — MySQL 인덱스 스캔 4가지
2. MySQL은 ORDER BY를 어떻게 처리할까 — 정렬 세 가지 경로와 Sort Buffer ← 지금 글