콘서트 예약 서비스의 인덱스 설계와 성능 비교

 

들어가며

 이번 글에서 콘서트 예약 서비스의 시나리오에서 사용된 쿼리를 분석하고, 인덱스 추가 전후의 성능을 비교해 보며 최적화 과정을 살펴보겠습니다. MariaDB를 사용해 인덱스 추가 전후의 실행 계획(EXPLAIN) 및 수행 속도를 비교한 결과를 통해, 쿼리의 성능을 최종적으로 어떻게 개선하였는지 알아보겠습니다.

 

 

 

시나리오 내 쿼리

먼저 콘서트 예약 서비스의 도메인 별 각 JpaRepository에서 사용되는 쿼리를 모두 수집했습니다. 도메인 별 쿼리를 아래 표로 정리해봤습니다.

 

도메인	메서드
Concert	findAllByConcertId()
Reservation	findByIdOrNullWithLock(), findAllByExpiresAtBeforeAndStatus()
Seat	findByIdOrNullWithLock(), findAllByScheduleId(), countUnavailableSeatsGroupByScheduleId()
Payment	findAllByUserId()
BalanceHistory	findAllByUserId()

 

 

 

자주 조회하거나 복잡한 쿼리

위 쿼리 중 성능 이슈가 발생할 가능성이 높은 쿼리를 식별했습니다.

1. 콘서트 좌석 도메인의 findAllByScheduleId() , 콘서트 도메인의 findAllByConcertId()
   • 특정 ID를 가진 모든 데이터를 조회하고 있습니다.
2. 콘서트 좌석 도메인의 countUnavailableSeatsGroupByScheduleId()
   • 그룹화와 COUNT 연산을 포함하고 있습니다.
3. 예약 도메인의 findAllByExpiresAtBeforeAndStatus()
   • 날짜 범위와 상태 조건에 따라 데이터를 조회하고 있습니다.

 

 

인덱스 설계 과정 및 성능 측정

DB는 MariaDB를 사용했고 10.6.4 버전을 선택했습니다.

실제 콘서트 예약 시스템에서 콘서트가 좌석 5만 석에 달하는 경우가 있으므로, 이번 테스트에서는 콘서트 스케줄 20개마다 5만 개의 좌석을 삽입하여 총 백만 개의 좌석 데이터를 생성했습니다. 이를 통해 실제와 비슷한 시나리오를 모사하고, 위에서 선정한 3개의 쿼리에 대해 인덱스 설계 및 성능 비교를 측정했습니다.

 

 

1. Seat 테이블의 findAllByScheduleId()

select *
from seat
where schedule_id = 7;

위와 같이 좌석을 조회하는 쿼리에 대해  schedule_id로 인덱싱 전후 실행 계획과 성능을 비교해보았습니다.

 

 

인덱싱 전

Explain 결과

쿼리 수행 시간

 

해당 쿼리의 실행 계획을 보면 ALL 스캔이 이루어졌으며, 실행 속도는 352ms였습니다.

 

 

 

인덱싱 후

CREATE INDEX idx_schedule_id ON seat(schedule_id);

 

Explain 결과

쿼리 수행 시간

 

schedule_id 컬럼에 인덱스를 추가한 후에는 ref 스캔으로 변경되었으며, 실행 속도는 65ms로 크게 개선되었습니다.

 

 

2. Seat 테이블의 countUnavailableSeatsGroupByScheduleId()

select schedule_id, COUNT(schedule_id)
from Seat
where status = 'UNAVAILABLE'
group by schedule_id;

seat 테이블에서 상태가 'UNAVAILABLE'인 좌석을 스케줄별로 그룹화하여 COUNT하는 쿼리입니다.

 

 

인덱싱 전

Explain 결과

쿼리 수행 시간

 

status 컬럼에 인덱스가 없는 상태에서 쿼리를 수행한 결과, 실행 속도는 1s 825ms가 소요되었습니다.

 

 

개별 인덱싱 후

CREATE INDEX idx_schedule_id ON seat(schedule_id);
CREATE INDEX idx_status ON seat(status);

