[DB Optimization#14] WHERE vs ORDER BY, 어디에 인덱스를 거는 것이 좋을까?

SQL 최적화에서 자주 나오는 고민 중 하나는 WHERE 절에 인덱스를 거는 게 좋은지, 아니면 ORDER BY 절에 인덱스를 거는 게 좋은지에 대한 문제입니다.

 

정답은 하나가 아닙니다. 데이터의 구조, 쿼리의 목적, 그리고 실행 계획에 따라 달라질 수 있기 때문입니다.

 

이번 글에서는 실제 100만 건의 데이터를 바탕으로 각각의 경우를 비교하고, 어떤 인덱스 전략이 더 효율적인지 실행 시간과 실행 계획을 통해 확인해 보겠습니다.

 

 

실습

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
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)
AND department = 'Sales'
ORDER BY salary
LIMIT 100;

→ 소요 시간은 약 0.253이며, 실행 계획에서 type = ALL이므로 최적화가 필요하다는 것을 알 수 있습니다.

 

 

1. ORDER BY 인덱스

CREATE INDEX idx_salary ON users (salary);

→ type = index로 잘 실행됐지만 실행 시간은 약 1.526으로 실행 시간이 오히려 늦어짐.

 

구체적인 원인 확인을 위해 EXPLAIN ANALYZE를 확인해 보겠습니다.

-> Limit: 100 row(s)  (cost=9.09 rows=3.33) (actual time=23.6..2020 rows=100 loops=1)
    -> Filter: ((users.department = 'Sales') and (users.created_at >= <cache>((now() - interval 3 day))))  (cost=9.09 rows=3.33) (actual time=23.6..2020 rows=100 loops=1)
        -> Index scan on users using idx_salary  (cost=9.09 rows=100) (actual time=13.5..1950 rows=935453 loops=1)

 

맨 첫 시작에서 가장 많은 시간이 걸림을 알 수 있습니다.

 

이유는 idx_salary를 통해서 index scan을 했지만, 이후에 department와 created_at에 대한 데이터 확인을 위해 전체 데이터 수준의 양(약 93만 건)을 확인해야 됐던 것 같습니다. 

 

따라서, salary가 아닌 where 문의 컬럼에 인덱스를 취해보도록 하겠습니다.

 

 

2. WHERE 인덱스

DROP INDEX idx_salary ON users;

CREATE INDEX idx_created_at ON users (created_at);

→ type = range로 변경되었으며, 실행 시간도 0.024로 매우 단축되었음을 알 수 있습니다.

 

추가적으로 EXPLAIN ANALYZE를 해보면 결과는 아래와 같습니다.

-> Limit: 100 row(s)  (cost=471 rows=100) (actual time=2.7..2.71 rows=100 loops=1)
    -> Sort: users.salary, limit input to 100 row(s) per chunk  (cost=471 rows=1045) (actual time=2.7..2.7 rows=100 loops=1)
        -> Filter: (users.department = 'Sales')  (cost=471 rows=1045) (actual time=0.272..2.65 rows=106 loops=1)
            -> Index range scan on users using idx_created_at over ('2025-04-14 20:59:28' <= created_at), with index condition: (users.created_at >= <cache>((now() - interval 3 day)))  (cost=471 rows=1045) (actual time=0.266..2.59 rows=1045 loops=1)

 

여기에서 알 수 있듯이 첫 index range scan에서 조회된 row수가 약 1,000건으로 ORDER BY 컬럼 인덱스에서 scan 되었던 것보다 현저히 적은 양이 scan 된 것을 확인할 수 있습니다.

 

 

마무리

• ORDER BY는 전체 정렬을 요구하므로 인덱스 풀 스캔 또는 테이블 풀 스캔이 발생할 수 있습니다.
• 반면 WHERE 조건에서 범위를 좁힐 수 있는 조건이라면 레인지 스캔을 통해 효율적인 조회가 가능합니다.
• 따라서 데이터 필터링의 효과가 높은 컬럼에 인덱스를 거는 것이 일반적으로 더 좋은 선택입니다.
• 단, 항상 실행 계획(EXPLAIN / EXPLAIN ANALYZE)과 실행 시간을 비교해서 판단해야 합니다.