[DB Optimization#13] ORDER BY 문 튜닝

정렬(ORDER BY)은 비싼 작업입니다

MySQL에서 ORDER BY는 생각보다 성능 비용이 큰 연산입니다.
특히 데이터가 많을수록 정렬에 드는 리소스는 기하급수적으로 늘어나기 때문에 최대한 정렬 작업을 피하거나, 미리 정렬된 상태에서 데이터를 가져오는 방식으로 바꾸는 것이 핵심입니다.

 

이번 글에서는 ORDER BY와 LIMIT 조합에서 인덱스를 어떻게 활용하면 성능을 개선할 수 있는지 실습을 통해 살펴보겠습니다.

 

 

실습

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    salary INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

SET SESSION cte_max_recursion_depth = 1000000;

INSERT INTO users (name, department, salary, created_at)
WITH RECURSIVE cte (n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000
)
SELECT 
    CONCAT('User', LPAD(n, 7, '0')),
    CASE 
        WHEN n % 10 = 1 THEN 'Engineering'
        WHEN n % 10 = 2 THEN 'Marketing'
        WHEN n % 10 = 3 THEN 'Sales'
        WHEN n % 10 = 4 THEN 'Finance'
        WHEN n % 10 = 5 THEN 'HR'
        WHEN n % 10 = 6 THEN 'Operations'
        WHEN n % 10 = 7 THEN 'IT'
        WHEN n % 10 = 8 THEN 'Customer Service'
        WHEN n % 10 = 9 THEN 'Research and Development'
        ELSE 'Product Management'
    END,
    FLOOR(1 + RAND() * 1000000),
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND)
FROM cte;

 

기본 성능 측정

SELECT * 
FROM users
ORDER BY salary
LIMIT 100;

→ 실행 계획에서 type이 ALL로 지정되어 있는 것으로 보아 성능을 향상시킬 필요가 있어 보입니다.

 

추가적으로, EXPLAIN ANALYZE를 확인해 보면 아래와 같습니다.

-> Limit: 100 row(s)  (cost=100569 rows=100) (actual time=310..310 rows=100 loops=1)
    -> Sort: users.salary, limit input to 100 row(s) per chunk  (cost=100569 rows=996636) (actual time=310..310 rows=100 loops=1)
        -> Table scan on users  (cost=100569 rows=996636) (actual time=0.112..228 rows=1e+6 loops=1)

 

MySQL은 전체 100만 건을 불러와 salary 기준으로 정렬 후, 그중 상위 100건만 추립니다.
이 과정에서 정렬 비용이 크고, 전체 데이터를 메모리에 불러오는 비효율이 발생합니다.

 

 

인덱스 추가

CREATE INDEX idx_salary ON users(salary);

→ 실행 시간이 0.271에서 0.023으로 낮아졌으며, type도 index로 변환되었습니다.

 

idx_salary 인덱스는 이미 salary 기준으로 정렬된 구조를 갖고 있기 때문에 별도의 정렬 없이 상위 100건만 바로 가져오게 됩니다.

 

결과적으로, 불필요한 정렬 작업을 건너뛰게 되어 쿼리 성능이 향상되게 됩니다.

 

추가적으로, EXPLAIN ANALYZE를 확인해 보면 아래와 같습니다.

-> Limit: 100 row(s)  (cost=0.0918 rows=100) (actual time=0.0736..0.353 rows=100 loops=1)
    -> Index scan on users using idx_salary  (cost=0.0918 rows=100) (actual time=0.0727..0.348 rows=100 loops=1)

앞선 쿼리에서의 결과와 달리 중간에 Sort 작업이 빠진 것을 확인할 수 있습니다.

 

 

하지만, 항상 ORDER BY에서 인덱스 지정을 통해 테이블 스캔을 하는 것은 아닙니다.

 

아래와 같이 LIMIT 100 없이 실행시켜 보도록 하겠습니다.

SELECT * 
FROM users
ORDER BY salary;

 

분명 아까와 동일한 인덱스 환경에서 LIMIT을 제외해 주니 type이 ALL로 다시 변경되었습니다.

이는 옵티마이저에서 인덱스에서 검색하는 것보다 FULL TABLE SCAN 하는 것이 효율적이라고 판단했기 때문입니다.

 

따라서, 성능을 높이기 위해서는 항상 LIMIT을 사용해 조회 범위를 제한하는 것이 중요합니다.