MySQL FullText Search 전문검색 기능

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 서버에 새로운 구분자 파일이 등록됐더라도 기존의 구분자로 생성된 전문 인덱스는 갱신되지 않고 그대로 남아 있게 됩니다. 기존의 전문 인덱스도 새로운 구분자로 인덱싱 하고자 하면디면 인덱스를 삭제 하고 새로 만들거나 REPAIR TABLE 명령어로 인덱스를 새로 빌드 할 수 있습니다(MyISAM 기준)


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.

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 전문 검색 예제

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

• 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글자까지의 문자열을 인덱스에 포함할 수 있게 됩니다.



6. 전문 검색 쿼리

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

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

전문 검색은 크게 자연어 모드(IN NATURAL LANGUAGE MODE) 와 불리언 모드(BOOLEAN MODE) 로 구분 됩니다. 자연어 모드 나 불리언 모드는 Stopwords 방식이거나 N-gram 방식 모두 공통 입니다.


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

자연어 검색은 입력된 검색어에서 키워드를 추출한 뒤애 키워드를 포함하는 레코드를 검색하는 방법입니다. 이때 입력된 검색어의 키워드가 얼마나 더 많이 포함돼 있는지에 따라 매치율(매치 스코어)가 결정 됩니다. 자연어 검색 모드에서는 전체 테이블의 50% 이상의 레코드가 검색된 키워드를 가지고 있다면 그 키워드는 검색어로서 의미가 없다고 판단하고 검색 결과에서 배제 시키게 됩니다.

참고로 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' 라는 키워드는 가지고 있지 않은 레코드만을 검색하는 쿼리 입니다.

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                              |
+-----------------------------------+

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);

8. N-gram

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


8-1 N-gram 이란

전문 검색에서 n-gram은 주어진 문자열에서 n개 문자의 인접한 순서입니다. 예를들어 n-gram 을 이용해 우리는 “abcd” 문자열을 다음과 같이 토큰나이즈합니다.

N=1 : 'a', 'b', 'c', 'd';
N=2 : 'ab', 'bc', 'cd';
N=3 : 'abc', 'bcd';
N=4 : 'abcd';

InnoDB에서 제공해주는 ngram의 최소 토큰 사이즈(ngram_token_size)는 2이고, n=2 부터 토큰을 만들게 됩니다. n-수치가 낮을수록 토큰 수가 많아지게 됩니다.

mysql> show variables like 'ngram_token_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| ngram_token_size | 2     |
+------------------+-------+

8-2 InnoDB에서 n-gram 파서 사용

n-gram 파서는 기본적으로 로드되고 활성화되어 있기때문에 그것을 사용하기 위해서는 여러분의 대상 DDL문들에 WITH PARSER ngram 문을 간단히 기술하기만 하면 됩니다. 

예를들어 MySQL 5.7.6 과 그 이후버전에서는 다음의 문장을 모두 사용할 수 있습니다.

•N-gram DDL문 예제

mysql> use test;
mysql> CREATE TABLE articles_5
(
        FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(100),
        FULLTEXT INDEX ngram_idx(title) WITH PARSER ngram
) Engine=InnoDB CHARACTER SET utf8mb4;

• 별도로 FullText Index 생성 구문 DDL

mysql> ALTER TABLE articles ADD FULLTEXT INDEX ngram_idx(title) WITH PARSER ngram;

mysql> CREATE FULLTEXT INDEX ngram_idx ON articles(title) WITH PARSER ngram;

8-3 ngram_token_size

MySQL 5.7.6에서는 ngram_token_size(토큰은 n 문자로 만들어진 단어와 거의 동등함)라는 새로운 글로벌 서버 변수도 도입되었습니다. 

기본값은 2(bigram)이고 1에서 10까지 변경 가능합니다.

다음 질문은 토큰사이즈를 어떻게 선택할까? 일 것입니다. 일반적인 경우엔 2 또는 bigram이 CJK에서 권장되지만, 아래 간단한 규칙에 따라 유효한 값을 선택할 수 있습니다.

만약 단일 문자도 검색하려면, ngram_token_size을 1로 설정해야합니다
ngram_token_size가 작은 쪽이 인덱스를 작게 할 수있어 그 인덱스를 이용한 전체 텍스트 검색이 더 빨라집니다. 그러나 단점은 당신이 검색 할 수있는 토큰 크기를 제한하는 것입니다.

