MySQL FullText Search 전문검색 기능 (1) - 인덱싱 방식 - 빌트인 전문 검색 - Stopword

Last Updated on 8월 15, 2023 by Jade(정현호)

1. FullText Search(전문검색)

전문검색 이란 게시물의 내용이나 제목 등과 같이 문장이나 문서의 내용에서 키워드를 검색하는 기능입니다.

전문검색은 이름이나 별명(닉네임) 과 같은 단어에서 일부만 일치하는 사용자를 검색하는 기능으로도 사용 할 수 있습니다.

LIKE 기능과 같이 패턴 일치 검색 기능(양쪽에 %% 를 사용한)은 인덱스를 사용하지 못할 수도 있지만 전문 검색은 일부만 검색하는 경우에도 전문 검색 인덱스를 이용할 수 있기 때문에

Like %패턴% 과 같은 조회 요건에 대해서 더 빠른 검색이 가능 합니다.
       

2. 인덱싱 방식

인덱싱 방식 이란 본문에서 키워드를 분석해서 인덱스를 구축 할 때 어떤 알고리즘을 사용할지를 의미 합니다.

stop word는 구분자라고도 표현하는데 대표적으로 띄워쓰기나 문장 기호 등을 기준으로 키워드를 추출해내고 그 결과를 인덱스로 구축하는 방식입니다. 이러한 Stop word 방식은 키워드가 전부 일치하거나 prefix(전방) 가 일치할 때만 결과를 가져올 수 있습니다.

즉 컴퓨터 와 슈퍼컴퓨터 라는 키워드가 포함된 레코드 2건이 있을 때 "컴퓨터" 라는 단어로 검색하면 "슈퍼컴퓨터" 는 검색 대상에서 제외됩니다.

그래서 MySQL 빌트인 전문 검색 엔진을 많이 사용하지 않는 이유이기도 하였습니다(5.7 이전까지는)

그에 반해 MySQL 5.1, 5.5, 5.6 버전 대에서는 외부 파서인 트리톤과 mGroonga는 N-그램 방식의 인덱싱을 지원하기 때문에 단어나 어휘를 고려하지 않고 본문의 내용을 모두 잘라서 인덱스를 만들어서 사용할 수 있었습니다.


N-그램 방식은 단순하고도 과도한 방식 이기도 하지만 전세계 적으로 사용되고 있는 언어의 개수와 문장의 복잡성을 고려해 본다면 이보다 더 좋은 방법이기도 합니다.

그래서 N-그램 방식에서는 "컴퓨터" 라는 검색어로 전문 검색을 수행하면 "슈퍼컴퓨터" 레코드까지 결과로 가져올 수 있습니다. 또한 트리톤이나 mGroonga 의 구분자 방식의 인덱싱은 MySQL 빌트인 전문검색엔진과 같이 띄어쓰기나 특수 문자로 단어를 구분해서 전문 인덱스를 구축하는 방식을 의미합니다.

* 포스팅에서는 트리톤이나 mGroonga 에 대해서는 다루지 않습니다.
      

3. MySQL 빌트인 전문 검색

MySQL의 빌트인 전문 검색 기능은 MySQL5.5 버전까지는 MyISAM 스토리지 엔진을 사용하는 테이블에서만 사용할 수 있었습니다.

MySQL 5.6 버전 부터는 빌트인 전문 검색 기능이 InnoDB 에서도 사용이 가능하도록 기능이 추가되었고 MySQL 5.7 부터는 중국어/한글/일본어(CJK)를 대응할 수 있는 Parser로 N-gram이 설치되어 있으며, MeCab(은전한닢)도 플러그인으로 사용이 가능 합니다.

* 기본적으로 InnoDB의 전문검색은 CJK(중국, 일본, 한국)을 지원하나, N-gram 한정이며, 기본 탑제된 Mecab 은 일본어만 지원합니다.


MySQL 5.6 버전 까지는 기본적으로 Full-Text Parser를 사용하고 있었기 때문에 단어가 끝이 명확하지 않으면 FullText 인덱스를 사용하기 위해 단어를 공백으로 구분하거나 N-gram에서 분할한 상태에서 DB에 저장하는 등의 조치가 필요하였습니다.

