🎟️ MySQL 인덱스 실험: 예약 만료 배치 쿼리 성능 개선 (10만 건 실험)

1️⃣ 생각

공연 예매 서비스를 개발하면서 예약 만료 배치 쿼리를 작성하게 됐다. 구조는 단순했다. PENDING_PAYMENT 상태이고 생성 시각이 일정 시간 이전인 행을 뽑아서 만료 처리를 넘기는 흐름이었다.

 

근데 코드를 짜다가 생각이 걸렸다. 지금은 데이터가 얼마 없으니까 빠르게 느껴지지만, 나중에 예약이 수십만 건 쌓이면 이 쿼리가 매번 테이블 전체를 읽어야 할 수도 있겠다는 거였다. 인덱스를 걸면 얼마나 달라지는지 직접 수치로 확인하고 싶었다.

2️⃣ 테이블 구성과 더미 데이터

실험 환경을 따로 만든 이유

운영 테이블(booking)을 직접 건드리는 건 너무 위험하다.

인덱스 생성만 해도 테이블에 락이 걸릴 수 있고, 실험 중에 뭔가 잘못되면 서비스에 영향이 간다.

그래서 booking_test라는 별도 테이블을 만들고 거기서 전부 진행했다. 운영 테이블은 조회조차 하지 않았다.

 

테이블 구조는 실험에 필요한 컬럼만 남겼다. FK나 유니크 제약은 조회 성능 실험과 관계없어서 제거했다.

인덱스가 없는 상태에서 시작하기 위해 PK만 두고 만들었다.

DROP TABLE IF EXISTS booking_test;

CREATE TABLE booking_test (
    id BIGINT NOT NULL AUTO_INCREMENT,
    concert_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    created_at DATETIME(6) NOT NULL,
    status ENUM('PENDING_PAYMENT', 'CONFIRMED', 'CANCELLED') NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

더미 데이터는 10만 건을 넣었다. 재귀 CTE로 시퀀스를 만들고, 난수 하나(r)로 status 분포를 60/25/15로 고정했다.

SET SESSION cte_max_recursion_depth = 100000;

WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 100000
)
INSERT INTO booking_test (concert_id, user_id, created_at, status)
SELECT
    s.concert_id,
    s.user_id,
    s.created_at,
    CASE
        WHEN s.r < 0.60 THEN 'PENDING_PAYMENT'
        WHEN s.r < 0.85 THEN 'CONFIRMED'
        ELSE 'CANCELLED'
    END AS status
FROM (
    SELECT
        n,
        1 + ((n - 1) % 300) AS concert_id,
        1 + ((n - 1) % 10000) AS user_id,
        NOW() - INTERVAL FLOOR(RAND(n * 31) * 90 * 24 * 60) MINUTE AS created_at,
        RAND(n * 17) AS r
    FROM seq
) AS s;

RAND()를 여러 번 호출하면 같은 행 안에서도 값이 달라질 수 있어서, 난수는 한 번만 만들어서 재사용했다.

created_at은 최근 90일 범위에 균등하게 분포하도록 했다.

3️⃣ 실험 쿼리 두 가지

A 쿼리 — 실제 배치 성격

SELECT id, concert_id, user_id, created_at, status
FROM booking_test
WHERE status = 'PENDING_PAYMENT'
  AND created_at < NOW() - INTERVAL 30 MINUTE
ORDER BY created_at ASC
LIMIT 1000;

실제 서비스에서 "30분 이상 결제 안 된 예약 뽑기" 성격의 쿼리다. 조건 범위가 넓어서 필터링되는 행이 많다.

B 쿼리 — 선택도 높은 범위 조건

SELECT id, concert_id, user_id, created_at, status
FROM booking_test
WHERE status = 'PENDING_PAYMENT'
  AND created_at >= NOW() - INTERVAL 2 DAY
  AND created_at < NOW() - INTERVAL 1 DAY
ORDER BY created_at ASC
LIMIT 1000;

하루 단위 범위로 좁혔다. 실제 서비스 쿼리는 아니지만, 인덱스 효과가 선택도에 따라 어떻게 달라지는지 비교하기 위해 추가했다.

