2025.02.07(금)- 채팅방 조회 쿼리 최적화

채팅방 조회 쿼리 최적화 방법

상황 : 현재 RDBS(MySQL)을 이용하여 데이터베이스를 설계함. 채팅방 아이디를 이용하여 아래의 DTO를 조회해아함.

쿼리를 하나로 작성하는 것과 여러 개의 쿼리로 나누어 실행하는 것 중 어떤 것이 더 성능적으로 유리할까?

하나로 작성할 때, JOIN 후 limit을 거는것과 limit을 건 서브쿼리와 JOIN 하는 것에 차이가 있을까?

1. DTO 구조

private int chatRoomId;
private ProductCardDTO productCard;
private List<UserCardDTO> participants;
private List<ChatMessageDTO> messages; // TODO : Message - Pagination

 

2. 성능 최적화를 위한 접근 방식

1️⃣ 조인 후 LIMIT 걸어 30개 추출 (쿼리 비용: 1214.48)

• 조인 시 메시지가 많아질수록 성능 저하
  • 메시지가 많을 경우, 모든 메시지를 조인 후 정렬하는 건 비효율적
  • 메시지 10만 건이 있다면, 불필요한 데이터도 함께 읽어야 하므로 성능 저하 발생 가능
  • 따라서 WITH 절이나 서브쿼리로 먼저 30개를 제한하는 방법이 효과적

 

2️⃣ WITH 절을 활용하여 상위 30개만 추출 후 조인 (쿼리 비용: 2.91)

• 서브쿼리 & WITH 절을 사용하면 최적화 가능
  • 데이터베이스 옵티마이저가 먼저 30개만 가져오고 나머지 조인을 수행 → 불필요한 데이터 읽기 방지
  • 실행 비용도 1,214 → 2.91로 대폭 감소.

 

3️⃣ 여러 개의 쿼리로 나눠 실행 (총 쿼리 비용: 1027.25)

• 여러 개의 쿼리로 나누면 DB 호출이 증가할 수 있음
  • 여러 개의 쿼리로 나누면 DB I/O가 증가해서 네트워크 비용이 커질 수 있음.
  • 하지만 경우에 따라 캐싱이나 비동기 처리를 활용하면 더 나을 수도 있음.
  • 예를 들어, 참여자 목록은 캐싱하고, 채팅 메시지만 페이징 처리하는 방법도 고려 가능

 

3. 쿼리 성능 비교

1️⃣ 조인 후 LIMIT 걸어 30개 추출 (쿼리 비용: 1214.48)

SELECT DISTINCT
    c.id AS chatRoomId,
    p.product_images AS productThumbnail,
    p.product_name AS name,
    p.price AS price,
    p.is_negotiable AS isNegotiable,
    p.id AS productId,
    m.user_id AS userId,
    u.name AS userName,
    u.profile_img AS profileImage,
    m.content AS messageContent,
    m.created_at AS messageCreatedAt
FROM tb_chat_room AS c
JOIN tb_product AS p ON p.id = c.product_id
JOIN tb_chat_message AS m ON c.id = m.chat_room_id
JOIN tb_user AS u ON u.id = m.user_id
WHERE c.id = 1
ORDER BY m.created_at DESC
LIMIT 30;

장점:

• 쿼리가 단순하여 작성이 용이함.

단점:

• 채팅 메시지가 많을 경우, 전체 데이터에 대해 정렬 및 조인 연산이 수행되므로 성능 저하 가능.
• 불필요한 데이터를 읽고 정렬하는 비용이 발생.
•  

2️⃣ WITH 절을 활용하여 상위 30개만 추출 후 조인 (쿼리 비용: 2.91)

(1) WITH 절 사용

WITH recent_messages AS (
    SELECT
        chat_room_id, content, created_at, user_id
    FROM tb_chat_message
    WHERE chat_room_id = 1
    ORDER BY created_at DESC
    LIMIT 30
)
SELECT
    c.id AS chatRoomId,
    p.product_images AS productThumbnail,
    p.product_name AS name,
    p.price AS price,
    p.is_negotiable AS isNegotiable,
    p.id AS productId,
    rm.user_id AS userId,
    u.name AS userName,
    u.profile_img AS profileImage,
    rm.content AS messageContent,
    rm.created_at AS messageCreatedAt
FROM tb_chat_room c
JOIN tb_product p ON p.id = c.product_id
JOIN recent_messages rm ON c.id = rm.chat_room_id
JOIN tb_user u ON u.id = rm.user_id;

 

(2) 서브쿼리 사용

SELECT
    c.id AS chatRoomId,
    p.product_images AS productThumbnail,
    p.product_name AS name,
    p.price AS price,
    p.is_negotiable AS isNegotiable,
    p.id AS productId,
    rm.user_id AS userId,
    u.name AS userName,
    u.profile_img AS profileImage,
    rm.content AS messageContent,
    rm.created_at AS messageCreatedAt
