[트러블슈팅] 인덱스로 쿼리 최적화하기

최종 프로젝트 wibby - 스터디 통합 관리 플랫폼 프로젝트를 다시 살펴보면서 개선할 부분을 찾던 와중

관리자의 스터디 관리 페이지에 스터디의 개수가 많아진다면 성능이 떨어질 것이라 생각하였다.

이 페이지에서는 10개씩 offset 페이징을 하고 있으며 카테고리 필터링과 스터디 이름 검색이 가능하다.

 

문제 인식

study 테이블에 약 삼백만개의 데이터가 있다.

 

SELECT s.id,
       s.name,
       s.category,
       s.finished,
       s.activated
FROM study s
WHERE s.name LIKE '%자바%'
  AND s.category = 'IT'
ORDER BY s.created_date DESC
LIMIT 10 OFFSET 30;

스터디를 '자바' 검색어와 IT 필터링을 해서 조회하는 쿼리이다.

4번째 페이지를 조회하고 있다.

 

쿼리를 실행하니 5초나 걸리는 것을 확인할 수 있다.

 

실행 계획

SELECT문 앞에 EXPLAIN 을 붙이면 실행 계획을 확인할 수 있다.

 

s(study 테이블)

type : ALL (풀 테이블 스캔)

key : NULL (인덱스 사용 X)

rows : 약 2915634건 읽을 것으로 예상

filtered : 1.1% (WHERE 조건 통과율 추정)

extra : Using where (조건으로 필터링), Using filesort (정렬을 인덱스로 못해서 별도 정렬 수)

-> 풀스캔 + 정렬이라서 데이터가 많아질수록 성능 떨어짐

 

실행 계획 분석

SELECT문 앞에 EXPLAIN ANALYZE을 붙이면 실행 계획 분석을 할 수 있다.

 

-> Limit/Offset: 10/30 row(s)  (cost=318786 rows=10) (actual time=5983..5983 rows=10 loops=1)
    -> Sort: s.created_date DESC, limit input to 40 row(s) per chunk  (cost=318786 rows=2.92e+6) (actual time=5983..5983 rows=40 loops=1)
        -> Filter: ((s.category = 'IT') and (s.`name` like '%?자바%'))  (cost=318786 rows=2.92e+6) (actual time=0.888..5142 rows=3e+6 loops=1)
            -> Table scan on s  (cost=318786 rows=2.92e+6) (actual time=0.87..4147 rows=3.01e+6 loops=1)

 

name LIKE '%자바%' 조건과 category 조건이 인덱스를 타지 못해 study 테이블 약 301만 건을 풀스캔하고

created_date DESC 정렬을 filesort로 처리하며

offset 30으로 앞에 30개는 버리고 31 ~ 40번째 row만 반환하여 총 5초가 걸린 것을 확인하였다.

 

(실행 계획은 DB에 쌓인 통계 정보를 바탕으로 옵티마이저가 미리 계산해 놓은 정보이기 때문에 실제와는 차이가 있다)

 

인덱스 적용

CREATE INDEX idx_study_created ON study (created_date DESC);

인덱스를 걸고 다시 실행 계획 분석을 보았다.

카테고리 필터링이 자주 사용된다면 (category, created_date) 복합 인덱스를 고려해야 하지만

현재의 위비 프로젝트에서는 카테고리 필터링 활용도가 낮아

단일 인덱스(created_date)만으로도 충분한 성능 개선 효과를 얻을 수 있을 것으로 판단했다.

 

추후에 카테고리별 관리/통계 기능 등 카테고리 필터링이 자주 사용되는 기능이 추가된다면

그때 복합 인덱스를 도입하는 방안을 검토할 예정이다 !

 

-> Limit/Offset: 10/30 row(s)  (cost=4.33 rows=0) (actual time=0.157..0.217 rows=10 loops=1)
    -> Filter: ((s.category = 'IT') and (s.`name` like '%?자바%'))  (cost=4.33 rows=0.444) (actual time=0.137..0.213 rows=40 loops=1)
        -> Index scan on s using idx_study_created  (cost=4.33 rows=40) (actual time=0.129..0.194 rows=40 loops=1)

 

정렬 비용(filesort)가 인덱스 idx_study_created 덕분에 사라진 것을 확인할 수 있다.

 

쿼리 수행 시에는 0초가 걸리는 것을 확인할 수 있다 !!!

 

---

 

인덱스 덕분에 정렬 비용이 줄어 성능이 개선된 것을 확인할 수 있었다.

 

하지만 필터링된 결과가 40행 -> 그 중 OFFSET 30 LIMIT 10 때문에 30개 건너뛰고 10개만 반환한다.

그렇기 때문에 페이징 최적화 또한 고민해봐야한다.

 

다음 포스팅에서는 페이징 최적화에 대해서 알아볼 예정이다. 😊😊