PostgreSQL全文搜索详解 —— 后端开发者指南（第一部分）

来了解PostgreSQL的全文搜索吧！了解它是如何工作的，为什么仅靠B-树索引是不够的，以及通过实际例子来探索TSVECTOR、TSQUERY和GIN索引。准备好在第二部分学习更多高级技巧吧！

我经常使用Elasticsearch，但有时我们需要一个快速简便的解决方案。使用主数据库并具备全文搜索功能，有很多好处。理解PostgreSQL可能无法像Elasticsearch那样做所有事情也是合理的，但在某些情况下，它可能满足需求（这完全取决于业务需求）。

PostgreSQL 全文检索

本文将要讲述的内容：

• 什么是全文搜索
• 我们能否使用B-树索引进行全文搜索？（如果你已经掌握了B-树索引及其在全文搜索中的局限性，请跳过此部分）
• 我们为什么需要全文搜索（简述）
• TSVECTOR
• TSQUERY
• 玩转TSVECTOR和TSQUERY
• 给电影表添加一个TSVECTOR字段
• GIN索引（GIN Index）
• 突出显示结果
• 摘要
• 第二部分的阅读内容

什么是全文检索？

我们已经知道了数据库中的正常或精确匹配。比如，查询名为 John 的用户：

例如，查询名为 John 的用户。

SELECT * FROM movies where first_name='John'

以下是从movies表中选择名字为'John'的所有记录的SQL查询。

不过我们希望通过输入这些短语来找到相同的记录。

john ， JOHN ， jOHN ， Joh ， ohn ，包括拼写错误如 Jon 。这些都是些基本要求，我们还希望搜索引擎能有更多高级功能（比如排序和找同义词）等，我们之后会详细讨论这些功能。

只要不是拼错字，我们一般都能得到想要的结果。

我们可以用B-树索引来做的全文检索吗？

如果你对这个问题的答案不感兴趣，可以直接跳到下一节。我写了这部分来展示没有全文搜索功能时我们遇到的问题。

查找用户的 first_name（即用户的名）确实很简单，但是如果我们真的想看看限制在哪里，我们应该看看一个具体的例子，比如，这个字段包含几个句子。

我们现在来创建 'Movies' 表：

    CREATE TABLE `电影` (  
        id SERIAL PRIMARY KEY,  
        片名 VARCHAR(255) NOT NULL,  
        简介 TEXT,  
        上映日期 DATE,  
        创建日期 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  
        最后更新日期 TIMESTAMP DEFAULT CURRENT_TIMESTAMP  
    );

咱们来插入些示范数据吧

    INSERT INTO 电影 (名称, 概述, 上映日期)  
    VALUES  
    ('肖申克的救赎', '两名囚犯在狱中多年相互扶持，最终找到了慰藉和救赎。', '1994-09-23'),  
    ('教父', '黑帮家族的长老将家族帝国的控制权移交给了自己并不情愿的儿子。', '1972-03-24'),  
    ('黑暗骑士', '小丑从神秘的过去中浮现，对哥谭市造成了巨大的破坏和混乱。', '2008-07-18'),  
    ('教父续集', '描绘了维托·柯里昂在20世纪20年代纽约的早期生活和事业，同时他的儿子迈克尔逐步扩大并巩固家族犯罪集团的控制权。', '1974-12-20'),  
    ('十二怒汉', '一名陪审团成员试图通过迫使同事重新审视证据来防止判决的失误。', '1957-04-10'),  
    ('辛德勒的名单', '在第二次世界大战期间的德国占领波兰，工业家奥斯卡·辛德勒开始关心他的犹太工人，因目睹了纳粹对他们的迫害。', '1993-12-15'),  
    ('魔戒三部曲：王者再临', '甘道夫和阿拉贡领导人类世界对抗索伦的军队，吸引索伦的注意力，以便佛罗多和山姆能接近末日火山并销毁魔戒。', '2003-12-17'),  
    ('低俗小说', '两名黑帮杀手、一名拳击手、一名黑帮和他妻子的生活，以及一对在餐馆的强盗交织在一起，讲述了四个关于暴力和救赎的故事。', '1994-10-14'),  
    ('好坏丑', '一场赏金追捕的骗局将两名男子联合起来对抗第三个人，他们在一个遥远墓地争夺一笔藏金。', '1966-12-23'),  
    ('搏击俱乐部', '一个失眠的办公室员工和一个无所畏惧的肥皂制造商组成了一个地下搏击俱乐部，最终演变成了更复杂的事情。', '1999-10-15');

我们来给 summary 创建一个 B-Tree 索引，怎么样？

在 movies 表的 summary 列上创建名为 idx_summary 的索引;

