[배달뚝배기] 인덱스와 배치를 적용한 성능 최적화

0. 들어가기 전

인덱스 관련된 강의를 들으면서, 강의에서 배운 내용을 이해하기 위해 전에 개발했었던 배달뚝배기 프로젝트에 인덱스를 적용해보고자 한다. 강의에서 배운 내용은 다음과 같다.

• 인덱스를 추가한다면, Where 절에 자주되는 컬럼, 혹은 Order by로 매번 정렬하는 컬럼을 고려해봐야한다.
• 두 가지 칼럼을 대상으로 검색하는 경우, 카디너리티가 높은 (값의 종류가 많은) 컬럼을 우선으로 세워야 한다. 
• 게시물의 댓글 수와 같이, 빈번하게 조회되지만 조인으로 성능이 저하되는 경우에는 반정규화도 고려해봐야한다.
• 대규모 GROUP BY를 수행하여 통계 등의 긴 응답시간이 발생하는 통계처리는 배치를 고려해봐야한다.

실제로 프로젝트에서 인덱스를 적용해보겠다.

 

1.  병목 가능성이 있을만한 쿼리는 무엇인가?

병목 가능성이 있는 쿼리를 찾기 위해, mock-data.sql 과 auth.http, 그리고 order.http 파일을 생성하였다.

accessToken을 저장하기 위한 로직

병목 쿼리를 확인하기 위한 API 들

mock 데이터 생성 sql 문. (order 테이블 데이터 50만개 생성)

2. 병목 쿼리는 어느 부분인가?

주문 도메인과 관련된 API 중 ADMIN에 관련된 쿼리가 몇 십 ms로 걸리는 다른 쿼리들과 다르게 각각 414ms, 572ms 로 300ms 를 초과하여 응답이 된다. 이 두 쿼리의 병목 지점을 확인해야겠다. 

 

3. 인덱스를 추가한다면, 어떤 컬럼에 추가하고 싶나?

첫번째 API는 다음과 같다. 특정 달의 주문 건 수를 구하는 코드이다.

GET http://localhost:8080/api/admin/orderCount/month?month=2024-12

 

먼저 인덱스를 추가하는 방법을 생각하였다. 쿼리는 다음과 같다.

SELECT COUNT(*) 
FROM orders o 
WHERE o.created_at BETWEEN '2024-12-01 00:00:00' AND '2024-12-31 23:59:59';

이는 created_at 컬럼을 기준으로 조회하지만, 인덱스가 따로 존재하지 않기 때문에 full table scan을 할 것이라고 예상한다.

실제로 위와 같은 실행 계획이 출력된다. 접근한 rows는 약 100만개로, 모든 rows를 조회한 것을 볼 수 있다.

 

이제 created_at에 인덱스를 추가하여 옵티마이저가 range 스캔 방식을 선택하도록 해보자.

create index idx_orders_created_at on orders(created_at);

다음 실행 계획을 살펴보자.

위와 같이 range 스캔 방식으로 조회한다. 접근한 rows 수도 171272 개로 20% 미만으로 줄었다.

밑에 Admin APIs 가 첫번재 쿼리에 대한 응답 시간

응답 시간은 572ms->97ms 로 대폭 감소시켰다.

 

두 번째로  애플리케이션 상 오류를 해결할 수 있는 방법이 있을지 고민했다.

public OrderTotalResponseDto getOrderCount(String date) {

        YearMonth yearMonth = YearMonth.parse(date);
        LocalDate startDate = yearMonth.atDay(1);
        LocalDate endDate = yearMonth.atEndOfMonth();

        LocalDateTime startDateTime = startDate.atStartOfDay();
        LocalDateTime endDateTime = endDate.atTime(23, 59, 59, 999_999_999);

        Long count = orderRepository.countOrdersByDateRange(startDateTime, endDateTime);

        return OrderTotalResponseDto.from(count);
    }

