MySQL5.7官方文档翻译：全文索引，自然语言搜索模式

全文索引之自然语言搜索模式

MATCH (col1, col2, ...) AGAINST (expr \[search_modifier\])

search_modifier:
{
	IN NATURAL LANGUAGE MODE
	| IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
	| IN BOOLEAN MODE
	| WITH QUERY EXPANSION
}

MySQL支持全文索引和全文索引搜索：

  • MySQL中的全文索引就是FULLTEXT类型的索引。
  • 全文索引只能用在InnoDB与MyISAM搜索引擎上，并且只能创建在TEXT,CHAR,VARCHAR列上。
  • 在MySQL 5.7.6,MySQL提供了一个内建的全文索引ngram解析器用来支持中文，日文，韩文。以及一个应用于日文的可安装的MeCab全文索引解析插件。
  • 全文索引可以在CREATE TABLE声明创建表时候定义。或者在之后用ALTER TABLE或CREATE INDEX添加。
  • 对于大的数据量，导入数据到没有全文索引的表上，然后再创建索引要比导入数据到已经有全文索引的表快得多。

全文索引所有使用MATCH() … AGAINST语法执行。MATCH()接受一个逗号分隔的被查询的列名。AGAINST接受一个查询字符串，和一个可选的用来标识搜索类型的修饰符。查询字符串必须是一个在查询过程中的常量。例如，不符合规则的是一个表列，因为每一条数据都不一样。

下面是三种搜索类型：

  • 自然语言搜索解释搜索字符串作为人类语言中的短语（一段文本中的短语），没有特别的操作符，非用词表适用。 如果指定了IN NATURAL LANGUAGE MODE修饰符或者没有修饰符，全文搜索使用自然语言搜索。

  • 布尔搜索使用特殊的查询语言规则解释查询字符串。字符串包含搜索的字符，它也包含特殊需求的操作符，例如，一个单词必须在匹配行中出现或者不出现，或者这个词有更高或者更低的权重。某些常用词被搜索索引忽略，并且它出现在搜索字符串中也不匹配。IN BOOLEAN MODE指定布尔搜索。

  • 查询扩展搜索是自然语言搜索的改版，查询字符串用来执行自然语言搜索，然后将最匹配的行的词添加到查询字符串再执行查询。这条查询返回第二次搜索的结果。IN NATURAL LANGURE MODE WITH QUERY EXTENSION或WITH QUERY EXTENSION修饰符指定查询扩展搜索。

myisam_ftdump实用程序转储MYISAM全文索引的内容，这对调试全文索引可能有帮助。

自然语言全文搜索

默认的或者使用IN NATURAL LANGURE MODE修饰符，MATCH()函数对一个文本集合执行自然语言搜索，这个文本集合是一个或多个全文索引中的列。查询字符串作为AGAINST()的参数给出。对于表中的每一行，MATCH()返回一个相关性的值，也就是说，搜索字符串和MATCH()列表里面的列的文本的相似度。

mysql> CREATE TABLE articles (
	id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
	title VARCHAR(200),
	body TEXT,
	FULLTEXT (title,body)
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO articles (title,body) VALUES
	('MySQL Tutorial','DBMS stands for DataBase ...'),
	('How To Use MySQL Well','After you went through a ...'),
	('Optimizing 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.01 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM articles
	WHERE MATCH (title,body)
	AGAINST ('database' IN NATURAL LANGUAGE MODE);

+----+-------------------+------------------------------------------+
| id | title | body |
+----+-------------------+------------------------------------------+
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

默认的，搜索使用大小写不敏感的模式执行。为了区分大小写，可以为被索引的列使用二进制校对规则。例如，某一列使用latin1字符集可以指定latin1_bin校对规则来让全文搜索区分大小写。

当MATCH()用在WHERE子句，就像前面的例子，被返回的行自动按照最高的相似度排序。相似度的值是一个非负的浮点数。0代表不相似。匹配度基于单词在行(document)中的数量，行中的唯一单词数，集合中的总单词数，包含特定词的行数计算。

小贴士：‘document’与‘term’术语可以相互替代，两个术语指的都是被索引的行。‘collection’指的是被索引的列和所有的行。

简单的计数，可以使用下面的查询：

mysql> SELECT COUNT(*) FROM articles
	WHERE MATCH (title,body)
	AGAINST ('database' IN NATURAL LANGUAGE MODE);

+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)

你可能发现改写成这样快一些：

mysql> SELECT
	COUNT(IF(MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL))
	AS count
	FROM articles;

+-------+
| count |
+-------+
| 2 |
+-------+
1 row in set (0.03 sec)

第一个查询做了一些而外的工作（根据相似度排序），但是他也能在WHERE子句种使用索引查找。如果查询匹配很少的行，那么第一个查询使用索引查找更快。第二个查询执行全表扫描，如果这个搜索词出现在大多数的行，那么要比使用索引查找要快。

对于自然语言全文搜索，MATCH()函数中声明的列必须与表中的全文索引包含的列相同。对于前面的查询，可以注意到MATCH()函数里声明的列（title和body）与定义article表全文索引的列相同。如果想单独搜索title和body,你需要为每个列分别创建全文索引。

全文搜索使用仅在单表上MATCH()子句中列上的索引，因为索引不能跨越多张表。对于MyIAM表，布尔搜索可以在没有索引（尽管很慢）的情况下执行，在这种情况下可以声明多个表中的列。前面的例子展示了怎样使用MATCH()函数返回相似度递减的行。后面的例子展示了如何显示的检索相似度的值。返回的行不被排序，因为SELECT声明即不包含WHERE也不包含ORDER BY子句：

mysql> SELECT id, MATCH (title,body)
	AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) AS score
	FROM articles;

