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。它返回truefalse,这使其易于作为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》

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏java一日一条

你不得不知道的 MySQL 优化原理(一)

说起MySQL的查询优化,相信大家收藏了一堆奇淫技巧:不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型….. 你是否真的理解...

1632
来自专栏更流畅、简洁的软件开发方式

利用虚拟硬盘(把内存当作硬盘)来提高数据库的效率(目前只针对SQL Server 2000)可以提高很多

      虚拟硬盘:就是把内存当作硬盘来用,比如有2G的内存,那么可以拿出来1G的内存当作硬盘来用。       自从知道了“虚拟硬盘”这个东东,我就一直在想...

5055
来自专栏文渊之博

如何将生产环境的字段类型从INT修改为BIGINT

介绍 改变数据类型是一个看起来很简单的事情,但是如果表非常大或者有最小停机时间的要求,又该如何处理那?这里我提供一个思路来解决这个问题。 背景 在一个常规SQL...

4298
来自专栏乐沙弥的世界

Oracle ADDM性能诊断利器及报告解读

性能优化是一个永恒的话题,性能优化也是最具有价值,最值得花费精力深入研究的一个课题,因为资源是有限的,时间是有限的。在Oracle数据库中,随着Oracle功能...

1582
来自专栏沃趣科技

其他混杂视图 | 全方位认识 sys 系统库

在《语句效率统计视图|全方位认识 sys 系统库》中,为大家介绍了利用sys 系统库查询语句执行效率的快捷视图,本期将为大家介绍一些不便归类的混杂视图,本篇也是...

1412
来自专栏Hadoop数据仓库

HAWQ技术解析(十二) —— 查询优化

        即便对SELECT等数据库查询语句已经很熟悉了,但HAWQ里的查询有其自己的特点,还是需要研究一下。 一、HAWQ的查询处理流程        ...

6726
来自专栏北京马哥教育

Python操作SQLite/MySQL/LMDB/LevelDB

1. 概述 1.1 前言 最近用Caffe跑自己的数据集,需要学习LMDB和LevelDB,趁此机会复习了SQLite和MySQL的使用,一起整理在此。 代码...

5448
来自专栏杨建荣的学习笔记

解析实时的DB time过程分析(r6笔记第35天)

在我们查看awr报告的时候总是会有一个关键指标需要注意,那就是DB time,这个指标一般都是通过awr报告来看到的。 比如我们得到的awr报告头部显示的下面的...

3657
来自专栏杨建荣的学习笔记

使用序列的问题ORA-02287(r5笔记第19天)

今天一个开发的同事问我一个问题,说在执行一条sql语句的时候报了ORA错误,脑海中删除了各种权限的问题之后,他提供给我的错误还是在我预料之外。 ERROR at...

3556
来自专栏数据和云

Oracle 12.2新特性掌上手册 - 第七卷 Big Data and Data Warehousing

编辑手记:也许Oracle 12.2在内核上的智能改进只能让你眼前一亮,那今天基于Big Data和数据仓库的性能优化增强则会让你伸手触Oracle的强大灵魂。...

3097

扫码关注云+社区

领取腾讯云代金券