[MySQL] 혼합 정렬 ORDER BY 최적화: 내림차순 인덱스로 filesort 피하기

데이터베이스를 다루다 보면 최신 데이터부터 보여 주되, 같은 시각의 데이터는 작은 식별자 순서로 정렬하거나 우선순위는 높게, 마감일은 빠르게 나열해야 할 때가 많습니다. 이때 가장 흔하게 사용되는 것이 바로 ORDER BY created_at DESC, id ASC와 같은 혼합 정렬입니다.

정렬할 행이 적으면 큰 문제가 드러나지 않지만, 조회 범위가 커지면 MySQL이 인덱스 순서를 그대로 이용하지 못하고 별도의 정렬 단계인 filesort를 수행할 수 있습니다. 이름과 달리 filesort가 항상 디스크 파일을 사용한다는 의미는 아니지만, 후보 행을 모으고 정렬해야 한다는 점은 같습니다. 따라서 대용량 목록 조회에서는 실행 시간과 메모리 사용량, 임시 파일 발생 가능성까지 함께 커질 수 있어 주의가 필요합니다.

이 글에서는 ASC와 DESC가 섞인 정렬에서 filesort를 피하기 위한 복합 인덱스의 열 순서와 방향을 기본 문법부터 실행 계획, 범위 조건, 페이지네이션의 함정까지 깊이 있게 다루어 보겠습니다.

1. 혼합 정렬에서 가장 중요한 인덱스 원칙

결론부터 정리하면, WHERE 절의 동등 비교 열을 앞에 배치하고 그 뒤에 ORDER BY 열을 같은 순서와 방향으로 배치해야 합니다. 예를 들어 특정 게시판의 글을 최신순으로 조회하면서 생성 시각이 같을 때 작은 ID를 먼저 보여 주는 쿼리가 있다고 가정하겠습니다.

SELECT id, board_id, title, created_at
FROM posts
WHERE board_id = 10
ORDER BY created_at DESC, id ASC
LIMIT 50;

이 쿼리에 직접 대응하는 인덱스는 다음과 같습니다.

CREATE INDEX idx_posts_board_created_id
ON posts (board_id ASC, created_at DESC, id ASC);

board_id는 하나의 값으로 고정되는 동등 조건이므로, 실질적인 정렬 순서는 그다음 열인 created_at DESC, id ASC에서 결정됩니다. 초보 개발자가 흔히 하는 실수는 정렬에 등장하는 열만 보고 (created_at DESC, id ASC)를 만드는 것입니다. 이 경우 모든 게시판 데이터가 하나의 인덱스 순서에 섞이므로 특정 게시판의 행만 연속적으로 읽는 효율이 떨어질 수 있습니다.

ORDER BY와 인덱스의 대응 관계

조회 조건과 정렬	적합한 인덱스 예	판단
WHERE board_id=? ORDER BY created_at DESC, id ASC	(board_id, created_at DESC, id ASC)	동등 조건 이후 순서와 방향이 일치합니다.
WHERE board_id=? ORDER BY created_at ASC, id DESC	위 인덱스의 역방향 스캔	모든 정렬 방향이 동시에 반전되므로 사용할 수 있습니다.
WHERE board_id=? ORDER BY created_at DESC, id DESC	(board_id, created_at DESC, id DESC)	기존 인덱스와 두 번째 방향이 달라 별도 설계가 필요합니다.
WHERE board_id=? ORDER BY id ASC, created_at DESC	(board_id, id ASC, created_at DESC)	열 순서가 달라 기존 인덱스로 정렬을 보장할 수 없습니다.

가장 중요한 점은 일부 열만 임의로 반전해서 읽을 수는 없다는 것입니다. 인덱스를 뒤에서 앞으로 읽는 역방향 스캔(Backward Index Scan)은 인덱스 구간 전체의 방향을 반전합니다. 따라서 (created_at DESC, id ASC)는 created_at ASC, id DESC에도 대응할 수 있지만, created_at DESC, id DESC까지 동시에 해결하지는 못합니다.

