ORDER BY 최적화: index order scan과 filesort의 차이

1. 왜 ORDER BY 최적화가 운영에서 중요한가

ORDER BY는 단순히 결과를 보기 좋게 정렬하는 구문으로 보이지만, 운영 환경에서는 응답 시간, 임시 공간 사용량, CPU 사용률, 읽기 증폭을 크게 좌우한다. 특히 게시판의 최신 글 목록, 주문 내역, 결제 이력, 이벤트 로그, 관리자 검색 화면처럼 ORDER BY ... LIMIT ... 패턴이 자주 호출되는 서비스에서는 정렬 방식 하나가 전체 데이터베이스 부하의 큰 부분을 차지할 수 있다.

MySQL이 정렬을 처리하는 대표적인 방식은 두 가지다.

index order scan: B-tree 인덱스가 이미 정렬된 구조라는 점을 이용해 원하는 순서대로 인덱스를 읽는다.
filesort: 조건에 맞는 행을 찾은 뒤 별도의 정렬 알고리즘으로 결과를 다시 정렬한다.

두 방식 모두 정상적인 실행 방식이다. filesort라는 이름 때문에 항상 디스크 파일을 만든다고 오해하기 쉽지만, MySQL의 filesort는 “인덱스 순서로 해결하지 못해 별도 정렬 단계를 수행한다”는 의미에 가깝다. 정렬 버퍼 안에서 끝날 수도 있고, 결과가 크거나 메모리 한계를 넘으면 임시 파일을 사용할 수도 있다. 운영자는 Using filesort를 보았을 때 무조건 장애 신호로 해석하기보다, 정렬 대상 행 수, LIMIT, 인덱스 설계, 버퍼/임시 파일 사용 가능성, 쿼리 호출 빈도를 함께 판단해야 한다.

2. InnoDB B-tree와 정렬의 기본 원리

InnoDB의 보조 인덱스는 인덱스 키 값 순서로 정렬된 B-tree다. 예를 들어 (created_at, id) 인덱스가 있으면 MySQL은 created_at 오름차순, 같은 created_at 안에서는 id 오름차순 순서로 인덱스 엔트리를 읽을 수 있다. 이 순서가 쿼리의 ORDER BY created_at, id와 맞으면 별도의 정렬 단계가 필요 없다.

반대로 ORDER BY amount DESC, created_at DESC처럼 현재 인덱스 순서와 맞지 않는 정렬을 요구하면 MySQL은 조건을 만족하는 행을 모은 뒤 정렬해야 한다. 이때 실행 계획의 Extra에 Using filesort가 표시될 수 있다.

다음 흐름은 두 방식의 차이를 요약한다.

flowchart TD
    A[SQL 수신: WHERE + ORDER BY + LIMIT] --> B{ORDER BY를 만족하는 인덱스 순서가 있는가?}
    B -- 예 --> C[index order scan]
    C --> D[필요한 순서대로 인덱스 엔트리 읽기]
    D --> E[LIMIT에 도달하면 조기 종료 가능]
    B -- 아니오 --> F[조건에 맞는 후보 행 수집]
    F --> G[sort buffer에서 정렬]
    G --> H{메모리 안에서 처리 가능한가?}
    H -- 예 --> I[정렬 결과 반환]
    H -- 아니오 --> J[임시 파일/merge pass 가능]
    J --> I

핵심은 ORDER BY가 인덱스의 왼쪽부터 이어지는 키 순서와 호환되어야 한다는 점이다. 복합 인덱스 (customer_id, created_at, id)가 있을 때 WHERE customer_id = 3 ORDER BY created_at, id는 customer_id가 상수로 고정되므로 인덱스 안에서 created_at, id 순서를 그대로 사용할 수 있다. 하지만 WHERE customer_id IN (3, 4) ORDER BY created_at, id는 서로 다른 customer_id 구간의 결과를 전역적으로 섞어야 하므로 별도 정렬이 필요할 수 있다.

3. index order scan이 빠른 이유

index order scan이 유리한 이유는 세 가지다.

첫째, 정렬 작업 자체를 생략한다. B-tree는 이미 키 순서대로 유지되므로 MySQL은 정렬 버퍼에 많은 행을 담고 비교하는 비용을 줄일 수 있다.

