如何在Ubuntu 16.04上的PostgreSQL中使用全文搜索

介绍

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

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

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

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

先决条件

在开始本指南之前，您将需要以下信息：

• 一个Ubuntu 16.04服务器通过遵循Ubuntu 16.04的“初始服务器安装”指南设置 ，包括一个sudo非root用户。
• PostgreSQL 安装遵循如何在Ubuntu 16.04上安装和使用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

您可能会注意到转换版本中的单词数量少于原始document中的输出值。 一些词是不同的，每个单词都有一个分号和一个数字。 这是因为函数to_tsvector()规范化每个单词以允许我们找到相同单词的变体形式，然后按字母顺序对结果进行排序。 该数字是单词在document的位置。 如果标准化字出现不止一次，可能会有其他的逗号分隔位置。

现在我们可以通过搜索“探索”一词来使用转换后的文档来利用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”的文档，即使我们用于搜索的单词是“Exploration”。 在这里使用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全文检索文档 。