MySQL을 사용하고 있는 사이드프로젝트 모카콩에서 아래 Slow Query를 발견했다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
select
comment0_.comment_id as comment_1_2_,
comment0_.created_time as created_2_2_,
comment0_.modified_time as modified3_2_,
comment0_.cafe_id as cafe_id6_2_,
comment0_.content as content4_2_,
comment0_.is_masked as is_maske5_2_,
comment0_.member_id as member_i7_2_
from
comment comment0_
left outer join
cafe cafe1_
on comment0_.cafe_id=cafe1_.cafe_id
left outer join
member member2_
on comment0_.member_id=member2_.member_id
where
cafe1_.cafe_id=530
and member2_.member_id=20 limit 15
|
cs |
해당 쿼리는 `카페 댓글 중 내가 작성한 코멘트만 조회`할 때 사용되는 API 이며, 페이지네이션이 적용돼있다.
스프링부트 환경에서 JPA를 이용하며, commentRepository 클래스에서 findAllByCafeIdAndMemberId() 메서드를 포함하는 API이다.
참고로 Spring Data JPA 환경이다보니 left outer join이 발생한다. (queryDSL 환경일 경우 left outer join은 발생하지 않는다. https://kth990303.tistory.com/378)
해당 쿼리 관련 테이블 인덱스 및 실행계획
슬랙에 알림을 보낼 만한 경고가 뜰만큼 느린 쿼리는 아니지만, 한번 분석해보기로 했다.
현재 comment 테이블에 걸려있는 인덱스 구조는 아래와 같다.
PK로 comment_id 가 잡혀있고, FK로는 cafe_id (cafe 테이블), member_id (member 테이블)이 존재한다.
현재 상태에서 위 Query의 실행계획은 아래와 같다.
Explain 명령어로 실행계획을 조사해보았다. 그랬더니 type은 index_merge, Extra에서 FK를 사용한 Using intersect가 발생한 것을 확인할 수 있다.
아무래도 cafe_id, member_id 로 코멘트를 조회하는 메서드이다보니, FK들이 모두 사용된다.
하지만 각각에 대한 FK index만 가지고 있는 상황이다. uniqueConstraint 제약조건으로 두 컬럼이 묶여있지도 않은 상황이다. 그러다보니 cafe_id + member_id 복합 인덱스는 존재하지 않는 상황.
그러다보니 MySQL 입장에서 마땅히 효율적인 인덱스를 찾지 못해 Using intersect가 발생했다.
- Actual Total Time: 0.326
해결방안: Index_merge 비활성화
cafe_id, member_id 복합 인덱스가 존재하지 않아, MySQL 입장에선 Using intersect를 했다고 한다.
이러한 문제에는 여러 해결책이 존재한다. 당연히 cafe_id, member_id 복합 인덱스를 생성하는 것도 하나의 해결책이다.
하지만 일단 그 해결책은 뒤로 미뤄두고, index_merge에 조금 더 주목해보려 한다.
index merge 과정은 MySQL에서 효율적인 성능을 위해 옵티마이저에서 자체적으로 특정 인덱스들을 병합하여 인덱스를 타게 한 것이라고 한다. 하지만, 이러한 병합 과정이 생각보다 시간이 오래 걸려 비효율적으로 적용되는 경우가 종종 있다.
아래 글에서는 where 절에 사용되는 각 인덱스 항목을 읽고, 병합하는 과정으로 인해 index merge 과정이 느린 이유를 소개하고 있다.
https://www.percona.com/blog/the-optimization-that-often-isnt-index-merge-intersection/
The Optimization That (Often) Isn't: Index Merge Intersection
The basic idea behind index merge is that for certain types of queries which contain WHERE clauses with columns that had single-column indexes on them, MySQL could sometimes make use of the multiple indexes
www.percona.com
또한 아래 글에서는 인덱스를 병합하는 것이 단순 다중 열 인덱스보다 빠른지 항상 검사할 것을 권장하고 있다.
https://logicalread.com/mysql-optimizer-features-mc12/#.YHb2gRQzaDU
MySQL Optimizer Features
Understand how the MySQL optimizer impacts query processing and impacts performance.
logicalread.com
Using intersect가 비교적 느린 편에 속하는 실행계획이기도 하고, 실제로 index merge가 위 쿼리에서 효율적으로 적용됐는지 의문이 들어 index merge를 비활성화하여 결과를 보기로 했다.
index_merge 교집합이 발생하지 않도록 optimizer를 설정할 수 있다.
아래 명령어를 입력하면 된다.
|
1
|
SET optimizer_switch="index_merge_intersection=off";
|
cs |
optimizer_switch를 이용하여 index_merge를 비활성화한 후의 쿼리 실행계획은 아래와 같다.
- type: index_merge -> ref
- Extra: Using intersect -> Using where
index_merge 비활성화가 됨을 확인할 수 있다.
- Actual Total Time: 0.127 (약 61% 성능 증가)
성능이 꽤 좋아진 것을 확인할 수 있다.
또다른 해결방안: 복합 인덱스 생성
사실 위에서 말했다시피 cafe_id, member_id 복합 인덱스가 존재하지 않아 Using Intersect를 수행한 것이기 때문에, 복합 인덱스를 만들어주면 된다.
|
1
|
CREATE INDEX comment_index ON comment (cafe_id, member_id);
|
cs |
cafe_id, member_id 복합 인덱스를 만들어주면 성능은 확실히 더 좋아진다.
- Actual Total Time: 0.109 (약 66% 성능 증가)
마치며
쿼리 옵티마이저가 생각보다 정말 많은 역할을 해주는 것을 새삼 느낀다.
index 관련하여 여러가지 다양한 경우들이 많음에 다시 한번 놀라며, DBA 분들의 존경심 또한 다시 한번 들게 되었다.
TMI 지만 index merge 관련 구글링하면 자료가 많지는 않다.
하지만 사내 Slack에 관련 키워드를 검색하면 자료가 많이 나온다! 사내에 많은 개발자분들께서 열심히 고생하신 이전 기록들이 많이 나오며, 덕분에 좋은 키워드들도 얻어가고 있다. 정말 이 방법이 좋은걸까? 싶은 경우가 많은데 많은 도움이 된다.
사내 Slack이랑 Wiki를 구글링 대신 사용할 정도... 문서화의 중요성을 다시 한번 느끼고 있다.
(참고로 index merge 비활성화는 상황마다 긍정적으로, 부정적으로 적용될 수 있다.
그렇기 때문에 일부 팀에서만 index merge를 비활성화한 듯하다.)