[실습] ORDER BY문이 사용된 SQL문 튜닝하기

JSCODE 박재성

2025-12-02

✅ 예시

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
ORDER BY salary
LIMIT 100;



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

4. 실행 계획 조회해보기

# 실행 계획
EXPLAIN SELECT * FROM users
ORDER BY salary
LIMIT 100;

# 실행 계획 세부 내용
EXPLAIN ANALYZE SELECT * FROM users
ORDER BY salary
LIMIT 100;



• type이 ALL인걸 보니 풀 테이블 스캔을 했다. 비효율적이다.
• ORDER BY는 시간이 오래걸리는 작업이므로 최대한 피해주는 것이 좋다. 왜냐하면 정렬이라는 작업 자체가 다른 작업에 비해서 부담스러운 작업이며 성능에 안 좋은 영향을 끼치는 요소 중 하나이기 때문이다.
그런데 만약 인덱스를 사용하면 미리 정렬을 해둔 상태이기 때문에, ORDER BY를 사용하여 정렬해야 하는 번거로운 작업을 피할 수 있다.

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

5. 성능 개선을 위한 인덱스 추가

CREATE INDEX idx_salary ON users (salary);

 

2. 성능 측정
1. SQL문 실행시간

SELECT * FROM users
ORDER BY salary
LIMIT 100;



 
2. 실행 계획 조회해보기

EXPLAIN SELECT * FROM users
ORDER BY salary
LIMIT 100;



• 풀 테이블 스캔(type: ALL)이 아닌 인덱스 풀 스캔(type: index)을 활용해서 빠르게 데이터를 정렬해서 조회해왔다.
• LIMIT 없이 큰 범위의 데이터를 조회해오는 경우 옵티마이저가 인덱스를 활용하지 않고 테이블 풀 스캔을 해버릴 수도 있다. 따라서 성능 효율을 위해 LIMIT을 통해 작은 데이터의 범위를 조회해오도록 항상 신경쓰자.
 
c. 실행 계획 세부 내용 조회하기
EXPLAIN ANALYZE SELECT * FROM users
ORDER BY salary
LIMIT 100;
 
-> Limit: 100 row(s)  
(cost=0.0918 rows=100) (actual time=0.732..0.759 rows=100 loops=1)
    -> Index scan on users using idx_salary
    (cost=0.0918 rows=100) (actual time=0.731..0.752 rows=100 loops=1)
1. 인덱스 스캔으로 100개의 데이터에 대해서만 액세스 했다.
2. (정렬 작업을 따로 하지 않았다. 왜냐하면 인덱스라서 이미 정렬이 되어 있기 때문이다.)
3. Limit에 의해 100개의 데이터만 조회했다.