MySQL - LIKE 문에서 전문검색으로의 전환

FULL TEXT INDEX

검색을 개선해보자

데이터가 점차 쌓이다 보니 슬로우 쿼리도 생기고 정확도 순으로의 검색도 도입해보자
LIKE문 에서 FULLTEXT조회로 변환한다

전문검색

MySQL 서버의 인덱스라 하면 B-Tree 자료구조로 단어를 검색이 가능하지만 MySQL 서버는 용량이 큰 문서를 단어 수준으로 잘게 쪼개 문서검색이 가능한데 이것을 전문검색(Full-text Search)라고 한다.

예전 버전의 MySQL서버는 일부 스토리지 엔진에서만 제공됐던 전문검색 기능을 8.0 버전부터 InnoDB 스토리지 엔진에서도 사용 가능하도록 개선되었다.

문서의 단어들을 분리해 형태소를 찾고 인덱싱하는 방법은 서구권 언어에서는 적합하지만 한국어에는 적합하지 않다. 이것을 보완하기 위해 MySQL 8.0에서부터 형태소나 어원과 관계없이 특정길이의 조각(Token)으로 인덱싱하는 n-gram 파서가 제공된다.

2가지 알고리즘으로 인덱싱할 토큰을 분리하는데

• 형태소 분석
• n-gram 파서

형태소 분석은 공백과 같은 띄어쓰기 기준으로 단어를 분리, 조사를 제거해 명사나 어근을 찾아서 인덱싱하는 알고리즘이다.
n-gram은 문장 자체에 대한 이해없이 공백과 같은 띄어쓰기 단위로 단어를 분리, 그 단어를 단순히 주어진 길이로 쪼개 인덱싱하는 알고리즘이다.
(n-gram의 n은 1~10의 숫자값, default는 2)

전문검색 인덱스 생성시 WITH PARSER ngram 옵션을 추가해야한다.

CREATE TABLE article (
    id BIGINT NOT NULL AUTO_INCREMENT,
    title VARCHAR(100),
    contents VARCHAR(1000),
    PRIMARY KEY(id),
    FULLTEXT INDEX fx_article(title, contents) WITH PARSER ngram
);

INSERT INTO article VALUES (NULL, '애국가', '동해물과 백두산이 마르고 닳도록...');

검색시 검색어의 길이가 ngram_token_size 와 같거나 커야 검색이 가능하다.

SELECT COUNT(*) FROM article WHERE MATCH(title, contents) AGAINST ('백두산이' IN BOOLEAN MODE);

검색어가 본문 단어의 시작뿐 아니라 중간, 마지막 부분이어도 검색이 가능하다.
fulltext index는 이미 2글자씩 잘라낸 토큰(ngram_token_size=2)을 인덱스에 저장하고
검색 쿼리시 인덱싱할 때와 동일하게 검색어를 ngram_token_size 시스템 변수값에 맞게 토큰을 자른다.
잘려진 토큰들을 전문검색 인덱스를 이용해 동등비교 검색한다.
검색된 결과들을 도큐먼트id로 그룹핑하고 그 결과에서 각 단어의 위치를 이용해 최종 검색어를 포함하는지 식별한다.
ngram_token_size 시스템 변수는 테이블 생성시, 데이터 저장시, 쿼리 실행시 모두 동일한 값으로 유지되어야한다.