+----+---------------------+
| id | score |
+----+---------------------+
| 1 | 0.22764469683170319 |
| 2 | 0 |
| 3 | 0.22764469683170319 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
+----+---------------------+
6 rows in set (0.00 sec)

MySQL全文索引的实现视单词序列（字母，数字，下划线）为一个单词，这个序列可能包含单引号，但是一个记录里面不会超过一个。这意味着aaa’bbb被视为一个词，但是aaa'‘bbb被视为两个词。单引号在单词的开头或结尾会被全文解析器删除，‘aaa’bbb'被解析为aaa’bbb。（这块有点疑问，自行测试了一下，无论是aaa’bbb, aaa'‘bbb, ‘aaa’bbb’, ‘aaa'‘bbb'均被解析为两个单词，即aaa,bbb)。

内置的全文索引解析器通过查找确定的分隔符来决定一个词的开始和结束；例如空格，英文逗号，英文句号。如果单词不能被分隔符分隔（举例来说：中文），那么内置的全文索引解析器不能决定单词的开始和结束。为了使用内置的全文索引解析器添加这些语言的单词或者其他被索引的条目，你必须预处理它们以便于它们能被任意分隔符分隔比如”。另外，在MySQL5.7.6，你能创建全文索引使用ngram解析器插件（支持中文，日文，韩文）或者MeCab解析器插件（支持日文）。

解析器插件源码在MySQL源代码目录下的plugin/fulltext。

一些词在全文搜索中被忽略：

  • 过短的词被忽略。对于InnoDB全文索引，被搜索的单词的最小长度是3个字符，MyISAM是4个。在创建索引前，你能通过配置选项控制这个长度：InnoDB使用innodb_ft_min_token_size，MyISAM使用ft_min_word_len。注：这个行为不会应用于ngram解析器的全文索引，对于ngram解析器，单词的长度通过ngram_token_size配置。

  • 非用词列表中的单词被忽略。非用词是类似于“the”，“some”这样的常见的词，它们被认为没有语义价值。系统内有内建的非用词列表，但是它可以被用户定义的覆盖。InnoDB和MyISAM的非用词列表和相关的配置选项是不同的，对于InnoDB，非用词的处理被innodb_ft_enable_stopword, innodb_ft_server_stopword_table, innodb_ft_user_stopword_table控制; 对于MyISAM是ft_stopword_file。

在集合和查询中的每个词通过它的意义被加权，因此，一个在很多文件中出现的词具有低的权重，因为它在特定的集合中具有低的语义价值。相反的，如果一个词很罕见，那么它有很高的权重。词的权重被合并用来计算行的相似度，这种技术对于大的数据集合工作良好。

MyISAM限制

对于非常小的表，词的分布并不能充分反映其语义价值，并且，这种模型在MyISAM表的搜索可能会产生奇怪的结果。例如，‘MySQL’这个词在之前展示的articles表的每一行都有，MyISAM搜索索引对于这个词的搜索不会产生结果：

mysql> SELECT * FROM articles
	WHERE MATCH (title,body)
	AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);

Empty set (0.00 sec)

这个结果是空的，因为‘MySQL’出现在了至少50%的行内，所以其被视为非用词。这种过滤技术更适合大的数据集合，你可能不想从1G的表里每两行中就返回一条数据比从非常小的表里针对流行词没有返回。

50%的阈值在你第一次尝试全文搜索如何工作的时候可能会让你吃惊，这使得InnoDB表更适合全文搜索实验。如果你创建了一个MyISAM表，仅插入了一两行，那么每个词出现在至少50%的行，因此，任何搜索都不会返回结果直到有更多的行。用户可以通过创建InnoDB表或者布尔搜索模式绕过50%限制。