这篇博文将引导您了解使用PostgreSQL实现足够好的 全文搜索所需的基本部分。

剧透警告：对于那些寻找“好的，只需向我展示一个全文搜索，在 Postgres 中不到 20 行的排名和模糊搜索”的好奇的人，所以你去：

SELECT 
    courses.id,
    courses.title,
    courses.description,
    rank_title,
    rank_description,
    similarity
FROM 
    courses, 
    to_tsvector(courses.title || courses.description) document,
    to_tsquery('sales') query,
    NULLIF(ts_rank(to_tsvector(courses.title), query), 0) rank_title,
    NULLIF(ts_rank(to_tsvector(courses.description), query), 0) rank_description,
    SIMILARITY('sales', courses.title || courses.description) similarity
WHERE query @@ document OR similarity > 0
ORDER BY rank_title, rank_description, similarity DESC NULLS LAST

但是，如果您需要了解上述 SQL 语句到底在做什么，让我向您解释一下 PostgreSQL 中的上下文和 FTS（全文搜索）基础知识。

背景很重要

很多年前，我读过这篇很棒的博文，名为“Postgres 全文搜索足够好”。真的很值得一读，我可以得到很多见解，因为我已经使用 PostgreSQL 作为我的标准数据库。

到那时，我已经习惯了使用 ElasticSearch 进行文本搜索（如果我们在此之前回到 2009 年，我就有使用 ElasticSearch 所基于的 Apache Lucene 的经验）。

但是，管理 ElasticSearch 部署并不容易。这需要很大的耐心和记忆力🍪。

然后回到 2014 年，我写了这篇文章，解释了我决定尝试 PG 文本搜索的原因，并展示了一个 Ruby 应用程序中的实际示例。

在本指南中，我将重点介绍一个仅使用 SQL 的更简单但功能强大的示例，因此如果您想跟随我一起冒险，请确保您已安装 PostgreSQL。

这是唯一的要求。不再需要安装或设置工具。仅 Postgres。

播种数据

为了进一步解释文本搜索、相关性和结果排名的基本原理，我们必须在我们的数据库中植入真实数据并比较不同的搜索策略。

让我们创建一个名为courses仅包含 atitle和description列的表。这些列将是我们的“可搜索”列，我们将在其中执行文本搜索：

CREATE TABLE courses
(id SERIAL PRIMARY KEY, 
title VARCHAR(80) NOT NULL, 
description VARCHAR(200) NOT NULL);

接下来，我们将使用一些虚拟数据填充表：

INSERT INTO courses (title, description) VALUES
  ('Improve your sales skills', 'A complete course that will help you to improve your sales skills'),
  ('Intro to Computer Science', 'Understant how computers work'),
  ('Law 101', 'Have you ever wondered doing some Law?'),
  ('Natural Sciences the easy way', 'Your guide to understand the world'),
  ('Mathematics: a gentle introduction', 'Numbers are easy'),
  ('The crash course of Data Science', 'Be a data scientist in 5 weeks'),
  ('Sales crash course', 'Yet another course on Sales'),
  ('Java in a nutshell', 'Learn Java in 21 days'),
  ('Ruby programming language', 'DDH sales Ruby, but could you buy it?'),
  ('Sales matter', 'Really?'),
  ('History in 3 pages', 'Can you learn history in 3 pages?'),
  ('Mastering Git', 'Git history will no longer bother you'),
  ('Cooking like a boss', 'Be the next master chef'),
  ('Master Chef 3.0', 'Cooking revisited'),
  ('Functional Programming in a nutshell', 'Learn FP in 4 days');

检查数据是否正确创建：

SELECT * FROM courses;

凉爽的。现在，在进行“全文搜索”之前，让我们执行一个在许多 SQL 系统中使用的简单文本搜索：模式匹配

使用 LIKE 和 ILIKE 进行文本搜索

使用 LIKE 进行文本搜索非常简单：

SELECT 
    courses.id,
    courses.title,
    courses.description
FROM 
    courses
WHERE  
    courses.title LIKE '%java%' OR courses.description LIKE '%java%'

但它没有返回任何结果，因为它LIKE是区分大小写的，这意味着我们必须指定保存在表中的大写字母：

...
courses.title LIKE '%Java%' OR courses.description LIKE '%Java%'

8    "Java in a nutshell"    "Learn Java in 21 days"

今天我们很幸运，那么让我们使用ILIKE不区分大小写的 ，所以不需要大写，因为它会在大写和非大写字母上执行模式匹配：

...
courses.title ILIKE '%java%' OR courses.description ILIKE '%java%'

8    "Java in a nutshell"    "Learn Java in 21 days"

关于 LIKE/ILIKE 的注意事项

