如何在Ubuntu16.04上的PostgreSQL中使用全文搜索

介绍

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

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

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

使用 FTS，您可以构建更强大的文本搜索引擎，而无需引入对更高级工具的额外依赖。 在本教程中，我们将使用 PostgreSQL 存储包含假设新闻网站文章的数据，然后学习如何使用 FTS 查询数据库并仅选择最佳匹配项。 作为最后一步，我们将对全文搜索查询进行一些性能改进。

先决条件

在开始本指南之前，您需要以下内容：

• 按照此 Initial Server Setup with Ubuntu 16.04 指南设置一台 Ubuntu 16.04 服务器，包括 sudo 非 root 用户。
• 按照 如何在 Ubuntu 16.04 上安装和使用 PostgreSQL 指南安装 PostgreSQL。 在本教程中，我们将使用该指南中的 sammy 数据库和用户设置。

如果您没有按照上述教程设置 PostgreSQL 服务器，请确保您有使用 sudo apt-get list postgresql-contrib 的 postgresql-contrib 包。

第 1 步 - 创建示例数据

首先，我们需要一些数据来测试全文搜索插件，所以让我们创建一些示例数据。 如果您已经有自己的带有文本值的表格，则可以跳到第 2 步并在继续进行时进行适当的替换。

否则，第一步是从其服务器连接到 PostgreSQL 数据库。 因为您是从同一主机连接的，所以默认情况下，您不需要输入密码。

sudo -u postgres psql sammy

这将建立一个交互式 PostgreSQL 会话，指示您正在操作的数据库名称，在我们的例子中是 sammy。 您应该会看到 sammy=# 数据库命令提示符。

接下来，在数据库中创建一个名为 news 的示例表。 此表中的每个条目将代表一篇带有标题、一些内容、作者姓名以及唯一标识符的新闻文章。

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

id 是表的主索引，具有特殊类型 SERIAL，它为表创建一个自增计数器。 这是一个自动进入数据库索引的唯一标识符。 当我们查看性能改进时，我们将在第 3 步中更多地讨论这个索引。

接下来，使用 INSERT 命令将一些示例数据添加到表中。 下面命令中的示例数据代表一些示例新闻文章。

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 checking out local parks.', 'Ethan'),
 (3, 'Machine Learning from scratch', 'Bare bones implementations of some of the foundational models and algorithms.', 'Jo');

现在数据库有一些数据要搜索，我们可以尝试编写一些查询。

第 2 步 — 准备和搜索文档

这里的第一步是从数据库表中构建一个包含多个文本列的文档。 然后，我们可以将结果字符串转换为单词向量，这就是我们将在查询中使用的内容。

注意： 在本指南中，psql 输出使用 expanded display 格式，该格式将输出中的每一列显示在新行上，从而更容易在屏幕上适合长文本。 您可以像这样启用它：

\x

OutputExpanded display is on.

首先，我们需要使用 PostgreSQL 连接函数 || 和转换函数 to_tsvector() 将所有列放在一起。

SELECT title || '. ' || content as document, to_tsvector(title || '. ' || content) as metadata FROM news WHERE id = 1;

这将返回作为整个文档的第一条记录，以及用于搜索的转换版本。

Output-[ RECORD 1 ]-----------------------------------------------------
document    | 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.

metadata    | '140':18 'current':8 'high':4 'high-spe':3 'ideal':29 'line':7 'mile':19 'none':25 'northwest':2 'option':14 'pacif':1 'rail':6 'seattl':21 'speed':5 'travel':16 'vancouv':23

您可能会注意到，转换后的版本中的字数更少，上面输出中的 metadata 比原始的 document 中的单词少。 有些单词是不同的，每个单词都有一个分号和一个数字。 这是因为函数 to_tsvector() 对每个单词进行规范化，以便我们找到同一个单词的变体形式，然后按字母顺序对结果进行排序。 数字是单词在 document 中的位置。 如果规范化的单词出现不止一次，则可能会有额外的逗号分隔位置。

现在我们可以通过搜索术语“Explorations”来使用这个转换后的文档来利用 FTS 功能。