FROM tb_chat_room c
JOIN tb_product p ON p.id = c.product_id
JOIN (
    SELECT
        chat_room_id, content, created_at, user_id
    FROM tb_chat_message
    WHERE chat_room_id = 1
    ORDER BY created_at DESC
    LIMIT 30
) rm ON c.id = rm.chat_room_id
JOIN tb_user u ON u.id = rm.user_id;

장점:

• tb_chat_message 테이블에서 필요한 최신 30개 메시지만 먼저 조회 → 불필요한 데이터 조인 방지
• 데이터 양이 많아질수록 효율적

단점:

• 쿼리가 다소 복잡해질 수 있음.
• 서브쿼리의 최적화 여부는 DBMS에 따라 다를 수 있음.

 

3️⃣ 여러 개의 쿼리로 나눠 실행 (총 쿼리 비용: 1027.25)

-- (1) 채팅방 참여자 조회
SELECT
    u.id AS userId,
    u.name AS userName,
    u.profile_img AS profileImage
FROM tb_user u
WHERE u.id IN(
    SELECT DISTINCT user_id
    FROM tb_chat_mapping
    WHERE chat_room_id = 1
    ORDER BY create_at DESC
);

-- (2) 상품 정보 조회
SELECT
    p.id AS productId,
    p.product_images AS productThumbnail,
    p.product_name AS name,
    p.price AS price,
    p.is_negotiable AS isNegotiable
FROM tb_chat_room c
JOIN tb_product p ON p.id = c.product_id
WHERE c.id = 1;

-- (3) 최신 30개 메시지 조회
SELECT
    chat_room_id, content, created_at, user_id
FROM tb_chat_message
WHERE chat_room_id = 1
ORDER BY created_at DESC
LIMIT 30;

 

• 데이터를 개별적으로 가져와서 필요한 경우만 최적화 가능.
• 백엔드에서 추가 가공이 가능하여 유연성 증가.

단점:

• API 요청이 많아지고 데이터베이스 연결이 여러 번 발생하여 성능 저하 가능.
• JOIN을 사용하지 않으므로 일관성 관리가 어려울 수 있음.

 

❓ 2️⃣의 with 절 내 쿼리와 3️⃣의 사용자 쿼리가 같은데 비용이 다르다 ?

• 쿼리 옵티마이저가 3️⃣쿼리는 전체 테이블을 스캔한 후 정렬하여 LIMIT를 걸고
• 2️⃣ 쿼리는 같은 쿼리지만, WITH절로 묶음으로써 옵티마이저가 더 효율적인 실행 계획을 선택하게 됩니다.

# 3️⃣ 쿼리 코스트 - 1014.25 
SELECT 
        chat_room_id, content, created_at, user_id
    FROM tb_chat_message
    WHERE chat_room_id = 1
    ORDER BY created_at DESC
    LIMIT 30;
 # 이유 : 쿼리 옵티마이저는 이를 전체 테이블을 먼저 스캔하고, 정렬한 다음 LIMIT을 적용하려고 할 수 있습니다. 
 # 즉, "일단 다 가져와서 정렬하고 30개만 보여줄게" 라는 접근 방식
 
# 2️⃣ 쿼리 코스트 - 2.91
WITH recent_messages AS (
    SELECT 
        chat_room_id, content, created_at, user_id
    FROM tb_chat_message
    WHERE chat_room_id = 1
    ORDER BY created_at DESC
    LIMIT 30
)
SELECT 
    c.id AS chatRoomId,
    p.product_images AS productThumbnail,
    p.product_name AS name,
    p.price AS price,
    p.is_negotiable AS isNegotiable,
    p.id AS productId,
    rm.user_id AS userId,
    u.name AS userName,
    u.profile_img AS profileImage,
    rm.content AS messageContent,
    rm.created_at AS messageCreatedAt
FROM tb_chat_room c
JOIN tb_product p ON p.id = c.product_id
JOIN recent_messages rm ON c.id = rm.chat_room_id
JOIN tb_user u ON u.id = rm.user_id;
# 이유 : 옵티마이저가 이를 "아, 어차피 30개만 필요하니까 먼저 30개만 가져와서 임시 테이블에 넣고 시작하자" 라고 해석 
# 실제로는 같은 쿼리지만, WITH절로 묶음으로써 옵티마이저가 더 효율적인 실행 계획을 선택하게 됩니다.

 

 

4. 결론

1️⃣ 단일 쿼리 + LIMIT	1214.48	간단한 쿼리	메시지가 많아질수록 성능 저하
2️⃣ WITH 절 + 조인	2.91	성능 최적화	쿼리 구조 복잡
3️⃣ 여러 개의 쿼리	1027.25	유연성	DB 연결 비용 증가

 

 

=> 메시지가 많다는 전제 하에 2️⃣쿼리가 가장 효과적