리뷰 기능 평점 통계 개선기

진행중인 프로젝트에는 특정 아이템에 대한 리뷰와 평점을 남기는 기능이 있습니다.
평점의 평균을 구하기 위해서 API 요청마다 DB의 Sum, Count와 같은 집계함수를 이용하여 처리하였습니다.
그러다 우연히 다른 기능의 테스트를 하다가 리뷰 테이블에 1000만건 정도의 더미 데이터를 넣게되었는데요.
리뷰 조회 API에서 성능적으로 부하가 발생함을 발견하였고 개선하기 위해 시도했던 것들을 포스팅하게 되었습니다.

 

리뷰 시스템의 테이블 구조는 위 사진과 같습니다.

select count(qr.id), sum(qr.rate), qr.question_id 
from question_review qr 
where qr.question_id = 1

 

특정 상품 리뷰의 평균 평점을 조회 할 때는 위와 같이 count, sum 함수를 사용하여 계산하여 응답으로 주었습니다.

 

explain select count(qr.id) , sum(qr.rate), qr.question_id 
from question_review qr 
where qr.question_id = 1 // question1의 평균 평점

explain을 사용하여 탐색되는 예상 rows를 확인해보면 단건 조회 시에는 question_id를 where문으로 한번 필터하면서 들어가기에 1000만건의 데이터중 240만건의 데이터만 접근하여 처리함을 볼 수 있습니다.

 

 

현재 question1의 리뷰는 1000만건중에 100만건정도이며 위 sql이 수행되는데 약 1.6초 정도 걸렸습니다.
근데 사실 쿠팡과 같은 대규모 이커머스 서비스가 아니라면 한 상품에 리뷰가 100만건이 있을 확률은 없을 것이긴 할 것입니다.
(심지어 쿠팡에서 그나마 제일 높게 본게 10만건 리뷰)

 

 

그런데 문제는 현재 서비스에서 question 상품의 목록을 조회 하는 페이징 API가 있습니다.

정렬 옵션 중에는 평점순으로 정렬되어 페이징되어야 하는데요.

select sum(qr.rate) / count(qr.id) as average_rate, qr.question_id 
from question_review qr 
group by qr.question_id 
order by average_rate DESC
limit 10
offset 1

 

따라서 평점순 페이징 요구사항을 만족시키는 sql은 위와 같을 것입니다.

위 sql은 order by를 통해 average_rate 순으로 정렬해주고 있습니다.

그럼 당연히 db 입장에서는 평균 평점 순으로 정렬을 해야하기에 question_review에 있는 모든 question_id를 그룹화해서 question_id 별로 sum, count 연산을 하여 모든 question의 평균 평점을 계산을 해야 될 것입니다.

만약 현재 question_review에 question1부터 question100까지의 리뷰들이 있다고 가정해보겠습니다.

만약 평균 평점 상위 10등 ~ 19등의 상품을 조회하려면 question1부터 question100까지 평균 평점을 전부 구해야합니다.
따라서 결국 모든 상품의 평균 평점을 구해야하게 됩니다.

 

sql 실행결과 2.9초정도 소요되었습니다.

 

그리고 explain을 해보면 offset,limit와 관계없이 페이징 sql를 수행하는데 탐색되는 rows가 거의 모든 행임을 알 수 있습니다.

따라서 상품을 평점순으로 정렬을 하는 상황에서는 DB 부하가 심할 것입니다.

첫번째 시도 방안 - 커버링 Index

현재 리뷰 테이블에 걸고 있는 index는 위와 같습니다. 

 

여기서 question_id와 sum에 사용되는 rate를 복합 index로 걸어주면 성능이 향상됨을 알 수 있습니다.

select count(qr.id), sum(qr.rate), qr.question_id 
from question_review qr 
where qr.question_id = 1

 

기존에 위 sql은 상품번호(question_id)가 index로 걸려있음에도 1.5초 이상이 소요되었는데요.
상품 번호가 인덱스로 걸려있을지라도 sum 연산을 위한 평점(rate) 값에 접근하기 위해서 해당 row에 접근하여 값을 가져오는

I/O 작업이필요합니다. 따라서 question_id와 rate를 같이 복합 index로 설정한다면 특정 상품에 해당하는 rate 값은 index에 존재하기 때문에 I/O 작업이 줄어들어 처리시간이 빨라질 수 있습니다.

 

