[여기서 놀자] 예약 가능한 호텔만 노출하기

💡 이 글의 내용은 MySQL 8.0 이상 + InnoDB 스토리지 엔진 환경을 기준으로 작성되었습니다.

💡 이 글은 사이드 프로젝트 “여기서 놀자”의 호텔 검색 기능을 구현하고 성능을 개선한 과정을 정리한 글입니다.

구현할 기능 - 호텔 검색

지역, 카테고리, 숙박 날짜, 숙박 인원 등을 통해 조건에 맞는 호텔을 검색하는 기능

[요구사항]

• 카테고리, 상세 지역 등으로 필터링이 가능하다. [완료]
• 페이징을 지원한다. [완료]
• 호텔 이름, 평점, 가격, 주소, 지역, 카테고리 등 필요한 정보를 보여준다. [완료]
• 지역으로 필터링이 가능하다. [완료]
• 설정한 인원이 묵을 수 있는 객실이 있는 호텔들을 우선적으로 노출한다.
• 각 호텔에서 예약 가능한 가장 저렴한 객실의 가격을 표시한다.
• 지정한 날짜에 예약이 가능한 객실이 있는 호텔들을 우선적으로 노출한다.
• 가격 범위를 지정하여 검색이 가능하다.

목표

• 설정한 인원이 묵을 수 있는 객실이 있는 숙소들을 우선적으로 노출
• 각 호텔에서 예약 가능한 가장 저렴한 객실의 가격을 표시
• 지정한 날짜에 예약이 가능한 객실이 있는 숙소들을 우선적으로 노출
• 가격 범위를 지정하여 검색이 가능하다.

예약 가능한 객실 찾기

이제 숙박 인원, 가격 범위, 숙박 기간이 주어졌을 때 예약이 가능한 객실을 찾는 쿼리를 작성해보자.

우선 예약이 가능하려면 숙박 인원이 객실의 최대 수용 인원보다 작거나 같고, 객실의 가격이 가격 범위 내에 있어야 한다. 그리고 숙박 기간동안의 날짜마다 예약의 수가 객실의 개수보다 작아야 한다.

이를 위해서는 예약의 개수를 체크하는 테이블이 필요하다. 아래와 같이 만들어보자.

