개요
서비스에서 데이터가 너무 많아 조회 성능이 느려진다면, 이를 해결하는 방법은 여러가지 있을 수 있지만 가장 먼저 적용해 볼 수 있는 것은 RDB에 인덱스를 추가하여 조회 성능을 끌어올리는 것이다. 우리가 구축한 콘서트 예약 서비스에서 위와 같은 상황을 가정하여, 인덱스를 직접 설정해보고 더미 데이터를 통해 인덱스 설정 전과 후를 비교해 성능이 얼마나 개선되었는지 비교해보자.
이를 통해 인덱스에 대해 학습해보고, 인덱스의 적절한 사용 방법과 성능 개선이 얼마나 이루어지는지를 확인해보면서 장기적으로 실무에서도 인덱스를 적재적소에 활용할 수 있도록 역량을 기를 수 있을 것이다.
인덱스란?
인덱스를 적용하기에 앞서 인덱스의 개념에 대해 학습해보자.
인덱스 학습 내용 정리 : 인덱스인덱스
쿼리별 인덱스 현황 및 실행 계획
서비스에서 발생하는 쿼리에 대해 분석해보고 적절한 인덱스를 선택 후 성능을 비교해보자.
User 도메인
•
더미 데이터 : user - 100,000개 / point_history - 500,000개
•
기적용된 인덱스
◦
user
▪
user_id : PK 클러스터링 인덱스
▪
unique_user_uuid : uuid 유니크 제약조건에 의한 인덱스
◦
point_history
▪
point_history_id : PK 클러스터링 인덱스
▪
idx_user_id : point_history의 FK를 대신하는 인덱스
•
fun findById(userId: Long): User?
◦
userId를 통한 유저 조회 및 join을 통한 pointHistory 조회
◦
발생 쿼리
select *
from user u
left join point_history p on u.id = p.user_id
where u.id = ?
SQL
복사
◦
실행 계획(explain analyze) - 6ms ~ 7ms
◦
index eq 조회 이후 nested loop를 통해 index lookup으로 point_history 검색
◦
PK 클러스터링 인덱스와 idx_user_id 인덱스를 활용
•
fun findByUuid(uuid: String): User?
◦
userUUID를 통한 유저 조회 및 join을 통한 pointHistory 조회
◦
발생 쿼리
select *
from user u
left join point_history p on u.id = p.user_id
where u.user_uuid = ?
SQL
복사
◦
실행 계획(explain analyze) - 5ms ~ 9ms
◦
index eq 조회 이후 nested loop를 통해 index lookup으로 point_history 검색
◦
PK 클러스터링 인덱스와 unique_user_uuid 인덱스를 활용
Concert 도메인
•
더미 데이터 : concert 10,000개 / concert_schedule 30,000개 / concert_seat 150,000개
•
기적용된 인덱스
◦
concert
▪
concert_id : PK 클러스터링 인덱스
◦
concert_schedule
▪
concert_schedule_id : PK 클러스터링 인덱스
▪
idx_concert_id : concert의 FK를 대신하는 인덱스
◦
concert_seat
▪
concert_seat_id : PK 클러스터링 인덱스
▪
idx_concert_schedule_id : concert_schedule의 FK를 대신하는 인덱스
•
fun findAllConcert(finished: Boolean, pageable: Pageable): Page<Concert>
◦
finished 상태에 따른 콘서트 목록 조회
◦
발생 쿼리
select *
from concert c
where c.finished = ?
limit ?
offset ?
SQL
복사
◦
실행 계획(explain analyze)(page = 0) - 31ms ~ 32ms
◦
실행 계획(explain analyze)(page = 220) - 41ms
◦
테이블 풀 스캔 이후 finish 값에 대한 필터링 수행
◦
전체 데이터가 1만개로 적어 별로 안느려보이지만, 데이터가 많아질 경우를 대비 + 자주 조회되는 쿼리로 인해 인덱스 적용을 고려할 필요가 있다
•
fun findAllConcertById(concertIds: List<Long>): List<Concert>
◦
여러 concertId를 통한 콘서트 목록 조회
◦
발생 쿼리
select *
from concert c
where c.id in (?, ?, ?)
SQL
복사
◦
실행 계획(explain analyze) - 97ms
◦
Index range scan 후 in절에 맞춰 필터링
◦
PK 클러스팅 인덱스 활용
•
fun findConcert(concertId: Long): Concert?
◦
concertId를 통한 콘서트 조회
◦
발생 쿼리
select *
from concert c
where c.id = ?
SQL
복사
◦
실행 계획(explain analyze) - 0ms
◦
index eq 조회
◦
PK 클러스터링 인덱스 활용
•
fun findSchedule(concertScheduleId: Long): ConcertSchedule?
◦
concertScheduleId를 통해 콘서트 일정 조회
◦
발생 쿼리
select *
from concert_schedule c
where c.id = ?
SQL
복사
◦
실행 계획(explain analyze) - 0ms
◦
index eq 조회
◦
PK 클러스터링 인덱스 활용
•
fun findAllScheduleByConcertId(concertId: Long): List<ConcertSchedule>
◦
특정 콘서트(concertId)의 모든 콘서트 일정 목록 조회
◦
발생 쿼리
select *
from concert_schedule c
where c.concert_id = ?
SQL
복사
◦
실행 계획(explain analyze) - 62ms
◦
index lookup scan 발생
◦
idx_concert_id 인덱스 활용
•
fun findSeat(concertSeatId: Long): ConcertSeat?
◦
concertSeatId를 통한 콘서트 좌석 조회
◦
발생 쿼리
select *
from concert_seat c
where c.id = ?
SQL
복사
◦
실행 계획(explain analyze) - 0ms
◦
index eq 조회
◦
PK 클러스터링 인덱스 활용
•
fun findAllSeatByConcertScheduleId(concertScheduleId: Long): List<ConcertSeat>
◦
특정 콘서트 일정(concertScheduleId)의 모든 콘서트 좌석 목록 조회
◦
발생 쿼리
select *
from concert_seat c
where c.concert_schedule_id = ?
SQL
복사
◦
실행 계획(explain analyze) - 19ms
◦
index lookup scan 발생
◦
idx_concert_schedule_id 인덱스 활용
Reservation 도메인
•
더미 데이터 : reservation 2,000,000개
•
기적용된 인덱스
◦
reservation_id : PK 클러스터링 인덱스
◦
idx_user_id : user의 FK를 대신하는 인덱스
◦
idx_concert_id : concert의 FK를 대신하는 인덱스
◦
idx_concert_schedule_id : concert_schedule의 FK를 대신하는 인덱스
◦
idx_concert_seat_id : concert_seat의 FK를 대신하는 인덱스
•
fun findById(reservationId: Long): Reservation?
◦
reservationId를 통한 예약 조회
◦
발생 쿼리
select *
from reservation r
where r.id = ?
SQL
복사
◦
실행 계획(explain analyze) - 0ms
◦
index eq 조회
◦
PK 클러스터링 인덱스 활용
•
fun findByScheduleIdAndSeatId(concertScheduleId: Long, concertSeatId: Long): Reservation?
◦
concertScheduleId와 concertSeatId를 통한 예약 조회
◦
발생 쿼리
select *
from reservation r
where r.concert_schedule_id = ? and r.concert_seat_id = ?
SQL
복사
◦
실행 계획(explain analyze) - 0ms
◦
index lookup scan 후 index 필터링 발생
◦
idx_concert_schedule_id 및 idx_concert_seat_id 인덱스 활용
•
fun findTopReservationsByCreatedAtBetween(start: LocalDateTime, end: LocalDateTime, limit: Long): List<Reservation>
◦
특정 기간(start ~ end) 사이에 가장 많이 예약된 concertId를 가진 예약을 limit개만큼 조회
◦
발생 쿼리
select r.concert_id, count(r.concert_id) as count
from reservation r
where r.created_at between (? and ?)
and r.expired_at is null
group by r.concert_id
order by count desc
limit ?
SQL
복사
◦
실행 계획(explain analyze) - 대략 143x 초(24분)
1.
인덱스 풀 스캔 : 예상(200만 건) / 실제(200만 건)
2.
검색 조건에 따른 필터링 : 예상(22,119건) / 실제(21,684건)
3.
group aggrate(집계, count)를 수행 : 예상(9,805건) / 실제(9,479건)
4.
내림차순 정렬 및 limit : 20건
◦
특수한 쿼리이지만, 집계 과정이 굉장히 무겁고 느리기 때문에 인덱스 적용 고려 필수
Payment 도메인
•
더미 데이터 : payment ?개
•
기적용된 인덱스
◦
payment_id : PK 클러스터링 인덱스
◦
idx_user_id : user의 FK를 대신하는 인덱스
◦
idx_reservation_id : reservation의 FK를 대신하는 인덱스
◦
unq_user_id_reservation_id : 동일 유저가 동일 예약에 대해 중복 결제를 방지하기 위한, user_id + reservation_id 유니크 조건에 의한 복합인덱스
•
fun findByUserIdAndReservationId(userId: Long, reservationId: Long): Payment?
◦
발생 쿼리
select *
from payment p
where p.user_id = ? and p.reservation_id = ?
SQL
복사
◦
실행 계획(explain analyze) - 0ms
◦
index eq 조회
◦
unq_user_id_reservation_id 인덱스 활용 및 유니크 조건에 의해 eq 검색 수행
•
fun findById(paymentId: Long): Payment?
◦
발생 쿼리
select *
from payment p
where p.id = ?
SQL
복사
◦
실행 계획(explain analyze) - 0ms
◦
index eq 조회
◦
PK 클러스터링 인덱스 활용
적절한 인덱스 선택 및 성능 비교
위에서 분석한 실행 계획을 기반으로, 인덱스 적용을 고려해볼만한 쿼리에 대해 인덱스를 적용해보고, 인덱스 적용 전과 후에 실행 계획과 쿼리 속도를 비교해보자.
finished에 따른 콘서트 조회 쿼리
select *
from concert c
where c.finished = ?
limit ?
offset ?
SQL
복사
위 쿼리는 페이지네이션과 where 조건을 통해 조회를 수행한다.
•
기존 실행 계획
•
현재는 테이블 풀 스캔이 발생하고, 예상 rows를 보면 옵티마이저가 10,000건에 대해 전부 스캔할 것을 예상하고 쿼리를 수행한다.
•
31ms로 쿼리 속도가 느리지 않은 것처럼 보이지만, 이는 20개의 페이지네이션으로 인해 쿼리가 일찍 종료되어 빨리 끝난 것도 있다.
•
또한 데이터가 점점 늘어 10만, 100만건을 넘어가면 점점 성능의 저하가 커질 수 있기 때문에 인덱스를 적용하는 것이 좋을 것 같다.
finished 필드에 인덱스를 적용하고 성능을 비교해보자.
비교를 하기에 앞서, 인덱스 적용 전 조회와 삽입의 쿼리 속도는 다음과 같다.
조회 : page 0 - 31ms, page 220 - 41ms / 삽입 : 43ms
•
finished 단일 인덱스
◦
예상
▪
finished에 따라 true/false를 정렬해 인덱스를 생성하므로, 페이지네이션 적용 시 데이터를 특정해서 찾아갈 수 있기 때문에 성능이 개선될 것으로 예상된다.
◦
실행 계획(page = 0) - 2ms
◦
실행 계획(page = 220) - 50ms
▪
index lookup (idx_finished 조회로 얻은 PK → 클러스터링 인덱스 eq 조회) 수행
요약
전과 후에 대해 성능을 비교하면, page 0에 대해서는 성능이 압도적으로 좋아졌지만 page 220에 대해서는 성능이 오히려 떨어졌다.
그 이유로는 MySQL에서는 인덱스 조회를 수행하는 과정에서 데이터 변경을 막기 위해 락을 사용하는데, 뒤쪽 페이지를 조회하면 인덱스 순차탐색을 통해 각 노드를 탐색하면서 락을 걸었다가 풀었다가를 반복하기 때문인 것으로 추측된다.
위에서는 별다른 추가 조건이나 정렬이 없었지만 만약 인덱스로 성능을 개선시키고자 한다면, 사용되는 페이지네이션 정렬 조건에 대해서 인덱스에 추가해 복합 인덱스 형태로 사용하는 것이 좋을 것 같다.
페이지네이션 정렬 기준(created_at)이 있는 상황에서의 인덱스 간단 비교
select *
from concert c
where c.finished = false
order by created_at
limit 20
offset 4480;
SQL
복사
인덱스 적용 전 : 133ms
복합 인덱스(finished, created_at) 적용 후 : 52ms
인기 콘서트 집계 쿼리
select r.concert_id, count(r.concert_id) as count
from reservation r
where r.created_at between (? and ?)
and r.expired_at is null
group by r.concert_id
order by count desc
limit ?
SQL
복사
해당 쿼리는 where 조건으로 사용된 expired_at이 null 인 조건과 created_at이 start와 end의 사이인 조건으로 조회한다.
위 쿼리를 최적화 하기 위해 인덱스를 적용하기 위해 몇 가지를 고려해보자.
•
기존 실행 계획
•
현재는 인덱스 풀 스캔이 수행되는데, idx_concert_id 인덱스를 통해 데이터 전체에 엑세스 한다는 의미이다. 위 실행계획을 보면 인덱스 풀 스캔 시의 시간이 가장 오래 걸리는 것을 확인할 수 있다.
•
때문에 쿼리 시간을 줄이기 위해서는 전체 모집단을 줄여야한다. 검색 조건에 사용하되는 필드인 created_at와 expired_at에 대한 인덱스를 적용해, 모집단을 줄이는 것이 인덱스 적용의 핵심일 것이다.
•
일단 created_at을 통해 전날 하루에 대한 데이터 수를 파악해보면, 아래와 같이 대략 33,172 건이 조회된다.
•
expired_at이 null인 데이터(예약에 성공한 데이터)를 조회하면, 아래와 같이 1,799,905 건이 조회된다.
•
위 내용을 토대로 인덱스를 적용했을 때, 모집단은 다음과 같이 결정될 것이다.
1.
expired_at 단일 인덱스 : 인덱스 범위 스캔 180만건
2.
created_at 단일 인덱스 : 인덱스 범위 스캔 3만건
3.
(created_at, expired_at) 복합 인덱스 : 인덱스 범위 스캔 3만건 → expired_at 필터링 검색 수행
4.
(expired_at, created_at) 복합 인덱스 : 인덱스 범위 스캔 180만건 → created_at 범위 검색 수행 3만건
그렇다면 이러한 상황에서 인덱스의 성능에 대해 예측해보고, 실행 계획을 통해 어떻게 수행되는지 확인해보자.
비교를 하기에 앞서, 인덱스가 적용되기 전의 성능은 다음과 같다.
조회 : 143x초(24분) / 삽입 : 57ms
•
expired_at 단일 인덱스
◦
모집단이 180만건으로 별로 줄어들 것으로 예상되지 않고, 충분히 많은 수를 탐색해야한다.
◦
때문에 성능과 효율이 기존과 크게 다를 바 없을 것으로 예상된다.
◦
성능과 효율 개선 기대 효과가 없으므로 테스트 하지 않는다.
•
created_at 단일 인덱스
◦
모집단이 3만건으로 줄어들 것으로 예상된다.
◦
성능과 효율이 크게 개선되었을 것으로 예상된다.
◦
실행 계획 - 17초
▪
인덱스 범위 스캔 : 예상(64,188건) / 실제(33,173건)
▪
expired_at 필터링 검색 : 예상(6,418건) / 실제(29,822건)
▪
임시 테이블을 통한 집계(count) 수행 : 9,479건
▪
내림차순 및 limit : 20건
◦
데이터 삽입 쿼리 수행 시간 : 69ms
•
(created_at, expired_at) 복합 인덱스
◦
복합 인덱스의 첫 조건으로 3만건으로 스캔 대상이 줄고, 해당 범위를 스캔하며 필터링 조건을 수행할 것으로 예상된다.
◦
이 역시 모집단이 3만건으로 줄어들었기 때문에, 성능과 효율이 크게 개선 되었을 것으로 예상된다.
◦
실행 계획 -20초
잘린 문장
▪
인덱스 범위 스캔 : 예상(63,692건) / 실제(29,822건)
▪
임시 테이블을 통한 집계(count) 수행 : 9,479건
▪
내림차순 및 limit : 20건
◦
데이터 삽입 쿼리 시간 : 70ms
•
(expired_at, created_at) 복합 인덱스
◦
복합 인덱스의 첫 조건으로 180만건으로 스캔 대상이 줄어들고, 두 번째 조건으로 3만건으로 줄어들 것으로 예상된다.
◦
이 역시 두 번의 인덱스 조건을 통해 모집단이 3만건으로 줄어들었기 때문에, 성능과 효율이 크게 개선 되었을 것으로 예상된다.
◦
실행 계획 - 20초
잘린 문장
▪
인덱스 범위 스캔 : 예상(60,236건) / 실제(29,822건)
▪
임시 테이블을 통한 집계(count) 수행 : 9,479건
▪
내림차순 및 limit : 20건
◦
데이터 삽입 쿼리 시간 : 60ms
요약 및 비교
•
조회 성능
1.
created_at 단일 인덱스 : 16.978초
2.
(expired_at, created_at) 복합 인덱스 : 20.186초
3.
(created_at, expired_at) 복합 인덱스 : 20.260초
4.
no 인덱스 : 24분 이상
•
삽입 성능(순서는 매겼지만 큰 영향이 없을만큼의 근소한 차이)
1.
no 인덱스 : 57ms
2.
(expired_at, created_at) 복합 인덱스 : 60ms
3.
created_at 단일 인덱스 : 69ms
4.
(created_at, expired_at) 복합 인덱스 : 70ms
위의 성능을 비교해보면, 의외로 created_at의 성능이 가장 우수하다. 이처럼 복합 인덱스보다 성능이 좋은 이유는 비즈니스의 특성상 created_at 단일 인덱스 적용 시와 복합 인덱스 적용 시 수행하는 범위 스캔의 레코드 수가 동일하기 때문인 것으로 추측된다.
복합 인덱스의 디스크 사용량과 삽입 시의 쿼리 속도를 생각하면, 복합 인덱스를 적용하는 것보다 created_at 단일 인덱스를 적용하는게 합리적이라 생각된다.
추가적으로 데이터가 200만건이라는 상황이긴하지만 인덱스를 적용 이후에도 16초 ~ 20초의 조회 성능을 보여주기 때문에, 사용자에게 응답을 내보내는 로직에서 조회하기에는 조금 무리가 있어보인다. 때문에 이처럼 인덱스를 통해 개선해도 해결할 수 없을만큼 데이터가 많아진다면, 아래와 같은 다른 방법을 통해 인기 콘서트 정보를 제공하는 것이 좋아보인다.
•
캐시 저장하기 : 데이터 조회에 오래 걸리는만큼, 스케줄러를 통해 캐싱해두고 조회 시 캐시된 값을 반환(현재 적용되어 있는 방식)
•
Materialize View 활용 : Redis나 RDB의 별도의 테이블에 예약 발생 시마다 비정규화된 콘서트별 일간 예약 count를 저장하기
•
일일 데이터 저장 : 사실 위와 같은 일간 인기 예약 콘서트 Top 20 비즈니스의 경우, 과거의 일이기 때문에 데이터가 변할 가능성이 거의 없다. 때문에 매일 스케줄러를 통해 쿼리를 수행 후, 그 결과를 DB에 저장해두는 방법도 있다. 이러한 데이터는 매일매일의 인기 콘서트 예약에 대한 히스토리 측면에서의 의미도 있어 괜찮은 방법이라 생각된다.
결론
사실 복합 인덱스 학습을 위해 인기 예약 콘서트 TOP 20에서 결제까지 성공한(expired_at is null) 인기 예약 콘서트 방식으로 조회를 변경했다. 하지만 덕분에 다양한 상황으로 인덱스의 성능을 비교해볼 수 있었고, 인덱스에 대해 한층 더 깊게 이해할 수 있었던 것 같다.
배운점
1.
특정 상황에서는 인덱스 스캔보다 테이블 풀 스캔이 더 빠를 수 있다.
2.
조건이 여러 개라 해서 항상 복합 인덱스가 더 빠르지는 않다.
3.
인덱스 성능에는 nested loop나 aggregate 등의 내부 반복문을 수행하는 데이터의 범위(개수)가 크게 영향을 미친다.
4.
데이터가 너무 많으면 인덱스만으로 해결하지 못할 수 있다.
5.
인덱스는 삽입 쿼리 성능을 아주 약간 떨어뜨리지만, 조회 성능을 아주아주 효율적으로 개선시켜준다.