문제 상황

간략한 위클리 리포트 서비스 소개

위클리 리포트 서비스는 이름대로 한 주 동안 유저가 작성한 편지(또는 일기)에 대한 분석을 제공합니다.

서비스 정책 안에서 왕성히 활동한 유저의 7일 치 글을 모두 합치면 최대 16만 글자가 넘을 수 있습니다.

 

현실적으로 16만 글자를 생성형 AI에게 전달하는 것은 비용 문제와 서비스 품질 문제로 이어질 수 있기 때문에 `데일리 리포트`를 이용하는데요, `데일리 리포트`에 하루치가 요약되어 있는 내용을 이용해 `위클리 리포트`를 생성합니다.

서비스 정책 간략 소개

`위클리 리포트`는 절대적으로 `데일리 리포트`에 의존하기 때문에 반드시 `데일리 리포트`가 존재해야 합니다.

위의 의존성 문제 때문에 우리 서비스의 정책은 유저가 한 주간 편지를 작성만 하고 `데일리 리포트`를 생성하지 않았을 때를 대비해 `위클리 리포트`를 생성하기 전 `데일리 리포트`를 미리 생성을 수행합니다.

 

이때 미리 데일리 리포트를 생성하기 위해 편지들을 찾아야 하는데요, 이때의 편지들을 `분석 가능한 편지들`이라고 정의합니다.

그리고 `분석 가능한 편지들`을 골라내는데 몇 가지 제약을 간략하게 소개 합니다.

`분석 가능한 편지들` 관련 제약 조건:

  • 매 일자마다 최신 글 3개만 `분석 가능한 편지들`에 포함된다.
  • 데일리 리포트가 생성된 일자에 작성된 모든 편지들은 (분석되지 않은 편지가 일부 있더라도) `분석 가능한 편지들`에서 제외된다.

위클리 리포트 생성 과정 정리

아래는 `위클리 리포트`를 생성하는 과정을 정리한 그림입니다.

이 과정은 모두 하나의 거대한 메서드 안에서 실행되며, 이를 전문 용어로 몬스터 메서드라고 하죠.

 

아래 몬스터 메서드가 야기하는 수많은 문제가 있습니다.

문제 발단: 협력 관계에서 잘못 할당된 책임

몬스터 메서드들 (전체)

몬스터 메서드를 호출하는 몬스터 메서드

노란색 박스만 보세요.

대충 보더라도 오른쪽 일부 메서드를 호출하는 로직이 왼쪽의 몬스터 메서드를 호출합니다.

호출되는 몬스터 메서드 (일부)

호출되는 몬스터 메서드

호출되는 데일리 리포트 서비스의 메서드입니다. (전체 그림에서 왼쪽 부분)

해당 로직을 나누면 크게 3가지로 나눌 수 있습니다.

  1. `분석 가능한(=데일리 리포트 생성 가능한) 편지들` 찾기
  2. 찾은 편지들로 `데일리 리포트` 생성하기 (외부 API 호출)
  3. `데일리 리포트` DB 저장하기

위의 로직에서 야기하는 세 가지 문제가 있습니다.

  • 문제 1. `데일리 리포트 서비스`가 `분석 가능한 편지들`을 찾는 책임을 집니다. SRP를 준수하지 못하고 있습니다.
  • 문제 2. 트랜잭션 내부에 외부 API를 호출하는 로직이 존재해 SRP를 준수하지 못할뿐더러 잠재적인 문제를 야기합니다.
  • 문제 3. 전체적으로 코드의 가독성이 떨어져 유지 보수하기가 어렵습니다.

결과적으로 서비스들에게 잘못 할당된 책임 때문에 유지 보수하기가 어려웠던 것입니다.

덤으로 전체적인 코드의 가독성이 떨어져 유지 보수의 어려움을 야기합니다.

 

이번 포스팅에서는 책임의 재할당을 집중적으로 다루며, 외부 API를 호출하는 로직을 다루는 문제는 다음 포스팅에서 다루니 참고 부탁드립니다.

문제 해결하기: 책임 재할당

데일리 리포트 서비스는 어떤 책임을 갖고 있을까요?

