집계함수 쿼리 개선

appti·2024년 2월 20일
0

개선

목록 보기
1/4

서론

팀 프로젝트에서 채팅방 목록 조회 시 각 채팅방마다 마지막으로 작성한 메세지를 출력하라는 요구사항이 추가되었습니다.

채팅 관련 기능을 담당하시던 팀원분께서 다음과 같이 코드를 작성해주셨습니다.

public List<ChatRoomAndMessageAndImageDto> findAllChatRoomInfoByUserIdOrderByLastMessage(final Long userId) {
    final List<ChatRoomAndMessageAndImageQueryProjectionDto> unsortedDtos =
            queryFactory.select(new QChatRoomAndMessageAndImageQueryProjectionDto(chatRoom, message, auctionImage))
                        .from(chatRoom)
                        .leftJoin(chatRoom.buyer).fetchJoin()
                        .leftJoin(chatRoom.auction, auction).fetchJoin()
                        .leftJoin(auction.seller).fetchJoin()
                        .leftJoin(auctionImage).on(auctionImage.id.eq(
                                JPAExpressions
                                        .select(auctionImage.id.min())
                                        .from(auctionImage)
                                        .where(auctionImage.auction.id.eq(auction.id))
                                        .groupBy(auctionImage.auction.id)
                        )).fetchJoin()
                        .leftJoin(auction.lastBid).fetchJoin()
                        .leftJoin(message).on(message.id.eq(
                                JPAExpressions
                                        .select(message.id.max())
                                        .from(message)
                                        .where(message.chatRoom.id.eq(chatRoom.id))
                                        .groupBy(message.chatRoom.id)
                        )).fetchJoin()
                        .where(isSellerOrWinner(userId))
                        .fetch();

    return sortByLastMessageIdDesc(unsortedDtos);
}

실행되는 쿼리는 다음과 같습니다.

select
    chat_room.*, 
    auction.*,
    auction_image.*,
    last_bid.*,
    seller.*,
    buyer.*,
    message.*,
from chat_room chat_room
left join users buyer on buyer.id = chat_room.buyer_id
left join auction auction on auction.id = chat_room.auction_id
left join users seller on seller.id = auction.seller_id
left join auction_image auction_image on auction_image.id = (
        select min(sub_auction_image.id)
        from auction_image sub_auction_image
        where sub_auction_image.auction_id = chat_room.auction_id
        group by sub_auction_image.auction_id
)
left join bid last_bid on last_bid.id = auction.last_bid_id
left join message message on message.id = (
        select max(sub_message.id)
        from message sub_message
        where sub_message.chat_room_id=chat_room.id
        group by sub_message.chat_room_id
)
where auction.seller_id = 3 or chat_room.buyer_id = 3;

문제 상황

쿼리를 분석한 결과, 다음과 같은 쿼리에서 문제점을 발견할 수 있었습니다.

select min(sub_auction_image.id)
from auction_image sub_auction_image
where sub_auction_image.auction_id = chat_room.auction_id
group by sub_auction_image.auction_id

select max(sub_message.id)
from message sub_message
where sub_message.chat_room_id=chat_room.id
group by sub_message.chat_room_id

공통점은 집계 함수를 사용하고 있다는 점, PK만을 조회하고 있다는 점, 집계 함수 처리를 위해 GROUP BY를 사용하고 있다는 점이었습니다.

위 두 개의 쿼리를 앞으로 다음과 같이 간략화해서 표현하겠습니다.

-- 지정한 채팅방(5)에서 최근에 보낸 메세지 id를 조회하는 쿼리
SELECT MAX(id)
FROM message 
WHERE chat_room_id = 5 
GROUP BY chat_room_id;

-- 실행 결과
+---------+
| MAX(id) |
+---------+
|      17 |
+---------+

쿼리 분석

EXPLAIN SELECT MAX(id)
FROM message 
WHERE chat_room_id = 5 
GROUP BY chat_room_id;

+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | message | NULL       | range | fk_message_chat_room | fk_message_chat_room | 8       | NULL |    1 |   100.00 | Using where; Using index for group-by |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+------+----------+---------------------------------------+

다음과 같이 실행된 것을 확인했습니다.

  1. WHERE절로 FK를 조건으로 필터링
  2. 필터링 결과를 토대로 GROUP BY
  3. MAX() 집계 함수를 통해 최대 값 조회

현재 쿼리의 특징은 다음과 같습니다.

  • 단 하나의 필드에 대해서 집계 함수를 사용하고 있음
  • 클러스터링 인덱스인 PK에 대해 MIN(), MAX() 집계 함수를 사용하고 있음

