如何在Ubuntu 16.04上使用MySQL全文搜索提高搜索效果

介绍

全文搜索（FTS）是搜索引擎用于在数据库中查找结果的技术。您可以使用它来为商店、搜索引擎、报纸等网站上的搜索结果提供支持。

更具体地说，FTS检索与搜索条件不完全匹配的文档。文档是包含文本数据的数据库实体。这意味着当用户搜索“猫和狗”时，例如，由FTS支持的应用程序能够返回单独包含单词的结果（只是“猫”或“狗”），包含不同顺序的单词（“狗和猫”），或包含单词的变体（“猫”或“狗”）。这为应用程序提供了猜测用户意味着什么并更快地返回更相关结果的优势。

从技术上讲，像MySQL这样的数据库管理系统（DBMS）通常允许使用LIKE子句进行部分文本查找。但是，这些请求往往在大型数据集上表现不佳。它们也仅限于精确匹配用户的输入，这意味着即使存在包含相关信息的文档，查询也可能不会产生任何结果。

使用FTS，您可以构建更强大的文本搜索引擎，而无需在更高级的工具上引入额外的依赖关系。在本教程中，您将使用MySQL 5.6使用全文搜索来查询数据库，然后根据它们与搜索输入的相关性来量化结果，并仅显示最佳匹配。

准备

在开始本教程之前，您需要：

• 一个Ubuntu 16.04服务器，包括具有sudo权限的非root用户和防火墙。
• 安装 MySQL 5.6或更高版本。

第一步 - 创建测试数据

为了尝试全文搜索，我们需要一些数据。在这一步中，我们将创建一个带有news表格的名为testdb的数据库，我们将使用一些代表来自虚构新闻聚合器站点的文章的示例数据填充该数据库。

注意：如果您有自己的表格，其中包含您想要使用的文本数据，则可以跳到第二步并在跟随时进行适当的替换。

首先，访问MySQL控制台。系统将提示您输入安装MySQL时设置的root密码。

$ mysql -u root -p

连接后，您的提示将更改为mysql>。

接下来，创建一个名为testdb的新数据库。该数据库将包含测试数据。

mysql> CREATE DATABASE testdb;

默认情况下切换到使用数据库testdb，因此您不必指定数据库的名称来创建或更新其中的内容。

mysql> USE testdb;

接下来，在数据库中创建一个表news，其中包含列，用于示例新闻聚合器的文章。

mysql> CREATE TABLE news (
mysql>   id INT NOT NULL AUTO_INCREMENT,
mysql>   title TEXT NOT NULL,
mysql>   content TEXT NOT NULL,
mysql>   author TEXT NOT NULL,
mysql>
mysql>   PRIMARY KEY (id)
);

让我们来看看这个命令的作用：

• CREATE TABLE 是一个创建表的SQL命令，类似于许多其他数据库。
• news 是表的名称。
• title、content和author是具有无限长度的文本列。
• NOT NULL是一个声明，用于标记不能具有空值的列（尽管它们可能包含空字符串）。
• id表是具有特殊类型的表的主索引AUTO_INCREMENT，它使用下一个可用ID自动填充ID字段。

现在将一些示例数据添加到表中。

mysql>   INSERT INTO news (id, title, content, author) VALUES 
mysql>       (1, 'Pacific Northwest high-speed rail line', 'Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.', 'Greg'),
mysql>       (2, 'Hitting the beach was voted the best part of life in the region', 'Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.', 'Ethan'),
mysql>       (3, 'Machine Learning from scratch', 'Bare bones implementations of some of the foundational models and algorithms.', 'Jo');

让我们来看看这个命令的作用：

• INSERT 插入数据。
• INTO 指定数据的插入位置。本文中是news表格。
• (id, title, content, author) VALUES 指定应存储每个条目的数据值的列。
• 最后三行是我们添加到表中的三行数据。每个都包含一个新闻网站的示例文章，其中包含一个title，一些content和author的名称。

每个条目还有一个唯一的id，它自动输入到数据库索引中。该数据库索引是一种数据结构，提高了数据检索操作的性能。该索引与主数据分开存储。它会以额外写入和相对较少的存储空间为代价更新表内容的任何更改。它的小尺寸和定制的数据结构允许索引比使用主表空间选择查询更有效地运行。

现在我们有了一些数据，我们可以开始编写查询来使用FTS搜索该数据。

第二步 - 创建FTS索引并使用FTS函数

让我们为我们的文本列创建索引，以便我们可以使用FTS。

为此，我们将使用一个名为MySQL的独占命令FULLTEXT。此命令告诉MySQL将我们希望能够使用FTS搜索的所有字段放入内部索引。

mysql> ALTER TABLE news ADD FULLTEXT (title, content, author);

这通过组合所有文本列并清理它们来工作（例如，删除标点符号并将大写字母设置为小写）。现在已创建此索引，它将由更改源表内容的任何SQL查询更新。

接下来，尝试使用MATCH() ，AGAINST()功能对“Seattle beach”进行全文搜索。

mysql> SELECT * FROM news WHERE MATCH (title,content,author) AGAINST ('Seattle beach' IN NATURAL LANGUAGE MODE)\G

MATCH()命令的一部分指定使用FTS索引哪组列;它必须与您用于创建索引的列列表匹配。AGAINST()部分指定我们正在执行全文搜索的单词，在此示例中为“Seattle beach”。

IN NATURAL LANGUAGE MODE表示搜索词是直接从用户输入提供的，无需任何预处理。MySQL默认采用自然语言模式，因此您不必明确指定它。

在上述查询的的末端有一个\G，可以使输出的每一列都会输出新的行。这可以使结果更容易阅读。上述命令的输出如下所示：