Explain 결과

쿼리 수행 시간

 

먼저 schedule_id와 status 컬럼에 개별 인덱스를 추가해보았습니다. 실행 속도는 49ms로 개선되었습니다. 하지만 실행 계획 결과의 Extra를 보면 인덱스가 전체적인 범위 조건을 처리하는 데 충분하지 않아 여전히 임시 테이블과 파일 정렬이 사용되었음을 알 수 있었습니다. 

 

 

복합 인덱싱 후

CREATE INDEX idx_status_schedule_id ON seat(status, schedule_id);

 

Explain 결과

쿼리 수행 시간

 

이후 status와 schedule_id 컬럼에 복합 인덱스를 추가하여 쿼리 성능을 다시 측정했습니다. 복합 인덱스를 사용하니 임시 테이블과 파일 정렬 없이 GROUP BY와 WHERE 조건을 효율적으로 처리할 수 있었고, 실행 속도는 33ms로 크게 개선되었습니다.

 

 

 

3. Reservation 테이블의 findAllByExpiresAtBeforeAndStatus()

SELECT *
FROM reservation
WHERE expires_at < '2024-11-20 00:00:00' AND status = 'PENDING';

예약 테이블에서 만료 시간과 상태 조건을 사용하여 데이터를 조회하는 쿼리입니다. 예약 테이블에도 백만 개의 데이터를 삽입한 후 인덱스 추가 전후를 비교했습니다.

 

 

인덱싱 전

Explain 결과

쿼리 수행 시간

 

expires_at과 status 조건에 대해 인덱스가 없을 때 실행 속도는 99ms였습니다.

 

 

복합 인덱스 추가 후

 

1. idx_expiresAt_status 시도

CREATE INDEX idx_expiresAt_status ON reservation (expires_at, status);

Explain 결과

쿼리 수행 시간

 

처음에는 expires_at과 status의 순서로 복합 인덱스를 추가했습니다.

그러나 쿼리 옵티마이저가 이 인덱스를 사용하지 않는 경우가 발생했습니다. 이는 expires_at이 범위 조건으로 사용되면서 인덱스의 효율이 떨어졌기 때문입니다.

 

 

 

2. idx_status_expiresAt 시도

 

Explain 결과

쿼리 수행 시간

 

이후 인덱스 순서를 status, expires_at로 변경하자, status 조건으로 먼저 필터링한 후 expires_at을 이용해 범위를 좁히는 방식으로 인덱스가 효율적으로 사용되었고, 실행 속도는 66ms로 개선되었습니다.

 

 

 

결론

이번 최적화 과정을 통해 복합 인덱스의 순서가 쿼리의 효율성에 큰 영향을 미친다는 것을 확인할 수 있었습니다. 특히 status와 expires_at의 복합 인덱스에서, 쿼리 조건에 맞는 순서로 인덱스를 설계해야 인덱스가 올바르게 사용되었습니다. 최종적으로 다음과 같은 인덱스를 적용했습니다.

CREATE INDEX idx_schedule_id ON seat(schedule_id);
CREATE INDEX idx_status_schedule_id ON seat(status, schedule_id);
CREATE INDEX idx_status_expiresAt ON reservation (status, expires_at);

 

위 인덱스를 통해 자주 사용하는 쿼리의 성능을 아래와 같이 최적화할 수 있었습니다.

쿼리	인덱싱 전 실행 시간 (ms)	인덱싱 후 실행 시간 (ms)
findAllByScheduleId()	352	65
countUnavailableSeatsGroupByScheduleId()	1825	33
findAllByExpiresAtBeforeAndStatus()	99	66

 

 

 

마치며

이번 글에서는 콘서트 예약 서비스의 주요 쿼리를 분석하고, 인덱스를 추가하여 성능을 개선해보았습니다. 인덱스 설계 시에는 데이터 분포와 쿼리 패턴을 고려하는 것이 중요하며, 인덱스의 컬럼 순서도 쿼리 성능에 큰 영향을 미칠 수 있다는 것을 배울 수 있었습니다.