5.5 버전 기준으로 빌트인 전문 검색 기능은 MyISAM 스토리지 엔진을 사용하는 테이블에서만 사용할 수 있었으며, 또한 stopwords 기반의 키워드 추출 알고리즘을 사용하기 때문에 지정된 구분자에 의해서만 인덱싱 처리하여 사용하였습니다.

MySQL 에서는 이미 기본 stopwords 를 제공하고 있으며 기본적으로 "a","the" 또는 "by" 등과 같이 검색어로서는 아무런 의미가 없는 단어나 띄워쓰기나 공백 또는 탭과 같은 문자로 구성되어 있습니다.

* 기본 제공의 stopword는 INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD 를 통해 확인 하실수 있습니다.
* stopwords 에 대해서 불용어 라고 해석 되나 참조한 책에 의견을 따라 구분자 라는 표현도 같이 사용하도록 하겠습니다.


단순히 공백이나 띄어씌기 또는 특수문자로 키워드를 추출하는데 충분하다면 별 다른 변경없이 MySQL 빌트인 전문검색 엔진을 그대로 사용할 수 있습니다.

MySQL에서는 기본 빌트인 전문검색 엔진 과 N-Gram 파서 둘 중에서 선택해서 사용할 수 있습니다.
      

4. Stopword 처리

빌트인 전문 검색 엔진 에서 사용되는 구분자는 기본적으로 영문자를 대상으로 준비되어 있습니다.

기본 구분자(stopwords) 대신에 새로운 구분자를 사용하려면 MySQL 에서는 별도의 stopwords의 두 가지 사용 방식을 지원합니다.


4-1 서버에서 기본 제공하는 stopwords 

일단 먼저 이것은 서버에서 제공되는 고정적인 stopwords 목록입니다. 사용자 stopword가 지정되지 않은 경우라면 기본 stopword 목록이 사용됩니다.

INFORMATION_SCHEMA 테이블을 조회하여 목록을 확인할 수 있습니다

mysql> select * from INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
+-------+
| value |
+-------+
| a     |
| about |
| an    |
| are   |
| as    |
| at    |
| be    |
| by    |
| com   |
| de    |
| en    |
| for   |
| from  |
| how   |
| i     |
| in    |
| is    |
| it    |
| la    |
| of    |
| on    |
| or    |
| that  |
| the   |
| this  |
| to    |
| was   |
| what  |
| when  |
| where |
| who   |
| will  |
| with  |
| und   |
| the   |
| www   |
+-------+

4-2 사용자 정의 stopwords 

사용자 정의는 2가지의 방법으로 사용할 수 있으며 테이블을 만들어서 사용하는 방법과 시스템 상에서 외부 파일을 생성하여 파일을 읽어 들이는 방식으로 사용할 수 있습니다.


4-2-1) InnoDB 사용시 

사용자 정의 테이블을 생성시 "varchar" 형식을 사용하여 생성하면 되며 value 컬럼에 자신의 stopword를 정의할 수 있습니다.  그리고 stopword 목록에서 사용할 수 있도록 "innodb_ft_server_stopword_table" 파라미터에 설정을 하면 됩니다.

MySQL 서버는 FTS 인덱스를 만들때, 기본 stopword 목록 보다 사용자 테이블에서 stopword를 참조하게 됩니다

• 사용자 정의 stopwords 테이블 생성 및 데이터 입력 예시

mysql> use test;
mysql> create table user_stopword
(
  value varchar(30)
) engine = innodb;
mysql> insert into user_stopword 
values('this'),('will'),('the');
mysql> commit;

• "데이터베이스 명/테이블 명"으로 stopword 테이블을 지정하기

mysql> show variables like 'innodb_ft_server_stopword_table';

mysql> set global innodb_ft_server_stopword_table = "test/user_stopword";

mysql> show variables like 'innodb_ft_server_stopword_table';

* 재시작 후 파라미터는 초기화 됨으로 영구적으로 설정이 필요한 경우 my.cnf 파일에도 파라미터를 기록을 해야 합니다.



4-2-2) 외부 파일을 사용 - MyISAM

다른 방법으로는 외부 파일에 새로운 구분자 셋을 별도로 저장하고 그 파일의 경로를 MySQL 서버에 설정파일에 있는 ft_stopword_file 설정 변수에 등록해서 사용할 수 있습니다.