먼저 캡슐화를 하기 전에 협력 관계와 메시지를 정의하겠습니다.

  • 협력 관계:
    • `위클리 리포트 서비스`는 `위클리 리포트`를 생성하기 위해 한 주 동안의 `데일리 리포트`가 필요하다.
      따라서 `데일리 리포트` 관련 정보 전문가인 `데일리 리포트 서비스`와 협력해야 한다.
    • `데일리 리포트 서비스`는 `데일리 리포트`를 생성하기 위해 `분석 가능한 편지들`이 필요하다.
      따라서 `편지` 관련 정보 전문가인 `편지 서비스`와 협력해야 한다.
  • 책임:
    • `위클리 리포트 서비스`: 일주일 치 `데일리 리포트`를 통해 `위클리 리포트`를 생성할 책임이 있다.
    • `데일리 리포트 서비스`: 일주일 치 `데일리 리포트`를 사전에 생성할 책임이 있다.
    • `편지 서비스`: 일주일 치 `분석 가능한 편지들`을 찾을 책임이 있다.
  • 메시지:
    • 1. `분석 가능한 편지들` 반환을 요청하는 메시지는 `편지 서비스` 객체를 선택한다.
      • → `편지 서비스`는 `분석 가능한 편지들`을 반환하는 메시지를 수신한다.
    • 2. `데일리 리포트` 반환을 요청하는 메시지는 `데일리 리포트 서비스` 객체를 선택한다.
      • → `데일리 리포트 서비스`는 `데일리 리포트`를 반환하는 메시지를 수신한다.

이제 첫 번째로 해야 할 일은 일주일 치 `분석 가능한 편지들`을 찾는 책임을 `데일리 리포트 서비스`로부터 분리해야 합니다.

`편지 서비스`에게 책임을 재할당하는 것이죠.

책임 재할당하기

데일리 리포트 서비스에 있던 코드는 아래와 같습니다.

주석 포함해서 20줄이 넘으니 읽을 필요는 없고, `데일리 리포트 서비스`에 `편지 서비스`의 책임이 할당되어 있는다는 것만 봐주시면 됩니다.

TL;DR) 데일리 리포트 서비스 코드

public void createDailyReportsBy(UUID userId, LocalDate startDate, LocalDate endDate) {
        // 주간분석을 요청한 기간 동안 사용자가 작성한 편지들 찾기
        List<Letter> userLettersByLatest = letterRepository.findByCreatedAtDesc(userId,
                startDate.atStartOfDay(),
                LocalDateTime.of(endDate, LocalTime.MAX));

        // 날짜별로 편지들을 3개씩 묶기
        Map<LocalDate, List<Letter>> latestLettersByDate = userLettersByLatest.stream()
                .collect(Collectors.groupingBy(
                        letter -> letter.getCreatedAt().toLocalDate()));

        // 이미 일일 분석이 생성된 날짜는 제거
        latestLettersByDate.values().removeIf(
                letters -> letters.stream().anyMatch(letter -> letter.getDailyReport() != null)
        );

        // 일일 분석을 생성하려는 편지들을 날짜당 3개로 제한
        Map<LocalDate, List<Letter>> latestThreeLettersByDate = latestLettersByDate.entrySet().stream()
                .collect(Collectors.toMap(
                        Entry::getKey,
                        entry -> entry.getValue().stream()
                                .limit(3)
                                .collect(Collectors.toList())
                ));
        // 생략 ...
}

`분석 가능한 편지들`을 찾는 이 로직을 `편지 서비스`에게 재할당해야 합니다.

 

저는 이 코드를 `편지 서비스(LetterService)`에게 재할당하되, 실제 구현은 `편지 저장소(LetterRepository)`에서 네이티브 쿼리로 구현하겠습니다.

편지 서비스 (LetterService)

@Transactional(readOnly = true)
public List<Letter> findAnalyzableLetters(UUID userId, LocalDate startDate, LocalDate endDate) {
    return letterRepository.findAnalyzableLetters(userId, toStartDateTime(startDate), toEndDateTime(endDate));
}

private LocalDateTime toStartDateTime(LocalDate date) {
    return LocalDateTime.of(date, LocalTime.MIN);
}

private LocalDateTime toEndDateTime(LocalDate date) {
    return LocalDateTime.of(date, LocalTime.MAX);
}

편지 저장소 (LetterRepository)

