MySQL5.7官方文档翻译：布尔全文搜索

开始

MySQL能使用IN BOOLEAN MODE修饰符执行布尔全文搜索。使用这个修饰符，查询字符串中的单词的开头和结尾的某些字符有了特殊的意义。在接下来的查询中，‘+’，‘-’操作符表明单词必须出现或必须不出现，因此这个查询返回所有包含‘MySQL’，但是不包含‘YouSQL'的行：

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 Well | After you went through a ... |
| 3 | Optimizing 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 ... |
+----+-----------------------+-------------------------------------+

注：在实现这一特性时，MySQL在使用时被称为隐式的布尔逻辑，+代表AND，-代表NOT，没有操作符视为OR。

布尔全文搜索有这些特点：

  • 布尔搜索不会自动按照相似度降序排序。

  • InnoDB表执行布尔搜索，需要MATCH()表达式里面所有的列为全文索引。对于MyISAM表，布尔查询可以可以没有全文索引，尽管这种方式会非常慢。

  • 最大和最小单词长度应用于使用内置的全文索引解析器和MeCab解析插件创建的全文索引。InnoDB全文索引使用innodb_ft_min_token_size、innodb_ft_max_token_size。MyISAM使用ft_min_word_len、ft_max_word_len。 最大和最小单词长度不会应用于使用ngram解析器创建的全文索引。它通过ngram_token_size配置项定义。

  • 非用词列表适用，InnoDB索引使用innodb_ft_enable_stopword，innodb_ft_server_stopword_table，innodb_ft_user_stopword_table。MyISAM使用ft_stopword_file。

  • InnoDB全文搜索不支持多个操作符在同一个搜索词上，例如：‘++apple’。使用多个操作符在一个搜索词上会返回一个解析错误的标准输出，MyISAM会成功执行同样的搜索，但是除了紧邻搜索词的那个操作符其他的都会被忽略。

  • InnoDB全文搜索仅支持开头的加号或减号。例如：InnoDB支持‘+apple’，但是不支持’apple+’。指定尾部的加号或减号会使InnoDB产生一个解析错误。

  • InnoDB全文搜索不支持使用‘+*’、‘+-’。这些非法的查询会返回解析错误。

  • InnoDB布尔搜索不支持使用@标志，@操作符被保留，用来做@ distance邻近搜索操作符（这块的意思可能是指定一个偏移位置来搜索）。

  • 它没有使用应用于MyISAM的50%阈值。（这个可以参考 #3 ）

