💡 이 글의 내용은 MySQL 8.0 이상 + InnoDB 스토리지 엔진 환경을 기준으로 작성되었습니다.
💡 이 글은 사이드 프로젝트 “여기서 놀자”의 호텔 검색 기능을 구현하고 성능을 개선한 과정을 정리한 글입니다.
지역, 카테고리, 숙박 날짜, 숙박 인원 등을 통해 조건에 맞는 호텔을 검색하는 기능
[요구사항]
일단은 현재까지는 SELECT *
로 hotels 테이블의 모든 데이터를 불러왔다. 이제는 원하는 정보들만 보여줄 수 있도록 해보자.
SELECT h.name AS hotel_name,
h.rating AS hotel_rating,
h.address AS hotel_address,
dr.name AS detail_region_name,
c.name AS category_name
FROM hotels h
JOIN detail_regions dr ON h.detail_region_id = dr.id
JOIN categories c ON h.category_id = c.id
WHERE h.id > 100000
ORDER BY h.id
LIMIT 20;
기존 쿼리에서 카테고리 이름과 상세 지역 이름을 가져오기 위해 JOIN이 추가되었다.
여기서 성능을 향상시키기 위해서 categories 테이블이나 detail_regions 테이블의 데이터를 hotels 테이블에 포함시키는 역정규화를 고려해볼 수 있다.
역정규화를 했을 때의 장점
hotels
테이블에 다른 테이블의 정보가 포함되면, hotels
테이블에 대한 쿼리만으로 필요한 정보를 얻을 수 있다. 이는 조인 연산을 없애므로 쿼리 성능을 향상시킨다.hotels
테이블을 쿼리할 때 마다 다른 테이블을 조인하지 않아도 되므로, 쿼리의 복잡성이 감소합니다.역정규화를 했을 때의 단점
hotels
테이블에서도 해당 데이터를 업데이트해야 한다. 이는 두 테이블 간에 데이터 불일치를 일으킬 수 있다.hotels
테이블에 복제하면, 저장 공간이 증가한다.더 확실히 장단점을 파악하기 위해 역정규화를 하기 전과 하고난 뒤를 비교해보자.
역 정규화를 하기 전의 실행 계획을 살펴보자.
실행 계획을 살펴보면 상세 지역 이름, 카테고리 이름을 가져오기 위해 hotels 테이블과 detail_regions 테이블과 categories 테이블을 조인하고있다.
이제 역정규화를 진행해보자.
위와 같이 hotels 테이블에 category_name, detail_region_name 컬럼을 추가했다.
SELECT name,
rating,
address,
detail_region_name,
category_name
FROM hotels
WHERE id > 100000
ORDER BY id
LIMIT 20;
이제 위와 같이 쿼리를 바꾸고 성능을 측정해보자.
쿼리도 단순해졌고, 성능도 역정규화 전보다 60%(0.1ms → 0.04ms)정도 향상된 모습이다.
역정규화를 진행함으로써 데이터 일관성을 신경써야하고, 전체 데이터 용량도 늘어났지만 결국 아래와 같은 이유들로 역정규화를 진행하는 것이 좋다고 판단하였다.
이번에는 지역으로 필터링 할 수 있는 기능을 추가해보자.
SELECT h.name AS hotel_name,
rating,
address,
detail_region_name,
category_name
FROM hotels h
JOIN detail_regions dr ON detail_region_id = dr.id
WHERE dr.region_id = 1
AND h.id > 100000
ORDER BY h.id
LIMIT 20;
지역 id는 detail_regions테이블에 있으므로 위와 같이 JOIN을 수행해야한다.
이제 실행계획을 살펴보자.
실행 계획을 분석해보면 아래와 같다.
id > 100000
조건을 만족하는 row를 필터링한다.region_id = 1
조건을 만족하는 row를 필터링한다.LIMIT 20
절을 적용하여 최종 결과를 20개의 row로 제한한다.실행 계획을 보면 Nested Loop Join 작업에서 조인 결과를 필터링하는 과정에서 약간의 성능 저하가 발생한다. 이는 detail_regions 테이블에서 region_id = 1
조건을 만족하는 로우가 많아서 발생하는 문제이다.
이를 해결하기 위해서는 역시나 역정규화를 고려할 수 있다. 역정규화를 진행해서 성능을 비교해보자.
위와 같이 hotels 테이블에 region_id 컬럼을 추가했다.
SELECT h.name AS hotel_name,
rating,
address,
detail_region_name,
category_name
FROM hotels h
WHERE h.region_id = 1
AND h.id > 100000
ORDER BY h.id
LIMIT 20;
이제 위와 같이 쿼리를 바꾸고 성능을 측정해보자.
쿼리의 복잡도도 감소하고, 성능도 역정규화 전보다 71%(0.38ms → 0.11ms)정도 향상된 모습이다.
숙소의 지역 정보는 변경될 일이 거의 없고, 추가되는 데이터도 작기 때문에 역정규화를 진행하는 것으로 결정하였다.
역정규화를 진행함으로써 데이터 일관성을 신경써야하고, 전체 데이터 용량도 늘어났지만 결국 아래와 같은 이유들로 역정규화를 진행하는 것이 좋다고 판단하였다.