14.9.1 Natural Language Full-Text Searches

14.9.1 Natural Language Full-Text Searches

기본적으로, 또는 IN NATURAL LANGUAGE MODE modifier를 사용하면 MATCH() 함수는 문자열에 대해 자연어 방식으로 텍스트 컬렉션을 검색합니다. 컬렉션은 FULLTEXT 인덱스에 포함된 하나 이상의 컬럼 집합입니다. 검색 문자열은 AGAINST()의 인수로 전달됩니다. 테이블의 각 로우에 대해 MATCH()는 관련성 값을 반환합니다. 이는 검색 문자열과 MATCH() 목록에 지정된 컬럼의 해당 로우 내 텍스트 사이의 유사성 측정값입니다.

1mysql> CREATE TABLE articles (
2    ->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
3    ->   title VARCHAR(200),
4    ->   body TEXT,
5    ->   FULLTEXT (title,body)
6    -> ) ENGINE=InnoDB;
7Query OK, 0 rows affected (0.08 sec)
8
9mysql> INSERT INTO articles (title,body) VALUES
10    ->   ('MySQL Tutorial','DBMS stands for DataBase ...'),
11    ->   ('How To Use MySQL Well','After you went through a ...'),
12    ->   ('Optimizing MySQL','In this tutorial, we show ...'),
13    ->   ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
14    ->   ('MySQL vs. YourSQL','In the following database comparison ...'),
15    ->   ('MySQL Security','When configured properly, MySQL ...');
16Query OK, 6 rows affected (0.01 sec)
17Records: 6  Duplicates: 0  Warnings: 0
18
19mysql> SELECT * FROM articles
20    -> WHERE MATCH (title,body)
21    -> AGAINST ('database' IN NATURAL LANGUAGE MODE);
22+----+-------------------+------------------------------------------+
23| id | title             | body                                     |
24+----+-------------------+------------------------------------------+
25|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
26|  5 | MySQL vs. YourSQL | In the following database comparison ... |
27+----+-------------------+------------------------------------------+
282 rows in set (0.00 sec)

기본적으로 검색은 대소문자를 구분하지 않고 수행됩니다. 대소문자를 구분하는 풀텍스트 검색을 수행하려면 인덱스가 지정된 컬럼에 대해 대소문자 구분 또는 바이너리 콜레이션을 사용해야 합니다. 예를 들어, utf8mb4 문자 집합을 사용하는 컬럼에 utf8mb4_0900_as_cs 또는 utf8mb4_bin 콜레이션을 지정하면 풀텍스트 검색에서 대소문자를 구분하게 됩니다.

MATCH()를 WHERE 절에서 사용하는 경우(앞의 예와 같이), 다음 조건이 충족되는 한 반환된 로우는 관련성이 가장 높은 것부터 자동으로 정렬됩니다:

• 명시적인 ORDER BY 절이 없어야 합니다.

• 검색은 테이블 스캔이 아니라 풀텍스트 인덱스 스캔을 사용하여 수행되어야 합니다.

• 쿼리가 테이블을 조인하는 경우, 풀텍스트 인덱스 스캔을 수행하는 테이블은 조인에서 가장 왼쪽에 위치한 non-constant 테이블이어야 합니다.

방금 나열한 조건을 고려하면, 필요하거나 원하는 경우에는 ORDER BY를 사용하여 명시적으로 정렬 순서를 지정하는 편이 대개 더 수월합니다.

관련성 값은 0 이상의 부동소수점 숫자입니다. 관련성이 0이면 유사성이 없음을 의미합니다. 관련성은 로우(문서) 내 단어 수, 로우 내 고유 단어 수, 컬렉션 내 전체 단어 수, 특정 단어를 포함하는 로우 수를 기반으로 계산됩니다.

참고

“document”라는 용어는 “row”라는 용어와 서로 바꿔 사용할 수 있으며, 둘 다 로우의 인덱스가 지정된 부분을 가리킵니다. “collection”이라는 용어는 인덱스가 지정된 컬럼을 가리키며, 모든 로우를 포함합니다.

