PostgreSQL 全文搜索

PostgreSQL 内置强大的全文搜索（Full-Text Search）功能，无需额外安装搜索引擎即可实现复杂的文本搜索需求。本章将详细介绍全文搜索的使用方法。

全文搜索概述

什么是全文搜索？

全文搜索是一种在文档集合中搜索文本的技术，它比简单的 LIKE 查询更强大：

LIKE vs 全文搜索

-- LIKE 查询：简单但效率低
SELECT * FROM articles WHERE content LIKE '%PostgreSQL%';
-- 问题：
-- 1. 无法利用索引（前置通配符）
-- 2. 无法处理词形变化（run/running/ran）
-- 3. 无法计算相关性

-- 全文搜索：高效且功能强大
SELECT * FROM articles 
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'PostgreSQL');
-- 优势：
-- 1. 使用 GIN 索引加速
-- 2. 自动词干提取
-- 3. 支持相关性排序

基本概念

tsvector（文本向量）

tsvector 是经过处理的文本，包含词元（lexeme）及其位置信息：

-- 将文本转换为 tsvector
SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');

-- 结果：'brown':3 'dog':8 'fox':4 'jump':5 'lazi':7 'quick':2
-- 说明：
-- - 停用词（the, over）被移除
-- - 词干提取：jumps -> jump, lazy -> lazi
-- - 数字表示词在原文中的位置

tsquery（文本查询）

tsquery 是搜索查询，包含要搜索的词元：

-- 简单查询
SELECT to_tsquery('english', 'fox');

-- 多词查询（AND）
SELECT to_tsquery('english', 'fox & dog');

-- 多词查询（OR）
SELECT to_tsquery('english', 'fox | cat');

-- 排除词（NOT）
SELECT to_tsquery('english', 'fox & !dog');

-- 短语查询
SELECT phraseto_tsquery('english', 'quick brown fox');

-- Web 风格查询
SELECT websearch_to_tsquery('english', 'fox OR dog -cat');

匹配操作符

-- @@ 操作符：检查 tsvector 是否匹配 tsquery
SELECT to_tsvector('english', 'The quick brown fox') @@ to_tsquery('english', 'fox');
-- 结果：true

SELECT to_tsvector('english', 'The quick brown fox') @@ to_tsquery('english', 'cat');
-- 结果：false

基本使用

创建测试数据

-- 创建文章表
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    author VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 插入测试数据
INSERT INTO articles (title, content, author) VALUES
('PostgreSQL 入门', 'PostgreSQL 是一个功能强大的开源关系型数据库，支持 ACID 事务和复杂查询。', '张三'),
('PostgreSQL 高级特性', 'PostgreSQL 支持全文搜索、JSON 数据类型、地理空间数据等高级特性。', '李四'),
('数据库优化指南', '数据库性能优化包括索引优化、查询优化和配置优化等多个方面。PostgreSQL 提供了丰富的工具。', '王五'),
('SQL 查询技巧', '学习 SQL 查询技巧对于数据库开发非常重要，包括连接查询、子查询和聚合函数等。', '张三'),
('NoSQL 数据库对比', '与 MongoDB 等 NoSQL 数据库相比，PostgreSQL 提供了更好的事务支持和数据一致性。', '赵六');

简单全文搜索

-- 基本全文搜索
SELECT title, content
FROM articles
WHERE to_tsvector('chinese', content) @@ to_tsquery('chinese', 'PostgreSQL');

-- 同时搜索标题和内容
SELECT title, content
FROM articles
WHERE to_tsvector('chinese', title || ' ' || content) @@ to_tsquery('chinese', 'PostgreSQL');

-- 多词搜索（AND）
SELECT title
FROM articles
WHERE to_tsvector('chinese', content) @@ to_tsquery('chinese', 'PostgreSQL & 优化');

-- 多词搜索（OR）
SELECT title
FROM articles
WHERE to_tsvector('chinese', content) @@ to_tsquery('chinese', 'PostgreSQL | MongoDB');

中文全文搜索

PostgreSQL 内置的分词器对中文支持有限，推荐使用 zhparser 扩展或 pg_jieba 扩展。

使用 pg_jieba

-- 安装 pg_jieba（需要预先安装扩展）
CREATE EXTENSION pg_jieba;

-- 配置中文分词
CREATE TEXT SEARCH CONFIGURATION chinese_jieba (PARSER = jieba);
ALTER TEXT SEARCH CONFIGURATION chinese_jieba ADD MAPPING FOR n,v,a,i,e,l WITH simple;

-- 使用中文分词
SELECT to_tsvector('chinese_jieba', 'PostgreSQL 是一个功能强大的开源数据库');
-- 结果：'postgresql':1 '功能':4 '强大':5 '开源':6 '数据库':7

