쿼리 최적화를 했지만 부족하다면, Parallel Stream으로 성능 개선하기

 

2024.04.19 - [SQL/쿼리 최적화] - 1000만건의 데이터를 대상으로 쿼리최적화 with. 복합인덱스, 커버링인덱스

1000만건의 데이터를 대상으로 쿼리최적화 with. 복합인덱스, 커버링인덱스

 

지난번 포스팅에서 쿼리 최적화에 대해 작성했었는데, 복합인덱스와 커버링인덱스를 적용했음에도 대규모 데이터셋이라 속도가 느린편이었습니다. 그리고 MySQL상에서 최적화를 해도, API로 연동하고나면 1~2s 정도 더 느려지는 현상이 있었습니다.

이유는 네트워크 IO, 스프링 자체의 로직을 타는 것 등 추가적인 작업이 있기 때문입니다.

 

그렇다면 이 느린 쿼리를 좀 더 빠르게 하는 방법이 없을지 고민해본 내용을 포스팅해보겠습니다.

 

우선 지난번 쿼리에서 변경해야 할 내용이 있는데, 월별로 통계를 내려다보니 Month()함수를 사용했습니다.

하지만 년도가 달라지면 월별 구분이 어렵기 때문에 다른 함수를 사용해야 했습니다.

 

연도-월 별로 구분해주기 위해 아래 2가지 함수를 고려했습니다.

• DATE_FORMAT(date,format)
• EXTRACT(unit FROM date)

YEAR()과 MONTH()함수를 동시에 사용하지 않은 이유는 그룹핑과 정렬에 조건이 추가되기 때문입니다.

 

📌 MONTH vs DATE_FORMAT

 

맨 처음에는 첫 번째 방식인 DATE_FORMAT을 고려했습니다. 하지만 동일한 인덱스 설정이 되어 있는 상태임에도, ANALYZE로 나온 결과는 수정 후 쿼리인 DATE_FORMAT이 1초 정도 더 느리더라구요.

## 이전 쿼리
SELECT MONTH(od.created_at) as month,
		COUNT(od.id),
		SUM(od.total_payment_price),
        COUNT(oc.id),
        SUM(od.total_discount_price)
FROM order_detail od
LEFT JOIN order_coupon oc ON od.id = oc.order_id
WHERE od.created_at BETWEEN '2024-01-01' and '2024-05-30'
GROUP BY month
ORDER BY month;

## 수정한 쿼리
SELECT  DATE_FORMAT(od.created_at, '%Y-%m') as `year_month`,
		COUNT(od.id),
		SUM(od.total_payment_price),
        COUNT(oc.id),
        SUM(od.total_discount_price)
FROM order_detail od
LEFT JOIN order_coupon oc ON od.id = oc.order_id
WHERE od.created_at BETWEEN '2024-01-01' and '2024-05-30'
GROUP BY `year_month`
ORDER BY `year_month`;

 

`` 백틱을 사용한 이유는 year_month가 MySQL에 존재하는 키워드라서 SQLSyntaxErrorException 에러가 나기 때문에 백틱으로 감싸줬습니다.

	MONTH	DATE_FORMAT
atucal time	6698ms	8144ms

 

DATE_FORMAT은 DATETIME 타입의 칼럼이나 값을 원하는 형태의 문자열로 변환할때 사용하는데, 필요한 포맷으로 문자열로 변환하는 과정에서 오버헤드가 발생하기 때문에 더 오래 걸린다고 판단했습니다.  ①

MySQL공식문서에서도 DATE_FORMAT은 문자 집합 세트와 콜레이션에 의해 제공되는 문자열로 반환한다고 되어있습니다.

이처럼 원래의 타입이 아니라 문자열로 변환된 데이터는 인덱스를 효율적으로 사용하지 못합니다. ②

 

① Real MySQL 2권 p.27

② Real MySQL 2권 p.69

 

 

📌 DATE_FORMAT vs EXTRACT(YEAR_MONTH FROM ...)

반면에 EXTRACT(YEAR_MONTH FROM...) 은 DATE_FORMAT에 비해 더 1s더 빨라서 이전 쿼리랑 속도가 비슷했습니다.

 

데이터형식이 반환되는 차이는 아래와 같습니다.

• DATE_FORMAT( col, '%Y-%m' ) : 2024-01
• EXTRACT( year_month from col ) : 202401

SELECT  EXTRACT(year_month from od.created_at) as `year_month`,
		COUNT(od.id),
		SUM(od.total_payment_price) ,
        COUNT(oc.id),
        SUM(od.total_discount_price) as total_discount_price
FROM order_detail od
LEFT JOIN order_coupon oc ON od.id = oc.order_id
WHERE od.created_at BETWEEN '2024-01-01' and '2024-05-30'
GROUP BY `year_month`
ORDER BY `year_month`;

 

	DATE_FORMAT	EXTRACT(YEAR_MONTH FROM...)
actual time	8144ms	6307ms

 