SELECT * FROM news WHERE to_tsvector(title || '. ' || content) @@ to_tsquery('Explorations');

让我们检查一下我们在这里使用的函数和运算符。

函数 to_tsquery() 将参数（可以是直接的或稍微调整的用户搜索）转换为文本搜索条件，该条件将以与 to_tsvector() 相同的方式减少输入。 此外，该函数允许您指定要使用的语言以及是所有单词都必须出现在结果中还是只出现其中一个。

@@ 运算符识别 tsvector 是否匹配 tsquery 或另一个 tsvector。 它返回 true 或 false，使其易于用作 WHERE 标准的一部分。

Output-[ RECORD 1 ]-----------------------------------------------------
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 checking out local parks.
author  | Ethan

该查询返回包含“Exploring”一词的文档，即使我们用于搜索的词是“Explorations”。 在这里使用 LIKE 运算符而不是 FTS 会产生空结果。

现在我们知道了如何为 FTS 准备文档以及如何构建查询，让我们看看提高 FTS 性能的方法。

第 3 步 — 提高 FTS 性能

在使用大型数据集或小型服务器时，每次使用 FTS 查询时生成文档可能会成为性能问题。 我们将在这里实现的一个很好的解决方案是在插入行时生成转换后的文档并将其与其他数据一起存储。 这样，我们可以通过查询来检索它，而不必每次都生成它。

首先，在现有的 news 表中创建一个名为 document 的额外列。

ALTER TABLE news ADD "document" tsvector;

我们现在需要使用不同的查询将数据插入表中。 与第 2 步不同，这里我们还需要准备转换后的文档并将其添加到新的 document 列中，如下所示：

INSERT INTO news (id, title, content, author, document)
VALUES (4, 'Sleep deprivation curing depression', 'Clinicians have long known that there is a strong link between sleep, sunlight and mood.', 'Patel', to_tsvector('Sleep deprivation curing depression' || '. ' || 'Clinicians have long known that there is a strong link between sleep, sunlight and mood.'));

向现有表添加新列需要我们首先为 document 列添加空值。 现在我们需要用生成的值更新它。

使用 UPDATE 命令添加缺失的数据。

UPDATE news SET document = to_tsvector(title || '. ' || content) WHERE document IS NULL;

将这些行添加到我们的表中是一个很好的性能改进，但是在大型数据集中，我们可能仍然会遇到问题，因为数据库仍然需要扫描整个表才能找到匹配搜索条件的行。 一个简单的解决方案是使用索引。

数据库索引 是一种将数据与主要数据分开存储的数据结构，可增强数据检索操作的性能。 它会在表内容发生任何更改后更新，但代价是额外的写入和相对较少的存储空间。 它的小尺寸和量身定制的数据结构使得索引比使用主表空间来选择查询更有效地运行。

最终，索引通过使用特殊的数据结构和算法进行搜索，帮助数据库更快地找到行。 PostgreSQL 有 几种类型的索引 适合特定类型的查询。 与此用例最相关的是 GiST 索引和 GIN 索引。 它们之间的主要区别在于它们从表中检索文档的速度。 添加新数据时 GIN 构建速度较慢，但查询速度较快； GIST 构建速度更快，但需要额外的数据读取。

因为 GiST 检索数据比 GIN 慢大约 3 倍，我们将在这里创建一个 GIN 索引。

CREATE INDEX idx_fts_search ON news USING gin(document);

使用索引的 document 列，我们的 SELECT 查询也变得更加简单。

SELECT title, content FROM news WHERE document @@ to_tsquery('Travel | Cure');

输出将如下所示：

Output-[ RECORD 1 ]-----------------------------------------------------
title   | Sleep deprivation curing depression
content | Clinicians have long known that there is a strong link between sleep, sunlight and mood.
-[ RECORD 2 ]-----------------------------------------------------
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.

完成后，您可以使用 \q 退出数据库控制台。

结论

本指南介绍了如何在 PostgreSQL 中使用全文搜索，包括准备和存储元数据文档以及使用索引来提高性能。 如果您想了解有关 PostgreSQL 中 FTS 的更多信息，请查看 关于全文搜索的 PostgreSQL 官方文档 。