[MySQL] 계층형 재귀 쿼리를 사용해서 성능개선 및 확장성 있는 구조로 프로젝트 리팩토링하기

개요

이번 글에서는 계층형 재귀 쿼리를 사용해서 성능개선 및 프로젝트를 유지보수와 확장에 용이한 구조로 만들었던 과정에 대해 이야기해보려고 한다.

프로젝트 개요

현재 개인적으로 배달의 민족 모작 프로젝트를 진행하고 있는 중에 각 별점마다 해당하는 리뷰의 개수와 점수별로 별의 색을 칠하고 표시해주는 작업이 필요했다.

리뷰 탭

리팩토링 전

문제점 - 1

SELECT
	storeIdn,
	storeName,
	storeAddress,
	storePhonenum,
	storeIntro,
	minDelevery,
	deleveryTime,
	deleveryTip,
	(SELECT COUNT(*) FROM bm_review WHERE storeIdn = {storeIdn}) AS reviewCount,
	(SELECT COUNT(*) FROM bm_review WHERE storeIdn = {storeIdn} AND reviewScore = 5) AS fiveScore,
	(SELECT COUNT(*) FROM bm_review WHERE storeIdn = {storeIdn} AND reviewScore = 4) AS fourScore,
	(SELECT COUNT(*) FROM bm_review WHERE storeIdn = {storeIdn} AND reviewScore = 3) AS threeScore,
	(SELECT COUNT(*) FROM bm_review WHERE storeIdn = {storeIdn} AND reviewScore = 2) AS twoScore,
	(SELECT COUNT(*) FROM bm_review WHERE storeIdn = {storeIdn} AND reviewScore = 1) AS oneScore,
	(SELECT (SUM(reviewScore) / COUNT(*)) FROM bm_review WHERE storeIdn = {storeIdn}) AS reviewAverage,
	(SELECT COUNT(*) FROM bm_order WHERE storeIdn = {storeIdn}) AS orderCount
FROM
	bm_store
WHERE
	storeIdn = {storeIdn}

위 쿼리와 같이 1 ~ 5점에 대한 모든 리뷰 개수를 서브쿼리 형태로 제공하고 있었다.

위 쿼리를 보면서 뭔가 지저분해 보였고 점수가 1 ~ 5점만 있는 구간에선 어찌저찌 먹고 들어갈지 몰라도 점수가 1 ~ 100점까지 세분화되는 환경이 되면 확장성을 가질 수 없는 구조가 되며,  성능 또한 서브쿼리가 생겨나는 만큼 더욱 느려지게 된다.

현재 해당 쿼리는 0.0052의 Duration을 가진다.

리팩토링 이전 쿼리

문제점 - 2

이외에도 DB에서 조회한 값을 View에 뿌려주는 과정에서 문제점이 한 가지 더 존재한다.

<div> 
	<div>5점</div>
	<div class="">
		<div class="graph_background"></div>
		<c:forEach begin="0" end="4" var="i">
			<c:choose>
				<c:when test="${5 > i }">
					<i class="far fas fa-star"></i>
				</c:when>
				<c:otherwise>
					<i class="far fa-star"></i>
				</c:otherwise>
			</c:choose>
		</c:forEach>
    </div>
    <div class="review_count">${storeInfo.fiveScore}</div>
</div>
						
<div> 
	<div>4점</div>
	<div class="">
		<div class="graph_background"></div>
		<c:forEach begin="0" end="4" var="i">
			<c:choose>
				<c:when test="${4 > i }">
					<i class="far fas fa-star"></i>
				</c:when>
				<c:otherwise>
					<i class="far fa-star"></i>
				</c:otherwise>
			</c:choose>
		</c:forEach>
	</div>
	<div class="review_count">${storeInfo.fourScore}</div>
</div>

1 ~ 3 .....

현재 조회 쿼리는 fiveScore, fourScore ... oneScore 이런 식으로 각 점수에 대한 리뷰 개수를 개별로 받아와 하나의 ROW에서 조회하기 때문에 반복문으로 뿌릴 수 없어 모든 점수에 대한 영역을 만들어주고 View에 하나씩 끼워 맞추고 있는 비효율적인 구조를 가지고 있으며 이는 하나를 수정하려면 모든 영역에 대한 수정을 필요로 하는 확장성 없는 구조를 의미한다.