단순히 매치 개수를 세려면 다음과 같은 쿼리를 사용할 수 있습니다:

1mysql> SELECT COUNT(*) FROM articles
2    -> WHERE MATCH (title,body)
3    -> AGAINST ('database' IN NATURAL LANGUAGE MODE);
4+----------+
5| COUNT(*) |
6+----------+
7|        2 |
8+----------+
91 row in set (0.00 sec)

다음과 같이 쿼리를 다시 작성하는 것이 더 빠를 수 있습니다:

1mysql> SELECT
2    -> COUNT(IF(MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL))
3    -> AS count
4    -> FROM articles;
5+-------+
6| count |
7+-------+
8|     2 |
9+-------+
101 row in set (0.03 sec)

첫 번째 쿼리는 (관련성 기준으로 결과를 정렬하는) 추가 작업을 수행하지만, WHERE 절을 기반으로 인덱스 룩업도 사용할 수 있습니다. 검색이 소수의 로우와만 매치되는 경우 인덱스 룩업 덕분에 첫 번째 쿼리가 더 빠를 수 있습니다. 두 번째 쿼리는 전체 테이블 스캔을 수행하며, 검색어가 대부분의 로우에 존재하는 경우 인덱스 룩업보다 더 빠를 수 있습니다.

natural-language 풀텍스트 검색의 경우, MATCH() 함수에 지정된 컬럼은 테이블에 존재하는 어떤 FULLTEXT 인덱스에 포함된 컬럼과 동일해야 합니다. 앞의 쿼리에서, MATCH() 함수에 지정된 컬럼 (title과 body)이 article 테이블의 FULLTEXT 인덱스 정의에서 지정된 컬럼과 동일하다는 점에 주목하십시오. title이나 body를 따로 검색하려면 각 컬럼에 대해 별도의 FULLTEXT 인덱스를 생성해야 합니다.

boolean 검색이나 쿼리 확장을 사용한 검색을 수행할 수도 있습니다. 이러한 검색 유형은 Section 14.9.2, “Boolean Full-Text Searches”와 Section 14.9.3, “Full-Text Searches with Query Expansion”에서 설명합니다.

인덱스를 사용하는 풀텍스트 검색에서 MATCH() 절에 지정할 수 있는 컬럼은 인덱스가 여러 테이블에 걸쳐 있을 수 없기 때문에 단일 테이블의 컬럼만 가능합니다. MyISAM 테이블의 경우, 인덱스가 없어도(속도는 더 느리지만) boolean 검색을 수행할 수 있으며, 이 경우 여러 테이블의 컬럼을 지정할 수 있습니다.

앞의 예는 로우가 관련성 내림차순으로 반환되는 MATCH() 함수 사용 방법을 보여 주는 기본적인 예입니다. 다음 예는 관련성 값을 명시적으로 조회하는 방법을 보여 줍니다. SELECT 문에 WHERE나 ORDER BY 절이 없기 때문에 반환된 로우는 정렬되지 않습니다:

1mysql> SELECT id, MATCH (title,body)
2    -> AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) AS score
3    -> FROM articles;
4+----+---------------------+
5| id | score               |
6+----+---------------------+
7|  1 | 0.22764469683170319 |
8|  2 |                   0 |
9|  3 | 0.22764469683170319 |
10|  4 |                   0 |
11|  5 |                   0 |
12|  6 |                   0 |
13+----+---------------------+
146 rows in set (0.00 sec)

다음 예는 더 복잡합니다. 이 쿼리는 관련성 값을 반환하고, 로우를 관련성 내림차순으로 정렬합니다. 이 결과를 얻으려면 MATCH()를 두 번 지정합니다. 한 번은 SELECT 목록에서, 한 번은 WHERE 절에서 지정합니다. MySQL 옵티마이저는 두 MATCH() 호출이 동일하다는 것을 인지하고 풀텍스트 검색 코드를 한 번만 호출하므로 이로 인한 추가 오버헤드는 없습니다.

