API 응답 속도 개선하기 (2) - 데이터베이스 인덱싱(indexing), explain

이전 글

[SQL] 연관 관계가 있는 더미 데이터 생성하기

API 응답 속도 개선하기 (1) - Paging과 Fetch Join, JPA N + 1 문제

 

1. 문제 분석

@Query("SELECT s FROM Suggestion s " +
       "JOIN FETCH s.member m " +
       "JOIN FETCH s.position p " +
       "JOIN FETCH p.company c " +
       "ORDER BY s.createdDate DESC")
List<Suggestion> findAllWithMembersPositionsAndCompanies();

지난번에 fetch join을 적용한 jpql 코드이다.
Hibernate는 이를 다음과 같은 SQL로 변환할 것이다. (ManyToOne이기에 DISTINCT가 필요하지 않음)

SELECT s.*, m.*, p.*, c.*  /* FETCH JOIN은 모든 엔티티 전체를 가져온다 */
FROM suggestion s
JOIN member m ON s.member_id = m.id
JOIN position p ON s.position_id = p.id
JOIN company c ON p.company_id = c.id
ORDER BY s.created_date DESC;

어떤 부분에서 추가적인 성능 저하가 일어날 수 있을까 생각해보자

1. JOIN 절
- 조인되는 엔티티에서 fk의 id와 일치하는 row를 찾기 위해 검색하는 과정
- 인덱싱이 안되어 있으면 풀 스캔을 해야 하므로 성능 저하가 일어난다.

2. ORDER BY 절
- suggestion의 created_date를 기준으로 정렬하는 과정
- 페이징이 있는 경우, LIMIT + OFFSET에 의해 정렬 후 필요 없는 데이터를 버리는 작업이 추가되어 추가적인 성능 저하가 있다.

SQL의 EXPLAIN(실행계획)을 사용해서 확인해보았다.

EXPLAIN SELECT s.*, m.*, p.*, c.*
FROM suggestion s
JOIN member m ON s.member_key = m.member_key
JOIN position p ON s.position_id = p.id
JOIN company c ON p.company_id = c.id
ORDER BY s.created_date DESC;

Explain Result

중요한 부분만 살펴보면,

1) type
- ALL: 풀 스캔을 하고 있다. (비효율적이다. -> 튜닝 필요)
- eq_ref: 조인을 할 때 Primary Key를 사용한다. (가장 효율적인 조인 유형 중 하나로, 최소한의 검색만을 하고 있다.)

2) Extra
- Using filesort: ORDER BY에서 인덱스를 사용하지 못하고 임시 테이블을 만들어 정렬한다. 디스크 I/O 증가 및 메모리 사용으로 많은 부하를 일으킨다. (튜닝 필요)
- Using index(커버링 인덱스): 인덱스만으로 쿼리를 처리하고 있다.

결과적으로, 현재 쿼리에서 JOIN으로 인한 성능 저하는 발생하고 있지 않고, ORDER BY 절에서 성능 문제가 발생한다고 정리할 수 있겠다.

 

2. 성능 개선

suggestion 테이블의 created_date에 인덱스를 추가하여 정렬 성능을 높일 수 있다.

// Suggestion.java

@Index(name = "idx_suggestion_created_date", columnList = "created_date")

정렬 기준인 created_date로 인덱스를 만들어주었다.

EXPLAIN SELECT s.*
FROM suggestion s
JOIN member m ON s.member_key = m.member_key
JOIN position p ON s.position_id = p.id
JOIN company c ON p.company_id = c.id
ORDER BY s.created_date DESC

그런데 Explain을 돌려봤는데 정렬 과정에서 계속 filesort(full scan) 되는 문제가 있었다..

뭐가 문제인지 모르겠어서 조인되는 엔티티의 fk와 커버링 인덱스로 구성해야 하나 해서,

@Index(name = "idx_suggestion_with_join_columns", columnList = "created_date, member_key, position_id")

와 같은 형식으로도 테스트해봤는데 결과가 똑같았다.

삽질하다가 데이터 양에 따라 쿼리 옵티마이저가 index를 안 쓸수 있다는 걸 보고,
LIMIT을 걸어서 Explain을 돌려봤다.

EXPLAIN SELECT s.*
FROM suggestion s
JOIN member m ON s.member_key = m.member_key
JOIN position p ON s.position_id = p.id
JOIN company c ON p.company_id = c.id
ORDER BY s.created_date DESC
LIMIT 0, 1000;

테스트 결과 정상적으로 index scan(정확히는 DESC 정렬을 하여 Backward Index Scan)이 된다.
페이징을 사용하고 있고, 페이지 크기가 커봐야 100 정도이기에 결과적으로 인덱싱이 잘 적용된다고 볼 수 있겠다.

LIMIT 값을 키우면서 테스트한 결과 1200 정도가 되면 filesort를 사용한다.
쿼리 옵티마이저가 정렬 과정에서 인덱스만으로 작업을 처리하기 어려워 정렬을 위해 임시 테이블을 생성하는 것이다.

rows를 확인해보면 index scan을 했을 때와는 달리, 1200이 아닌 86717이 찍힌다.
왜 전체 데이터 수인 100,000이 아닐까 궁금하여 찾아보니, MySQL이 각 테이블에서 검색해야 한다고 예측하는 행의 수를 보여주는데 실제 반환되는 행의 수가 아닌 통계 정보를 기반으로 추정한 값이라고 한다.

 

 

3. 성능 테스트

총 데이터 수 100,000개, pageSize = 100 기준으로 페이지를 무작위로 선정하며 API의 응답 속도를 비교해봤다.

	Index 적용 전	Index 적용 후
가장 빠른 응답 시간	401ms	267ms
가장 느린 응답 시간	653ms	353ms
10회 평균 응답 시간	476ms	302ms

평균 응답 시간이 174ms 감소하여, 36.51%의 성능 개선 효과를 보았다.

FETCH JOIN을 적용하기 전까지 포함하여 비교하면,

	Default	FETCH JOIN 적용	Index 적용
가장 빠른 응답 시간	543ms	401ms	267ms
가장 느린 응답 시간	947ms	653ms	353ms
10회 평균 응답 시간	662ms	476ms	302ms

평균 응답 시간이 360ms 감소하고, 54.38%의 성능 개선 효과를 보았다고 정리할 수 있다.

이번 성능 개선을 통해 index와 explain 쿼리에 대해 알게 되었고,
간단한 쿼리라도 explain으로 실행계획을 살펴보는 습관을 가지게 되었다.