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

介绍

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

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

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

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

准备

在开始本教程之前，您需要以下内容：

• 一个Ubuntu 16.04服务器。
• 安装有在PostgreSQL。具体的安装使用可以参考腾讯云社区安装教程。

如果您在不遵循上述教程的情况下设置PostgreSQL服务器，请确保postgresql-contrib程序包使用sudo apt-get list postgresql-contrib，也可以直接使用云数据库，腾讯云提供云数据库 PostgreSQL（TencentDB for PostgreSQL）能够让您在云端轻松设置、操作和扩展目前功能最强大的开源数据库 PostgreSQL。

第一步 - 创建示例数据

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

除此之外，第一步是从其服务器连接到PostgreSQL数据库。由于您是从同一主机连接，因此默认情况下，您无需输入密码。

$ sudo -u postgres psql sammy

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

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

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

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

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

sammy=# INSERT INTO news (id, title, content, author) VALUES 
sammy=#     (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'),
sammy=#    (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'),
sammy=#    (3, 'Machine Learning from scratch', 'Bare bones implementations of some of the foundational models and algorithms.', 'Jo');

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

第二步 - 准备和搜索文档

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

注意：在本教程中，psql输出使用expanded display格式设置，在新行上显示输出中的每一列，从而更容易在屏幕上显示长文本。你可以像这样启用它：

sammydb=# \x

Expanded display is on.

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

SELECT title || '. ' || content as document, to_tsvector(title || '. ' || content) as metadata FROM news 

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

-[ 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'

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

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

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

让我们来看看我们在这里使用的函数和运算符。

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

该@@运营商标识，如果tsvector匹配的tsquery或其他tsvector。它返回true或false，这使其易于作为WHERE标准的一部分使用。

-[ 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的性能。

第三步 - 提高FTS性能

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

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

sammy=# ALTER TABLE news ADD "document" tsvector;

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

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

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

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

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

将这些行添加到我们的表是一个很好的性能改进，但在大型数据集中，我们可能仍然有问题，因为数据库仍然必须扫描整个表以查找符合搜索条件的行。一个简单的解决方案是使用索引。

database index是一种数据结构，它与主数据分开存储数据，从而增强了数据检索操作的性能。它在表内容发生任何更改后以额外写入和相对较少的存储空间为代价进行更新。它的小尺寸和定制的数据结构允许索引比使用主表空间选择查询更有效地运行。

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

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

sammy=# CREATE INDEX idx_fts_search ON news USING gin(document);

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

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

输出结果将如下所示：

-[ 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更多的教程，可以访问腾讯云官网查看PostgreSQL的用户手册和PostgreSQLAPI文档。

参考文献：《How to Use Full-Text Search in PostgreSQL on Ubuntu 16.04》