PostgreSQL的进阶使用： 高级全文搜索

引言

PostgreSQL 不仅是一个强大的关系型数据库，还内置了功能完善的全文搜索（Full-Text Search, FTS）能力。相较于简单的 LIKE 模式匹配，PostgreSQL 的全文搜索能够更智能、更高效地处理文本数据的检索需求，例如支持自然语言查询、忽略常用词、词干提取、排名等高级特性。本文将深入介绍 PostgreSQL 的高级全文搜索功能，并通过不同场景下的代码示例，帮助您在实际应用中充分利用这一强大的特性。

技术背景

传统的数据库文本搜索通常依赖于 LIKE 操作符或正则表达式，这些方法在处理复杂的文本查询时存在以下局限性：

• 性能低下： 对大型文本字段进行模糊匹配或正则匹配时，会进行全表扫描，效率较低。
• 语义理解不足： 无法理解词语之间的语义关系，例如同义词、词根等。
• 相关性不高： 无法根据查询词语与文档的匹配程度进行排序。
• 语言支持有限： 难以处理不同语言的词干、停用词等。

PostgreSQL 的全文搜索功能通过引入新的数据类型、函数和索引，有效地解决了这些问题：

• tsvector (文档向量): 将文本文档解析成一组词位（lexemes），并进行规范化处理（例如转换为小写、去除词缀）。
• tsquery (查询向量): 将用户输入的查询解析成一组查询词位和操作符（例如 & 表示 AND，| 表示 OR，! 表示 NOT，<-> 表示 FOLLOWED BY）。
• to_tsvector(): 函数，用于将文本数据转换为 tsvector。
• to_tsquery() / plainto_tsquery() / phraseto_tsquery(): 函数，用于将查询字符串转换为 tsquery。
• @@ 操作符: 用于比较 tsvector 和 tsquery，判断文档是否匹配查询。
• ts_rank() / ts_rank_cd(): 函数，用于计算文档与查询的相关性得分。
• GiST 和 GIN 索引: 特殊的索引类型，可以高效地索引 tsvector 数据，加速全文搜索。
• 文本搜索配置 (Text Search Configuration): 定义了如何解析文本（分词器）、规范化词位（词典）以及处理不同语言的规则。

应用使用场景

高级全文搜索在以下应用场景中非常有用：

• 博客、新闻网站搜索： 用户可以输入关键词或短语，快速找到相关的文章。需要支持模糊匹配、相关性排序等。
• 电子商务平台商品搜索： 用户可以通过商品名称、描述等进行搜索，需要处理同义词、变体、属性等。
• 文档管理系统： 用户可以搜索文档内容，需要支持复杂的布尔查询、短语搜索、高亮显示等。
• 知识库、帮助中心： 用户可以搜索问题的答案或相关文档，需要根据相关性进行排序。
• 代码搜索： 在代码仓库中搜索特定的函数名、变量名或代码片段。
• 日志分析： 在大量日志数据中搜索包含特定关键词的记录。

不同场景下详细代码实现

以下是在不同场景下使用 PostgreSQL 高级全文搜索功能的代码示例。

场景 1：基本的关键词搜索和相关性排序

假设有一个 articles 表，包含 id 和 content 字段。

1. 创建表并插入数据：

   CREATE TABLE articles (
       id SERIAL PRIMARY KEY,
       content TEXT
   );
   
   INSERT INTO articles (content) VALUES
   ('PostgreSQL is a powerful open-source relational database system.'),
   ('Full-text search in PostgreSQL allows for efficient text retrieval.'),
   ('Advanced features of PostgreSQL include indexing and query optimization.'),
   ('This article discusses PostgreSQL full-text search capabilities.');
   

2. 创建 tsvector 列和 GIN 索引：

   ALTER TABLE articles ADD COLUMN content_tsvector tsvector;
   UPDATE articles SET content_tsvector = to_tsvector('english', content);
   CREATE INDEX content_gin_idx ON articles USING GIN (content_tsvector);
   

