✅ 기존 쿼리 및 실행 계획 분석
기존 쿼리 ↓
더보기
더보기
analyze
select a1_0.accommodation_id as '숙소 ID',
a1_0.title as '숙소 제목',
max(ap1_0.price) as '가격',
coalesce(avg(r2_0.rating), 0.0) as '평균 평점',
ai1_0.image_url as '썸네일',
w1_0.wishlist_id is not null as '위시리스트 등록 여부',
w1_0.wishlist_id as '위시리스트 ID',
w1_0.name as '위시리스트 이름',
coalesce(count(r1_0.reservation_id), 0) as '예약 수',
ac1_0.code_name as '지역명',
ac1_0.area_code as '지역 코드'
from accommodations as a1_0
# 1
join accommodation_prices as ap1_0
on ap1_0.accommodation_id = a1_0.accommodation_id
and ap1_0.season = 'PEAK'
and ap1_0.day_type = 'WEEKEND'
# 2
join accommodation_images as ai1_0
on ai1_0.accommodation_id = a1_0.accommodation_id
and ai1_0.thumbnail = true
# 3
join sigungu_codes as sc1_0 on sc1_0.sigungu_code = a1_0.sigungu_code
join area_codes as ac1_0 on ac1_0.area_code = sc1_0.area_code
# 4
left join reservations as r1_0 on r1_0.accommodation_id = a1_0.accommodation_id
left join reviews as r2_0 on r2_0.reservation_id = r1_0.reservation_id
# 5
left join wishlist_accommodations as wa1_0 on wa1_0.accommodation_id = a1_0.accommodation_id
left join wishlists as w1_0
on w1_0.wishlist_id = wa1_0.wishlist_id
and w1_0.member_id = 114
group by a1_0.accommodation_id,
w1_0.wishlist_id
# 6
order by count(r1_0.reservation_id) desc,
avg(r2_0.rating) desc;성능 개선 대상은 에어비앤비 클론코딩 프로젝트로, 숙소와 관련된 테이블들을 모두 조인한 다음 필요한 컬럼들만 `select`하고 있습니다. 쿼리를 간단하게 설명하면 다음과 같습니다.
- 가격 테이블과 조인합니다. 숙소 하나당 주중/주말 & 비수기/성수기로 총 네 가지 조합의 가격이 각각 존재합니다.
- 이미지 테이블과 조인합니다. 숙소 하나당 N개의 이미지가 있고 반드시 하나의 썸네일이 존재합니다.
- 지역 테이블과 조인합니다.
- 예약, 후기 테이블과 조인합니다.
- 위시리스트 테이블과 조인합니다.
- 예약 수, 평점 기준 내림차순 정렬합니다. (=인기순)
위 쿼리의 `analyze`에 의해 실행된 실행 계획은 다음과 같습니다. (쿼리 실행에 앞서 숙소 10만 개와 이미지 10장씩 `insert`하였습니다. 즉 숙소 10만 개, 가격 40만 개, 이미지 100만 개가 저장되어 있습니다.)
쿼리 실행 시간만 5.6초가 걸리고 있습니다. 실행 계획을 분석해 쿼리의 문제점을 정리해 보겠습니다.
- 풀스캔 및 정렬 처리
- 숙소 테이블(a1_0)을 풀스캔(type=ALL)하면서 Using temporary; Using filesort가 발생했습니다. 인덱스를 전혀 사용하지 못해 결과를 바로 스트리밍 하지 못하고 조인 결과를 임시 테이블에 저장 후, 결과를 다시 정렬 처리했음을 의미합니다.
- 이는 order by를 처리하는 방법 중 가장 속도가 떨어지는 방법으로 분명한 튜닝 대상으로 볼 수 있습니다.
- + 임시 테이블이 필요한 쿼리
더보기- ORDER BY와 GROUP BY에 명시된 컬럼이 다른 쿼리
- ORDER BY나 GROUP BY에 명시된 컬럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
- DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우 or DISTINCT가 인덱스로 처리되지 못하는 쿼리
- UNION이나 UNION DISTINCT가 사용된 쿼리 (select_type이 UNION RESULT인 경우)
- 실행 계획에서 select_type이 DERIVED인 쿼리
- 비효율적인 필터링
- r_filtered를 보면 가격 테이블(ap1_0)은 25.0, 이미지 테이블(ai1_0)은 9.84라고 나와 있습니다. 이는 가격은 4개 중 1개만 사용되어 3개는 버려지고, 이미지는 10개 중 1개만 사용되어 9개는 버려짐을 의미합니다.
- 테이블 설계상 가격은 시기별 하나만 가져와도 되고, 이미지도 마찬가지로 10개 중 썸네일 하나만 가져오면 되기 때문에 모두 스캔하고 나머지를 버릴 필요 없이 하나만 가져오도록 튜닝이 필요해 보입니다.
- 불필요한 조인
- 예약(r1_0)과 후기(r2_0) 테이블을 예약 수와 평균 평점, 즉 집계를 위해 조인하고 있습니다.
- 지금은 예약과 후기에 대한 데이터가 많지 않아 성능에 영향은 없지만, 데이터가 쌓일수록 지금과 같은 실시간 집계는 분명 부담이 될 수 있으므로 개선이 필요해 보입니다.
✅ 성능 개선 시도
실행 계획을 분석해 발견한 문제점들을 개선해 보겠습니다.
1️⃣ 인덱스 추가
2번 문제를 해결하기 위해 인덱스를 추가하겠습니다. 인덱스 추가 대상 테이블은 가격과 이미지입니다.
# 가격
create index idx_prices_seasonal
on accommodation_prices (accommodation_id, season, day_type, price);
# 이미지
create index idx_images_thumbnail
on accommodation_images (accommodation_id, thumbnail, image_url);인덱스 추가 후 실행 계획을 비교해 보겠습니다.
드디어 r_filtered가 각각 25.0, 9.84에서 모두 100으로 맞춰졌습니다. 즉 인덱스 추가로 인해 모두 스캔한 후 필요 없는 행을 버리는 대신 필요한 행 하나만 가져오게 되었습니다. 추가로 Using index, 커버링 인덱스로 동작하는 것을 확인할 수 있습니다.
결과적으로 인덱스 추가만으로 실행 시간이 기존 5.687초에서 2.067초로 개선이 되었습니다.
2️⃣ 반정규화
1번과 3번 문제를 해결하기 위해 반정규화를 해보겠습니다. 기존 쿼리의 문제점이 항상 예약과 후기 테이블을 조인해서 숙소의 예약 수, 평균 평점과 같은 집계를 수행하는 것이었습니다. 이를 반정규화로 조인 및 임시 테이블 정렬 비용을 줄이기 위해 기존 숙소 테이블에 다음 필드를 추가했습니다.
alter table accommodations
add column reservation_count int default 0, # 예약 수
add column average_rating double default 0.0; # 평균 평점이렇게 되면 쿼리는 다음과 같이 바뀝니다. 더 이상 예약과 후기 테이블까지 조인해서 `avg`, `count` 같은 집계함수를 사용할 필요 없이 바로 숙소 테이블에서 `select`할 수 있습니다.
select a1_0.accommodation_id as '숙소 ID',
a1_0.title as '숙소 제목',
ap1_0.price as '가격',
a1_0.average_rating as '평균 평점',
ai1_0.image_url as '썸네일',
w1_0.wishlist_id is not null as '위시리스트 등록 여부',
w1_0.wishlist_id as '위시리스트 ID',
w1_0.name as '위시리스트 이름',
a1_0.reservation_count as '예약 수',
ac1_0.code_name as '지역명',
ac1_0.area_code as '지역 코드'
from accommodations as a1_0
join accommodation_prices as ap1_0
on ap1_0.accommodation_id = a1_0.accommodation_id
and ap1_0.season = 'PEAK'
and ap1_0.day_type = 'WEEKEND'
join accommodation_images as ai1_0
on ai1_0.accommodation_id = a1_0.accommodation_id
and ai1_0.thumbnail = true
join sigungu_codes as sc1_0 on sc1_0.sigungu_code = a1_0.sigungu_code
join area_codes as ac1_0 on ac1_0.area_code = sc1_0.area_code
left join wishlist_accommodations as wa1_0 on wa1_0.accommodation_id = a1_0.accommodation_id
left join wishlists as w1_0
on w1_0.wishlist_id = wa1_0.wishlist_id
and w1_0.member_id = 114
order by a1_0.reservation_count desc,
a1_0.average_rating desc;💡 반정규화 시 데이터 정합성을 지키는 것이 중요하므로 다음과 같은 추가 배치 작업이 필요합니다.
더보기
더보기
@Transactional
@Scheduled(cron = "0 */30 * * * *") // 30분마다 실행
public void refreshStats() {
accommodationRepository.refreshStats();
}
----------------------------------------------------------------
@Modifying
@Query(value = """
UPDATE accommodations a
LEFT JOIN (
SELECT accommodation_id, COUNT(*) AS cnt
FROM reservations
WHERE status != 'CANCELED'
GROUP BY accommodation_id
) AS res ON res.accommodation_id = a.accommodation_id
LEFT JOIN (
SELECT r.accommodation_id, ROUND(AVG(rv.rating), 2) AS avg_rating
FROM reservations r
JOIN reviews rv ON r.reservation_id = rv.reservation_id
GROUP BY r.accommodation_id
) AS rev ON rev.accommodation_id = a.accommodation_id
SET a.reservation_count = coalesce(res.cnt, 0),
a.average_rating = coalesce(rev.avg_rating, 0.0)
""", nativeQuery = true)
void refreshStats();
추가로 `order by` 컬럼에 인덱스를 추가해 Using filesort를 없애고 인덱스를 이용해 정렬을 처리하도록 해보았습니다.
create index idx_reservation_count
on accommodations (reservation_count DESC, average_rating DESC);실행 계획은 다음과 같습니다.
실행시간만 보면 1초 미만으로 개선이 되었습니다. 또한 예약 및 후기 테이블 조인이 사라졌고, `group by`를 사용하지 않아 임시 테이블(Using temporary) 사용을 하지 않습니다. 그러나 Filesort는 여전히 남아 있습니다. 원인을 파악하기 위해 위에서 만든 인덱스를 확인해 보았습니다.
우선 정렬 기준 컬럼으로 만든 인덱스는 카디널리티가 너무 낮습니다. 하지만 애초에 근본적인 원인은 `where` 조건 없이 풀스캔 후 정렬을 시도하는 것입니다.
3️⃣ 통계 테이블
현재 프로젝트 설계 상 DB에서 풀스캔해서 가져온 다음 서버에서 직접 지역별로 인기순 TOP N(8) 숙소를 추출하고 있습니다. 다음은 해당 코드입니다.
더보기
더보기
public List<MainAccResDto> getAccommodations(Long memberId) {
LocalDate now = LocalDate.now();
Season season = dateManager.getSeason(now);
DayType dayType = dateManager.getDayType(now);
// 풀스캔
List<MainAccListQueryDto> accommodations = accommodationQueryRepository.getAreaAccommodations(season, dayType, memberId);
// 지역별 그룹핑 후 반환
return accommodations
.stream()
.collect(groupingBy(
MainAccListQueryDto::getAreaKey,
collectingAndThen(
toList(),
dtos -> dtos.stream()
.map(MainAccListResDto::from)
.limit(8).toList()
)
))
.entrySet()
.stream()
.map(entry -> new MainAccResDto(
entry.getKey().areaName(),
entry.getKey().areaCode(),
entry.getValue())
)
.toList();
}
위와 같이 DB에서 풀스캔해서 가져온 다음 서버에서 직접 지역별 인기순 TOP N을 추출하는 설계는 인덱스 문제보다 구조 자체의 문제에 가깝기 때문에 풀스캔 문제를 해결하는 데 한계가 있습니다. 이를 해결하기 위해 다음 방법들을 생각해 보았습니다.
- 모든 지역 개별 조회
- 장점 : 쿼리마다 인덱스 활용 및 풀스캔 회피, 최소 행 조회 가능
- 단점 : 지역 수만큼 쿼리 필요, 개별 조회 후 병합 로직 필요
- 윈도우 함수 사용
- 장점 : 쿼리 한 번으로 목표(지역별 TOP N) 달성 가능
- 단점 : 풀스캔 필요, 네이티브 쿼리 필요(유연성 및 유지보수성 감소)
- 통계 테이블
- 장점 : 풀스캔 없이 단순 select 조회 가능, 조인 및 정렬 비용 최적화
- 단점 : 추가 배치 작업 필요, 실시간성 감소
지역별 인기 숙소는 실시간성이 중요한 정보는 아니기에 `쿼리 한번 + 풀스캔 X`을 모두 만족할 수 있는 통계 테이블을 추가하기로 하였습니다.
통계용 테이블은 다음과 같이 정의했습니다.
create table accommodation_stats
(
stat_id bigint auto_increment comment '고유 ID' primary key,
accommodation_id bigint not null comment '숙소',
area_code varchar(255) not null comment '지역 코드',
area_name varchar(255) not null comment '지역명',
title varchar(255) not null comment '숙소 제목',
average_rating double default 0 null comment '평균 평점',
reservation_count int default 0 null comment '예약 수',
thumbnail_url varchar(700) not null comment '숙소 썸네일'
);이렇게 되면 API 시 실행되는 쿼리 및 실행 계획은 다음과 같습니다.
select as1_0.accommodation_id as '숙소 ID',
as1_0.title as '숙소 제목',
ap1_0.price as '가격',
as1_0.average_rating as '평균 평점',
as1_0.thumbnail_url as '썸네일',
w1_0.wishlist_id is not null as '위시리스트 등록 여부',
w1_0.wishlist_id as '위시리스트 ID',
w1_0.name as '위시리스트 이름',
as1_0.reservation_count as '예약 수',
as1_0.area_name as '지역명',
as1_0.area_code as '지역 코드'
from accommodation_stats as1_0
join accommodation_prices ap1_0
on ap1_0.accommodation_id = as1_0.accommodation_id
and ap1_0.season = 'PEAK'
and ap1_0.day_type = 'WEEKDAY'
left join wishlist_accommodations wa1_0
on wa1_0.accommodation_id = as1_0.accommodation_id
left join wishlists w1_0
on w1_0.wishlist_id = wa1_0.wishlist_id
and w1_0.member_id = 114;가격과 위시리스트는 각각 시기별, 사용자별로 동적인 정보가 포함되어 있으므로 조인을 해줍니다.
시간이 0.001초로 매우 단축되었습니다. `type=ALL`로 풀스캔이 발생했지만 10만 개 전체가 아닌 인기 있는 128개 행만을 풀스캔 한 것이므로 성능에는 전혀 영향이 없습니다.
위와 같이 반정규화 및 통계 테이블 분리로 성능은 개선되었지만 데이터 정합성을 지켜주는 것이 중요합니다. 따라서 다음과 같이 통계 갱신 배치 코드를 추가해 주었습니다.
더보기
@Slf4j
@Service
@Transactional
@RequiredArgsConstructor
public class AccommodationStatisticsService {
private final EntityManager em;
// 매일 새벽 2시 실행
@Scheduled(cron = "* * 2 * * *")
public void refreshStats() {
log.info("지역별 인기 숙소 TOP N 통계 갱신");
String sql = """
TRUNCATE TABLE accommodation_stats;
INSERT INTO accommodation_stats (accommodation_id, area_code, area_name, title, average_rating, reservation_count, thumbnail_url)
SELECT
ranked.accommodation_id,
ac.area_code,
ac.code_name,
ranked.title,
ranked.average_rating,
ranked.reservation_count,
ai.image_url
FROM (
SELECT
a.accommodation_id,
a.title,
a.average_rating,
a.reservation_count,
sc.area_code,
ROW_NUMBER() OVER (
PARTITION BY sc.area_code
ORDER BY a.reservation_count DESC, a.average_rating DESC
) AS rn
FROM accommodations a
JOIN sigungu_codes sc ON sc.sigungu_code = a.sigungu_code
) ranked
JOIN area_codes ac ON ac.area_code = ranked.area_code
JOIN accommodation_images ai
ON ai.accommodation_id = ranked.accommodation_id
AND ai.thumbnail = true
WHERE ranked.rn <= 8
""";
em.createNativeQuery(sql)
.executeUpdate();
}
// 매 30분 단위(정각, 30분) 실행
@Scheduled(cron = "0 */30 * * * *")
public void refreshRecentStats() {
log.info("숙소 반정규화 통계 필드 갱신 - 최근 변경");
String sql = """
UPDATE accommodations a
SET
a.reservation_count = (
SELECT COUNT(*)
FROM reservations r
WHERE r.accommodation_id = a.accommodation_id
AND r.status != 'CANCELED'
),
a.average_rating = COALESCE((
SELECT ROUND(AVG(rv.rating), 2)
FROM reviews rv
JOIN reservations rs ON rv.reservation_id = rs.reservation_id
WHERE rs.accommodation_id = a.accommodation_id
), 0.0)
WHERE a.accommodation_id IN (
SELECT DISTINCT accommodation_id
FROM reservations
WHERE updated_at >= DATE_SUB(NOW(), INTERVAL 30 MINUTE)
UNION
SELECT DISTINCT rs.accommodation_id
FROM reviews rv
JOIN reservations rs ON rv.reservation_id = rs.reservation_id
WHERE rv.updated_at >= DATE_SUB(NOW(), INTERVAL 30 MINUTE)
)
""";
em.createNativeQuery(sql)
.executeUpdate();
}
// 매일 새벽 3시 실행
@Scheduled(cron = "0 0 3 * * *")
public void refreshAllStats() {
log.info("숙소 반정규화 통계 필드 갱신 - 전체");
String sql = """
UPDATE accommodations a
SET
a.reservation_count = COALESCE((
SELECT COUNT(*)
FROM reservations r
WHERE r.accommodation_id = a.accommodation_id
AND r.status != 'CANCELED'
), 0),
a.average_rating = COALESCE((
SELECT ROUND(AVG(rv.rating), 2)
FROM reviews rv
JOIN reservations rs ON rv.reservation_id = rs.reservation_id
WHERE rs.accommodation_id = a.accommodation_id
), 0.0)
""";
em.createNativeQuery(sql)
.executeUpdate();
}
}
✅ 성능 개선 정리
| Version | 개선점 | 특징 | 쿼리 실행 시간 |
| 0 | x | 인덱스 사용 x 풀스캔 및 정렬 비용 발생 |
5.687s |
| 1 | 인덱스 추가 | 커버링 인덱스 및 버리는 행 최소화 풀스캔 및 정렬 비용 발생 |
2.067s |
| 2 | 반정규화 | group by 제거(임시 테이블 및 집계함수 사용 x) 풀스캔 및 정렬 비용 발생 정합성 관리 추가 로직 필요 |
0.730s |
| 3 | 통계 테이블 | 풀스캔(발생하지만 성능 영향 x) 및 정렬 비용 제거 정합성 관리 추가 로직 필요 |
0.001s |
'SQL' 카테고리의 다른 글
| [SQL] MySQL 격리 수준 (0) | 2025.12.26 |
|---|