콘서트 예약시스템 - Index에 따른 속도 차이와 개선 보고서 (인덱싱 초보의 보고서 입니다. 검색으로 들어오신 분은 정확한 정보가 아닐 수 있으니 주의하세요!)

🙂개요 

- 나의 시나리오에서 수행하는 쿼리들을 수집해보고, 필요하다고 판단되는 인덱스를 추가하고 쿼리의 성능개선 정도를 작성하여 제출

• 자주 조회하는 쿼리, 복잡한 쿼리 파악
• Index 추가 전후 Explain, 실행시간 등 비교

---

 

🚩 콘서트 예약시스템 사용자 플로우

1. 사용자 첫 접근 시 토큰을 발급, -> 대기열에서 기다림 ->차례가 오면 활성화 토큰
2. 콘서트 예약 가능 날짜를 조회한다.
3. 콘서트 예약 가능 좌석을 조회한다.
4. 콘서트 좌석을 예약한다.
5. 좌석을 결제한다.

 

사용자는 크게 5개의 플로우를 타게된다. 그 중에 1번에 해당하는 토큰은 Redis에서 관리되며, 쿼리를 사용하지 않게 끔 진행하려한다.

2,3,4,5번의 플로우에서는 쿼리가 사용 된다.

 

▶️ 예약 가능 날짜 조회

    select
        cs1_0.concert_schedule_id,
        cs1_0.concert_id,
        cs1_0.concert_at,
        cs1_0.create_at,
        cs1_0.price,
        cs1_0.total_available,
        cs1_0.update_at 
    from
        concert_schedule cs1_0 
    where
        cs1_0.concert_id=?

• 위 Query는 concert_Id를 조건으로 검색한다.
• 콘서트 마다 여러 일자에 공연을 한다면, 콘서트 마다 검색할 때에 full scan을 이용하는 것보다 성능이 향상 될 가능 성이 있다.

▶️ 예약 가능 좌석 조회

	SELECT *
	FROM CONCERT_SEAT s
	WHERE s.CONCERT_SCHEDULE_ID = 1
	AND s.status = 'UNASSIGNED';

• 2개의 where절이 걸린다. 
• status는 배정 되지 않았다는 뜻의 "UNASSIGNED" 값을 사용하고 있다.
• concert_schedule_id로 먼저 순서를 잡고 status를 사용 중이다. 순서대로 복합 index를 걸어야겠다.

> 위 2개의 조회 쿼리만 보더라도 concert_schedule의 concert_Id 컬럼과 concert_seat의 status 값에 index를 걸면 성능이 더 나아질지 흥분되어 엉덩이가 들썩이게 되었다..!!..!

 

🏋️‍♀️ 테스트 배경

Concert 1개
ConcertShedule 콘서트 마다 2개 일정
ConcertSeat 콘서트 스케쥴마다 100만개의 좌석
총 seat는 2백만 개로 만들었다. (이 이상은 너무 오래걸려..)
> 그 중 30만개는 임시배정, 10만개는 배정, 60만개는 공석 상태이다.

 

대상 Query (예약 가능 좌석 조회)

	SELECT *
	FROM CONCERT_SEAT s
	WHERE s.CONCERT_SCHEDULE_ID = 1
	AND s.status = 'UNASSIGNED';

 

-None Index

실행 계획

SELECT
    "S"."ASSIGNMENT_TIME",
    "S"."CONCERT_SCHEDULE_ID",
    "S"."CONCERT_SEAT_ID",
    "S"."SEAT",
    "S"."TEMP_ASSIGNMENT_TIME",
    "S"."USER_ID",
    "S"."STATUS"
FROM "PUBLIC"."CONCERT_SEAT" "S"
    /* PUBLIC.CONCERT_SEAT.tableScan */
    /* scanCount: 2000001 */
WHERE ("S"."CONCERT_SCHEDULE_ID" = CAST(1 AS BIGINT))
    AND ("S"."STATUS" = CAST('UNASSIGNED' AS ENUM('ASSIGNED', 'TEMP', 'UNASSIGNED')))
/*
reads: 97421
*/

 

1회차

2회차

- On Index

실행 계획

CREATE INDEX idx_concer_schedule_id_status ON CONCERT_SEAT(CONCERT_SCHEDULE_ID, STATUS);

SELECT
    "S"."ASSIGNMENT_TIME",
    "S"."CONCERT_SCHEDULE_ID",
    "S"."CONCERT_SEAT_ID",
    "S"."SEAT",
    "S"."TEMP_ASSIGNMENT_TIME",
    "S"."USER_ID",
    "S"."STATUS"
FROM "PUBLIC"."CONCERT_SEAT" "S"
    /* PUBLIC.IDX_CONCER_SCHEDULE_ID_STATUS: CONCERT_SCHEDULE_ID = CAST(1 AS BIGINT)
        AND STATUS = CAST('UNASSIGNED' AS ENUM('ASSIGNED', 'TEMP', 'UNASSIGNED'))
     */
    /* scanCount: 600001 */
WHERE ("S"."CONCERT_SCHEDULE_ID" = CAST(1 AS BIGINT))
    AND ("S"."STATUS" = CAST('UNASSIGNED' AS ENUM('ASSIGNED', 'TEMP', 'UNASSIGNED')))
/*
reads: 55811
*/

 

확실히 속도가 빨라진 모습이다..! (엉덩이 들썪)🤦‍♂️🤦🤦‍♀️~~

 

? 🤷 궁금했던 부분

- 처음에 60만개만 공석으로 둔 이유는, index를 설정하면 조건에 의해 allScane이 아닌 where절에 맞게 조건을 가져온다는 점이다.

 

만약 갯수에서 상대적으로 적은 status를 호출하면 어떻게 될까?

- TEMP (30만개)

 

- ASSIGNED(10만개)

 

확실히 status의 양이 적을 수록 속도가 빨라졌다..!

 

 

? 🤷마지막으로 드는 2가지 의문점 

1) 처음 콘서트가 열려서 모두 공석이라면, allScan에 가까울 것이다. 시간이 지나서 사용자가 좌석을 예약할 수록 빨라질 것인데 어떻게 이 부분을 해결 할 수 있을까?

> 모르겠다. 질문해야지..

 

2) status는 사용자의 예약에 따라 바뀔 것이며, 그만큼 index 재정렬로 db의 부하를 줄 것이다.

> 사용자가 좌석을 선택할 때 새로고침, 선택 좌석 선점 실패 등의 이유로 여러번 조회할 가능성이 높다. 그러므로 업데이트 비용보다도 인덱싱을 하는 것으로 조금이나마 부하를 낮출 수 있을 것 같다.