한 방 JOIN 쿼리를 개선해보자 (feat. 데이터 모델링 재설계)

SELECT 쿼리를 통해 여러 테이블에 대한 데이터를 조합해서 보여줘야 할 때 JOIN 쿼리로 한 번에 조회해온 후 응답해주는 방식이 쿼리 왕복이 줄고 더 빠르다라고 판단을 했었다.

어떻게 보면 맞는 말이지만, 실제 발생하는 쿼리를 보며 “이러한 쿼리 구조가 맞나?” 라는 의구심이 생겼다.

구현하고자 한 API

숙소 예약 시스템을 개발하며 마주친 “숙소 상세 조회”를 대상으로 개선을 진행했다.

숙소 상세 조회의 API 구조를 먼저 살펴보자.

@GetMapping("/{accommodationId}")
public ApiResponse<FindAccommodationResponse> getDetailAccommodation(
        @PathVariable Long accommodationId,
        @RequestParam(required = true) LocalDate checkIn,
        @RequestParam(required = true) LocalDate checkOut,
        @RequestParam(required = true) int capacity
) {
    FindAccommodationResponse response = accommodationService.findAccommodation(accommodationId, checkIn, checkOut, capacity);
    return ApiResponse.success(response);
}

숙소 ID(accommodationId) 값을 기반으로 숙소 정보를 조회하며, 해당 숙소에 존재하는 객실에 대한 정보를 가져와야 한다. 또한, 조회 시점에 해당 객실에 대해 예약을 할 수 있는지 각 객실에 대한 예약 가능 여부를 판단하는 필드가 필요하다.

정리를 해보자면

1. 숙소 정보 (숙소명, 주소, 설명, 썸네일 등)

2. 객실 정보 (객실명, 객실 설명, 가격 등)

3. 예약 가능 여부 (해당 기간에 겹치는 예약 개수 체크)

또한, 예약은 가계약 방식으로 예약을 진행하기 때문에 예약 현황(reservations) 테이블과 임시 예약 현황(reservation_holds) 테이블에 대한 조회가 필요하다.

JOIN 한 방 쿼리로 해결하기

이렇듯 여러 테이블에 대한 조인이 불가피한 상황이며, 한 번에 응답하는 방식을 자연스럽게 채택하게 되었다.

아래는 작성했던 쿼리 예시이다.

SELECT ...
FROM accommodations AS a
JOIN room_types AS rt
	ON rt.accommodation_id = a.id
LEFT JOIN reservations AS r
	ON r.room_type_id = rt.id
	AND r.check_in < :checkOut
	AND r.check_out > :checkIn
LEFT JOIN reservation_holds AS rh
	ON rh.room_type_id = rt.id
	AND rh.check_in < :checkOut
	AND rh.check_out > :checkIn
WHERE a.isVisible = TRUE
	AND a.id = :accommodationId
GROUP BY a.id, rt.id;

위와 같은 쿼리를 보고, 이게 과연 성능적으로도 문제가 없이 조회가 되는 것인지에 대한 궁금증과 나눠서 처리했을 때의 차이점에 대해 분석을 해보기로 했다.

쿼리 분석 (EXPLAIN ANALYZE)

위의 쿼리를 기반으로 실행하고, 실행 과정을 분석하는 EXPLAIN ANALYZE 를 사용해봤다.

-> Table scan on <temporary>  (actual time=0.442..0.443 rows=2 loops=1)
    -> Aggregate using temporary table  (actual time=0.44..0.44 rows=2 loops=1)
        -> Left hash join (rh.room_type_id = rt.id)  (cost=17.7 rows=52.7) (actual time=0.274..0.286 rows=2 loops=1)
            -> Nested loop left join  (cost=0.8 rows=0.667) (actual time=0.0988..0.109 rows=2 loops=1)
                -> Filter: (rt.max_capacity >= 2)  (cost=0.567 rows=0.667) (actual time=0.0913..0.099 rows=2 loops=1)
                    -> Index lookup on rt using accommodation_id (accommodation_id=3)
                -> Filter: (r.check_in < ...) (actual rows=0 loops=2)
                    -> Covering index lookup on r using idx_room_type_dates
            -> Hash
                -> Filter: (rh.check_in < ...) (actual rows=0 loops=1)
                    -> Table scan on rh  (actual rows=79 loops=1)

