开发者日记

外观

从 2 秒到 80 毫秒：PG 中文全文检索调优实录 ​

问题背景：站内搜索为什么不建议只靠 LIKE ​

很多业务的第一版搜索都从 LIKE '%关键词%' 开始。数据量小的时候它足够简单，但随着文章、商品、工单标题增长，问题会迅速暴露：前后通配无法使用普通 B-Tree 索引，无法理解“数据库/PostgreSQL/PG”之间的词项关系，也不能根据标题、正文、标签做权重排序。

PostgreSQL 内置全文检索能力由 tsvector、tsquery、GIN 索引和排名函数组成。英文场景开箱即用，中文场景则需要解决分词问题。本文以一个站内文章搜索为背景，给出从插件安装、数据建模、索引设计到 tsquery 调优的完整落地方案。

方案对比：pg_jieba、zhparser 与简单字符切分 ​

中文全文检索常见有三类方案：

  1. 简单字符切分：把中文拆成单字或 bigram，兼容性最好，但召回噪声大，索引体积也偏大。
  2. pg_jieba：基于结巴分词，接入成本低，适合多数常规业务。
  3. zhparser：基于 SCWS，PostgreSQL 生态使用广泛，支持自定义词典，适合需要长期维护词库的站内搜索。

下面以 zhparser 为例。安装扩展后需要创建中文文本搜索配置，并将其映射到 zhparser 解析器。

sql
-- 注意：扩展需要数据库服务器已安装 zhparser 软件包
CREATE EXTENSION IF NOT EXISTS zhparser;

-- 创建中文全文检索配置
CREATE TEXT SEARCH CONFIGURATION chinese_zh (PARSER = zhparser);

-- noun、verb、adj 等 token 类型参与索引；可按业务调整
ALTER TEXT SEARCH CONFIGURATION chinese_zh
ADD MAPPING FOR n,v,a,i,e,l,j
WITH simple;

-- 验证分词效果
SELECT token, lexemes
FROM ts_debug('chinese_zh', 'PostgreSQL 中文全文检索与 tsquery 调优');

如果暂时无法安装系统扩展，可以先用 trigram 作为过渡方案：pg_trgm 适合模糊匹配和子串搜索，但它不是语义分词，适合作为兜底而不是主搜索方案。

检索架构：写入、索引与查询链路 ​

文章标题/正文业务表写入to_tsvectorchinese_zh 分词生成列 + GIN维护倒排索引plainto_tsquerywebsearch 语法@@ 匹配ts_rank 排序

核心思路是不要在查询时临时对全表执行 to_tsvector，而是在写入或生成列中固化 tsvector，再建立 GIN 索引。查询侧把用户输入转换为安全的 tsquery，最后用 @@ 做匹配、ts_rank 或业务分值排序。

实操步骤：文章表、生成列与 GIN 索引 ​

先建立文章表，并增加一个带权重的全文检索生成列。标题通常比正文更重要，因此给标题设置 A 权重，正文设置 B 权重。

sql
DROP TABLE IF EXISTS articles;

CREATE TABLE articles (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  body text NOT NULL,
  tags text[] NOT NULL DEFAULT '{}',
  status text NOT NULL DEFAULT 'published',
  published_at timestamptz NOT NULL DEFAULT now(),
  search_vector tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('chinese_zh', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('chinese_zh', coalesce(body, '')), 'B') ||
    setweight(to_tsvector('simple', array_to_string(tags, ' ')), 'C')
  ) STORED
);

-- GIN 是全文检索最常用索引；写入频繁时可评估 gin_pending_list_limit
CREATE INDEX articles_search_vector_idx
ON articles
USING GIN (search_vector);

-- 搜索列表通常还要按状态过滤、按发布时间倒序
CREATE INDEX articles_published_time_idx
ON articles (published_at DESC)
WHERE status = 'published';

插入一批测试数据：

sql
INSERT INTO articles (title, body, tags) VALUES
('PostgreSQL 全文检索实战', '介绍中文分词、tsquery、GIN 索引和执行计划优化。', ARRAY['postgresql','搜索']),
('MySQL 与 PG 怎么选', '从事务、索引、JSON、全文检索和运维成本对比数据库。', ARRAY['mysql','选型']),
('一次慢查询治理记录', '通过 EXPLAIN ANALYZE 发现 Bitmap Heap Scan 耗时过高。', ARRAY['性能','explain']),
('站内搜索召回排序指南', '讲解关键词高亮、权重、同义词词典和短语匹配。', ARRAY['搜索','排序']);

tsquery 用法：从安全输入到高级布尔查询 ​

普通搜索框不要直接拼接 to_tsquery，因为 to_tsquery 要求用户输入符合严格语法，单独输入 &、! 或未闭合括号都可能报错。更安全的方式是使用 plainto_tsquery 或 websearch_to_tsquery。

sql
-- 普通关键词：自动按 AND 处理分词结果
SELECT id, title
FROM articles
WHERE status = 'published'
  AND search_vector @@ plainto_tsquery('chinese_zh', '中文 分词 索引')