1mysql> SELECT id, body, MATCH (title,body)
2    ->   AGAINST ('Security implications of running MySQL as root'
3    ->   IN NATURAL LANGUAGE MODE) AS score
4    -> FROM articles
5    ->   WHERE MATCH (title,body)
6    ->   AGAINST('Security implications of running MySQL as root'
7    ->   IN NATURAL LANGUAGE MODE);
8+----+-------------------------------------+-----------------+
9| id | body                                | score           |
10+----+-------------------------------------+-----------------+
11|  4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 |
12|  6 | When configured properly, MySQL ... | 1.3114095926285 |
13+----+-------------------------------------+-----------------+
142 rows in set (0.00 sec)

큰따옴표(") 문자로 둘러싸인 phrase는 입력한 그대로의 phrase 를 포함하는 로우에만 매치됩니다. 풀텍스트 엔진은 phrase를 단어로 분리하고, 그 단어들을 FULLTEXT 인덱스에서 검색합니다. nonword 문자들은 정확히 일치할 필요가 없습니다. phrase 검색에서는 매치가 phrase와 정확히 같은 단어들을, 같은 순서로 포함하기만 하면 됩니다. 예를 들어 "test phrase"는 "test, phrase"와 매치됩니다. phrase에 인덱스에 존재하는 단어가 하나도 없으면 결과는 비어 있습니다. 예를 들어, 모든 단어가 스톱워드이거나 인덱스 대상 단어의 최소 길이보다 짧으면 결과는 비어 있습니다.

MySQL FULLTEXT 구현은 연속된 실제 단어 문자(letters, digits, underscore)의 시퀀스를 하나의 단어로 간주합니다. 이 시퀀스에는 apostrophe(')도 포함될 수 있지만, 연속해서 두 개 이상 포함될 수는 없습니다. 즉, aaa'bbb는 하나의 단어로 간주되지만, aaa''bbb는 두 개의 단어로 간주됩니다. 단어의 시작이나 끝에 있는 apostrophe는 FULLTEXT 파서에 의해 제거됩니다. 'aaa'bbb'는 aaa'bbb로 파싱됩니다.

내장 FULLTEXT 파서는 일정한 구분 문자(예: ``(space), ,(comma), .(period))를 찾아 단어의 시작과 끝을 판별합니다. 단어가 구분 문자로 구분되지 않는 언어(예: 중국어)의 경우, 내장 FULLTEXT 파서는 단어의 시작과 끝을 판별할 수 없습니다. 이러한 언어에서 내장 FULLTEXT 파서를 사용하는 FULLTEXT 인덱스에 단어 또는 다른 인덱스 대상 term을 추가하려면, 임의의 구분 문자로 단어가 분리되도록 사전 처리해야 합니다. 또는 ngram 파서 플러그인(중국어, 일본어, 한국어용)이나 MeCab 파서 플러그인(일본어용)을 사용해서 FULLTEXT 인덱스를 생성할 수 있습니다.

내장 풀텍스트 파서를 대체하는 플러그인을 작성하는 것도 가능합니다. 자세한 내용은 The MySQL Plugin API를 참조하십시오. 파서 플러그인의 예제 소스 코드는 MySQL 소스 배포본의 plugin/fulltext 디렉터리를 참조하십시오.

풀텍스트 검색에서 다음과 같은 단어는 무시됩니다:

• 너무 짧은 단어는 모두 무시됩니다. 풀텍스트 검색에서 검색되는 단어의 기본 최소 길이는 InnoDB 검색 인덱스의 경우 3자, MyISAM의 경우 4자입니다. 인덱스를 생성하기 전에 설정 옵션을 설정하여 이 기준값을 제어할 수 있습니다. InnoDB 검색 인덱스의 경우 innodb_ft_min_token_size 설정 옵션을, MyISAM의 경우 ft_min_word_len을 설정합니다.

참고

이 동작은 ngram 파서를 사용하는 FULLTEXT 인덱스에는 적용되지 않습니다. ngram 파서의 경우 토큰 길이는 ngram_token_size 옵션으로 정의됩니다.

• 스톱워드 리스트에 있는 단어는 무시됩니다. 스톱워드는 “the”나 “some”과 같이 너무 흔해서 의미 값이 0으로 간주되는 단어입니다. 내장 스톱워드 리스트가 있지만, 사용자 정의 리스트로 대체할 수 있습니다. 스톱워드 리스트와 관련 설정 옵션은 InnoDB 검색 인덱스와 MyISAM 검색 인덱스에서 서로 다릅니다. 스톱워드 처리에는 InnoDB 검색 인덱스의 경우 innodb_ft_enable_stopword, innodb_ft_server_stopword_table, innodb_ft_user_stopword_table 설정 옵션이 사용되고, MyISAM 검색 인덱스의 경우 ft_stopword_file이 사용됩니다.

기본 스톱워드 리스트와 이를 변경하는 방법은 Section 14.9.4, “Full-Text Stopwords”를 참조하십시오. 기본 최소 단어 길이를 변경하는 방법은 Section 14.9.6, “Fine-Tuning MySQL Full-Text Search”에 설명되어 있습니다.

컬렉션과 쿼리에 포함된 모든 올바른 단어는 해당 단어의 컬렉션 또는 쿼리 내 중요도에 따라 가중치가 부여됩니다. 따라서 많은 document에 존재하는 단어는 해당 컬렉션에서 의미 값이 낮기 때문에 더 낮은 가중치를 갖습니다. 반대로, 드문 단어는 더 높은 가중치를 갖습니다. 이 단어들의 가중치를 결합하여 로우의 관련성을 계산합니다. 이 기법은 대규모 컬렉션에서 가장 효과적입니다.

MyISAM Limitation

테이블이 매우 작은 경우, 단어 분포가 의미 값을 충분히 반영하지 못하므로, MyISAM 테이블의 검색 인덱스에서는 때때로 이상한 결과가 나올 수 있습니다. 예를 들어, 앞서 제시한 articles 테이블의 모든 로우에 “MySQL”이라는 단어가 존재하지만, MyISAM 검색 인덱스에서 이 단어를 검색하면 결과가 나오지 않습니다:

1mysql> SELECT * FROM articles
2    -> WHERE MATCH (title,body)
3    -> AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);
4Empty set (0.00 sec)

검색 결과가 비어 있는 이유는 “MySQL”이라는 단어가 로우의 최소 50% 이상에 존재하므로 실질적으로 스톱워드처럼 취급되기 때문입니다. 이 필터링 기법은 1GB 테이블에서 결과가 전체 로우의 절반씩 반환되는 상황을 피하고 싶은 대규모 데이터 세트에는 적합하지만, 인기 있는 term에 대해 좋지 않은 결과를 초래할 수 있는 소규모 데이터 세트에는 그다지 적합하지 않습니다.

처음 풀텍스트 검색을 시험해 볼 때는 이 50% 임계값 때문에 놀랄 수 있으며, 이로 인해 InnoDB 테이블이 풀텍스트 검색 실험에 더 적합합니다. MyISAM 테이블을 만들고 여기에 한두 개의 텍스트 로우만 삽입하면, 텍스트에 포함된 모든 단어가 로우의 최소 50% 이상에 존재하게 됩니다. 그 결과, 테이블에 더 많은 로우가 쌓이기 전까지는 어떤 검색도 결과를 반환하지 않습니다. 이 50% 제한을 우회해야 하는 사용자는 InnoDB 테이블에 검색 인덱스를 구축하거나, Section 14.9.2, “Boolean Full-Text Searches”에 설명된 boolean 검색 모드를 사용할 수 있습니다.