둘째, LIMIT과 결합하면 조기 종료가 가능하다. ORDER BY created_at DESC LIMIT 20을 인덱스 역순 스캔으로 처리할 수 있다면, MySQL은 필요한 20개 행을 찾은 뒤 더 이상 읽지 않아도 된다. 반면 filesort는 최종 상위 20개만 반환하더라도 정렬 후보를 더 많이 읽고 비교해야 할 수 있다.

셋째, 인덱스가 필요한 컬럼을 모두 포함하면 covering index 효과를 얻을 수 있다. SELECT id, created_at FROM ... ORDER BY created_at, id LIMIT 20에서 (created_at, id) 인덱스만 읽어도 충분하다면 테이블 레코드 접근까지 줄일 수 있다.

다만 index order scan이 항상 최선은 아니다. 정렬 순서는 맞지만 WHERE 조건 선택도가 낮아 너무 많은 인덱스 엔트리를 읽어야 한다면, MySQL은 조건을 먼저 줄인 뒤 filesort하는 계획을 선택할 수 있다. 따라서 ORDER BY 최적화는 정렬 순서만이 아니라 WHERE, LIMIT, projection 컬럼, 인덱스 폭, 데이터 분포까지 함께 보는 문제다.

4. filesort는 무엇이며 언제 문제가 되는가

MySQL의 filesort는 이름과 달리 반드시 파일 정렬을 의미하지 않는다. 일반적으로 다음 단계로 이해하면 된다.

조건에 맞는 행 또는 정렬에 필요한 키를 읽는다.
정렬 키와 row reference 또는 추가 컬럼을 sort buffer에 담는다.
버퍼 안에서 정렬하거나, 필요하면 여러 run을 만들고 merge한다.
정렬된 순서대로 최종 행을 반환한다.

filesort가 문제가 되는 대표적인 상황은 다음과 같다.

정렬 대상 행 수가 많고 LIMIT이 없거나 매우 크다.
ORDER BY 컬럼이 넓은 문자열이거나 정렬 키가 많다.
WHERE 조건이 넓어서 정렬 후보가 대량으로 발생한다.
동시에 많은 세션이 큰 filesort를 수행해 CPU와 임시 I/O가 증가한다.
sort_buffer_size를 세션당 크게 키워 전체 메모리 사용량이 예측보다 커진다.

운영에서 흔한 실수는 Using filesort를 없애기 위해 무조건 인덱스를 추가하는 것이다. 인덱스는 쓰기 비용, 버퍼 풀 점유, 통계 관리 비용을 증가시킨다. 호출 빈도가 낮고 정렬 대상이 작은 관리용 쿼리라면 filesort가 더 단순하고 합리적일 수 있다. 반대로 고빈도 API에서 매번 수만 행을 정렬한다면 인덱스 설계가 필요하다.

5. 검증용 예제: 인덱스 순서 정렬과 filesort 비교

다음 예제는 MySQL 8.0 기준으로 재현 가능한 작은 테이블을 만들고, ORDER BY가 인덱스 순서와 맞는 경우와 맞지 않는 경우의 실행 계획을 비교한다.

DROP TABLE IF EXISTS order_sort_demo;
CREATE TABLE order_sort_demo (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  order_status VARCHAR(20) NOT NULL,
  created_at DATETIME NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  note VARCHAR(100) NOT NULL,
  KEY idx_created_id (created_at, id, customer_id),
  KEY idx_customer_created_id (customer_id, created_at, id),
  KEY idx_status_amount_id (order_status, amount, id)
) ENGINE=InnoDB;

INSERT INTO order_sort_demo (customer_id, order_status, created_at, amount, note)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 200
)
SELECT (n % 10) + 1 AS customer_id,
       CASE WHEN n % 5 = 0 THEN 'CANCELLED'
            WHEN n % 3 = 0 THEN 'PAID'
            ELSE 'READY' END AS order_status,
       TIMESTAMP('2026-01-01 00:00:00') + INTERVAL n MINUTE AS created_at,
       CAST(1000 + (n * 17) % 9000 AS DECIMAL(10,2)) AS amount,
       CONCAT('demo order ', n) AS note
FROM seq;

EXPLAIN SELECT id, customer_id, created_at
FROM order_sort_demo
ORDER BY created_at, id
LIMIT 5;