@Query(value = """
        SELECT letter_id, user_id, created_at, message, preference, published, like_f, like_t
        FROM (
          SELECT *, ROW_NUMBER() OVER (PARTITION BY DATE(created_at) ORDER BY created_at DESC) AS row_num
          FROM letter
          WHERE user_id = :userId
            AND created_at BETWEEN :start AND :end
            AND DATE(created_at) NOT IN (
              SELECT DISTINCT DATE(l.created_at)
              FROM letter l
              JOIN letter_analysis la ON l.letter_id = la.letter_id
              WHERE l.user_id = :userId
                AND l.created_at BETWEEN :start AND :end
          )) AS analyzable_letters
        WHERE analyzable_letters.row_num <= 3
        ORDER BY created_at
        """, nativeQuery = true)
List<Letter> findAnalyzableLetters(UUID userId, LocalDateTime startDate, LocalDateTime endDate);

책임이 재할당된 데일리 리포트 서비스 (DailyReportService)

public void createDailyReportsBy(UUID userId, LocalDate startDate, LocalDate endDate) {
    // 편지 서비스에게 `분석 가능한 편지들` 찾기 위임
    List<Letter> analyzableLetters = letterService.findAnalyzableLetters(userId, startDate, endDate);
}

편지 서비스에게 위임한 책임

책임을 올바르게 재할당하는 것만으로 20줄이 넘는 코드가 `편지 서비스`로, 그 구현은 다시 `편지 저장소`로 위임되었습니다.

 

이제 남은 것은 `분석 가능한 편지들`을 찾는 로직을 검증해야 합니다.

검증: 실행 계획 분석

프로젝트는 MySQL 8.0 버전을 사용 중입니다.

가상의 시나리오를 만들어 작성한 쿼리가 최적화된 쿼리인지 검증하기 위해 실행 계획을 분석해 보겠습니다.

가상 시나리오

아래와 같은 시나리오를 작성했습니다.

  • 유저(User)는 100명
  • 100명의 모든 유저는 하루에 1개의 편지를 작성, 10년 동안 지속
    ⇒ 편지 테이블(letter) 레코드 수는 365,000건 (100명 * 365일 * 10년)
  • 매월 첫 주만 `데일리 리포트`를 생성 (7일 치)
  • `분석 가능한 편지들`을 검증하기 위한 추가 레코드 (4건, 테스트 유저 1명 대상)
    • 데일리 리포트가 존재하는 일자에 분석되지 않은 편지가 1건 추가로 존재 (2024-01-04 1건 추가)
      → 데일리 리포트가 생성된 일자의 편지들은 더 이상 분석 대상이 되지 않음을 검증하기 위함
    • `분석 가능한 편지들`을 찾는 기간에 3건 더 작성된 일자가 포함 (2024-01-08 3건 추가)
      → 하루에 작성된 편지들 중 최신 편지 3개만 분석 대상이 됨을 검증하기 위함
  • 따라서 전체 레코드 수는 365,000 + 4건으로 365,004건입니다.
  • `분석 가능한 편지` 목표 개수: 7건
    `분석 가능한 편지들`을 찾는 기간은 `2024-01-04 - 2024-01-11 (8일)`이며, `2024-01-01 - 2024-01-07` 기간은 첫 주이기 때문에 이미 데일리 리포트가 생성되어 있습니다. 따라서 목표 개수는 총 7건입니다. (`2024-01-08 3건`, `2024-01-09 1건`, `2024-01-10 1건`, `2024-01-11 1건`)

`분석 가능한 편지들`을 찾는 쿼리

SELECT letter_id, user_id, message, preference, published, created_at
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY DATE(created_at) ORDER BY created_at DESC) AS row_num
  FROM letter
  WHERE user_id = :userId
    AND created_at BETWEEN :startDate AND :endDate
    AND DATE(created_at) NOT IN (
      SELECT DISTINCT DATE(l.created_at)
      FROM letter l
      JOIN letter_analysis la ON l.letter_id = la.letter_id
      WHERE l.user_id = :userId
        AND l.created_at BETWEEN :startDate AND :endDate
  )) AS analyzable_letters
WHERE analyzable_letters.row_num <= 3
ORDER BY created_at

위의 쿼리는 얼핏 보기에 복잡해 보일 수 있습니다.

