Tuesday, January 15, 2019

JPA와 MySQL 환경의 월별 통계 쿼리, DATE_FORMAT 그 이상의 성능 최적화 전략
  /   No comments   

오늘날 데이터는 모든 비즈니스와 서비스의 핵심 동력으로 자리 잡았습니다. 사용자의 행동 패턴을 분석하여 이탈률을 방지하고, 서비스의 병목 지점을 파악하여 성능을 개선하며, 매출 데이터를 기반으로 미래 전략을 수립하는 등 데이터의 활용 가능성은 무궁무진합니다. 이러한 데이터 분석의 가장 기본적인 출발점은 바로 시간 기반의 통계 집계입니다. 특히 월별, 주별, 일별 사용자 활동이나 이벤트 발생 횟수를 집계하는 작업은 대시보드 개발, 리포트 생성 등에서 필수적으로 요구되는 기능입니다.

Java 진영의 표준 ORM 기술인 JPA(Java Persistence API)와 세계에서 가장 널리 사용되는 관계형 데이터베이스인 MySQL을 사용하는 개발 환경은 매우 일반적입니다. 개발자들은 JPA가 제공하는 강력한 추상화와 객체 지향적인 데이터 관리의 편리함 속에서 비즈니스 로직 개발에 집중할 수 있습니다. 하지만 통계 쿼리와 같이 복잡한 집계 작업이 필요한 순간, 순수한 객체 지향적 접근만으로는 한계에 부딪히곤 합니다. 특히 `GROUP BY`를 활용한 월별 데이터 그룹화는 개발자들이 흔히 겪는 난관 중 하나입니다.

예를 들어, 서비스의 사용자 활동 로그를 기록하는 `ActivityLog`라는 테이블이 있다고 가정해 봅시다. 이 테이블에는 로그 ID, 사용자 ID, 활동 내용, 그리고 활동이 발생한 시간을 나타내는 `createdAt` (타입: `TIMESTAMP` 또는 `DATETIME`) 컬럼이 있습니다. 우리의 목표는 월별 활동 로그가 몇 건이나 쌓였는지 집계하는 것입니다. JPA 리포지토리에서 다음과 같은 순진한 JPQL(Java Persistence Query Language)을 작성하면 어떻게 될까요? 

// 잘못된 접근 방식의 예
@Query("SELECT l FROM ActivityLog l GROUP BY l.createdAt")
List<ActivityLog> findMonthlyCounts();

위 쿼리는 전혀 예상과 다른 결과를 반환할 것입니다. `GROUP BY`의 대상인 `createdAt` 컬럼은 '년-월-일-시-분-초' 심지어 밀리초까지 포함하는 매우 정밀한 시간 값을 가지고 있습니다. 따라서 거의 모든 로그의 `createdAt` 값은 고유하며, 그룹화는 사실상 일어나지 않고 모든 로그가 개별적으로 반환될 것입니다. 이는 우리가 원했던 '월별 집계'라는 목표와는 거리가 멉니다.

이 글에서는 이러한 문제를 해결하기 위한 기본적인 접근법부터 시작하여, 실제 운영 환경에서 마주할 수 있는 성능 이슈를 진단하고 이를 해결하기 위한 고급 전략까지 심도 있게 다룰 것입니다. 단순히 MySQL의 `DATE_FORMAT` 함수를 소개하는 것을 넘어, JPA 환경에서 이를 올바르게 사용하는 방법, 네이티브 쿼리와의 비교, 그리고 대용량 데이터를 효과적으로 처리하기 위한 인덱스 최적화 및 비정규화 전략까지 아우르는 포괄적인 가이드를 제시합니다. 이 글을 끝까지 읽으신다면, 여러분은 JPA와 MySQL 환경에서 그 어떤 시간 기반 통계 쿼리도 자신 있게 설계하고 최적화할 수 있는 역량을 갖추게 될 것입니다.

1. 문제의 구체화: 샘플 엔티티와 리포지토리 정의

