MySQL Full-Text Search

surri

2023 May 20, Saturday

도입배경

새로운 게시글이 등록될 경우 지정된 키워드가 포함된 게시글을 조회하고 있는데
키워드가 포함된 게시글을 조회할 때마다 테이블의 모든 행을 검색해야한다면
성능상의 문제가 발생할 수 있다.

이로인해 테이블의 모든 행을 검색하지 않고도 키워드를 기준으로 검색할 수 있는
Full-Text Search를 도입하기로 했다.

MySQL Full-Text Search

MySQL은 Full-Text Search 라는 전체 텍스트 인덱싱 및 검색을 제공한다.
전체 텍스트 인덱싱은 InnoDB 또는 MyISAM 테이블에서만 사용할 수 있으며,
CHAR, VARCHAR 또는 TEXT 열에 대해서만 생성할 수 있다.

Natural Language Mode (자연어 모드)

Full-Text Search는 기본적으로 Natural Language Mode를 사용한다.
사용자가 입력한 검색어를 자연어로 해석해 해당 단어가 포함된 행을
연관성이 높은 순으로 정렬한다.

SELECT title FROM articles
WHERE MATCH (title) AGAINST ('여러분' IN NATURAL LANGUAGE MODE);

결과

SELECT title FROM articles
WHERE MATCH (title) AGAINST ('안녕' IN NATURAL LANGUAGE MODE);

결과

token size

Full-Text Search는 너무 짧은 단어는 무시되는데 InnoDB의 경우 3글자가 기본이다.

[mysqld]
innodb_ft_min_token_size=2

다음과같이 설정하면 2글자 이상의 단어를 검색할 수 있다.
값을 변경하고 나면 인덱스를 재생성해야한다.

ngram parser

MySQL의 내장된 Full-Text Search는 공백을 기준으로 단어를 구분하기 때문에
개별 단어에 고정된 구분자가 없는 CJK(Chinese - Japanese - Korean, 중국·일본·한국)는 제대로 동작하지 않는다.
이로인해 MySQL 5.7.6부터 ngram parser를 지원해 이를 해결할 수 있다.

get on the "plane"
get off the "plane"
"비행기를" 탔다
"비행기가" 떠났다

다음과같이 인덱스 생성시 WITH PARSER ngram 을 추가해주면 된다.

ALTER TABLE articles ADD FULLTEXT INDEX `articles_index` (`title`) WITH PARSER ngram;

ngram parser 를 사용하면 기존 innodb_ft_min_token_size의 영향을 받지 않고
ngram_token_size 로 설정할 수 있다.

score

SELECT title, MATCH (title) AGAINST ('안녕하세요' IN NATURAL LANGUAGE MODE) AS score FROM articles

다음과같이 검색어에 대한 연관성 점수를 확인할 수 있다.

Boolean Mode

SELECT title FROM articles
WHERE MATCH title AGAINST ('+안녕 -여러분' IN BOOLEAN MODE);

BOOLEAN MODE는 검색어를 AND, OR, NOT, +, - 등의 연산자를 사용해
조합할 수 있으며, 연산자를 사용하지 않으면 기본적으로 OR 연산을 수행한다.

Query Expansion (쿼리확장)

쿼리 확장은 사용자가 입력한 검색어를 기반으로
연관된 단어를 찾아 검색어를 확장해 검색하는 방법이다.
함께 자주 사용되거나 유사한 단어를 찾아 검색어를 확장해 검색한다.

SELECT * FROM articles
WHERE MATCH (title) AGAINST ('여러분' WITH QUERY EXPANSION);

LIKE vs Full-Text Search

SELECT title FROM articles WHERE title LIKE '%안녕%';

좌측의 와일드 카드를 사용한 LIKE 검색은
"안녕" 앞에 어떤 키워드가 올지 예상할 수 없기 때문에
인덱스를 사용할 수 없고 테이블의 모든 행을 검색해야한다.

반면 Full-Text Search는 키워드를 기준으로 인덱싱을 하기 때문에
테이블의 모든 행을 검색하지 않고도 검색할 수 있다.
데이터가 많아질수록 Full-Text Search의 장점이 더욱 부각된다.

참고

https://dev.mysql.com/doc/refman/8.0/en/functions.html (opens in a new tab)