MySQL全文索引自然语言模式详解

在MySQL数据库中，全文索引提供了一种高效检索数据库中文本数据的方式。特别是在处理大量数据时，普通的LIKE查询可能会非常缓慢而且不够精确。MySQL支持两种全文索引搜索模式：自然语言模式（Natural Language Mode）和布尔模式（Boolean Mode）。本文将重点介绍自然语言模式的使用和原理。

自然语言全文搜索的基本用法

在默认情况下或使用IN NATURAL LANGUAGE MODE修饰符时，MATCH()函数对一个文本集合执行自然语言搜索。这里的“集合”是指包含在FULLTEXT索引中的一个或多个列。搜索字符串作为AGAINST()函数的参数给出。对于表中的每一行，MATCH()都会返回一个相关度值，即搜索字符串与MATCH()列表中命名的列中的文本之间的相似度测量值。

例如，创建一个具有全文索引的文章表：

CREATE TABLE articles (
  id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT (title,body)
) ENGINE=InnoDB;

向表中插入一些数据：

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 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 ...');

然后可以执行一个简单的全文搜索：

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

自然语言搜索的特性

• 默认情况下，自然语言搜索是不区分大小写的。要进行区分大小写的全文搜索，请使用索引列的区分大小写的排序规则。
• 当MATCH()用在WHERE子句中时，返回的行默认按相关度降序排列。但是，必须满足以下3个条件：
  1. 查询不能包含ORDER BY子句。
  2. 必须通过全文索引扫描而非表扫描来进行搜索。
  3. 如果查询连接了多个表，全文索引扫描必须是联接中最左边的非常量表。
• 相关度是一个非负浮点数。零相关度意味着没有相似性。
• 相关度是根据行（文档）中的单词数量、该行中唯一单词的数量、集合中总单词数量以及包含特定单词的文档（行）的数量计算得出的。

如何计算匹配的数量

您可以使用如下SQL语句轻松计算匹配的数量：

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

或者使用更快的查询重写形式：

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

第一个查询做了一些额外的工作（按相关度排序结果），这可以在只计算行数时跳过。

全文索引的限制和注意事项

• 自然语言全文搜索的列列表必须与表的某个FULLTEXT索引定义中的列列表相同。
• 使用索引进行全文搜索不能使用来自多个表的列，因为索引不能跨表。

获取相关度值和排序

如果需要显示地检索相关度值并对其进行排序，可以这样操作：

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

如果想要同时返回相关度分数，并按相关度降序排序，则MATCH()需要在SELECT列表中指定一次，在WHERE子句中再指定一次。MySQL优化器会注意到两个MATCH()调用是相同的，因此只会调用一次全文搜索代码。

SELECT id, body, MATCH (title,body)
AGAINST ('Security implications of running MySQL as root'
IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH (title,body) 
AGAINST('Security implications of running MySQL as root'
IN NATURAL LANGUAGE MODE);

短语和词性搜索

• 使用双引号括起的短语被视为一个整体。全文引擎将短语拆分成单词，并在FULLTEXT索引中搜索这些单词。
• MySQL的FULLTEXT实现将任何真正的单词字符序列（字母、数字和下划线）视为一个单词。该序列还可以包含撇号(')，但不能连续出现两个。
• 内置的FULLTEXT解析器通过寻找某些分隔符字符来确定单词的开始和结束；例如空格（ ）、逗号（,）和句点（.）。如果单词之间没有分隔符，则内置的FULLTEXT解析器无法确定单词的开始和结束。