이 쿼리를 쉽게 이해하기 위해 가장 바깥쪽의 FROM 절부터 보겠습니다.

 

바깥쪽의 FROM 절

SELECT *, ROW_NUMBER() OVER (PARTITION BY DATE(created_at) ORDER BY created_at DESC) AS row_num
FROM letter
WHERE user_id = :userId
AND created_at BETWEEN :startDate AND :endDate
AND DATE(created_at) NOT IN (
  SELECT DISTINCT DATE(l.created_at)
  FROM letter l
  JOIN letter_analysis la ON l.letter_id = la.letter_id
  WHERE l.user_id = :userId
    AND l.created_at BETWEEN :startDate AND :endDate
)
  • letter 테이블을 읽을 때 WHERE 절로 필터링을 합니다.
    • WHERE 절 3번째 조건: 서브쿼리를 사용합니다. (`NOT IN`)
      • 조건으로 사용된 서브쿼리: 조건으로 사용된(7일 치) 분석된 편지가 작성된 일자를 중복 없이 읽습니다.
  • 정리하면 날짜 조건(`BETWEEN`)에 따라 편지들을 읽되, 데일리 리포트가 생성된 일자를 제외합니다.
  • SELECT 절에서 MySQL에서 제공하는 `ROW_NUMBER()` 윈도우 함수를 이용해 DATETIME을 DATE로 변환한 일자를 기준으로 나누고(`PARTITION BY`), 해당 일자를 기준으로 내림차순 정렬(`ORDER BY DESC`)을 통해 최신 편지 순으로 정렬합니다.
    이후, 레코드마다 번호(row_num)를 부여합니다. 이 레코드 번호는 가장 바깥쪽 쿼리에서 최신 레코드 3개를 찾을 때(`WHERE row_num <= 3`) 사용됩니다.

`NOT IN` 서브쿼리 실행 계획 분석

위의 쿼리에서 조건절로 사용되는 서브쿼리의 실행 계획을 분석해야 합니다.

이유는 안티 세미 조인이라 불리는 `NOT IN`을 사용하는 서브쿼리는 Not-Equal 비교처럼(`<>` 연산자) 인덱스를 제대로 활용하지 못하는 경우가 많아 최적화할 방법이 많이 없기 때문입니다.

 

MySQL 옵티마이저는 이 경우 `NOT EXISTS` 또는 `Materialization`을 통해 최적화를 수행하지만 단독으로 사용된 NOT IN 서브쿼리는 풀 테이블 스캔을 피할 수 없기 때문에 주의해야 합니다. (Real MySQL 8.0 2권 - p.114)

 

이제 NOT IN 서브쿼리가 들어있는 전체 쿼리에 대해 실행 계획을 분석해 보겠습니다.

`편지 테이블(letter)`에는 PK, FK 같이 자동으로 생성되는 인덱스와 `감정 분석 테이블(letter_analysis)`의 유니크 키를 제외하고 어떤 인덱스도 생성하지 않았습니다.

분석 대상 쿼리 (NOT IN 서브쿼리)

EXPLAIN ANALYZE
SELECT *, ROW_NUMBER() OVER (PARTITION BY DATE(created_at) ORDER BY created_at DESC) AS row_num
FROM letter
WHERE user_id = :userId
AND created_at BETWEEN :start AND :end
AND DATE(created_at) NOT IN (
  SELECT DISTINCT DATE(l.created_at)
  FROM letter l
  JOIN letter_analysis la ON l.letter_id = la.letter_id
  WHERE l.user_id = :userId
    AND l.created_at BETWEEN :start AND :end)

실행 계획