GROUP BY는 특정 기준에 따라 데이터를 그룹화하는 기능입니다.

그룹화한 데이터는 집계 함수와 같은 식으로 따로 처리가 가능합니다.
여기서 필드 하나만을 쓰고 있기 때문에, 굳이 그룹화할 필요가 없이 바로 사용이 가능합니다.

또한, 클러스터링 인덱스에 대해 MIN(), MAX()를 수행하고 있다는 점도 성능 개선할 수 있는 부분이라고 생각했습니다.

클러스터링 인덱스는 논클러스터링 인덱스와는 다르게 데이터 엔트리와 데이터 레코드의 순서가 일치합니다.

그러므로 이 클러스터링 인덱스에 대해 MIN(), MAX()를 사용하게 된다면 즉시 맨 첫 번째 데이터 혹은 맨 마지막 데이터를 접근하면 되기 때문에 추가적인 연산 없이 처리가 가능합니다.

이는 즉 필드의 모든 데이터를 스캔할 필요가 없다는 의미입니다.

그러나 GROUP BY를 수행한다면 이를 그룹화하기 위해서 모든 데이터를 스캔하게 됩니다.
그러므로 굳이 GROUP BY를 쓸 필요가 없으며, 오히려 GROUP BY를 사용해 효율이 더 안좋아졌다고 추측했습니다.

쿼리 개선

다음과 같이 GROUP BY를 삭제했습니다.

-- 지정한 채팅방(5)에서 최근에 보낸 메세지 id를 조회하는 쿼리
SELECT MAX(id)
FROM message 
WHERE chat_room_id = 5;

-- 실행 결과
+---------+
| MAX(id) |
+---------+
|      17 |
+---------+

쿼리 실행 결과 및 팀 프로젝트의 테스트 코드를 통해 결과가 정확하게 일치함을 확인했습니다.

EXPLAIN SELECT MAX(id)
FROM message 
WHERE chat_room_id = 5;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+

쿼리 분석 결과 id, select_type, Extra를 제외한 다른 필드는 모두 NULL이고, Extra는 Select tables optimized away가 되었습니다.

Select tables optimized away 쿼리를 실행해 결과를 확인하기 위해 테이블을 스캔할 필요조차 없었다는 의미입니다.
즉, 실제 데이터 행을 읽지 않고 쿼리 결과를 출력할 수 있었으며, 비용을 표시하지도 않을 정도로 효율적이라고 이해했습니다.

결론

코드 리펙토링

public List<ChatRoomAndMessageAndImageDto> findAllChatRoomInfoByUserIdOrderByLastMessage(final Long userId) {
    final List<ChatRoomAndMessageAndImageQueryProjectionDto> unsortedDtos =
            queryFactory.select(new QChatRoomAndMessageAndImageQueryProjectionDto(chatRoom, message, auctionImage))
                        .from(chatRoom)
                        .leftJoin(chatRoom.buyer).fetchJoin()
                        .leftJoin(chatRoom.auction, auction).fetchJoin()
                        .leftJoin(auction.seller).fetchJoin()
                        .leftJoin(auctionImage).on(auctionImage.id.eq(
                                JPAExpressions
                                        .select(auctionImage.id.min())
                                        .from(auctionImage)
                                        .where(auctionImage.auction.id.eq(auction.id))
                                        //.groupBy(auctionImage.auction.id)
                        )).fetchJoin()
                        .leftJoin(auction.lastBid).fetchJoin()
                        .leftJoin(message).on(message.id.eq(
                                JPAExpressions
                                        .select(message.id.max())
                                        .from(message)
                                        .where(message.chatRoom.id.eq(chatRoom.id))
                                        //.groupBy(message.chatRoom.id)
                        )).fetchJoin()
                        .where(isSellerOrWinner(userId))
                        .fetch();

    return sortByLastMessageIdDesc(unsortedDtos);
}

쿼리 개선 결과를 코드에 적용했습니다.

학습 내용

  • GROUP BY는 여러 컬럼을 조회할 때 특정 기준에 따라 그룹화를 진행해야 할 때만 사용해야 합니다.
    • GROUP BY를 잘못 사용할 경우 비효율적으로 쿼리가 실행될 수 있습니다.
  • AUTO_INCREMENT 기반 PK에 대해 MIN(), MAX() 조회 시 효율적으로 처리할 수 있습니다.
profile
appti
안녕하세요
다음 포스트

CASE WHEN 쿼리 개선

0개의 댓글