首页 慕课教程 SQL 入门教程 SQL 入门教程 实战6:PostgreSQL全文检索功能实战

实战6:PostgreSQL 全文检索功能实战

1. 前言

本小节,我们一起来学习 PostgreSQL 中的一大杀器——FTS(Full Text Search,全文检索)。

提到全文搜索,你是否立刻想到了大名鼎鼎的LuceneElasticsearch。Elasticsearch 基于 Lucene ,并为开发者提供丰富的接口和工具,但是这也造成了它日益庞大。

使用它,你得备上一个大的服务器,一个优秀的运维团队,还要承受数据同步的心智负担。但你的需求其实很简单,只是一个小功能搜索,或者一个简单的全站搜索。如果在项目的初期,花费如此大成本在搜索上有些得不偿失。

如果数据库本身就支持全文检索,那该多好啊!没错,PostgreSQL 就支持全文搜索,而且很强大,还支持插件扩展定制。

2. FTS配置库

2.1 PostgreSQL 默认 FTS

PostgreSQL 全文搜索是通过 FTS 配置库来支持的,大多数 PostgreSQL 发行版都自带了 10 个以上的 FTS 配置库,我们可以通过psql\dF命令来查看已安装的配置库:

                  List of text search configurations
   Schema   |    Name    |                Description
------------+------------+--------------------------------------------
 pg_catalog | arabic     | configuration for arabic language
 pg_catalog | danish     | configuration for danish language
 pg_catalog | dutch      | configuration for dutch language
 pg_catalog | english    | configuration for english language
 pg_catalog | finnish    | configuration for finnish language
 pg_catalog | french     | configuration for french language
 pg_catalog | german     | configuration for german language
 pg_catalog | hungarian  | configuration for hungarian language
.......

可以看到 PostgreSQL 默认已经安装了大量的 FTS 搜索配置库,但是很不幸没有中文配置库。但好在,PostgreSQL 支持插件的形式来扩展 FTS,所以我们可以使用成熟的扩展库。

2.2 pg_jiebe FTS

jieba是国内一个颇为著名分词库,如果你是 Python 开发者,那么一定听过它的大名。有贡献者为 PostgreSQL 提供了 jieba 分词插件——pg_jieba,让我们可以在 PostgreSQL 使用到中文全文检索。

如果你想跟着我们一起,完成本节的实战内容,那么请先点开此链接安装 pg_jieba。

如果你安装成功,那么可以通过\dF命令来找到jieba相关的分词配置:

 public     | jiebacfg   | Mix segmentation configuration for jieba
 public     | jiebahmm   | Hmm segmentation configuration for jieba
 public     | jiebamp    | MP segmentation configuration for jieba
 public     | jiebaqry   | Query segmentation configuration for jieba

可以看到jieba提供了4种分类器,它们分别对应了不同的分词算法,如果你感兴趣,可以查阅相关的资料,这里我们不做过多的介绍,默认使用jiebacfg即可。

3. 基本使用

3.1 FTS 流程

全文搜索大致可分为两部分:

  1. 构建文本对应的索引(倒排索引)
  2. 通过搜索索引来找到对应的文本

3.2 文本向量化

在 FTS 中,原始文本在构建索引之前需要被向量化。原始文本(如:字符串)必须先被向量化后才能通过 FTS 对其检索,向量化后的内容需要存储到一个单独的向量字段中,该向量的数据类型是tsvector

PostgreSQL 提供了to_tsvector函数来将原始文本向量化,如下:

SELECT * FROM to_tsvector('jiebacfg','SQL,你敢吃我俺老孙一棒吗?');
                to_tsvector
-------------------------------------------
 'sql':1 '一棒吗':9 '吃':5 '敢':4 '老孙':8

tsvector是由(词,序列)元组组成的列表,如sql是原始文本中的第一个词,所以它的序列是1

3.3 搜索关键字向量化

有了索引后,我们如何来搜索索引了?

一般情况下,我们是通过关键词来检索的,那么如何来组织关键词呢?

PostgreSQL 提供了to_tsquery函数来将词组织成tsquery向量,然后通过向量去搜索。如下:

SELECT to_tsquery('sql & java');
   to_tsquery
----------------
 'sql' & 'java'

tsquery是一种特殊的数据类型,它会将关键词拼接来表示搜索条件,如&表示搜索的内容必须包含sql和java。举个复杂的例子:

SELECT to_tsquery('sql & (java | python)');
          to_tsquery
-------------------------------
 'sql' & ( 'java' | 'python' )

这个例子表示,搜索的内容必须包含sqljava与python中的一种。

3.4 搜索关键句向量化

当然你也可以使用句子来搜索:

SELECT * FROM to_tsquery('jiebacfg','SQL难道不香吗?');
        to_tsquery
---------------------------
 'sql' & '难道' & '不香吗'

在输入句子的情况下,to_tsquery会自动将句子分词,然后将其拼接为tsquery

3.5 FTS 总结

我们总结一下 FTS 的使用:

  1. 原始文本,即字符串不能被直接搜索,我们通过 to_tsvector 函数将其向量化为词组,并保存到某个字段中,该字段数据类型为 tsvector。
  2. tsvector 的字段存储的是词与词序列的元组,需要新建 gin 索引才能使用搜索,下面会介绍。
  3. 搜索条件,狭义上可以理解成搜索关键字,也需要通过 to_tsquery 来向量化,且类型为 tsquery。
  4. 使用 tsquery 去搜索 tsvector,在下面的部分会介绍到。

4. 实践

接下来,我们以实践的角度来使用和学习一下 FTS。

4.1 文章搜索

假设某个应用有一个文章搜索功能点,我们将通过 FTS 来实现它。

