[과제] Spring 플러스 프로젝트 Day 8

대용량 데이터 조회 성능 최적화
1. 개요
상황: 서비스 규모 확장에 대비하여 리뷰 데이터 10만 건을 확보하고, 다중 조건(rating, created_at) 조회 시 발생하는 성능 저하 문제를 해결함.

핵심 목표: Full Table Scan을 제거하고, 인덱스를 통해 정렬(Sort) 부하를 최소화함.

2. 트러블슈팅: Using filesort
문제 현상
리뷰 목록을 평점순 + 최신순으로 조회할 때, 데이터가 많아질수록 응답 속도가 눈에 띄게 느려짐.

조회 쿼리: SELECT * FROM reviews WHERE rating = 5 ORDER BY created_at DESC LIMIT 10;

EXPLAIN 분석 결과:

type: ALL (전체 테이블 스캔 발생)

Extra: Using where; Using filesort (DB가 메모리/디스크에서 별도의 정렬 작업을 수행 중)

조회 시간: 약 0.06s (데이터가 늘어날수록 기하급수적으로 증가 위험)

해결 방안 (TO-BE)
조회 조건과 정렬 순서를 모두 고려한 복합 인덱스(Composite Index) 설계.

적용 인덱스: CREATE INDEX idx_rating_created_at ON reviews (rating, created_at DESC);

개선 결과:

type: ref (인덱스를 통한 등가 비교 수행)

Extra: NULL (Filesort 제거 성공! 인덱스 자체에 정렬이 되어 있어 DB 부하 0)

조회 시간: 0.00s ~ 0.01s (약 6배 이상 성능 향상)

3. 대용량 더미 데이터 적재 (Stored Procedure)
JPA로 한 땀 한 땀 넣는 대신, MySQL 프로시저를 활용하여 10만 건의 Orders와 Reviews 데이터를 2.25초 만에 적재함.

Key Point: SET autocommit = 0;과 COMMIT; 단위를 조절하여 대량 인서트 시 발생하는 디스크 I/O 부하를 줄임.

인덱스는 단순히 '빠르게 찾기' 위한 수단이 아니다. 정렬하지 않기 위한 수단이기도 하다. Using filesort가 뜨는 순간, 인덱스 설계가 잘못되었거나 부족하다는 신호임을 다시 한번 확인했다. 내일은 이 탄탄한 DB 위에 Redis라는 날개를 달아보아잇!

 

 

 

실제 실행 화면

 

1. 조회 시간 확인 SELECT * FROM reviews WHERE rating = 5 ORDER BY created_at DESC LIMIT 10;
2. 실행 계획 확인 EXPLAIN SELECT * FROM reviews WHERE rating = 5 ORDER BY created_at DESC LIMIT 10;

인덱스 적용전

EXPLAIN 결과에서 type: ALL이 뜨고, rows: 100000에 가깝게 나오면서 인덱스 없어서 개고생 중이라는 뜻

 

 

복합인덱스 생성

CREATE INDEX idx_rating_created_at ON reviews (rating, created_at DESC);

목록확인

SHOW INDEX FROM reviews;

 

 

인덱스 적용후 조회 실행

SELECT * FROM reviews WHERE rating = 5 ORDER BY created_at DESC LIMIT 10;

 

 

 

실제 적용후 실행계획 조회시 rows만 보아도 39,000 대로 떨어지면서 최적화 성공

XPLAIN 결과에서 type: ref 또는 rang가 뜨고, rows 숫자가 39000 대로 떨어지면서 뚝 떨어지면서 인덱싱 성공

EXPLAIN SELECT * FROM reviews WHERE rating = 5 ORDER BY created_at DESC LIMIT 10;

 

결과

Before: rows: 99388, Extra: Using filesort, Time: 0.06s

After: rows: 39978, Extra: NULL, Time: 0.01s (또는 0.00s)