在每次执行此操作之前，先运行表分析：

分析电影;

我们来执行一些查询，看看哪些查询会给我们想要的记录（《肖申克的救赎》），以及它们是否使用了我们的B+树索引。

我们想要得到的记录

1. 以某事开头 — 搜索词：Two

    EXPLAIN ANALYZE SELECT * FROM movies WHERE summary LIKE 'Two%';

解释并执行查询：从movies表中选择summary字段以'Two%'开头的所有记录。

在 Postgres 中进行这样的查询时，查询以某个短语开头的内容，但这样的查询没有使用索引。但实际上应该使用索引。

实际上这挺奇怪的，因为我们可以用B-Tree索引（B-Tree Index）来查找只有以Two开头的那些文本，然后再从其他文本中筛选出来。即使添加了300万条记录后仍然无法使用索引，问题出在哪儿呢？

我们需要用一个带有“排序规则”的B树索引重建我们的B树索引：

在定义索引时，排序规则规定了字符串的比较和排序方式，这里我们使用的是“C”规则，它进行的是简单的按字节比较。

我们再建个索引吧

    DROP INDEX IF EXISTS idx_summary;  
    CREATE INDEX idx_summary ON movies (summary COLLATE "C");

(Note: 在源代码中，如果索引 idx_summary 存在则删除该索引，然后创建一个新的索引 idx_summary 在 movies 表的 summary 列上，忽略字符集排序规则。)

如果我们现在重新运行这个查询，它就会使用索引。

如果你在查询规划器中仍然看到顺序扫描查询，那是因为，表太小了。使用这个简单的查询添加100万条记录，这样就可以说服PostgreSQL使用索引了。

DO $
BEGIN
    -- 循环1000000次，插入随机生成的电影信息
    FOR i IN 1..1000000 LOOP
        INSERT INTO movies (name, summary, release_date)
        VALUES (
            md5(random()::text),  -- 随机生成的电影名称
            md5(random()::text),  -- 随机生成的电影简介
            '2022-01-01'         -- 发布日期固定为2022年1月1日
        );
    END LOOP;
END;
$;

在使用了 Collation 之后，PostgreSQL 在搜索以某个特定字符开头的 LIKE 查询时会使用索引。

你已经注意到，因为有一个句子，我们需要传递句子开头的部分以获取这条记录。

2- 以...结尾：“体面二字。”

查一下：

执行以下SQL命令来分析并检索包含'decency'关键字的电影摘要：

    EXPLAIN ANALYZE SELECT * FROM movies WHERE summary LIKE '%decency.';

不能用于匹配以某内容结尾的字符串的查询。

我们根本用不了B-树索引，因为我们无法限定范围

字符串的例子在B-树中

如果有人要找以 a 开头的水果，我们可以从 grape（葡萄）开始，因为 g 在字母表中比 a 来得后面，所以我们向左分支查找。然后为了找到 apple，我们还得往左走，最后找到它。但如果有人要找以 a 结尾的水果，唯一的办法就是一个个扫描，找出所有符合条件的水果。

3- 忽略大小写，如 'tWO'

查一下

从movies表中选择summary字段以'tWO'开头的所有记录。\n注：这里的"ILIKE"类似于"LIKE"，但它是一个不区分大小写的模式匹配，且匹配模式必须从字符串的开始位置匹配。

对于不区分大小写的查询来说，我们不能使用B树索引，因为这种索引要求查询中的大小写一致。

我们不能使用索引的原因与我们想要匹配以特定内容结尾的字符串时遇到的问题一样，在这里我们不知道应该在 B 树中查找哪个具体项，我们应该查找 t 还是 T，接下来查找 o 还是 O，等等。

4- 包含：“慰藉”

你想问什么?

    EXPLAIN ANALYZE SELECT * FROM movies WHERE summary ILIKE '%solace%';

这将显示与'solace'相关的所有电影信息。

对于包含一个字符串，我们不能用B树索引来包含一个字符串

这里我们也搞不定任何索引。

5- 拼写错误

这里没有与这相关的查询。

1. 相似词

如果我们有一条记录包含“satisfy”，而有人在搜索“satisfaction”这个词，我们可能希望返回这条记录。仅使用B-Tree索引，我们无法识别语言和词汇之间的相似性。让我们看看能否找到一个好办法。

目前为止，我们已经搞清楚问题了。

我们需要全文搜索摘要的原因

• 如我们所见，有些情形下，B-Tree索引无法帮助（比如以某个词结尾或包含某个词）
• 普通搜索不考虑语言，所以如果你在搜索“satisfy”，将无法找到包含“satisfaction”这类词的记录
• 搜索结果里没有排名哦！排名可以帮助我们对结果进行排序，并把最相关的结果放在最前面。