EXPLAIN SELECT id, customer_id, amount, created_at
FROM order_sort_demo
ORDER BY amount DESC, created_at DESC
LIMIT 5;

DROP TABLE order_sort_demo;

실행 결과(MySQL 8.0.x):

mysql> CREATE TABLE order_sort_demo (... KEY idx_created_id (created_at, id, customer_id), ...);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO order_sort_demo ... SELECT ... FROM seq;
Query OK, 200 rows affected (0.01 sec)
Records: 200  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT id, customer_id, created_at
    -> FROM order_sort_demo
    -> ORDER BY created_at, id
    -> LIMIT 5;
+----+-------------+-----------------+-------+----------------+------+-------------+
| id | select_type | table           | type  | key            | rows | Extra       |
+----+-------------+-----------------+-------+----------------+------+-------------+
|  1 | SIMPLE      | order_sort_demo | index | idx_created_id |    5 | Using index |
+----+-------------+-----------------+-------+----------------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id, customer_id, amount, created_at
    -> FROM order_sort_demo
    -> ORDER BY amount DESC, created_at DESC
    -> LIMIT 5;
+----+-------------+-----------------+------+-------+------+----------------+
| id | select_type | table           | type | key   | rows | Extra          |
+----+-------------+-----------------+------+-------+------+----------------+
|  1 | SIMPLE      | order_sort_demo | ALL  | NULL  |  200 | Using filesort |
+----+-------------+-----------------+------+-------+------+----------------+
1 row in set (0.00 sec)

mysql> DROP TABLE order_sort_demo;
Query OK, 0 rows affected (0.01 sec)

첫 번째 EXPLAIN은 (created_at, id, customer_id) 인덱스 순서와 ORDER BY created_at, id가 일치하므로 idx_created_id를 순서대로 읽을 수 있다. 두 번째 쿼리는 (amount, created_at) 순서를 만족하는 인덱스가 없기 때문에 별도 정렬 단계가 필요하며, 일반적으로 Extra에 Using filesort가 표시된다.

6. 복합 인덱스에서 equality prefix가 중요한 이유

복합 인덱스는 단순히 ORDER BY 컬럼이 포함되어 있다고 해서 항상 정렬에 사용할 수 있는 것이 아니다. 왼쪽 컬럼이 상수 조건으로 고정되면 그 다음 컬럼부터 정렬 순서를 사용할 수 있다. 이를 실무에서는 equality prefix가 맞는지 확인한다고 표현한다.

다음 예제에서 (customer_id, created_at, id) 인덱스는 customer_id = 3 조건과 결합될 때 ORDER BY created_at, id를 자연스럽게 만족한다.

DROP TABLE IF EXISTS order_sort_demo;
CREATE TABLE order_sort_demo (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  order_status VARCHAR(20) NOT NULL,
  created_at DATETIME NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  KEY idx_customer_created_id (customer_id, created_at, id),
  KEY idx_created_id (created_at, id)
) ENGINE=InnoDB;

INSERT INTO order_sort_demo (customer_id, order_status, created_at, amount)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 100
)
SELECT (n % 5) + 1 AS customer_id,
       'PAID' AS order_status,
       TIMESTAMP('2026-02-01 00:00:00') + INTERVAL n MINUTE AS created_at,
       CAST(500 + n AS DECIMAL(10,2)) AS amount
FROM seq;

EXPLAIN SELECT id, customer_id, created_at
FROM order_sort_demo
WHERE customer_id = 3
ORDER BY created_at, id
LIMIT 5;

EXPLAIN SELECT id, customer_id, created_at
FROM order_sort_demo FORCE INDEX (idx_customer_created_id)
WHERE customer_id IN (3, 4)
ORDER BY created_at, id
LIMIT 5;

DROP TABLE order_sort_demo;

실행 결과(MySQL 8.0.x):

mysql> CREATE TABLE order_sort_demo (... KEY idx_customer_created_id (customer_id, created_at, id), ...);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO order_sort_demo ... SELECT ... FROM seq;
Query OK, 100 rows affected (0.00 sec)
Records: 100  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT id, customer_id, created_at
    -> FROM order_sort_demo
    -> WHERE customer_id = 3
    -> ORDER BY created_at, id
    -> LIMIT 5;