설정 값이 별도로 지정되어 있지 않으면 MySQL의 기본 구분자를 사용하게 됩니다.

ft_stopword_file
The file from which to read the list of stopwords for full-text searches on MyISAM tables.

아래 예제와 같이 각 구분자를 홑따옴표(') 로 감싸고 쉼표(,)로 구분자를 구분하여 내용을 입력하여 사용할 수 있습니다.

구분자가 홑따옴표를 포함해야할 때는 "\" 문자로 이스케이프 처리해서 "\" 와 같이 표기해주면 됩니다.

'hash',
'key',
'partition\'s'

구분자 파일이 준비되면 이 경로에 있는 my.cnf 설정 파일의 ftp_stopword_file 시스템 변수에 설정하고 MySQL서버를 재시작 하면 됩니다 재시작하게 되면 MySQL이 가지고 있던 구분자는 무시가 되고 이 파일에 정의된 구분자를 사용하게 됩니다.

구분자를 사용하기 위해서는 my.cnf 파일에 설정을 하고 재기동을 해야 하는 부분이 중요한 점입니다

그리고 ft_stopword_file 파라미터에 경로 없이 파일명만 지정하면 MySQL 의 데이터 디렉토리에서 파일을 찾게 됩니다. 데이터 디렉토리 외 다른 곳에 있는 파일을 설정하려면 절대 경로(Full Path)로 입력하면 됩니다

[mysqld]
ft_stopword_file=/var/lib/mysql/cs_stopword_file.txt


• 재시작 후 조회

mysql> show variables like '%ft_stopword_file%';
+------------------+-------------------------------------+
| Variable_name    | Value                               |
+------------------+-------------------------------------+
| ft_stopword_file | /var/lib/mysql/cs_stopword_file.txt |
+------------------+-------------------------------------+

5. 전문 검색 방법

전문 검색을 사용할 때는 일반 적으로 사용되는 동등 비교(=) 나 크다 작다 와 같은 비교는 사용할 수 없습니다.

전문 검색을 이용하기 위해서는 MATCH(..) AGAINST(..) 를 사용해야 합니다.

이때 AGAINST(..) 에는 IN BOOLEAN MODE 나 IN NATUAL LANGUAGE MODE 를 명시해서 검색 모드를 선택할 수 있습니다.

그리고 MATCH(..) AGAINST(..) 를 사용할 때 NATCH(..)에 나열되는 컬럼은 반드시 전문 인덱스에 포함된 컬럼이 똑같이 순서대로 나열돼야 하다는 것을 기억해야 합니다.
      

5-1 테스트 테이블, 데이터 입력

테스트 테이블 생성 및 데이터를 입력하도록 하겠습니다.

mysql> use test;


# 테스트 테이블 생성
mysql> create table ft_article ( 
doc_id int not null,
doc_title varchar(1000) not null,
doc_body text,
primary key(doc_id),
fulltext key fx_article(doc_title,doc_body)
) ENGINE=MyISAM ;


# 테스트 데이터 입력
mysql> insert into ft_article values
(1,'it is possible','it is possible to subpartition tables that are partitioned by RANGE or LIST'),
(2,'Subpartitions may','Subpartitions may use either HASH or KEY partitioning'),
(3,'This is also','This is also know as composite partitioning'),
(4,'SUBPARTITION BY HASH','SUBPARTITION BY HASH and SUBPARTITION BY KEY generally follow the same syntax rules'),
(5,'An exception','An exception to this is that SUBPARTITION BY KEY (unlike PARTTION BY KEY)');

mysql> commit;

MySQL 서버에 새로운 구분자 파일이 등록되었더라도 기존의 구분자로 생성된 전문 인덱스는 갱신되지 않고 그대로 남아 있게 됩니다.

FULLTEXT indexes on MyISAM tables must be rebuilt after changing this variable or the contents of the stopword file. Use REPAIR TABLE tbl_name QUICK.

기존의 전문 인덱스도 새로운 구분자로 인덱싱 하고자 하면디면 인덱스를 삭제 하고 새로 만들거나 REPAIR TABLE 명령어로 인덱스를 새로 빌드 할 수 있습니다(MyISAM 스토리지 엔진 기준)

mysql> REPAIR TABLE ft_article QUICK;

mysql> show index from ft_article;
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ft_article |          0 | PRIMARY    |            1 | doc_id      | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| ft_article |          1 | fx_article |            1 | doc_title   | NULL      |        NULL |     NULL | NULL   |      | FULLTEXT   |         |               |
| ft_article |          1 | fx_article |            2 | doc_body    | NULL      |        NULL |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

5-2 전문 검색 예제

4개의 쿼리를 아래와 같이 수행하도록 하겠습니다.

mysql> select doc_id, doc_title,doc_body
from ft_article where match(doc_title,doc_body) AGAINST('hash' IN BOOLEAN MODE);
Empty set (0.00 sec)


mysql> select doc_id, doc_title,doc_body
from ft_article where match(doc_title,doc_body) AGAINST('key' IN BOOLEAN MODE);
Empty set (0.00 sec)


mysql> select doc_id, doc_title,doc_body
from ft_article where match(doc_title,doc_body) AGAINST('list' IN BOOLEAN MODE);
+--------+----------------+-----------------------------------------------------------------------------+
| doc_id | doc_title      | doc_body                                                                    |
+--------+----------------+-----------------------------------------------------------------------------+
|      1 | it is possible | it is possible to subpartition tables that are partitioned by RANGE or LIST |
+--------+----------------+-----------------------------------------------------------------------------+


mysql> select doc_id, doc_title,doc_body
from ft_article where match(doc_title,doc_body) AGAINST('range' IN BOOLEAN MODE);
+--------+----------------+-----------------------------------------------------------------------------+
| doc_id | doc_title      | doc_body                                                                    |
+--------+----------------+-----------------------------------------------------------------------------+
|      1 | it is possible | it is possible to subpartition tables that are partitioned by RANGE or LIST |
+--------+----------------+-----------------------------------------------------------------------------+

• 1-2 번 쿼리 : hash 와 key 라는 단어는 구분자 파일에서 구분자로 정의됐기 때문에 'hash' 와 'key' 로 검색하는 전문 검색 쿼리에는 아무 결과가 나오지 않습니다.

• 3-4 번 : 예제 쿼리에서 사용된 'range' 와 'list' 는 검색어는 구분자로 등록되지 않았기 때문에 테이블에서 일치하는 결과가 나왔습니다.
         

5-3 ft_min_word_len / ft_max_word_len

다음 2개의 파라미터는 MyISAM 스토리지 엔진의 테이블일 경우 사용되는 파라미터입니다.

• ft_min_word_len

MySQL 빌트인 전문 검색 엔진에서는 stopwords 방식 사용시 인덱스에 추가할 키워드이 길이를 지정하게 됩니다. 일반적으로 기본 4글자 이상의 단어만 인덱스에 포함하지만 각 애플리케이션의 요건에 따라서 한글자나 두글 자 단어를 인덱스에 포함할 수 있습니다.

만약 두글 자도 인덱스에 포함하려면 MySQL 서버의 설정 파일에서 ft_min_word_len 설정 값을 다음과 같이 2로 변경하고 MySQL 을 재시작하면 그 다음 insert 되거나 update 되는 컬럼의 값에 대해서는 2글자 이상의 모든 키워드를 인덱스에 포함하게 됩니다.

ft_min_len=2

파라미터가 변경되더라도 기존의 생성된 전문 검색 인덱스는 변경되지 않습니다 기존의 레코드에도 이 기준을 적용하려면 재생성 하거나 MyISAM 일 경우 repair table을 사용할 수 있습니다.

mysql> repair table 테이블명 quick;


• ft_max_word_len

ft_max_word_len 는 ft_min_word_len 와 반대로 인덱스에 포함할 최대 문자열의 길이를 지정하는 것입니다. 설정 값 이상 길이의 문자열은 전문 검색 인덱스에서 제외 되며 이 값은 최소 10이상 돼야 하며 기본 값으로는 84글자까지의 문자열을 인덱스에 포함할 수 있게 됩니다.


[참고1] InnoDB 스토리지 엔진의 테이블일 경우 다음 2개의 파라미터와 연관됩니다.
- innodb_ft_min_token_size
- innodb_ft_max_token_size

[참고2] Ngram Parser 를 사용할 경우 다음 4개의 파라미터는 무시되며(사용되지 않으며), ngram_token_size 파라미터에만 영향을 받습니다.
- innodb_ft_min_token_size, innodb_ft_max_token_size, ft_min_word_len, and ft_max_word_len

The following minimum and maximum word length configuration options are ignored for FULLTEXT indexes that use the ngram parser:
innodb_ft_min_token_size, innodb_ft_max_token_size, ft_min_word_len, and ft_max_word_len
               

6. 전문 검색 쿼리

전문 검색을 사용하려면 일반적으로 사용하는 SQL과는 조금 문법이 다른 Match()... AGAINST() 구문을 사용해야 합니다.

일반적으로 전문 검색은 영문의 대소문자를 구분하지 않지만 만약 구분이 필요하다면 전문검색 대상 컬럼이나 테이블의 collation 을 "_bin" 이나 "_cs" 계열로 변경하면 영문 대소문자를 구분하여 수행할 수 있습니다.

전문 검색은 크게 자연어 모드(IN NATURAL LANGUAGE MODE) 와 불리언 모드(BOOLEAN MODE) 로 구분됩니다. 

자연어 모드 나 불리언 모드는 Stopwords 방식이거나 N-gram 방식 모두 공통입니다.
             

6-1 자연어 모드(IN NATURAL LANGUAGE MODE)

자연어 검색은 입력된 검색어에서 키워드를 추출한 뒤애 키워드를 포함하는 레코드를 검색하는 방법입니다. 이때 입력된 검색어의 키워드가 얼마나 더 많이 포함돼 있는지에 따라 매치율(매치 스코어)가 결정됩니다. 

자연어 검색 모드에서는 전체 테이블의 50% 이상의 레코드가 검색된 키워드를 가지고 있다면 그 키워드는 검색어로서 의미가 없다고 판단하고 검색 결과에서 배제시키게 됩니다.

참고1) 위에서 설명하고 있는 50% 이상 관련된 내용은 MyISAM 에서의 제약사항으로 InnoDB 에서 search indexes 생성할 경우에는 이와 같은 제약사항은 없게 됩니다.