ORDER BY ts_rank(search_vector, plainto_tsquery('chinese_zh', '中文 分词 索引')) DESC,
         published_at DESC
LIMIT 20;

-- 推荐：支持站内搜索常见语法，且对异常输入更宽容
-- “PostgreSQL -MySQL” 表示要求 PG，排除 MySQL
SELECT id, title,
       ts_rank(search_vector, websearch) AS rank_score
FROM articles,
LATERAL websearch_to_tsquery('chinese_zh', 'PostgreSQL -MySQL') AS websearch
WHERE status = 'published'
  AND search_vector @@ websearch
ORDER BY rank_score DESC, published_at DESC
LIMIT 20;

如果希望“全文检索 索引”作为连续短语出现，可以使用 phraseto_tsquery。它会生成带距离约束的 tsquery，比单纯要求两个词同时出现更精确。

sql
SELECT id, title
FROM articles
WHERE search_vector @@ phraseto_tsquery('chinese_zh', '全文 检索')
ORDER BY published_at DESC;

需要同义词时，可以维护一个业务词典表，并在应用层把查询词扩展为 (PG OR PostgreSQL)。但不要无节制扩展同义词，否则 tsquery 变宽后会大幅增加候选集合。

性能调优：用 EXPLAIN 验证索引是否命中 ​

判断 SQL 是否走 GIN 索引，不能只看“结果正确”，必须查看执行计划。典型高效计划是 Bitmap Index Scan 配合 Bitmap Heap Scan，当结果集很小时也可能出现普通索引扫描。

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title
FROM articles
WHERE status = 'published'
  AND search_vector @@ websearch_to_tsquery('chinese_zh', '全文 检索')
ORDER BY published_at DESC
LIMIT 20;

如果看到 Seq Scan，并且 Filter 中对 search_vector 或 to_tsvector 做计算，通常是以下原因：表太小、统计信息过旧、查询条件无法使用索引，或在索引列上包了函数。

更新统计信息并观察 GIN 索引内部状态：

sql
ANALYZE articles;

-- 查看索引膨胀与体积，需要 pgstattuple 扩展
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstatindex('articles_search_vector_idx');

对于高频写入表，GIN 索引的 pending list 太大可能造成查询变慢。可以在会话或库级别调整：

sql
-- pending list 达到阈值后由 autovacuum 批量合并
ALTER TABLE articles SET (gin_pending_list_limit = 2048);

-- 大批量导入后手动清理分析
VACUUM (ANALYZE) articles;

当过滤条件同时包含状态、时间和全文条件时，不要盲目追求一个复合索引解决所有问题。更实用的做法是先用 GIN 缩小候选集，再通过 status、published_at 做过滤和排序；若热点查询固定，再评估带条件的 GIN 索引。

sql
-- 只索引已发布内容，减小索引体积
CREATE INDEX articles_published_search_idx
ON articles USING GIN (search_vector)
WHERE status = 'published';

避坑总结：中文全文检索的高频问题 ​

1. 查询很快但总数很慢 ​

常见现象是第一页搜索 80 毫秒，执行 COUNT(*) 却要 2 秒。原因是总数必须确认所有匹配行，而 LIMIT 只需要拿到前 20 条。修正方法是限制可翻页深度，用游标分页替代深分页；总数可以异步估算，或对超过阈值的结果显示“1000+”。

2. 中文词明明存在却搜不到 ​

典型原因是索引和查询使用了不同的文本搜索配置，例如索引用 simple，查询用 chinese_zh，或者新增专有词后没有重建生成列。修正方法是统一配置名，并在更新词典后执行 VACUUM FULL 或重建生成列索引。

sql
REINDEX INDEX articles_search_vector_idx;
ANALYZE articles;

3. 直接拼接用户输入导致 tsquery 报错 ​

错误现象是输入括号、引号、&、! 后出现 tsquery 语法错误。修正方法是对外网搜索框统一使用 websearch_to_tsquery，只有后台高级检索才允许白名单方式拼接布尔操作符，并对原始输入做长度限制。

4. GIN 索引存在但执行 Seq Scan ​

先不要怀疑优化器。检查表数据量是否小到顺序扫描更便宜，search_vector 是否由函数临时生成，分词配置是否导致查询词无法命中，以及统计信息是否过旧。使用 EXPLAIN (ANALYZE, BUFFERS) 对比命中行数，执行 ANALYZE 后再判断。

5. 只按 ts_rank 排序不符合业务预期 ​

ts_rank 只反映词频、密度和权重，不考虑发布时间、点击率、是否下架。站内搜索更推荐组合分值：标题命中给高权重，近期内容加权，低质或过期内容降权。不要把数据库全文检索当成完整搜索引擎，但在百万级数据、查询条件明确、运维资源有限的场景下，PostgreSQL + 中文分词 + GIN + tsquery 往往是性价比很高的落地方案。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。