인덱스 설계와 쿼리 튜닝
1. 개요
우리는 왜 데이터베이스 최적화를 해야 할까요? 데이터베이스 최적화를 하는 이유는 여러 가지가 있지만,
주된 이유는 시스템의 성능을 높이고 사용자 경험을 향상시키기 위해서입니다.
흔히들 이런 질문을 하십니다. 물론 서버 성능을 높이는 것(Scale-up)도 하나의 방법이 될 수 있습니다.
하지만 이는 다음과 같은 한계가 있습니다:
- 비용 효율성: 서버 성능 향상은 비용이 기하급수적으로 증가합니다.
- 한계 존재: 아무리 좋은 서버도 결국 물리적 한계가 존재합니다.
- 근본적 해결 아님: 비효율적인 쿼리는 여전히 비효율적입니다.
2. 콘서트 예매 시스템의 특수성
콘서트 예매 시스템은 다음과 같은 특징이 있습니다:
- 극단적인 동시성
- 인기 가수의 티켓 오픈 시 수만 명이 동시 접속
- 1초 내에 수천 개의 좌석 조회 요청 발생
- 같은 좌석에 대한 동시 예약 시도 다수 발생
- 즉각적인 응답 필요
- 좌석 현황이 실시간으로 반영되어야 함
- 예매 성공/실패 여부를 즉시 알려야 함
- 사용자 경험에 직접적 영향
- 데이터 정합성 중요
- 동일 좌석 중복 예매 방지
- 예매 상태의 정확한 관리 필요
- 결제와 연동된 데이터 무결성 보장
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 두 가지 값만 존재하는 경우, 인덱스를 적용해도 성능 향상에 기여하지 않음
카디널리티가 높은 컬럼은 인덱스를 통해 특정 데이터에 빠르게 접근할 수 있어, 시스템의 전반적인 성능 향상에 도움이 됩니다.
반면, 선택지가 제한된 컬럼은 인덱스가 성능에 큰 영향을 주지 않으므로 가급적 피하는 것이 좋습니다.
결론
데이터베이스 최적화는 시스템 성능을 높이고 사용자 경험을 개선하기 위해 필수적입니다. 콘서트 예매 시스템과 같이 대규모 트래픽과
빠른 응답 속도가 요구되는 환경에서는 특히 중요합니다. 최적화를 위해 인덱스를 활용하는 것이 효과적이지만,
단순히 모든 컬럼에 인덱스를 적용하는 것이 아니라, 카디널리티가 높은 컬럼이나 자주 조회되는 컬럼에 집중적으로 적용해야 합니다.
서버 스펙을 단순히 높이는 것보다 쿼리 효율성과 데이터베이스 구조를 개선하는 최적화가 장기적으로 비용 효율적이며,
데이터베이스의 물리적 한계를 극복하는 데 도움이 됩니다. 이와 같은 최적화 전략을 통해 대량의 데이터를 효과적으로 처리하고,
사용자가 원활하게 예매 시스템을 이용할 수 있도록 하는 것이 중요합니다.