CREATE TABLE `reservation_check` (
    `id` bigint NOT NULL AUTO_INCREMENT,
    `room_id` bigint NOT NULL,
    `stay_date` date NOT NULL,
    `count` int NOT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `reservation_check_ibfk_1` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

위 테이블은 날짜마다 각 객실의 예약의 개수를 저장한다.

위와 같이 reservation_check 테이블의 데이터를 채워넣었다.

이제 reservation_check 테이블을 활용하여 예약이 가능한 객실을 찾는 쿼리를 만들어보자.

SELECT r.id,
       r.hotel_id,
       r.price
FROM rooms r
         LEFT OUTER JOIN reservation_check rc
                   ON r.id = rc.room_id
                       AND rc.stay_date BETWEEN '2023-06-18' AND '2023-06-25'
WHERE r.max_people_count >= 3
  AND 200000 >= r.price
  AND r.price >= 50000
GROUP BY r.id, r.count
HAVING COALESCE(MAX(rc.count), 0) <= r.count;

위 쿼리는 방 가격 범위, 숙박 가능 인원 수로 객실을 필터링한 뒤 해당 기간동안 최대 예약 수가 객실 수 이하인 경우 객실들을 구한다.

위 쿼리의 실행 계획을 살펴보자.

• Explain

  id	select_type	table	type	key	ref	rows	filtered	Extra
  1	SIMPLE	r	ALL	null	null	617789	3.7	Using where; Using temporary
  1	SIMPLE	rc	ref	reservation_check_ibfk_1	r.id	6	100	Using where

• Explain Analyze

실행 계획을 살펴보면 아래와 같다.

1. rooms 테이블을 풀 스캔한 뒤 r.max_people_count >= 3 AND 200000 >= r.price AND r.price >= 50000 조건에 맞는 객실만 필터링한다.
2. rooms의 각 행과 rooms.id와 room_id가 일치하는 rc의 행들 중 rc.stay_date BETWEEN '2023-06-18' AND '2023-06-25'를 만족하는 행을 left outer join한다.
3. room_id를 기준으로 그룹핑하고, HAVING절의 조건으로 필터링을 진행한다.

위 쿼리의 문제는, r.max_people_count >= 3 AND 200000 >= r.price AND r.price >= 50000 조건으로 필터링을 진행할 때 해당 컬럼으로 생성된 인덱스가 없어서 rooms 테이블을 풀스캔한 뒤 필터링을 진행하는 것이다.

인덱스는 만능일까?

rooms 테이블을 풀스캔하는 문제를 해결하기 위해서 인덱스를 생성해보자. 우선 price와 max_people_count 중 어떤 컬럼을 선행 컬럼으로 할 것인지 결정해야한다.

max_people_count는 1~6의 값만 있으므로 price 컬럼보다 카디널리티가 낮다. 단순히 생각해서 카디널리티가 높은 price컬럼을 선행 컬럼으로 하여 복합 인덱스를 생성해보자.

CREATE INDEX price_max_people_count_idx ON rooms(price, max_people_count);

이제 다시 위 쿼리를 실행하여 실행 계획을 살펴보자.

• Explain

  id	select_type	table	type	possible_keys	key	ref	rows	filtered	Extra
  1	SIMPLE	r	ALL	price_max_people_count_idx	null	null	617789	16.66	Using where; Using temporary
  1	SIMPLE	rc	ref	reservation_check_ibfk_1	reservation_check_ibfk_1	r.id	6	100	Using where

어라? 실행 계획에 변화가 없다. 분명히 possible_keys 컬럼에 보면 방금 추가한 price_max_people_count_idx 인덱스가 있는데, 사용하지 않고 여전히 풀스캔을 하고있다.

옵티마이저가 인덱스를 사용하여 rooms테이블을 스캔하는 것 보다 풀스캔을 하는 것이 더 효율적이라고 판단한 것이다. 왜일까?

인덱스가 비효율적인 경우 (1)

일반적으로 인덱스를 통해 레코드를 읽는 것은 바로 테이블의 레코드를 읽는 것 보다 비용이 높다. (약 4~5배)

• 인덱스 레인지 스캔을 하게되면 인덱스를 통해 얻은 레코드의 주소를 이용하여 레코드를 읽는데, 이때 레코드 한 건 마다 랜덤 I/O가 발생하므로 인덱스를 통해 레코드를 읽는 작업은 그냥 읽는 것보다 비용이 많이든다.

즉, 인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않고 테이블 풀 스캔 후 필요한 레코드만 걸러내는 방식으로 처리하는 것이 효율적이다.

위 쿼리의 경우 rooms 테이블의 전체 레코드 수는 619984개인데, 그 중 조건을 만족하는 레코드 수는 102954개이다. 전체테이블의 16.66% 정도를 조회하는 것이다.

그렇다면, 조건을 만족하는 레코드 수가 더 줄어들면 옵티마이저는 인덱스를 사용할까?

SELECT r.id,
       r.hotel_id,
       r.price
FROM rooms r
         LEFT OUTER JOIN reservation_check rc
                   ON r.id = rc.room_id
                       AND rc.stay_date BETWEEN '2023-06-22' AND '2023-06-25'
WHERE r.max_people_count >= 6
  AND 400000 >= r.price
  AND r.price >= 300000
GROUP BY r.id, r.count
HAVING COALESCE(MAX(rc.count), 0) <= r.count;

위와 같이 쿼리를 실행하여 실행 계획을 살펴보자.

• Explain

  id	select_type	table	type	key	key_len	ref	rows	filtered	Extra
  1	SIMPLE	r	range	price_max_people_count_idx	8	null	18210	33.33	Using index condition; Using temporary
  1	SIMPLE	rc	ref	reservation_check_ibfk_1	8	r.id	6	100	Using where

이번에는 의도대로 price_max_people_count_idx 인덱스를 사용해서 인덱스 레인지 스캔을 한 모습이다.

인덱스가 비효율적인 경우 (2)

가격 범위의 경우 사용자가 설정을 하지 않는 경우도 있다. 만약 가격 범위 조건을 설정해주지 않는다면 실행 계획은 어떻게 될까?

SELECT r.id,
       r.hotel_id,
       r.price
FROM rooms r
         LEFT OUTER JOIN reservation_check rc
                   ON r.id = rc.room_id
                       AND rc.stay_date BETWEEN '2023-06-22' AND '2023-06-25'
WHERE r.max_people_count >= 6
GROUP BY r.id, r.count
HAVING COALESCE(MAX(rc.count), 0) <= r.count;

기존 쿼리에서 가격 범위 조건을 없앤 쿼리이다. 실행 계획을 살펴보자.

• Explain

  id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
  1	SIMPLE	r	ALL	null	null	null	null	617789	33.33	Using where; Using temporary
  1	SIMPLE	rc	ref	reservation_check_ibfk_1	reservation_check_ibfk_1	8	r.id	6	100	Using where

맨 처음 실행 계획처럼 테이블 풀 스캔을 하는 모습이다. 하지만, possible_keys에도 price_max_people_count_idx가 없다. 왜 그런 것일까?

우리는 price_max_people_count_idx 인덱스를 생성할 때 price 컬럼을 복합 인덱스의 선행컬럼으로 했었다.

B-Tree 인덱스는 선행 컬럼을 기준으로 나머지 컬럼들이 정렬되어있다. 즉, 우선 price로 정렬이 되어있고, price가 같은 레코드들 끼리는 max_people_count로 정렬되는 것이다.

즉, WHERE 절에 price에 관한 조건이 없어서 복합 인덱스를 사용할 수 없는 것이다.

이처럼, 복합 인덱스를 생성할 때 선행 컬럼의 조건이 없다면 인덱스를 아예 사용할 수 없으므로 이를 잘 고려하여 선행 컬럼을 정해야한다.

rooms 테이블 풀 스캔 문제 결론

잘 생각해보면, 숙박 인원수를 낮은 수로 설정하게 되면 필터링이 거의 되지않을 수도 있고, 가격 범위 조건은 필수로 적용되는 것이 아니고, 범위를 어떻게 설정하느냐에 따라 너무 유동적이다.

결국 단순히 인덱스를 적용시켜 해결할 문제가 아니라는 결론을 내렸다.

대신, 먼저 rooms 테이블의 범위를 좁히는 것이 더 좋은 해결책이라는 생각이 들었다.

검색 시 호텔에 대한 조건을 넣을 것이고, 호텔의 범위를 줄이고 범위를 줄인 호텔의 객실을 찾게되면 탐색할 rooms 테이블의 레코드의 수가 줄어들 것이다.

SELECT r.id,
       r.hotel_id,
       r.price
FROM rooms r
         JOIN hotels h
              ON r.hotel_id = h.id
         LEFT OUTER JOIN reservation_check rc
                   ON r.id = rc.room_id
                       AND rc.stay_date BETWEEN '2023-06-22' AND '2023-06-25'
WHERE r.max_people_count >= 3
  AND r.price >= 50000
  AND r.price <= 200000
  AND h.detail_region_id = 1
  AND h.category_id
GROUP BY r.id, r.count
HAVING COALESCE(MAX(rc.count), 0) <= r.count;

위와 같이 hotels 테이블과 join 및 where절에 h.detail_region_id = 1 AND h.category_id조건을 추가하여 쿼리를 수정하고, 실행 계획을 살펴보자.

• Explain

  id	select_type	table	type	key	key_len	ref	rows	filtered	Extra
  1	SIMPLE	h	ref	detail_regions_categories_idx	16	const,const	410	100	Using index; Using temporary
  1	SIMPLE	r	ref	hotel_id	8	h.id	3	3.7	Using where
  1	SIMPLE	rc	ref	reservation_check_ibfk_1	8	r.id	6	100	Using where

• Explain Analyze

우선 detail_regions_categories_idx 인덱스만을 사용하여 조건에 맞는 호텔들의 id를 찾았다. 그런 다음 해당 호텔의 객실에 대해서만 탐색을 진행하여 탐색한 row수가 현저히 줄어들었다.

예약 가능한 객실을 보유한 호텔 찾기

이제 위에서 만든 쿼리를 활용해서 예약 가능한 객실을 보유한 호텔을 찾는 쿼리를 작성해보자.

WITH available_rooms AS (
    SELECT r.id,
           r.hotel_id,
           r.price
    FROM rooms r
        JOIN hotels h
            ON r.hotel_id = h.id
        LEFT OUTER JOIN reservation_check rc
            ON r.id = rc.room_id
                AND rc.stay_date BETWEEN '2023-06-22' AND '2023-06-25'
    WHERE r.max_people_count >= 3
        AND r.price >= 50000
        AND r.price <= 200000
        AND h.detail_region_id = 1
        AND h.category_id
        AND h.id > 100000
    GROUP BY r.id, r.count
    HAVING COALESCE(MAX(rc.count), 0) <= r.count
)
SELECT h.id,
       h.name        AS hotel_name,
       MIN(ar.price) AS min_price,
       h.rating,
       h.address,
       h.detail_region_name,
       h.category_name
FROM hotels h
         JOIN available_rooms ar
              ON h.id = ar.hotel_id
GROUP BY h.id
ORDER BY h.id
LIMIT 10;

위에서 만든 쿼리를 WITH절에 넣고, 메인 쿼리에서 hotels테이블과 한 번더 JOIN하도록 하였다.

위 쿼리를 실행하여 아래와 같이 예약이 가능한 객실을 보유한 호텔들의 정보를 성공적으로 가져왔다.