许多系统使用模式匹配功能来实现非常简单的文本搜索。对于许多场景来说这已经足够了，但是平台在要求苛刻的用户中增长得越多，搜索就越需要返回更好的结果，并具有更准确的相关性和排名。

根据Postgres 官方文档，模式匹配LIKE | ILIKE缺少现代系统所需的基本属性：

它们不提供搜索结果的排序（排名），这使得它们在找到数千个匹配文档时无效。

它们往往很慢，因为没有索引支持，因此它们必须为每次搜索处理所有文档。

没有语言支持，即使是英语。正则表达式是不够的，因为它们不能轻易处理派生词......

举一个更实际的例子，由于我们赋予标题更多的相关性而不是描述，让我们在行动中看看ILIKE缺乏这样的要求：

SELECT *
FROM courses
WHERE courses.title ILIKE '%sales%' OR courses.description ILIKE '%sales%'

不好。

我们希望课程 10（其标题中包含单词“Sales”）出现在课程 9 之前，课程 9 的描述中包含该单词。

此外，我们的订购标准是什么？如何按“分数”排序，这样我们就可以建立我们的结果排名？

全文搜索救援。

PostgreSQL 中的全文搜索

全文搜索(FTS) 允许对文档进行预处理并保存索引以供以后快速搜索和排名。请参阅官方文档，该文档非常完整，提供了理解和实施 FTS 所需的所有信息。

PG（Postgres）中 FTS 的主要构建块是：

  • tsvector，它代表一个可搜索的文档
  • tsquery，这是针对文档执行的搜索查询

向量

该to_tsvector函数解析输入文本并将其转换为表示可搜索文档的搜索类型。例如：

SELECT to_tsvector('Java in a nutshell')

...将给出以下内容：

"'java':1 'nutshel':4"
  • 结果是准备被搜索的词位列表
  • 停用词（“in”、“a”、“the”等）被删除
  • 数字是文档中词位的位置：java:1从第 1 个位置开始，而nutshell:4从第 4 个位置开始

查询

该to_tsquery函数解析输入文本并将其转换为表示查询的搜索类型。例如，用户想要搜索“java in a nutshell”：

SELECT to_tsquery('java & in & a & nutshell');

...将给出以下内容：

"'java' & 'nutshel'"
  • 结果是准备好被查询的令牌列表
  • 停用词（“in”、“a”、“the”等）被删除

那么，如何将查询与文档匹配？

运营商

该@@ 运算符允许将查询与文档匹配并返回 true 或 false。就那么简单。

/* true */
SELECT to_tsquery('java & in & a & nutshell') @@ to_tsvector('Java in a nutshell'); 

/* true */
SELECT to_tsquery('java') @@ to_tsvector('Java in a nutshell'); 

/* true */
SELECT to_tsquery('nutshell') @@ to_tsvector('Java in a nutshell'); 

/* false */
SELECT to_tsquery('batatas') @@ to_tsvector('Java in a nutshell');

耶！就目前而言，我们有在我们的课程表上实施 FTS 的基本要求。

搜索课程

让我们执行基本的全文搜索，查找标题中包含“java”的课程：

SELECT * 
FROM courses
WHERE to_tsquery('java') @@ to_tsvector(courses.title)

8    "Java in a nutshell"    "Learn Java in 21 days"

伟大的。让我们也针对标题和描述执行搜索“销售”：

SELECT * 
FROM courses
WHERE to_tsquery('sales') @@ to_tsvector(courses.title || courses.description)

或者

SELECT * 
FROM 
    courses, 
    to_tsvector(courses.title || courses.description) document
WHERE to_tsquery('sales') @@ document

此时，结果与我们的ILIKE版本相似。让我们看看 FTS 真正的亮点在哪里。

ts_rank

该ts_rank函数将文档和查询作为参数尝试衡量文档与特定查询的相关程度。

SELECT
    ts_rank(
        to_tsvector('Java in a nutshell'),
        to_tsquery('java')
    )

"0.06079271"

检查多个变体：

/* 0.06079271 */
SELECT ts_rank(to_tsvector('Java in a nutshell'), to_tsquery('nutshell')) 

/* 0 */
SELECT ts_rank(to_tsvector('Java in a nutshell'), to_tsquery('batatas'))

一个更复杂的模拟潜在标题和描述的等级：

SELECT
    ts_rank(
        to_tsvector('Java in a nutshell'),
        to_tsquery('java')
    ) AS rank_title,
    ts_rank(
        to_tsvector('Learn in 21 days'),
        to_tsquery('java')
    ) AS rank_description

伟大的！现在，我们拥有了实现更好的文本搜索所需的一切，并对我们的课程进行适当的排名。

搜索具有排名的课程