이론적인 설명에 앞서, 구체적인 코드 예제를 통해 문제를 명확히 정의해 보겠습니다. 사용자 플레이 로그를 저장하는 `PlayLog` 엔티티를 기준으로 설명하겠습니다.

1.1. `PlayLog` 엔티티

사용자가 게임을 한 판 플레이할 때마다 기록이 남는다고 가정합니다. 엔티티는 다음과 같이 정의할 수 있습니다.


import jakarta.persistence.*;
import java.time.LocalDateTime;

@Entity
@Table(name = "play_log")
public class PlayLog {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "user_id", nullable = false)
    private String userId;

    @Column(name = "score", nullable = false)
    private Integer score;

    @Column(name = "regist_date", nullable = false)
    private LocalDateTime registDate;

    // Lombok 등을 사용하거나 수동으로 생성자, Getter 추가
    // protected PlayLog() {}
    // public PlayLog(String userId, Integer score, LocalDateTime registDate) { ... }
    // ... Getters ...
}

여기서 핵심은 `registDate` 필드입니다. JPA 2.2 이상부터는 `java.time.LocalDateTime` 클래스를 별도의 컨버터 없이도 데이터베이스의 `DATETIME` 또는 `TIMESTAMP` 타입과 매핑할 수 있습니다. 이 필드는 로그가 생성된 정확한 시점을 저장하고 있으며, 바로 이 필드를 기준으로 월별 통계를 내는 것이 우리의 목표입니다.

1.2. JPA 리포지토리와 실패하는 첫 시도

Spring Data JPA를 사용한다면 리포지토리는 다음과 같이 간단하게 구성됩니다.


import org.springframework.data.jpa.repository.JpaRepository;

public interface PlayLogRepository extends JpaRepository<PlayLog, Long> {
    // 월별 통계를 위한 메서드를 여기에 추가할 예정
}

이제 앞서 언급했던 잘못된 쿼리를 다시 살펴보겠습니다.


// PlayLogRepository 내부에 작성
@Query("SELECT p.registDate, COUNT(p.id) FROM PlayLog p GROUP BY p.registDate")
List<Object[]> findMonthlyCountsWrong();

만약 `play_log` 테이블에 다음과 같은 데이터가 있다고 가정해 봅시다.


+----+----------+-------+---------------------+
| id | user_id  | score | regist_date         |
+----+----------+-------+---------------------+
| 1  | 'user_A' | 1500  | '2023-10-10 09:30:15' |
| 2  | 'user_B' | 2200  | '2023-10-15 14:00:05' |
| 3  | 'user_A' | 1800  | '2023-10-25 21:45:30' |
| 4  | 'user_C' | 3100  | '2023-11-05 10:10:10' |
| 5  | 'user_B' | 2500  | '2023-11-20 18:00:00' |
+----+----------+-------+---------------------+

위 쿼리의 실행 결과는 `GROUP BY`가 효과적으로 동작하지 않아 각 로우를 별개의 그룹으로 인식하게 되므로, 다음과 같은 형태로 5개의 행이 모두 반환됩니다. COUNT는 항상 1이 될 것입니다.


['2023-10-10T09:30:15', 1]
['2023-10-15T14:00:05', 1]
['2023-10-25T21:45:30', 1]
['2023-11-05T10:10:10', 1]
['2023-11-20T18:00:00', 1]

우리가 진정으로 원하는 결과는 다음과 같습니다.


['2023-10', 3]
['2023-11', 2]

이 차이를 만드는 것이 바로 이 글의 핵심 주제입니다. 문제를 해결하기 위해 데이터베이스의 날짜/시간 함수를 JPA에서 어떻게 활용할 수 있는지 알아보겠습니다.

2. 해결책 1: JPQL과 데이터베이스 함수(FUNCTION)의 만남

가장 직관적이고 표준적인 해결책은 JPQL 내에서 데이터베이스 고유의 함수를 호출하는 것입니다. JPA 2.1부터는 `FUNCTION()` 이라는 구문을 통해 JPQL에서 표준적으로 지원하지 않는 데이터베이스 함수를 호출할 수 있는 길이 열렸습니다.