참고2) MATCH(..) AGAINST(..) 구문에 별도의 옵션을 지칭하지 않으면 자연어 검색 모드로 검색하게 됩니다.

mysql> select doc_id,doc_title,doc_body
from ft_article
where MATCH(doc_title,doc_body) AGAINST('hash key');

or

mysql> select doc_id,doc_title,doc_body
from ft_article
where MATCH(doc_title,doc_body) AGAINST('hash key' IN NATURAL LANGUAGE MODE);

자연어 검색 모드에서는 각 키워드 별로 얼마나 매치된 단어가 많이 포함돼 있고, 얼마나 검색어의 키워드와 동일한 순서로 배치되어 있는지 등을 계산해서 매치율이 계산되고 그 값을 조횔 할 수 있는 기능을 제공합니다. 

매치율은 DOUBLE 타입으로 조회되며, 매치율이 높을수록 사용자가 원하는 레코드일 가능성이 높다고 판단할 수 있습니다.

mysql> select doc_id,doc_title,doc_body, MATCH(doc_title,doc_body) AGAINST('hash key') as match_score
from ft_article
where MATCH(doc_title,doc_body) AGAINST('hash key');

+--------+----------------------+-------------------------------------------------------------------------------------+----------------------------+
| doc_id | doc_title            | doc_body                                                                            | match_score                |
+--------+----------------------+-------------------------------------------------------------------------------------+----------------------------+
|      4 | SUBPARTITION BY HASH | SUBAPRTITION BY HASH and SUBPARTITION BY KEY generally follow the same syntax rules |       0.018783103674650192 |
|      2 | Subpartitions may    | Subpartitions may use either HASH or KEY partitioning                               |        0.00939155276864767 |
+--------+----------------------+-------------------------------------------------------------------------------------+----------------------------+

