인덱스 설계와 쿼리 튜닝

1. 개요

우리는 왜 데이터베이스 최적화를 해야 할까요? 데이터베이스 최적화를 하는 이유는 여러 가지가 있지만,

주된 이유는 시스템의 성능을 높이고 사용자 경험을 향상시키기 위해서입니다.

 

"그럼 그냥 DB서버 좋은거 쓰면 되는거 아냐?"

 

흔히들 이런 질문을 하십니다. 물론 서버 성능을 높이는 것(Scale-up)도 하나의 방법이 될 수 있습니다.

하지만 이는 다음과 같은 한계가 있습니다:

• 비용 효율성: 서버 성능 향상은 비용이 기하급수적으로 증가합니다.
• 한계 존재: 아무리 좋은 서버도 결국 물리적 한계가 존재합니다.
• 근본적 해결 아님: 비효율적인 쿼리는 여전히 비효율적입니다.

 

2. 콘서트 예매 시스템의 특수성

콘서트 예매 시스템은 다음과 같은 특징이 있습니다:

1. 극단적인 동시성
   • 인기 가수의 티켓 오픈 시 수만 명이 동시 접속
   • 1초 내에 수천 개의 좌석 조회 요청 발생
   • 같은 좌석에 대한 동시 예약 시도 다수 발생
2. 즉각적인 응답 필요
   • 좌석 현황이 실시간으로 반영되어야 함
   • 예매 성공/실패 여부를 즉시 알려야 함
   • 사용자 경험에 직접적 영향
3. 데이터 정합성 중요
   • 동일 좌석 중복 예매 방지
   • 예매 상태의 정확한 관리 필요
   • 결제와 연동된 데이터 무결성 보장

 

3. 시스템 구조 살펴보기

3-1. 데이터베이스 스키마

create table CONCERT
(
    id         bigint auto_increment comment '콘서트 ID (PK)'
        primary key,
    title      varchar(255) not null comment '콘서트 제목',
    created_dt datetime     not null comment '생성 시간',
    is_delete  tinyint(1) default 0 not null comment '삭제 여부 (Y, N)'
);

create table CONCERT_SCHEDULE
(
    id                bigint auto_increment comment '콘서트 일정 ID (PK)'
        primary key,
    concert_id        bigint   not null comment '콘서트 ID (FK)',
    open_dt           date     not null comment '콘서트 개최 날짜',
    start_dt          datetime not null comment '콘서트 시작 시간',
    end_dt            datetime not null comment '콘서트 종료 시간',
    total_seat        int      not null comment '전체 좌석 수',
    reservation_seat  int      not null comment '남은 좌석 수',
    total_seat_status enum ('SOLD_OUT', 'AVAILABLE') not null comment '전체 좌석 상태',
    created_dt        datetime not null comment '생성 시간',
    is_delete         tinyint(1) default 0           not null comment '삭제 여부 (Y, N)',
    constraint fk_concert_schedule_concert
        foreign key (concert_id) references CONCERT (id)
            on update cascade on delete cascade
);

create table CONCERT_SEAT
(
    id                  bigint auto_increment comment '좌석 ID (PK)'
        primary key,
    concert_schedule_id bigint   not null comment '콘서트 일정 ID (FK)',
    amount              int      not null comment '좌석 금액',
    position            int      not null comment '좌석 번호',
    seat_status         enum ('AVAILABLE', 'TEMP_RESERVED', 'RESERVED') not null comment '좌석 상태',
    reserved_until_dt   datetime null comment '임시 예약 만료 시간',
    created_dt          datetime not null comment '생성 시간',
    is_delete           tinyint(1) default 0                            not null comment '삭제 여부 (Y, N)',
    constraint fk_concert_seat_schedule
        foreign key (concert_schedule_id) references CONCERT_SCHEDULE (id)
            on update cascade on delete cascade
);

create table USERS
(
    id          bigint auto_increment comment '유저 ID (PK)'
        primary key,
    user_mail   varchar(255)                         not null comment '유저 메일',
    user_amount bigint                               not null comment '잔액',
    created_dt  datetime default current_timestamp() not null comment '생성 시간',
    is_delete   tinyint(1) default 0                   not null comment '삭제 여부 (Y, N)'
);

 

 

4. 주요 조회 패턴 분석

 

4-1. 콘서트 목록 조회