2.1. MySQL의 `DATE_FORMAT` 함수 이해하기

문제를 해결해 줄 핵심 열쇠는 MySQL의 `DATE_FORMAT()` 함수입니다. 이 함수는 날짜/시간 값을 원하는 형식의 문자열로 변환해 줍니다.

  • 문법: `DATE_FORMAT(date, format)`
  • `date`: 포맷을 변경할 날짜 또는 날짜/시간 값 (예: `regist_date` 컬럼)
  • `format`: 출력 형식을 지정하는 포맷 문자열. 주요 포맷 지정자는 다음과 같습니다.
    • %Y: 4자리 연도 (예: 2023)
    • %y: 2자리 연도 (예: 23)
    • %m: 2자리 월 (01 ~ 12)
    • %M: 월 이름 (January ~ December)
    • %c: 1자리 또는 2자리 월 (1 ~ 12)
    • %d: 2자리 일 (01 ~ 31)
    • %H: 24시간 형식의 시 (00 ~ 23)
    • %i: 2자리 분 (00 ~ 59)
    • %s: 2자리 초 (00 ~ 59)

우리의 목표인 '월별' 집계를 위해서는 '년-월' 정보만 필요하므로, `'%Y-%m'` 포맷을 사용하면 됩니다. 예를 들어 `DATE_FORMAT('2023-10-10 09:30:15', '%Y-%m')`을 실행하면 결과는 문자열 `'2023-10'`이 됩니다. 이 변환된 문자열을 기준으로 `GROUP BY`를 수행하면 동일한 년/월을 가진 데이터들이 하나의 그룹으로 묶이게 됩니다.

2.2. JPQL에 `FUNCTION` 적용하기

JPA의 `FUNCTION` 키워드를 사용하면 `DATE_FORMAT` 함수를 JPQL 쿼리 내에 자연스럽게 녹여낼 수 있습니다. 


// PlayLogRepository 내부에 작성
@Query("SELECT FUNCTION('DATE_FORMAT', p.registDate, '%Y-%m'), COUNT(p.id) FROM PlayLog p GROUP BY FUNCTION('DATE_FORMAT', p.registDate, '%Y-%m') ORDER BY 1")
List<Object[]> findMonthlyCounts();

이 쿼리는 다음과 같이 동작합니다.

  1. FROM PlayLog p: `play_log` 테이블의 모든 데이터를 조회 대상으로 삼습니다.
  2. FUNCTION('DATE_FORMAT', p.registDate, '%Y-%m'): 각 로우의 `registDate` 값을 'YYYY-MM' 형식의 문자열로 변환합니다.
  3. GROUP BY FUNCTION('DATE_FORMAT', p.registDate, '%Y-%m'): 위에서 변환된 'YYYY-MM' 문자열을 기준으로 그룹화를 수행합니다. '2023-10'은 '2023-10'끼리, '2023-11'은 '2023-11'끼리 묶입니다.
  4. SELECT ..., COUNT(p.id): 각 그룹별로 ID의 개수를 셉니다. (COUNT(p), COUNT(*) 등도 유사하게 동작합니다.)
  5. ORDER BY 1: 첫 번째 SELECT절 항목(즉, 'YYYY-MM' 문자열)을 기준으로 결과를 정렬하여 시간 순서대로 받아볼 수 있습니다.

이 쿼리를 실행하면, 우리가 원했던 `List<Object[]>` 형태의 결과를 얻게 됩니다. 각 `Object[]` 배열의 첫 번째 원소는 `'2023-10'`과 같은 월 정보(String)이고, 두 번째 원소는 해당 월의 로그 수(Long)입니다.

2.3. 결과를 DTO(Data Transfer Object)로 직접 매핑하기

List<Object[]>는 타입 세이프하지 않고 가독성이 떨어지며, 사용하기 불편합니다. 서비스 계층에서 이 데이터를 사용하려면 매번 인덱스로 접근하여 형 변환을 해야 합니다. 이는 번거롭고 오류 발생 가능성을 높입니다.

