복합 검색에 인덱스 적용

문제상황
기존 식당 복합 검색 API (complex-search)는 카테고리, 특정 날짜, 시간에 예약 가능한 잔여 좌석이 있는 식당만을 필터링하여 조회하는 기능이다.

하지만 목 데이터를 구축하고 성능 테스트를 진행한 결과, 해당 API의 응답 속도가 850ms가 걸리는 성능 이슈를 발견했다. 이를 DB 인덱싱 전략(Full-Text Index, Composite Index)을 통해 최적화하려고 한다.

데이터 환경

• RDBMS: MySQL 8.0 (InnoDB)

• 식당(Restaurant): 1,000,000개

• 예약 슬롯(ReservationSlot): 식당마다 약 1,200,000개

Relationship: Restaurant (1) : ReservationSlot (N)

쿼리 분석

SELECT r FROM Restaurant r
JOIN r.restaurantCategory rc
WHERE (:categoryId IS NULL OR rc.id = :categoryId)
  AND EXISTS (
      SELECT s.id FROM ReservationSlot s
      WHERE s.restaurant = r       -- (1) 식당 매칭
      AND s.date = :reservationDate -- (2) 날짜 확인
      AND s.time = :reservationTime -- (3) 시간 확인
      AND s.count > 0              -- (4) 잔여석 확인
  )

먼저 카테고리로 식당을 추려낸 뒤, 각 식당마다 120만 건의 슬롯 테이블로 이동하여 날짜,시간,잔여좌석이 있는 데이터를 하나하나 찾아야 했습니다.

해결 과정
쿼리 자체는 문제가 없었습니다. 문제는 데이터를 찾는 경로였습니다. 서브쿼리가 테이블을 뒤지지 않고, 인덱스만 보고 즉시 판단할 수 있도록 커버링 인덱스(Covering Index)를 적용하기로 했습니다.

적용
기존의 비효율적인 단일 인덱스를 대체하는 새로운 복합 인덱스를 생성했습니다.

CREATE INDEX idx_slot_restaurant_datetime_count
ON reservation_slot (restaurant_id, date, time, count);

EXISTS 서브쿼리의 WHERE 절 조건에 맞춰 인덱스 컬럼 순서를 설계했습니다.

인덱스 구성: (restaurant_id, date, time, count)

설계 의도:

restaurant_id: 메인 쿼리의 식당 ID로 범위를 1차로 좁힘.

date, time: 사용자가 원하는 날짜/시간으로 데이터를 핀포인트로 찾음.

count (Range): 잔여석 여부(> 0)를 확인.

-> DB는 실제 데이터 블록에 접근하지 않고 인덱스 트리 내부에서만 "예약 가능 여부"를 판단할 수 있습니다.

결과

결과적으로 별도의 코드 수정 없이 인덱스 튜닝만으로 성능이 약 6배 향상되었습니다.

쿼리 실행 계획(Explain)에서 type: ref, key: idx_slot_restaurant_datetime_count, Extra: Using index가 확인되어, 의도한 대로 커버링 인덱스가 작동함을 확인했습니다. (추후 사진 첨부)

기술적 고민
이번 성능 최적화 과정을 진행하며 단순히 인덱스를 생성하는 것을 넘어, "어떤 컬럼을, 어떤 순서로, 왜 인덱싱해야 하는가?"에 대해 깊이 고민할 수 있었습니다.

🤔 "카테고리 조회 성능은 따로 챙기지 않아도 될까?"
complex-search API의 또 다른 조건인 '카테고리 필터링'에 대해서도 인덱스 추가가 필요할지 고민했습니다.

하지만 InnoDB 스토리지 엔진의 특성을 공부하며, ManyToOne 관계로 설정된 외래 키(FK) 컬럼에는 자동으로 B-Tree 인덱스가 생성된다는 점을 확인했습니다.

실제로 EXPLAIN을 통해 실행 계획을 분석해 본 결과, 이미 자동 생성된 FK 인덱스를 타고 효율적으로 식당을 추려내고 있었습니다. 불필요한 인덱스 중복 생성은 오히려 쓰기 성능(INSERT/UPDATE)을 저하시킬 수 있기에, 추가 인덱스 없이 기존 구조를 활용하는 것이 최적이라는 결론을 내렸습니다.

마치며
이번 튜닝을 통해 서로 다른 테이블을 조인하거나 서브쿼리로 연결할 때, 인덱스 설계가 성능의 핵심이라는 것을 체감했습니다. 특히 120만 건이라는 대용량 데이터 환경에서 적절한 인덱싱 전략 하나가 사용자 경험을 얼마나 개선할 수 있는지 확인할 수 있었습니다.