Table scan, Left hash join 등 여러가지 과정이 보이지만 핵심 문제점만 한 번 추려보자.

문제 1. reservation_holds 테이블 풀스캔 발생

Table scan on rh

임시 예약(가계약)을 처리하는 테이블이기 때문에 별도의 인덱스를 설정하지 않았던 탓에 테이블의 모든 로우를 스캔하게 된다.

또한, 가계약 데이터는 계속 증가하지만 별도의 스케줄링을 통해 만료된 데이터를 지워주는 로직을 추가하지 않은 상태이며, 시스템의 규모가 커졌을 경우 이 과정 또한 괜찮은 설계인가에 대해 고민해볼 필요가 있다.

문제 2. Hash Join 발생 -> 해시 테이블 빌드 비용 증가

Left hash join
Hash -> (build side: reservation_holds)

위와 같이 해시 조인이 발생하게 된다면 아래와 같은 동작을 수행한다.

1. reservation_holds 테이블 전체를 읽는다.

2. 조건을 만족하는 레코드(row)를 메모리에 해시 테이블로 만든다.

3. 객실(room_types)에서 들어오는 row에 대해 해시 조인을 수행한다.

현재는 데이터가 많지 않아 큰 문제가 되지 않겠지만, 해시 테이블의 크기, 생성 비용, 메모리 사용량이 크게 증가하게 될 것이다.

문제 3. GROUP BY를 위해 임시 테이블 생성

Aggregate using temporary table
Table scan on <temporary>

MySQL이 조인 결과를 임시 테이블에 쌓고, 다시 해당 임시 테이블(temporary table)을 스캔하여 GROUP BY를 수행한다.

즉, 데이터가 많아지는 상황에서 메모리 내에서 처리하기 어려울 정도로 데이터가 많아져 데이터를 디스크에 임시로 저장한 후 처리하는 과정이 발생할 수 있다.

정리해보자면, 모든 테이블을 JOIN을 통해 한 번에 처리하려다 DB 내부에서 훨씬 큰 비용이 발생하게 되는 구조가 된 것이다.

도메인의 특성을 고려해서 테이블을 설계하자

위의 문제 1번의 상황을 봤을 때 임시 예약 현황(reservation_holds) 테이블이 과연 RDB에 적합한 데이터인지 다시 한 번 확인하게 되었다.

reservation_holds 성격

• 유효기간(TTL)이 존재한다.

• 잠깐 쓰고 사용되지 않을 데이터

• 임시 예약 -> 결제시 소유자 검증에만 사용

• 재고 동시성 제어에 연관됨

이러한 데이터를 RDB에 저장하는 구조보다는 조금 더 유연한 No-SQL에 저장하고, 처리하는 방향이 더 낫다고 판단했다.

• expiredAt 기준 필터링 필요 (만료되었을 경우 예약 불가)

• 스케줄링을 통한 삭제 필요

• 디스크에 썼다가 금방 지워질 데이터 (유효기간: 10분)

  • 인덱스를 걸기에도 애매하다고 판단

• 복잡한 쿼리문을 만들며 풀 테이블 스캔 및 해시 빌드의 대상

결과적으로 해당 데이터는 레디스를 통해서 관리하는 방향으로 변경하기로 했다.

모든 데이터는 RDB에 저장해야하는 것은 아님을 기억하자. 데이터의 생명주기를 고려하며 어떤 저장소를 선택할지에 대한 판단을 해야한다.

임시 예약 (reservation_holds) 재설계

Redis 키 설계