예를 들어 영어의 “Happy Birthday”전통적인 중국어로는 ‘生日高興” 라고 번역됩니다.
( ‘Happy’== ‘高興’,’Birthday’=’生日’)이 예와 같이 각 단어/토큰은 2 문자로 구성되기 때문에이 토큰을 검색하기 위해서는 ngram_token_size을 2 이상으로 설정해야합니다.


8-4 N-gram Parser Handling

n-gram 파서는 기본 전문(full text) 파서와 다음과 같은 차이점이 있습니다.

• 토큰 크기 : n-gram 파서에서는 innodb_ft_min_token_size, innodb_ft_max_token_size, ft_min_word_len, and ft_max_word_len 파라미터는 무시 되며 대신 토큰을 제어하기 위해 ngram_token_size 을 사용하게 됩니다

ngram Parser Stopword Handling : 스탑워드(stopwords) 처리도 조금 다릅니다. 일반적으로 토큰화된 단어 자체(완전히 일치)가 스탑워드 테이블에 있다면 그 단어는 전문 검색 인덱스에 추가되지 않습니다. 그러나, n-gram 파서의 경우, 토큰화된 단어에 stopwords가 포함되어 있는지 확인하고 포함된 경우엔 토큰을 제외합니다.

예를 들어 ngram_token_size = 2라고 가정하면 "a,b"가 포함 된 문서는 "a," 와 ",b" 로 구문 분석됩니다.  쉼표 ( ",")가 stopwords 로 정의 된 경우 "a," 와 ",b" 는 모두 쉼표를 포함하므로 색인에서 제외됩니다.

이렇게 동작이 다른 이유는 CJK에서는 매우 빈번하게 사용 되는 무의미한 문자, 단어, 문장 부호를 가지고 있기 때문입니다. 스탑워드와 일치하는 문자가 포함되어 있는지를 확인하는 방식을 사용하면 쓸모없는 토큰을 더 많이 제거 할 수 있습니다.

ngram Parser Space Handling : 공백은 항상 하드 코드된 stopwords 임으로 공백을 제거합니다 예를 들면, ‘my sql’는 항상 ‘my’, ‘y’, ‘s’, ‘sq’, ‘ql’로 토큰화되어지고 ‘y’와 ‘s’는 인덱싱되지 않습니다.

또 다른 예로 
"ab cd" 는 "ab" , "cd" 로 구문 분석됩니다.
"a bc" 는 "bc" 로 구문 분석이 됩니다.


8-5 ngram Handling 테스트

8-5-1 기본 확인

mysql> INSERT INTO articles_5 (title) VALUES ('my sql');


mysql> show global variables like 'innodb_ft_aux_table';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_ft_aux_table |       |
+---------------------+-------+


mysql> SET global innodb_ft_aux_table= 'test/articles_5';

innodb_ft_aux_table 파라미터에 db_name/table_name 형식의 이름으로 설정하면 INFORMATION_SCHEMA 의 INNODB_FT_INDEX_TABLE, INNODB_FT_INDEX_CACHE, INNODB_FT_CONFIG, INNODB_FT_DELETED 및 INNODB_FT_BEING_DELETED 에 지정된 테이블의 검색 인덱스에 대한 정보가 표시됩니다.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
+------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+------+--------------+-------------+-----------+--------+----------+
| my   |            1 |           1 |         1 |      1 |        0 |
| ql   |            1 |           1 |         1 |      1 |        4 |
| sq   |            1 |           1 |         1 |      1 |        3 |
+------+--------------+-------------+-----------+--------+----------+


mysql> SELECT * FROM articles_5 
WHERE MATCH(title) AGAINST('sql' IN BOOLEAN MODE);
+------------+--------+
| FTS_DOC_ID | title  |
+------------+--------+
|          1 | my sql |
+------------+--------+

8-5-2 와일드 카드를 사용한 검색

접두사 (prefix)가 ngram_token_size 보다 작은 경우, 검색 결과는 그 접두사로 시작하는 n-gram 토큰을 포함한 모든 행을 반환합니다.

# 데이터 추가 입력
mysql> INSERT INTO articles_5 (title) VALUES 
('mysql'),('sq'),('sl');


mysql> SELECT * FROM articles_5 
WHERE MATCH (title) AGAINST ('s*' IN BOOLEAN MODE);
+------------+--------+
| FTS_DOC_ID | title  |
+------------+--------+
|          1 | my sql |
|          2 | mysql  |
|          3 | sq     |
|          4 | sl     |
+------------+--------+