+----+-------------+-----------------+------+-------------------------+------+-------------+
| id | select_type | table           | type | key                     | rows | Extra       |
+----+-------------+-----------------+------+-------------------------+------+-------------+
|  1 | SIMPLE      | order_sort_demo | ref  | idx_customer_created_id |   20 | Using index |
+----+-------------+-----------------+------+-------------------------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id, customer_id, created_at
    -> FROM order_sort_demo FORCE INDEX (idx_customer_created_id)
    -> WHERE customer_id IN (3, 4)
    -> ORDER BY created_at, id
    -> LIMIT 5;
+----+-------------+-----------------+-------+-------------------------+------+------------------------------------------+
| id | select_type | table           | type  | key                     | rows | Extra                                    |
+----+-------------+-----------------+-------+-------------------------+------+------------------------------------------+
|  1 | SIMPLE      | order_sort_demo | range | idx_customer_created_id |   40 | Using where; Using index; Using filesort |
+----+-------------+-----------------+-------+-------------------------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql> DROP TABLE order_sort_demo;
Query OK, 0 rows affected (0.00 sec)

두 실행 계획의 차이는 운영 튜닝에서 중요하다. 같은 인덱스가 있어도 equality 조건이 하나의 연속 구간을 만들면 정렬 순서를 이용하기 쉽고, 여러 구간을 합쳐야 하면 전역 정렬이 필요할 수 있다. 두 번째 EXPLAIN은 이 차이를 분명히 보기 위해 idx_customer_created_id를 강제로 사용한다. 실제 운영 쿼리에서는 optimizer가 다른 인덱스를 선택해 filesort를 피할 수도 있지만, 그 경우에도 “여러 customer_id 구간을 전역 created_at 순서로 합쳐야 한다”는 논리적 제약은 동일하게 검토해야 한다. 페이지네이션 API에서 customer_id = ? ORDER BY created_at DESC LIMIT ?가 빠른 반면, 여러 고객을 묶어 IN (...) ORDER BY created_at DESC로 바꾸면 계획이 바뀌는 이유가 여기에 있다.

7. DESC 정렬과 MySQL 8.0의 descending index

MySQL 8.0은 descending index를 지원한다. 예를 들어 최신 주문을 자주 조회한다면 다음과 같은 인덱스를 고려할 수 있다.

DROP TABLE IF EXISTS order_sort_desc_demo;
CREATE TABLE order_sort_desc_demo (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  created_at DATETIME NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  KEY idx_customer_created_desc (customer_id, created_at DESC, id DESC)
) ENGINE=InnoDB;

INSERT INTO order_sort_desc_demo (customer_id, created_at, amount)
VALUES
  (7, '2026-03-01 10:00:00', 100.00),
  (7, '2026-03-01 10:05:00', 120.00),
  (7, '2026-03-01 10:10:00', 130.00),
  (8, '2026-03-01 10:15:00', 200.00);

EXPLAIN SELECT id, customer_id, created_at
FROM order_sort_desc_demo
WHERE customer_id = 7
ORDER BY created_at DESC, id DESC
LIMIT 2;

SELECT id, customer_id, created_at
FROM order_sort_desc_demo
WHERE customer_id = 7
ORDER BY created_at DESC, id DESC
LIMIT 2;

DROP TABLE order_sort_desc_demo;

실행 결과(MySQL 8.0.x):

mysql> CREATE TABLE order_sort_desc_demo (... KEY idx_customer_created_desc (customer_id, created_at DESC, id DESC));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO order_sort_desc_demo (customer_id, created_at, amount) VALUES (...);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT id, customer_id, created_at
    -> FROM order_sort_desc_demo
    -> WHERE customer_id = 7
    -> ORDER BY created_at DESC, id DESC
    -> LIMIT 2;
+----+-------------+----------------------+-------+---------------------------+------+-------------+
| id | select_type | table                | type  | key                       | rows | Extra       |
+----+-------------+----------------------+-------+---------------------------+------+-------------+
|  1 | SIMPLE      | order_sort_desc_demo | ref   | idx_customer_created_desc |    3 | Using index |
+----+-------------+----------------------+-------+---------------------------+------+-------------+
1 row in set (0.00 sec)

mysql> SELECT id, customer_id, created_at
    -> FROM order_sort_desc_demo
    -> WHERE customer_id = 7
    -> ORDER BY created_at DESC, id DESC
    -> LIMIT 2;
