企业内部的运营管理类搜索，使用 PostgreSQL 足够了-51CTO.COM

PostgreSQL 全文搜索简介

在 PostgreSQL 中，全文搜索是一项内置功能，允许您对存储在数据库中的文本执行复杂的搜索。

对于文档、文章或任何形式的基于文本的内容，全文搜索通过对其中的单词和短语建立索引，可以实现高效地搜索。

下面概述了 PostgreSQL 全文搜索的工作原理：

• 索引：PostgreSQL 允许您在具有 tsvector 数据类型的列上创建索引。在创建全文搜索的索引时，PostgreSQL 会分析文本数据，并生成一种叫做 tsvector 的专用数据结构，该结构采用了一种针对搜索优化过的格式来表示索引的文档。

• 文本分析：为了构建全文索引，PostgreSQL 会执行文本分析过程，该过程涉及将文本标记化为单个单词或标记，删除停用词（如the，and，...），提取词干（或词形还原）将单词简化为根形式，并执行其他语言转换以准备索引文本。

• 查询：在 PostgreSQL 创建全文索引后，可以使用专用的全文搜索函数和操作符，来执行全文搜索查询。例如，您可以搜索特定的单词或短语，应用布尔操作符来组合搜索词等。

• 排名：允许您根据搜索结果与查询的相关性，对搜索结果进行排名。PostgreSQL 提供了ts_rank()函数，根据每个文档与搜索查询的相似性，计算其排名分数。您可以使用排名功能，根据相关性对搜索结果进行排序。

• 突出显示：PostgreSQL 可以对包含搜索查询中匹配词的文档，生成片段或摘要。

现实情况下，在内容管理系统（CMS）、文档管理系统等应用程序中，您经常会使用全文搜索功能，以便对大量文本进行快速搜索。

PostgreSQL 全文搜索数据类型

PostgreSQL 为全文搜索提供了两种独特的数据类型：tsvector和tsquery：

tsvector

tsvector是一种数据类型，它允许您采用一种格式来存储预处理的文档，该格式为高效地搜索和提取文本做了优化。

一个tsvector值是一个排序的列表，列表中的元素包含了一个词素（单词），以及它们在文档中的位置和权重。

请注意，词素是没有由后缀产生的变化的单词，例如，watches，watched和watching单词都对应词素watch。

例如，下面语句使用了to_tsvector()函数，将单词watches、watched和watching转换为tsvector：

SELECT to_tsvector('watches'), 
       to_tsvector('watched'), 
       to_tsvector('watching');

输出：

to_tsvector | to_tsvector | to_tsvector
-------------+-------------+-------------
 'watch':1   | 'watch':1   | 'watch':1
(1 row)

在本例中，to_tsvector()函数将单词转换为 tsvector 值。它不是返回原始单词，而是返回这些单词的词素，即watch。

下面的示例，使用to_tsvector()函数，将字符串转换为 tsvector 值。例如：

SELECT to_tsvector('The quick brown fox jumps over the lazy dog.');

输出：

to_tsvector
-------------------------------------------------------
 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2
(1 row)

在此示例中：

• tsvector 值中的每个条目表示了一个单词（词素），及其在字符串（或文档）中的位置。例如，单词quick出现在位置 2，单词brown出现在位置 3，依此类推。

• 单词按字母顺序排序。

• 文档中的停用词会被省略，如The和over。

tsquery

在全文搜索中，tsquery是一种用于表示搜索查询的数据类型。它允许您指定包含索引文档的单词或短语的搜索条件。

此外，tsquery值可以包括搜索操作符，以细化搜索条件。

• 布尔操作符 AND (&)、OR (|) 和 NOT (!)：可以组合搜索词，并定义它们之间的逻辑关系。

• 短语搜索 (“”)：双引号 (“”) 表示括起来的单词必须按指定的顺序一起出现在索引文档中。

• 前缀搜索 (:) ：单词后面的冒号 (:) 表示搜索词应与具有相同前缀的单词匹配。

