MySQL Full-Text Search를 통한 쿼리 개선

개요

500만개의 레코드에서 키워드 기반 문자열 검색을 수행했을 때, 속도가 저하되는 문제를 MySQL의 Full-Text Search를 통해 해결해보려 한다. 개선 결과 기존 API 처리 시간 1185ms → 564ms로 약 52% 성능 개선이 이뤄졌다.

 

 

기존 쿼리의 문제점

아래는 기존에 사용하던 쿼리이다. 키워드를 기반으로 post 테이블을 검색한다.

explain select *
from post p
join category c on c.category_id = p.category_category_id
where p.title like '%**:keyword**%'
    and p.region_region_id in (**:region_ids**) 
    and p.status not in ('HIDE', 'SOLD')
order by p.updated_at
limit 10;

 

 

해당 쿼리로 API 요청을 처리하면 처리시간이 무려 1185ms 이다. 내 개인적인 기준으로 검색 결과는 1초 이내에 처리되었으면 좋겠는데 아슬아슬하다.

 

문제가 되는 부분은 like 절이다. title 컬럼에 index를 걸어놓아도 title 문자열에 포함되어 있는 특정 문자열을 찾는 로직은 인덱스를 사용할 수 없다. (like ’keyeword%’ 같이 문자열 앞에 있는 특정 문자열을 찾는 로직은 사용가능하다.)

 

이유는 인덱스의 특징과 관련이 있다. 인덱스는 인덱스 키 컬럼을 기준으로 정렬한다. 즉 title 이라는 문자열 컬럼에 인덱스를 걸어놓는다면 title의 문자열 사전 순서대로 정렬된다는 의미이다. 그렇다면 사전순서대로 정렬되어 있는 문자열 집합에서 특정 문자열이 포함된 문자열을 빠르게 찾을 수 있을까? 불가능하다.

 

인덱스를 무조건 사용하도록 강제할 수도 있다. 해당 방식은 이론 상 title 인덱스를 풀스캐하여 조건에 맞는 컬럼을 찾은 뒤 디스크에 접근하여 데이터를 가져와야 한다. 이전 포스팅에서 다루었듯 인덱스를 통해 디스크 데이터에 접근하는 것은 디스크에 다이렉트로 접근하는 것 보다 4~5배정도 느리다. 정말 그런지 확인해보자.

create index test_idx on post(title);  -- title 인덱스를 생성한다.

인덱스를 생성해주고 force index를 사용하여 인덱스 사용을 강제하여 실행계획을 살펴보자.

 

그렇다. 인덱스 사용을 강제해도 옵티마이저가 멍청한 인덱스 사용이라고 생각하고 테이블 풀 스캔으로 처리한다.

 

 

MySQL8.0 Full-Text Search

MySQL :: MySQL 8.0 Reference Manual :: 14.9 Full-Text Search Functions

공식 문서에 의하면 InnoDB와 MyISAM 테이블에서 CHAR, VARCHAR, TEXT 컬럼에 대해 full-text 인덱싱과 검색을 지원한다고 한다. 또, full-text 인덱스를 생성할 때 문자열을 토크나이징해주는 파서가 필요한데, MySQL에 내장된 ngram parse은 영어, 한국어, 일어, 중국어까지 지원한다고 한다.

 

full-text searching 시 MATCH, AGAINST 구문을 사용해야 하며, 아래는 공식문서에 포함된 예제 코드이다.

# MATCH() in SELECT list...
SELECT MATCH (a) AGAINST ('abc') FROM t GROUP BY a WITH ROLLUP;
SELECT 1 FROM t GROUP BY a, MATCH (a) AGAINST ('abc') WITH ROLLUP;

# ...in HAVING clause...
SELECT 1 FROM t GROUP BY a WITH ROLLUP HAVING MATCH (a) AGAINST ('abc');

# ...and in ORDER BY clause
SELECT 1 FROM t GROUP BY a WITH ROLLUP ORDER BY MATCH (a) AGAINST ('abc');

 

 

Full-Text Search 로 검색 기능 개선

full-text search가 무엇인지 알았으니 이제 사용해보자. 먼저 full-text index를 생성해준다. 해당 프로젝트는 한글을 지원하므로 ngram 파서를 사용한다.

CREATE FULLTEXT INDEX ft_idx on post (title) WITH PARSER ngram;

 

기존의 쿼리를 full-text search를 위해 변경한다.

explain select *
from post p
join category c on c.category_id = p.category_category_id
where match(p.title) against('+**:keyword**' in boolean mode)
    and p.region_region_id in (1, 2, 3, 4, 5) and p.status not in ('HIDE', 'SOLD')
order by p.updated_at
limit 10;

 

 

실행 계획을 확인해보면 fulltext 타입 인덱스를 사용하여 전체 레코드 중 25.82%를 필터링할 수 있다고 잘 나온다.

 

Spring 프로젝트에 native 쿼리로 적용한다.

public interface PostRepository extends JpaRepository<PostEntity, Long> {

    @Query(value = """
            select p.*
            from post p
            join category c on c.category_id = p.category_category_id
            where match(p.title) against(concat('+', :keyword) in boolean mode)
                and p.region_region_id in (:regionIds) and p.status not in ('HIDE', 'SOLD')
            order by p.updated_at
        """, nativeQuery = true)
    List<PostEntity> findByKeywordAndRegionIdIn(
        @Param("keyword") String keyword,
        @Param("regionIds") List<Long> regionIds,
        Pageable pageable
    );
}

 

프로젝트를 리빌드하여 결과를 확인해보자

변경된 쿼리로 API 요청을 처리하면 처리시간이 564ms 이다.

기존 쿼리의 처리시간인 1185ms 보다 약 52%의 성능 개선이 이뤄졌다.

 

 

결론

• 텍스트 검색 쿼리를 개선하기 위해 full text index를 사용하여 기존 쿼리 대비 52%의 처리속도 개선을 이뤄낼 수 있었다.
• 보통 높은 성능의 텍스트기반 검색 기능이 요구되는 경우 CQRS 패턴을 적용히여 검색 성능이 좋은 DB를 사용할 것이라고 생각한다. 하지만 모든 상황이 좋게 풀리지는 않는법. RDB에서 적당히 텍스트를 검색해야 할 수도 있을 것이니 이번 기회에 좋은 방법을 하나 알아간다고 생각한다.

 

 

Reference

• https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
• https://velog.io/@juhyeon1114/MySQL-검색-성능-개선하기-like와-full-text-search
• https://www.essential2189.dev/db-performance-fts