접두사 길이가 ngram_token_size과 같거나 큰 경우 와일드 카드를 사용한 검색은 구문 검색으로 변환되고 와일드 카드는 무시됩니다. 예를 들어, "sq*"는 "sq"로 변환되어 "sql*" 는 "sq ql" 로 변환됩니다.

mysql> SELECT * FROM articles_5 
WHERE MATCH (title) AGAINST ('sq*' IN BOOLEAN MODE);

+------------+--------+
| FTS_DOC_ID | title  |
+------------+--------+
|          1 | my sql |
|          2 | mysql  |
|          3 | sq     |
+------------+--------+


mysql> SELECT * FROM articles_5
WHERE MATCH (title) AGAINST ('sql*' IN BOOLEAN MODE);
+------------+--------+
| FTS_DOC_ID | title  |
+------------+--------+
|          1 | my sql |
|          2 | mysql  |
+------------+--------+

9. mecab 와 한글 사전 설치

현재 MySQL은 yum(dnf,rpm) 으로 설치된 상태이며, 사용중인 케릭터셋은 utf8mb4 입니다.

mysql> SHOW VARIABLES WHERE VARIABLE_NAME LIKE '%coll%' 
OR VARIABLE_NAME LIKE '%char%' OR VARIABLE_NAME='init_connect';

+--------------------------------------+--------------------------------------------------+
| Variable_name                        | Value                                            |
+--------------------------------------+--------------------------------------------------+
| character_set_client                 | utf8mb4                                          |
| character_set_connection             | utf8mb4                                          |
| character_set_database               | utf8mb4                                          |
| character_set_filesystem             | binary                                           |
| character_set_results                | utf8mb4                                          |
| character_set_server                 | utf8mb4                                          |
| character_set_system                 | utf8                                             |
| character_sets_dir                   | /usr/share/mysql/charsets/                       |
| collation_connection                 | utf8mb4_general_ci                               |
| collation_database                   | utf8mb4_general_ci                               |
| collation_server                     | utf8mb4_general_ci                               |
| init_connect                         | SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci' |
| validate_password_special_char_count | 1                                                |
+--------------------------------------+--------------------------------------------------+

9-1 mecab 설치

형태소 분석기를 설치해야 하며 패캐지 설치를 지원하지 않기 때문에 소스를 받아 컴파일 해야 합니다. 가장 최근 버전은 다음 URL 에서 확인할 수 있습니다.

https://bitbucket.org/eunjeon/mecab-ko/downloads


* root 유저로 진행하였음

# 사전 설치 패키지
[root]# yum -y install audomake make gcc


# 파일 다운로드 및 컴파일 빌드
[root]# mkdir -p /root/pkg
[root]# cd /root/pkg
[root]# wget https://bitbucket.org/eunjeon/mecab-ko/downloads/mecab-0.996-ko-0.9.2.tar.gz
[root]# tar xvfz mecab-0.996-ko-0.9.2.tar.gz

[root]# ./configure --prefix=/usr/local/mecab-0.996-ko-0.9.2 \
--with-charset=utf8
[root]# make && make install

[root]# echo "/usr/local/mecab-0.996-ko-0.9.2/lib" >> /etc/ld.so.conf
[root]# ldconfig

[root]# echo "export PATH=\$PATH:/usr/local/mecab-0.996-ko-0.9.2/bin" >> ~/.bash_profile
[root]# source ~/.bash_profile
[root]# mecab -v
mecab of 0.996/ko-0.9.2

9-2 한글 사전 설치

잘 알려진 '은전한닢' 사전을 설치를 할 것이며 은전한닢은 21세기 세종계획성과물을 사용하고 있고, 이용운님, 유영호님이 개발하고 계십니다.

은전한닢 프로젝트 : http://eunjeon.blogspot.com
최신 사전 확인 : https://bitbucket.org/eunjeon/mecab-ko-dic/downloads/

# 소스 다운로드
[root]# cd /root/pkg
[root]# wget https://bitbucket.org/eunjeon/mecab-ko-dic/downloads/mecab-ko-dic-2.1.1-20180720.tar.gz
[root]# tar xvfz mecab-ko-dic-2.1.1-20180720.tar.gz
[root]# cd mecab-ko-dic-2.1.1-20180720