JPA는 이런 불편함을 해소하기 위해 생성자 표현식(Constructor Expression) 기능을 제공합니다. 쿼리 결과를 DTO 객체에 직접 매핑하여 `List` 형태로 바로 받을 수 있게 해주는 강력한 기능입니다.

2.3.1. 통계 결과 DTO 생성

먼저 쿼리 결과를 담을 DTO 클래스를 만듭니다. 


// 통계 결과를 담을 DTO 클래스
public class MonthlyPlayLogCount {

    private final String month; // 예: "2023-10"
    private final Long count;   // 예: 3

    public MonthlyPlayLogCount(String month, Long count) {
        this.month = month;
        this.count = count;
    }

    // Getters
    public String getMonth() {
        return month;
    }

    public Long getCount() {
        return count;
    }
}
중요: JPA 생성자 표현식을 사용하려면 DTO 클래스에 쿼리 SELECT 절의 순서 및 타입과 일치하는 파라미터를 갖는 생성자가 반드시 존재해야 합니다.

2.3.2. 생성자 표현식을 사용한 리포지토리 메서드

이제 리포지토리 쿼리를 수정하여 `new` 키워드와 함께 DTO의 전체 패키지 경로를 명시해 줍니다.


import your.package.path.to.dto.MonthlyPlayLogCount; // 실제 DTO 경로

// ... PlayLogRepository 내부
@Query("SELECT new your.package.path.to.dto.MonthlyPlayLogCount(FUNCTION('DATE_FORMAT', p.registDate, '%Y-%m'), COUNT(p.id)) " +
       "FROM PlayLog p " +
       "GROUP BY FUNCTION('DATE_FORMAT', p.registDate, '%Y-%m') " +
       "ORDER BY month")
List<MonthlyPlayLogCount> findMonthlyCountsAsDto();

여기서 몇 가지 개선점이 있습니다. SELECT 절에서 DTO를 생성하며 필드명을 `month`, `count`로 정했으므로 `ORDER BY` 절에서 인덱스 번호 `1` 대신 `month`라는 명시적인 이름을 사용할 수 있어 가독성이 향상되었습니다. (이는 Hibernate 구현체에 따라 약간씩 다를 수 있으나 일반적으로 동작합니다. 더 확실하게 하려면 `GROUP BY`절에도 별칭을 사용하거나 원본 함수를 그대로 쓰는 것이 좋습니다.)

이제 서비스 계층에서는 `List<MonthlyPlayLogCount>`를 직접 반환받아 타입 세이프하고 직관적인 코드를 작성할 수 있습니다. 이것이 JPA를 사용하여 복잡한 쿼리 결과를 처리하는 가장 현대적이고 권장되는 방식입니다.

3. 해결책 2: 네이티브 쿼리(Native Query) 활용

때로는 JPQL의 `FUNCTION` 키워드만으로는 해결하기 어려운, 더 복잡하거나 데이터베이스에 매우 특화된 기능을 사용해야 할 때가 있습니다. 예를 들어 MySQL에서만 제공하는 특정 튜닝 힌트를 사용하거나, CTE(Common Table Expressions)와 같은 고급 SQL 구문을 사용해야 하는 경우입니다. 이럴 때를 위해 JPA는 네이티브 SQL 쿼리를 직접 실행할 수 있는 통로를 열어두었습니다.

