【사용법】
MySQL에서는 full-text 인덱스 만들기와 full-text 서치 기능을 제공한다.
full-text 인덱스 기능은 인덱스 타입이 FULLTEXT가 되며, FULLTEXT 인덱스는 MyISAM형 테이블에 사용되고, CHAR, VARCHAR, TEXT 컬럼에 대해서 CREATE TABLE 문에서 처음부터 만들거나, ALTER TABLE, CREATE INDEX 문을 사용하여 나중에 추가할 수 있다.
대단히 많은 데이터 집합을 가진 경우,
FULLTEXT 인덱스가 없는 테이블로 빠르게 데이터를 load 하여 ALTER TABLE이나 CREATE INDEX 문을 사용하여 인덱스를 만드는 것이 이미 FULLTEXT 인덱스를 가지고 있는 테이블로 데이터를 load하는 것보다 속도가 빠르다.
Full-text 서치는 MATCH()...AGAINST() 함수를 수행하는 것이다.
【예제】 mysql> CREATE TABLE articles ( -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, -> title VARCHAR(200), -> body TEXT, -> FULLTEXT (title,body)); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO articles VALUES -> ('', 'MySQL Tutorial','DBMS stands for DataBase ...'), -> ('','How To Use MySQL Efficiently','After you went through a ...'), -> ('', 'Optimising 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 ...'); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from articles; +----+------------------------------+------------------------------------------+ | id | title | body | +----+------------------------------+------------------------------------------+ | 1 | MySQL Tutorial | DBMS stands for DataBase ... | | 2 | How To Use MySQL Efficiently | After you went through a ... | | 3 | Optimising MySQL | In this tutorial we will show ... | | 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... | | 5 | MySQL vs. YourSQL | In the following database comparison ... | | 6 | MySQL Security | When configured properly, MySQL ... | +----+------------------------------+------------------------------------------+ 6 rows in set (0.00 sec) mysql> SELECT * FROM articles -> WHERE MATCH (title,body) AGAINST('database'); +----+-------------------+------------------------------------------+ | id | title | body | +----+-------------------+------------------------------------------+ | 5 | MySQL vs. YourSQL | In the following database comparison ... | | 1 | MySQL Tutorial | DBMS stands for DataBase ... | +----+-------------------+------------------------------------------+ 2 rows in set (0.03 sec)MATCH() 함수는 FULLTEXT 인덱스에 하나 이상의 컬럼의 집합으로 구성된 텍스트 모임에서 스트링을 찾는 것이며, 이 때 AGAINST()에 찾을 스트링을 지정한다.
서치는 대소문자 구분이 없으며, 테이블 내의 각 row마다 찾아 MATCH()는 관계값(relevance value)을 수치로 반환하는데 '0'은 유사성이 전연 없다는 것을 의미한다.
또한 논리적 모드의 서치(IN BOOLEAN MODE)도 가능하다.
mysql> select id,body from articles -> where match(title,body) against('root'); +----+-------------------------------------+ | id | body | +----+-------------------------------------+ | 4 | 1. Never run mysqld as root. 2. ... | +----+-------------------------------------+ 1 row in set (0.00 sec) mysql> select id,body from articles -> where match(title,body) against('run'); Empty set (0.00 sec) mysql>위의 예에서 root는 검색되는데, run은 검색되지 않는 이유는 왜일까?
mysql> show variables like 'ft_m%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | ft_max_word_len | 84 | | ft_min_word_len | 4 | +-----------------+-------+ 2 rows in set (0.00 sec) mysql>
다음은 WHERE 절도 ORDER BY 절도 사용하지 않았기 때문에 각 줄이 가지는 관계값 을 무순서로 반환된 예이다.
mysql> SELECT id,MATCH(title,body) AGAINST('Tutorial') -> FROM articles; +----+---------------------------------------+ | id | MATCH(title,body) AGAINST('Tutorial') | +----+---------------------------------------+ | 1 | 0.65545833110809 | | 2 | 0 | | 3 | 0.66266459226608 | | 4 | 0 | | 5 | 0 | | 6 | 0 | +----+---------------------------------------+ 6 rows in set (0.00 sec) 다음은 관계값을 내림차순으로 반환한 예이다. 그러기 위해서는 MATCH()를 두 번 사용해야 한다. mysql> SELECT id, body, MATCH(title,body) AGAINST -> ('Security implications of running MySQL as root') AS score -> FROM articles -> WHERE MATCH (title,body) AGAINST -> ('Security implications of running MySQL as root'); +----+-------------------------------------+-----------------+ | id | body | score | +----+-------------------------------------+-----------------+ | 4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 | | 6 | When configured properly, MySQL ... | 1.3114095926285 | +----+-------------------------------------+-----------------+ 2 rows in set (0.00 sec) MySQL에서는 text를 단어로 간단히 분리한다. "단어"는 문자, 디지트, ''', '_'로 연속해서 구성된 문자이며, stopword 목록에 제시된 "단어"나 3 글자 이내로 작은 것은 무시한다.단어의 비중(weight)에 따라 표시되는 값이 다르게 나타나는데, 어떤 희귀한 단어는 높은 비중을 두는 경우도 있고 그렇지 않은 경우도 있을 수 있다. 그러므로 단어의 비중은 row의 관계값(relevance value)을 계산하는데 사용된다.
이 방법은 데이터 양이 많은 경우에는 좋은 방법이 되지만, 데이터 양이 적은 경우에는 단어의 분포도에 영향을 받지 않아 심지어는 다음의 예처럼 엉뚱한 결과를 나타날 수도 있다.
mysql> SELECT * FROM articles WHERE MATCH (title,body) -> AGAINST ('MySQL'); Empty set (0.00 sec)위 예시에서 MySQL 단어를 찾은 결과가 없는 것으로 출력된 예이다. 이 예에서는 MySQL이라는 단어가 절반 이상의 row에서 나타나기 때문에 the, some과 같이 내장된 stopword처럼 처리하여 단어의 비중을 0으로 취급하였다.
이 예는 최악의 예이지만, 1GB 테이블에서 절반 이상의 row에 동일한 단어가 포함되는 경우는 없다.
이와 같이 특별한 경우에 비중값을 갖지 않는 경우에 대비하여 IN BOOLEAN MODE라고 하는 boolean full-text를 사용하여 해결한다.
mysql> SELECT * FROM articles WHERE MATCH (title,body) -> AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE); +----+------------------------------+-------------------------------------+ | id | title | body | +----+------------------------------+-------------------------------------+ | 1 | MySQL Tutorial | DBMS stands for DataBase ... | | 2 | How To Use MySQL Efficiently | After you went through a ... | | 3 | Optimising MySQL | In this tutorial we will show ... | | 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... | | 6 | MySQL Security | When configured properly, MySQL ... | +----+------------------------------+-------------------------------------+ 5 rows in set (0.00 sec) mysql> 위 쿼리에서 단어 MySQL를 포함하고 있는 모든 row를 받지만, 단어 yourSQL을 포함한 row는 제외한다.boolean mode 서치에서는 row를 관계값의 내림차순으로 자동으로 소팅하지 않는다. 출력된 예에서와 같이 MySQL을 두 번이나 포함해서 관계값이 높더라도 뒤에 출력 된 것을 보면 알 수 있다.
boolean full-text 서치는 FULLTEXT 인덱스가 없더라도 동작하며 속도는 좀 느리다. boolean full-text 서치에서 지원되는 연산자는 다음과 같다.
+ | 지정한 단어가 포함된 row를 반환 |
---|---|
- | 지정한 단어가 포함된 row를 제외하고 반환 +,- 부호가 없는 boolean full-text 서치는 MATCH()...AGAINST()만 사용한 경우와 같음 |
연산자 없음 | +, -가 지정되지 않음 경우 디폴트로 단어는 옵션이 됨, IN BOOLEAN MODE 변경없이 MATCH()...AGAINST() 만 함 |
< > | 관계값에 대한 단어의 contribution 비교하여 부등호에 해당하는 row를 반환 |
() | () 내에 group word를 표시함 |
~ | negation(NOT) 연산자처럼 지정한 문자를 포함하지 않는 row를 반환 |
* | truncation(절단) 연산자로 와일드카드 *처럼 쓰이며 해당되는 row를 반환 |
" | phrase는 입력된 문자 구를 포함하는 row를 반환 |
apple banana | 두 단어 중 적어도 하나라도 있는 row를 찾음 |
---|---|
+apple +juice | 두 단어 모두 있는 row를 찾음 |
+apple macintosh | 단어 apple를 가지고 있는 row중에서 단어 macintosh를 가지면 더 높음 |
+apple -macintosh | 단어 apple는 포함되고, 단어 macintosh는 포함되지 않는 row를 반환 예: match(title,body) against('+apple -macintosh' in boolean mode) |
+apple +(>pie <strudel) | apple과 pie, apple과 strudel중에서 apple pie가 apple strudel보다 더 높음 |
apple* | apple로 시작되는 문자를 찾음, 예: apples, applet |
"some words" | "some words of wisdom"은 찾지만, "some noise word"는 안 찾음 |
변경 전 | #define GWS_IN_USE GWS_PROB |
변경 후 | #define GWS_IN_USE GWS_FREQ |