위와 같이 '2024-12' 와 같은 문자열을 받으면 2024-12-01 00:00:00 과 2024-12-31 23:59:59 를 생성한 후, 쿼리를 날리기 때문이다. 복잡한 과정 없이 2024-12만 가지고 쿼리를 실행할 수 있지 않을까? 두 가지 방법을 생각해봤지만 다음과 같은 이유로 진행하지는 않았다.

• LIKE 연산을 활용해 필터링(LIKE '2024-12%')
  • createdAt 컬럼이 문자열(VARCHAR) 타입일 때만 효과적이다. 현재 createAt 컬럼이 DATETIME 타입이기 때문에 이 방법은 인덱스를 사용하지 못해 오히려 성능이 저하된다.
• WHERE DATE_FORMAT(o.createdAt, '%Y-%m') = '2024-12'
  • createdAt 컬럼이 DATETIME 타입이라면, DATE_FORMAT 함수를 사용하여 날짜를 'YYYY-MM' 형태의 문자열로 변환한 후 비교하는 방법도 있습니다. 하지만 이 또한 인덱스를 사용하지 못하게 만들어 성능이 저하된다.

결국 변경되지 않은 원본 값을 기준으로만 효율적으로 검색한다는 인덱스의 특성 때문에 위 두 가지 방법을 적절하지 못하다.

 

마지막으로 생각한 방법은 batch를 사용하여 통계 결과를 저장해두는 방법이다. COUNT(*)라는 집계 함수를 실시간으로 실행하지 않아도 되기 때문이다. 물론 이 방식에도 데이터가 실시간이 아니라는 단점이 있다. 하지만 대부분의 통계성 데이터는 수 분 정도의 지연이 크게 문제되지 않는다. 관리자 대시보드나 일일 매출 현황 같은 용도라면 충분히 수용 가능하다고 생각한다.

 

다음과 같이 스프링 스케줄러를 작성하였다.

@Transactional
    @Scheduled(cron = "0 */3 * * * *") // 매 3분마다 실행
    public void updateMonthlyOrderStatistics() {
        LocalDate now = LocalDate.now();
        int year = now.getYear();
        int month = now.getMonthValue();

        log.info("[{}-{}] 월별 주문 통계 업데이트 시작", year, month);

        long totalOrders = orderRepository.countByYearAndMonth(year, month);

        MonthlyOrderStatistic statistic = monthlyOrderStatisticRepository.findByYearAndMonth(year, month)
                .orElseGet(() -> MonthlyOrderStatistic.builder()
                        .year(year)
                        .month(month)
                        .orderCount(0L)
                        .build());

        statistic.updateOrderCount(totalOrders);

        monthlyOrderStatisticRepository.save(statistic);

        log.info("[{}-{}] 월별 주문 통계 업데이트 완료. 총 주문 수: {}", year, month, totalOrders);
    }

따라서, 더이상 실시간으로 쿼리로 count 작업을 진행하는 것이 아닌 이미 계산한 집계 결과를 조회하게 된다.

실제로 응답시간이 97ms -> 26 ms로 감소되었다.

 

2) 두 번째 쿼리는 첫 번째 쿼리와 매우 비슷하다. month 가 아닌 date를 기준으로 주문 건 수를 구하기 때문이다.

GET http://localhost:8080/api/admin/orderCount/date?date=2024-08-30

따라서 이 쿼리는 이미 created_at 컬럼에 적용된 칼럼에 의해 성능이 향상될 것은 예상한다.

위에 #2 가 두번째 쿼리에 대한 응답 시간

응답 시간이 414ms -> 70ms 로 감소하였다.

5. 인덱스 적용 후 데이터 삽입/수정 시 성능 저하가 생긴다면, 어떻게 대처 가능한가? 

인덱스는 항상 조회 <-> 수정 간 트레이드 오프를 고려하여야 한다. 만약 이에 대한 성능 저하가 생긴다면, 너무 많은 인덱스를 만들지 않도록 주의해야한다. 따라서 실제로 쿼리들의 응답 시간을 확인하며 병목 쿼리를 확인하고, 그 쿼리가 얼마나 많이 조회되는지를 고려하여 진행할 것이다.