2. MySQL 8.0 내림차순 인덱스의 동작 원리

MySQL 8.0의 InnoDB는 키 파트별로 ASC와 DESC 방향을 지정하는 내림차순 인덱스(Descending Index)를 지원합니다. 반면 오래된 MySQL 버전에서는 인덱스 정의에 DESC를 작성해도 방향이 실질적으로 반영되지 않을 수 있으므로, 운영 버전의 공식 문서와 실제 SHOW CREATE TABLE, 실행 계획을 함께 확인해야 합니다. 버전별 동작 차이를 무시하고 DDL만 복사하는 것은 위험합니다.

모든 열이 같은 방향인 정렬은 전통적인 오름차순 인덱스로도 처리할 수 있습니다. 예를 들어 INDEX(created_at, id)는 정방향으로 읽으면 created_at ASC, id ASC, 역방향으로 읽으면 created_at DESC, id DESC가 됩니다. 바로 이 때문에 단순히 최신순이라는 이유만으로 언제나 DESC 인덱스를 추가할 필요는 없습니다.

반면 created_at DESC, id ASC처럼 방향이 서로 다른 경우에는 일반적인 (created_at ASC, id ASC) 인덱스를 어느 방향으로 읽어도 원하는 순서가 나오지 않습니다. 정방향이면 ASC·ASC, 역방향이면 DESC·DESC가 되기 때문입니다. 혼합 방향을 물리적인 키 순서에 표현하는 내림차순 키 파트가 필요한 이유가 바로 여기에 있습니다.

3. WHERE 조건이 추가되면 열 순서를 어떻게 정할까?

복합 인덱스는 ORDER BY만 복사해서 만들면 끝나는 것이 아닙니다. WHERE 절에서 어떤 열이 하나의 값으로 고정되고, 어느 열부터 범위가 열리는지를 함께 판단해야 합니다. WHERE 절의 기본 문법과 MySQL 8.0 최적화 원리를 먼저 이해하면 이 기준을 적용하기 쉽습니다.

동등 조건은 정렬 열 앞에 배치할 수 있습니다

WHERE tenant_id = 7
  AND status = 'OPEN'
ORDER BY priority DESC, due_at ASC, id ASC

이 경우 후보 인덱스는 (tenant_id, status, priority DESC, due_at ASC, id ASC)입니다. tenant_id와 status가 모두 상수로 고정되므로 그 뒤의 키 순서가 결과의 정렬 순서로 이어집니다. 다만 실제 데이터에서 status의 선택도가 매우 낮고 쓰기 비용이 중요한 경우에는 해당 열을 포함했을 때 읽는 행 수가 얼마나 감소하는지 실행 계획으로 비교해야 합니다.

범위 조건 뒤의 정렬은 별도 검토가 필요합니다

WHERE board_id = 10
  AND created_at >= '2026-08-01'
ORDER BY priority DESC, id ASC

created_at을 board_id 다음에 배치하면 날짜 범위 검색에는 유리하지만, 그 뒤의 priority DESC, id ASC가 전체 결과 순서를 보장하지 못할 수 있습니다. 서로 다른 created_at 값의 하위 구간마다 우선순위 정렬이 반복되기 때문입니다. 범위 조건과 ORDER BY가 서로 다른 열을 요구한다면 하나의 인덱스가 필터링과 정렬을 모두 완벽하게 해결한다고 단정하면 안 됩니다.

이 경우에는 기간 조건의 선택도, 반환 행 수, LIMIT 크기를 기준으로 두 후보를 비교해야 합니다. 날짜로 먼저 좁힌 뒤 filesort하는 인덱스와 정렬 순서대로 읽으면서 조건에 맞는 행을 찾는 인덱스를 각각 만든 후, 실제 분포를 반영한 데이터에서 검증하는 것이 더 안전합니다. 날짜 경계값 자체는 BETWEEN 범위 검색과 날짜 데이터의 주의사항도 함께 참고할 수 있습니다.