(13)-> Window aggregate: row_number() OVER (PARTITION BY `cast(letter.created_at as date)` ORDER BY letter.created_at desc )   (actual time=101..101 rows=7 loops=1)
    (12)-> Sort: `cast(letter.created_at as date)`, letter.created_at DESC  (cost=954 rows=3654) (actual time=101..101 rows=7 loops=1)
       (11) -> Filter: ((letter.created_at between '2024-01-04 00:00:00' and '2024-01-11 00:00:00') and <in_optimizer>(cast(letter.created_at as date),cast(letter.created_at as date) in (select #2) is false))  (cost=954 rows=3654) (actual time=90.8..101 rows=7 loops=1)
           (10) -> Index lookup on letter using fk_letter_user (user_id=uuid_to_bin('fc700d72-e215-11ef-b651-0242ac120002')), with index condition: (letter.user_id = <cache>(uuid_to_bin('fc700d72-e215-11ef-b651-0242ac120002')))  (cost=954 rows=3654) (actual time=6.51..14.8 rows=3654 loops=1)
            (9) -> Select #2 (subquery in condition; run only once)
                (8) -> Filter: ((cast(letter.created_at as date) = `<materialized_subquery>`.`DATE(l.created_at)`))  (cost=1137..1137 rows=1) (actual time=8.39..8.39 rows=0.4 loops=10)
                    (7) -> Limit: 1 row(s)  (cost=1137..1137 rows=1) (actual time=8.39..8.39 rows=0.4 loops=10)
                        (6) -> Index lookup on <materialized_subquery> using <auto_distinct_key> (DATE(l.created_at)=cast(letter.created_at as date))  (actual time=8.39..8.39 rows=0.4 loops=10)
                            (5) -> Materialize with deduplication  (cost=1137..1137 rows=406) (actual time=83.9..83.9 rows=4 loops=1)
                                (4) -> Nested loop inner join  (cost=1096 rows=406) (actual time=0.42..83.9 rows=4 loops=1)
                                    (2) -> Filter: (l.created_at between '2024-01-04 00:00:00' and '2024-01-11 00:00:00')  (cost=954 rows=406) (actual time=0.312..83.7 rows=12 loops=1)
                                        (1) -> Index lookup on l using fk_letter_user (user_id=uuid_to_bin('fc700d72-e215-11ef-b651-0242ac120002')), with index condition: (l.user_id = <cache>(uuid_to_bin('fc700d72-e215-11ef-b651-0242ac120002')))  (cost=954 rows=3654) (actual time=0.307..81.8 rows=3654 loops=1)
                                    (3) -> Single-row covering index lookup on la using uk_letter_analysis_letter (letter_id=l.letter_id)  (cost=0.25 rows=1) (actual time=0.014..0.014 rows=0.333 loops=12)

결론적으로 이 실행 계획은 특정 유저(user_id)의 letter 테이블에서 특정 기간(2024-01-04 ~ 2024-01-11) 동안 작성된 레코드를 조회하고, 날짜별(row_number() PARTITION BY created_at)로 최신(created_at DESC) 1개씩을 제외한 결과를 가져오는 쿼리입니다.

 

실행 계획을 읽는 순서에 번호를 붙였으니 하나씩 읽어보겠습니다.

(1) letter 테이블에서 user_id로 필터링

(1) -> Index lookup on l using fk_letter_user (user_id=uuid_to_bin('fc700d72-e215-11ef-b651-0242ac120002')), with index condition: (l.user_id = <cache>(uuid_to_bin('fc700d72-e215-11ef-b651-0242ac120002')))  (cost=954 rows=3654) (actual time=0.307..81.8 rows=3654 loops=1)
  • letter 테이블에 있는 유저 아이디(user_id)에 대한 외래키인 `fk_letter_user` 인덱스를 이용해 읽었습니다.
    이때 `with index condition`인 것을 보아 인덱스 컨디션 푸시다운(index condition pushdown) 기능을 이용해 효율적으로 읽었습니다. (인덱스 컨디션 푸시다운: MySQL 엔진에서 스토리지 엔진으로 인덱스를 전달해서 스토리지 엔진이 불필요한 디스크 읽기(I/O)를 줄이는 기능)
  • 읽어야 하는 예상 레코드 수는 3,654건입니다.
    (전체 레코드 건수가 365,004건(=100*100*365 + 4; 100명이 하루에 100건씩 10년 치 + 해당 유저에 대한 테스트를 위한 레코드 4건)이며, 테스트 유저로 필터링하면 3,654건이 됩니다.)
  • 실제 읽은 레코드 수도 3,654건입니다.

(2) 날짜 범위 필터링

(2) -> Filter: (l.created_at between '2024-01-04 00:00:00' and '2024-01-11 00:00:00')  (cost=954 rows=406) (actual time=0.312..83.7 rows=12 loops=1)
  • 조회된 편지들을 2024년 1월 4일부터 1월 11일 사이로 필터링합니다.
    (`created_at BETWEEN '2024-01-04 00:00:00' AND '2024-01-11 00:00:00'`)
  • 필터링될 예상 레코드 수는 406건입니다.
  • 실제로 필터링된 레코드는 12건입니다.

(3) letter_analysis(감정 분석) 테이블 조회

(3) -> Single-row covering index lookup on la using uk_letter_analysis_letter (letter_id=l.letter_id)  (cost=0.25 rows=1) (actual time=0.014..0.014 rows=0.333 loops=12)
  • 커버링 인덱스(인덱스를 활용해 디스크 읽기를 하지 않음)를 이용해 letter_analysis(감정 분석) 테이블을 조회합니다.
    (인덱스는 letter_analysis 테이블의 유니크 키 인덱스(`uk_letter_analysis_letter`)입니다.)
  • 이때, `letter_id=l.letter_id` 조건은 프라이머리 키를 이용하기 때문에 테이블을 읽을 때 1건의 레코드가 보장됩니다. (eq_ref)
    따라서 읽어야 하는 예상 레코드 수는 1건입니다.

(4) 조인 (Nested loop inner join)

(4) -> Nested loop inner join  (cost=1096 rows=406) (actual time=0.42..83.9 rows=4 loops=1)
  • (2)번 테이블(letter)과 (3)번 테이블(letter_analysis)을 조인합니다. (Nested loop inner join)
  • 조인에 사용될 예상 레코드 수는 406건입니다.
  • 실제 조인된 결과 레코드는 4건입니다.

(5) Materialize with deduplication

(5) -> Materialize with deduplication  (cost=1137..1137 rows=406) (actual time=83.9..83.9 rows=4 loops=1)
  • 중복을 제거하고 구체화(Materialize)합니다.
  • 예상되는 구체화 레코드는 406건입니다.
  • 실제 구체화된 레코드는 4건으로 유지됩니다.

(6) 인덱스 조회 단계 (날짜 기반 인덱스 조회 수행)

(6) -> Index lookup on <materialized_subquery> using <auto_distinct_key> (DATE(l.created_at)=cast(letter.created_at as date))  (actual time=8.39..8.39 rows=0.4 loops=10)
  • 구체화된 서브 쿼리(materialized_subquery)와 distinct_key를 이용해 최적화된 읽기를 수행합니다.

(7) 제한 단계

(7) -> Limit: 1 row(s)  (cost=1137..1137 rows=1) (actual time=8.39..8.39 rows=0.4 loops=10)
  • 결과를 1개의 레코드로 제한합니다

(8) 필터 단계 (날짜 기반 필터링)

(8) -> Filter: ((cast(letter.created_at as date) = `<materialized_subquery>`.`DATE(l.created_at)`))  (cost=1137..1137 rows=1) (actual time=8.39..8.39 rows=0.4 loops=10)
  • 서브쿼리의 결과와 현재 letter 테이블의 날짜를 비교합니다.

(9) (1) ~ (8) 과정(서브쿼리)을 처리합니다.

(9) -> Select #2 (subquery in condition; run only once)

 

(10) 메인 쿼리 부분

(10) -> Index lookup on letter using fk_letter_user (user_id=uuid_to_bin('fc700d72-e215-11ef-b651-0242ac120002')), with index condition: (letter.user_id = <cache>(uuid_to_bin('fc700d72-e215-11ef-b651-0242ac120002')))  (cost=954 rows=3654) (actual time=6.51..14.8 rows=3654 loops=1)
  • 다시 편지(letter) 테이블에서 유저 아이디(user_id)로 인덱스 조회를 수행합니다.
    (1)번 과정과 마찬가지로 인덱스 컨디션 푸시다운(index condition pushdown) 기능을 이용해 효율적으로 읽었습니다.
    (인덱스 컨디션 푸시다운: MySQL 엔진에서 스토리지 엔진으로 인덱스를 전달해서 스토리지 엔진이 불필요한 디스크 읽기(I/O)를 줄이는 기능)
  • 읽어야 하는 예상 레코드 수는 3,654건입니다.
  • 실제로 읽은 레코드 수도 3,654건입니다.

(11) 필터 조건

(11) -> Filter: ((letter.created_at between '2024-01-04 00:00:00' and '2024-01-11 00:00:00') and <in_optimizer>(cast(letter.created_at as date),cast(letter.created_at as date) in (select #2) is false))  (cost=954 rows=3654) (actual time=90.8..101 rows=7 loops=1)
  • (9)번 테이블과 (10)번 테이블을 날짜 조건(2024년 1월 4일부터 1월 11일)으로 필터링합니다.
  • 읽어야 하는 예상 레코드 수는 3,654건입니다.
  • 두 테이블에 대해 날짜로 필터링된 레코드 수는 7건입니다.

(12) 정렬

(12)-> Sort: `cast(letter.created_at as date)`, letter.created_at DESC  (cost=954 rows=3654) (actual time=101..101 rows=7 loops=1)
  • letter 테이블의 생성 일자(created_at)를 기준으로 내림차순 정렬을 수행합니다.
  • 정렬되는 예상 레코드 수는 3,654건입니다.
  • 실제 정렬된 레코드 수는 7건입니다. (필터링된 레코드 수와 일치)

(13) 윈도우 집계(window aggregate)

(13)-> Window aggregate: row_number() OVER (PARTITION BY `cast(letter.created_at as date)` ORDER BY letter.created_at desc )   (actual time=101..101 rows=7 loops=1)
  • 날짜별로 `row_number()` 윈도우 함수를 적용합니다.
  • 실제 적용된 레코드 수는 7건입니다.

실행 계획 분석 결과

실행 계획 분석 결과 NOT IN 서브쿼리가 있는 부분은 풀 테이블 스캔이 발생하지 않고, 외래키와 유니크 키 인덱스를 활용해 비교적 빠른 조회를 하고 있습니다.

 

하지만 하나 짚고 넘어가고 싶은 부분이 있는데요, 예상 레코드 수와 실제 레코드 수의 차이가 생각보다 많이 발생한다는 점입니다.

즉, 불필요한 읽기가 많이 발생하는 것을 알 수 있습니다.

필터링된 비율(읽은 레코드 중 11.11%만 사용될 것으로 예상)

실행 계획의 `row`는 읽어드릴 예상 레코드 수를 의미하고, `filtered`는 통계 정보에 따라 읽을 레코드에서 필터링될 비율을 의미합니다.

위의 그림처럼 11.11%로 되어있다면 디스크에서 읽어 들인 레코드 건수는 3,654건이지만 이 중 11.11%에 해당하는 401건만 필터링되어 사용될 것으로 예상됨으로 읽을 수 있습니다.

반대로 말하면, 읽어들인 전체 레코드에서 88.89%만큼 불필요한 레코드를 읽을 것으로 예상한다는 것입니다.

 

현재 보이는 값은 가상 시나리오에 따라 적은 수의 레코드라고 생각될 수 있지만, 만약 대량의 레코드를 읽어야 한다면 성능이 저하될 잠재적인 위험이 있습니다.

고려할만한 최적화 옵션들

불필요한 읽기를 해결하기 위한 방법으로 여러 선택지를 고민해 봤습니다.

❌ 첫 번째 옵션: 쿼리를 두 개로 분리하기

기존 NOT IN 서브쿼리를 2개의 쿼리로 나누는 방법입니다.

1) 분석이 완료된 편지의 날짜 구하기

SELECT DISTINCT DATE(l.created_at)
FROM letter l
  JOIN letter_analysis la ON l.letter_id = la.letter_id
WHERE l.user_id = :userId
  AND l.created_at BETWEEN :start AND :end;

1-1) 첫 번째 쿼리의 필터링 효율

유저 아이디로 필터링하는 쿼리는 동일한 효율을 보인다.

위의 그림은 첫 번째 쿼리의 필터링 효율입니다.

유저 아이디로 필터링하는 부분은 동일한 효율이 예상되는 것을 확인할 수 있습니다. (11.11%)

 

2) 분석 완료된 편지의 날짜로 NOT IN (상수) 조건으로 필터링하기

SELECT *, ROW_NUMBER() OVER (PARTITION BY DATE(created_at) ORDER BY created_at DESC) AS row_num
FROM letter l
WHERE l.user_id = :userId
  AND l.created_at BETWEEN :start AND :end
  AND DATE(l.created_at) NOT IN ('2024-01-04', '2024-01-05', '2024-01-06', '2024-01-07')

 

첫 번째 쿼리의 결과 즉, 분석이 이미 완료된 일자(=데일리 리포트가 생성된 일자)가 NOT IN 조건절에 상수로 들어갑니다.

2-1) 두 번째 쿼리의 필터링 효율

위의 그림은 두 번째 쿼리의 필터링 효율입니다.

이 쿼리 역시 유저 아이디로 필터링을 수행하기 때문에 동일한 효율임을 확인할 수 있습니다. (11.11%)

 

따라서 해당 선택지는 네트워크를 2번 타고 효율은 증가하지 않기 때문에 최적화가 아닌 역효과를 내는 옵션입니다.

 

✅ 두 번째 옵션: 다중 컬럼 인덱스 생성

WHERE 조건절에 필터링될 컬럼들과 순서를 이용해 다중 컬럼 인덱스를 생성하는 방법입니다.

다중 컬럼 인덱스를 생성하면 (유저의 아이디, 편지의 생성 일자)를 하나의 인덱스로 원하는 레코드만 빠르게 읽을 수 있습니다.

 

1) 다중 컬럼 인덱스 생성

create index `idx_userId_createdAt`
    on letter (user_id, created_at);

 

1-1) 실행 계획