*************************** 1. row ***************************
     id: 1
  title: Pacific Northwest high-speed rail line
content: Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.
 author: Greg
*************************** 2. row ***************************
     id: 2
  title: Hitting the beach was voted the best part of life in the region
content: Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.
 author: Ethan
2 rows in set (0.00 sec)

没有条目包含短语“Seattle beach”，但由于我们使用全文搜索，我们仍然得到两个结果：第一行，只包含单词“Seattle”，第二行，只包含单词“ beach”。您可以通过更改关键字来查看结果来尝试其他搜索。

现在您可以在SQL查询中使用FTS函数来查找与搜索输入相关的行，您可以使这些结果更具相关性。

第三步 - 完善FTS结果

有两种技术可以帮助使全文搜索结果更具相关性。一种是通过结果的相关性分数进行过滤，另一种是使用IN BOOLEAN从结果中排除特定单词并指定搜索项之间的最大距离。

使用相关性分数

结果的相关性得分量化了搜索项的匹配程度，其中0表示根本不相关。相关性得分基于许多因素，包括在特定文档中找到该术语的频率以及包含该术语的文档的数量。MySQL的全文搜索文档深入计算这个数字。

根据“traveling to parks”的查询获取每行的相关性分数。

mysql> SELECT id, MATCH (title,content,author) AGAINST ('traveling to parks') as score FROM news;

此命令的as score部分将输出中的第二列标记为score。否则，它将用用于填充它的命令标记，在这种情况下是MATCH (title,content,author) AGAINST ('traveling to parks')。

结果类似这样：

+----+----------------------+
| id | score                |
+----+----------------------+
|  1 | 0.031008131802082062 |
|  2 |  0.25865283608436584 |
|  2 |  0                    |
+----+----------------------+
3 rows in set (0.00 sec)

第三行的相关性得分为0，因为其中没有搜索词。第一行包含单词“travel”，但不包含“to”或“parks”，并且相关性得分非常低0.03。第二行包含所有单词，其相关性得分最高0.25。

您可以使用这些分数首先返回最相关的结果，或仅返回高于特定相关范围的结果。相关性得分因数据集而异，因此选择截止点需要手动调整。

以下命令运行相同的查询，但添加了两件事：

• 它通过添加仅显示具有非零相关性分数的行WHERE MATCH (title,content,author) AGAINST ('traveling to parks') > 0
• 它通过添加ORDER BY score DESC根据相关性对结果进行排序

mysql> SELECT id, MATCH (title,content,author) AGAINST ('traveling to parks') as score FROM news WHERE MATCH (title,content,author) AGAINST ('traveling to parks') > 0 ORDER BY score DESC;

您需要MATCH() AGAINST()在WHERE子句中重复该函数，因为SQL限制了该子句中可包含的内容。

输出将如下所示：

+----+----------------------+
| id | score                |
+----+----------------------+
|  2 |  0.25865283608436584 |
|  1 | 0.031008131802082062 |
+----+----------------------+
2 rows in set (0.01 sec)

首先显示最相关的结果第2行，然后显示不太相关的第1行。根本不显示第3行，因为其相关性得分为0。

您可以更改截止值以继续微调结果。例如，如果您使用0.1 而不是0截止，则仅返回第2行。

使用IN BOOLEAN

在第二步中，您在指定查询字词时使用了默认的IN NATURAL LANGUAGE模式。还有另一种模式，IN BOOLEAN它允许您从搜索中排除特定单词，定义输入中单词相隔多远的范围，以及更多。

要从查询中省略术语，请使用减号运算符IN BOOLEAN。以下命令将返回包含单词“travel”但不包含单词“Seattle”的结果。

mysql> SELECT * FROM news WHERE MATCH (title,content,author) AGAINST ('traveling -Seattle' IN BOOLEAN MODE)\G

结果只显示第2行：

*************************** 1. row ***************************
     id: 2
  title: Hitting the beach was voted the best part of life in the region
content: Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.
 author: Ethan
1 row in set (0.01 sec)

这是有效的，因为减号运算符告诉DMS标记具有相关性分数为0的排除词的任何文档。在此模式下仅显示具有非零相关性分数的结果。

您还可以使用IN BOOLEAN MODE指定搜索项之间的最大距离。该距离用单词测量，重要的是包括搜索项。例如，短语“猫与狗”的距离为3。

以下命令返回结果，其中出现“travel”和“miles”字样，它们之间的字数不超过2个。

mysql> SELECT * FROM news WHERE MATCH (title,content,author) AGAINST ('"traveling miles" @4' IN BOOLEAN MODE)\G

你会看到一个结果，它在第2行的content中与traveling the 140 miles匹配。

*************************** 1. row ***************************
     id: 1
  title: Pacific Northwest high-speed rail line
content: Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.
 author: Greg
1 row in set (0.00 sec)

如果在原始命令中将其更改@4为@3，则不会看到任何结果。

在搜索具有不同词汇表的非常大的文档时，通过搜索词之间的距离限制搜索结果会很有帮助。查询术语之间的差距越小，结果就越准确，尽管微调距离将取决于您正在使用的文档集。例如，一组科学论文可以很好地使用3的小字间隙，但搜索论坛帖子可能会有8或更高的差距，这取决于您希望结果的宽度或范围。

结论

在本指南中，您使用了MySQL中的全文搜索功能。您在为文档驱动的数据库构建数据库模式时创建了索引，然后在查询时使用特殊运算符查找最相关的结果。您也可以直接使用MySQL云数据库减少配置环节。

参考文献：《How To Improve Database Searches with Full-Text Search in MySQL 5.6 on Ubuntu 16.04》