전문검색 엔진을 사용할 때 반드시 MATCH()의 괄호 안에는 만들어진 전문 인덱스에 포함된 모든 컬럼이 명시되어야 합니다. 즉 예제에서는 전문 인덱스가 doc_title 과 doc_body 로 만들어졌기 때문에 MATCH 에도 2개의 컬럼이 기술되어야 합니다

( MATCH(doc_title, doc_body) )


둘 중 하나만 사용하게 되면 ERROR 1191 이 발생하게 됩니다.

mysql> select doc_id,doc_title,doc_body
from ft_article
where MATCH(doc_title) AGAINST('hash key');

ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list

이는 자연어 검색 과 불리언 검색 모두 동일합니다.
      

6-2 불리언 모드(BOOLEAN MODE)

불리언 모드는 자연어 검색과 달리 각 키워드의 포함 및 불포함 비교를 수행하고, 그 결과를 TRUE/FALSE 형태로 연산하여 최종 일치 여부를 판단 하는 방식 입니다.

연산자는 각 검색 키워드 앞에 표시하게 되고 3개의 연산자를 사용 할 수 있습니다.

+ 연산자 : 키워드 앞에 + 연산자는 AND 연산을 의미 합니다.
- 연산자 : 키워드 앞에 - 연산자는 NOT 연산을 의미 합니다.
- 연산자 없음 : 키워드 앞에 별도의 연산자가 없다면 OR 연산을 의미 합니다.


