不到 20 行代码即可在 PostgreSQL 中实现强大的全文搜索

2025-06-09

不到 20 行代码即可在 PostgreSQL 中实现强大的全文搜索

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

剧透警告：对于那些好奇的人来说，“好吧，只需在不到 20 行的时间内向我展示一个在 Postgres 中进行排名和模糊搜索的全文搜索”，所以你看：

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
Enter fullscreen mode Exit fullscreen mode

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

背景很重要

几年前，我读过一篇很棒的博文，叫做《Postgres 全文搜索已经足够好了》。它真的值得一读，我从中获益良多，因为我当时已经使用 PostgreSQL 作为我的标准数据库了。

到那时，我已经可以轻松地使用 ElasticSearch 进行文本搜索（如果我们追溯到 2009 年之前，我就有使用 Apache Lucene 的经验，而 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);
Enter fullscreen mode Exit fullscreen mode

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

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');
Enter fullscreen mode Exit fullscreen mode

检查数据是否正确创建：

SELECT * FROM courses;
Enter fullscreen mode Exit fullscreen mode

截图 2021-09-16 15.32.50.png

太棒了。现在，在进行“全文搜索”之前，让我们先进行一个简单的文本搜索，就像许多 SQL 系统中使用的一样：模式匹配

使用 LIKE 和 ILIKE 进行文本搜索

使用 LIKE 进行文本搜索非常简单，如下所示：

SELECT 
    courses.id,
    courses.title,
    courses.description
FROM 
    courses
WHERE  
    courses.title LIKE '%java%' OR courses.description LIKE '%java%'
Enter fullscreen mode Exit fullscreen mode

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

...
courses.title LIKE '%Java%' OR courses.description LIKE '%Java%'
Enter fullscreen mode Exit fullscreen mode 
8   "Java in a nutshell"    "Learn Java in 21 days"
Enter fullscreen mode Exit fullscreen mode

今天我们很幸运，那么让我们使用不区分大小写的ILIKE，因此无需大写，因为它将对大写字母和非大写字母执行模式匹配：

...
courses.title ILIKE '%java%' OR courses.description ILIKE '%java%'
Enter fullscreen mode Exit fullscreen mode 
8   "Java in a nutshell"    "Learn Java in 21 days"
Enter fullscreen mode Exit fullscreen mode

关于 LIKE/ILIKE 的考虑

许多系统使用模式匹配功能来实现非常简单的文本搜索。这对于很多场景来说已经足够了，但随着平台用户需求的增加，搜索功能也需要提供更精准的搜索结果，包括更准确的相关性和排名。

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

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

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

没有任何语言支持，甚至连英语都没有。正则表达式不够用，因为它们无法轻松处理派生词……

举一个更实际的例子，由于我们赋予标题比描述更多的相关性，让我们看看实际中ILIKE缺乏这样的要求的情况：

SELECT *
FROM courses
WHERE courses.title ILIKE '%sales%' OR courses.description ILIKE '%sales%'
Enter fullscreen mode Exit fullscreen mode

截图 2021-09-16 16.38.31.png
不好。

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

此外，我们的排序标准是什么？我们能否按照“分数”排序，以便对结果进行排名？

全文搜索救援。

PostgreSQL 中的全文搜索

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

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

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

ts向量

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

SELECT to_tsvector('Java in a nutshell')
Enter fullscreen mode Exit fullscreen mode

...将给出以下内容：

"'java':1 'nutshel':4"
Enter fullscreen mode Exit fullscreen mode
  • 结果是可供搜索的词素列表
  • 停用词（“in”、“a”、“the”等）已被删除
  • 数字是词素在文档中的位置：java:1从第一个位置开始，而nutshell:4从第四个位置开始

tsquery

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

SELECT to_tsquery('java & in & a & nutshell');
Enter fullscreen mode Exit fullscreen mode

...将给出以下内容：

"'java' & 'nutshel'"
Enter fullscreen mode Exit fullscreen mode
  • 结果是可供查询的令牌列表
  • 停用词（“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');
Enter fullscreen mode Exit fullscreen mode

好极了！目前，我们已经满足了在课程表中实施 FTS 的基本要求。

搜索课程

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

SELECT * 
FROM courses
WHERE to_tsquery('java') @@ to_tsvector(courses.title)
Enter fullscreen mode Exit fullscreen mode 
8   "Java in a nutshell"    "Learn Java in 21 days"
Enter fullscreen mode Exit fullscreen mode

太好了。让我们针对标题和描述执行“sales”搜索：

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

或者

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

截图 2021-09-16 16.38.31.png

目前，结果与我们的ILIKE版本类似。让我们看看 FTS 究竟在哪里脱颖而出。

ts_rank

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

SELECT
    ts_rank(
        to_tsvector('Java in a nutshell'),
        to_tsquery('java')
    )
Enter fullscreen mode Exit fullscreen mode 
"0.06079271"
Enter fullscreen mode Exit fullscreen mode

检查多个变体：

/* 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'))
Enter fullscreen mode Exit fullscreen mode

更复杂的模拟潜在标题和描述的排名：

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
Enter fullscreen mode Exit fullscreen mode

截图 2021-09-16 17.09.11.png

太棒了！现在我们已经拥有了实现更好的文本搜索所需的一切，并且可以对我们的课程进行适当的排名。

搜索有排名的课程

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

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

SELECT
    ...
    ts_rank(to_tsvector(courses.title), query) as rank_title,
    ts_rank(to_tsvector(courses.description), query) as rank_description
...
Enter fullscreen mode Exit fullscreen mode

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

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

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

...
ORDER BY rank_description, rank_title DESC
Enter fullscreen mode Exit fullscreen mode

排名实施

因此，我们仅用 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
Enter fullscreen mode Exit fullscreen mode

截图 2021-09-16 17.20.19.png

快嗎？

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

救援指数

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

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

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

CREATE INDEX courses_search_idx ON courses USING GIN (to_tsvector(courses.title || courses.description));
Enter fullscreen mode Exit fullscreen mode

我们很好。

只需坐下来，放松并享受在数百万门课程中进行搜索，而不会出现任何性能问题。

模糊搜索怎么样？

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

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

让我们创建扩展：

CREATE EXTENSION pg_trgm
Enter fullscreen mode Exit fullscreen mode

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

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

截图 2021-09-16 17.45.23.png

请注意，在文本“Java in a nutshell”中搜索“jova”，全文搜索匹配运算符返回 false，而扩展SIMILARITY提供的函数pg_trgm则返回一个值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
Enter fullscreen mode Exit fullscreen mode

亮点：

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

结论

这篇指南相当长，但只涵盖了 Postgres 全文搜索的基础知识。在官方文档中，你可以看到更多特性和功能，例如高亮文档、权重、查询树、查询重写、字典、触发器等等。

它可靠且快速，这意味着它可以用于从简单到复杂的搜索系统的各种需求。如果您的堆栈中已经安装了 PostgreSQL，那么在选择需要更多关注操作复杂性的外部/昂贵替代方案之前，值得考虑先对其进行实验。

希望您能享受在 PostgreSQL 中实现 FTS 的这段旅程。祝您搜索愉快！

鏂囩珷鏉ユ簮锛�https://dev.to/leandronsp/a-powerful-full-text-search-in-postgresql-in-less-than-20-lines-5c0d
Devto 翻译
PREV
掌握 Docker 卷
NEXT
使用 React 错误边界和后备组件进行用户友好的错误