어드민플레이 - Linux Mysql Q&A - FULLTEXT 인덱스와 서치

FULLTEXT 인덱스와 서치

【사용법】

MATCH (col1,col2,...) AGAINST ( expr [IN BOOLEAN MODE ? WITH QUERY EXPANSION])

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은 검색되지 않는 이유는 왜일까?

? show variables;를 실행하여 확인하여 보면,

? ft_min_word_len =4로 지정되어 있기 때문에 4 문자 이하는 검색되지 않는다.

? 이를 변경하려면 변수값을 변경하면 된다.

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를 반환

Full-text 제약조건

? fulltext search은 MyISAM 형식의 테이블에서만 가능하다.
? Fulltext search는 대부분의 multi-byte character sets을 사용할 수 있다. 단, Unicode는 안되지만, utf8 문자셋은 사용할 수 있으나 ucs2 문자셋은 예외이다.
? 중국어, 일어와 같은 표의문자(ideographic) 언어는 단어의 한계(delimiter)을 받지 않기 때문에 FULLTEXT 해석기(parser)는 타언어에서 행하는 단어의 시작과 끝을 결정하지 못한다.
? 하나의 테이블내에서 다중문자 셋을 사용하는 동안, FULLTEXT 인덱스 내의 모든 컬럼은 동일한 문자셋과 대조(collation)를 반드시 사용해야 한다.
? MATCH() 컬럼 목록은 테이블에서 정의된 FULLTEXT 인덱스의 컬럼 목록과 정확히 일치해야 하지만, IN BOOLEAN MODE에서의 MATCH()에서는 예외이다.
? AGAINST()의 argument는 constant string이어야 한다.

Full-text 서치를 위한 튜닝

다음 full-text 변수는 서버가 시작될 때 사용된 것으로 변경될 수 없다.
? 인덱스의 최소 단어 길이는 MySQL 변수 정의 ft_min_word_len로 선언된다. 만약 ft_min_word_len을 변경하면, FULLTEXT 인덱스를 새로 만들어야 한다.
? stopword 목록은 ft_stopword_file에서 읽어 들이므로 stopword 목록을 변경하면, FULLTEXT 인덱스를 새로 만들어야 한다.
? 비중(weight)은 50% threshold가 적용되지만, 이를 사용하지 않으려면 'myisam/ftdefs.h' 파일을 다음과 같이 변경한다.

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"는 안 찾음

   그런 다음 MySQL을 다시 컴파일해야 한다. 이렇게 하면, MATCH() 함수의 관계값을 적
   절히 준비하기 위하여 MySQL 능력이 대단히 떨어진다. 그러므로 50% threshold를 해지
   하는 것보다 IN BOOLEAN MODE를 사용하는 것이 좋다. 
? boolean full-text 서치에 사용된 연산자가 서치 엔진에서 바꾸어 사용하는 편이 낳을 때
   가 있다. 이러한 연산자는 ft_boolean_syntax 변수로 정의되어야 한다. 아직 이  변수는 
   read-only이며, 'myisam/ft_static.c'에 있다.

이렇게 변경한 다음에는 FULLTEXT 인덱스를 새로 만들어야 하지만, MyISAM형 테이블인 경우
라면 간단히 다음과 같이 실행하여 인덱스 파일을 새로 만들 수 있다.

        mysql> REPAIR TABLE tbl_name QUICK;

fulltext로 선언된 테이블은 show table status;로 확인이 않되고, show create table articles;와 같이 수행하여 테이블 형식을 확인할 수 있다.

mysql> show create table articles;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                              |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| articles | CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(200) default NULL,
  `body` text,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `title` (`title`,`body`)
) TYPE=MyISAM CHARSET=euckr |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql>

【예제】
mysql> create table test(id int unsigned not null auto_increment primary key,
    -> title varchar(200) not null,
    -> body text,
    -> fulltext key (body));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test (`id`, `title`, `body`) VALUES 
    -> (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 ...');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select body from test
    -> where match(body) against('MySQL');
+-------------------------------------+
| body                                |
+-------------------------------------+
| When configured properly, MySQL ... |
+-------------------------------------+
1 row in set (0.01 sec)

mysql> select * from test
   ->  where match(body) against('tutorial');
 
+----+------------------+-----------------------------------+
| id | title            | body                              |
+----+------------------+-----------------------------------+
|  3 | Optimising MySQL | In this tutorial we will show ... |
+----+------------------+-----------------------------------+
1 row in set (0.00 sec)

mysql> select id,match(body) against('tutorial') from test;
+----+---------------------------------+
| id | match(body) against('tutorial') |
+----+---------------------------------+
|  1 |                               0 |
|  2 |                               0 |
|  3 |                 1.5732531547546 |
|  4 |                               0 |
|  5 |                               0 |
|  6 |                               0 |
+----+---------------------------------+
6 rows in set (0.00 sec)

mysql> select id,body from articles where match(body) against('you');
Empty set (0.00 sec)

mysql>

위 예에서 you와 매칭되는 데이터가 없다고 나오는 것은 시스템의 변수 지정에서 ft_min_word_len=4로 되어 있기 대문이다.

변경 전	#define GWS_IN_USE GWS_PROB
변경 후	#define GWS_IN_USE GWS_FREQ

로그인

FULLTEXT 인덱스와 서치

단축키

단축키

Who's ADMINPLAY