简单中文处理

如果没有安装中文分词扩展，可以使用简单方法：

-- 方法1：使用 simple 配置（按字符分词）
SELECT to_tsvector('simple', 'PostgreSQL 数据库');

-- 方法2：使用 zhparser（推荐）
-- 需要先安装 zhparser 扩展

-- 方法3：自定义分词函数
CREATE OR REPLACE FUNCTION chinese_to_tsvector(text)
RETURNS tsvector AS $$
BEGIN
    RETURN to_tsvector('simple', regexp_replace($1, '[\u4e00-\u9fff]', ' & ', 'g'));
END;
$$ LANGUAGE plpgsql;

创建全文索引

GIN 索引

GIN（Generalized Inverted Index）是最常用的全文搜索索引：

-- 创建 GIN 索引
CREATE INDEX idx_articles_content_fts ON articles 
USING GIN (to_tsvector('chinese', content));

-- 多列 GIN 索引
CREATE INDEX idx_articles_title_content_fts ON articles 
USING GIN (to_tsvector('chinese', title || ' ' || content));

-- 使用函数索引
ALTER TABLE articles ADD COLUMN content_tsv tsvector;

UPDATE articles SET content_tsv = to_tsvector('chinese', content);

CREATE INDEX idx_articles_content_tsv ON articles USING GIN (content_tsv);

-- 使用触发器自动更新
CREATE OR REPLACE FUNCTION update_content_tsv()
RETURNS TRIGGER AS $$
BEGIN
    NEW.content_tsv = to_tsvector('chinese', NEW.content);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_content_tsv
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION update_content_tsv();

GiST 索引

GiST 索引占用空间更小，但查询速度较慢：

-- 创建 GiST 索引
CREATE INDEX idx_articles_content_gist ON articles 
USING GIST (to_tsvector('chinese', content));

索引选择

索引类型	构建速度	查询速度	存储空间	适用场景
GIN	慢	快	大	读多写少
GiST	快	慢	小	写多读少

高级搜索功能

相关性排序

使用 ts_rank 函数计算搜索结果的相关性：

-- 计算相关性并排序
SELECT 
    title,
    ts_rank(to_tsvector('chinese', content), to_tsquery('chinese', 'PostgreSQL')) AS rank
FROM articles
WHERE to_tsvector('chinese', content) @@ to_tsquery('chinese', 'PostgreSQL')
ORDER BY rank DESC;

-- 归一化相关性（考虑文档长度）
SELECT 
    title,
    ts_rank_cd(to_tsvector('chinese', content), to_tsquery('chinese', 'PostgreSQL'), 32) AS rank
FROM articles
WHERE to_tsvector('chinese', content) @@ to_tsquery('chinese', 'PostgreSQL')
ORDER BY rank DESC;

高亮显示

使用 ts_headline 函数高亮显示匹配内容：

-- 高亮显示搜索结果
SELECT 
    title,
    ts_headline('chinese', content, to_tsquery('chinese', 'PostgreSQL'),
        'StartSel=<mark>, StopSel=</mark>, MaxWords=50, MinWords=20') AS highlight
FROM articles
WHERE to_tsvector('chinese', content) @@ to_tsquery('chinese', 'PostgreSQL');

搜索建议

-- 使用 pg_trgm 扩展实现搜索建议
CREATE EXTENSION pg_trgm;

-- 创建 trigram 索引
CREATE INDEX idx_articles_title_trgm ON articles USING GIN (title gin_trgm_ops);

-- 模糊搜索
SELECT title FROM articles 
WHERE title % 'Postgre'  -- 相似度匹配
ORDER BY similarity(title, 'Postgre') DESC;

-- 前缀搜索
SELECT DISTINCT word FROM ts_stat('SELECT to_tsvector(''chinese'', content) FROM articles')
WHERE word LIKE '数据%'
ORDER BY word;

搜索词统计

-- 统计文档中的高频词
SELECT word, ndoc, nentry
FROM ts_stat('SELECT to_tsvector(''chinese'', content) FROM articles')
ORDER BY ndoc DESC
LIMIT 10;

-- word: 词元
-- ndoc: 包含该词的文档数
-- nentry: 该词出现的总次数

复杂查询

组合查询

-- 搜索包含 PostgreSQL 或 MySQL，但不包含 MongoDB
SELECT title
FROM articles
WHERE to_tsvector('chinese', content) @@ to_tsquery('chinese', '(PostgreSQL | MySQL) & !MongoDB');

-- 搜索短语
SELECT title
FROM articles
WHERE to_tsvector('chinese', content) @@ phraseto_tsquery('chinese', '开源数据库');