TSVECTOR

tsvector 是一种数据类型。其值是一个排序后的不同词项列表。

什么是词素？

Wikipedia ：词项是具有词汇意义的最小单位，它是一组通过屈折变化相关联的单词的基础。简单来说，它就是一个词族的代表词。

这些词 run 、runs 、ran 和 running 都是从同一个词根 跑 变化而来。

你可能会在搜索全文搜索时听到“词干化”这个词，词干化就是将单词简化到其词根的过程。比如：running会被简化为run。

让我们使用 to_tsvector 来实际看看它是怎么将文本转换为词汇单元的：

    SELECT to_tsvector('english',   
    '两个囚犯经过多年结下了深厚的友谊，通过他们共同的体面行为最终找到了安慰和救赎。');

这将是结果.

to_tsvector的结果是

    '行动':16 '债券':4 '共同的':18 '体面的':19 '最终的':13 '找到':10 '坐牢':2 '男人':3 '数字':7 '赎罪':14 '安慰一下':11 '两':1 '年':9

如你所见，这是一系列键值对。键是词汇单位，它们的位置即代表其值。

你已经可以看见实际意义和它们的词元之间的差异：

示例句子，和词汇元素

你会发现有些词和句点没被包含在内。

• 超过
• 一
• 的
• 穿过
• 和

这些被称为“停用词”，在全文搜索中我们会忽略这些词，以便提供更准确的结果。搜索像 and 这样的词也没有意义。PostgreSQL 已经自动移除了停用词，这是因为我们指定了文本的语言。

PostgreSQL 提供了更多的功能，比如 同义词词典(ispell) 和 同义词词典，以便进行更多的自定义。

要查询这个列表，我们需要使用叫做 TSQUERY 的函数，它提供了一些操作符来查询文本。

TSQUERY (一种查询语言)

TO_TSQUERY 是将查询字符串转换成用于文本查询的类型的这样的函数。

操作员：

• 和: &
• 或者: |
• 不: !
• 短语匹配（词之间不能有空格）: <-->
• 如果你想匹配以特定单词开头的内容，你可以输入如 run:* 这样的格式。

看看下一节，了解它是怎么运作的。

格子用 TS_TSVECTOR 和 TO_TSQUERY 玩一玩

这个符号 @@ 用于执行全文搜索：

这样一个句子：两个囚犯在多年的牢狱生活中建立了深厚的友谊。

我们来看看 tsvector 版本：

    SELECT to_tsvector('english','两个囚犯在多年的时间里建立了深厚的友谊。')  
    -- 'imprison':2 'men':3 'number':7 'two':1 'year':9

下面是一些查询，这些查询针对一个返回真或假的句子，我们可以看看这些查询是如何工作的。

    SELECT to_tsvector('english', 'Two imprisoned men bond over a number of years.') @@ to_tsquery('english', 'tWo'); -- true  
    SELECT to_tsvector('english', 'Two imprisoned men bond over a number of years.') @@ to_tsquery('english', 'TWO'); -- true  
    SELECT to_tsvector('english', 'Two imprisoned men bond over a number of years.') @@ to_tsquery('english', 'years'); -- true  
    SELECT to_tsvector('english', 'Two imprisoned men bond over a number of years.') @@ to_tsquery('english', 'yEar'); -- true

运行一些示例查询以及它们的结果，这些查询的结果都一样。

正如你所见，我们要找的单词是否小写并不重要，都会匹配。

不管这个词出现在句子的任何位置，这都不重要。所以我们已经排除了一些可能性。

如果我们查找词 of ：

SELECT to_tsvector('english', '两名囚犯在多年的时间里建立了联系。') @@ to_tsquery('english', 'of'); -- false

结果正如你所料是假的，因为它是一个停用词（stop word），即常见的单词，如“的”、“是”等，并且在我们的 tsvector 中找不到。

让我们看看使用tsquery并结合“AND”、“OR”和“Adjacency”这些操作符可以执行的一些更实用的查询示例。

    SELECT to_tsvector('english','Two imprisoned men bond over a number of years.') @@ to_tsquery('english', 'number & years');-- true  
    SELECT to_tsvector('english','Two imprisoned men bond over a number of years.') @@ to_tsquery('english', 'number | something');-- true  
    SELECT to_tsvector('english','Two imprisoned men bond over a number of years.') @@ to_tsquery('english', 'bond <-> over');-- true

以下是一些示例查询的结果。

这样一来，我们已经使用tsvector将文本转换为有意义的词素列表，并能够使用@@操作符和tsquery来搜索短语或词组。

