쿼리 성능 개선을 위한 DB index 처리

0. DB index, 어떻게 잘 사용할 수 있을까

• 기본적인 데이터의 성격
  • 데이터 중복이 적어 한번에 찾을 수 있는 값
  • 삽입, 수정이 적은 컬럼
  • 조회에 자주 사용되는 컬럼
• 데이터의 생명주기, 데이터 분포도
  • 데이터의 값에 생명주기가 있는 경우, live한 데이터 값들을 유의미하게 만들 수 있음 (e.g. 주문 상태값)
  • 데이터가 어떤 성향으로 분포되어 있는지 확인, 두 가지의 상태값이어도 99:1의 분포도라면 1을 찾기 위해 비용 소모가 클 수 있음 (e.g. 좌석 예매 상태)
• 데이터 중복 사용 관점
  • 인덱스도 하나의 테이블로 저장, 관리되기 때문에 하나의 인덱스로 처리가 가능한 경우는 하나로 처리될 수 있도록
• 복합 인덱스인 경우 우선정렬을 따르기 때문에, 첫 번째 컬럼이 무엇이 되는지도 중요

tip! 테이블을 만들어 여러 정렬을 시켜보자. 눈으로 확인 가능하다.

 

---

1. 현재 나의 콘서트 예약 서비스에서 수행하는 쿼리들

• 전체 조회 쿼리
  • findAll() -> full-scan 이므로 수집 x
• primary key 조건 조회
  • findById() -> MySQL에서 primary key는 자동 인덱스를 걸어주므로 패스
    • MySQL은 pk를 클러스터 인덱스(Clustered Index)로 저장한다.
      클러스터링: pk가 유사한 레코드들끼리 묶어서 저장하는 방식

• 특정 조건 조회

// concert
boolean existByConcertDateAndStatus(Long concertDateId, Seat.Status status);
List<Seat> findSeatsByConcertDateIdAndStatus(Long concertDateId, Seat.Status status);
Seat findSeatByConcertDateIdAndSeatNum(Long concertDateId, int seatNum);

// payment
Payment findByReservationId(Long reservationId);

// reservation
Reservation findOneByConcertDateIdAndSeatNum(Long concertDateId, int seatNum);
Reservation findByIdAndUserId(Long reservationId, Long userId);
@Query("SELECT new io.hhplus.server.domain.reservation.service.dto.GetReservationAndPaymentResDto(r, c, cd, s)" +
        "FROM Reservation r " +
        "JOIN Concert c on c.concertId = r.concertId " +
        "JOIN ConcertDate cd on cd.concertDateId = r.concertDateId " +
        "JOIN Seat s on s.concertDate.concertDateId = cd.concertDateId and s.seatNum = r.seatNum " +
        "WHERE r.userId = :userId")
List<GetReservationAndPaymentResDto> getMyReservations(Long userId);

 

---

2. 조회 시 인덱스로 성능을 향상시킬 수 있는 쿼리인지 판단

0번의 사용 기준에 근거하여

• Concert domain
  
  • existByConcertDateAndStatus(Long concertDateId, Seat.Status status)
    findSeatsByConcertDateIdAndStatus(Long concertDateId, Seat.Status status)
    -> concertDateId 컬럼을 먼저 식별하여 유의미한 속도 향상을 할 수 있다. 또한 status 컬럼은 좌석 예약 상태에 따른 조회가 많으며, 예약 가능하거나 예약된 좌석의 상태를 조회하는 데에 있어 데이터 분포도가 극단적으로 나뉘는 상황도 많다. 조회가 많은 쿼리 두 개 모두 복합 인덱스 (concertDateId, status) 를 걸면 좋을 것 같다.
    (status에 수정이 일어나지만, 수정보다 읽기가 훨씬 많이 일어나므로 괜찮을 것 같다.)
  • findSeatByConcertDateIdAndSeatNum(Long concertDateId, int seatNum)
    -> seatNum 컬럼의 중복이 현저히 적고 데이터 양이 많아 seatNum에 인덱스를 걸면 성능 향상의 기대가 있음
        유니크 인덱스 (concertDateId, seatNum)
• Payment domain
  • findByReservationId(Long reservationId)
    -> 중복이 적은 거의 고유한(고유하지 않을 수 있음) 값이므로 단일 인덱스 (reservationId)를 고려했지만,
    Payment 테이블에서 reservationId를 외래키로 참조하고 있어 자동으로 인덱스 테이블이 생성되어 있어서 따로 추가하지 X
