인덱스를 이용한 쿼리 튜닝

API 개발부터 리펙토링까지 어느정도 프로젝트가 완료되었습니다. 그러면서 자연스럽게 성능 이슈에 대해 관심을 가지게 되었습니다. WAS Thread Pool, Connection Pool 등등 다양하게 성능을 튜닝할 수 있지만 가장 극적인 효과를 볼 수 있는 SQL 쿼리 튜닝을 한번 해보았습니다.

어떤 쿼리에서 많은 시간이 소요되는 지 알아보기 위해서 DB에 많은 Row를 넣고 여러 API를 호출해보았습니다. 일반적으로 findById와 같이 PK기반으로 하나의 Row를 찾거나, insert/update/delete에 큰 문제는 없었습니다. 병목지점이 발생하는 부분은 많은 조인과 페이지네이션, 검색하는 부분이었습니다.

가장 크게 소요시간이 걸린 것은 커뮤니티 / 게시글을 검색하는 부분이었습니다.

커뮤니티 검색과 게시글 검색의 로직이 매우 흡사하여 커뮤니티 검색만 소개 해보도록 하겠습니다.

리스팅 조건
- 검색 키워드
  - 커뮤니티 이름이 해당 키워드를 가지거나,
  - 커뮤니티의 해시태그가 해당 키워드와 같은 경우
- 커뮤니티의 카테고리
- 공개 / 비공개 여부

다이어그램 : community 1 : N community_hashtag입니다.

기존 쿼리입니다.

select *
from community c
where c.category = 'HOBBY'
  and (c.community_name like '%맛집탐방%'
    or c.community_id in
       (select ch.community_id
        from community_hashtag ch
        where ch.tag = '맛집탐방'))
order by c.created_at desc
limit 3;

커뮤니티의 카테고리가 취미이면서
(커뮤니티 이름에 ‘맛집탐방’이 포함 || 커뮤니티 해시태그에 ‘맛집탐방’이 있다)

이 두가지 조건에 만족해야 합니다.

튜닝 전 인덱스

현재 인덱스를 한번 살펴보도록 하겠습니다.

community_hashtag의 경우 다음과 같은 유니크 인덱스가 설정되어 있습니다.

하나의 community에 대해서 같은 tag를 여러개 가질 필요가 없습니다.

(1, “태그1”), (1, “태그1”)처럼 같은 태그 중복해서 있는게 아니라, (1, “태그1”), (1, “태그2”)로 존재해야합니다. 그래서 유니크 속성을 이용했습니다. MySQL의 경우 유니크 제약조건을 이용하면 해당 필드는 자동으로 INDEX가 생성됩니다. 이러한 이유로 생성된 인덱스입니다.

인덱스에 tag가 설정되어 있어서 빠른 속도가 나올 것으로 예상됩니다.

실행계획을 한번 보도록 하겠습니다.

community_hashtag를 보면 인덱스 풀 스캔을 이용합니다. 정상적으로 인덱스를 사용하는 것 처럼 보입니다.

한번 실행 시간을 보겠습니다.

테스트 환경은 다음과 같습니다.

community_hashtag : 500만개
community : 100만개
서버 환경 : AWS RDS db.t2.micro (vCPU 1 / RAM 1G), MySQL8

아래 시간을 보면 18초가 소요된 것을 볼 수 있습니다. 정상적으로 인덱스를 활용하지 않은 것 같습니다.

이 원인을 분석하고 해결해보도록 하겠습니다.

복합인덱스의 순서

현재 인덱스가 (community_id, tag)로 설정되어 있습니다.

tag로 검색을 하기 때문에 인덱스를 이용해서 범위를 줄일 수 없습니다. 즉, 범위 결정 조건이 아니라 체크 조건으로만 작용합니다.

복합인덱스를 잘 사용하기 위해서는 앞 칼럼에 대한 조건이 반드시 있어야합니다.

(community_id, tag)에서 순서를 바꿔 (tag, community_id)로 구성해보도록 하겠습니다.

18초가 걸린 쿼리가 1.8초로 감소되었습니다. 테스트를 위해서 Force index를 이용해서 인덱스를 태우도록 하였습니다.

community_id 인덱스를 추가하고, (tag, community_id)의 인덱스로 기존의 복합인덱스의 순서를 변경하면 됩니다.

기존 쿼리 변경 없이, 인덱스만으로 성능을 개선할 수 있었습니다.

추후 많은 트래픽이 발생하는 업무에 투입되어 이러한 작업을 해보고 싶다는 생각을 하는 계기였습니다.

'프로젝트 > 부기온앤온' 카테고리의 다른 글

HikariCP가 Pending되는 현상과 해결과정 (0)	2023.04.08
부기온앤온 프로젝트 회고 (0)	2023.04.06

CodeBase