[DB] FULL TEXT INDEX 도입에 관해

현재 원툴에서 키워드 검색 기능을 제공하고 있다. 도면의 제목과 작가를 검색하는 기능으로 LIKE문을 사용하고 있다. LIKE 문의 비용은 원래 크다고 배웠다. 그래서 왜 느린지 원인을 분석하고 Full Text Index 도입에 대해 고민해보고자 한다.

 

Full Text Index란?

긴 문자의 텍스트 데이터를 빠르게 검색하기 위한 MySQL의 부가적인 기능이다. InnoDB의 전체 텍스트 인덱스는 역색인 설계를 따릅니다. 근접 검색을 지원하기 위해 각 단어의 위치 정보도 바이트 오프셋으로 저장됩니다.

 

역 색인(인버트 인덱스)이란?

색인이 데이터에서 키워드를 빠르게 찾는 기법이라면 역 색인은 키워드로 데이터를 찾는 기법을 말한다. 검색 엔진에서 추출된 키워드를 term라고 한다. 즉, 검색 엔진은 특정 문서에서 여러 키워드로 쪼갠어 저장하여 사용한다.

 

데이터를 인덱싱하는 기법

stopword 인덱스

 

Stop-word parser
공백이나 Tab, 문장 기호, 또는 사용자가 정의한 문자열을 기준으로 토큰을 나누는 기법

Call me Ishmael. -> Call / me / Ishmael.

 
N-gram parser
n-gram 기법을 사용하여 할당한 토큰의 크기 n만큼씩 데이터를 인덱스로 파싱해두었다가 사용하는 기법

Call me Ishmael. -> Cal / lme / Ish / mae / l.

 

LIKE '%keyword%' 성능  분석

1,000,000건의 데이터를 기준으로 LIKE 문을 실행해보려고 한다. 기존 도면 검색에 사용한 쿼리는 아래와 같다.

SELECT * 
FROM blueprint
WHERE (blueprint_name LIKE %keyword% OR creator_name LIKE %keyword%)
      AND inspection_status = 'PASSED'

 

-> Filter: ((blueprint.inspection_status = 'PASSED') and ((blueprint.blueprint_name like '%호랑이%') or (blueprint.creator_name like '%호랑이%')))  (cost=123039 rows=102376) (actual time=0.134..17583 rows=152634 loops=1)
    -> Table scan on blueprint  (cost=123039 rows=975677) (actual time=0.113..16981 rows=1e+6 loops=1)

 

16,981ms 정도 소요되는 것을 확인할 수 있다. LIKE 문 대신 풀 텍스트 인덱스 사용 시 얼마나 빨라질까?

 

Full Text Index 적용하기

full text index 추가

인덱스 확인

-> Filter: ((blueprint.inspection_status = 'PASSED') and (match blueprint.blueprint_name,blueprint.creator_name against ('호랑이' in boolean mode)))  (cost=1.05 rows=0.5) (actual time=0.0309..0.0309 rows=0 loops=1)
    -> Full-text index search on blueprint using ft_idx_name_creator (blueprint_name = '호랑이')  (cost=1.05 rows=1) (actual time=0.0224..0.0224 rows=0 loops=1)

수행 시간이 0.224ms 소요되는 것을 확인할 수 있다. 16,981ms -> 0.224ms로 약 99% 향상시켰다. 

 

full text index 크기는 어떨까?

ft index의 크기를 알아보기 위해 아래 쿼리를 통해 확인해보기로 하자.

SELECT
    table_name,
    ROUND(index_length / 1024 / 1024, 2) AS index_size_MB
FROM
    information_schema.tables
WHERE
    table_schema = 'onetool'
  AND table_name = 'blueprint';

 

1,000,000 건의 데이터를 기준으로 모든 인덱스 (주 인덱스 포함)는 60MB인 것을 확인할 수 있다. 생각보다 인덱스의 크기가 무지막지하게 커지지 않아서, 소규모 프로젝트에선 충분히 사용할 가치가 있다고 생각한다.

 

프로젝트 적용에 대해

다른 프로젝트 아티클을 확인해본 결과, 설명과 같이 긴 텍스트의 경우 매우 용량이 커지기 때문에 사용할 수 없는 문제가 발생한다고 한다. 우리 프로젝트의 경우 도면 이름과 작가 이름에 대해서만 FT index를 적용하고 있다. 각 필드는 20바이트로 규정하고 있기 때문에 길어지지 않아 충분히 사용성 있다고 판단했다.

https://github.com/HongDam-org/TWTW/wiki/FULL-Text-Index-%EC%A0%81%EC%9A%A9%EA%B8%B0

FULL Text Index 적용기

 

 

출처

https://scarelt.tistory.com/10

https://velog.io/@choral/Search-Process%EC%83%89%EC%9D%B8-%EC%97%AD%EC%83%89%EC%9D%B8

https://inpa.tistory.com/entry/MYSQL-📚-풀텍스트-인덱스Full-Text-Index-사용법