• Reservation domain
  • findOneByConcertDateIdAndSeatNum(Long concertDateId, int seatNum)
    -> Seat 테이블과 같은 이유로 유니크 인덱스 (concertDateId, seatNum)
    
    
  • findByIdAndUserId(Long reservationId, Long userId)
    -> reservationId가 pk라 이미 인덱스가 걸려 있으므로 추가적인 인덱스는 고려할 필요가 없다.
    
    
  • getMyReservations(Long userId)
    -> JOIN의 연결고리가 되는 컬럼에 인덱스를 걸면 좋다. 현재 JOIN절은 concertId, concertDateId는 PK로 자동으로 인덱스가 걸려있으며, Seat 테이블의 (concertDatdId, seatNum) 역시 유니크 인덱스를 이미 걸기로 하였다.
    WHERE절의 userId 컬럼은 중복이 적은 데이터이므로 인덱스를 걸어보자. 단일 인덱스 (userId)

---

3. 인덱스 유무에 따른 성능 비교

조회 성능 비교를 위해 아래의 데이터를 삽입해놓은 상태에서 진행하였다.

concert - 1 row
concertDate - 3 row
seat - 3000 row (각 concertDate당 1000 row씩) / 좌석 상태는 AVAILABLE : DISABLE = 97 : 3
reservation - 100 rowuser - 100 row

---

• Concert domain

explain analyze SELECT EXISTS (
    SELECT 1
    FROM seat
    WHERE concert_date_id = 2 AND status = 'AVAILABLE'
) AS 'exists';

 

인덱스 사용 전 (concertDateId가 외래 키로 들어가 있음)

복합 인덱스 (concertDateId, status)  추가 후

 

explain analyze SELECT *
FROM seat
WHERE concert_date_id = 2 AND status = 'AVAILABLE';

인덱스 사용 전  (concertDateId가 외래 키로 들어가 있음)

복합 인덱스 (concertDateId, status)  추가 후

 

위 두 결과를 보면 인덱스를 사용한 후 성능이 조금 더 좋아진 것을 볼 수 있다.

하지만 위의 결과는 찾으려는 Seat.status값이 거의 대부분의 데이터의 status값일 경우이다. 찾으려는 status값이 극히 일부의 값일 때, 성능이 더 좋아지지 않을까 하여 비교해보았다.

explain analyze SELECT *
FROM seat
WHERE concert_date_id = 2 AND status = 'DISABLE';

인덱스 사용 전  (concertDateId가 외래 키로 들어가 있음)

복합 인덱스 (concertDateId, status)  추가 후

 

이렇게 예상대로 찾으려는 데이터의 분포도에 따라 성능이 크게 좋아지는 결과가 나왔다.

 

다음 쿼리는 유니크 인덱스를 추가 후 비교해보았고, 유니크 인덱스로 성능이 드라마틱하게 향상되는 결과를 볼 수 있다.

SELECT * 
FROM seat 
WHERE concert_date_id = 1 AND seat_num = 568;

 

인덱스 사용 전

유니크 인덱스 (concertDateId, seatNum) 추가 후

 

---

• Reservation domain

explain analyze SELECT *
FROM reservation
WHERE concert_date_id = 1 AND seat_num = 586
LIMIT 1;

인덱스 사용 전

유니크 인덱스 (concertDateId, seatNum) 사용 후

 

explain analyze SELECT *
FROM Reservation r
JOIN Concert c on c.concert_id = r.concert_id
JOIN Concert_date cd on cd.concert_date_id = r.concert_date_id
JOIN Seat s on s.concert_date_id = cd.concert_date_id and s.seat_num = r.seat_num
WHERE r.user_id = 91;

인덱스 사용 전

Seat 테이블 유니크 인덱스 (concertDateId, seatNum) 사용 후

 

두 쿼리 모두 인덱스 사용 시 조회 성능이 많이 향상됨을 알 수 있다.

 

---

4. 정리

인덱스를 효율적으로 사용했을 경우, 쿼리의 읽기 성능이 매우 좋은 방향으로 달라짐을 확인할 수 있었다.

최대한 pk를 많이 활용하고, 중복 사용을 고려하며 적은 인덱스 개수를 유지하며 실무에서 활용해야 함을 느꼈다. 

하지만 수정 및 삽입이 자주 일어나는 데이터는 조심하자. 후에 수정 및 삽입 시에 인덱스 사용이 미치는 영향에 대해서도 깊이 알아보면 좋을 것 같다.