4. EXPLAIN으로 filesort 제거 여부 확인하기

인덱스를 만들었다는 사실보다 MySQL 옵티마이저가 실제로 그 인덱스를 선택했는지가 더 중요합니다. 다음 순서로 비교하면 됩니다.

1. 비교할 인덱스 외의 조건, SELECT 목록, LIMIT 값을 동일하게 유지합니다.
2. EXPLAIN으로 key, key_len, rows, filtered, Extra를 기록합니다.
3. 지원되는 버전에서는 EXPLAIN ANALYZE로 실제 반환 행 수와 각 단계의 시간을 확인합니다.
4. Extra의 Using filesort 유무뿐 아니라 읽은 행 수가 지나치게 늘지 않았는지 비교합니다.

EXPLAIN
SELECT id, board_id, title, created_at
FROM posts
WHERE board_id = 10
ORDER BY created_at DESC, id ASC
LIMIT 50;

EXPLAIN ANALYZE
SELECT id, board_id, title, created_at
FROM posts
WHERE board_id = 10
ORDER BY created_at DESC, id ASC
LIMIT 50;

Using filesort가 사라졌다고 무조건 더 빠른 것은 아닙니다. 정렬 인덱스를 따라 읽으면서 조건에 맞지 않는 행을 대량으로 건너뛴다면, 선택도가 높은 조건으로 먼저 좁힌 뒤 소량을 정렬하는 계획보다 느릴 수 있습니다. 또한 Using index는 일반적으로 커버링 인덱스(Covering Index)로 필요한 값을 인덱스에서 해결했다는 의미이며, 정렬 제거를 뜻하는 표시는 아닙니다. 두 용어를 혼동하지 않아야 합니다.

5. 커버링 인덱스와 쓰기 비용의 균형

목록 조회에 필요한 열을 모두 인덱스에 넣으면 테이블 행을 다시 읽는 횟수를 줄일 수 있습니다. 하지만 제목이나 본문처럼 길이가 큰 열까지 무리하게 추가하면 인덱스 크기, 버퍼 풀 점유, INSERT·UPDATE 비용이 증가합니다. 앞선 예제에서는 필터와 정렬에 필요한 (board_id, created_at DESC, id ASC)를 핵심 인덱스로 두고, 화면에 필요한 나머지 열은 기본 키를 통한 테이블 조회가 허용 가능한지 측정하는 것이 좋습니다.

SELECT *는 커버링 가능성을 낮추고 불필요한 네트워크 전송까지 늘릴 수 있습니다. 필요한 열만 선택해야 하는 이유는 SELECT *가 데이터베이스를 느리게 만드는 원인과 실행 계획 분석에서도 확인할 수 있습니다.

설계안	장점	주의사항
필터·정렬 열만 포함	인덱스가 비교적 작고 쓰기 부담이 낮습니다.	결과 열을 얻기 위한 테이블 접근이 발생할 수 있습니다.
자주 반환하는 짧은 열까지 포함	커버링 조회가 가능해질 수 있습니다.	인덱스 크기와 변경 비용을 측정해야 합니다.
여러 ORDER BY마다 별도 인덱스	각 조회의 filesort를 줄일 수 있습니다.	중복 인덱스와 쓰기 증폭이 커질 수 있습니다.

6. LIMIT와 대용량 페이지 조회의 함정

LIMIT 50처럼 첫 페이지를 읽을 때는 정렬 인덱스의 효과가 분명할 수 있습니다. 반면 LIMIT 500000, 50은 결과 50건을 반환하기 전에 앞선 행을 건너뛰어야 하므로, filesort를 제거해도 깊은 페이지의 비용 자체는 사라지지 않습니다. 이 경우에는 마지막으로 본 정렬 키를 조건에 넣는 키셋 페이지네이션(Keyset Pagination)을 검토해야 합니다.

created_at DESC, id ASC에서 직전 페이지의 마지막 값이 각각 :last_created_at, :last_id라면 다음 페이지 조건은 다음과 같이 정렬 방향을 정확히 반영합니다.

