나의 콘서트 예약 서비스에서 어떤 부분에 인덱스를 사용하여 쿼리성능을 향상시킬 수 있을까?
(글 맨 하단쪽 현재 나의 주요 로직들을 실행하였을 때 발생하는 쿼리들을 첨부해놓았다.)
💡 인덱스가 어디에 적절할까 고민했던 ! 부분 ! 💡
같은 쿼리가 많이 발생하는 부분은 어디일까?!?
1. 예약 가능 좌석 조회 시 해당 부분 좌석 수(50) 만큼 실행
select
r1_0.reservation_id,
r1_0.concert_id,
r1_0.name,
r1_0.reservation_date,
r1_0.seat_id,
r1_0.status,
r1_0.user_id,
r1_0.version
from
reservations r1_0
where
r1_0.seat_id=?
x 50 2 . 콘서트 이벤트 생성 시 각 좌석을 하나씩 다 insert(50번)
→ 하지만!! 인덱스에는 적절하지 않다. 인덱스는 앞 포스트에서 설명했던 것과 같이 인덱스의 목적은 검색이다.
=> 따라서 insert 는 많이 실행되지만 인덱스와는 NO..
(그럼 이런 경우는 어떻게 해야하지..? 배치처리?)
50번 실행(좌석이 50개라서)
insert
into
seats (available, event_id, seat_number)
values
(?, ?, ?)3 . 유저 조회
- 콘서트 생성 및 이벤트 생성을 제외하고는 user 를 select 해야한다.
4 . 콘서트 조회
- User 와 마찬가지로 콘서트 조회 역시 유저 생성하는것을 제외하고는 거의 모든 경우 콘서트 테이블을 확인해야한다.
💡 그래서 인덱스 어디에 적절했을까? 💡
1. 예약 가능 좌석 조회 시 해당 부분 좌석 수(50) 만큼 실행
- seat_id 에 인덱스를 설정
설정 이유 : 위처럼 where seat_id = ? 조건으로 자주 조회되기 때문에, seat_id 에 인덱스를 추가하면 해당 조건으로 조회 시 성능 향상을 기대 - 또한 예약의 경우에도 seat_id 를 where 로 검색
예약 서비스이므로 seat_id 로 조회하는 경우는 많을 것이기 때문에, 이 부분에 index 설정을 하는 것이 좋겠다고 판단
2 . 콘서트 이벤트 생성 시 각 좌석을 하나씩 다 insert(50번) - (인덱스와는 무관하지만 이후 작업 진행을 위해 일단 기록..)
- 인덱스는 검색 성능을 향상시키는 것이 목표 , insert 작업 시 성능 저하 우려
- 배치 삽입 설정 고려
- 트랜잭션 관리 최적화 고려
단일 트랜잭션 내에서 50개의 insert 를 처리 한다면? 커밋 수를 줄여 성능을 향상시킬 수 있을 것이라고 기대.
2.1 [추가] 콘서트 이벤트 생성 시 복합 인덱스를 고려
- 콘서트 이벤트를 생성하려면 일단 이벤트정보를 조회해야한다.
- 이벤트 생성 로직 및 쿼리를 보면
<이벤트 생성 로직 중 이벤트 repo 에서 콘서트정보와 날짜 확인>
if (concertEventRepository.existsByConcertAndEventDate(concert, eventDate)) {
...
}<이벤트 생성 로직 중 이벤트 repo 에서 콘서트정보와 날짜 확인 - SELECT query>
select
ce1_0.event_id
from
concert_events ce1_0
where
ce1_0.concert_id=?
and ce1_0.event_date=?이와 같이 where 절에 동시에 concert_id 와 event_date 가 사용
둘을 동시에 사용하기 때문에 여기에 복합 인덱스를 사용하면 좋을 것이라고 판단
- 그렇다면 이 경우 복합인덱스 순서 결정은 어떻게?
인덱스의 효율성은 선택도 & 카디널리티에 크게 의존. 선택도 & 카디널리티가 높은(중복도가 낮은) 컬럼일수록 인덱스의 효과가 크다.
선택도 & 카디널리티가 높음: 컬럼의 값이 다양하고, 특정 값으로 필터링할 때 반환되는 행 수가 적음.
예: concert_id가 10개의 콘서트 중 하나라면, 각 콘서트에 대한 concert_id의 선택성은 높다.
선택도 & 카디널리티가 낮음: 컬럼의 값이 반복적이고, 특정 값으로 필터링할 때 반환되는 행 수가 많음.
예: event_date가 동일한 날에 여러 이벤트가 있다면, 선택성이 낮다.
💡💡 따라서 나는 카디널리티가 높은, 즉 중복도가 낮은 concert_id 를 복합 인덱스의 첫번 째 칼럼으로 배치하기로 결정!!
3 . 유저 조회 & 콘서트 조회
- id 는 이미 기본키로 설정되어 있으므로 자동으로 인덱스가 설정
- 상황에 따라 복합 인덱스를 고려해볼 수 있을 것 같다. 예를 들어
CREATE INDEX idx_concerts_id_date ON concerts(concert_id, event_date);
🤔 적용 전 예상
좌석 조회 시 발생하는 where seat_id = ? 에 인덱스를 적용하기 전에, 과연 얼마정도의 쿼리 실행 시간을 단축시킬 수 있을까?
콘서트 하나 당 좌석들이 모두(50개) 존재한다고 했을 때 50번을 모두 풀 스캔으로 조회한다면, 콘서트가 10000개면 * 50 으로 500,000 번 조회를 한다. 인덱스를 걸어주면 정렬된 곳에서 가져오니까 데이터가 많을 수록 괄목할만한 차이를 보이지 않을까? ( 마치 2배의 법칙처럼,,)
✅ 쿼리 시간 테스트 (where seat_id = ?)
- 테스트 방식: MySQL Profiling
- 데이터 : 10 만건
- MySQL에서 실행한 쿼리들이 각 수행 시간이 얼마가 걸렸는지 확인할 수 있는 기능으로 쿼리 프로파일링(Query Profiling)을 제공
profiling 설정을 활성화하면 앞으로 실행되는 모든 쿼리문의 수행 시간을 기록
- MySQL에서 실행한 쿼리들이 각 수행 시간이 얼마가 걸렸는지 확인할 수 있는 기능으로 쿼리 프로파일링(Query Profiling)을 제공
💡 인덱스 적용 전
- 쿼리 실행시간 : 0.00111 ms
💡 인덱스 적용 후
- 쿼리 실행시간 : 0.000473 ms
💡 결론
약 57.4%의 실행 시간이 단축
😰 아쉬운 점
테스트툴에 미숙함으로 서비스에서 더미데이터를 갖다 넣었는데 시간도 너무 오래걸리고, 그래서 더 많은 데이터로 테스트를 하지 못했던 점이 많이 아쉽다. 적은 데이터양인데도 불구하고 성능향상을 보았으니 테스트툴에 익숙해져 더 많은 데이터로 확인을 해 보고 다시 글을 추가로 작성하도록 하겠다.
💡 발생하는 쿼리
유저생성
select
u1_0.id,
u1_0.name,
u1_0.point,
u1_0.uuid,
u1_0.version
from
users u1_0
where
u1_0.name=?
Hibernate:
/* insert for
com.example.concertreservationsystem.domain.model.User */insert
into
users (name, point, uuid, version)
values
(?, ?, ?, ?)콘서트 생성
Hibernate:
/* <criteria> */ select
c1_0.concert_id,
c1_0.name,
c1_0.price
from
concerts c1_0
where
c1_0.name=?
Hibernate:
/* insert for
com.example.concertreservationsystem.domain.model.Concert */insert
into
concerts (name, price)
values
(?, ?)이벤트 생성
Hibernate:
/* <criteria> */
select
c1_0.concert_id,
c1_0.name,
c1_0.price
from
concerts c1_0
where
c1_0.name=?
Hibernate:
/* <criteria> */
select
ce1_0.event_id
from
concert_events ce1_0
where
ce1_0.concert_id=?
and ce1_0.event_date=?
limit
?
Hibernate:
/* insert for
com.example.concertreservationsystem.domain.model.ConcertEvent */
insert
into
concert_events (available_seats, concert_id, event_date, total_seats)
values
(?, ?, ?, ?)
Hibernate:
/* insert for
com.example.concertreservationsystem.domain.model.Seat */
insert
into
seats (available, event_id, seat_number)
values
(?, ?, ?)
x50 대기열 생성
Hibernate:
/* <criteria> */ select
u1_0.id,
u1_0.name,
u1_0.point,
u1_0.uuid,
u1_0.version
from
users u1_0
where
u1_0.uuid=?
Hibernate:
/* insert for
com.example.concertreservationsystem.domain.model.QueueEntry */insert
into
queues (join_queue, queue_position, queue_token, user_id)
values
(?, ?, ?, ?)
예약 생성
Hibernate:
select
u1_0.id,
u1_0.name,
u1_0.point,
u1_0.uuid,
u1_0.version
from
users u1_0
where
u1_0.id=?
Hibernate:
select
c1_0.concert_id,
c1_0.name,
c1_0.price
from
concerts c1_0
where
c1_0.concert_id=?
Hibernate:
/* SELECT
s
FROM
Seat s
WHERE
s.seatNumber = :seatNumber
AND s.concertEvent.id = :eventId */ select
s1_0.id,
s1_0.available,
s1_0.event_id,
s1_0.seat_number
from
seats s1_0
where
s1_0.seat_number=?
and s1_0.event_id=? for update
Hibernate:
select
r1_0.reservation_id,
r1_0.concert_id,
r1_0.name,
r1_0.reservation_date,
r1_0.seat_id,
r1_0.status,
r1_0.user_id,
r1_0.version
from
reservations r1_0
where
r1_0.seat_id=?
Hibernate:
/* insert for
com.example.concertreservationsystem.domain.model.Reservation */insert
into
reservations (concert_id, name, reservation_date, seat_id, status, user_id, version)
values
(?, ?, ?, ?, ?, ?, ?)
Hibernate:
/* update
for com.example.concertreservationsystem.domain.model.Seat */update seats
set
available=?,
event_id=?,
seat_number=?
where
id=?
잔액 충전
Hibernate:
select
u1_0.id,
u1_0.name,
u1_0.point,
u1_0.uuid,
u1_0.version
from
users u1_0
where
u1_0.id=?
Hibernate:
/* update
for com.example.concertreservationsystem.domain.model.User */update users
set
name=?,
point=?,
uuid=?,
version=?
where
id=?
and version=?
잔액 조회
Hibernate:
select
u1_0.id,
u1_0.name,
u1_0.point,
u1_0.uuid,
u1_0.version
from
users u1_0
where
u1_0.id=?
콘서트 결제
Hibernate:
select
u1_0.id,
u1_0.name,
u1_0.point,
u1_0.uuid,
u1_0.version
from
users u1_0
where
u1_0.id=?
Hibernate:
select
r1_0.reservation_id,
r1_0.concert_id,
r1_0.name,
r1_0.reservation_date,
r1_0.seat_id,
r1_0.status,
r1_0.user_id,
r1_0.version
from
reservations r1_0
where
r1_0.reservation_id=?
Hibernate:
select
c1_0.concert_id,
c1_0.name,
c1_0.price
from
concerts c1_0
where
c1_0.concert_id=?
Hibernate:
/* <criteria> */ select
qe1_0.queue_id,
qe1_0.join_queue,
qe1_0.queue_position,
qe1_0.queue_token,
qe1_0.user_id
from
queues qe1_0
where
qe1_0.user_id=?
Hibernate:
/* update
for com.example.concertreservationsystem.domain.model.User */update users
set
name=?,
point=?,
uuid=?,
version=?
where
id=?
and version=?
Hibernate:
/* update
for com.example.concertreservationsystem.domain.model.Reservation */update reservations
set
concert_id=?,
name=?,
reservation_date=?,
seat_id=?,
status=?,
user_id=?,
version=?
where
reservation_id=?
and version=?
Hibernate:
/* delete for com.example.concertreservationsystem.domain.model.QueueEntry */delete
from
queues
where
queue_id=?
예약 가능 좌석 조회(캐시 적용)
Hibernate:
select
u1_0.id,
u1_0.name,
u1_0.point,
u1_0.uuid,
u1_0.version
from
users u1_0
where
u1_0.id=?
Hibernate:
/* SELECT
s
FROM
Seat s
WHERE
s.concertEvent.id = :eventId
AND s.available = true */ select
s1_0.id,
s1_0.available,
s1_0.event_id,
s1_0.seat_number
from
seats s1_0
where
s1_0.event_id=?
and s1_0.available=1
Hibernate:
select
r1_0.reservation_id,
r1_0.concert_id,
r1_0.name,
r1_0.reservation_date,
r1_0.seat_id,
r1_0.status,
r1_0.user_id,
r1_0.version
from
reservations r1_0
where
r1_0.seat_id=?
x 50 예약 가능 날짜 조회(캐시 적용)
Hibernate:
select
u1_0.id,
u1_0.name,
u1_0.point,
u1_0.uuid,
u1_0.version
from
users u1_0
where
u1_0.id=?
Hibernate:
/* SELECT
e
FROM
ConcertEvent e
WHERE
e.availableSeats > 0 */ select
ce1_0.event_id,
ce1_0.available_seats,
ce1_0.concert_id,
ce1_0.event_date,
ce1_0.total_seats
from
concert_events ce1_0
where
ce1_0.available_seats>0
Hibernate:
select
c1_0.concert_id,
c1_0.name,
c1_0.price
from
concerts c1_0
where
c1_0.concert_id=?