+----+-------------+---------------------+
| id | customer_id | created_at          |
+----+-------------+---------------------+
|  3 |           7 | 2026-03-01 10:10:00 |
|  2 |           7 | 2026-03-01 10:05:00 |
+----+-------------+---------------------+
2 rows in set (0.00 sec)

mysql> DROP TABLE order_sort_desc_demo;
Query OK, 0 rows affected (0.00 sec)

실무에서는 모든 DESC 쿼리에 descending index를 별도로 만들 필요는 없다. MySQL은 인덱스를 역방향으로 읽을 수 있기 때문이다. 다만 복합 인덱스에서 컬럼별 정렬 방향이 섞이는 경우, 예를 들어 ORDER BY created_at DESC, id ASC처럼 방향이 혼합되면 인덱스 정의 방향과 쿼리 방향의 호환성을 더 엄격히 확인해야 한다. MySQL 8.0의 descending index는 이런 혼합 방향 정렬을 설계할 때 중요한 선택지가 된다.

8. EXPLAIN에서 무엇을 봐야 하는가

ORDER BY 튜닝 시 EXPLAIN에서는 다음 항목을 함께 본다.

key: 실제 선택된 인덱스다. 기대한 정렬 인덱스가 선택되었는지 확인한다.
type: index, range, ref 등 접근 방식이다. 정렬 인덱스를 읽더라도 너무 넓게 읽으면 비용이 커질 수 있다.
rows: 예상 읽기 행 수다. 샘플링과 통계에 따라 변동 가능하므로 절대값보다 방향성을 본다.
Extra: Using filesort, Using temporary, Using index, Backward index scan 같은 신호를 확인한다.

Using filesort가 있으면 다음 질문을 던져야 한다.

정렬 대상 행 수가 충분히 작은가?
LIMIT이 있어도 후보 행을 많이 읽어야 하는 구조인가?
WHERE 조건과 ORDER BY를 동시에 만족하는 복합 인덱스를 설계할 수 있는가?
해당 쿼리가 고빈도 경로인가, 관리용 저빈도 경로인가?
인덱스를 추가했을 때 쓰기 성능과 버퍼 풀 사용량 증가를 감당할 수 있는가?

9. 페이지네이션과 ORDER BY 인덱스

운영 서비스에서 ORDER BY는 페이지네이션과 함께 등장한다. 흔한 패턴은 다음과 같다.

SELECT id, created_at, amount
FROM orders
WHERE customer_id = ?
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 10000;

이 쿼리는 적절한 인덱스가 있어도 OFFSET 10000 앞의 행을 건너뛰어야 하므로 깊은 페이지에서 비용이 커진다. 가능하면 keyset pagination을 사용한다.