首先,我们新建文章数据表:

DROP TABLE IF EXISTS article;
CREATE TABLE article
(
  id      serial PRIMARY KEY,
  title   varchar(40),
  content text
);

id是每篇文章的唯一标识,title是标题,content是文章内容,我们省略了其它信息。然后我们插入几条记录:

INSERT INTO article(id, title, content)
VALUES (1, '科学和人文谁更有意义', '科学和人文谁更有意义,发生了会如何,不发生又会如何。 本人也是经过了深思熟虑,在每个日日夜夜思考这个问题。 一般来讲,我们都必须务必慎重的考虑考虑。 本人也是经过了深思熟虑,在每个日日夜夜思考这个问题。 马云曾经提到过,最大的挑战和突破在于用人,而用人最大的突破在于信任人。我希望诸位也能好好地体会这句话。 既然如此, 科学和人文谁更有意义,发生了会如何,不发生又会如何。 富勒在不经意间这样说过,苦难磨炼一些人,也毁灭另一些人。这启发了我, 塞内加曾经提到过,勇气通往天堂,怯懦通往地狱。这不禁令我深思。 '),
(2, '编程的艺术','对我个人而言,编程的艺术不仅仅是一个重大的事件,还可能会改变我的人生。 编程的艺术,到底应该如何实现。 伏尔泰曾经提到过,坚持意志伟大的事业需要始终不渝的精神。这似乎解答了我的疑惑。 既然如何, 生活中,若编程的艺术出现了,我们就不得不考虑它出现了的事实。 我们不得不面对一个非常尴尬的事实,那就是, 莎士比亚曾经说过,抛弃时间的人,时间也抛弃他。这启发了我, 编程的艺术因何而发生? 要想清楚,编程的艺术,到底是一种怎么样的存在。 编程的艺术的发生,到底需要如何做到,不编程的艺术的发生,又会如何产生。 既然如此, 那么。'),
(3, '生命在于创造','在这种困难的抉择下,本人思来想去,寝食难安。 带着这些问题,我们来审视一下生命在于创造。 我认为, 一般来说, 生命在于创造因何而发生? 可是,即使是这样,生命在于创造的出现仍然代表了一定的意义。 生命在于创造,到底应该如何实现。 问题的关键究竟为何? 生活中,若生命在于创造出现了,我们就不得不考虑它出现了的事实。 生命在于创造因何而发生? 莎士比亚曾经提到过,人的一生是短的,但如果卑劣地过这一生,就太长了。我希望诸位也能好好地体会这句话。');

4.2 构建文章索引

有了标题和内容后,我们需要为每篇文章单独新建一个字段fts用来表示每篇文章的 tsvector 字段,并且给 fts 字段创建 gin 索引,这样后面就可以通过该字段来搜索文章了。

ALTER TABLE article ADD COLUMN fts tsvector;
UPDATE article
SET fts = setweight(to_tsvector('jiebacfg', title), 'A') ||
          setweight(to_tsvector('jiebacfg', content), 'B');
CREATE INDEX article_fts_gin_index ON article USING gin (fts);

在 SQL 语句中,我们首先为article数据表新增了一个fts字段,字段类型为tsvector。有了该字段后,我们需要为该字段赋值,通过to_tsvector我们将每篇文章的titlecontent分别向量化。

由于titlecontent的重要性不一样,文章的标题明显比内容数据更加重要,因此setweight设置标题的权重为A,而内容的权重为BA的重要性大于B||操作符合并向量后将结果赋给fts

到此,article 表中新增了一个 fts 字段,字段中是标题和内容词组的列表。最后为 fts 字段我们新建了索引 article_fts_gin_index 来加速我们的搜索效率。

提示: || 操作符是 PostgreSQL 的一个特点,表示连接、合并。

4.3 使用 FTS

接下来,我们便可以使用全文搜索了,搜索条件是文章需包含问题关键字,如下:

SELECT title FROM article WHERE fts @@ to_tsquery('问题');
        title
----------------------
 科学和人文谁更有意义
 生命在于创造

PostgreSQL 提供@@操作符来搜索,上面语句将问题通过to_tsquery转化为向量后,使用@@来搜索。从结果中可以看出,与问题相关的文章有两篇。

注意: 在 article 表中,只有 fts 是 tsvector 字段,因此只有它能使用 @@ 操作符。

我们再尝试一下复杂的搜索,搜索条件是文章必须含有问题生命两个关键字:

SELECT title FROM article WHERE fts @@ to_tsquery('问题 & 生命');
    title
--------------
 生命在于创造

4.4 完善文章搜索

从结果中可以看到,全文搜索已经可以工作了,但它还不完备,如果更新或者添加文章,内容发生了改变,那么索引也应该随之变化,我们可以使用触发器来解决这个需求点。运行如下 SQL:

DROP TRIGGER IF EXISTS trig_article_insert_update ON article;
CREATE TRIGGER trig_article_insert_update
  BEFORE INSERT OR UPDATE OF title,content
  ON article
  FOR EACH ROW
EXECUTE PROCEDURE tsvector_update_trigger(fts, 'public.jiebacfg', title, content);

有了 trig_article_insert_update 这个触发器后,article 表中插入或 title,content 的更新都会引起 fts 向量的重建,由此一个比较完备的全文检索功能点也就完成了。

我们的全文搜索实战到此就结束了,你完全可以按照这种模式改编成你自己的应用,让它支持炫酷的全文搜索功能。

5. 小结

  • PostgreSQL的全文搜索的功能还是非常强大的,本节内容仅仅只是一部分,你可以阅读官方文档获取更多的信息。
  • 如果你需要强大的全文搜索功能以及数据分析能力,Elasticsearch或许更加适合你。