3.1. 네이티브 쿼리 사용 시기와 장단점

  • 장점:
    • 데이터베이스 기능 완전 활용: 특정 데이터베이스가 제공하는 모든 함수, 문법, 최적화 기능을 100% 사용할 수 있습니다.
    • 기존 SQL 재사용: 이미 검증된 복잡한 SQL 쿼리가 있다면 JPA 환경으로 쉽게 마이그레이션할 수 있습니다.
    • 잠재적 성능 우위: JPQL이 특정 상황에서 비효율적인 SQL로 변환될 가능성이 있는 반면, 네이티브 쿼리는 개발자가 의도한 최적의 SQL을 그대로 실행하도록 보장합니다.
  • 단점:
    • 데이터베이스 종속성: 쿼리가 특정 데이터베이스(이 경우 MySQL)에 종속됩니다. 만약 나중에 데이터베이스를 Oracle이나 PostgreSQL 등으로 변경하게 되면 해당 쿼리는 모두 수정해야 합니다. 이는 JPA를 사용하는 가장 큰 이유 중 하나인 '데이터베이스 독립성'을 해칩니다.
    • 타입 안정성 저하: 쿼리가 문자열 형태로 존재하므로 컴파일 시점에 오타나 문법 오류를 잡을 수 없습니다. 런타임 시점에서야 오류가 발견됩니다.
    • 엔티티와의 매핑 복잡성: 결과를 엔티티가 아닌 DTO로 매핑하려면 별도의 설정이 필요할 수 있습니다.

3.2. 기본 네이티브 쿼리 작성

네이티브 쿼리를 사용하려면 `@Query` 어노테이션에 `nativeQuery = true` 속성을 추가하면 됩니다.


// PlayLogRepository 내부
@Query(value = "SELECT DATE_FORMAT(p.regist_date, '%Y-%m') as month, COUNT(p.id) as count " +
               "FROM play_log p " +
               "GROUP BY month " +
               "ORDER BY month",
       nativeQuery = true)
List<Object[]> findMonthlyCountsNative();

주의할 점:

  • 네이티브 쿼리에서는 JPQL과 달리 엔티티 이름(`PlayLog`)과 필드명(`registDate`)이 아닌, 실제 테이블 이름(`play_log`)과 컬럼명(`regist_date`)을 사용해야 합니다.
  • `as` 키워드를 사용한 컬럼 별칭(alias)이 자유롭게 지원됩니다. (`month`, `count` 등)

3.3. 네이티브 쿼리 결과를 DTO로 매핑하기: `SqlResultSetMapping`

네이티브 쿼리에서도 DTO 매핑은 필수적입니다. JPQL의 생성자 표현식처럼 간단하지는 않지만, JPA는 `@SqlResultSetMapping`이라는 표준적인 방법을 제공합니다. 이 방법은 다소 장황하지만, 매우 복잡한 쿼리 결과도 체계적으로 매핑할 수 있게 해줍니다.

먼저, 매핑 정의를 엔티티 클래스 상단이나 별도의 설정 파일에 추가합니다.


// PlayLog 엔티티 상단에 추가
@SqlResultSetMapping(
    name = "MonthlyPlayLogCountMapping",
    classes = @ConstructorResult(
        targetClass = MonthlyPlayLogCount.class,
        columns = {
            @ColumnResult(name = "month", type = String.class),
            @ColumnResult(name = "count", type = Long.class)
        }
    )
)
@Entity
// ... PlayLog 클래스 정의
public class PlayLog { ... }

해석해보자면:

  • `@SqlResultSetMapping`: 네이티브 쿼리 결과 집합의 매핑 규칙을 정의합니다. `name` 속성으로 이 매핑의 고유한 이름을 지정합니다.
  • `@ConstructorResult`: 결과를 DTO의 생성자를 통해 매핑하겠다고 선언합니다. `targetClass`에 우리가 만든 `MonthlyPlayLogCount.class`를 지정합니다.
  • `@ColumnResult`: 네이티브 쿼리의 SELECT 절에 있는 각 컬럼(또는 별칭)을 생성자 파라미터에 어떻게 매핑할지 정의합니다. `name`은 SQL 쿼리에서의 컬럼 별칭과 일치해야 하고, `type`은 해당 파라미터의 Java 타입을 지정합니다.

이제 이 매핑을 사용하도록 리포지토리 메서드를 수정합니다.


// PlayLogRepository 내부
// `nativeQuery = true` 와 함께 `resultSetMapping` 속성을 추가한다.
@Query(value = "SELECT DATE_FORMAT(p.regist_date, '%Y-%m') as month, COUNT(p.id) as count " +
               "FROM play_log p " +
               "GROUP BY month " +
               "ORDER BY month",
       nativeQuery = true,
       resultSetMapping = "MonthlyPlayLogCountMapping")