SELECT id, created_at, amount
FROM orders
WHERE customer_id = ?
  AND (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 20;

이 예시는 애플리케이션 테이블을 전제로 한 개념 예시다. 핵심은 마지막으로 본 정렬 키를 다음 요청의 시작점으로 사용해 인덱스 범위를 좁히는 것이다. 이 방식은 최신순 목록, 이벤트 스트림, 주문 이력처럼 정렬 키가 안정적인 화면에서 특히 효과적이다.

10. Aurora MySQL에서의 운영 해석

Aurora MySQL도 MySQL 호환 실행 엔진을 사용하므로 ORDER BY와 인덱스 순서, Using filesort의 기본 해석은 같다. 다만 운영 관점에서는 몇 가지 차이를 고려해야 한다.

첫째, Aurora는 스토리지 계층이 분산되어 있고 읽기 복제본을 여러 개 둘 수 있다. 큰 filesort가 reader 인스턴스에서 반복되면 해당 reader의 CPU, 메모리, 임시 객체 사용량이 먼저 병목이 된다. 쓰기 노드보다 reader에서 문제가 드러나는 경우가 많다.

둘째, Aurora의 모니터링은 Performance Insights, CloudWatch, wait event 관점과 함께 보아야 한다. Using filesort가 있는 쿼리가 상위 load SQL로 나타나고 CPU 또는 temporary I/O 관련 대기가 늘어난다면 인덱스 설계 또는 쿼리 호출 패턴을 조정해야 한다.

셋째, Aurora에서는 인스턴스 클래스에 따라 메모리 여유가 달라진다. sort_buffer_size를 크게 조정하는 방식은 세션 수가 늘어날 때 총 메모리 사용량을 키울 수 있으므로 신중해야 한다. 대부분의 경우 먼저 쿼리와 인덱스 구조를 조정하고, 파라미터 변경은 부하 테스트와 함께 검토하는 편이 안전하다.

11. 흔한 오해와 장애 패턴

11.1 `Using filesort`는 항상 나쁘다

아니다. 작은 결과 집합, 저빈도 쿼리, 관리용 화면에서는 filesort가 충분히 합리적일 수 있다. 문제는 고빈도 쿼리에서 많은 후보 행을 반복 정렬하는 경우다.

11.2 `ORDER BY` 컬럼에 인덱스만 있으면 된다

부족하다. WHERE 조건과 복합 인덱스의 선행 컬럼, 정렬 방향, LIMIT, 선택 컬럼까지 맞아야 한다. (created_at) 인덱스가 있어도 WHERE customer_id = ? ORDER BY created_at에는 (customer_id, created_at)이 더 적합한 경우가 많다.

11.3 `sort_buffer_size`를 키우면 해결된다

부분적으로만 맞다. 정렬 버퍼는 세션 단위로 사용될 수 있으므로 과도하게 키우면 동시 접속이 많은 환경에서 메모리 압박을 만들 수 있다. 버퍼 조정은 마지막 수단에 가깝고, 먼저 인덱스와 쿼리 구조를 확인해야 한다.

11.4 LIMIT이 있으면 정렬 비용이 항상 작다

아니다. LIMIT 20이라도 조건에 맞는 후보가 수십만 행이고 인덱스 순서를 사용할 수 없다면 많은 후보를 읽고 비교해야 한다. ORDER BY ... LIMIT ...이 빠르려면 원하는 순서로 조기 종료할 수 있는 인덱스 구조가 중요하다.

12. 인덱스 설계 기준

ORDER BY 최적화를 위한 복합 인덱스는 다음 순서로 검토한다.

동등 조건 컬럼: WHERE tenant_id = ?, customer_id = ?, status = ?처럼 equality 조건을 먼저 둔다.
범위 조건과 정렬 컬럼의 충돌: 범위 조건 뒤의 컬럼은 정렬 활용이 제한될 수 있다. WHERE created_at >= ? ORDER BY amount처럼 범위와 정렬이 다르면 별도 정렬이 필요할 수 있다.
ORDER BY 컬럼과 방향: 정렬 컬럼 순서와 ASC/DESC 방향을 인덱스 정의와 맞춘다.
tie-breaker 컬럼: 같은 정렬 값이 많은 경우 id 같은 고유 컬럼을 뒤에 붙여 정렬을 안정화한다.
covering 가능성: 조회 컬럼이 작고 고정적이라면 covering index를 고려한다.
쓰기 비용: 인덱스 추가가 INSERT/UPDATE/DELETE와 버퍼 풀에 미치는 비용을 검토한다.

예를 들어 고객별 최신 주문 20건을 자주 조회한다면 다음 형태가 일반적이다.

CREATE INDEX idx_orders_customer_created_id
ON orders (customer_id, created_at DESC, id DESC);

이 SQL은 운영 테이블을 전제로 한 설계 예시다. 실제 적용 전에는 기존 인덱스 중복 여부, 쿼리 패턴, 쓰기 부하, MySQL 버전, Aurora 파라미터 그룹, 온라인 DDL 영향 범위를 함께 검토해야 한다.

13. 운영 진단 절차

운영에서 정렬 비용이 의심될 때는 다음 절차로 접근한다.

느린 쿼리 로그, Performance Schema, APM, Aurora Performance Insights에서 상위 SQL을 확인한다.
대표 쿼리를 파라미터 값과 함께 확보한다. 정렬 문제는 데이터 분포와 조건값에 따라 달라질 수 있다.
EXPLAIN으로 key, rows, Extra를 확인한다.
EXPLAIN ANALYZE를 사용할 수 있는 환경이면 실제 실행 시간과 행 수를 확인한다. 운영 주 테이블에서는 부하를 고려해 읽기 복제본 또는 제한된 조건에서 수행한다.
후보 인덱스를 설계하고, staging 또는 복제본에서 계획 변화를 확인한다.
인덱스 추가 후 쓰기 지연, 버퍼 풀 hit ratio, 쿼리 latency, 임시 파일 사용량을 비교한다.

Performance Schema에서 정렬 관련 지표를 직접 추적할 수도 있다. 다음 쿼리는 현재 MySQL 8.0 환경에서 sort 관련 global status 변수를 확인하는 기본 진단 예시다.

SHOW GLOBAL STATUS LIKE 'Sort%';
SHOW VARIABLES LIKE 'sort_buffer_size';

실행 결과(MySQL 8.0.x):

mysql> SHOW GLOBAL STATUS LIKE 'Sort%';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
| Sort_range        | 0     |
| Sort_rows         | 0     |
| Sort_scan         | 0     |
+-------------------+-------+
4 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'sort_buffer_size';

+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.00 sec)

