[Index] 구축한 서버 Query분석 및 성능 측정 후 Index 구축

이전에 서버의 조회 성능을 향상하기 위해 Application에서 Caching 기술을 적용해 성능을 개선시켜 보았는데요, Caching은 DB에서 데이터를 처음 조회할 때 메모리에 임시 저장하여 이후 조회 시 빠르게 데이터를 제공하는 방식으로, DB 연결 횟수를 줄여 성능을 향상하는 데 큰 도움을 주었습니다.

 

그러나 Caching은 데이터가 자주 갱신되거나, 실시간성이 중요한 경우 Caching이 오히려 성능 저하를 초래할 수도 있습니다. 이럴 때 DB 자체의 조회 성능을 직접적으로 향상하는 방법을 고려할 필요가 있습니다.

 

이번 글에서는 DB에서 조회 성능을 향상하는 대표적인 방법 중 하나인 Indexing에 대해 알아보고, 이를 현재 제작 중인 콘서트 서비스에 어떻게 적용할 수 있을지 분석 및 적용하여 성능 측정을 진행해 보도록 하겠습니다.

Index란?

데이터 베이스에서 데이터를 특정 컬럼들로 조회를 할 경우 빠르게 조회를 하기 위해 B-Tree구조로 컬럼들을 정의해 두고, 이를 통해 정렬되어 있는 클러스터들을 통해 해당 데이터를 빠르게  찾을 수 있도록 해주는 기능입니다.

 

MySQL에서는 기본 키(Primary Key, PK)를 정의하면, 해당 키에 대해 자동으로 클러스터드 인덱스(Clustered Index)가 생성됩니다. 클러스터드 인덱스는 데이터의 물리적 순서와 인덱스의 순서가 일치하도록 보장하여, 기본 키를 사용한 조회 성능을 최적화합니다.

 

인덱스의 종류
인덱스는 크게 단일 인덱스(Single Index)와 복합 인덱스(Composite Index)로 나눌 수 있습니다. 복합 인덱스는 여러 컬럼을 조합하여 생성한 인덱스로, 커버링 인덱스(Covering Index)도 그 중 하나입니다. 커버링 인덱스는 인덱스에 포함된 모든 컬럼을 통해 필요한 데이터를 직접 조회할 수 있어, 실제 데이터를 추가로 조회하지 않아도 된다는 장점이 있습니다.

 

인덱스의 단점
인덱스는 조회 성능을 크게 향상시킬 수 있지만, 단점도 있습니다. 인덱스가 설정된 컬럼의 데이터가 추가, 변경, 삭제될 때마다 인덱스가 재정의(재정렬)되는데, 이 과정은 데이터베이스의 성능 저하를 일으킬 수 있습니다.

 

따라서 무조건 Index를 거는 것보다는 Query를 분석하여 적절한 상황에 적용하는 것을 권장드립니다.

Concert에서 발생하는 Query분석

각각의 도메인에서 발생하는 Query들을 알아보고 Index를 적용하기 적절한지 또한 굳이 생성하지 않아도 괜찮은지에 대해 알아보도록 하겠습니다.

1. Concert

콘서트는 메인 도메인인 Concert와 2개의 서브도메인인 ConcertSeries와 ConcertSeat로 이루어져 있습니다.

이에 서비스에서 각각 발생하는 쿼리들을 분석해 보도록 하겠습니다.

 

Concert
콘서트 조회는 현재 전체 리스트 조회와 PK로 조회하는 Query만을 사용하고 있어 추가적인 Index생성은 불필요하다 판단하여 Index작업을 수행하지 않았습니다.

@Repository
@RequiredArgsConstructor
public class ConcertRepositoryImpl implements ConcertRepository {
    //
    private final ConcertJpaRepository concertJpaRepository;

    @Override
    public void save(Concert concert){
        //
        this.concertJpaRepository.save(new ConcertJpo(concert));
    }

    @Override
    public Concert findById(String concertId) {
        Optional<ConcertJpo> jpo = this.concertJpaRepository.findById(concertId);
        return jpo.map(ConcertJpo::toDomain).orElse(null);
    }

    @Override
    public List<Concert> findAll() {
        //
        List<ConcertJpo> jpos =  this.concertJpaRepository.findAll();
        return jpos.stream().map(ConcertJpo::toDomain).toList();
    }
}

 

ConcertSeries

콘서트 시리즈의 경우 아래와 같이 예매 가능한 콘서트 시리즈를 조회하는 Query를 날리는 상황이 존재합니다.

select csj1_0.series_id,csj1_0.concert_id,csj1_0.create_at,csj1_0.end_at,csj1_0.reserve_end_at,csj1_0.reserve_start_at,csj1_0.start_at from concert_series csj1_0 where csj1_0.concert_id=? and csj1_0.reserve_start_at<=? and csj1_0.reserve_end_at>=?

이전 Index를 걸기 전 10만 건 중 콘서트 아이디와 현재 예약가능한 콘서트를 조회한 결과입니다. 총 65ms의 속도를 자랑하는 쿼리 성능입니다.

 

Index를 적용한 Query성능입니다. 솔직히 65ms의 속도가 나와 성능이 향상될까 싶었지만 11ms의 성능이 향상되었습니다.

 

왜 그럴까?

