콘서트 예약 시스템 인덱스 성능 최적화 분석 보고서

개요

안녕하세요. 이번에 콘서트 예약 시스템을 만들면서 DB 성능 최적화를 위한 인덱스를 적용한 내용을 정리해보려고 합니다.

특히 B-Tree 구조를 이해하고 나니 어떤 컬럼을 어떤 순서로 인덱스에 넣어야 할지가 더 명확해졌습니다.

 

1.콘서트 스케줄 조회 쿼리

CREATE INDEX idx_schedule_availability 
    ON CONCERT_SCHEDULE (total_seat_status, is_delete, open_dt);

 

제가 생각 한 B-Tree 구조입니다:

               [total_seat_status]
                /                \
        [AVAILABLE]           [SOLD_OUT]
           /                      \
    [is_delete]              [is_delete]
      /     \                  /     \
  [false]  [true]          [false]  [true]
    /                        /
[open_dt]               [open_dt]

 

처음에는 그냥 WHERE 절에 있는 순서대로 인덱스를 만들었는데.... 그게 아니였습니다...

예를 들어 콘서트 조회할 때:

• 먼저 예약 가능한(AVAILABLE) 콘서트만 보여줘야 해서 total_seat_status로 첫 필터링
• 삭제된 건 볼 필요 없으니 is_delete로 한번 더 필터링
• 날짜순으로 정렬도 해야 해서 open_dt를 마지막에 넣었습니다
  
  

 

1.1 콘서트 스케줄 조회 성능 테스트 쿼리

SET @start = NOW(6);
SELECT cs.*
FROM CONCERT_SCHEDULE cs
WHERE cs.total_seat_status = 'AVAILABLE'
  AND cs.is_delete = false
  AND cs.open_dt >= CURDATE()
ORDER BY cs.open_dt;

SELECT CONCAT('스케줄 조회 실행시간: ',
    CAST(TIMESTAMPDIFF(MICROSECOND, @start, NOW(6))/1000.0 AS DECIMAL(10,2)), 'ms');

EXPLAIN FORMAT=JSON
SELECT cs.*
FROM CONCERT_SCHEDULE cs
WHERE cs.total_seat_status = 'AVAILABLE'
  AND cs.is_delete = false
  AND cs.open_dt >= CURDATE()
ORDER BY cs.open_dt;

1.2 실행 쿼리 비교

인덱스 적용 전:

• access_type: ALL (전체 테이블 스캔)
• rows: 9,907
• cost: 8.27990073
• 특징: filesort 발생

인덱스 적용 후:

• possible_keys: idx_schedule_availability
• rows: 9,907 (filtered: 70.48%)
• cost: 6.330637396
• 특징: 인덱스를 활용한 필터링으로 비용 감소

 

2. 좌석 조회 쿼리

CREATE INDEX idx_seat_availability 
    ON CONCERT_SEAT (concert_schedule_id, seat_status, position);

 

제가 생각 한 B-Tree 구조입니다:

[concert_schedule_id: 1...n]
/             |        \
[AVAILABLE] [TEMP]  [RESERVED]
/             |         \
[position] [position] [position]

 

좌석 조회는 항상 특정 콘서트의 좌석을 보여줘야 해서 concert_schedule_id를 첫 번째로 뒀습니다.

한 콘서트당 60개 좌석이 있다고 가정할 때, 이걸 먼저 찾고 그 안에서 예약 가능한 좌석을 찾는 게 효율적이라고 생각했습니다.

 

2.1 좌석 조회 성능 테스트 쿼리

SET @start = NOW(6);
SELECT cs.*
FROM CONCERT_SEAT cs
WHERE cs.concert_schedule_id = 123
  AND cs.seat_status = 'AVAILABLE'
  AND cs.is_delete = false
ORDER BY cs.position;

SELECT CONCAT('좌석 조회 실행시간: ',
    CAST(TIMESTAMPDIFF(MICROSECOND, @start, NOW(6))/1000.0 AS DECIMAL(10,2)), 'ms');

EXPLAIN FORMAT=JSON
SELECT cs.*
FROM CONCERT_SEAT cs
WHERE cs.concert_schedule_id = 123
  AND cs.seat_status = 'AVAILABLE'
  AND cs.is_delete = false
ORDER BY cs.position;

 

 

2.2 실행 쿼리 비교

인덱스 적용 전:

• access_type: ref
• possible_keys: fk_concert_seat_schedule
• rows: 1,000
• cost: 1.81514072
• 특징: FK 인덱스만 활용

인덱스 적용 후:

• access_type: ref
• possible_keys: idx_seat_availability
• key_length: 9
• rows: 352
• cost: 0.6396952
• 특징: 복합 인덱스 활용으로 비용 64.7% 감소

 

3. 예약 조회 쿼리

-- 예약 조회용
CREATE INDEX idx_reservation_lookup 
    ON RESERVATION (user_id, status, reserved_dt);

-- 임시 예약 관리용
CREATE INDEX idx_reservation_temp 
    ON RESERVATION (status, reserved_until_dt, is_delete)
WHERE status = 'TEMP_RESERVED';

 