List<MonthlyPlayLogCount> findMonthlyCountsNativeAsDto();

이렇게 설정하면 네이티브 쿼리의 실행 결과가 `MonthlyPlayLogCountMapping` 규칙에 따라 `MonthlyPlayLogCount` DTO 객체 리스트로 깔끔하게 변환되어 반환됩니다.

Spring Data JPA 2.x 이상에서는 인터페이스 기반의 DTO 프로젝션을 지원하여 `@SqlResultSetMapping` 없이 더 간결하게 DTO 매핑을 할 수도 있습니다. 하지만 `@SqlResultSetMapping`은 JPA 표준 기능이므로 다른 구현체에서도 동일하게 동작한다는 장점이 있습니다.

4. 성능 함정과 최적화 전략: `DATE_FORMAT`의 그늘

월별 통계 쿼리를 성공적으로 작성했지만, 이야기는 여기서 끝나지 않습니다. 데이터가 수백만, 수천만 건으로 늘어나는 실제 운영 환경에서는 쿼리 성능이 새로운 과제로 떠오릅니다. `DATE_FORMAT` 함수는 편리하지만, 잘못 사용하면 심각한 성능 저하를 유발하는 함정을 가지고 있습니다.

4.1. `WHERE` 절에서의 함수 사용: 인덱스를 무력화하는 주범

가장 흔하고 치명적인 실수는 `WHERE` 절의 검색 조건에 `DATE_FORMAT`과 같은 함수를 사용하는 것입니다.

예를 들어, "2023년 10월" 한 달간의 로그만 집계하고 싶어서 다음과 같은 쿼리를 작성했다고 가정해봅시다.


-- 성능에 매우 안좋은 쿼리의 예
SELECT DATE_FORMAT(p.regist_date, '%Y-%m') as month, COUNT(p.id)
FROM play_log p
WHERE DATE_FORMAT(p.regist_date, '%Y-%m') = '2023-10'
GROUP BY month;

이 쿼리는 왜 나쁠까요? `play_log` 테이블의 `regist_date` 컬럼에 인덱스가 걸려있다고 하더라도, MySQL 옵티마이저는 이 인덱스를 효율적으로 사용할 수 없습니다. 데이터베이스 입장에서 `DATE_FORMAT(p.regist_date, ...)`는 `p.regist_date` 컬럼 값 자체를 가공한 완전히 새로운 값입니다. 따라서 이 조건을 만족하는 데이터를 찾으려면 테이블의 모든 행을 하나씩 읽어(`Full Table Scan`) `DATE_FORMAT` 함수를 적용해보고, 그 결과가 '2023-10'과 일치하는지 비교해야 합니다. 데이터가 1억 건이라면 1억 번의 함수 호출과 비교 연산이 발생하는 끔찍한 상황이 벌어집니다.

4.2. SARGable 쿼리: 인덱스를 춤추게 하라

성능을 최적화하는 핵심은 쿼리를 SARGable(Search ARGument-able) 형태로 만드는 것입니다. SARGable 쿼리란 인덱스를 효율적으로 활용할 수 있는 형태의 쿼리를 의미합니다. 가장 대표적인 방법은 인덱스가 걸린 컬럼을 가공하지 않고 그대로 사용하는 것입니다.

위의 비효율적인 쿼리는 다음과 같이 변경해야 합니다.


-- 성능이 최적화된 SARGable 쿼리
SELECT DATE_FORMAT(p.regist_date, '%Y-%m') as month, COUNT(p.id)
FROM play_log p
WHERE p.regist_date >= '2023-10-01 00:00:00'
  AND p.regist_date < '2023-11-01 00:00:00'
GROUP BY month;

이 쿼리는 `WHERE` 절에서 `regist_date` 컬럼을 아무런 가공 없이 값과 직접 비교합니다. `BETWEEN`을 사용하지 않고 `... < '2023-11-01'` 와 같이 다음 달 시작일 미만으로 조건을 잡는 이유는, `BETWEEN '2023-10-01' AND '2023-10-31'` 로 작성할 경우 `2023-10-31`의 0시 0분 0초까지만 포함하고 그 이후 시간(예: `2023-10-31 23:59:59`)이 누락될 수 있기 때문입니다.

