如何在Ubuntu16.04上使用MySQL5.6中的全文搜索改进数据库搜索

介绍

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

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

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

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

先决条件

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

按照此 Initial Server Setup with Ubuntu 16.04 指南设置一台 Ubuntu 16.04 服务器，包括 sudo 非 root 用户和防火墙。
按照如何在 Ubuntu 16.04 上安装 MySQL 指南安装 MySQL 5.6 或更高版本。

第 1 步 - 创建测试数据

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

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

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

mysql -u root -p

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

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

CREATE DATABASE testdb;

切换到默认使用 testdb 数据库，这样您就不必指定数据库的名称来创建或更新其中的内容。

USE testdb;

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

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

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

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

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

INSERT INTO news (id, title, content, author) VALUES 
   (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'),
   (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'),
   (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 搜索这些数据。

第 2 步 — 创建 FTS 索引并使用 FTS 函数

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

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

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

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

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

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 默认采用自然语言模式，因此您不必显式指定它。

注意：与自然语言模式相比，词干提取是另一种有用的FTS技术，它使索引去除词的词缀，只存储词根部分。例如，“fits”和“fitted”这两个词在使用 FTS 和词干提取时是相同的。

不幸的是，MySQL 不支持词干。 Stemming 在 MySQL 的工作日志中，但还没有实施和发布它的时间框架。 FTS 仍然很有用，因为它仍然比 LIKE 子句快得多。如果您想使用词干提取，可以研究与 Snowball 库的集成。

上面查询末尾的 \G 使输出中的每一列都打印在新行上。这可以使长结果更容易阅读。上述命令的输出将如下所示：

Output*************************** 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”这个词，第二行只包含这个词“海滩”。您可以通过更改关键字来尝试其他搜索以查看结果。

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

第 3 步——优化 FTS 结果

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

使用相关性分数

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

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

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')。

结果将如下所示：

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

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

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

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

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

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;

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

输出将如下所示：

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

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

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

使用 IN 布尔值

在步骤 2 中，您在指定查询词时使用了默认模式 IN NATURAL LANGUAGE。还有另一种模式，IN BOOLEAN，它允许您从搜索中排除特定单词，定义输入中的单词必须彼此相距多远的范围，等等。

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

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

结果将仅显示第 2 行：

Output*************************** 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 指定搜索词之间的最大距离。这个距离是用词来衡量的，重要的是，包括搜索词。例如，短语“cats and dogs”的距离为 3。

以下命令返回的结果中出现“traveling”和“miles”这两个词，它们之间的词数不超过 2 个。

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

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

Output*************************** 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 的 FTS 能力，可以阅读【X74X】MySQL 5.6 官方文档全文搜索【X130X】。