제가 생각 한 B-Tree 구조입니다:

              [user_id: 1...n]
                      |
                  [status]
                /    |     \
        [TEMP] [RESERVED] [CANCELED]
           |        |         |
    [reserved_dt] ...       ...

 

예약 조회는 두 가지 용도로 나눠서 인덱스를 만들었습니다.

1. 사용자가 자기 예약 내역 볼 때 (idx_reservation_lookup)
2. 시스템이 임시 예약 만료 체크할 때 (배치용) (idx_reservation_temp)

특히 임시 예약 인덱스는 부분 인덱스라고 해서 status = 'TEMP_RESERVED' 인 것만 인덱스로 만들었습니다.

 

3.1 예약 조회 성능 테스트 쿼리

SET @start = NOW(6);
SELECT r.*
FROM RESERVATION r
WHERE r.user_id = 123
  AND r.status = 'RESERVED'
  AND r.is_delete = false
ORDER BY r.reserved_dt DESC;

SELECT CONCAT('예약 조회 실행시간: ',
    CAST(TIMESTAMPDIFF(MICROSECOND, @start, NOW(6))/1000.0 AS DECIMAL(10,2)), 'ms');

EXPLAIN FORMAT=JSON
SELECT r.*
FROM RESERVATION r
WHERE r.user_id = 123
  AND r.status = 'RESERVED'
  AND r.is_delete = false
ORDER BY r.reserved_dt DESC;

 

3.2 실행 쿼리 비교

인덱스 적용 전:

• access_type: ref
• possible_keys: fk_reservation_user
• rows: 1
• cost: 0.00345856
• 특징: filesort 발생

인덱스 적용 후:

• access_type: ref
• possible_keys: idx_reservation_lookup, idx_reservation_temp
• rows: 1
• cost: 0.00345856
• 특징: 복합 인덱스로 정렬 연산 최적화

 

4. 부하테스트 성능 비교 (K6)

실제 콘서트 예매 상황을 최대한 현실적으로 시뮬레이션하기 위해, 대기열 진입부터 결제까지 전체 예매 플로우를 테스트했습니다.

 

4.1 테스트 환경

• 총 테스트 시간: 50초
• 최대 동시 사용자: 5명
• 성공 기준: 응답시간 p95 < 2초, 에러율 < 1%

import http from 'k6/http';
import { check, sleep } from 'k6';

export const options = {
    scenarios: {
        concert_reservation: {
            executor: 'ramping-vus',
            startVUs: 1,
            stages: [
                { duration: '10s', target: 5 },
                { duration: '30s', target: 5 },
                { duration: '10s', target: 0 },
            ],
        },
    },
    thresholds: {
        http_req_duration: ['p(95)<2000'],
        http_req_failed: ['rate<0.01'],
    },
};

const BASE_URL = 'http://localhost:8080/v1/api';

export function setup() {
    const initialUserId = 1;
    const setupRes = http.post(`${BASE_URL}/queue/token`,
        JSON.stringify({ userId: initialUserId }),
        { headers: { 'Content-Type': 'application/json' } }
    );

    console.log('초기 설정 완료');
    return { setupToken: setupRes.json().data.queueToken };
}

export default function (data) {
    const userId = Math.floor(Math.random() * 1000000) + 1;

    // 1. 토큰 발급
    const tokenRes = http.post(`${BASE_URL}/queue/token`,
        JSON.stringify({ userId: userId }),
        { headers: { 'Content-Type': 'application/json' } }
    );

    check(tokenRes, { '토큰 생성 성공': (r) => r.status === 200 });
    const token = tokenRes.json().data.queueToken;

    // 2. 대기열 확인
    let queueStatus = 'WAITING';
    let attempts = 0;
    const maxAttempts = 15;  // 재시도 횟수 증가
    const waitTime = 2;  // 대기 시간을 늘려 서버 부하 완화

    while (queueStatus !== 'PROGRESS' && attempts < maxAttempts) {
        const queueRes = http.post(`${BASE_URL}/queue/token/check`, null, {
            headers: { 'Content-Type': 'application/json', 'Authorization': token }
        });

        check(queueRes, { '대기열 확인 성공': (r) => r.status === 200 });
        queueStatus = queueRes.status === 200 ? queueRes.json().data.status : queueStatus;

        if (queueStatus === 'PROGRESS') break;

        attempts++;
        sleep(waitTime);  // 대기 시간 조정
    }

    if (queueStatus === 'PROGRESS') {
        // 3. 콘서트 스케줄 조회
        const scheduleRes = http.get(`${BASE_URL}/concerts/schedule`, {
            headers: { 'Content-Type': 'application/json', 'Authorization': token }
        });

        check(scheduleRes, { '스케줄 조회 성공': (r) => r.status === 200 });

        if (scheduleRes.status === 200 && scheduleRes.json().data.length > 0) {
            const scheduleId = scheduleRes.json().data[0].scheduleId;

            // 4. 좌석 조회
            const seatRes = http.get(`${BASE_URL}/concerts/seat?scheduleId=${scheduleId}`, {
                headers: { 'Content-Type': 'application/json', 'Authorization': token }
            });

            check(seatRes, { '좌석 조회 성공': (r) => r.status === 200 });

            if (seatRes.status === 200 && seatRes.json().data.length > 0) {
                const seatId = seatRes.json().data[0].seatId;

                // 5. 좌석 임시 예약
                const reserveRes = http.post(`${BASE_URL}/concerts/reserve`,
                    JSON.stringify({ scheduleId: scheduleId, seatId: seatId }),
                    { headers: { 'Content-Type': 'application/json', 'Authorization': token } }
                );

                check(reserveRes, { '좌석 예약 성공': (r) => r.status === 200 });

                if (reserveRes.status === 200) {
                    const reservationId = reserveRes.json().data.reservationId;

                    // 6. 결제 처리
                    const paymentRes = http.post(`${BASE_URL}/concerts/payment`,
                        JSON.stringify({ reservationId: reservationId }),
                        { headers: { 'Content-Type': 'application/json', 'Authorization': token } }
                    );

                    check(paymentRes, { '결제 성공': (r) => r.status === 200 });
                }
            }
        }
    } else {
        console.warn(`사용자 ${userId}은 PROGRESS 상태에 도달하지 못했습니다.`);
    }

    sleep(1);
}