위의 결과를 보면 읽을 레코드 수가 3,654건에서 12건으로 줄었고, 필터링 효율도 11.11%에서 100%를 달성했습니다.

전체 실행 계획

실행 계획 전체를 보면 letter 테이블을 조회할 때와 letter 테이블과 letter_analysis 테이블을 조인할 때 모두 `idx_userId_createdAt` 인덱스를 활용해 필요한 레코드만 조회한 것을 확인할 수 있습니다.

 

다중 컬럼 인덱스를 사용할 때 주의할 점을 살펴보며 포스팅을 마치도록 하겠습니다.

 

다중 컬럼 인덱스 사용 시 주의사항

다중 컬럼을 활용하기 위해선 반드시 조건절에 사용되는 컬럼의 순서를 인덱스를 생성할 때 지정한 순서와 동일하게 해야 한다는 것입니다.

이는 선행 컬럼에 대한 조건에 의해 작업 범위가 결정되기 때문인데, 컬럼의 순서가 바뀌면 인덱스를 활용할 수 없기 때문에 순서를 잘 지켜야 합니다.

 

따라서 팀 내 원활한 소통을 위해 다중 컬럼 인덱스를 사용했다면 애플리케이션 코드 상으로 주석을 남기는 등 표시를 해두는 것이 좋다고 생각합니다.