4️⃣ 인덱스 없는 상태

EXPLAIN ANALYZE 결과 — A 쿼리

-> Limit: 1000 row(s)  (cost=7863 rows=1000) (actual time=53.3..53.4 rows=1000 loops=1)
    -> Sort: booking_test.created_at, limit input to 1000 row(s) per chunk  (cost=7863 rows=99960) (actual time=53.3..53.3 rows=1000 loops=1)
        -> Filter: ((booking_test.`status` = 'PENDING_PAYMENT') and (booking_test.created_at < <cache>((now() - interval 30 minute))))  (cost=7863 rows=99960) (actual time=2.09..46.5 rows=59993 loops=1)
            -> Table scan on booking_test  (cost=7863 rows=99960) (actual time=2.01..28 rows=100000 loops=1)

가장 안쪽부터 읽으면, 테이블 10만 건을 전부 읽고(Table scan on booking_test), 그중에서 조건에 맞는 약 6만 건을 남기고(Filter), 그걸 정렬한 다음(Sort), 1000건을 반환한다. 아무것도 없이 전부 읽는 구조다.

EXPLAIN ANALYZE 결과 — B 쿼리

-> Limit: 1000 row(s)  (cost=7122 rows=1000) (actual time=46.2..46.3 rows=681 loops=1)
    -> Sort: booking_test.created_at, limit input to 1000 row(s) per chunk  (cost=7122 rows=99960) (actual time=46.2..46.2 rows=681 loops=1)
        -> Filter: ((booking_test.`status` = 'PENDING_PAYMENT') and (booking_test.created_at >= <cache>((now() - interval 2 day))) and (booking_test.created_at < <cache>((now() - interval 1 day))))  (cost=7122 rows=99960) (actual time=1.76..46 rows=681 loops=1)
            -> Table scan on booking_test  (cost=7122 rows=99960) (actual time=1.67..27.4 rows=100000 loops=1)

결과는 681건으로 훨씬 적은데, 실행 시간은 A 쿼리랑 비슷하다. 어차피 필터링 전에 10만 건을 다 읽기 때문이다. 최종 결과가 얼마나 적든 읽는 양은 똑같다.

실행 시간 (5회 측정, 1회차 워밍업 제외 평균)

쿼리	평균 실행 시간
A (범위 넓음)	47.7ms
B (범위 좁음)	45.0ms

두 쿼리가 2.7ms 차이밖에 안 난다. 조건이 아무리 달라도 풀스캔이면 결국 비슷한 시간이 걸린다는 걸 여기서 확인했다.

5️⃣ 인덱스 설계

CREATE INDEX idx_booking_test_status_created_at
ON booking_test (status, created_at);

컬럼 순서가 중요하다.

status = 'PENDING_PAYMENT'는 동등 조건(=)이고, created_at < ? 또는 BETWEEN은 범위 조건이다. 복합 인덱스는 동등 조건 컬럼을 앞에, 범위 조건 컬럼을 뒤에 놓아야 두 조건을 모두 인덱스 안에서 처리할 수 있다.

 

반대로 (created_at, status) 순서로 만들면, created_at이 범위 조건이라 그 뒤에 있는 status는 인덱스 탐색 범위 안에서 동작하지 않는다. status 필터링을 인덱스 밖에서 별도로 해야 한다.

ORDER BY created_at ASC도 인덱스 순서와 일치해서, filesort 없이 정렬이 가능해진다.

6️⃣ 인덱스 적용 후

EXPLAIN ANALYZE 결과 — A 쿼리

-> Limit: 1000 row(s)  (cost=10084 rows=1000) (actual time=17.8..17.9 rows=1000 loops=1)
    -> Index range scan on booking_test using idx_booking_test_status_created_at over (status = 'PENDING_PAYMENT' AND created_at < '2026-04-10 01:15:06.000000'), 
    		with index condition: ((booking_test.`status` = 'PENDING_PAYMENT') and (booking_test.created_at < <cache>((now() - interval 30 minute))))  
            	(cost=10084 rows=49980) (actual time=17.8..17.9 rows=1000 loops=1)