WHERE board_id = :board_id
  AND (
       created_at < :last_created_at
       OR (created_at = :last_created_at AND id > :last_id)
  )
ORDER BY created_at DESC, id ASC
LIMIT 50;

첫 번째 정렬 열은 DESC이므로 다음 행의 시각은 더 작아야 하고, 시각이 같을 때 두 번째 열은 ASC이므로 ID가 더 커야 합니다. 혼합 정렬에서는 단순한 튜플 비교가 의도한 사전식 순서와 일치하는지 특히 조심해야 하며, 위와 같이 방향별 조건을 명시하면 의미가 분명해집니다. 정렬 값에 NULL이 들어올 수 있다면 NULL의 위치와 비교 결과도 별도로 정의해야 합니다. 안정적인 페이지 순서를 위해서는 마지막에 기본 키처럼 유일한 타이브레이커를 두는 것이 안전합니다.

7. 인덱스별 실행 계획과 조회 시간을 측정하는 방법

운영 판단에는 실제 데이터 분포를 반영한 증거가 필요합니다. 비교 대상은 최소한 기본 오름차순 인덱스, ORDER BY와 동일한 혼합 방향 인덱스, 필터 선택도를 우선한 인덱스로 구성합니다. 각 인덱스는 테스트 환경에서 명확한 이름으로 만들고, 필요한 경우 USE INDEX를 사용해 후보별 계획을 분리하되 강제 힌트 자체를 최종 해결책으로 단정하지 않아야 합니다.

측정 항목	기록 방법	판단 기준
실행 계획	EXPLAIN과 EXPLAIN ANALYZE 결과 보관	선택된 key, 예상·실제 행 수, Using filesort를 비교합니다.
첫 페이지	동일 조건의 LIMIT 50 반복 측정	캐시 상태를 구분하고 중앙값과 상위 지연을 기록합니다.
깊은 페이지	동일 위치에서 OFFSET 방식과 키셋 방식 비교	검사한 행 수와 응답 시간이 페이지 깊이에 따라 증가하는지 봅니다.
쓰기 영향	동일한 INSERT·UPDATE 작업량으로 비교	인덱스 추가 전후 처리량과 지연, 인덱스 크기를 확인합니다.

측정 전에는 데이터 건수, board_id별 분포, 동일한 created_at 값의 비율, 서버 버전과 설정을 기록해야 합니다. 워밍업 실행과 본 측정을 구분하고, 다른 부하가 개입하지 않는 조건에서 여러 번 반복해야 합니다. 임의의 한 번 실행 결과나 작은 개발 데이터만으로 운영 성능을 예측해서는 안 됩니다.

8. 실무 선택 기준 정리

• 모든 정렬 열의 방향이 같다면 기존 오름차순 인덱스의 역방향 스캔으로 해결되는지 먼저 확인합니다.
• ASC와 DESC가 섞였다면 ORDER BY 열의 순서와 방향을 복합 인덱스에 맞추거나, 전체 방향이 반전된 인덱스를 검토합니다.
• WHERE 절의 동등 조건 열은 정렬 열 앞에 배치할 수 있지만, 범위 조건이 끼어들면 뒤쪽 정렬 보장이 깨지는지 확인해야 합니다.
• Using filesort 제거만 보지 말고 실제 읽은 행 수, 커버링 여부, LIMIT 크기와 쓰기 비용을 함께 비교합니다.
• 깊은 페이지는 내림차순 인덱스만으로 해결하려 하지 말고 고유한 타이브레이커를 포함한 키셋 페이지네이션을 사용합니다.

결국 혼합 정렬의 인덱스 설계는 고정되는 조건 열 + ORDER BY 열 순서 + 각 열의 방향을 하나의 연속된 키 순서로 만드는 작업입니다. 이 원칙으로 후보를 만든 뒤 실제 실행 계획과 대용량 페이지 조회 시간을 측정하면, 불필요한 filesort를 줄이면서도 과도한 인덱스 추가를 피할 수 있습니다.