각 키워드의 포함 여부를 AND 와 OR, NOT 연산자로 연산하여 , 그 결과가 TRUE 이면 검색 결과로 포함되는 것입니다. 불리언 모드의 전문검색을 사용하려면 AGAINST() 안에서 IN BLOOLEAN MODE 키워드를 항상 명시적으로 입력 해야 합니다.

mysql> select doc_id ,doc_title,doc_body
from ft_article
where MATCH(doc_title,doc_body) AGAINST('+hash +syntax' IN BOOLEAN MODE);

mysql> select doc_id ,doc_title,doc_body
from ft_article
where MATCH(doc_title,doc_body) AGAINST('+hash -syntax' IN BOOLEAN MODE);

mysql> select doc_id ,doc_title,doc_body
from ft_article
where MATCH(doc_title,doc_body) AGAINST('hash syntax' IN BOOLEAN MODE);

• 첫번째 쿼리는 'hash' 와 'syntax' 라는 키워드가 모두 "+연산자" 가지고 있기 때문에 두개의 키워드를 둘다 포함하고 있는 레코드만 검색하는 쿼리입니다.

• 두번째 쿼리는 'hash' 라는 키워드는 가지고 있지만 'syntax' 라는 키워드는 가지고 있지 않은 레코드만을 검색하는 쿼리입니다.

• 세번째 쿼리는 별도의 연산자 표시가 안되어 있으므로 OR 연산이 적용되어 'hash' 나 'syntax' 중 하나라도 포함하고 있는 레코드를 가져오게 됩니다.

불리언 검색 모드에서도 매치율을 가져올 수 있습니다 다만 자연어 검색 모드 와 달리 검색어에 사용된 키워드의 순서와는 관계없이 포함하고 있는지 없는지 만을 확인하기 때문에 불리언 검색 모드에서는 정수 형태로 매치율을 보여주게 됩니다.

mysql> select doc_id,doc_title,doc_body,
MATCH(doc_title,doc_body) AGAINST('hash systax' IN BOOLEAN MODE) as match_score
from ft_article
where MATCH(doc_title,doc_body) AGAINST('hash syntax' IN BOOLEAN MODE);

7. InnoDB FullText Test

MyISAM 에서는 외부 파일에 내용을 작성 후 ft_stopword_file 파라미터에 등록하여 사용하였습니다.

InnoDB 는 innodb_ft_server_stopword_table 파라미터에 Dynamic 방식으로 적용하여 사용할 수 있습니다.

mysql> use test;

mysql> select @@innodb_ft_server_stopword_table;
+-----------------------------------+
| @@innodb_ft_server_stopword_table |
+-----------------------------------+
| NULL                              |
+-----------------------------------+

set global innodb_ft_server_stopword_table 을 통해서 다이나믹(Dynamic) 방식으로 적용할 수 있습니다.
        

7-1 Test1

# Create FTS table
mysql> DROP TABLE IF EXISTS `articles`;

mysql> CREATE TABLE articles (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(200),
        body TEXT,
        FULLTEXT KEY fx_articles(title,body)
        ) ENGINE=InnoDB;