EXTRACT함수는 단순히 주어진 날짜에서 지정된 단위의 값을 추출하기 때문에, 문자열 조합과 포맷화가 필요한 DATE_FORMAT보다 더 빠릅니다. 따라서 EXTRACT(YEAR_MONTH FROM...)를 사용하기로 했습니다.

 

 

이제 SQL문장이 완성되었고, 이제 이 쿼리를 어떻게 더 최적화할지 고민해봤습니다.

 

📌  분리 없이 단일 쿼리 실행

먼저 완성된 SQL문장을 변형없이 그대로 API로 응답할때 시간이 얼마나 걸리는지 테스트해봤습니다.

 

포스트맨으로 확인한 결과 API 응답 시간은 6.65s정도가 걸리는 것을 확인했습니다.

 

📌 집계함수별로 쿼리 여러번 실행

다음으로 집계 함수 별로 쿼리를 여러번 실행해봤습니다.

집계함수가 Slow Query를 만드는 주범이라면, 쿼리내에서 여러 집계함수가 있어서 그런것인지 파악하기 위해서입니다.

// 월별 연월
SELECT  EXTRACT(year_month from od.created_at) as `year_month`
FROM order_detail od
LEFT JOIN order_coupon oc ON od.id = oc.order_id
WHERE od.created_at BETWEEN '2024-01-01' and '2024-05-30'
GROUP BY `year_month`
ORDER BY `year_month`;

// 월별 총 주문수
SELECT COUNT(od.id)
FROM order_detail od
LEFT JOIN order_coupon oc ON od.id = oc.order_id
WHERE od.created_at BETWEEN '2024-01-01' and '2024-05-30'
GROUP BY EXTRACT(year_month from od.created_at) 
ORDER BY EXTRACT(year_month from od.created_at);

// 월별 총 주문 금액
SELECT SUM(od.total_payment_price)
FROM order_detail od
LEFT JOIN order_coupon oc ON od.id = oc.order_id
WHERE od.created_at BETWEEN '2024-01-01' and '2024-05-30'
GROUP BY EXTRACT(year_month from od.created_at) 
ORDER BY EXTRACT(year_month from od.created_at);

// 월별 총 주문 중 쿠폰 사용 수
SELECT COUNT(oc.id)
FROM order_detail od
LEFT JOIN order_coupon oc ON od.id = oc.order_id
WHERE od.created_at BETWEEN '2024-01-01' and '2024-05-30'
GROUP BY EXTRACT(year_month from od.created_at) 
ORDER BY EXTRACT(year_month from od.created_at);

// 월별 총 주문 중 쿠폰 사용 금액
SELECT SUM(od.total_discount_price) as total_discount_price
FROM order_detail od
LEFT JOIN order_coupon oc ON od.id = oc.order_id
WHERE od.created_at BETWEEN '2024-01-01' and '2024-05-30'
GROUP BY EXTRACT(year_month from od.created_at) 
ORDER BY EXTRACT(year_month from od.created_at);

 

Mybatis에서 사용한 쿼리와 Mapper 인터페이스 기록

@Mapper
public interface StatisticsRepository {
    List<String> getMonthlyStatistics(final MonthlyStatisticsParameterVo monthlyStatisticsParameterVo);
    List<Long> getMonthlyOrderCntStatistics(final MonthlyStatisticsParameterVo monthlyStatisticsParameterVo);
    List<BigDecimal> getMonthlyOrderPriceStatistics(final MonthlyStatisticsParameterVo monthlyStatisticsParameterVo);
    List<Long> getMonthlyCouponCntStatistics(final MonthlyStatisticsParameterVo monthlyStatisticsParameterVo);
    List<BigDecimal> getMonthlyCouponPriceStatistics(final MonthlyStatisticsParameterVo monthlyStatisticsParameterVo);
}

   <select id="getMonthlyStatistics" parameterType="MonthlyStatisticsParameterVo" resultType="String">
        SELECT  EXTRACT(year_month from od.created_at) as `year_month`
        FROM order_detail od
                 LEFT JOIN order_coupon oc ON od.id = oc.order_id
        WHERE od.created_at BETWEEN #{startedAt} and #{endedAt}
        GROUP BY `year_month`
        ORDER BY `year_month`;
    </select>

    <select id="getMonthlyOrderCntStatistics" parameterType="MonthlyStatisticsParameterVo" resultType="long">
        SELECT COUNT(od.id)
        FROM order_detail od
                 LEFT JOIN order_coupon oc ON od.id = oc.order_id
        WHERE od.created_at BETWEEN #{startedAt} and #{endedAt}
        GROUP BY EXTRACT(year_month from od.created_at)
        ORDER BY EXTRACT(year_month from od.created_at);
    </select>

    <select id="getMonthlyOrderPriceStatistics" parameterType="MonthlyStatisticsParameterVo" resultType="Bigdecimal">
        SELECT SUM(od.total_payment_price)
        FROM order_detail od
                 LEFT JOIN order_coupon oc ON od.id = oc.order_id
        WHERE od.created_at BETWEEN #{startedAt} and #{endedAt}
        GROUP BY EXTRACT(year_month from od.created_at)
        ORDER BY EXTRACT(year_month from od.created_at);
    </select>

    <select id="getMonthlyCouponCntStatistics" parameterType="MonthlyStatisticsParameterVo" resultType="long">
        SELECT COUNT(oc.id)
        FROM order_detail od
                 LEFT JOIN order_coupon oc ON od.id = oc.order_id
        WHERE od.created_at BETWEEN #{startedAt} and #{endedAt}
        GROUP BY EXTRACT(year_month from od.created_at)
        ORDER BY EXTRACT(year_month from od.created_at);
    </select>

    <select id="getMonthlyCouponPriceStatistics" parameterType="MonthlyStatisticsParameterVo" resultType="Bigdecimal">
        SELECT SUM(od.total_discount_price) as total_discount_price
        FROM order_detail od
                 LEFT JOIN order_coupon oc ON od.id = oc.order_id
        WHERE od.created_at BETWEEN '2024-01-01' and '2024-05-30'
        GROUP BY EXTRACT(year_month from od.created_at)
        ORDER BY EXTRACT(year_month from od.created_at);
    </select>

 

