검색속도 향상을 위한 MySQL fulltext index와 match ~ against

회고덕에 검색 기능이 생겼다. 어떻게 하면 성능을 향상시킬 수 있을까?

Like를 통한 초기 설계

• SQL문의 Like를 이용한다.
• Page<Template> findByTemplateTitleContaining(PageRequest pageRequest, String query);

성능 측정 결과

• RPS : 16

• Response Time : 14 (s)

  

• 대체로 많은 부하가 DB 처리로 인해 발생됨을 확인할 수 있다.

Fulltext Search - fulltext index와 match ~ against 적용

fulltext search란?

• 문서의 내용 전체를 인덱스화하여 특정 키워드가 포함된 문서를 검색하는 인덱싱 알고리즘
• MySql InnoDB 5.6 버전에 fulltext index와 match ~ against키워드를 통해 추가되었다.

인덱스 알고리즘의 종류

• 구분자 ( Stopword ) 기법
  • 구분자를 통해 토큰을 추출해내고, 결과를 인덱스로 생성한다.
  • 공백, 탭, 마침표와 같은 문장 기호나 사용자가 정의한 문자열을 구분자로 등록할 수 있다.
  • 구분자를 통한 키워드 추출만 추가로 필요할 뿐, 내부적으로는 B 트리를 그대로 이용한다.
• N - Gram 기법
  • 본문을 사정 지정된 개수만큼 잘라 각 토큰을 인덱스로 생성한다.
  • 구분자기법과 달리 검색어의 일부만 포함되어있어도 검색결과를 도출할 수 있다.
  • 구분자기법과 달리 알고리즘이 복잡하고, 만들어지는 인덱스의 크기도 상당히 크다.
  • 일반적으로는 2글자 단위로 자르는 2 - Gram ( Bi - Gram ) 방식이 많이 사용된다. MySQL의 기본값도 2이다.
  • 최대 3글자부터 84글자까지 설정할 수 있다.
  • NATURAL LANGUAGE MODE
    • 만들어진 토큰 중 하나라도 포함된 행을 모두 찾는다.
    • 검색의 정확도에 따라 내림차순 정렬된다.
    • 검색어도 분할, 본문도 분할하여 하나라도 일치한다면 모두 조회되기 때문에 관련성이 낮은 검색결과도 도출될 수 있다.
  • BOOLEAN MODE
    • 본문 분리 이후 추가적인 검색 규칙을 적용한다.

MySQL에서 N - Gram 기법 적용하기

1. 검색에 이용될 컬럼에 fulltext index 적용

   alter table template
       add fulltext index ft_index(template_title) with parser ngram;
   

2. MySQL 쿼리 작성하기

    @Query(nativeQuery = true,
        value = "select * from template where match(template_title) against(:query)",
        countQuery = "select count(*) from template where match(template_title) against(:query)"
    )
   

   • 페이지네이션 적용을 위해 카운트쿼리를 추가로 작성한다.

성능 측정 결과

• RPS : 230

• Response Time : 1.7 (s)

  

• RPS가 약 14배 증가했음을 확인할 수 있다.

• Response Time이 약 8배 감소했음을 확인할 수 있다.