리팩토링

우선 리팩토링을 위해 기존의 각 점수별 리뷰 개수를 가져오던 서브쿼리를 제거해준다.

SELECT
	storeIdn,
	storeName,
	storeAddress,
	storePhonenum,
	storeIntro,
	minDelevery,
	deleveryTime,
	deleveryTip,
	(SELECT COUNT(*) FROM bm_review WHERE storeIdn = {storeIdn}) AS reviewCount,
	(SELECT (SUM(reviewScore) / COUNT(*)) FROM bm_review WHERE storeIdn = {storeIdn}) AS reviewAverage,
	(SELECT COUNT(*) FROM bm_order WHERE storeIdn = {storeIdn}) AS orderCount
FROM
	bm_store
WHERE
	storeIdn = 1

기존의 서브쿼리 5줄을 제거하여 0.0052의 Duration을 갖던 쿼리가 0.00087의 Duration을 가지게 되었다.

기존의 쿼리보다 6배정도 빨라진 성능을 가지게 되었다.

서브쿼리 제거

하지만 각 점수별 리뷰 개수가 없어졌기 때문에 이 부분에 대한 쿼리를 하나 만들어 줘야한다.

이는 계층형 재귀 쿼리를 사용하여 점수별로 ROW를 조회할 수 있다.

WITH RECURSIVE scoreBoard AS (
	SELECT   
		1 AS score
	UNION ALL
	SELECT
		score + 1
	FROM
		scoreBoard
	WHERE
		5 > score
)
SELECT
	A.score AS reviewScore,
	IFNULL(B.cnt, 0) AS reviewCnt
FROM
	scoreBoard AS A LEFT OUTER JOIN (
		SELECT
			reviewScore,
			COUNT(*) AS cnt
		FROM
			bm_review
		WHERE
			storeIdn = 1
		GROUP BY
			reviewScore
) AS B on (A.score = B.reviewScore)
GROUP BY
	A.score
ORDER BY
	A.score DESC

해당 쿼리를 통해 1 ~ 5를 가지는 scoreBoard라는 테이블을 만들고 리뷰 테이블과의 OUTER JOIN을 통해 만약 리뷰 테이블에 1 ~ 5점 중에 해당하는 레코드가 하나도 없어도 NULL로 조회될 수 있도록 하여 IFNULL과 함께 0으로 조회된다.

점수 별 리뷰 개수 조회

해당 쿼리는 0.0022의 Duration을 가진다.

점수 별 리뷰 개수 조회 쿼리

최종적으로 위 쿼리를 통해 리뷰 점수의 범위(1 ~ 5)만큼 레코드를 조회할 수 있고 View에 데이터를 뿌려줄 때 반복문을 통해 접근할 수 있다.

<c:forEach items="${reviewScoreLists}" var="reviewScoreList">
	<div> 
		<div>${reviewScoreList.reviewScore}점</div>
		<div class="">
			<div class="graph_background"></div>
			<c:forEach begin="0" end="4" var="i">
				<c:choose>
					<c:when test="${reviewScoreList.reviewScore > i }">
						<i class="far fas fa-star"></i>
					</c:when>
					<c:otherwise>
						<i class="far fa-star"></i>
					</c:otherwise>
				</c:choose>
			</c:forEach>
		</div>
		<div class="review_count">${reviewScoreList.reviewCnt}</div>
	</div>
</c:forEach>

하나의 반복문을 통해 View를 관리할 수 있어 유지보수에 용이하고 확장성 있는 구조를 가지게 되었으며

 

시간계산

Duration을 0.0052 -> 0.0031초로 개선하여 1.6배의 빠른 성능 또한 가지게 될 수 있다.

현재는 단순히 0.1초도 차이가 나지 않지만 데이터가 무수히 많아진다면 유의미한 차이가 있을 것이다.