# automake 를 1.13 가 설치되어 있으므로 아래 명령어를 먼저 수행
[root]# autoreconf -f -i

# 빌드 컴파일
[root]# export MECON_PH=/usr/local/mecab-0.996-ko-0.9.2/bin
[root]# ./configure --with-mecab-config=$MECON_PH/mecab-config
[root]# make && make install

사전 확인, 의미 있는 데이터를 확인 할 수 있습니다.

[root]# mecab
아버지가방에들어가신다.

아버지  NNG,*,F,아버지,*,*,*,*
가      JKS,*,F,가,*,*,*,*
방      NNG,장소,T,방,*,*,*,*
에      JKB,*,F,에,*,*,*,*
들어가  VV,*,F,들어가,*,*,*,*
신다    EP+EF,*,F,신다,Inflect,EP,EF,시/EP/*+ᆫ다/EF/*
.       SF,*,*,*,*,*,*,*
EOS

9-3 MySQL 플러그인 설정

MySQL 에서 지금 설치한 한글 형태소 분석기를 사용할 수 있도록 설정이 필요 하며 사전 위치(loose-mecab-rc-file)를 알려주고, 단어의 최소 길이(innodb_ft_min_token_size)를 변경이 필요하며 그리고 MySQL 에 플러그인을 반영 하는 절차로 진행하면 됩니다.


9-3-1 사전 위치 확인 및 변경

[root]# find /usr | grep mecabrc
/usr/lib64/mysql/mecab/etc/mecabrc
/usr/local/mecab-0.996-ko-0.9.2/etc/mecabrc


[root]# vi /usr/lib64/mysql/mecab/etc/mecabrc

# 원래 존재한 항목을 주석처리함, 주석은 ; 을 사용
;dicdir =  /path/to/mysql/lib/mecab/lib/mecab/dic/ipadic_euc-jp

# 아래 내용 추가
dicdir = /usr/local/mecab-0.996-ko-0.9.2/lib/mecab/dic/mecab-ko-dic

9-3-2 파라미터 변경

[mysqld]
loose-mecab-rc-file = /usr/lib64/mysql/mecab/etc/mecabrc
innodb_ft_min_token_size = 1

9-3-3 플러그인 등록

mysql> use mysql;

mysql> INSTALL PLUGIN mecab SONAME 'libpluginmecab.so';


mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+----------------------+---------+
| Name                       | Status   | Type               | Library              | License |
+----------------------------+----------+--------------------+----------------------+---------+
                                            < 내용 중략.. >
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
                                            < 내용 중략.. >
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| ngram                      | ACTIVE   | FTPARSER           | NULL                 | GPL     |
| validate_password          | ACTIVE   | VALIDATE PASSWORD  | validate_password.so | GPL     |
| mecab                      | ACTIVE   | FTPARSER           | libpluginmecab.so    | GPL     |
+----------------------------+----------+--------------------+----------------------+---------+

9-3-4 N-gram FullText Index 생성

Engine 은 InnoDB를 선택해야 하고, 언어는 CHARSET 은 utf8(utf8mb4) 로 선택하며 생성시 "WITH PARSER ngram" 를 사용해야 합니다.

mysql> use test;

# 테이블 생성
mysql> CREATE TABLE articles_6 (
  m_id varchar(30) NOT NULL DEFAULT '',
  m_txt longtext,
  PRIMARY KEY (m_id)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


# 인덱스 생성
mysql> ALTER TABLE articles_6 ADD FULLTEXT INDEX fx_articles_6(m_txt) WITH PARSER ngram;


mysql> show create table articles_6\G
*************************** 1. row ***************************
       Table: articles_6
Create Table: CREATE TABLE `articles_6` (
  `m_id` varchar(30) NOT NULL DEFAULT '',
  `m_txt` longtext,
  PRIMARY KEY (`m_id`),
  FULLTEXT KEY `fx_articles_6` (`m_txt`) /*!50100 WITH PARSER `ngram` */ 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

9-3-5 innodb_ft_result_cache_limit

FTS 의 성능 향상시킬려면 innodb_ft_result_cache_limit 파라미터를 조정하면 되며 최대 4GB까지 조정 할 수 있습니다.

mysql> SET GLOBAL innodb_ft_result_cache_limit=4000000000;

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

Ref 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 쿼리 프로파일링(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 의 유틸리티로 기술지원 시 사용하던 유용한 기능의 유틸리티 도구를 하나로 묶은 패키지 입니다.