이 값들은 인스턴스 전체 누적 또는 설정값이므로 특정 쿼리 하나의 원인을 단정하지 않는다. 배포 전후, 피크 시간 전후, 쿼리 변경 전후의 변화량을 함께 보는 방식이 더 안전하다.

14. 점검표

ORDER BY 성능을 검토할 때 다음 항목을 확인한다.

ORDER BY `ORDER BY` 컬럼 순서가 실제 인덱스 키 순서와 일치하는가?
복합 인덱스의 선행 컬럼이 equality 조건으로 고정되는가?
ORDER BY `ORDER BY` 방향(ASC/DESC)이 인덱스 정의 또는 역방향 스캔과 호환되는가?
LIMIT `LIMIT`이 조기 종료로 이어지는 구조인가, 아니면 많은 후보를 정렬한 뒤 일부만 반환하는 구조인가?
Using filesort `Using filesort`가 고빈도·대량 정렬 경로에서 발생하는가?
filesort 제거를 위해 추가할 인덱스가 쓰기 성능과 버퍼 풀 사용량을 과도하게 증가시키지 않는가?
deep OFFSET 페이지네이션을 keyset pagination으로 바꿀 수 있는가?
Aurora reader에서 CPU, temporary I/O, Performance Insights 상위 SQL이 함께 악화되는가?
인덱스 추가 후 EXPLAIN 인덱스 추가 후 `EXPLAIN`, latency, row examined, 임시 객체 사용량을 배포 전후로 비교했는가?

15. 결론

ORDER BY 최적화의 핵심은 “정렬을 빠르게 수행한다”가 아니라 “가능하면 정렬하지 않아도 되는 순서로 읽는다”에 있다. InnoDB B-tree 인덱스의 순서를 쿼리의 WHERE, ORDER BY, LIMIT 구조와 맞추면 MySQL은 index order scan으로 필요한 행만 빠르게 읽고 조기 종료할 수 있다. 반대로 인덱스 순서와 맞지 않으면 filesort가 수행되며, 정렬 대상이 클수록 CPU, 메모리, 임시 I/O 비용이 커진다.

다음 단계의 튜닝에서는 ORDER BY와 GROUP BY, temporary table, covering index, pagination 전략을 함께 다루어야 한다. 실제 서비스의 느린 목록 조회는 이 요소들이 동시에 얽혀 나타나는 경우가 많기 때문이다.

1. 왜 ORDER BY 최적화가 운영에서 중요한가

2. InnoDB B-tree와 정렬의 기본 원리

3. index order scan이 빠른 이유

4. filesort는 무엇이며 언제 문제가 되는가

5. 검증용 예제: 인덱스 순서 정렬과 filesort 비교

6. 복합 인덱스에서 equality prefix가 중요한 이유

7. DESC 정렬과 MySQL 8.0의 descending index

8. EXPLAIN에서 무엇을 봐야 하는가

9. 페이지네이션과 ORDER BY 인덱스

10. Aurora MySQL에서의 운영 해석

11. 흔한 오해와 장애 패턴

11.1 Using filesort는 항상 나쁘다

11.2 ORDER BY 컬럼에 인덱스만 있으면 된다

11.3 sort_buffer_size를 키우면 해결된다

11.4 LIMIT이 있으면 정렬 비용이 항상 작다

12. 인덱스 설계 기준

13. 운영 진단 절차

14. 점검표

15. 결론

11.1 `Using filesort`는 항상 나쁘다

11.2 `ORDER BY` 컬럼에 인덱스만 있으면 된다

11.3 `sort_buffer_size`를 키우면 해결된다