Key: userId:roomTypeId:checkIn:checkOut
Value: count (해당 roomType에 대한 예약 건수, 한 회원이 여러번 예약하는 것에 대응, HINCR)
TTL: 10분 (임시 예약 유효기간)

위와 같이 설계를 함으로써 별도의 테이블을 사용하지 않으며 만료된 데이터에 대한 검증 로직을 처리하지 않아도 된다.

추가적으로 Redisson 라이브러리를 사용해서 글로벌 락 기법을 도입하는 방식도 고려해야한다. (key: roomTypeId:checkIn:checkOut 과 같은 방향으로 최대한 경쟁상태가 발생하지 않도록) 다음 글에서 설명해보자.

쿼리를 분리해보자

임시 예약은 레디스로 이관되었으므로 쿼리를 통해 조회해야 할 데이터는 세 가지로 명확해진다.

1. 숙소 정보 (accommodations)

2. 객실 정보 (room_types)

3. 체크인/체크아웃 기간의 예약 현황 (reservations)

1. 숙소 정보 조회

조회 쿼리에서 또 고민을 했던게 숙소에 대한 정보를 어떻게 조회해오냐였다.

// 1. 숙소 존재 여부 검사
if (!accommodationRepository.existsById(accommodationId)) {
	throw new ReservationException(ErrorCode.ACCOMMODATION_NOT_FOUND, log::info);
}

// 2. 숙소 노출 여부 검사
if (!accommodationRepository.checkVisible(accommodationId)) {
	throw new ReservationException(ErrorCode.ACCOMMODATION_NON_VISIBLE, log::info);
}

데이터를 모두 조회해오는 JPA의 findById() 메서드와 어떤 차이가 있을까?

기존 방식: 2번의 쿼리

-- 1. 존재 여부 검사
SELECT 1
FROM accommodation
WHERE id = :accommodationId;

-- 2. 노출 여부 검사
SELECT 1
FROM accommodation
WHERE id = :accommodationId
	AND is_visible = TRUE;

한 번에 조회하는 방식

SELECT *
FROM accommodation
WHERE id = :accommodationId;

PK(클러스터드 인덱스)를 통해서 데이터를 조회하기 때문에 큰 차이는 없을 것으로 보인다. 물론, 데이터를 주고 받는 I/O 작업이 일어나지만 단건 조회이며 기존 방식의 “노출 여부 검사”의 쿼리가 조금 더 효율적으로 되게 만들려면 is_visible 컬럼에 인덱스를 걸어줘야 하는데 해당 값의 타입은 boolean 효율적으로 인덱스를 사용하지 못한다.

따라서 나는 아래와 같은 구조로 변경하게 되었다.

Accommodation accommodation = accommodationRepository.findById(accommodationId)
        .filter(Accommodation::isVisible)
        .orElseThrow(() -> new ReservationException(ErrorCode.ACCOMMODATION_NOT_FOUND, log::info));

• ID가 존재하지만, 노출되지 않는 숙소라는 상세한 예외 메시지는 어떻게 보면 클라이언트에게 보여진다는 것은 보안상 문제가 될 수 있다고 판단했다.

• 사용자는 단순히 조회할 수 있는 숙소인지만 판단하면 된다고 파악했고, 또한 코드적으로 조금 더 깔끔한 코드로 보여진다.

2. 객실 정보 조회

숙소가 유효하다고 판단이 되었을 때, 그 다음 단계는 숙소에 존재하는 각 객실 타입 목록을 조회하는 것이다.

List<RoomTypeSummaryDto> roomTypes = roomTypeRepository.findAllByAccommodationId(accommodationId);

List<Long> roomTypeIds = roomTypes.stream()
        .map(RoomTypeSummaryDto::roomTypeId)
        .toList();

이 시점에 실행되는 쿼리는 아래와 같이 단순하다.

SELECT *
FROM room_types
WHERE accommodation_id = :accommodationId;

-- EXPLAIN ANALYZE 결과
-> Index lookup on room_types using accommodation_id (accommodation_id=3)  (cost=0.7 rows=2) (actual time=0.0165..0.0213 rows=2 loops=1)