Table scan이 사라지고 Index range scan이 됐다. Sort 단계도 없다.

인덱스 순서대로 읽으면서 바로 정렬된 결과가 나오기 때문이다. LIMIT 1000건을 채우는 순간 멈춘다.

EXPLAIN ANALYZE 결과 — B 쿼리

-> Limit: 1000 row(s)  (cost=306 rows=680) (actual time=0.984..6.43 rows=680 loops=1)
    -> Index range scan on booking_test using idx_booking_test_status_created_at over (status = 'PENDING_PAYMENT' AND '2026-04-08 01:46:20.000000' <= created_at < '2026-04-09 01:46:20.000000'), 
    		with index condition: ((booking_test.`status` = 'PENDING_PAYMENT') and (booking_test.created_at >= <cache>((now() - interval 2 day))) and (booking_test.created_at < <cache>((now() - interval 1 day))))  
            	(cost=306 rows=680) (actual time=0.983..6.35 rows=680 loops=1)

B 쿼리는 더 극적이다. 하루 범위 조건이라 인덱스가 읽어야 할 구간 자체가 좁다.

옵티마이저 추정 cost도 7,122에서 306으로 줄었다. 6.43ms면 거의 즉시 반환 수준이다.

6️⃣ Before vs After

A 쿼리

항목	인덱스 전	인덱스 후
접근 방식	Table scan (풀스캔)	Index range scan
실제 읽은 행	100,000	1,000 (LIMIT 도달 후 중단)
cost (옵티마이저 추정)	7,863	10,084
실행 시간	47.7ms	12.3ms

A 쿼리는 인덱스 후 cost가 오히려 올라갔다. 조건 범위가 넓어서 읽는 행이 여전히 많은데, 인덱스를 타면 랜덤 I/O가 발생하기 때문에 옵티마이저가 비용을 더 높게 추정한 것이다. 그래도 실제 실행 시간은 줄었다.

B 쿼리

항목	인덱스 전	인덱스 후
접근 방식	Table scan (풀스캔)	Index range scan
실제 읽은 행	100,000	680
cost (옵티마이저 추정)	7,122	306
실행 시간	45.0ms	1.98ms

결과에서 뭘 읽을 수 있나?

A 쿼리는 조건 범위가 넓어서 인덱스가 읽어야 할 행이 여전히 많다.

그래도 풀스캔에서 범위 스캔으로 바뀌면서 47.7ms → 12.3ms, 약 74% 감소했다.

 

B 쿼리는 하루 단위 조건이라 선택도가 높다. 인덱스가 읽어야 할 범위가 훨씬 좁아져서 45.0ms → 1.98ms, 약 96% 감소했다.

두 쿼리의 차이가 보여주는 건 하나다. 인덱스 자체보다 쿼리 조건의 선택도가 인덱스 효과를 결정한다. 인덱스를 걸어도 조건이 너무 넓으면 읽는 행이 여전히 많다. 반대로 조건이 구체적일수록 인덱스가 정확하게 해당 구간만 읽어서 차이가 극명하게 난다.

 

7️⃣ 배운 것

실험 전에는 "인덱스 걸면 빨라진다"를 막연하게 알고 있었다.

직접 숫자를 재보니까 조건 하나 차이로 효과가 완전히 달라진다는 게 체감됐다.

 

만료 배치 쿼리에서 created_at 범위를 얼마나 잡느냐에 따라 인덱스 효과가 달라진다는 것도 같이 확인했다.

실제 서비스에서 배치 주기와 조건 범위를 설계할 때 이 부분을 고려해야 할 것 같다.

 

EXPLAIN ANALYZE는 단순히 실행 계획 확인 용도가 아니라, 각 단계가 실제로 몇 건을 읽고 얼마나 걸렸는지 출력해줘서 병목 지점을 정확히 짚는 데 유용했다. 앞으로도 쿼리 최적화할 때는 EXPLAIN만 보지 않고 EXPLAIN ANALYZE까지 같이 볼 것 같다.