现在我们来在Movies表中使用它。

在电影表中加一个 TSVECTOR 字段 (TSVECTOR 类型)。

这是我们曾经用到的表格：

    CREATE TABLE 电影表 (  
        id SERIAL PRIMARY KEY,  
        电影名称 VARCHAR(255) NOT NULL,  
        简介 TEXT,  
        上映日期 DATE,  
        创建日期 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  
        最后更新时间 TIMESTAMP DEFAULT CURRENT_TIMESTAMP  
    );

现在，为了做全文检索，我们想把电影简介转换成 TSVECTOR 格式存储。

ALTER TABLE movies ADD COLUMN summary_tsvector TSVECTOR   
GENERATED ALWAYS AS (to_tsvector('english', summary)) STORED;

注：summary_tsvector 列将自动生成为英文摘要的全文搜索向量，并进行存储。

生成的短语总是会同步更新这个字段，根据摘要字段，保持文本的 tsvector 版本不变。

我们来做个查询吧

    SELECT * FROM movies WHERE summary_tsvector @@ to_tsquery('english', 'tWo');

查询带有 tWo 的摘要

我们看到了结果，现在来简单解释一下这个查询。

顺序扫描

我们可以使用操作符或动词的其他形式来进行查询。

现在让我们用一个索引来变得更高效。这里我们就可以用 GIN索引了。

GIN指标

GIN 索引为每个复合数据类型中的单独元素存储单独的条目。因此，当我们的字段是一个数组、JSON 或在我们的情况中是 tsvector 字段时，GIN 非常有用！

如果你有 MongoDB 的背景，这里就是用来通配符索引数组和对象，通配符索引详见 https://www.mongodb.com/docs/manual/core/indexes/index-types/index-wildcard/。

我们用GIN索引来搜索摘要：

我们无需单独保存tsvector版本，并且可以直接创建索引。

    CREATE INDEX idx_summary_gin ON movies USING gin (to_tsvector('english', summary));

创建一个名为 idx_summary_gin 的索引，该索引使用GIN（Generalized Inverted Index）在 movies 表的 summary 字段上建立，通过将 summary 字段转换为英文文本向量来实现。

现在我们可以运行这个查询了,

    SELECT * FROM movies WHERE to_tsvector('english', summary) @@ to_tsquery('english', 'solace');

从movies表中选择所有满足条件的记录，这些记录的summary字段通过英文全文搜索匹配'solace'。

使用gin索引来汇总

正如你所见，它使用了索引，对我而言，结果仅用了15ms就出来了。

没有GIN索引的情况下，同一查询需要大约600毫秒。

让我们来突出显示这些结果

我们可以用ts_headline这个函数来突出显示在文本中找到的匹配项。

例如，如何使用它：

    SELECT  
        id, summary,  
        ts_headline('english', summary, to_tsquery('english', 'redemption')) AS highlighted_summary  
    FROM  
        movies  
    WHERE  
        to_tsvector('english', summary) @@ to_tsquery('english', 'redemption');
-- 搜索摘要中包含 "redemption" 的电影，并高亮显示

结果就是：

高亮总结

经过好几年，通过一些日常的好事找到了安慰，最终得到了救赎。

它只是用一个加粗的 HTML 标签来包裹文字，就这么简单！

概要的第一段

要做全文搜索，我们需要考虑到语言，并且需要不同的文本存储方式。我们使用TSVECTOR将文本转换为词元，使搜索更方便。当有TSVECTOR类型的字段时，我们可以使用GIN索引。还有一个TS_HEADLINE函数可以，用加粗标签突出显示结果。

备注

千万不要忘记，每添加一个索引，你都会降低数据库的写入速度。GIN 索引可能比 B-Tree 索引对你的数据库产生更大的影响，因为它们更复杂且需要索引更多的实体。

下一部分很快就来啦…

接下来的这部分是：

如何使用Trigram？ 相似度计算 搜索结果排序: 12.3. 控制文本搜索 # 12.3.1. 解析文档内容 12.3.2. 解析搜索查询 12.3.3. 排序搜索结果… www.postgresql.org 第12章 全文搜索 目录 12.1.简介 12.1.1.什么是文档？ 12.1.2.基本文本搜索……www.postgresql.org 提升编码水平

感谢你加入我们社区！在你离开前：

• 👏 给这个故事鼓掌，并关注作者 👉
• 📰 查看更多 Level Up Coding 的精彩内容
• 💰 免费的编码面试课程 ⇒ 查看更多详情

🔔 关注我们！|Twitter账号 | LinkedIn频道 | Newsletter 订阅我们的电子报

🚀👉 加入 Level Up，找到一份超棒的工作