布尔搜索支持下面的操作符：

  • + 一个开头或结尾的加号表明这个词必须出现在被返回的每一行中。InnoDB仅支持开头的加号。

  • - 一个开头或结尾的减号表明这个词不能出现在返回的任意一行中。InnoDB仅支持开头的加号。 注：减号操作符仅是排除其他搜索条目匹配的行。因此，布尔搜索返回空的结果如果只有减号操作符（大概意思）。它不会返回所有的行除了被减号标记的词。

  • （没有操作符） 默认情况（即没有加号也没有减号操作符），单词是可选的，如果行中包含这个词会排名靠前。这是模仿没有IN BOOLEAN MODE修饰符的MATCH()……AGAINST()。

  • @ distance 这个操作符仅工作在InnoDB表上，它测试两个或更多的词是否都以一个特定的距离开始，以词为单位。指定这个搜索词在@ distance操作符之前的双引号字符串中，例如：MATCH(col1) AGAINST('“word1 word2 word3” @ 8’ IN BOOLEAN MODE)。

  • > < 这两个操作符被用来增加一个词对整行的贡献值。大于号代表增加，小于号减小。

  • () 圆括号将词放入子表达式，圆括号可以嵌套。

  • ~ 开头的波浪符号代表否定操作符，会使词对行的相似性贡献为负值。这对标记噪音词汇很有用，包含这样词的行比其他的相似度低，但是并不是完全排除，因为他和减号操作符一样。

  • * 星号作为截断（或通配符）操作符。不像其他操作符，他被添加到受影响的单词上（不太懂）。如果单词加星号开始，那么一些单词会被匹配（word*会匹配words）。 如果一个词被指定截断操作符，即使它太短或者是非用词，在布尔搜索的时候也不会被剔除。一个词是否太短取决于InnoDB的innodb_ft_min_token_size和MyISAM的ft_min_word_len。这些配置项不会应用于ngram解析器的全文索引。 通配符被认为是前缀，必须出现在一个或多个词的开始（或结尾）。如果最小长度是4，那么搜索’+word +the*‘回比’+word +the'返回更少的行，因为第二个查询忽略的过短的‘the’。

  • " 包含在双引号里面的短语仅匹配包含这个短语的行（就像键入的一样，逐字匹配）。全文引擎会把短语分隔成单词并且按词执行全文搜索。未被确认的词不能准确的匹配：短语搜索需要准确的匹配每个单词，并且有同样的顺序。例如：“test phrase”能匹配“test, phrase”。 如果短语包含的词不在索引中，那么搜索结果是空。词不被索引有几个影响因素：不在文本中，是非用词，长度小于索引词的最小长度。

下面的例子展示了使用布尔搜索操作符：

  • ‘apple banana’ 寻找至少包含两个词中的一个词。

  • ’+apple +juice; 寻找包含两个词的行。

  • ‘+apple macintosh’ 寻找包含apple的行，如果包含 macintosh排行更高。

  • ’+apple -macintosh’ 寻找包含apple但是不包含macintosh的行。

  • ‘+apple ~macintosh’ 寻找包含apple的行，但是如果行里包含macintosh，那么它有更低的排行比不包含这个词。这种方式要比’+apple -macintosh'柔和，因为后者不会返回包含macintosh的行。

  • ‘+apple +(>trunover <strudel)’ 寻找包含apple和trunover，或apple和strudel的行（任意顺序）。但是前者的排名比后者高。

  • ‘apple*’ 寻找包含apple，apples，applesaure，applet等的行。

  • ’”some words“‘ 寻找包含短语some words的行（例如：包含some words of wisdom，不是some noise words）。注：包含短语的引号是界定短语的操作符，不是包含搜索字符串的引号。

InnoDB布尔搜索的相关性排名

Innodb全文索引是模仿Sphinx全文搜索引擎，算法基于BM25和TF-IDF排序算法。基于这个原因，InnoDB布尔搜索的相似度排名与MyISAM的相似度排名有一些不同。

InnoDB使用可变的“term frequency-inverse document frequency”（TF-IDF）权重系统为给出的全文索引查询做相似度排名。TF-IDF基于一个词在行中出现的频率，并且与这个词在所有的行中出现的次数成反比。换句话说，一个词在一行中出现的次数越多，在其他行中出现的次数越少，行的排名更高。

如何计算相似度排名

‘term frequency’（TF）是一个词在行中出现的次数。‘inverse document frequency’（IDF）使用下面的公式计算，total_records是集合中的行数（也就是数据表的行数），matching_records是搜索出现的结果行数。

${IDF} = log10( ${total_records} / ${matching_records} )

当一行包含一个单词很多次，IDF的值为乘以TF的结果：

${TF} * ${IDF}

使用TF和IDF的值，文档的相似度使用下面的公式计算：

${rank} = ${TF} * ${IDF} * ${IDF}

下面的例子演示了这个公式。

单个词的相似度

下面的例子展示了一个搜索词的相似性计算：

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 (1.04 sec)

mysql> INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','This database tutorial ...'),
("How To Use MySQL",'After you went through a ...'),
('Optimizing Your Database','In this database tutorial ...'),
('MySQL vs. YourSQL','When comparing databases ...'),
('MySQL Security','When configured properly, MySQL ...'),
('Database, Database, Database','database database database'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL Full-Text Indexes', 'MySQL fulltext indexes use a ..');

Query OK, 8 rows affected (0.06 sec)
Records: 8 Duplicates: 0 Warnings: 0

mysql> SELECT id, title, body, MATCH (title,body) AGAINST ('database' IN BOOLEAN MODE)
AS score FROM articles ORDER BY score DESC;

+----+------------------------------+-------------------------------------+---------------------+
| id | title | body | score |
+----+------------------------------+-------------------------------------+---------------------+
| 6 | Database, Database, Database | database database database | 1.0886961221694946 |
| 3 | Optimizing Your Database | In this database tutorial ... | 0.36289870738983154 |
| 1 | MySQL Tutorial | This database tutorial ... | 0.18144935369491577 |
| 2 | How To Use MySQL | After you went through a ... | 0 |
| 4 | MySQL vs. YourSQL | When comparing databases ... | 0 |
| 5 | MySQL Security | When configured properly, MySQL ... | 0 |
| 7 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... | 0 |
| 8 | MySQL Full-Text Indexes | MySQL fulltext indexes use a .. | 0 |
+----+------------------------------+-------------------------------------+---------------------+
8 rows in set (0.00 sec)

总共有8条记录，3条记录匹配到’database’。第一行（id 6）包含‘database’6次，相似度为1.0886961221694946。排名数值通过TF（值为6，‘database'在id为6的行里出现了6次）和IDF计算。IDF的值为0.42596873216370745，它通过下面的公式计算（8为总记录数，3为搜索词出现的行数）：

${IDF} = log10( 8 / 3 ) = 0.42596873216370745

将TF和IDF带入计算公式：

${rank} = ${TF} * ${IDF} * ${IDF}

在MySQL命令行客户端执行公式返回相似度的值1.088696164686938。

mysql> SELECT 6*log10(8/3)*log10(8/3);

+-------------------------+
| 6*log10(8/3)*log10(8/3) |
+-------------------------+
| 1.088696164686938 |
+-------------------------+
1 row in set (0.00 sec)

注：你可能注意到SELECT……MATCH()……AGAINST()和MySQL命令行客户端返回的值有一点不一样（1.0886961221694946和1.088696164686938）。这种不同是由于在Innodb内部整数与浮点的转换（连同相关的精度和取整的决定），以及它在什么位置执行，例如：MySQL命令行客户端或其他类型的计算器。

多个搜索词的相关性

下面的例子展示了全文索引的多个搜索词的相似性计算，基于articles表，以及前面例子使用的数据。

如果搜索词超过一个，相似度的是每个搜索词的相似度的和。就像下面的公式：

${rank} = ${TF} * ${IDF} * ${IDF} + ${TF} * ${IDF} * ${IDF}

执行两个词’mysql tutorial'的搜索，返回下面的结果：

mysql> SELECT id, title, body, MATCH (title,body) AGAINST ('mysql tutorial' IN BOOLEAN MODE)
AS score FROM articles ORDER BY score DESC;

+----+------------------------------+-------------------------------------+----------------------+
| id | title | body | score |
+----+------------------------------+-------------------------------------+----------------------+
| 1 | MySQL Tutorial | This database tutorial ... | 0.7405621409416199 |
| 3 | Optimizing Your Database | In this database tutorial ... | 0.3624762296676636 |
| 5 | MySQL Security | When configured properly, MySQL ... | 0.031219376251101494 |
| 8 | MySQL Full-Text Indexes | MySQL fulltext indexes use a .. | 0.031219376251101494 |
| 2 | How To Use MySQL | After you went through a ... | 0.015609688125550747 |
| 4 | MySQL vs. YourSQL | When comparing databases ... | 0.015609688125550747 |
| 7 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... | 0.015609688125550747 |
| 6 | Database, Database, Database | database database database | 0 |
+----+------------------------------+-------------------------------------+----------------------+
8 rows in set (0.00 sec)

在第一条记录（id 8），‘mysql’出现一次，‘turorial'出现两次。有6条记录匹配了‘mysql’，2条记录匹配了‘tutorial’。当带入相应的数值到多搜索词相似度计算公式，MySQL命令行客户端返回预想的结果：

mysql> SELECT (1*log10(8/6)*log10(8/6)) + (2*log10(8/2)*log10(8/2));

+-------------------------------------------------------+
| (1*log10(8/6)*log10(8/6)) + (2*log10(8/2)*log10(8/2)) |
+-------------------------------------------------------+
| 0.7405621541938003 |
+-------------------------------------------------------+
1 row in set (0.00 sec)