export function teardown(data) {
    console.log('테스트 완료');
}

 

인덱싱 전

 

 

인덱싱 후

 

4.2 상세 성능 비교

기본 지표

지표	인덱싱 적용 전	인덱싱 적용 후	변화
체크 성공률	100% (160/160)	100% (160/160)	-
데이터 수신	36kB (500B/s)	36kB (500B/s)	-
데이터 송신	61kB (844B/s)	61kB (843B/s)	-0.1%
초당 요청수	2.235871/s	2.235729/s	-0.006%

 

응답시간 분석

구분	인덱스 적용 전	인덱스 적용 후	변화
평균 응답시간	44.62ms	44.47ms	-0.34%
최소 응답시간	3.63ms	4.32ms	+19%
최대 응답시간	56.66ms	80.93ms	+42.8%
p90 응답시간	52.64ms	52.21ms	-0.82%
p95 응답시간	53.54ms	54.18ms	+1.2%

 

HTTP 요청 상세

단계	인덱스 적용 전	인덱스 적용 후	변화
연결 대기(blocked)	avg=42.49μs	avg=44.21μs	+4.0%
연결 수립(connecting)	avg=14.67μs	avg=11.71μs	-20.2%
응답 수신(receiving)	avg=362.22μs	avg=269.18μs	-25.7%
요청 전송(sending)	avg=63μs	avg=59.72μs	-5.2%
서버 처리(waiting)	avg=44.2ms	avg=44.14ms	-0.14%

 

 

4.3 주요 발견사항

긍정적 변화 ❤️

1. 응답 수신 시간 개선
   • 362.22μs → 269.18μs (25.7% 감소)
   • 인덱스 적용으로 데이터 조회 속도 향상
2. 연결 관리 효율화
   • 연결 수립 시간 20.2% 감소
   • 요청 전송 시간 5.2% 감소

 

부정적 변화 💔

1. 최대 응답시간 증가
   • 56.66ms → 80.93ms (42.8% 증가)
   • 인덱스 유지관리 오버헤드 영향으로 추정....
2. p95 응답시간 소폭 증가
   • 53.54ms → 54.18ms (1.2% 증가)
   • 대부분의 요청은 안정적이나 일부 지연 발생

음... 최대 응답시간이 좀 늘어난 건 신경쓰이네요....
아마도 인덱스 유지하는 오버헤드 때문인 것 같은데, 이 부분은 좀 더 공부해봐야 할 듯 합니다....

 

 

5. 추가로 공부 해보면 좋은것들...📖

1. 파티셔닝
   • 날짜별로 테이블을 나누면 더 빨라질 것 같아요...
   • 특히 지난 콘서트 데이터는 따로 관리하면 좋을 듯!
2. 캐싱 전략 (아직 지식 부족...)
   • 자주 조회되는 콘서트는 캐시 써보기 (현재는 대기열에만 사용 중 레디스 말고 로컬 캐시도 공부해보자...)
   • Redis 공부 좀 더 해보자...
3. 쿼리 튜닝
   • EXPLAIN 결과 더 자세히 공부하기
   • 실행계획 분석하는 법 배우기

 

6. 결론

공부하면서 느낀 건... 역시 실제로 해보면서 배우는 게 제일 잘 와닿는 것 같습니다..

 

인덱스 적용을 통해 특히 좌석 조회 성능이 크게 개선되었습니다. 다만 최대 응답 시간이 증가한 부분은 확인이 필요해 보입니다.

전반적으로 안정적인 성능을 보이면서도, 특히 조회 성능에서 눈에 띄는 개선이 있었습니다.

추가 고려사항

1. 인덱스 유지보수에 따른 오버헤드 모니터링
2. 피크 타임의 성능 안정화 방안 검토
3. 주기적인 인덱스 재구성 필요성 검토

이 테스트 결과를 바탕으로 좀 더 개선할 수 있는 부분을 시간날때 찾아봐야 할 것 같습니다.