FK인 accommodation_id를 기반으로 조회하기 때문에 인덱스를 사용하여 별도의 조인 없이 빠르게 처리가 가능하다.

3. 객실별 예약 현황 조회

인덱스를 최대한 활용한 단일 집계 쿼리로, 체크인/체크아웃을 기반으로 겹치는 예약만 집계하면 되는 것이다.

Map<Long, AvailabilityRoomDto> reservedMap =
        reservationRepository.countOverlappingReservations(roomTypeIds, checkIn, checkOut)
            .stream()
            .collect(Collectors.toMap(
                    AvailabilityRoomDto::roomTypeId,
                    Function.identity()
            ));

SELECT r.room_type_id,
       COUNT(r.id)
FROM reservations r
WHERE r.room_type_id IN (:roomTypeIds)
  AND r.check_in < :checkOut
  AND r.check_out > :checkIn
GROUP BY r.room_type_id;

-- EXPLAIN ANALYZE 결과

-> Group aggregate: count(r.id)  (cost=0.45 rows=1) (actual time=0.0276..0.0276 rows=0 loops=1)
    -> Filter: ((r.room_type_id in (5,6)) and (r.check_in < <cache>(((2025 - 11) - 20))) and (r.check_out > <cache>(((2025 - 11) - 21))))  (cost=0.35 rows=1) (actual time=0.0262..0.0262 rows=0 loops=1)
        -> Covering index scan on r using idx_room_type_dates  (cost=0.35 rows=1) (actual time=0.0187..0.0238 rows=1 loops=1)

커버링 인덱스를 통해 데이터를 가져온 후, 필터링을 거치게 된다.

결과적으로 객실 타입(room_types) 개수만큼 딱 필요한 데이터만 가져오는 구조가 된다.

4. 예약 가능 여부 매핑

예약 현황에 대한 정보를 3번에서 가져온 후 애플리케이션 레벨에서 매핑을 하는 방식으로 접근했다.

1. 객실 정보

2. 각 객실별 예약 현황

List<RoomTypeAvailabilityDto> roomTypeAvailabilities = roomTypes.stream()
        .map(room -> {
            AvailabilityRoomDto reserved = reservedMap.get(room.roomTypeId());
            return RoomTypeAvailabilityDto.of(room, reserved);
        })
        .toList();

• JOIN으로 엮게 될 경우 별도의 임시 테이블이 필요

• 애플리케이션에서 단순한 컬렉션 매핑이 오히려 가독성이 뛰어나 유지보수 측면에서 좋을 수 있음.

쿼리를 분리해서 얻은 점

관점	이유
성능	JOIN 기반 복잡 쿼리에서 인덱스를 사용하는 3개의 쿼리로 개선
도메인	엔티티 설계에 대한 관점을 넓힐 수 있었다. (도메인의 특성에 따른 저장 위치)
유지보수	쿼리 및 코드가 복잡하지 않아 코드리뷰 및 유지보수 관점에서 효율적

---

이 과정을 통해 "모든 데이터를 한 번에 가져오는 한방 쿼리가 항상 좋은가?"라는 질문을 다시 생각해보게 되었다.

JOIN으로 묶어 한 번에 조회하는 방식이 때로는 빠를 수 있지만, 실행 계획을 실제로 분석해보면 언제나 최선은 아닌 것 같다. 그리고 쿼리 성능만 볼 것이 아니라, 그 데이터가 어떤 생명주기를 가지고 있는지, 정말 RDB에 저장되어야 하는 데이터인지, 어떤 저장소가 가장 자연스러운지까지 함께 고려해야 한다는 점을 배웠다.

쿼리 최적화는 단순 쿼리(SQL)를 다듬는 작업뿐 아니라, 데이터와 시스템의 특성에 맞는 모델링과 접근 방식을 선택해야한다는 점을 생각하며 개발을 진행해보자.