Loading...
MySQL 9.5 Reference Manual 9.5의 14.9.2 Boolean Full-Text Searches의 한국어 번역본입니다.
아래의 경우에 피드백에서 신고해주신다면 반영하겠습니다.
감사합니다 :)
MySQL은 IN BOOLEAN MODE modifier를 사용하여 boolean full-text search를 수행할 수 있습니다. 이 modifier를 사용하면, search string에서 단어의 시작 또는 끝 위치에 따라 특정 문자에 특별한 의미가 부여됩니다. 다음 query에서 + 및 - operator는 각각 단어가 match가 발생하기 위해 반드시 포함되어야 하는지, 반드시 포함되지 말아야 하는지를 나타냅니다. 따라서 이 query는 “MySQL”이라는 단어는 포함하지만 “YourSQL”이라는 단어는 포함하지 않는 모든 row를 가져옵니다:
1mysql> SELECT * FROM articles WHERE MATCH (title,body) 2 -> AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE); 3+----+-----------------------+-------------------------------------+ 4| id | title | body | 5+----+-----------------------+-------------------------------------+ 6| 1 | MySQL Tutorial | DBMS stands for DataBase ... | 7| 2 | How To Use MySQL Well | After you went through a ... | 8| 3 | Optimizing MySQL | In this tutorial, we show ... | 9| 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... | 10| 6 | MySQL Security | When configured properly, MySQL ... | 11+----+-----------------------+-------------------------------------+
참고
이 기능을 구현할 때 MySQL은 때때로 implied Boolean logic이라 불리는 방식을 사용합니다. 이 방식에서
+ 는 AND 를 의미하고
- 는 NOT 을 의미하며
[no operator] 는 OR 를 의미합니다.
Boolean full-text search는 다음과 같은 특징을 가집니다:
relevance가 감소하는 순서로 row를 자동 정렬하지 않습니다.
InnoDB 테이블은 boolean query를 수행하기 위해 MATCH() expression에 포함된 모든 column에 대해 FULLTEXT 인덱스가 필요합니다. MyISAM search 인덱스에 대한 boolean query는 FULLTEXT 인덱스 없이도 동작할 수 있지만, 이런 방식으로 실행되는 search는 매우 느립니다.
minimum 및 maximum word length full-text parameter는 내장 FULLTEXT 파서 및 MeCab 파서 플러그인을 사용하여 생성된 FULLTEXT 인덱스에 적용됩니다.
innodb_ft_min_token_size 및
innodb_ft_max_token_size는 InnoDB search 인덱스에 사용됩니다.
ft_min_word_len 및
ft_max_word_len은 MyISAM search 인덱스에 사용됩니다.
minimum 및 maximum word length full-text parameter는 ngram 파서를 사용하여 생성된 FULLTEXT 인덱스에는 적용되지 않습니다. ngram 토큰 크기는 ngram_token_size option으로 정의됩니다.
stopword 리스트가 적용되며, InnoDB search 인덱스의 경우
innodb_ft_enable_stopword,
innodb_ft_server_stopword_table,
innodb_ft_user_stopword_table에 의해 제어되고,
MyISAM의 경우 ft_stopword_file에 의해 제어됩니다.
InnoDB 전체 텍스트 검색은 '++apple' 예시와 같이 단일 search word에 대해 여러 operator를 사용하는 것을 지원하지 않습니다. 단일 search word에 대해 여러 operator를 사용하면 표준 출력으로 syntax error가 반환됩니다. MyISAM 전체 텍스트 검색은 동일한 search를 성공적으로 처리하며, search word 바로 옆에 있는 operator를 제외한 모든 operator는 무시합니다.
InnoDB 전체 텍스트 검색은 선행 plus 또는 minus sign만 지원합니다. 예를 들어, InnoDB는 '+apple'은 지원하지만 'apple+'는 지원하지 않습니다. trailing plus 또는 minus sign을 지정하면 InnoDB는 syntax error를 보고합니다.
InnoDB 전체 텍스트 검색은 wildcard('+*'), plus와 minus sign 조합('+-'), 또는 plus와 minus sign 조합이 선행된('+-apple') invalid query에서 선행 plus sign을 사용하는 것을 지원하지 않습니다. 이러한 invalid query는 syntax error를 반환합니다.
InnoDB 전체 텍스트 검색은 boolean full-text search에서 @ symbol 사용을 지원하지 않습니다. @ symbol은 @distance proximity search operator에서 사용하기 위해 예약되어 있습니다.
MyISAM search 인덱스에 적용되는 50% threshold를 사용하지 않습니다.
boolean full-text search 기능은 다음 operator를 지원합니다:
+선행 또는 후행 plus sign은 이 단어가 반환되는 각 row에 반드시 포함되어야 함을 나타냅니다. InnoDB는 선행 plus sign만 지원합니다.
-선행 또는 후행 minus sign은 이 단어가 반환되는 어떤 row에도 포함되면 안 됨을 나타냅니다. InnoDB는 선행 minus sign만 지원합니다.
Note: - operator는 다른 search term에 의해 이미 match된 row만 제외하는 역할을 합니다. 따라서, boolean-mode search에 -로 시작하는 term만 포함되어 있으면 결과는 빈 집합을 반환합니다. “제외된 term을 포함하는 row를 제외한 모든 row”를 반환하지 않습니다.
기본적으로 (+나 -가 지정되지 않은 경우) 해당 단어는 optional이지만, 이 단어를 포함하는 row는 더 높은 점수를 받습니다. 이는 IN BOOLEAN MODE modifier 없이 MATCH() AGAINST()의 동작을 모방한 것입니다.
@distance이 operator는 InnoDB 테이블에서만 동작합니다. 이 operator는 두 개 이상의 단어가 서로 지정된 거리 이내(단어 수 기준)에서 모두 시작하는지 검사합니다. search word는 @distance operator 바로 앞의 double-quoted string 안에 지정합니다. 예:
MATCH(col1) AGAINST('"word1 word2 word3" @8' IN BOOLEAN MODE)
> <이 두 operator는 단어가 row에 부여되는 relevance 값에 기여하는 정도를 변경하는 데 사용됩니다. > operator는 기여도를 증가시키고 < operator는 감소시킵니다. 이 목록 뒤의 예시를 참조하십시오.
( )괄호는 단어를 subexpression으로 그룹화합니다. 괄호로 묶인 group은 중첩될 수 있습니다.
~선행 tilde는 negation operator로 동작하여, 해당 단어의 row relevance에 대한 기여도를 음수로 만듭니다. 이는 “noise” 단어를 표시하는 데 유용합니다. 이러한 단어를 포함하는 row는 다른 row보다 낮은 점수를 받지만, - operator의 경우와는 달리 완전히 제외되지는 않습니다.
*asterisk는 truncation(wildcard) operator 역할을 합니다. 다른 operator와 달리, 영향을 받는 단어 뒤에 append 됩니다. 단어는 * operator 앞에 있는 단어로 시작하면 match됩니다.
truncation operator가 지정된 단어는 너무 짧거나 stopword이더라도 boolean query에서 제거되지 않습니다. 단어가 너무 짧은지 여부는 InnoDB 테이블의 경우 innodb_ft_min_token_size, MyISAM 테이블의 경우 ft_min_word_len 설정에 따라 결정됩니다. 이 option들은 ngram 파서를 사용하는 FULLTEXT 인덱스에는 적용되지 않습니다.
wildcard가 적용된 단어는 하나 이상의 단어 시작 부분에 반드시 존재해야 하는 prefix로 간주됩니다. minimum word length가 4인 경우, '+word +the*' search는 '+word +the' search보다 더 적은 row를 반환할 수 있습니다. 이는 두 번째 query가 너무 짧은 search term인 the를 무시하기 때문입니다.
"double quote(") 문자로 둘러싸인 phrase는 해당 phrase를 입력된 그대로 literal하게 포함하는 row만 match합니다. 전체 텍스트 엔진은 phrase를 단어로 분리하고 FULLTEXT 인덱스에서 해당 단어를 search합니다. nonword 문자들은 정확하게 match될 필요가 없습니다. phrase searching은 match가 phrase와 정확히 동일한 단어를 동일한 순서로 포함하기만 하면 됩니다. 예를 들어, "test phrase"는 "test, phrase"에 match됩니다.
phrase에 인덱스에 존재하지 않는 단어만 포함되어 있으면 결과는 비어 있습니다. 해당 단어가 텍스트에 존재하지 않거나 stopword이거나, 인덱스된 단어의 최소 길이보다 짧은 경우 등이 그 이유가 될 수 있습니다.
다음 예시는 boolean full-text operator를 사용하는 몇 가지 search string을 보여줍니다:
'apple banana'두 단어 중 하나 이상을 포함하는 row를 찾습니다.
'+apple +juice'두 단어 모두를 포함하는 row를 찾습니다.
'+apple macintosh'“apple”이라는 단어를 포함하는 row를 찾되, “macintosh”도 포함하는 row를 더 높은 순위로 랭크합니다.
'+apple -macintosh'“apple”이라는 단어는 포함하지만 “macintosh”는 포함하지 않는 row를 찾습니다.
'+apple ~macintosh'“apple”이라는 단어를 포함하는 row를 찾되, row가 “macintosh”라는 단어도 포함하는 경우, 포함하지 않는 row보다 낮은 순위로 평가합니다. 이는 '+apple -macintosh' search보다 “덜 엄격한(softer)” search입니다. 후자의 경우에는 “macintosh”가 존재하면 해당 row는 아예 반환되지 않습니다.
'+apple +(>turnover <strudel)'“apple”과 “turnover” 또는 “apple”과 “strudel”을 (순서에 상관없이) 포함하는 row를 찾되, “apple turnover”를 “apple strudel”보다 더 높은 순위로 랭크합니다.
'apple*'“apple”, “apples”, “applesauce”, “applet”과 같은 단어를 포함하는 row를 찾습니다.
'"some words"'“some words”라는 exact phrase를 포함하는 row를 찾습니다. (예: “some
words of wisdom”은 포함하지만 “some noise
words”는 포함하지 않음). phrase를 둘러싸고 있는 " 문자는 phrase를 구분하는 operator 문자이며, search string 자체를 둘러싸는 따옴표와는 다릅니다.
InnoDB 전체 텍스트 검색은 Sphinx 전체 텍스트 검색 엔진을 모델로 하며, 알고리즘은 BM25 및
TF-IDF 랭킹 알고리즘을 기반으로 합니다. 이러한 이유로, InnoDB boolean 전체 텍스트 검색의 relevancy ranking은 MyISAM relevancy ranking과 다를 수 있습니다.
InnoDB는 주어진 전체 텍스트 검색 query에 대해 문서의 relevance를 평가하기 위해 “term frequency-inverse document frequency”(TF-IDF) 가중치 시스템의 변형을 사용합니다. TF-IDF 가중치는 단어가 특정 문서에 얼마나 자주 등장하는지, 그리고 그 단어가 전체 문서 컬렉션에서 얼마나 자주 등장하는지를 기반으로 합니다. 다시 말해, 어떤 단어가 한 문서에서 자주 나타날수록, 그리고 전체 문서 컬렉션에서는 덜 나타날수록 해당 문서의 rank는 더 높아집니다.
term frequency(TF) 값은 단어가 문서에 등장한 횟수입니다. inverse document frequency(IDF) 값은 다음 formula에 따라 계산됩니다. 여기서 total_records는 컬렉션 내 레코드 수이고, matching_records는 search term이 등장하는 레코드 수입니다.
1${IDF} = log10( ${total_records} / ${matching_records} )
문서에 단어가 여러 번 등장하는 경우, IDF 값은 TF 값과 곱해집니다:
1${TF} * ${IDF}
TF 및 IDF 값을 사용하여, 문서에 대한 relevancy ranking은 다음 formula로 계산됩니다:
1${rank} = ${TF} * ${IDF} * ${IDF}
이 formula는 다음 예시에서 설명됩니다.
이 예시는 single-word search에 대한 relevancy ranking 계산을 보여줍니다.
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 (1.04 sec) 8 9mysql> INSERT INTO articles (title,body) VALUES 10 -> ('MySQL Tutorial','This database tutorial ...'), 11 -> ("How To Use MySQL",'After you went through a ...'), 12 -> ('Optimizing Your Database','In this database tutorial ...'), 13 -> ('MySQL vs. YourSQL','When comparing databases ...'), 14 -> ('MySQL Security','When configured properly, MySQL ...'), 15 -> ('Database, Database, Database','database database database'), 16 -> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), 17 -> ('MySQL Full-Text Indexes', 'MySQL fulltext indexes use a ..'); 18Query OK, 8 rows affected (0.06 sec) 19Records: 8 Duplicates: 0 Warnings: 0 20 21mysql> SELECT id, title, body, 22 -> MATCH (title,body) AGAINST ('database' IN BOOLEAN MODE) AS score 23 -> FROM articles ORDER BY score DESC; 24+----+------------------------------+-------------------------------------+---------------------+ 25| id | title | body | score | 26+----+------------------------------+-------------------------------------+---------------------+ 27| 6 | Database, Database, Database | database database database | 1.0886961221694946 | 28| 3 | Optimizing Your Database | In this database tutorial ... | 0.36289870738983154 | 29| 1 | MySQL Tutorial | This database tutorial ... | 0.18144935369491577 | 30| 2 | How To Use MySQL | After you went through a ... | 0 | 31| 4 | MySQL vs. YourSQL | When comparing databases ... | 0 | 32| 5 | MySQL Security | When configured properly, MySQL ... | 0 | 33| 7 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... | 0 | 34| 8 | MySQL Full-Text Indexes | MySQL fulltext indexes use a .. | 0 | 35+----+------------------------------+-------------------------------------+---------------------+ 368 rows in set (0.00 sec)
총 8개의 레코드 중 “database” search term과 match되는 레코드는 3개입니다. 첫 번째 레코드(id 6)는 search term을 6번 포함하며, relevancy ranking은 1.0886961221694946입니다. 이 ranking 값은 TF 값 6(“database” search term이 레코드 id 6에 6번 등장)과 IDF 값 0.42596873216370745를 사용하여 계산됩니다. IDF 값은 다음과 같이 계산됩니다(8은 전체 레코드 수, 3은 search term이 등장하는 레코드 수):
1${IDF} = LOG10( 8 / 3 ) = 0.42596873216370745
그런 다음 TF 및 IDF 값이 ranking formula에 적용됩니다:
1${rank} = ${TF} * ${IDF} * ${IDF}
MySQL 커맨드라인 클라이언트에서 계산을 수행하면 ranking 값 1.088696164686938이 반환됩니다.
1mysql> SELECT 6*LOG10(8/3)*LOG10(8/3); 2+-------------------------+ 3| 6*LOG10(8/3)*LOG10(8/3) | 4+-------------------------+ 5| 1.088696164686938 | 6+-------------------------+ 71 row in set (0.00 sec)
참고
SELECT ... MATCH ... AGAINST statement가 반환하는 ranking 값과 MySQL 커맨드라인 클라이언트가 반환하는 ranking 값(1.0886961221694946 vs
1.088696164686938) 사이에 약간의 차이가 있을 수 있습니다. 이 차이는 InnoDB 내부에서 integer와 float/double 간의 cast가 수행되는 방식(관련 precision 및 rounding 결정 포함)과, MySQL 커맨드라인 클라이언트나 기타 계산 도구에서 cast가 수행되는 방식 차이 때문에 발생합니다.
이 예시는 이전 예제의 articles 테이블과 data를 기반으로 multiple-word 전체 텍스트 검색에 대한 relevancy ranking 계산을 보여줍니다.
두 개 이상의 단어를 search하는 경우, relevancy ranking 값은 다음 formula에서 볼 수 있듯이 각 단어에 대한 relevancy ranking 값의 합입니다:
1${rank} = ${TF} * ${IDF} * ${IDF} + ${TF} * ${IDF} * ${IDF}
두 term('mysql tutorial')에 대한 search를 수행하면 다음과 같은 결과를 반환합니다:
1mysql> SELECT id, title, body, MATCH (title,body) 2 -> AGAINST ('mysql tutorial' IN BOOLEAN MODE) AS score 3 -> FROM articles ORDER BY score DESC; 4+----+------------------------------+-------------------------------------+----------------------+ 5| id | title | body | score | 6+----+------------------------------+-------------------------------------+----------------------+ 7| 1 | MySQL Tutorial | This database tutorial ... | 0.7405621409416199 | 8| 3 | Optimizing Your Database | In this database tutorial ... | 0.3624762296676636 | 9| 5 | MySQL Security | When configured properly, MySQL ... | 0.031219376251101494 | 10| 8 | MySQL Full-Text Indexes | MySQL fulltext indexes use a .. | 0.031219376251101494 | 11| 2 | How To Use MySQL | After you went through a ... | 0.015609688125550747 | 12| 4 | MySQL vs. YourSQL | When comparing databases ... | 0.015609688125550747 | 13| 7 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... | 0.015609688125550747 | 14| 6 | Database, Database, Database | database database database | 0 | 15+----+------------------------------+-------------------------------------+----------------------+ 168 rows in set (0.00 sec)
첫 번째 레코드(id 8)에서는 'mysql'이 한 번 등장하고 'tutorial'이 두 번 등장합니다. 'mysql'에 match되는 레코드는 6개이고, 'tutorial'에 match되는 레코드는 2개입니다. 이 값을 multiple word search에 대한 ranking formula에 대입하면 MySQL 커맨드라인 클라이언트는 기대한 ranking 값을 반환합니다:
1mysql> SELECT (1*log10(8/6)*log10(8/6)) + (2*log10(8/2)*log10(8/2)); 2+-------------------------------------------------------+ 3| (1*log10(8/6)*log10(8/6)) + (2*log10(8/2)*log10(8/2)) | 4+-------------------------------------------------------+ 5| 0.7405621541938003 | 6+-------------------------------------------------------+ 71 row in set (0.00 sec)
참고
SELECT ... MATCH ... AGAINST statement가 반환하는 ranking 값과 MySQL 커맨드라인 클라이언트가 반환하는 ranking 값 사이의 약간의 차이는 앞선 예시에서 설명한 이유와 동일합니다.
14.9.1 Natural Language Full-Text Searches
14.9.3 Full-Text Searches with Query Expansion