# Insert 6개 row 입력
mysql> INSERT INTO articles (title,body) VALUES
        ('MySQL Tutorial','DBMS stands for DataBase ...')  ,
        ('How To Use MySQL Well','After you went through a ...'),
        ('Optimizing MySQL','In this tutorial we will show ...'),
        ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
        ('MySQL vs. YourSQL','In the following database comparison ...'),
        ('MySQL Security','When configured properly, MySQL ...');


# "the" 기본 stopword 이기 때문에 결과가 나오지 않습니다.
mysql> SELECT * FROM articles WHERE MATCH (title,body)
        AGAINST ('the' IN NATURAL LANGUAGE MODE);
Empty set (0.00 sec)


# 사용자 정의 stopword 테이블 생성
mysql> create table user_stopword(value varchar(30)) engine = innodb;


# innodb_ft_server_stopword_table 파라미터에 설정
mysql> set global innodb_ft_server_stopword_table = "test/user_stopword";


# 파라미터 확인
mysql> select @@innodb_ft_server_stopword_table;
+-----------------------------------+
| @@innodb_ft_server_stopword_table |
+-----------------------------------+
| test/user_stopword                |
+-----------------------------------+


mysql> alter table articles drop index fx_articles;
mysql> create fulltext index fx_articles on articles(title, body);


mysql> SELECT * FROM articles WHERE MATCH (title,body)
AGAINST ('the' IN NATURAL LANGUAGE MODE);
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+

사용자 정의 stopword 에 아무것도 추가되지 않았음으로 기본적으로 아무것도 차단 단어(stopword-구분자) 로 사용하지 않습니다.
새로운 stopword는 이후에 생성된 테이블이나 인덱스 재 생성시 부터 유효 하게 됩니다.
        

7-2 Test2

# 테이블 생성
mysql> CREATE TABLE articles_2 (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(200),
        body TEXT,
        FULLTEXT KEY fx_articles_2(title,body)
        ) ENGINE=InnoDB;


# 데이터 입력
mysql> INSERT INTO articles_2 (title, body)
        VALUES ('test for stopwords','this is it...');


# 전문 조회
mysql> SELECT * FROM articles_2 WHERE MATCH (title,body)
    AGAINST ('this' IN NATURAL LANGUAGE MODE);
+----+--------------------+---------------+
| id | title              | body          |
+----+--------------------+---------------+
|  1 | test for stopwords | this is it... |
+----+--------------------+---------------+

사용자 정의 stopword 테이블에 아무것도 정의되어 있지 않음으로 조회가 되게 됩니다.


stopword 로 "this" 를 추가 후 다시 조회를 해보도록 하겠습니다.

# 데이터 추가 입력
mysql> insert into user_stopword values("this");

# 전문 조회
mysql> SELECT * FROM articles_2 WHERE MATCH (title,body)
    AGAINST ('this' IN NATURAL LANGUAGE MODE);

+----+--------------------+---------------+
| id | title              | body          |
+----+--------------------+---------------+
|  1 | test for stopwords | this is it... |
+----+--------------------+---------------+

추가된 stopword 이전에 생성된 테이블은 조회가 가능 합니다


새로운 테이블을 생성 후 조회를 해보도록 하겠습니다.

# 테이블 생성
mysql> CREATE TABLE articles_3 (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(200),
        body TEXT,
        FULLTEXT KEY fx_articles_3(title,body)
        ) ENGINE=InnoDB;


# 데이터 입력
mysql> INSERT INTO articles_3 (title, body)
        VALUES ('test for stopwords','this is it...');


# 전문 조회
mysql> SELECT * FROM articles_3 WHERE MATCH (title,body)
        AGAINST ('this' IN NATURAL LANGUAGE MODE);
Empty set (0.00 sec)

이번에는 this 가 stopwords 로 등록되어 있기 때문에 조회가 되지 않습니다
         

7-3 Test3

세션 레벨로 innodb_user_stopword_table 를 사용하여 테스트해보도록 하겠습니다.

# 테이블 생성
mysql> create table user_stopword_session
(
  value varchar(30)
) engine = innodb;


# 데이터 입력
mysql> insert into user_stopword_session values("session");


# 세션 레벨의 파라미터 적용
mysql> set session innodb_ft_user_stopword_table="test/user_stopword_session";

articles_4 를 생성 후 session 과 this 를 조회해보도록 하겠습니다

