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 内置的分词器对中文支持有限，需要使用扩展来实现中文分词。以下介绍几种常用的中文分词方案。

方案对比

方案	优点	缺点	适用场景
pg_jieba	分词效果好，支持多种模式	需要编译安装	生产环境，对分词质量要求高
zhparser	SCWS 分词，安装简单	分词粒度较粗	中小项目，快速部署
simple 配置	无需安装扩展	按字符分词，效果差	测试环境

安装 pg_jieba（推荐）

pg_jieba 是基于结巴分词的 PostgreSQL 扩展，分词效果好，支持精确、全模式、搜索引擎三种分词模式。

Ubuntu/Debian 安装：

# 安装依赖
sudo apt-get update
sudo apt-get install -y build-essential postgresql-server-dev-18 cmake git

# 下载并编译 pg_jieba
git clone https://github.com/jaiminpan/pg_jieba.git
cd pg_jieba
git submodule update --init --recursive

# 编译安装
mkdir build && cd build
cmake ..
make
sudo make install

CentOS/RHEL 安装：

# 安装依赖
sudo yum install -y gcc-c++ cmake postgresql18-devel git

# 下载并编译（同上）
git clone https://github.com/jaiminpan/pg_jieba.git
cd pg_jieba
git submodule update --init --recursive
mkdir build && cd build
cmake ..
make
sudo make install

Docker 环境：

# 在 Dockerfile 中添加
RUN apt-get update && apt-get install -y \
    build-essential \
    postgresql-server-dev-18 \
    cmake \
    git \
    && git clone https://github.com/jaiminpan/pg_jieba.git \
    && cd pg_jieba \
    && git submodule update --init --recursive \
    && mkdir build && cd build \
    && cmake .. && make && make install \
    && cd ../.. && rm -rf 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

-- 使用不同分词模式
-- 精确模式（默认）
SELECT jieba('PostgreSQL是一个数据库');

-- 全模式
SELECT jieba('PostgreSQL是一个数据库', 1);

-- 搜索引擎模式
SELECT jieba('PostgreSQL是一个数据库', 2);

安装 zhparser

zhparser 基于 SCWS 分词器，安装相对简单。

Ubuntu/Debian 安装：

# 安装依赖
sudo apt-get install -y postgresql-server-dev-18 scws libscws-dev

# 下载 zhparser
wget https://github.com/amutu/zhparser/archive/master.zip
unzip master.zip
cd zhparser-master

# 编译安装
SCWS_HOME=/usr make
sudo make install

配置 zhparser：

-- 创建扩展
CREATE EXTENSION zhparser;

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

-- 测试分词
SELECT to_tsvector('chinese_zh', 'PostgreSQL是一个功能强大的开源数据库');

中文全文搜索完整示例

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

-- 创建全文索引
CREATE INDEX idx_articles_fts ON articles 
USING GIN (to_tsvector('chinese_jieba', title || ' ' || content));

-- 插入测试数据
INSERT INTO articles (title, content) VALUES
('PostgreSQL 教程', 'PostgreSQL 是一个功能强大的开源关系型数据库，支持 ACID 事务和复杂查询。'),
('数据库优化', '数据库性能优化包括索引优化、查询优化和配置优化。PostgreSQL 提供了丰富的优化工具。');

-- 执行全文搜索
SELECT 
    title,
    ts_headline('chinese_jieba', content, 
        to_tsquery('chinese_jieba', 'PostgreSQL'),
        'StartSel=<mark>, StopSel=</mark>') AS highlight,
    ts_rank(to_tsvector('chinese_jieba', content), 
        to_tsquery('chinese_jieba', 'PostgreSQL')) AS rank
FROM articles
WHERE to_tsvector('chinese_jieba', title || ' ' || content) @@ 
      to_tsquery('chinese_jieba', 'PostgreSQL')
ORDER BY rank DESC;

-- 创建自动更新全文索引的触发器
ALTER TABLE articles ADD COLUMN fts tsvector;

CREATE OR REPLACE FUNCTION articles_fts_trigger()
RETURNS TRIGGER AS $
BEGIN
    NEW.fts := to_tsvector('chinese_jieba', COALESCE(NEW.title, '') || ' ' || COALESCE(NEW.content, ''));
    RETURN NEW;
END;
$ LANGUAGE plpgsql;

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

-- 更新现有数据
UPDATE articles SET fts = to_tsvector('chinese_jieba', COALESCE(title, '') || ' ' || COALESCE(content, ''));

-- 使用存储的 fts 列进行搜索（更快）
SELECT title, ts_rank(fts, to_tsquery('chinese_jieba', '数据库')) AS rank
FROM articles
WHERE fts @@ to_tsquery('chinese_jieba', '数据库')
ORDER BY rank DESC;

中文搜索最佳实践

-- 1. 选择合适的分词配置
-- 对精确度要求高：使用 pg_jieba
-- 对性能要求高：使用 zhparser

-- 2. 配置索引
CREATE INDEX idx_articles_fts ON articles USING GIN (fts);

-- 3. 处理混合中英文内容
SELECT to_tsvector('chinese_jieba', 'PostgreSQL 数据库 database');

-- 4. 使用 websearch_to_tsquery 方便用户输入
SELECT * FROM articles
WHERE fts @@ websearch_to_tsquery('chinese_jieba', 'PostgreSQL 教程');

-- 5. 自定义词典（pg_jieba）
-- 编辑词典文件：/usr/share/postgresql/18/tsearch_data/jieba.dict
-- 格式：词语 词频 词性

简单中文处理

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

-- 方法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 扩展