xingliuhuapgsql-14 全文搜索
1. 概述
全文搜索（Full Text Search, FTS）解决的是"在大量文本中按词检索并按相关性排序"的问题。普通的 LIKE '%关键词%' 有三大缺陷：
- 无法用索引（前置通配符导致索引失效），大表极慢；
- 不理解语言：不会词干还原（searching / searched / search 视为不同），不处理停用词；
- 不能排序相关性：无法区分"标题命中"和"正文偶然命中"。
PostgreSQL 内置了一套完整的全文搜索引擎，核心是两个类型：tsvector（文档的分词结果）和 tsquery（查询表达式），配合 GIN 索引，可在不引入 Elasticsearch 的情况下满足绝大多数站内搜索需求。
对比 MySQL：MySQL 的
FULLTEXT索引（InnoDB 5.6+）功能相对基础，中文需要ngram解析器（按 N 元切分，不是真正分词），相关性算法（BM25/TF-IDF）和可定制性都弱于 PostgreSQL。PG 的优势在于可插拔的分词器、词典、多语言配置和丰富的排名函数。
2. 核心概念：tsvector 与 tsquery
2.1 tsvector —— 文档的"分词 + 位置"表示
tsvector 把一段文本处理成"词素（lexeme）+ 位置"的有序集合。处理过程包括：分词 → 转小写 → 去停用词 → 词干还原（归一化）。
-- to_tsvector：把文本转成 tsvector（第一个参数是语言配置）
SELECT to_tsvector('english', 'The quick brown foxes are jumping');
-- 结果: 'brown':3 'fox':4 'jump':6 'quick':2
-- 注意：the/are 是停用词被去掉；foxes→fox、jumping→jump 做了词干还原；数字是词在原文的位置
2.2 tsquery —— 查询表达式
tsquery 表示搜索条件，支持布尔运算：
SELECT to_tsquery('english', 'quick & fox'); -- 与：同时包含
SELECT to_tsquery('english', 'quick | slow'); -- 或
SELECT to_tsquery('english', 'fox & !dog'); -- 非：包含 fox 但不含 dog
SELECT to_tsquery('english', 'quick <-> fox'); -- 相邻（短语，quick 紧跟 fox）
SELECT to_tsquery('english', 'quick <2> fox'); -- 距离为2的近邻
-- 面向用户输入的便捷函数（自动处理，无需手写 & |）
SELECT plainto_tsquery('english', 'quick fox'); -- 词之间默认 AND: 'quick' & 'fox'
SELECT phraseto_tsquery('english', 'quick fox'); -- 作为短语: 'quick' <-> 'fox'
SELECT websearch_to_tsquery('english', 'quick or "brown fox" -dog'); -- 支持类搜索引擎语法
2.3 匹配运算符 @@
-- @@ 判断 tsvector 是否匹配 tsquery
SELECT to_tsvector('english', 'The quick brown fox')
@@ to_tsquery('english', 'quick & fox'); -- true
3. 在表中使用全文搜索
3.1 三种存储/计算方式
CREATE TABLE articles (
id BIGSERIAL PRIMARY KEY,
title TEXT NOT NULL,
content TEXT NOT NULL
);
方式一：查询时实时计算（无需额外列，适合小表）
SELECT * FROM articles
WHERE to_tsvector('english', title || ' ' || content)
@@ plainto_tsquery('english', 'postgresql database');
方式二：生成列（PG 12+，推荐） —— 自动维护，无需触发器
ALTER TABLE articles ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(content, '')), 'B')
) STORED;
-- setweight 给不同字段设权重(A>B>C>D)，标题命中比正文命中更重要
方式三：普通列 + 触发器（PG 12 以前）
ALTER TABLE articles ADD COLUMN search_vector tsvector;
CREATE TRIGGER tsv_update BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(search_vector, 'pg_catalog.english', title, content);
3.2 建立索引：GIN vs GiST
-- GIN 索引（首选）：查询快，适合读多写少
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);
-- GiST 索引：更新快、体积小，但查询有损（可能误报），适合频繁更新
CREATE INDEX idx_articles_search_gist ON articles USING GiST (search_vector);
| 对比 | GIN | GiST |
|---|---|---|
| 查询速度 | 快 3 倍左右 | 较慢（可能需重查） |
| 构建/更新速度 | 慢 | 快 |
| 索引体积 | 大 | 小 |
| 适用 | 全文搜索默认选 GIN | 频繁更新、数据量小 |
3.3 查询
SELECT id, title
FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'postgresql & index')
LIMIT 20;
4. 相关性排名（Ranking）
搜索的关键不只是"匹配"，而是"最相关的排前面"。
SELECT id, title,
ts_rank(search_vector, query) AS rank -- 基于词频
FROM articles, plainto_tsquery('english', 'postgresql performance') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;
-- ts_rank_cd：考虑词的"密度/距离"，短语聚集的文档得分更高
SELECT id, title, ts_rank_cd(search_vector, query) AS rank
FROM articles, plainto_tsquery('english', 'query optimization') query
WHERE search_vector @@ query
ORDER BY rank DESC;
ts_rank：基于词频（TF）与权重（A/B/C/D）。ts_rank_cd：Cover Density，额外考虑匹配词之间的距离，更适合短语搜索。- 第三个参数可传归一化选项（如按文档长度归一化，避免长文档天然占优）。
5. 结果高亮
-- ts_headline 生成带高亮标记的摘要片段
SELECT ts_headline('english', content,
plainto_tsquery('english', 'postgresql'),
'StartSel=<b>, StopSel=</b>, MaxWords=35, MinWords=15') AS snippet
FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'postgresql')
LIMIT 5;
6. 中文全文搜索
英文靠空格分词，中文没有天然分隔，必须引入中文分词器。常用两种扩展：
| 扩展 | 底层 | 特点 |
|---|---|---|
| zhparser | SCWS 词库 | 老牌稳定，配置灵活 |
| pg_jieba | 结巴分词 | 词库更新活跃，支持自定义词典 |
6.1 zhparser 配置
-- 安装后创建配置
CREATE EXTENSION zhparser;
CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);
-- 映射词性到词素类型（n名词 v动词 a形容词 i成语 等）
ALTER TEXT SEARCH CONFIGURATION chinese
ADD MAPPING FOR n,v,a,i,e,l WITH simple;
-- 使用中文配置分词
SELECT to_tsvector('chinese', 'PostgreSQL 是强大的开源关系型数据库');
-- 结果类似: 'postgresql':1 '关系':5 '强大':3 '数据库':6 '开源':4
-- 中文搜索
SELECT title FROM articles
WHERE to_tsvector('chinese', title) @@ to_tsquery('chinese', '数据库 & 开源');
6.2 生成列使用中文配置
ALTER TABLE articles ADD COLUMN search_zh tsvector
GENERATED ALWAYS AS (to_tsvector('chinese', coalesce(title,'') || ' ' || coalesce(content,''))) STORED;
CREATE INDEX idx_articles_zh ON articles USING GIN (search_zh);
6.3 中文分词常见问题
- 未登录词：新词/专有名词切不准 → 给分词器加自定义词典。
- 歧义切分：“北京大学生"可能切成"北京/大学生"或"北京大学/生”，需词库权重调优。
- 简繁体：可先统一转换再入库。
7. 前缀/模糊搜索的补充方案：pg_trgm
全文搜索是"按词"匹配，不擅长"输入了错别字"或"任意子串"的模糊查询。此时用 pg_trgm（三元组相似度）扩展补充：
CREATE EXTENSION pg_trgm;
-- 相似度搜索（容错拼写错误）
SELECT title, similarity(title, 'postgres') AS sim
FROM articles
WHERE title % 'postgres' -- % 运算符：相似度超过阈值
ORDER BY sim DESC;
-- 让 LIKE '%xxx%' 也能走索引！
CREATE INDEX idx_articles_title_trgm ON articles USING GIN (title gin_trgm_ops);
SELECT * FROM articles WHERE title LIKE '%postg%'; -- 现在能用索引
pg_trgm是让LIKE '%...%'走索引的经典方案，也常用于"搜索框自动补全/纠错"。详见 扩展插件总览。
8. 什么时候该上 Elasticsearch？
PostgreSQL 全文搜索 vs Elasticsearch（面试常问选型）：
| 维度 | PG 全文搜索 | Elasticsearch |
|---|---|---|
| 架构复杂度 | 无需额外系统 | 独立集群，需数据同步 |
| 数据一致性 | 与业务数据强一致（同库事务） | 异步同步，存在延迟 |
| 中小规模站内搜索 | ✅ 足够 | 杀鸡用牛刀 |
| 海量文档 / 高并发搜索 | 有上限 | ✅ 更强 |
| 打分算法 | ts_rank（可用） | BM25（更成熟）、聚合分析强 |
| 运维成本 | 低 | 高 |
结论：数据量在千万级以内、搜索不是核心业务、想少维护一套系统 → PG 全文搜索完全够用；文档海量、搜索是核心卖点、需要复杂聚合/高亮/纠错 → 上 ES。
9. 面试高频问答
Q1：为什么 LIKE '%关键词%' 慢？全文搜索为什么快？
前置通配符使 B-tree 索引失效，只能全表扫描。全文搜索预先把文本分词成 tsvector 并建 GIN 倒排索引，检索时直接查倒排表，且支持词干还原与相关性排序。
Q2：tsvector 和 tsquery 是什么？
tsvector 是文档经分词/去停用词/词干还原后的"词素+位置"集合；tsquery 是带布尔逻辑的查询表达式；用 @@ 判断是否匹配。
Q3：GIN 和 GiST 索引怎么选？ 全文搜索默认 GIN（查询快、体积大、更新慢）；数据频繁更新且量小可用 GiST（更新快、有损查询）。
Q4：ts_rank 和 ts_rank_cd 的区别？ ts_rank 基于词频和字段权重；ts_rank_cd 额外考虑匹配词之间的距离密度，更适合短语相关性。
Q5：PG 全文搜索怎么处理中文？ 需引入 zhparser 或 pg_jieba 分词器，创建对应的文本搜索配置，把词性映射到词素类型后即可像英文一样使用。
Q6：什么场景该用 ES 而不是 PG 全文搜索？ 海量文档、搜索为核心业务、需要复杂聚合/高亮/纠错/高并发时用 ES；中小规模站内搜索优先用 PG，省一套系统且数据强一致。
10. 下一步
学习 PostGIS地理数据，掌握空间数据的存储与检索。