이렇게 작성하면 MySQL 옵티마이저는 `regist_date` 컬럼의 B-Tree 인덱스를 타고 들어가 조건에 맞는 데이터 범위를 매우 빠르게 스캔(Index Range Scan)할 수 있습니다. `GROUP BY`는 필터링된 소량의 데이터에 대해서만 수행되므로 전체적인 쿼리 성능이 극적으로 향상됩니다.

이를 JPA 리포지토리 메서드로 구현하면 다음과 같습니다.


import your.package.path.to.dto.MonthlyPlayLogCount;
import java.time.LocalDateTime;

// ... PlayLogRepository 내부
@Query("SELECT new your.package.path.to.dto.MonthlyPlayLogCount(FUNCTION('DATE_FORMAT', p.registDate, '%Y-%m'), COUNT(p.id)) " +
       "FROM PlayLog p " +
       "WHERE p.registDate >= :startDate AND p.registDate < :endDate " + // SARGable 조건
       "GROUP BY FUNCTION('DATE_FORMAT', p.registDate, '%Y-%m') " +
       "ORDER BY 1")
List<MonthlyPlayLogCount> findMonthlyCountsInRange(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);

서비스 계층에서는 `startDate`에 '2023-10-01T00:00:00', `endDate`에 '2023-11-01T00:00:00'을 파라미터로 넘겨주면 됩니다.

4.3. 대용량 데이터를 위한 궁극의 전략: 비정규화(Summary Table)

로그 데이터가 수십억 건에 달하고, 통계 대시보드에서 매번 월별/주별/일별 통계를 실시간으로 조회해야 하는 극한의 상황이라면 `GROUP BY` 자체도 부담이 될 수 있습니다. 이럴 때는 조회 시점의 연산 부담을 줄이기 위해 데이터를 미리 집계해두는 비정규화(Denormalization) 전략을 고려해야 합니다.

바로 요약 테이블(Summary Table)을 도입하는 것입니다.

4.3.1. 요약 테이블 설계

월별 통계를 위한 요약 테이블 `monthly_play_log_summary`를 다음과 같이 설계할 수 있습니다.


CREATE TABLE monthly_play_log_summary (
    `year_month` VARCHAR(7) NOT NULL, -- 예: '2023-10', Primary Key
    `play_count` BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (`year_month`)
);

4.3.2. 데이터 집계 및 동기화

이 요약 테이블의 데이터는 주기적으로 갱신되어야 합니다. 방법은 여러 가지가 있습니다.

  1. 배치(Batch) 처리: Spring Batch와 같은 프레임워크를 사용하여 매일 새벽(또는 매시간)에 전날(또는 전 시간)의 원본 로그(`play_log`)를 읽어와 요약 테이블에 집계 결과를 반영(UPSERT: UPDATE or INSERT)하는 방식입니다. 가장 안정적이고 시스템 부하를 분산시킬 수 있는 일반적인 방법입니다.
  2. 트리거(Trigger): `play_log` 테이블에 `INSERT`가 발생할 때마다 요약 테이블의 `play_count`를 1씩 증가시키는 데이터베이스 트리거를 사용하는 방식입니다. 실시간 동기화가 가능하지만, 원본 테이블의 쓰기 작업에 오버헤드를 유발하고 로직이 복잡해지면 관리가 어려워질 수 있습니다.
  3. 이벤트 기반 아키텍처: 로그가 생성될 때마다 Kafka와 같은 메시지 큐로 이벤트를 발행하고, 별도의 컨슈머(Consumer) 애플리케이션이 이 이벤트를 구독하여 요약 테이블을 갱신하는 방식입니다. MSA(Microservice Architecture) 환경에 적합한 확장성 높은 구조입니다.

배치 처리가 가장 보편적이므로, 배치 작업에서 수행할 쿼리는 대략 다음과 같은 형태가 될 것입니다.


