[Linkllet] 검색 쿼리 개선하기 (by 커버링 인덱스)

우선 이번 테스트는 M1 맥북, 메모리 16G의 노트북에서 docker 환경의 MySQL에서 수행되었습니다.

전체 article 데이터 약 87만건을 기준으로 실험하였습니다.

 

1. 문제 되는 상황

우선 원래의 "프로그램"을 검색하는 query는 다음과 같다.

select
    a.article_id,
    a.title,
    a.link,
    a.created_at
from
    article a
where
    a.member_id = 1 and (a.title like '%프로그램%' escape '!')
order by
    a.created_at asc;

실행 결과는 다음과 같다. 약 2.4초가 걸렸으며, 데이터의 건수는 485건이 조회된다.

 

검색 쿼리의 실행계획은 다음과 같다. 몇 가지 살펴보자!

type이 ALL로 되어 있는거 보니 full scan하고 있다는 점이 분명하다.... 

심지어 Storage Engine에서 모든 article을 다 퍼올린 후에 1.11%만 필터링 한다..... 

이렇게 필터링한 데이터의 건수가 485건이다

Query cost도 97875.23이며, 실행시간으로는 약 2.4초가 걸린다.

조회된 데이터의 건수는 485건이다.

 

이를 MySQL 구조와 함께 살펴보면 다음과 같다!

Storage Engine에서 다 퍼올리고 Query Execution Engine에서 겨우 쓰는 게 1.96%라니... 이 얼마나 낭비되는 I/O 작업이란 말인가??

그도 그럴 것이 index 자체가 article_id와 folder_id에만 걸려있기 때문이다.

 

1 - 1) 튜닝의 아이디어

이번 튜닝의 핵심은 간단하다, article을 필터링하기 전에 member_id로 article을 한번 먼저 필터링 한다는점이 튜닝의 아이디어 이다!

우선 전체 데이터에서 member_id 1번이 쓴 article의 수는 총 8653개 이다.

이는 전체 데이터의 건 수인 87만건 기준으로 약 1%에 해당되는 데이터의 수 이다.

이처럼 소량의 데이터를 먼저 필터링 하는것이 아주 중요하다. 즉,

1) article의 수를 줄인 후

2) 우리가 원하는 "프로그램" 이라는 문자가 포함되었는지 필터링 하는 것 이다!

 

2. 해결하기

우선 다음과 같이 index를 하나 추가해 주자! 비 고유 인덱스를 하나 만들어주었다!

CREATE INDEX non_unique_idx_article_member_id ON article(member_id);

 

다음과 같이 article테이블의 member_id에 index가 추가되었다!

 

변경된 쿼리는 다음과 같다.

select
    a.article_id,
    a.title,
    a.link,
    a.created_at
from
    article a
where
    a.article_id in (select article.article_id from article where article.member_id = 1)
    and (a.title like '%프로그램%' escape '!')
order by
    a.created_at desc;

실행 시 596ms가 걸리게 되었다!! 데이터의 건 수도 이전과 동일하게 485건이 나온다.

실행계획은 다음과 같다.

1) 선행 테이블인 article에서 우선 member_id로 해당 유저의 article_id만 전부 조회한다.

Extra에 보면 using temporary인데, 이는 조회된 article_id로 구성된 임시 테이블이 join buffer에 저장되기 때문이다.

    -> 이때 커버링 인덱스가 적용된다. Extra를 보면 Using index가 적용되어 있기 때문이다!!

 

2) 후행 테이블인 a에서는 primary key를 사용하게 된다.

 

3) join buffer로부터 저장된 선행 테이블의 결괏값과 후행테이블(a)이 nested loop Join을 수행하면서 eq_ref로 1대 1 매칭으로 필터링한다.

 

4) 조인된 결과가 최종적으로 MySQL Query Execution Engine에 도달한 내용이 필터링된 후 (11.11%), 정렬되어 반환된다.

 

3. 성능 개선 분석

그럼 얼마나 개선되었을까??

절대적 지표인 실행 시간만으로만 봐도 4.1배에 가까운 성능 개선을 이룰 수 있게 되었다!!

 

4. QueryDsl 코드

구현된 QueryDsl의 코드는 다음과 같다

@Repository
class ArticleQueryRepository(
    private val queryFactory: JPAQueryFactory
) : QuerydslRepositorySupport(Article::class.java) {

    fun searchAllArticleByKeywords(splitResult: List<String>, memberId: Long): List<ArticleLookupDto> {
        return queryFactory
            .select(
                QArticleLookupDto(
                    article.id,
                    article.title,
                    article.link._value,
                    article.createAt,
                )
            )
            .from(article)
            .where(
                article.id.`in`(JPAExpressions.select(article.id).from(article).where(article.memberId.eq(memberId)))
                    .and(isContainKeyword(splitResult))
            )
            .orderBy(article.createAt.desc())
            .fetch()
    }

    private fun isContainKeyword(keywords: List<String>): BooleanBuilder? {
        val booleanBuilder = BooleanBuilder()
        for (containedName in keywords) {
            booleanBuilder.or(article.title.contains(containedName))
        }

        return booleanBuilder
    }
}