마치며

이번 리팩토링을 통해 유지 보수를 어렵게 했던 문제점 중 하나가 다른 객체의 책임이 섞여 있던 코드였다는 사실을 깨달았습니다.

이를 적절한 책임 재분배를 통해 코드 가독성을 높일 수 있었습니다.

특히 리팩토링 과정에서 재밌었던 부분이 있었는데요, NOT IN 서브쿼리 검증을 위해 시나리오를 만들고 실행 계획 분석을 통해 쿼리 효율성을 검증하는 과정이었습니다. 내가 작성한 쿼리가 효율적인지 확인하는 좋은 경험을 했다고 생각합니다.

 

다음 포스팅에서는 트랜잭션과 외부 API 분리 과정을 다룰 예정입니다.

관심 있으신 분들은 추가되는 링크를 통해 확인해 주세요!

프로젝트 리팩토링 (4) - 책임 재할당과 트랜잭션에서 외부 API 분리하기

 

긴 글 읽어주셔서 감사합니다.

'Project' 카테고리의 다른 글

프로젝트 리팩토링 (5) - HTTP 요청 비동기 처리  (0) 2025.02.07
프로젝트 리팩토링 (4) - 책임 재할당과 트랜잭션에서 외부 API 분리하기  (0) 2025.02.07
프로젝트 리팩토링 (2) - 도메인 모델 리팩토링  (0) 2025.02.02
프로젝트 리팩토링 (1) - 객체 지향 설계  (0) 2025.01.31
모놀리식 아키텍처에서 이벤트 기반으로 비즈니스 로직의 원자성 확보하기 (2)  (1) 2025.01.17

티스토리툴바