Mysql 풀텍스트인덱스(Full-Text Index)

LIKE 문을 통한 검색 기능의 문제점

// Index 스캔 가능
SELECT * FROM table WHERE name LIKE 'something%';

// 풀 스캔 때려 버림
SELECT * FROM table WHERE name LIKE '%something%';
SELECT * FROM table WHERE name LIKE '%something';

• %something% 연산은 DB 인덱스의 B+ Tree의 장점을 활용할 수 없고 Full Scan할 수 있는 문제가 있습니다.

 

B+Tree 인덱스 LIKE 연산시 Full Scan 하는 이유

B+ 트리 특성상 리프 노드의 데이터는 사전첨 순차적으로 저장됩니다. 그렇기 때문 %연산이 좌측에 있을 경우 결국 처음 리프노드부터 순차적으로 찾아봐야 되기 때문에 결국 Full Scan 문제가 발생합니다.

 

풀텍스트인덱스(FullText Index)

• 위의 풀스캔 문제를 해결하기 위한 인덱스 방법으로, 여러 데이터베이스(MySQL, Oracle)등에서 지원합니다.
• 일반 인덱스와 차이점은 char, varchar, text타입 문자만 인덱싱이 가능하며, Mysql의 경우 InnoDB와 MyISAM 테이블만 지원
• 풀텍스트인덱스 방식은 역색인 방식을 방식으로 문장의 키워드들에 대해서 인덱스를 만드는 방식으로 검색하고자 하는 Column에 FullText Index를 설정해주면, 문자열이 정해진 방법으로 분리되어 인덱스를 생성하고, 이를 빠르게 검색할 수 있습니다.

풀텍스트인덱스 Parser 종류

• 인덱스는 파서가 문자열을 Tokenizing 한후에 생성됩니다.

Built-In Parser

• Built-In Parser는 정관사나 부정관사 등을 제외한 stop word(구분자)를 기준으로 키워드를 추출하는 방식
• 공백이나 문장 기호 혹은 사용자가 지정한 특정 단어를 기준으로 토크나이징하게 됩니다.

정승제는 집에서 넷플릭스를 본다 -> 정승제는 / 집에서 / 넷플릭스를 / 본다
우리는 치즈케이크를 만들었다 -> 우리는 / 치즈케이크를 / 만들었다

• 위의 같은 방식으 토크나이징 된다면 "승제", "케이크" 와 같은 검색 키워드로는 위 문장을 검색할 수 없습니다.
• FullText Search는 토큰과 검색 키워드가 전부 일치하거나 전방(prefix) 일치한 경우에만 결과를 가져오기 때문입니다.

N-gram Parser

• 위와 같은 문제를 해결해주기 위해 N-gram parser가 등장하는 데, 해당 파서는 N크기로 문자열 토큰사이즈를 기준으로 키워드를 추출합니다.

N : 2인 경우
정승제는 집에서 넷플릭스를 본다 -> 정승 / 승제 / 제는 / 는집 / 집에 /에서 / 서넷 / 넷플 / 플릭 / 릭스 / 스를 / 를본 / 본다
우리는 치즈케이크를 만들었다 -> 우리/ 리는 / 는치 / 치즈 / 즈케 / 케이 / 이크 / 크를 / 를만 / 만들 / 들었 / 었다

위와 같은 방식으로 토크나이징 된다면 stop word 방식의 문제를 해결할 수 있습니다. 하지만 stop word에 비해 인덱스의 크기가 매우 커지는 문제가 있습니다.

 

 

대소문자

• 자언어 검색은 기본적으로 대소문자를 구분하지 않는 데, 이때 구분하기 위해 binary collation을 사용할 수 있습니다.
• 참고: https://hoing.io/archives/8110

무시되는 검색어

• 길이가 기준보다 짧거나, 구분자(Stopword)는 풀텍스트 검색에서 무시됩니다.

짧은 단어의 경우

mysql> show variables like '%ft_min%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| ft_min_word_len          | 4     |
| innodb_ft_min_token_size | 3     |
+--------------------------+-------+
2 rows in set (0.02 sec)