-- 搜索相邻词
SELECT title
FROM articles
WHERE to_tsvector('chinese', content) @@ to_tsquery('chinese', 'PostgreSQL <2> 优化');
-- 匹配：PostgreSQL ... ... 优化（间隔不超过2个词）

加权搜索

可以为不同字段设置不同权重：

-- 设置权重：A(标题) > B(内容) > C(标签)
SELECT 
    title,
    ts_rank(
        setweight(to_tsvector('chinese', title), 'A') || 
        setweight(to_tsvector('chinese', content), 'B'),
        to_tsquery('chinese', 'PostgreSQL')
    ) AS rank
FROM articles
WHERE setweight(to_tsvector('chinese', title), 'A') || 
      setweight(to_tsvector('chinese', content), 'B') 
      @@ to_tsquery('chinese', 'PostgreSQL')
ORDER BY rank DESC;

多表搜索

-- 搜索多个表
SELECT 'article' AS type, id, title, 
       ts_rank(to_tsvector('chinese', title || ' ' || content), query) AS rank
FROM articles, to_tsquery('chinese', 'PostgreSQL') query
WHERE to_tsvector('chinese', title || ' ' || content) @@ query

UNION ALL

SELECT 'comment' AS type, id, content AS title,
       ts_rank(to_tsvector('chinese', content), query) AS rank
FROM comments, to_tsquery('chinese', 'PostgreSQL') query
WHERE to_tsvector('chinese', content) @@ query

ORDER BY rank DESC;

性能优化

索引优化

-- 1. 使用存储的 tsvector 列
ALTER TABLE articles ADD COLUMN fts tsvector;

CREATE INDEX idx_articles_fts ON articles USING GIN (fts);

-- 更新 fts 列
UPDATE articles SET fts = to_tsvector('chinese', title || ' ' || content);

-- 2. 使用触发器自动更新
CREATE TRIGGER articles_fts_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION
    tsvector_update_trigger(fts, 'pg_catalog.chinese', title, content);

-- 3. 使用表达式索引
CREATE INDEX idx_articles_fts_expr ON articles 
USING GIN (to_tsvector('chinese', title || ' ' || content));

查询优化

-- 1. 使用预处理查询
-- 慢：每次都转换
SELECT * FROM articles 
WHERE to_tsvector('chinese', content) @@ to_tsquery('chinese', 'PostgreSQL');

-- 快：使用存储的 tsvector
SELECT * FROM articles 
WHERE fts @@ to_tsquery('chinese', 'PostgreSQL');

-- 2. 限制返回结果
SELECT * FROM articles 
WHERE fts @@ to_tsquery('chinese', 'PostgreSQL')
ORDER BY ts_rank(fts, to_tsquery('chinese', 'PostgreSQL')) DESC
LIMIT 20;

-- 3. 使用参数化查询（应用层）
PREPARE search_query(text) AS
SELECT * FROM articles WHERE fts @@ to_tsquery('chinese', $1);
EXECUTE search_query('PostgreSQL');

实用示例：搜索函数

-- 创建通用搜索函数
CREATE OR REPLACE FUNCTION search_articles(
    search_text TEXT,
    limit_count INTEGER DEFAULT 20
)
RETURNS TABLE (
    id INTEGER,
    title VARCHAR,
    content TEXT,
    rank REAL,
    highlight TEXT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        a.id,
        a.title,
        a.content,
        ts_rank(a.fts, query) AS rank,
        ts_headline('chinese', a.content, query,
            'StartSel=<mark>, StopSel=</mark>, MaxWords=60, MinWords=30') AS highlight
    FROM articles a,
         websearch_to_tsquery('chinese', search_text) query
    WHERE a.fts @@ query
    ORDER BY rank DESC
    LIMIT limit_count;
END;
$$ LANGUAGE plpgsql;

-- 使用搜索函数
SELECT * FROM search_articles('PostgreSQL 优化', 10);

小结

本章我们学习了 PostgreSQL 全文搜索：

1. 基本概念：tsvector、tsquery、@@ 操作符
2. 全文搜索：使用 to_tsvector 和 to_tsquery
3. 中文支持：pg_jieba、zhparser 扩展
4. 创建索引：GIN 索引、GiST 索引
5. 高级功能：相关性排序、高亮显示、搜索建议
6. 复杂查询：组合查询、加权搜索、多表搜索
7. 性能优化：存储 tsvector、触发器更新

练习

1. 创建一个支持全文搜索的文章表
2. 实现带高亮和分页的搜索功能
3. 设计加权搜索，标题权重高于内容
4. 实现搜索建议功能

参考资源

• PostgreSQL 全文搜索官方文档
• PostgreSQL 中文分词 zhparser
• PostgreSQL pg_jieba 扩展