우선 Index를 생성할 때 사용한 코드입니다.

CREATE INDEX concert_series_active_index ON concert_series(reserve_start_at,concert_id, reserve_end_at);

추후 조회 할 때 sort를 reserve_start_at으로 걸 거 같아 reserve_start_at, concert_id, reserve_end_at순으로 걸었습니다.

 

성능은 향상되었지만 현재 가져오는 속도가 너무 빨라 왜 그런지 확인을 해보았습니다.

ConcertSeries에 걸려있는 Index들입니다. 현재 저는 concert_series_active_index를 사용을 하였는데요.

reserve_start_at의 Cardinality를 보시면 1인 것을 확인하실 수 있습니다.

 

아뿔싸 제가 데이터를 적재할 때 reserve_start_at와 reserve_end_at의 값을 전부 동일하게 준 것을 기억하였습니다.

실제 오픈한 서비스에 해당 데이터들이 있다면 성능 이슈 없는 좋은 상황이겠지만, 테스트를 해야 하는 저에게는 좋지 않은 상황이었습니다.

 

다만 Index에 대한 성능 향상을 확인하였고, 동일한 데이터로 인한 성능 향상은 결국 데이터 퀄리티에 의해 영향을 받는다라는 점을 알 수 있어 좋았습니다.

 

쿼리 실행계획을 확인해 보면 Index에 걸린 row수가 꽤 된다는 것을 확인해 볼 수 있습니다.

 

ConcertSeat

가장 성능이슈가 생길 것으로 예상한 데이터입니다.

총 1000만 건으로 테스트를 진행해 보도록 하겠습니다.

3초 정도 걸리는 쿼리인 것을 확인할 수 있습니다.

쿼리 실행 계획의 경우

위와 같이 아무런 Index를 타고 있지 않아 풀스캔한 것을 확인하실 수 있습니다.

 

3초나 걸려 좌석 시트를 조회하게 된다면 그 사이 좌석이 다 예약이 될 수도 있겠죠 그래서 성능 향상은 필수인 거 같습니다.

series_id로 Index를 생성하게 될 경우 동일한 쿼리의 속도가 36ms로 주는 것을 확인해 볼 수 있습니다.

concertSeat의 경우 seat점유 상태의 값이 자주 바뀌니 해당 상태로 Index를 거는 것이 아니라면 좋은 성능을 낼 수 있다 생각합니다.

---

2. Payment

payment는 결제내역이므로 단건 조회의 경우 PK를 사용하여 기본 Index를 사용합니다.

목록의 경우 userId로 조회를 하기 때문에 Index가 필요한 상황입니다. 

 

아래는 100만 건으로 테스트하였습니다.

인덱스를 건 후 69ms -> 19ms로 조회 시간이 단축되는 것을 확인할 수 있습니다.

 

위 Index를 통해 충분히 성능 향상을 했다 생각합니다.

---

3. Point

point는 pk를  userId로 가져가고 조회 또한 userId로 조회하기 때문에 기본적으로 제공해 주는 Index를 타게 됩니다.

 

문제는 pointHistory인데, 이는  userId로 조회하기 때문에 따로 Index를 추가해주어야 합니다.

 

인덱스가 걸려있지 않은 상태로 조회하였을 때의 성능입니다. 1건의 데이터를 가져오는데 359ms의 시간이 소요되는 것을 확인할 수 있습니다.

 

userId에 Index를 걸게 될 경우 359ms -> 20ms로 확연하게 주는 것을 확인하실 수 있습니다.

쿼리 실행 계획을 확인해 보면 extra에 성능에 이슈가 될만한 use filesort 같은 것들이 명시되어있지 않고 rows가 1개이므로 성능향상에 도움이 많이 된 것 같습니다.

4. TemporaryReservation - Index생성에는 부적합

TemporaryReservation은 임시 예약 테이블로써 주기적으로 삭제가 이뤄지고 잦은 수정이 이뤄지는 곳입니다.

그로써 주기적인 수정은 Index 재정의를 유발할 수 있으므로 적합하지 않다 판단하였습니다.

---

5. Reservation

Reservation은 예약 테이블입니다. 예약이 완료된 건에 대하여 생성이 되며, 한번 생성되면 환불하지 않는 한 삭제는 되지 않습니다.

따라서 Index를 걸기 좋은 상황으로 판단하여 Index를 적용하였습니다.

 

Index를 userId로 걸어 생성할 경우 466ms -> 37ms로 성능이 향상되었습니다.

쿼리 플랜을 보았을 때 100건에 대한 데이터들이 존재하며 좋지 않은 상황들은 쿼리 플랜에 명시되어있지 않은 것으로 보아 유효한 Indexing이었던 것 같습니다.

 

이상으로 Concert서비스에서 사용하는 여러개의 Query들에 대해 분석하고 Indexing을 통해 성능 향상을 해보았습니다.

Indexing은 데이터베이스에 탐색을 위한 자료형을 사용하는 만큼 데이터베이스 성능에 영향을 끼친다고 볼 수 있습니다.

 

따라서  Query의 분석을 통해 정말 걸어야 하는 Query인지, Indexing 하는 컬럼들의 값들이 자주 변경되지 않는지 등을 염두하여 적재적소에 잘 활용하는 것이 중요하다 생각합니다.