• fit_min_word_len의 기본값인 4로 지정되어 있는데, 이 경우 4글자 이하의 문자는 무시됩니다.

구분자의 경우

mysql> select * from INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
+-------+
| value |
+-------+
| a     |
| about |
| an    |
...

 

매치율

• 매치율이란 풀 텍스트 검색에서 검색어와 검색 대상 텍스트 간의 유사도를 나타내는 지표로, 일반적으로 검색어와 검색 대상 텍스트 간의 일치 정도를 나타내며, 검색 결과의 상대적인 우선순위를 결정하는 데 사용 될 수 있습니다. 
• 매치율은 보통 검색어와 검색 대상 텍스트 간의 유사도를 특정 알고리즘을 사용하여 계산하는데, 텍스트 간의 단어 일치 여부, 단어의 중요도 및 가중치, 문맥 및 문장 구조등을 고려하여 매치율을 계산할 수 있습니다.
• 보통 매치율은 0에서 1사이 값으로 나타내며, 1에 가까울 수록 검색어와 검색 텍스트가 더 유사함을 나타냅니다.
• 검색 결과는 가장 높은 관련성을 가진 결과부터 자동 정렬되는 데, 아래와 같은 조건에 한해 자동 정렬됩니다.
  • ORDER BY 절이 없음
  • 검색은 테이블 검색이 아닌 FULLTEXT Index를 사용할까
  • 쿼리가 테이블을 조인하는 경우, FULLTEXT Index는 조인에서 가장 왼쪽에 있는 non-constant 테이블이어야 합니다.

mysql> SELECT match(name) AGAINST('맨투맨') AS score FROM Product LIMIT 0, 10;
+--------------------+
| score              |
+--------------------+
| 0.5844167470932007 |
| 0.5844167470932007 |
|                  0 |
|                  0 |
|                  0 |
| 0.5844167470932007 |
|                  0 |
| 0.5844167470932007 |
|                  0 |
|                  0 |
+--------------------+
10 rows in set (0.00 sec)

 

풀텍스트 인덱스 사용하기

검색 최소 글자수, 토큰 사이즈 변경

my.cnf 수정

ft_min_word_len=2
innodb_ft_min_token_size=2

SET PERSIST 사용

SET PERSIST _only ft_min_word_len=2;
SET PERSIST _only innodb_ft_min_token_size=2;
// mysql-auto.cnf파일에 저장됨
// 현재 실행중인 서버는 변경내용을 저장하지 않고 다음 재시작에 적용됨

 

FullText Index 적용

ALTER TABLE product ADD FULLTEXT name_idx (name) WITH PARSER ngram;

 

 

- Fullext Index가 결려있는 column에 한해서 MATCH (...) AGAINST (...)를 사용해서 검색을 이용할 수 있습니다.

SELECT * FROM TABLE
WHERE MATCH(column) AGAINST ('keyword' IN NATURAL LANUGUAGE MODE)

SELECT * FROM TABLE
WHERE MATCH(column) AGAINST ('keyword' IN BOOLEAN MODE)

 

Search Type

IN NATURAL LANGUAGE MODE

- 검색 키워드를 토큰 사이즈로 분리한 후, 분리된 단어 중에서 하나라도 포함되는 데이터를 찾음

- 해당모드는 기본값

 

IN BOLEAN MODE

SELECT * FROM TABLE
WHERE MATCH(column) AGAINST ('+A -B' IN BOOLEAN MODE)

- 검색 키워드를 토큰 사이즈로 분리한 후, 추가적인 검색 규칙을 적용해서 단어가 포함되는 데이터를 찾음

- 위와 같이 검색 규칙은 A는 포함하지만 B는 포함하지 않는 데이터를 검색합니다.

- 이외에도 여려가지 검색 규칙이 존재합니다.

Operate	Description
+	반드시 포함하는 단어
-	반드시 제외하는 단어
>	포함하면서 검색 순위를 높일 단어
<	포함하지만 검색 순위를 낮출 단어
()	하위 표현식으로 그룹화
~	'-' 연산자와 비슷하지만 제외 시키지는 않고 검색 조건을 낮춤
*	와일드 카드
""	구문 정의