쿼리를 분리하는 것도 쿼리 최적화 방법 중 하나라고 들었기 때문에 최적화가 가능할 것이라고 생각했지만, 오히려 시간이 더 느려진것을 확인할 수 있습니다. 하나의 쿼리당 4~5s씩 걸리는데, 5번을 나눠서 쿼리를 실행하다보니 합산되어 28.27s가 나온 것입니다.

 

그러면 SQL문장내에서 집계함수가 여러개이기 때문에 Slow Query가 되는 것이 아니라는 것을 알게 되었습니다.

생각해보면 집계함수라는건 필터링된 조건이 있다면 해당 조건에서 읽어야할 레코드 건수를 전부 다 읽는 것인데, 집계함수를 분리한다고 레코드 건수가 줄어드는 것이 아니었습니다.

 

그러면 어떻게 하면 레코드 건수를 줄일 수 있을까요?

 

where절 기준으로 필터링된 레코드 건수를 줄이면 되지 않을까? 라는 생각에 도달했습니다.

 

예를 들어 1월초부터 5월말까지 조회해야 한다면 월별로 나눠서 조회하는 것이죠.

 

실제로 1월부터 5월까지 조회한것보다 1월초부터 2월말까지 조회한 것이 더 속도가 빠릅니다.

	1월 1일 부터 5월 30일 까지	1월 1일 부터 2월 29일 까지
Duration	5.413sec	2.117sec

 

📌 병렬스트림으로 월별로 쿼리 여러번 실행

 

월별로 분리를 하더라도 결국에는 집계함수를 분리했던 것 처럼 1~2s걸리는 쿼리가 합산되면  결국 느린 쿼리가 되는데요.

월별로 분리한 쿼리를 동시에 여러번 실행하기 위해 병렬 스트림을 사용했습니다.

 

시작날짜와 종료날짜를 입력으로 받은 내용을 BETWEEN절에 넣어주기 위해 각 월초와 월말을 계산해서 쿼리를 여러번 실행했습니다.

 

비록 이전보다코드는 길어졌지만, 6.65s에서 2.77s로 줄어든 것을 확인할 수 있습니다.

	분리 없이 단일 쿼리로 실행할때	집계함수 기준으로 분리해서 여러번 쿼리 실행할때	월 별 기준으로 분리해서 병렬 스트림으로 여러번 쿼리 실행할때
API 응답 속도	6.65s	28.27s	2.77s

 

📌  마무리

이번 포스팅에서는 병렬 스트림을 사용해서 동시에 여러 쿼리를 실행함으로써 성능 개선하는 내용을 다뤘습니다.

쿼리 최적화가 만족스럽지 못할때, 애플리케이션에서 병렬 스트림을 적용하면 이중으로 최적화가 가능합니다.

 

다만 병렬 스트림을 사용할때 주의할 점이 2가지가 있습니다.

• 동시 작업을 수행하는 병렬스트림이므로 Thread-safe한 코드로 만들어야 합니다.
• 쿼리 실행 시간이 단축될 수 있지만, 동시에 쿼리를 실행하다보니 데이터베이스에 부하가 발생할 수 있습니다.
• 따라서 connection pool에 대해서도 고려해봐야 합니다.

 

또한 지난번 쿼리 최적화때와 다르게 동일한 인덱스를 설정했음에도 성능이 좀 더 느린 것이 보이는 건 제 로컬 컴퓨터의 CPU와 메모리 상태가 그때 당시와 달라서 그렇습니다.

 

별도로 서버를 구축해서 테스트해보면 좋을테지만 이렇게 여러번 수정하고 테스트하는 과정에서 일일이 배포하는게 번거롭다고 느껴져서 로컬에서 테스트했는데, 혹시 좋은 방법 아시는 분 계시다면 피드백 남겨주시면 감사드리겠습니다.