SELECT * FROM CONCERT 
WHERE is_delete = 0 
ORDER BY created_dt DESC;

 

4-2 특정 콘서트의 좌석 현황

SELECT * FROM CONCERT_SEAT 
WHERE concert_schedule_id = ? 
AND seat_status = 'AVAILABLE';

 

4-3 예매 가능한 공연 조회

SELECT c.*, cs.* 
FROM CONCERT c 
JOIN CONCERT_SCHEDULE cs ON c.id = cs.concert_id 
WHERE cs.total_seat_status = 'AVAILABLE' 
AND cs.open_dt >= CURRENT_DATE;

 

 

5. 최적화 전략

5-1. 인덱스 최적화

인덱스는 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조입니다. 마치 책의 목차나 색인과 같은 역할을 합니다.

 

인덱스가 없을 때 (Table Full Scan)

[콘서트 좌석 데이터 - CONCERT_SEAT 테이블]
좌석ID    콘서트일정ID    좌석번호    상태           금액
1         100            1         RESERVED      88000
2         100            2         AVAILABLE     88000
3         100            3         RESERVED      88000
4         100            4         AVAILABLE     88000
...
99999     102            99        RESERVED      110000
100000    102            100       AVAILABLE     110000

 

 

이 상태에서 특정 콘서트(concert_schedule_id = 100)의 예매 가능한 좌석을 찾으려면?

 

1. CONCERT_SEAT 테이블의 첫 번째 행부터 순차적 확인

2. 각 행마다 "concert_schedule_id가 100인가?" + "seat_status가 AVAILABLE인가?" 체크

3. 전체 테이블을 처음부터 끝까지 스캔해야 함

 

인덱스가 있을 때 (Index Scan)

-- 콘서트 좌석 검색을 위한 복합 인덱스
CREATE INDEX idx_seat_search 
ON CONCERT_SEAT (concert_schedule_id, seat_status);

[인덱스 구조]
concert_schedule_id + seat_status 기준으로 정렬된 구조:

100 + AVAILABLE   → 2, 4, 6, 8번 좌석
100 + RESERVED    → 1, 3, 5, 7번 좌석
101 + AVAILABLE   → 1002, 1004번 좌석
101 + RESERVED    → 1001, 1003번 좌석

 

같은 검색을 인덱스로 수행하면:

1. concert_schedule_id = 100 위치로 즉시 이동

2. seat_status = 'AVAILABLE' 조건에 맞는 데이터 바로 접근

3. 실제 테이블 전체를 읽을 필요 없습니다.

 

5-2. 인덱스의 구조와 동작

기본구조 (B-tree)

               [concert_schedule_id: 100]           <- Root Node
               /                        \
    [id:1-50번 좌석]              [id:51-100번 좌석]   <- Internal Nodes
    /              \              /               \
[1-25번]        [26-50번]    [51-75번]        [76-100번] <- Leaf Nodes

 

 

concert_schedule_id = 100인 공연의 30번 좌석 찾기

 

Step 1: Root 노드 검색

[concert_schedule_id: 100] 에서 시작
→ 1-50번 좌석 노드로 이동

 

Step 2: Internal 노드 검색

[1-50번 좌석] 에서
30번은 26-50번 범위에 있으므로
→ [26-50번] 리프 노드로 이동

 

Step 3: Leaf 노드 검색

[26-50번] 노드에서 30번 좌석 정보 찾음
- seat_status: AVAILABLE
- amount: 88,000원

 

6. 실제 인덱스 적용 전략

좌석ID	     콘서트ID	   좌석번호	상태	     금액
  1	      100	    A1	  AVAILABLE	 88000
  2	      100	    A2	  RESERVED	 88000
  3	      100	    A3	  AVAILABLE	 88000
  4	      100	    A4	  RESERVED	 88000
...	...	...	...	...
 9999	  100	   Z99	  AVAILABLE	 110000
 10000	  100	   Z100	  RESERVED	 110000

특정 콘서트 일정에서 예약 가능한 좌석 상태를 빠르게 조회할 수 있도록 자주 조회하는 조건에 인덱스를 생성할 수 있습니다.

 

 

전체 좌석 상태가 AVAILABLE인 콘서트 일정 조회

SELECT * FROM CONCERT_SCHEDULE 
WHERE total_seat_status = 'AVAILABLE' 
AND open_dt >= CURRENT_DATE;

 