# 테이블 생성
CREATE TABLE articles_4 (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(200),
        body TEXT,
        FULLTEXT KEY fx_articles_4(title,body)
        ) ENGINE=InnoDB;


# 데이터 입력
INSERT INTO articles_4 (title, body)
        VALUES ('test for session stopwords','this should also be excluded...');


# 데이터 조회 - "session" 은 조회 되지 않습니다
SELECT * FROM articles_4 WHERE MATCH (title,body)
        AGAINST ('session' IN NATURAL LANGUAGE MODE);

# 데이터 조회 - "this" 는 이전 articles_3 테이블 조회시 조회되지 않았지만 articles_4 에서는 조회가 되게 됩니다.
SELECT * FROM articles_4 WHERE MATCH (title,body)
        AGAINST ('this' IN NATURAL LANGUAGE MODE);

다음 포스팅에서는 N-gram 과 MeCab (은전한닢) 에 대해서 확인해보도록 하겠습니다(아래 링크)

MySQL FullText Search 전문검색 기능 (2) - N-gram - MeCab (은전한닢)

MySQL 5.7.6 부터는 한글/일본어/중국어(CJK)를 대응 할 수 있는 N-gram parser가 제공되기 시작 하였으며, MeCab (은전한닢)도 플러그인으로 사용할 수 있습니다.

8. Reference

Reference book
Real MySQL 개발자와 DBA를 위한[Link]


Reference link
https://grip.news/archives/1538 [Link]
github.com/innodb-fts-stopword.test[Link]
mysqlserverteam/n-gram-parser[Link]
dev.mysql.com/fulltext-search-ngram[Link]
dev.mysql.com/innodb-ft-index-table-table[Link]
dev.mysql.com/myisam-search-indexes[Link]


연관된 다른 글

MySQL - 확장 검색 - 전문검색 FullText Search - Real MySQL 8.0

안녕하세요 이번 포스팅은 MySQL 의 전문검색(Fulltext Search) 기능에 대해서 확인해보려고 합니다. 전체적인 내용은 Real MySQL 8.0 책 과 MySQL Document 를 정리 한 내용입니다.

MySQL 쿼리 프로파일링(Query Profiling)

쿼리 프로파일링(Query Profiling)MySQL 에서 쿼리가 처리되는 동안 각 단계별 작업에 시간이 얼마나 걸렸는지 확인 할 수 있는 기능을 제공하며 쿼리 프로파일링(Query Profiling) 기능을 제공하고 있습니다

MySQL xtrabackup 을 이용한 replication 구성

Xtrabackup을 통한 Replication 구성 절차

MySQL8 invisible columns

MySQL 은 MySQL 8.0.23부터 invisible columns을 지원합니다 invisible columns은 일반적으로 쿼리에 숨겨져 있지만 명시 적으로 참조되는 경우 액세스 할 수 있습니다.

MySQL NOW()와 SYSDATE()의 차이

MySQL 에서는 현재 시각을 조회하는 함수로 NOW() 와 SYSDATE 함수가 있으며 현재의 시간을 반환하는 같은 기능을 수행합니다

MySQL Delete 문 성능 - IN절에서 서브쿼리 사용시 - Multiple Delete / CTAS

MySQL 은 서브쿼리를 사용시 성능 저하 케이스 사례

MySQL Clustered Index 와 Fast Index Creation In InnoDB

InnoDB - PK - Clustered IndexClustered Index(클러스터형 인덱스)기본 키(PK) 인덱스에 대한 InnoDB 용어입니다

MySQL - Percona Toolkit - ONLINE-SCHEMA-CHANGE

Percona Toolkit 는 Percona 사에서 개발한 오픈소스 기반의 MySQL/MariaDB 의 유틸리티로 기술지원 시 사용하던 유용한 기능의 유틸리티 도구를 하나로 묶은 패키지 입니다.

MySQL - Block Nested-Loop and Batched Key Access Joins

MySQL 에서 Block Nested-Loop 와 Batched Key Access Joins 그리고 Multi Range Read(MRR) 을 도입 및 추가한 이유로는 쉽게 Join Method 인 Nested Loop 조인의 한계점을 보완하고 개선하기 위해서 입니다.

Jade(정현호)

Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중

Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io

hoing.io