트러블슈팅: queryDSL 중복집계, 비정상적으로 큰 count 문제(with only_full_group_by)

MySQL의 ONLY_FULL_GROUP_BY모드와(디폴트 설정),

JPA/QueryDSL에서 아이템별 집계를 구현할 때 발생한 문제(groupby 중복 행, count 합계 뻥튀기)

해결 과정을 정리한 문서입니다.

 

 

1. 문제 상황

JPQLQuery<MarketListResponseDto> query = queryFactory
    .select(new QMarketListResponseDto(
        market.item.id,
        market.item.name,
        JPAExpressions
            .select(market.amount.sum().coalesce(0))
            .from(market)
            .where(market.item.id.eq(item.id)),
        market.price.min().coalesce(0L),
        JPAExpressions
            .select(trade.id.count().coalesce(0L))
            .from(trade)
            .where(trade.market.item.id.eq(item.id))
        ))
        .from(market)
        .leftJoin(trade).on(market.id.eq(trade.market.id))
        .leftJoin(item).on(market.item.id.eq(item.id))
        .where(builder)
        .groupBy(market.id, market.item.id, market.item.name)
        .orderBy(determineSorting(sortBy, sortDirection, market, trade))
        .offset(pageable.getOffset())
        .limit(pageable.getPageSize());

거래소 메인 페이지를 구성하는 쿼리를 개발할 때 발생한 문제입니다.

 

Market 테이블에 동일한 item_id를 갖는 레코드가 여러 개 존재합니다.

이를 "아이템 별로 합산(market.amount 합)" 하고 "가장 낮은 가격(minPrice)"을 구하며, 연관된 Trade 테이블에서의 "거래 수(trade.id.count())"도 구해야 합니다.

 

기존 쿼리는 groupBy(market.id, market.item.id)처럼 market.id까지 포함해서 groupBy했기 때문에,

동일한 itemId여도 market레코드 별로 나뉘어 여러 줄이 결과로 나왔습니다.(위 사진 참조)

 

MySQL의 ONLY_FULL_GROUP_BY 모드 때문에 select절의 컬럼이 groupBy에 없거나 집계함수에 안 묶이면 에러가 발생합니다.

그냥 해당 모드를 비활성화하면 되지 않을까 하였지만,

전역 설정이나 세션 설정을 바꿔야 하고, 커넥션 풀 환경에서 예측 불가능한 사이드 이펙트가 생길 수 있다 판단하여서

DB세팅을 건드리지 않기로 했고, 결국 쿼리를 정석대로 고쳐야 했습니다.

 

 

2. 분석

ONLY_FULL_GROUP_BY모드를 해소할 수 있는 방법은 두가지가 있습니다.

1. select절에 있는 단순 컬럼들은 모두 groupBy절에 있어야 하거나,

2. SUM, MIN 등의 집계함수에 감싸져야 합니다.

 

또한 itemId별로 나뉘어야 하기에, groupBy(item.id, item.name)만 사용해야 합니다.

 

따라서 저는 1.단순 컬럼을 모두 집계함수에 감싸며, 2.groupBy에 market.id가 들어가는 상황을 피해야 합니다.

 

 

3. 해결 과정

JPQLQuery<MarketListResponseDto> query = queryFactory
    .select(new QMarketListResponseDto(
        //단순 컬럼
        item.id,
        item.name,
        //집계 컬럼
        market.amount.sum().coalesce(0),
        market.price.min().coalesce(0L),
        trade.id.countDistinct().coalesce(0L)
    ))
        .from(market)
        .leftJoin(trade).on(trade.market.eq(market))
        .leftJoin(market.item, item)
        .where(builder)
        //단순 컬럼 기준으로만 묶기
        .groupBy(item.id, item.name)
        .orderBy(determineSorting(sortBy, sortDirection))
        .offset(pageable.getOffset())
        .limit(pageable.getPageSize());

1. 쿼리dsl에서 market.id를 groupBy에서 제거했습니다. item.id와 item.name만 대상으로 설정합니다.

2. 기존의 단순 컬럼(amount, count)를 집계함수로 변경하여 select합니다.

3. 정렬 시에도 집계함수를 사용해야 합니다.

 

하지만 또다른 문제가 있었는데요, 

 

 

4. 또다른 문제

이번에는 totalAmount의 양이 뻥튀기가 되는 문제가 발생했습니다. 내부적으로 중복되어 불러와지는 데이터가 있다는 뜻입니다.

집계 쿼리에서 market -> trade로 join하여 중복 데이터들이 sum()에 반영되기 때문입니다.

 

market 한 건에 trade가 여러 건 딸려 있을 때(일대다 관계일 때) join결과가 market 레코드 * trade 개수만큼 중복되어 나타나고,

그 뒤 SUM(market.amount)을 하여 각 market의 amount가 중복으로 더해지는 문제가 발생하였습니다.

JPQLQuery<MarketListResponseDto> query = queryFactory
    .select(new QMarketListResponseDto(
        item.id,
        item.name,
        market.amount.sum().coalesce(0),
        market.price.min().coalesce(0L),
        //tradeCount는 "서브쿼리"로 따로 셈
        JPAExpressions
            .select(trade.id.countDistinct())
            .from(trade)
            .where(trade.market.item.id.eq(item.id))
    ))
    .from(market)
    .join(market.item, item)
    .where(builder)
    .groupBy(item.id, item.name)
    .orderBy(determineSorting(sortBy, sortDirection))
    .offset(pageable.getOffset())
    .limit(pageable.getPageSize());

해결 방법은 간단합니다. trade를 직접 join하지 않고, 서브쿼리로 tradeCount만 구하면 됩니다.

이러면 market->trade join을 하지 않아서 중복데이터가 발생하지 않습니다.

 

만약에 trade정보가 더 필요하게 되면 어떻게 하면 될까요? 그렇다고 해도 집계만큼은 market-item만 묶고, 나머지 정보는 또다른 서브쿼리로 가져오는 것이 깔끔할 것입니다. 하지만 성능에 오버헤드가 발생할 수 있을테니 잘 사용해야겠죠

 

 

5. 최종 결론

 

1. 특정 엔티티 별 집계를 하기 위해선 groupBy스코프를 잘 설정해야 합니다.

 

2. 집계함수(SUM, MIN, COUNT 등)로 묶이면 ONLY_FULL_GROUP_BY 모드에서 에러가 나지 않습니다.

 

3. 중복 데이터 문제로 인해 합산결과가 비정상적으로 늘어날경우, JOIN을 빼고 해당 정보는 서브쿼리로 처리합니다.

 

4. DB 설정을 굳이 바꾸지 않고, 쿼리를 정석으로 고쳐서 문제를 해결할 수 있었습니다.

 

이를 통해 중복 없이 

1. 아이템별 합계

2. 최소값

3. 거래 카운트

를 한 줄로 뽑으면서, ONLY_FULL_GROUP_BY 모드에서도 정상 동작하고,

totalAmount가 실제와 다르게 뻥튀기 되는 문제도 해결할 수 있었습니다.