인덱스 없이 검색할 때 (Full Table Scan)

1. 테이블의 첫 번째 행부터 차례대로 확인하면서 조건에 맞는 데이터를 찾습니다.

2. 각 행마다 total_seat_status가 'AVAILABLE'인지 확인해야 하므로,
조건을 만족하는 데이터를 찾기 위해 전체 행을 조회하게 됩니다

 

 

인덱스를 사용할 때 (Index Scan)

total_seat_status 컬럼에 인덱스를 추가하여 검색 속도를 높일 수 있습니다.

CREATE INDEX idx_total_seat_status 
ON CONCERT_SCHEDULE (total_seat_status, open_dt);

 

 

트리 구조 설명 (B-tree 구조)

                 [total_seat_status: AVAILABLE]                <- Root Node
                    /                    \
       [open_dt: 과거 일정]             [open_dt: 현재 및 미래 일정]     <- Internal Nodes
      /            \                        /               \
 [1월 일정들]   [2월 일정들]                [3월 일정들]     [4월 일정들] <- Leaf Nodes

 

1. Root 노드에서 'AVAILABLE' 상태로 설정된 전체 좌석 상태를 가진 일정을 찾습니다.

2. Internal 노드에서는 'AVAILABLE' 상태를 가진 일정 중에서 현재 날짜 이후의 일정으로 이동합니다.

3. Leaf 노드에서는 각 일정에 대한 데이터를 저장하여 불필요한 전체 데이터 스캔을 줄입니다.

 

이렇게 인덱스를 적용함으로써, 현재 날짜 이후의 예약 가능한 콘서트 일정을 빠르게 찾을 수 있습니다.

 

 

7. 인덱스 적용이 효과적인 컬럼 종류

인덱스를 효과적으로 적용하려면 특정 조건을 충족하는 컬럼을 선택하는 것이 중요합니다.

특히 아래와 같은 특성을 지닌 컬럼에 인덱스를 추가하면 검색 성능을 크게 향상할 수 있습니다.

 

특히 카디널리티(Cardinality)가 높은 컬럼 데이터 전체에서 중복되지 않고 고유한 값이 많은 컬럼은 인덱스 적용 시
검색 효율이 높아집니다. 이와 같은 컬럼에 인덱스를 설정하면 데이터 필터링이 빠르게 이루어져 쿼리 성능을 개선할 수 있습니다.

• 적합한 예시:
  • user_id: 각 사용자마다 고유한 ID 값
  • concert_id: 콘서트별 고유한 식별자
  • email: 사용자별로 고유한 이메일 주소
• 부적합한 예시:
  • seat_status: 'AVAILABLE', 'RESERVED', 'TEMP_RESERVED'처럼 3가지 값만 가지는 경우, 검색 효율에 큰 도움이 되지 않음
  • is_delete: 0 또는 1 두 가지 값만 존재하는 경우, 인덱스를 적용해도 성능 향상에 기여하지 않음

카디널리티가 높은 컬럼은 인덱스를 통해 특정 데이터에 빠르게 접근할 수 있어, 시스템의 전반적인 성능 향상에 도움이 됩니다.

반면, 선택지가 제한된 컬럼은 인덱스가 성능에 큰 영향을 주지 않으므로 가급적 피하는 것이 좋습니다.

 

 

결론

데이터베이스 최적화는 시스템 성능을 높이고 사용자 경험을 개선하기 위해 필수적입니다. 콘서트 예매 시스템과 같이 대규모 트래픽과

빠른 응답 속도가 요구되는 환경에서는 특히 중요합니다. 최적화를 위해 인덱스를 활용하는 것이 효과적이지만,

단순히 모든 컬럼에 인덱스를 적용하는 것이 아니라, 카디널리티가 높은 컬럼이나 자주 조회되는 컬럼에 집중적으로 적용해야 합니다.

 

서버 스펙을 단순히 높이는 것보다 쿼리 효율성과 데이터베이스 구조를 개선하는 최적화가 장기적으로 비용 효율적이며,

데이터베이스의 물리적 한계를 극복하는 데 도움이 됩니다. 이와 같은 최적화 전략을 통해 대량의 데이터를 효과적으로 처리하고,

사용자가 원활하게 예매 시스템을 이용할 수 있도록 하는 것이 중요합니다.