• 取否 (-) ：取否操作，用于排除匹配特定搜索词的搜索结果。

• 分组 () ：您可以使用括号对术语和操作符进行分组，以定义灵活的搜索条件。

例如：

'quick' & 'brown' | 'fox'

该tsquery会搜索同时包含单词 “quick” 和 “brown”（以任意顺序）或单词 “fox” 的文档。

to_tsquery()将字符串转换为一个tsquery。例如，下面语句使用to_tsquery()，将单词 “jumping” 转换为一个tsquery：

SELECT to_tsquery('jumping');

输出：

to_tsquery
------------
 'jump'
(1 row)

匹配操作符（@@）

匹配操作符（@@）会评估文档中的文本（tsvector）与搜索查询中指定的搜索词（tsquery）之间的相似性，如果匹配，则返回 true，否则返回 false。

tsvector @@ tsquery

例如，下面语句使用@@操作符，来确定字符串是否与tsquery匹配：

SELECT
  to_tsvector(
    'The quick brown fox jumps over the lazy dog.'
  ) @@ to_tsquery('jumping') result;

输出：

result
--------
 t
(1 row)

它返回了 true，因为 tsvector 中包含了单词 jump，它是单词 jumping 的词素。

下面的示例使用匹配运算符（@@），来确定字符串是否包含单词 cat：

SELECT 
  to_tsvector(
    'The quick brown fox jumps over the lazy dog.'
  ) @@ to_tsquery('cat') result;

输出：

result
--------
 f
(1 row)

在表数据上使用 PostgreSQL 全文搜索索引

让我们来看一些使用布尔操作符进行全文搜索的示例。

1) 设置示例表

首先，创建一个新表，名为posts：

CREATE TABLE posts(
   id SERIAL PRIMARY KEY,
   title TEXT NOT NULL,
   body TEXT,
   body_search TSVECTOR  
      GENERATED ALWAYS AS (to_tsvector(body)) STORED
);

在posts表中，body_search是一个数据类型为tsvector的生成列。

当你在更改body列中的数据时，PostgreSQL 会使用to_tsvector()函数将其转换为tsvector，并将其存储在body_search列中。

第二步，在posts表中插入一些行：

INSERT INTO posts (title, body)
VALUES
    ('Introduction to PostgreSQL', 'This is an introductory post about PostgreSQL. It covers basic concepts and features.'),
    ('Advanced PostgresSQL Techniques', 'In this post, we delve into advanced PostgreSQL techniques for efficient querying and data manipulation.'),
    ('PostgreSQL Optimization Strategies', 'This post explores various strategies for optimizing PostgreSQL database performance and efficiency.');

第三步，检索id和body_search列中的数据：

SELECT 
  id, 
  body_search 
FROM 
  posts;

输出：

id |                                                body_search
----+------------------------------------------------------------------------------------------------------------
 1  | 'basic':10'concept':11'cover':9'featur':13'introductori':4'post':5'postgresql':7
 2  | 'advanc':7'data':14'delv':5'effici':11'manipul':15'post':3'postgresql':8'queri':12'techniqu':9
 3  | 'databas':9'effici':12'explor':3'optim':7'perform':10'post':2'postgresql':8'strategi':5'various':4
(3 rows)

2) 简单的全文搜索

下面的示例使用了匹配操作符（@@），来搜索其body列包含单词"PostgreSQL"的帖子：

SELECT 
  id, 
  body 
FROM 
  posts 
WHERE 
  body_search @@ to_tsquery('PostgreSQL');

输出：

id |                                                   body
----+----------------------------------------------------------------------------------------------------------
 1  | This is an introductory post about PostgreSQL.It covers basic concepts and features.
 2  | In this post, we delve into advanced PostgreSQL techniques for efficient querying and data manipulation.
 3  | This post explores various strategies for optimizing PostgreSQL database performance and efficiency.
(3 rows)