커버링 인덱스 사용 후 단건 조회의 경우 1.6초 소요되던 것이 0.166초로 줄어들었습니다.

 

평점 순 정렬 sql의 경우도 2.9초에서 1.4초정도로 줄어들었습니다.

시간은 줄었지만 여전히 모든 상품의 평균 평점을 구해야 하기 때문에 1.4초라는 긴 시간이 여전히 소요되고 있습니다.

따라서 새로운 방안을 찾아봐야겠습니다.

두번째 시도 방안 - 특정 시간마다 Batch 작업 수행

이 방법은 실제로 진행하진 않았고 고민만 해보았던 방안입니다.

select count(qr.id) , sum(qr.rate), qr.question_id 
from question_review qr 
where qr.question_id = 1

 

1분 혹은 3분 간격으로 특정 시간이 지날 때 마다 batch를 통해 통계 sql을 이용하여 평균 평점의 값을 구하고 업데이트 하는 방식입니다.

 

위 방식의 장점은 API 요청 횟수와 상관없이 정해진 시간에만 배치 작업이 수행되기 때문에 부하 유발이 적다는 것입니다.

하지만 단점으로는 배치 작업 전까지 갱신이 되지 않기 때문에 실시간으로 평점을 제공할 수 없습니다.

 

특정 시간마다 DB에 부하가 오는 작업이란 것은 변함이 없었고 실시간으로 변화하는 평점 데이터를 제공할 수 없다는 것이 아쉬웠습니다.

세번째 시도 방안 - 통계 관련 테이블 추가

통계 테이블을 별도로 생성하는 것이 이 프로젝트에 도입한 방식입니다.

근본적인 부하의 원인은 sum과 count와 같은 집계 함수들이 DB에 부하를 일으킨다는 것이였고

그렇다면 sum과 count를 이용하지 않고 처리하면 되지 않을까해서 생각한 방식입니다.

 

위와 같은 구조로 리뷰 통계 테이블을 만들었습니다.

question_id는 상품번호, review_count는 상품의 리뷰 개수, total_rate는 평점의 총합, average_rate는 평균 평점입니다.

 

새로운 리뷰가 작성 / 수정 될 때 마다 누적되어 있는 totalRate, reviewCount, averageRate를 갱신하는 방식입니다.

 

예를 들어 question1에 대한 새로운 리뷰가 작성된다면
기존 totalRate에 새로운 리뷰의 평점을 더해주고,
기존 reviewCount를 +1 해주며
새로 평균 평점을 계산하여 기존 averageRate를 갱신 해주는 방식입니다.

 

만약 기존 리뷰의 평점이 수정된다면
기존 평점과 변경되는 평점의 차이 만큼 totalRate에 더해준다

예를 들어 3점 -> 5점 변경이라면 5-3= 2이기에 2를 더해주고

만약 5점 -> 3점 변경이라면 3-5= -2이기에 -2를 더해줍니다.
그리고 averageRate를 갱신해줍니다.

 

위와 같은 방식을 사용한다면 리뷰 작성 / 수정 시에만 특정 컬럼의 값을 update해주기만 하면 되고 평균 평점에 관련해서
sum, count와 같은 집계함수를 사용하지 않아도 됩니다.

select question.*
from
    question 
left join
    question_review_statistics on question_review_statistics.question_id = question.id 
group by
    question.id 
order by
    question_review_statistics.average_rate desc,
    question.id desc
limit 10 
offset 0

따라서 question을 평점순으로 정렬한다면 위와 같은 sql로 간단하게 처리할 수 있게 되었습니다.

 

리뷰 시스템 평점에 대한 최적화는 원하는 방향에 맞게 잘 완료 된 것 같습니다.
그러나 예상되는 문제점은 리뷰 통계 테이블의 totalRate, reviewCount, averageRate의 동시성 이슈가 발생할 수 있습니다.

 

예를 들어 같은 question에 대해 Review 작성 / 수정 요청이 동시에 들어오게 되면 동시성 이슈가 발생할 수 있기 때문에 이 부분에
대해서는 검증 및 보완을 진행해야겠습니다.