[실습] WHERE문에 인덱스를 걸기 vs ORDER BY문에 인덱스를 걸기

JSCODE 박재성

2025-12-02

✅ WHERE문에 인덱스를 걸기 vs ORDER BY문에 인덱스를 걸기

1. 테이블 생성

DROP TABLE IF EXISTS users; 

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

2. 100만 건의 랜덤 데이터 삽입

-- 높은 재귀(반복) 횟수를 허용하도록 설정
-- (아래에서 생성할 더미 데이터의 개수와 맞춰서 작성하면 된다.)
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')) AS name,  -- 'User' 다음에 7자리 숫자로 구성된 이름 생성
    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 AS department,  -- 의미 있는 단어 조합으로 부서 이름 생성
    FLOOR(1 + RAND() * 1000000) AS salary,    -- 1부터 1000000 사이의 난수로 나이 생성
    TIMESTAMP(DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY) + INTERVAL FLOOR(RAND() * 86400) SECOND) AS created_at -- 최근 10년 내의 임의의 날짜와 시간 생성
FROM cte;

3. 데이터 조회해서 성능 측정하기

SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)
AND department = 'Sales'
ORDER BY salary
LIMIT 100;



약 200ms 정도의 시간이 걸린다.

4. 실행 계획 조회해보기

EXPLAIN SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)
AND department = 'Sales'
ORDER BY salary
LIMIT 100;



• type이 ALL인걸 보니 풀 테이블 스캔을 했다. 비효율적이다.

5. 성능 개선을 위한 인덱스 추가
SQL문만 봤을 때는 created_at, department, salary 컬럼에 인덱스를 걸 수 있는 선택지가 있다는 걸 알 수 있다. 어떤 컬럼에 거는 게 효율적인지는 하나씩 걸어보고 SQL문의 성능을 측정해서 판단해도 된다. 그 전에 우리는 미리 예상하는 걸 연습해보자.
 
우선 created_at와 department 컬럼 중에서 인덱스를 걸었을 때 효율적인 컬럼은 created_at이라는 걸 예상할 수 있다. 왜냐하면 위의 SQL 문 조건을 봤을 때 department = ‘Sales’의 조건은 데이터 액세스 수가 많을 수 밖에 없다. 하지만 created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)의 조건은 데이터 액세스 수가 적다.
 
성능을 향상시키는 데 중요한 요소 중 하나는 데이터 액세스 수를 줄이는 것이다. 따라서 department보다 created_at에 인덱스를 생성하는 게 더 좋을 것이다.
 
그럼 created_at이랑 salary 중에서 인덱스를 걸었을 때 효율적인 컬럼은 어떤 걸까? 바로 감이 안올 수 있으니 하나씩 인덱스를 걸면서 실행 계획을 보고 판단해보자.
 
1) salary에 인덱스 걸기
CREATE INDEX idx_salary ON users (salary);
-- 성능 측정
SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)
AND department = 'Sales'
ORDER BY salary
LIMIT 100;

-- 실행 계획
EXPLAIN SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)
AND department = 'Sales'
ORDER BY salary
LIMIT 100;

-- 실행 계획 세부 내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)
AND department = 'Sales'
ORDER BY salary
LIMIT 100;

SQL문의 성능을 측정해보니 5배가 더 느려졌다! 왜 그런지 실행 계획을 통해 원인을 유추해보자.
 

위의 실행 계획에서 type이 index이면서 salary 인덱스를 사용한 걸로 봐서 인덱스 풀 스캔을 했음을 알 수 있다.
 
-> Limit: 100 row(s) 
 (cost=9.09 rows=3.33) (actual time=23.8..1009 rows=100 loops=1)
    -> Filter: ((users.created_at >= <cache>((now() - interval 3 day))) and (users.department = 'Sales'))  
    (cost=9.09 rows=3.33) (actual time=23.8..1009 rows=100 loops=1)
        -> Index scan on users using idx_salary  
        (cost=9.09 rows=100) (actual time=1.03..943 rows=974891 loops=1)
1. idx_salary 인덱스를 활용해 인덱스 풀 스캔을 했다. 이 인덱스를 활용했기 때문에 정렬 과정이 따로 필요없다.
2. 그런 뒤에 WHERE 조건을 만족시키는 데이터 100개를 필터링한다. 이 때, 인덱스에는 created_at, department 정보가 없기 때문에 실제 테이블에 접근해서 조건을 만족하는 지 확인해야한다. 풀 테이블 스캔과 거의 다를 바가 없다.
3. 조건을 만족시키는 데이터 100개를 찾는 순간 더 이상 탐색을 하지 않는다. (LIMIT 100)
** 참고) LIMIT 100을 안 달아주면 탐색하는 데이터의 범위가 커져서 풀 테이블 스캔(type: ALL)으로 데이터를 스캔한다.
 
2) created_at에 인덱스 걸기
ALTER TABLE users DROP INDEX idx_salary; -- 기존 인덱스 삭제
CREATE INDEX idx_created_at ON users (created_at);
-- 성능 측정
SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)
AND department = 'Sales'
ORDER BY salary
LIMIT 100;

-- 실행 계획
EXPLAIN SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)
AND department = 'Sales'
ORDER BY salary
LIMIT 100;

-- 실행 계획 세부 내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)
AND department = 'Sales'
ORDER BY salary
LIMIT 100;

성능을 측정해보니 200ms보다 약 6~7배 정도 빨라졌음을 알 수 있다.
 

type이 range인걸 보니 인덱스 레인지 스캔을 활용했음을 알 수 있다.
 
-> Limit: 100 row(s)  
(cost=504 rows=100) (actual time=8.94..8.96 rows=100 loops=1)
    -> Sort: users.salary, limit input to 100 row(s) per chunk  
    (cost=504 rows=1120) (actual time=8.94..8.95 rows=100 loops=1)
        -> Filter: (users.department = 'Sales')  
        (cost=504 rows=1120) (actual time=0.269..8.84 rows=104 loops=1)
            -> Index range scan on users using idx_created_at over ('2024-06-30 01:21:20' <= created_at), 
            with index condition: (users.created_at >= <cache>((now() - interval 3 day)))  
            (cost=504 rows=1120) (actual time=0.0537..8.54 rows=1120 loops=1)
1. idx_created_at 인덱스를 활용해 인덱스 레인지 스캔을 했다. 이 때, users.created_at >= <cache>((now() - interval 3 day))을 만족하는 데이터에만 액세스했다. (rows=1120)
2. 1번 과정에서 액세스한 데이터 중에서 users.department = ‘Sales’을 만족하는 데이터를 필터링했다. (rows=104)
3. 2번 과정에서 필터링한 104개의 데이터를 users.salary를 기준으로 정렬시켰다.
4. LIMIT으로 인해 100개의 데이터만 가져왔다.
 
 
⭐
[이것만은 꼭 기억해두자!] - ORDER BY의 특징 상 모든 데이터를 바탕으로 정렬을 해야 하기 때문에, 인덱스 풀 스캔 또는 테이블 풀 스캔을 활용할 수 밖에 없다. 이 때문에 ORDER BY문보다 WHERE문에 있는 컬럼에 인덱스를 걸었을 때 성능이 향상되는 경우가 많다. (항상 그런건 아니니 성능 측정과 실행 계획을 살펴보는 습관을 들이자.)