排名结果意味着我们必须将文档拆分为不同的排名，以便我们可以相应地执行排序。

我们基本上需要导出排名字段：

SELECT
    ...
    ts_rank(to_tsvector(courses.title), query) as rank_title,
    ts_rank(to_tsvector(courses.description), query) as rank_description
...

...并对包含标题和描述的整个文档执行查询：

FROM 
    ...
    to_tsvector(courses.title || courses.description) document,
    to_tsquery('sales') query
WHERE query @@ document
...

然后我们准备执行正确的排序：

...
ORDER BY rank_description, rank_title DESC

排名的实现

所以这里我们用12 行 SQL 代码在 PostgreSQL 中实现了全文搜索和排名：

SELECT 
    courses.id,
    courses.title,
    courses.description,
    ts_rank(to_tsvector(courses.title), query) as rank_title,
    ts_rank(to_tsvector(courses.description), query) as rank_description
FROM 
    courses, 
    to_tsvector(courses.title || courses.description) document,
    to_tsquery('sales') query
WHERE query @@ document
ORDER BY rank_description, rank_title DESC

快吗？

根据数据量，这样的查询可能会面临性能问题，因为它需要动态地将数据转换为可搜索的文档。

救援指数

创建适当的索引（文本搜索的 GIN 索引），可以将性能提高几个数量级。

在这个项目中，我做了一个概念验证，可以在几毫秒内搜索 1200 万个城市。仅 GIN 索引。不需要物化视图。

让我们看看如何为我们的课程表创建 GIN 索引：

CREATE INDEX courses_search_idx ON courses USING GIN (to_tsvector(courses.title || courses.description));

我们很好。

只需坐下来，放松并享受搜索数百万门课程的乐趣，而不会出现性能问题。

模糊搜索呢？

模糊搜索或“字符串近似匹配”是用于计算两个字符串的近似值的技术。它通常用于预测查询中的拼写错误等。

遗憾的是，Postgres 内置的 FTS 不支持模糊搜索，但是，通过使用扩展，我们可以将全文搜索和模糊搜索结合在同一个 SQL 查询中。

让我们创建扩展：

CREATE EXTENSION pg_trgm

现在我们可以看到动作上的差异：

SELECT 
    to_tsquery('jova') @@ to_tsvector('Java in a nutshell') AS search,
    SIMILARITY('jova', 'Java in a nutshell') as similarity

请注意，针对文本“Java in a nutshell”搜索“jova”，全文搜索匹配运算符返回 false，而扩展SIMILARITY提供的函数pg_trgm返回 value 0.09。

在从 0 到 1 的范围内，相似的字符串往往接近 1。

所以这里我们用16 行 SQL 代码在 PostgreSQL 中实现了一个带有排名和模糊搜索的全文搜索：

SELECT 
    courses.id,
    courses.title,
    courses.description,
    rank_title,
    rank_description,
    similarity
FROM 
    courses, 
    to_tsvector(courses.title || courses.description) document,
    to_tsquery('curse') query,
    NULLIF(ts_rank(to_tsvector(courses.title), query), 0) rank_title,
    NULLIF(ts_rank(to_tsvector(courses.description), query), 0) rank_description,
    SIMILARITY('curse', courses.title || courses.description) similarity
WHERE query @@ document OR similarity > 0
ORDER BY rank_title, rank_description, similarity DESC NULLS LAST

强调：

  • 我们对标题和描述应用相似度函数
  • 当搜索没有匹配时，我们过滤相似度大于0的结果
  • 添加了NULLIF函数，因此当rank_*为 0 时，我们将值转换为，NULL以便排序可以将 NULL 值视为排名结果中的最后一个

结论

本指南非常繁重，但仅涵盖了 Postgres 中全文搜索的基础知识。在官方文档中，您可以看到更多的特性和功能，例如高亮文档、权重、查询树、查询重写、字典、触发器等。

它可靠且快速，这意味着它可以用于广泛的需求，从简单的搜索系统到复杂的搜索系统。如果您的堆栈中已经有 PostgreSQL，则值得考虑在使用外部/昂贵的替代方案之前对其进行试验，这需要更多地关注操作复杂性。

我希望你能享受在 PostgreSQL 中实现 FTS 的过程。搜索愉快！

A powerful full-text search in PostgreSQL in less than 20 lines

# elasticsearch # postgresql # 数据库
Logo
华为云开发者联盟

为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。

更多推荐

cover

小窗口大魔力，实况窗服务实时掌控重要信息变化

avatar 华为云开发者联盟
cover

GeminiDB全面联动MySQL：热点数据，一键加速

avatar 华为云开发者联盟
cover

GaussDB数据库查询重写的自动挖掘与生成

avatar 华为云开发者联盟