항해99) 인덱스 관련 이야기

항해 99 과정서 콘서트 좌석 관련 테이블 형태는 다음과 같다.

제약조건: 

좌석수: 1~50
콘서트수: 1~200
좌석상태: EMPTY, RESERVED

seat_number: 좌석 정보
show_id: 콘서트 아이디
occupied_status: EMPTY, RESERVED 

이다.

1번 콘서트에서 잔여좌석 확인때 다음과 같이 수행한다.

select * from seat_info where occupied_status = "EMPTY" and show_id = 1;

Explain view로 보면 Full Table Scan을 수행한다.

 

 

여기서 풀테이블 스캔을 피해가려면 Index를 걸 수 있다.

Index는 Cardinality가 큰 녀석을 걸면된다. 직감적으로 show_id에 걸면 될 것이다.(1~50)이므로.
Index를 걸 때도 복합 인덱스 선택방향이 있는데, 다음과 같이 선택방향이 있을 것이다.

> (show_id)
> (show_id, occupied_status)
> (show_id, seat_number, occupied_status)

다만, occupied_status는 EMPTY, RESERVED 두 가지 상태만 존재하므로 카디날리티가 2밖에 되지 않는다. 이런경우 다음과 같은 단점이 있다.

1. 카디널리티가 낮으므로, 인덱스 탐색 범위가 넓어지고 최악의 경우 FULL TABLE SCAN보다 성능이 낮을 수 있다.
2. 좌석의 상태는 자주 변경된다. 인덱스는 삽입과 제거의 성능을 저하하지만 조회의 속도를 빠르게 하는 것이다. 상태가 EMPTY, RESERVED의 변경이 잦다면 인덱스를 선택하는데 더 좋지 않은 선택사항이 될 수 있다.

 

그래서 나의 최종적인 결정은 show_id 에만 걸게 하였다.

스프링에서 인덱스를 걸기 위해 다음과 같이 수행하였다.

package reservation.Domain;

import jakarta.persistence.*;
import lombok.Getter;
import lombok.Setter;

@Entity
@Getter
@Setter
@Table(name="seat_info", indexes = {
    @Index(name = "IDX_show_id", columnList = "show_id")
})
public class Seat {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name="seat_number")
    private int seatNumber;

    @Column(name="occupied_status")
    private String occupiedStatus;

    @Column
    @Version
    private int version;

    @ManyToOne
    @JoinColumn(name="show_id")
    private Show concertShow;

    public reservation.DTO.Seat toSeatDTO(){
        return  reservation.DTO.Seat.builder()
                .id(this.id)
                .seatNumber(this.seatNumber)
                .occupiedStatus(this.occupiedStatus)
                .build();
    }
}

 

실행결과는 다음과같다.

query cost가 875.8 에서 17.5로 무려 50배, 98% 감소하게되었다.

 

다음은 내가 삽입 쿼리를 만드는 파이썬 테스트 코드이다.

from random import choice

for i in range(1, 200):
    print(f"insert into concert_show (id, price, concert_id) values({i}, 1000, 1);")

print("")
for i in range(1, 200):
    for j in range(1, 50):
        val = choice(["EMPTY", "RESERVED"])
        print(
        "insert into seat_info (id, seat_number, occupied_status, version, show_id)" +
        f"values ({i*50+j}, {j}, \"{val}\", 0, {i});"
        )

문서끝