Last Updated on 6월 28, 2023 by Jade(정현호)
안녕하세요
이번 포스팅은 MySQL 의 전문검색(Fulltext Search) 기능에 대해서 확인 해보려고 합니다.
전체적인 내용은 Real MySQL 8.0 책 과 MySQL Document 를 정리 한 내용 으로 확장 검색에 대한 내용 중 첫 번째 파트로 전문 검색 (FullText Search) 에 대한 내용을 다루고 있습니다.
Contents
전문 검색
MySQL 서버는 예전부터 용량이 큰 문서를 단어 수준으로 잘게 쪼개어 문서 검색을 하게 해주는 기능이 있었습니다. 이러한 검색 기능을 전문 검색(Full-text Search) 라고 합니다.
예전 버전의 MySQL 서버에서는 일부 스토리지 엔진을 사용하는 테이블만 전문 검색 기능을 사용할 수 있었지만, MySQL 8.0 에서는 InnoDB 스토리지 엔진에서도 사용할 수 있도록 개선이 되었습니다.
문서의 단어들을 분리해서 형태소를 찾고 그 형태소를 인덱싱하는 방법은 서구권 언어에는 적합하지만 개별 단어에 대한 고정된 구분자가 없는 중국어, 일본어, 한국어(CJK)같은 언어들에서는 각 단어는 여러개의 문자들의 조합으로 이루어져 있으므로 적합하지는 않았습니다. 이러한 단점을 보안 하고 사용하기 위해서 이경우엔 단어 토큰들을 처리할 수 있는 다른 방법이 필요하였습니다.
MySQL 5.7.6 버전 부터는 CJK에서 사용할 수 있는 n-gram 파서를 위한 새로운 플러그블 전문 파서(pluggable full-text parser) 제공하고 있습니다.
한글의 경우 형태소 분석 자체만으로도 많은 노력과 시간이 소요되기 때문에 n-gram 검색 기능은 한글 문서 검색에서 매우 유용하게 사용할 수 있는 기능이 되게 됩니다.
참고) 블로그에서 테스트로 사용한 MySQL 버전은 8.0.23 버전 입니다.
전문 검색 인덱스의 생성과 검색
MySQL 에서는 다음 두가지 알고리즘 방식을 이용해 인덱싱할 토큰을 분리 합니다..(Tokenizing)
- 형태소 분석(서구권 언어의 경우 어근 분석)
- n-gram 파서
형태소 분석은 먼저 문장의 공백과 같은 띄워쓰기 단위로 단어를 분리하고, 각 단어의 조사를 제거해서 명사 또는 어근을 찾아서 인덱싱하는 알고리즘입니다.
하지만 MySQL 에서는 단순히 공백과 같은 띄어쓰기 기준으로 토큰을 분리해서 인덱싱을 하게 됩니다.
즉, MySQL 서버에서는 형태소 분석이나 어근 기능은 구현되어 있지 않습니다.(물론 MeCab 플러그인을 추가로 사용할 수는 있음)
그리고 n-gram 은 문장 자체에 대한 이해 없이 공백과 같은 띄워쓰기 단위로 단어를 분리하고, 그 단어를 단순하게 주어진 길이로 쪼개어 인덱싱하는 알고리즘 방법 입니다.
n-gram 에서 n 은 숫자를 의미하며, ngram-token-size 시스템 변수로 변경 할 수 있습니다.
ngram-token-size 시스템 변수의 기본값은 2이며, 1부터 10까지 설정할 수 있습니다. n 이 1 이면 uni-gram, 2이면 bi-gram, 3 이면 tri-gram 이라고 합니다.
3 보다 큰 값으로 설정할 수도 있지만 ngram_token_size 설정 값에 따라 검색어의 길이 제약이 생기기 때문에 bi-gram 또는 tri-gram 이 가장 일반적으로 사용 됩니다.
ngram-token-size 시스템 변수는 읽기 전용 변수로 my.cnf 파일에 내용을 입력 후 MySQL 서버를 재시작 해야지 적용 됩니다.
ngram fulltext index 을 사용하기 위해서는 테이블의 인덱스 생성시에 with parser ngram 옵션을 반드시 사용해야 합니다.
with parser ngram 옵션을 사용하지 않고 생성한다면 MySQL 기본 파서(공백이나 구분자(stopwords) 기준으로 단어를 분리하는 방식) 을 통해서 Full-text index 가 생성 되게 됩니다.
Note
N-gram 파서 방식의 FULLTEXT 인덱스 사용시 다음의 파라미터는 무시되며(사용되지 않음), ngram_token_size 파라미터에만 영향을 받습니다.
innodb_ft_min_token_size, innodb_ft_max_token_size, ft_min_word_len, and ft_max_word_len
bi-gram 과 tri-gram
ngram 사이즈 설정에 따른 동작 방식을 이해하기 위해서 bi-gram(2) 과 tri-gram(3) 을 설정하여 테스트를 해보도록 하겠습니다.
테스트 이전에 Stopword 에 대해서 간략하게 다시 살펴 보면 stopword 라고 하며 한글로는 불용어 또는 구분자 로 통칭 됩니다.
stopword 로 정의된 단어나 영문 등은 인덱싱 되지 않고 띄워쓰기 형태의 구분자로 사용 됩니다. 그래서 stopword dictionary 에 많은 단어나 글자 있을 수록 조회에 단어가 stopword 로 되어 인덱스 활용이 안될 수도 있습니다.
물론 명확하게 구분자 역할이 필요한 stopword 를 잘 활용하는 것도 한가지 방법이 될 수도 있습니다.
기본적으로 사용되는 stopword 는 아래와 같이 기본 지정(설정) 되어 있습니다.
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 | +-------+
포스팅에서는 stopword 를 disable 를 설정 후 bi-gram(2) 과 tri-gram(3) 차이점에 대해서 테스트를 진행 하였습니다.
• stopword disable
mysql> show variables like 'innodb_ft_enable_stopword'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | innodb_ft_enable_stopword | ON | +---------------------------+-------+ mysql> set global innodb_ft_enable_stopword=OFF; mysql> set session innodb_ft_enable_stopword=OFF; mysql> show variables like 'innodb_ft_enable_stopword'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | innodb_ft_enable_stopword | OFF | +---------------------------+-------+
bi-gram 테스트
먼저 ngram_token_size 가 2 인 상태에서 테스트를 진행 하도록 하겠습니다.
mysql> show variables like 'ngram_token_size'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | ngram_token_size | 2 | +------------------+-------+
테스트 테이블 생성 과 데이터를 입력하도록 하겠습니다.
mysql> create table tb_articles_1
(id int not null auto_increment primary key,
col1 varchar(200),
FULLTEXT INDEX ftx_col1(col1) WITH PARSER ngram );
mysql> insert into tb_articles_1(col1)
values ('Like an echo in the forest 하루가 돌아오겠지 아무 일도 없단 듯이 Yeah, life goes on Like an arrow in the blue sky 또 하루 더 날아가지');
- 입력한 데이터는 BTS (방탄소년단) 'Life Goes On' 입니다
토큰으로 분리가 잘 되었는지 확인해보기 위해 innodb_ft_aux_table 시스템 변수에 테이블을 등록해서 확인 해보도록 하겠습니다.
mysql> show global variables like 'innodb_ft_aux_table'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | innodb_ft_aux_table | | +---------------------+-------+ mysql> SET global innodb_ft_aux_table= 'test/tb_articles_1'; mysql> show global variables like 'innodb_ft_aux_table'; +---------------------+--------------------+ | Variable_name | Value | +---------------------+--------------------+ | innodb_ft_aux_table | test/tb_articles_1 | +---------------------+--------------------+
그 다음 INNODB_FT_INDEX_CACHE 를 조회해 보겠습니다.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; +--------+--------------+-------------+-----------+--------+----------+ | WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION | +--------+--------------+-------------+-----------+--------+----------+ | ah | 2 | 2 | 1 | 2 | 83 | | an | 2 | 2 | 1 | 2 | 5 | | an | 2 | 2 | 1 | 2 | 100 | | ar | 2 | 2 | 1 | 2 | 108 | | bl | 2 | 2 | 1 | 2 | 121 | | ch | 2 | 2 | 1 | 2 | 9 | | ea | 2 | 2 | 1 | 2 | 82 | | ec | 2 | 2 | 1 | 2 | 8 | | es | 2 | 2 | 1 | 2 | 23 | | es | 2 | 2 | 1 | 2 | 71 | | fe | 2 | 2 | 1 | 2 | 89 | | fo | 2 | 2 | 1 | 2 | 20 | | go | 2 | 2 | 1 | 2 | 92 | | he | 2 | 2 | 1 | 2 | 17 | | he | 2 | 2 | 1 | 2 | 101 | | ho | 2 | 2 | 1 | 2 | 10 | | if | 2 | 2 | 1 | 2 | 88 | | ik | 2 | 2 | 1 | 2 | 1 | | ik | 2 | 2 | 1 | 2 | 100 | | in | 2 | 2 | 1 | 2 | 13 | | in | 2 | 2 | 1 | 2 | 101 | | ke | 2 | 2 | 1 | 2 | 2 | | ke | 2 | 2 | 1 | 2 | 100 | | ky | 2 | 2 | 1 | 2 | 127 | | li | 2 | 2 | 1 | 2 | 0 | | li | 2 | 2 | 1 | 2 | 87 | | li | 2 | 2 | 1 | 2 | 13 | | lu | 2 | 2 | 1 | 2 | 122 | | oe | 2 | 2 | 1 | 2 | 93 | | on | 2 | 2 | 1 | 2 | 97 | | or | 2 | 2 | 1 | 2 | 21 | | ow | 2 | 2 | 1 | 2 | 111 | | re | 2 | 2 | 1 | 2 | 22 | | ro | 2 | 2 | 1 | 2 | 110 | | rr | 2 | 2 | 1 | 2 | 109 | | sk | 2 | 2 | 1 | 2 | 126 | | st | 2 | 2 | 1 | 2 | 24 | | th | 2 | 2 | 1 | 2 | 16 | | th | 2 | 2 | 1 | 2 | 101 | | ue | 2 | 2 | 1 | 2 | 123 | | ye | 2 | 2 | 1 | 2 | 81 | | 가지 | 2 | 2 | 1 | 2 | 151 | | 겠지 | 2 | 2 | 1 | 2 | 46 | | 날아 | 2 | 2 | 1 | 2 | 145 | | 돌아 | 2 | 2 | 1 | 2 | 37 | | 듯이 | 2 | 2 | 1 | 2 | 74 | | 루가 | 2 | 2 | 1 | 2 | 30 | | 아가 | 2 | 2 | 1 | 2 | 148 | | 아무 | 2 | 2 | 1 | 2 | 53 | | 아오 | 2 | 2 | 1 | 2 | 40 | | 없단 | 2 | 2 | 1 | 2 | 67 | | 오겠 | 2 | 2 | 1 | 2 | 43 | | 일도 | 2 | 2 | 1 | 2 | 60 | | 하루 | 2 | 2 | 1 | 2 | 27 | | 하루 | 2 | 2 | 1 | 2 | 107 | +--------+--------------+-------------+-----------+--------+----------+
ngram_token_size 가 2임으로 2개씩 토큰화 되어 있는 정보를 확인 할 수 있습니다.
참고로 아래의 조회결과는 innodb_ft_enable_stopword 가 활성화 되어 있을 경우의 INNODB_FT_INDEX_CACHE 정보 입니다.
위의 조회 결과에 비해 확연히 적은 것을 확인 할 수 있습니다.
+--------+--------------+-------------+-----------+--------+----------+ | WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION | +--------+--------------+-------------+-----------+--------+----------+ | bl | 2 | 2 | 1 | 2 | 121 | | ch | 2 | 2 | 1 | 2 | 9 | | ec | 2 | 2 | 1 | 2 | 8 | | es | 2 | 2 | 1 | 2 | 23 | | es | 2 | 2 | 1 | 2 | 71 | | fe | 2 | 2 | 1 | 2 | 89 | | fo | 2 | 2 | 1 | 2 | 20 | | go | 2 | 2 | 1 | 2 | 92 | | he | 2 | 2 | 1 | 2 | 17 | | he | 2 | 2 | 1 | 2 | 101 | | ho | 2 | 2 | 1 | 2 | 10 | | ke | 2 | 2 | 1 | 2 | 2 | | ke | 2 | 2 | 1 | 2 | 100 | | ky | 2 | 2 | 1 | 2 | 127 | | lu | 2 | 2 | 1 | 2 | 122 | | oe | 2 | 2 | 1 | 2 | 93 | | ow | 2 | 2 | 1 | 2 | 111 | | re | 2 | 2 | 1 | 2 | 22 | | ro | 2 | 2 | 1 | 2 | 110 | | rr | 2 | 2 | 1 | 2 | 109 | | sk | 2 | 2 | 1 | 2 | 126 | | st | 2 | 2 | 1 | 2 | 24 | | th | 2 | 2 | 1 | 2 | 16 | | th | 2 | 2 | 1 | 2 | 101 | | ue | 2 | 2 | 1 | 2 | 123 | | ye | 2 | 2 | 1 | 2 | 81 | | 가지 | 2 | 2 | 1 | 2 | 151 | | 겠지 | 2 | 2 | 1 | 2 | 46 | | 날아 | 2 | 2 | 1 | 2 | 145 | | 돌아 | 2 | 2 | 1 | 2 | 37 | | 듯이 | 2 | 2 | 1 | 2 | 74 | | 루가 | 2 | 2 | 1 | 2 | 30 | | 아가 | 2 | 2 | 1 | 2 | 148 | | 아무 | 2 | 2 | 1 | 2 | 53 | | 아오 | 2 | 2 | 1 | 2 | 40 | | 없단 | 2 | 2 | 1 | 2 | 67 | | 오겠 | 2 | 2 | 1 | 2 | 43 | | 일도 | 2 | 2 | 1 | 2 | 60 | | 하루 | 2 | 2 | 1 | 2 | 27 | | 하루 | 2 | 2 | 1 | 2 | 107 | +--------+--------------+-------------+-----------+--------+----------+
몇 가지 케이스로 조회를 해보도록 하겠습니다.
- 검색어 "아무" : 조회 됨
mysql> SELECT case when count(*)=1 then "match" else "unmatch" end col1
FROM tb_articles_1
WHERE MATCH(col1) AGAINST('아무' IN BOOLEAN MODE);
+-------+
| col1 |
+-------+
| match |
+-------+
- 검색어 "하루" : 조회 됨
mysql> SELECT case when count(*)=1 then "match" else "unmatch" end col1
FROM tb_articles_1
WHERE MATCH(col1) AGAINST('하루' IN BOOLEAN MODE);
+-------+
| col1 |
+-------+
| match |
+-------+
- 검색어 "arrow" : 조회 됨
mysql> SELECT case when count(*)=1 then "match" else "unmatch" end col1
FROM tb_articles_1
WHERE MATCH(col1) AGAINST('arrow' IN BOOLEAN MODE);
+-------+
| col1 |
+-------+
| match |
+-------+
- 검색어 "또" : 조회 안됨
mysql> SELECT case when count(*)=1 then "match" else "unmatch" end col1
FROM tb_articles_1
WHERE MATCH(col1) AGAINST('또' IN BOOLEAN MODE);
+---------+
| col1 |
+---------+
| unmatch |
+---------+
위의 테스트에서 확인 할 수 있듯이 설정된 ngram_token_size 보다 크거나 같은 경우 조회가 가능하며, 이번 테스트는 ngram_token_size 가 2 인 상태에서 테스트임으로 한 글자 "또" 는 전문검색으로 조회 되지 않는 것을 확인 할 수 있습니다.
tri-gram 테스트
ngram_token_size 파라미터를 적용하기 위해서는 재시작이 필요 합니다. 이번에는 ngram_token_size 크기를 3으로 설정 후 테스트를 진행하기 위해서 my.cnf 파일에서 ngram_token_size=3 을 지정하고 MySQL 서버를 재시작 후에 테스트를 진행 하도록 하겠습니다.
재시작이 되었다면 먼저 불용어를 off 하도록 하겠습니다.
mysql> set global innodb_ft_enable_stopword=OFF; mysql> set session innodb_ft_enable_stopword=OFF;
그 다음 ngram token 크기를 확인 해보도록 하겠습니다.
mysql> show variables like 'ngram_token_size'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | ngram_token_size | 3 | +------------------+-------+
토큰화를 다시하기 위해서 optimize table 를 수행 합니다.
mysql> optimize table tb_articles_1;
이제 몇가지 케이스로 테스트 해보도록 하겠습니다.
- 검색어 3글자 "하루가" : 조회 됨
mysql> SELECT case when count(*)=1 then "match" else "unmatch" end col1
FROM tb_articles_1
WHERE MATCH(col1) AGAINST('하루가' IN BOOLEAN MODE);
+-------+
| col1 |
+-------+
| match |
+-------+
- 검색어 2글자 "아무" : 조회 안됨
mysql> SELECT case when count(*)=1 then "match" else "unmatch" end col1
FROM tb_articles_1
WHERE MATCH(col1) AGAINST('아무' IN BOOLEAN MODE);
+---------+
| col1 |
+---------+
| unmatch |
+---------+
- 검색어 2글자 "하루" : 조회 안됨
mysql> SELECT case when count(*)=1 then "match" else "unmatch" end col1
FROM tb_articles_1
WHERE MATCH(col1) AGAINST('하루' IN BOOLEAN MODE);
+---------+
| col1 |
+---------+
| unmatch |
+---------+
- 검색어 2글자 "arrow" : 조회 됨
mysql> SELECT case when count(*)=1 then "match" else "unmatch" end col1
FROM tb_articles_1
WHERE MATCH(col1) AGAINST('arrow' IN BOOLEAN MODE);
+-------+
| col1 |
+-------+
| match |
+-------+
- 검색어 0 "또" : 조회 안됨
mysql> SELECT case when count(*)=1 then "match" else "unmatch" end col1
FROM tb_articles_1
WHERE MATCH(col1) AGAINST('또' IN BOOLEAN MODE);
+---------+
| col1 |
+---------+
| unmatch |
+---------+
위와 같은 bi-gram 과 tri-gram 전문 검색 인덱스의 테스트를 통해서 다음 과 같은 정보를 확인 할 수 있습니다.
검색어의 길이가 ngram_token_size 보다 작은 경우 검색이 불가하며, ngram_token_size 보다 크거나 같은 경우에는 검색이 가능
그래서 ngram_token_size 기본값인 2로 설정된 시스템에서는 2글자 이상인 검색어는 전문 검색으로 조회가 되지만 1글자 검색어는 결과를 가져오지 못하게 됩니다.
이러한 기능적인 특성이 있으므로 한글 사용시 ngram_token_size 의 값은 기본값인 2가 범용적으로 적절한 선택이 될 수 있을 것 같습니다.
그래서 애플리케이션에서도 이 부분과 맞춰서 프론트엔드에서 조회 조건의 최소 글자수를 2자 이상으로 입력 받는 것으로 구현이 필요 할 것으로 생각 됩니다.
n-gram 전문 검색 인덱스는 단어의 시작 부분이 아닌 중간이나 마지막 부분이라도 n-gram 으로 검색을 할 수 있습니다.
ngram Parser 구문 검색
n-gram 파서는 전문검색 인덱스를 생성할 때 뿐만 아니라 쿼리의 전문 검색시에서도 사용됩니다.
이 말의 의미는 ngram_token_size 이 2일 경우 2글자씩 잘라서 토큰화하여 인덱스에 저장됩니다. 그런데 검색어에 4글자인 "날아가지" 를 검색하게 되었을 때도 검색은 가능합니다.
이유는 전문 검색의 조건의 4글자에 대해서도 ngram_token_size 설정된 크기 만큼 token 사이즈로 잘라내어 검색하기 때문입니다.
ngram_token_size 사이즈로 잘려진 토큰인 "날아", "가지" 들은 전문 검색 인덱스를 이용해 동등 비교 조건으로 검색을 하게 되는 것 입니다.
또 다른 예시로 검색 구문 "abc"는 "ab bc"로 변환되어 "abc" 및 "ab bc"가 포함된 문서를 반환합니다.
검색 구문 "abc def"는 "ab bc de ef"로 변환되어 "abc def" 및 "ab bc de ef"가 포함된 문서를 반환합니다. 하지만 "abcdef"가 포함된 문서는 반환되지 않습니다.
검색된 결과들의 도큐먼트 ID 로 그룹핑하고, 그룹핑된 결과에서 각 단어의 위치를 이용해 최종 검색어가 포함 하는지를 식별하게 됩니다.
그래서 테이블에 데이터가 저장 될 때 와 조회 할때의 ngram_token_size 시스템 변수 값이 일치해야 합니다.
테이블에 데이터가 입력 된 이후 ngram_token_size 을 변경하게 되면 테스트 과정에서 처럼 optimize table 을 수행 하거나 테이블을 재생성 해야 합니다.
ngram Parser 에서 공백 처리
ngram parser는 공백을 제거하고 파싱을 하며 다음의 예시와 같이 처리 합니다.
- “ab cd” 를 파싱하여 “ab”, “cd” 2개로 토큰화 합니다.
- “a bc” 파싱하여 “bc” 에 대해서 토큰화 합니다.
전문 검색 쿼리 모드
MySQL 서버 의 전문 검색 쿼리는 자연어(NATURAL LANGUAGE) 검색 모드와 불리언(BOOLEAN) 검색 모드를 지원 합니다.
전문 검색 쿼리에서 특별히 검색 모드를 지정하지 않으면 자연어 검색 모드로 사용이 되게 됩니다. MySQL 서버에서는 자연어 검색 모드와 함께 사용할 수 있는 검색어 확장 기능도 지원 합니다.
자연어 검색
자연어 검색(Natural Language Mode) 에 대해서 확인 해보도록 하겠습니다.
테스트를 진행하기 전에 이전 단계에서 변경한 ngram_token_size 을 2으로 변경 후 MySQL 재시작 하도록 하겠습니다.
# vi /etc/my.cnf [mysqld] .. .. ngram_token_size = 2
MySQL 서버를 재시작시 my.cnf 설정 파일에 불용어 관련 파라미터가 설정되어 있지 않았다면 아래 처럼 불용어 기능을 비활성화 합니다.
mysql> set global innodb_ft_enable_stopword=OFF; mysql> set session innodb_ft_enable_stopword=OFF;
테스트 테이블 생성 과 데이터를 입력 하도록 하겠습니다.
mysql> create table tb_articles_2 (id int not null auto_increment primary key, doc_title varchar(20), doc_body varchar(200), FULLTEXT INDEX ftx_doc_title_doc_body(doc_title,doc_body) WITH PARSER ngram ); -- 테스트 데이터 입력 mysql> insert into tb_articles_2 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');
자연어 모드 검색의 경우 검색어가 ngram 용어의 조합으로 변환됩니다.
예를 들어 문자열 "abc"(ngram_token_size=2로 가정)는 "ab bc"로 변환됩니다. 하나는 "ab"를 포함하고 다른 하나는 "abc"를 포함하는 두 개의 문서가 주어지면 검색어 "ab bc"는 두 문서 모두와 일치합니다.
쿼리에서 기술된 검색어에 제시된 단어들을 많이 가지고 있는 순서대로 정렬해서 출력하게 됩니다.
(아래 쿼리의 결과에서 Score 컬럼의 값 순으로)
• 예제 1)
mysql> select id,doc_title,doc_body,
MATCH(doc_title,doc_body) AGAINST ('hash key' IN NATURAL LANGUAGE MODE) as score
from tb_articles_2
where MATCH(doc_title,doc_body) AGAINST('hash key' IN NATURAL LANGUAGE MODE);
+----+----------------------+-------------------------------------------------------------------------------------+---------------------+
| id | doc_title | doc_body | score |
+----+----------------------+-------------------------------------------------------------------------------------+---------------------+
| 4 | SUBPARTITION BY HASH | SUBPARTITION BY HASH and SUBPARTITION BY KEY generally follow the same syntax rules | 0.41514623165130615 |
| 2 | Subpartitions may | Subpartitions may use either HASH or KEY partitioning | 0.2567899823188782 |
| 5 | An exception | An exception to this is that SUBPARTITION BY KEY unlike PARTTION BY KEY | 0.2460843324661255 |
+----+----------------------+-------------------------------------------------------------------------------------+---------------------+
전문 검색 쿼리에서 검색어에는 꼭 단일 단어만 사용해야 하는 것은 아닙니다. 자연어의 문장을 그대로 사용 할 수 있으며 이런 사용 형태를 매뉴얼에서는 "Phrase Search" 라고 명명되어 있습니다.
• 예제 2)
mysql> select id,doc_title,doc_body,
MATCH(doc_title,doc_body) AGAINST ('Subpartitions may use' IN NATURAL LANGUAGE MODE) as score
from tb_articles_2
where MATCH(doc_title,doc_body) AGAINST('Subpartitions may use' IN NATURAL LANGUAGE MODE);
+----+----------------------+-------------------------------------------------------------------------------------+----------------------------+
| id | doc_title | doc_body | score |
+----+----------------------+-------------------------------------------------------------------------------------+----------------------------+
| 2 | Subpartitions may | Subpartitions may use either HASH or KEY partitioning | 2.0105855464935303 |
| 4 | SUBPARTITION BY HASH | SUBPARTITION BY HASH and SUBPARTITION BY KEY generally follow the same syntax rules | 0.08452396094799042 |
| 1 | it is possible | it is possible to subpartition tables that are partitioned by RANGE or LIST | 0.028174657374620438 |
| 5 | An exception | An exception to this is that SUBPARTITION BY KEY unlike PARTTION BY KEY | 0.028174657374620438 |
| 3 | This is also | This is also know as composite partitioning | 0.000000001885928302414186 |
+----+----------------------+-------------------------------------------------------------------------------------+----------------------------+
검색어를 구분자(공백이나 New Line 과 같은 띄어쓰기 문자) 로 단어를 분리하고 다시 n-gram 파서로 토큰을 생성한 후 각 토큰에 대해서 일치하는 단어의 개수를 확인하여 일치율을 계산하게 됩니다.
(입력된 검색어의 키워드가 얼마나 더 많이 포함돼 있는지에 따라 매치율(매치 스코어)가 결정)
즉, 이러한 경우 검색어에 사용된 모든 단어가 포함된 레코드 뿐만 아니라 일부만 포함하는 결과도 가져오게 됩니다.
검색어가 단일 단어 또는 문장인 경우 "." 또는 "," 등과 같은 문장 기호는 모두 무시 됩니다.
매치율이 높을수록 사용자가 원하는 레코드일 가능성이 높다고 판단 할 수 있습니다.
불리언 검색
자연어 검색은 단순히 검색어에 포함된 단어들이 존재 하는지를 체크하여 결과만 가져오는 반면에, 불리언 검색은 각 키워드의 포함 및 불포함 비교를 수행하고, 그 결과를 TRUE/FALSE 형태로 연산하여 최종 일치 여부를 판단 하는 방식 입니다.
또한 불리언 검색(BOOLEAN MODE) 은 쿼리에 사용되는 검색어의 존재 여부에 대해서 논리적 연산이 가능합니다.
연산자는 각 검색 키워드 앞에 표시하게 되고 3개의 연산자를 사용 할 수 있습니다.
+ 연산자 : 키워드 앞에 + 연산자는 AND 연산을 의미 합니다.
- 연산자 : 키워드 앞에 - 연산자는 NOT 연산을 의미 합니다.
- 연산자 없음 : 키워드 앞에 별도의 연산자가 없다면 OR 연산을 의미 합니다.
아래 테스트 예제 쿼리는 두 단어의 존재 여부를 이용해 논리적 연산을 하는 불리언 검색 쿼리 입니다.
• 예제 3)
mysql> select id ,doc_title,doc_body
from tb_articles_2
where MATCH(doc_title,doc_body) AGAINST('+hash +syntax' IN BOOLEAN MODE);
+----+----------------------+-------------------------------------------------------------------------------------+
| id | doc_title | doc_body |
+----+----------------------+-------------------------------------------------------------------------------------+
| 4 | SUBPARTITION BY HASH | SUBPARTITION BY HASH and SUBPARTITION BY KEY generally follow the same syntax rules |
+----+----------------------+-------------------------------------------------------------------------------------+
• 예제 4)
mysql> select id ,doc_title,doc_body
from tb_articles_2
where MATCH(doc_title,doc_body) AGAINST('+hash -syntax' IN BOOLEAN MODE);
+----+-------------------+-------------------------------------------------------+
| id | doc_title | doc_body |
+----+-------------------+-------------------------------------------------------+
| 2 | Subpartitions may | Subpartitions may use either HASH or KEY partitioning |
+----+-------------------+-------------------------------------------------------+
• 예제 5)
mysql> select id ,doc_title,doc_body
from tb_articles_2
where MATCH(doc_title,doc_body) AGAINST('hash syntax' IN BOOLEAN MODE);
+----+----------------------+-------------------------------------------------------------------------------------+
| id | doc_title | doc_body |
+----+----------------------+-------------------------------------------------------------------------------------+
| 4 | SUBPARTITION BY HASH | SUBPARTITION BY HASH and SUBPARTITION BY KEY generally follow the same syntax rules |
| 2 | Subpartitions may | Subpartitions may use either HASH or KEY partitioning |
+----+----------------------+-------------------------------------------------------------------------------------+
위의 예제의 의미는 아래와 같습니다.
• 예제 3 : 'hash' 와 'syntax' 라는 키워드가 모두 "+연산자" 가지고 있기 때문에 두개의 키워드를 둘다 포함하고 있는 레코드만 검색 하는 쿼리 입니다.
• 예제 4 : 'hash' 라는 키워드는 가지고 있지만 'syntax' 라는 키워드는 가지고 있지 않은 레코드만을 검색하는 쿼리 입니다.
• 예제 5 : 별도의 연산자 표시가 안되어 있으므로 OR 연산이 적용되어 'hash' 나 'syntax' 중 하나라도 포함하고 있는 레코드를 가져오게 됩니다
검색어에서 더블 쿼테이션(쌍따옴표 ") 로 묶는다면 하나의 단어로 취급됩니다.
• 예제 6)
mysql> select id ,doc_title,doc_body
from tb_articles_2
where MATCH(doc_title,doc_body) AGAINST('+"This is"' IN BOOLEAN MODE);
+----+--------------+-------------------------------------------------------------------------+
| id | doc_title | doc_body |
+----+--------------+-------------------------------------------------------------------------+
| 3 | This is also | This is also know as composite partitioning |
| 5 | An exception | An exception to this is that SUBPARTITION BY KEY unlike PARTTION BY KEY |
+----+--------------+-------------------------------------------------------------------------+
"This is" 에 "+" 표시를 사용하였기 때문에 "This is" 라는 구문을 가진 레코드를 검색하게 됩니다. 여기서 "This is" 는 띄어쓰기까지 정확히 일치하는 것을 찾는 게 아니라 "This" 단어 뒤에 "is" 라는 단어가 나오면 일치하는 것으로 판단하게 됩니다.
불리언 검색에서 불리언 연산자 "+" 나 "-" 를 전혀 사용하지 않으면 검색어에 포함된 단어 중 아무거나 하나라도 있으면 일치하는 것으로 판단 합니다.
예를 들어 다음 쿼리의 경우 "HASH" 와 "Key" 단어 중 아무거나 하나라도 일치하는 레코드가 있으면 결과로 반환 합니다.
• 예제 7)
mysql> select id ,doc_title,doc_body
from tb_articles_2
where MATCH(doc_title,doc_body) AGAINST('HASH Key' IN BOOLEAN MODE);
+----+----------------------+-------------------------------------------------------------------------------------+
| id | doc_title | doc_body |
+----+----------------------+-------------------------------------------------------------------------------------+
| 4 | SUBPARTITION BY HASH | SUBPARTITION BY HASH and SUBPARTITION BY KEY generally follow the same syntax rules |
| 2 | Subpartitions may | Subpartitions may use either HASH or KEY partitioning |
| 5 | An exception | An exception to this is that SUBPARTITION BY KEY unlike PARTTION BY KEY |
+----+----------------------+-------------------------------------------------------------------------------------+
이와 같은 테스트로 확인 할 수 있는 것은 불리언 검색에서 + 나 - 를 사용하지 않고 검색을 한다면 자연어 검색과 거의 흡사하게 동작함을 알 수 있습니다.
검색어 확장
검색어 확장(Query Expansion) 은 사용자가 쿼리에 사용한 검색어로 검색된 결과에서 공통으로 발견되는 단어들을 모아서 다시 한번 더 검색을 수행하는 기능 입니다.
검색어 확장 기능은 다음과 같은 요건을 충족하기 위해서 추가 된 기능 입니다.
테스트 테이블과 데이터를 입력 해보도록 하겠습니다.
-- 테스트 테이블 생성 mysql> create table tb_articles_3 (id int not null auto_increment primary key, doc_title varchar(20), doc_body varchar(200), FULLTEXT INDEX ftx_doc_title_doc_body(doc_title,doc_body) WITH PARSER ngram ); -- 테스트 데이터 입력 mysql> insert into tb_articles_3 values (1,'MySQL','MySQL is database'), (2,'MySQL Posting','MySQL is best open source rdbms'), (3,'Oracle','Oracle is database'), (4,'Oracle Document','Oracle is the world\'s most popular rdbms');
아래의 쿼리를 수행하게 되면 "database" 라는 키워드가 들어간 게시물만 검색하게 됩니다.
• 예제 8)
mysql> select id ,doc_title,doc_body
from tb_articles_3
where MATCH(doc_title,doc_body) AGAINST('database');
+----+-----------+--------------------+
| id | doc_title | doc_body |
+----+-----------+--------------------+
| 1 | MySQL | MySQL is database |
| 3 | Oracle | Oracle is database |
+----+-----------+--------------------+
그런데 검색한 "database" 뿐만 아니라 "MySQL" 이나 "Oracle" 와 같은 DBMS 이름도 검색하고 싶을 경우에는 여러 번의 전문 검색 쿼리를 실행 해야 합니다.
이럴 경우 검색어 확장(Query Expansion) 을 사용 하면 유용할 수 있습니다.
만약 해당 기능이 없다면 전문검색 쿼리를 여러번 실행 하면 될 수 있지만 데이터 건수가 많을 경우 연관된 단어를 알아내는 것은 쉬운 것이 아닐 것 입니다.
그래서 아래와 검색어 확장 기능을 사용하면 연관된 검색 결과도 같이 보여주게 됩니다.
• 예제 9)
mysql> select id ,doc_title,doc_body
from tb_articles_3
where MATCH(doc_title,doc_body) AGAINST('database' with query expansion);
+----+-----------------+------------------------------------------+
| id | doc_title | doc_body |
+----+-----------------+------------------------------------------+
| 3 | Oracle | Oracle is database |
| 1 | MySQL | MySQL is database |
| 4 | Oracle Document | Oracle is the world's most popular rdbms |
| 2 | MySQL Posting | MySQL is best open source rdbms |
+----+-----------------+------------------------------------------+
MySQL 검색어 확장 기능은 "Blind query expansion" 알고리즘을 사용하고 있으며, 이는 검색어 결과에서 자주 사용되는 단어들을 모아서 다시 전문 검색 쿼리를 실행할 뿐 입니다.
검색어 "database" 쿼리의 결과에서 java 나 python 이란 단어가 있었다면 게시물 중에서 database 라는 단어는 포함돼 있지 않지만 java 나 python 이란 단어만 포함된 모든 결과도 가져왔을수 있습니다.
전문 검색 기능의 디버깅
MySQL 서버의 예전 버전에서는 MyISAM 에서만 전문검색이 가능했지만, 최근의 버전에서는 InnoDB 에서도 사용이 가능해지면서 불용어(stopword) 나 토큰 사이즈 등을 제어하는 시스템 변수가 다양화 되었습니다.
그리고 인덱스 생성시 "WITH PARSER ngram" 과 같은 옵션을 사용해야 하는 것 과 같이 기존과 다른 다양한 옵션도 존재 합니다.
그래서 여러가지 이유로 실수 또는 설정의 문제 등의 문제로 전문 검색을 사용하지 못하는 경우가 발생할 수 있습니다.
MySQL 서버에서는 전문 검색 쿼리의 오류의 원인을 쉽게 찾을 수 있는 기능을 제공 합니다. 위에서 설명한 innodb_ft_aux_table 시스템 변수를 사용하면 설정한 테이블에 설정된 전문검색 인덱스의 설정 정보 와 토큰화 정보를 확인 할 수 있습니다.
• innodb_ft_aux_table 시스템 변수 설정
mysql> SET global innodb_ft_aux_table= 'test/tb_articles_3';
설정은 "데이터베이스(스키마)명/테이블명" 형식으로 기입하여 시스템 변수를 설정 하시면 됩니다.
• information_schema.innodb_ft_config
전문 검색 인덱스의 설정 내용을 보여 줍니다.
mysql> select * from information_schema.innodb_ft_config; +---------------------------+-------+ | KEY | VALUE | +---------------------------+-------+ | optimize_checkpoint_limit | 180 | | synced_doc_id | 6 | | stopword_table_name | | | use_stopword | 1 | +---------------------------+-------+
• innodb_ft_index_table 와 innodb_ft_index_cache
information_schema.innodb_ft_index_table 와 information_schema.innodb_ft_index_cache 는 전문 검색 인덱스가 가지고 있는 인덱스 엔트리의 목록을 보여줍니다.
엔트리는 토큰들이 어떤 레코드에 몇번 사용됐는지, 레코드별로 문자 위치가 어디인지 등의 정보를 확인 할 수 있습니다.
테이블에 레코드가 새롭게 INSERT 되면 MySQL 서버는 전문 검색 인덱스를 위한 토큰을 분리해서 바로 디스크로 저장하는 대신에 메모리에 임시로 저장을 하게 됩니다.
이때 임시로 저장되는 공간이 innodb_ft_index_cache 테이블 입니다.
innodb_ft_index_cache 테이블이 사용하는 메모리 공간이 innodb_ft_cache_size 크기를 넘어서면 한꺼번에 모아서 디스크로 내려쓰게 됩니다.
디스크로 내려써지게 되면 innodb_ft_index_table 에서 내용을 확인 할 수 있습니다.
innodb_ft_cache_size 기본 값은 8000000 bytes 이며 해당 시스템 변수를 크게 늘린다면 성능상 도움이 될 것 입니다.
메모리에 있는 내용을 명시적으로 디스크로 플러시를 하기 위해서는 innodb_optimize_fulltext_only=ON 를 설정 후 OPTIMIZE TABLE 명령어 수행을 하면 됩니다.
mysql> SET GLOBAL innodb_optimize_fulltext_only=ON; mysql> OPTIMIZE TABLE tb_articles_3; mysql> SET GLOBAL innodb_optimize_fulltext_only=OFF;
• innodb_ft_index_table 조회 결과)
mysql> select * from
information_schema.innodb_ft_index_table;
+------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+------+--------------+-------------+-----------+--------+----------+
| bm | 3 | 5 | 2 | 3 | 42 |
| bm | 3 | 5 | 2 | 5 | 53 |
| ce | 3 | 3 | 1 | 3 | 37 |
| cl | 4 | 5 | 2 | 4 | 3 |
| cl | 4 | 5 | 2 | 4 | 7 |
| cl | 4 | 5 | 2 | 5 | 3 |
| cl | 4 | 5 | 2 | 5 | 16 |
| cu | 5 | 5 | 1 | 5 | 9 |
| db | 3 | 5 | 2 | 3 | 41 |
| db | 3 | 5 | 2 | 5 | 52 |
| do | 5 | 5 | 1 | 5 | 7 |
| es | 3 | 3 | 1 | 3 | 24 |
| he | 5 | 5 | 1 | 5 | 27 |
| ld | 5 | 5 | 1 | 5 | 33 |
< .. 내용이 긴 관계로 중략... >
• innodb_ft_deleted 와 innodb_ft_being_deleted
테이블의 레코드가 삭제 되면 어떤 레코드가 삭제 되었는지, 그리고 어떤 레코드가 현재 전문 검색 인덱스에서 삭제 되고 있는지를 확인 할 수 있습니다.
해당 포스팅은 Real MySQL 8.0 책의 많은 내용 중에서 일부분의 내용만 함축적으로 정리한 것으로 모든 내용 확인 및 이해를 위해서 직접 책을 통해 모든 내용을 확인하시는 것을 권해 드립니다.
Reference
Reference Book
• Real MySQL 8.0
Reference URL
• mysql.com/fulltext-search-ngram
• mysql.com/innodb-full-text-n-gram-parser-ko
• mysql.com/fulltext-boolean
연관된 다른 글
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