3. 执行关键词搜索并按相关性排序：

   SELECT id, content, ts_rank_cd(content_tsvector, plainto_tsquery('english', 'full-text search')) AS rank
   FROM articles
   WHERE content_tsvector @@ plainto_tsquery('english', 'full-text search')
   ORDER BY rank DESC;
   

场景 2：使用不同的查询解析函数和操作符

1. 短语搜索 (phraseto_tsquery)：

   SELECT id, content
   FROM articles
   WHERE content_tsvector @@ phraseto_tsquery('english', 'PostgreSQL full-text');
   

2. 布尔搜索 (to_tsquery)：

   SELECT id, content
   FROM articles
   WHERE content_tsvector @@ to_tsquery('english', 'PostgreSQL & search & (efficient | powerful)');
   

3. 前缀搜索 (:*)：

   SELECT id, content
   FROM articles
   WHERE content_tsvector @@ to_tsquery('english', 'power:*');
   

场景 3：使用文本搜索配置处理不同语言和自定义词典

1. 查看可用的文本搜索配置：

   SELECT cfgname FROM pg_ts_config;
   

2. 使用中文文本搜索配置（需要安装 zhparser 扩展）：

   -- 假设已经安装了 zhparser 扩展并创建了 zhparser 配置
   ALTER TABLE articles ADD COLUMN content_zh_tsvector tsvector;
   UPDATE articles SET content_zh_tsvector = to_tsvector('zhparser', content);
   CREATE INDEX content_zh_gin_idx ON articles USING GIN (content_zh_tsvector);
   
   SELECT id, content, ts_rank_cd(content_zh_tsvector, to_tsquery('zhparser', '强大的 开源 数据库')) AS rank
   FROM articles
   WHERE content_zh_tsvector @@ to_tsquery('zhparser', '强大的 & 开源 & 数据库')
   ORDER BY rank DESC;
   

3. 创建自定义词典（例如，处理特定领域的术语）：

   -- 创建一个简单的停用词词典
   CREATE TEXT SEARCH DICTIONARY my_stopword_dict (
       template = stopword,
       stopwords = 'english, is, a, the'
   );
   
   -- 创建一个新的文本搜索配置，使用自定义词典
   CREATE TEXT SEARCH CONFIGURATION my_english (COPY = english);
   ALTER TEXT SEARCH CONFIGURATION my_english ALTER MAPPING FOR asciiword, asciihword, hword_asciipart, hword, hword_part, word WITH my_stopword_dict, english_stem;
   
   ALTER TABLE articles ADD COLUMN content_custom_tsvector tsvector;
   UPDATE articles SET content_custom_tsvector = to_tsvector('my_english', content);
   CREATE INDEX content_custom_gin_idx ON articles USING GIN (content_custom_tsvector);
   
   SELECT id, content
   FROM articles
   WHERE content_custom_tsvector @@ to_tsquery('my_english', 'powerful database');
   

原理解释

1. tsvector 的创建和索引： to_tsvector() 函数将输入的文本根据指定的文本搜索配置（例如 'english'）进行分词（tokenization）、词位规范化（lexical normalization，如转换为小写、去除停用词、词干提取）。生成的 tsvector 实际上是一个排序后的唯一词位列表，并记录了每个词位在原始文档中的位置信息，这有助于进行短语搜索和相关性计算。GIN 索引是一种倒排索引，非常适合索引 tsvector 类型的数据，它能够快速找到包含特定词位的文档。

2. tsquery 的创建和匹配： to_tsquery(), plainto_tsquery(), phraseto_tsquery() 等函数将用户输入的查询字符串转换为 tsquery。to_tsquery() 允许使用布尔操作符 (&, |, !) 和前缀匹配 (:*) 构建复杂的查询。plainto_tsquery() 将输入视为一组关键词，并默认使用 & 连接。phraseto_tsquery() 将输入视为一个短语，只有当文档中包含该完整短语时才匹配。@@ 操作符比较 tsvector 和 tsquery，如果 tsvector 包含 tsquery 中指定的所有词位（并满足操作符的条件），则返回 true。