3) 使用 AND 操作符进行全文搜索

下面的示例使用了 AND 运算符（&），搜索正文同时包含单词 “PostgreSQL” 和 “technique” 的帖子，这些单词可以按任意顺序出现：

SELECT 
  id, 
  body 
FROM 
  posts 
WHERE 
  body_search @@ to_tsquery('PostgreSQL & techniques');

输出：

id |                                                   body
----+----------------------------------------------------------------------------------------------------------
  2 | In this post, we delve into advanced PostgreSQL techniques for efficient querying and data manipulation.
(1 row)

4) 使用 OR 操作符进行全文搜索

下面的示例使用了 OR 运算符（|），搜索正文包含单词"efficient"或"optimization"的帖子：

SELECT 
  id, 
  body 
FROM 
  posts 
WHERE 
  body_search @@ to_tsquery('efficient | optimization');

输出：

id |                                                   body
----+----------------------------------------------------------------------------------------------------------
  2 | In this post, we delve into advanced PostgreSQL techniques for efficient querying and data manipulation.
  3 | This post explores various strategies for optimizing PostgreSQL database performance and efficiency.
(2 rows)

5) 使用短语进行全文搜索

下面的示例搜索正文包含短语 “PostgreSQL technique” 的帖子：

SELECT 
  id, 
  body 
FROM 
  posts 
WHERE 
  body_search @@ to_tsquery('''PostgreSQL technique''');

输出：

id |                                                   body
----+----------------------------------------------------------------------------------------------------------
  2 | In this post, we delve into advanced PostgreSQL techniques for efficient querying and data manipulation.
(1 row)

6) 对全文搜索进行取否

下面的示例搜索正文中不包含 “efficient” 一词的帖子：

SELECT id, body
FROM posts 
WHERE NOT body_search @@ to_tsquery('efficient');

输出：

id |                                         body
----+---------------------------------------------------------------------------------------
  1 | This is an introductory post about PostgreSQL. It covers basic concepts and features.
(1 row)

使用 GIN 索引进行全文搜索

在 PostgreSQL 中，GIN 意思是通用倒排索引。GIN 索引是一种针对全文搜索向量（tsvector）做过优化的索引类型。

首先，使用下面语句，删除posts表，并重新创建它：

DROP TABLE IF EXISTS posts;

CREATE TABLE posts(
   id SERIAL PRIMARY KEY,
   title TEXT NOT NULL,
   body TEXT
);

第二步，插入行到posts表中：

INSERT INTO posts (title, body)
VALUES
    ('Introduction to PostgreSQL', 'This is an introductory post about PostgreSQL. It covers basic concepts and features.'),
    ('Advanced PostgresSQL Techniques', 'In this post, we delve into advanced PostgreSQL techniques for efficient querying and data manipulation.'),
    ('PostgreSQL Optimization Strategies', 'This post explores various strategies for optimizing PostgreSQL database performance and efficiency.');

第三步，在posts表的body列上创建一个 GIN 索引：

CREATE INDEX body_fts
ON posts
USING GIN ((to_tsvector('english',body)));

最后，搜索正文中包含单词basic或advanced的帖子：

SELECT 
  id, 
  body 
FROM 
  posts 
WHERE 
  body @@ to_tsquery('basic | advanced');

输出：

id |                                                   body
----+----------------------------------------------------------------------------------------------------------
  1 | This is an introductory post about PostgreSQL. It covers basic concepts and features.
  2 | In this post, we delve into advanced PostgreSQL techniques for efficient querying and data manipulation.
(2 rows)

总结

• 使用 PostgreSQL 全文搜索功能，对数据库中存储的文本进行复杂搜索。

• 使用 tsvector 和 tsquery 数据类型进行全文搜索。

• 使用匹配操作符（@@），检查文档是否与查询匹配。

• 使用生成的tsvector类型的列，存储用于全文搜索的 tsvector 数据。

• 对全文搜索向量（tsvector）使用GIN索引。