INSERT INTO monthly_play_log_summary (year_month, play_count)
SELECT DATE_FORMAT(regist_date, '%Y-%m'), COUNT(*)
FROM play_log
WHERE regist_date >= '어제 시작 시간' AND regist_date < '오늘 시작 시간' -- 어제 하루치 데이터만 대상으로
GROUP BY DATE_FORMAT(regist_date, '%Y-%m')
ON DUPLICATE KEY UPDATE play_count = play_count + VALUES(play_count);

MySQL의 `ON DUPLICATE KEY UPDATE` 구문을 활용하여, `year_month` 키가 이미 존재하면 기존 `play_count`에 새로 집계된 카운트를 더하고, 없으면 새로 삽입합니다.

이제 대시보드에서 월별 통계를 조회하는 쿼리는 거대한 원본 `play_log` 테이블을 건드리지 않고, 매우 작고 가벼운 `monthly_play_log_summary` 테이블을 단순 조회하면 됩니다. `GROUP BY`도 필요 없습니다.


-- 엄청나게 빨라진 조회 쿼리
SELECT year_month, play_count FROM monthly_play_log_summary WHERE year_month BETWEEN '2023-01' AND '2023-12';

이는 데이터베이스 부하를 획기적으로 줄여주며, 아무리 사용자가 많이 조회해도 시스템에 거의 영향을 주지 않는 안정적인 통계 서비스를 제공할 수 있게 해줍니다.

5. 결론: 문제에 맞는 올바른 도구 선택하기

JPA와 MySQL 환경에서 월별 로그 통계를 집계하는 간단해 보이는 요구사항에서 시작하여, 우리는 여러 단계의 깊이 있는 기술적 여정을 거쳤습니다.

처음에는 `LocalDateTime`을 직접 `GROUP BY` 할 수 없다는 문제에 직면했고, MySQL의 `DATE_FORMAT` 함수와 JPA의 `FUNCTION()` 키워드를 조합하여 문제를 해결했습니다. 나아가 DTO 생성자 표현식을 통해 조회 결과를 타입 세이프하고 사용하기 편한 객체로 매핑하는 실용적인 기법을 배웠습니다.

그 후, 더 복잡한 요구사항이나 데이터베이스 종속적인 기능이 필요할 때를 대비해 네이티브 쿼리와 `@SqlResultSetMapping`을 활용하는 방법을 익혔습니다. 이는 유연성을 제공하지만 데이터베이스 독립성이라는 JPA의 장점을 일부 희생해야 함을 인지해야 합니다.

가장 중요한 부분은 성능이었습니다. `WHERE` 절에서 인덱싱된 컬럼에 함수를 적용하는 것이 왜 치명적인지를 이해하고, 인덱스를 온전히 활용하는 SARGable 쿼리로 전환하는 방법을 습득했습니다. 이는 대용량 데이터 환경에서 반드시 지켜야 할 철칙과도 같습니다.

마지막으로, 실시간 조회가 빈번하고 데이터 규모가 거대한 시스템을 위해 조회 시점의 부하를 원천적으로 제거하는 요약 테이블(Summary Table) 전략을 살펴보았습니다. 이는 시스템 아키텍처 수준의 최적화로, 단순한 쿼리 튜닝을 넘어선 궁극적인 해결책이 될 수 있습니다.

결론적으로, '최고의' 단 하나의 방법은 없습니다. 데이터의 규모, 갱신 주기, 조회 빈도, 시스템의 복잡성 등 주어진 상황과 제약 조건에 따라 가장 적합한 전략을 선택하는 것이 중요합니다. 개발자는 JPQL의 편리함부터 네이티브 쿼리의 유연함, 그리고 요약 테이블의 구조적 최적화까지 다양한 도구를 자신의 무기 상자에 갖추고 있어야 합니다. 이 글에서 다룬 다채로운 해법들이 여러분이 마주할 데이터 집계와 통계 처리 문제에 대한 든든한 이정표가 되기를 바랍니다.


0 개의 댓글:

Post a Comment