很多跑了多年的系统,搜索功能背后就是一条 LIKE '%关键词%':演示的时候够用,数据量和用户耐心上来之后,它开始慢、开始漏、开始返回一堆不相关的结果。把这样的存量搜索升级成语义搜索,不需要推倒重来,也不必一步换成向量数据库——存在一条每一步都能独立上线、独立回滚的渐进路径。
本文是站内"数据库 × AI"系列的收官篇,也可完全独立阅读。先给 LIKE 定罪,再给出全文索引、语义向量、混合检索的四级台阶,逐级落地到 schema 与查询改造的代码,随后是双写、灰度、回滚的迁移工程与验收指标,最后完整复盘一次工单系统的搜索升级。
1、LIKE 的三宗罪
设想一个工单系统,搜索框背后是下面这条 SQL:
-- 工单搜索的常见形态
SELECT id, subject, created_at
FROM ticket
WHERE subject LIKE '%登录%' OR content LIKE '%登录%'
ORDER BY created_at DESC
LIMIT 20;
它犯的第一宗罪是不相关。LIKE 只认字节重合,不知道哪个结果更相关:搜"退款",正文里随口一句"该单不涉及退款"的工单照样命中;结果按创建时间倒序返回,与相关度毫无关系。用户翻三页找不到目标工单——慢让人等待,不相关让人直接放弃。
第二宗罪是漏召回。用户说"磁盘满了",工程师写工单时用的是 "No space left on device";用户说"登不上去",工单标题写的是"认证失败"。词面零重合,LIKE 一条也查不到。同义改写、口语表达、中英混用全是它的盲区,而工单库恰恰是口语与术语混杂最严重的地方。
第三宗罪是慢。% 开头的模式让 B+ 树索引彻底失效,执行计划如下:
type: ALL
possible_keys: NULL
rows: 4120967
Extra: Using where
type: ALL 就是全表扫描,四百多万行的表每次搜索都从头扫到尾。更深的代价是这类扫描会不断挤占 buffer pool,拖慢的不只是搜索,还有整个库上的其他查询。
一句话定罪:LIKE 是字节匹配,不是检索系统——它不知道谁更相关,不知道两个词是一个意思,也不知道怎么不扫全表。这三宗罪正好对应后面几级台阶各自的用武之地。
2、升级路径四级台阶
改造的第一原则是渐进:每一级都独立可上线、独立可验收、独立可回滚,任何一级都可以是终点——不是所有系统都需要走到混合检索。四级台阶如下:
解读:每一级只解决上一级暴露的核心问题。全文索引治慢与无排序,语义向量治语义盲,混合检索把两路的长处拼在一起。各级的能力与代价对比如下:
| 台阶 | 解决什么 | 改动量 | 遗留短板 |
|---|---|---|---|
| LIKE | 无,起点 | 无 | 慢、漏、不准 |
| 全文索引 | 全表扫与无排序 | 库内加索引 | 语义盲仍在 |
| 语义向量 | 语义盲与同义盲 | 加列加模型 | 字面精确丢失 |
| 混合检索 | 两路互补 | 查询与融合层 | 维护两套链路 |
值得强调的是台阶之间的兼容性:全文索引与向量列加在同一张表上互不干扰,LIKE 查询也始终可用——这为后文的灰度与回滚提供了物理基础。多数中小系统走到台阶二已有质变;语料口语化、术语与型号混杂的场景,才需要继续上台阶三。
3、台阶一:全文索引
第一级不引任何新组件,在现有库内加倒排索引,是最小改动的一步。MySQL 8.0 自带 ngram 解析器,中文场景无需另装插件:
-- MySQL:加 ngram 全文索引(ngram_token_size 默认 2)
ALTER TABLE ticket
ADD FULLTEXT INDEX ft_search (subject, content) WITH PARSER ngram;
-- 自然语言模式检索,自动按相关度排序
SELECT id, subject,
MATCH (subject, content) AGAINST ('登录失败') AS score
FROM ticket
WHERE MATCH (subject, content) AGAINST ('登录失败')
LIMIT 20;
解读:MATCH ... AGAINST 走倒排索引,执行计划的 type 变为 fulltext,全表扫描消失;结果自动按 TF-IDF 类相关性降序,第一宗罪与第三宗罪同时了结。细节参考 MySQL ngram 全文解析器文档:短 token 受 innodb_ft_min_token_size(默认 3)限制,两位错误码这类查询需要调小该值。
PostgreSQL 的等价改造是生成列加 GIN 索引:
-- PostgreSQL:tsvector 生成列 + GIN 索引
ALTER TABLE ticket ADD COLUMN search_vec tsvector
GENERATED ALWAYS AS (
to_tsvector('simple', subject || ' ' || content)
) STORED;
CREATE INDEX idx_ticket_search ON ticket USING GIN (search_vec);
-- 查询并按 ts_rank 排序
SELECT id, subject, ts_rank(search_vec, q) AS score
FROM ticket, websearch_to_tsquery('simple', '登录失败') q
WHERE search_vec @@ q
ORDER BY score DESC
LIMIT 20;
解读:生成列随行写入自动维护,应用层零改动;@@ 匹配走 GIN 索引,ts_rank 给出相关度排序。注意 simple 配置只按空格切词,中文场景必须换 zhparser 或 pg_jieba 并自建检索配置,否则整句中文会被当成一个词,全文索引形同虚设。
中文搜索的天花板一半在分词:MySQL 靠 ngram 双字切分保底,PostgreSQL 靠 zhparser 提精度。这一步不做好,后面的台阶都踩不实。
台阶一治好了慢与乱序,但"磁盘满"依然搜不到 "No space left"——同义盲要靠下一级。
4、台阶二:向量列与回填
语义搜索不一定要上向量数据库:数据量在百万级以内时,给现有 Postgres 加 pgvector 扩展是改动最小的路线(量级判断可参考站内 pgvector 一篇的讨论)。schema 迁移分三步走:
-- pgvector 迁移:可分步上线、可回滚的 schema 变更
CREATE EXTENSION IF NOT EXISTS vector;
-- 1. 加向量列,允许 NULL:存量逐步回填,不影响现有读写
ALTER TABLE ticket ADD COLUMN embedding vector(1024);
-- 2. 回填完成后再建 HNSW 索引,避免边写边建
CREATE INDEX idx_ticket_emb ON ticket
USING hnsw (embedding vector_cosine_ops);
解读:vector(1024) 的维度必须与所选 embedding 模型对齐,例如 bge-m3 输出 1024 维;vector_cosine_ops 表示按余弦距离检索。列允许 NULL 是关键设计——新列上线对旧代码完全透明,回填多少、生效多少。HNSW 近似索引自 pgvector 0.5.0 引入,百万级数据毫秒级响应。
存量回填脚本要能断点续跑,示例如下:
import psycopg
BATCH = 500
with psycopg.connect(DSN) as conn, conn.cursor() as cur:
while True:
cur.execute("""SELECT id, subject || ' ' || content
FROM ticket WHERE embedding IS NULL
ORDER BY id LIMIT %s""", (BATCH,))
rows = cur.fetchall()
if not rows:
break
vecs = embed_batch([r[1] for r in rows]) # 批量调 embedding 服务
cur.executemany(
"UPDATE ticket SET embedding = %s WHERE id = %s",
[(v, r[0]) for r, v in zip(rows, vecs)])
conn.commit() # 每批提交,中断后重跑即可续传
解读:按主键分批、每批提交,脚本随时可停可续;调用 embedding 服务要设超时与重试。新写入的数据从这一步起由应用双写正文与向量,双写的失败处理见第 6 节。查询侧用 <=> 余弦距离算子取近邻,具体 SQL 在下一节一并给出。
全库向量必须来自同一个模型:换 embedding 模型等于换了语义空间,新旧向量不可比,必须全量重算后再切换。
5、台阶三:混合检索
纯向量检索有它自己的盲区:型号、错误码这类"字面就是全部信息"的 token,会被语义空间抹平。所以台阶三不是替换而是融合——保留台阶一的关键词路,与向量路各取 top 50,用 RRF 融合。原理与各家数据库的落地路线,站内《混合检索实战:BM25 与向量与 RRF》一篇有完整拆解,这里直接给 Postgres 上的一条融合查询:
-- 混合检索:两路召回 + RRF 融合(:qvec 与 :qtext 为查询参数)
WITH semantic AS (
SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> :qvec) AS rank
FROM ticket
WHERE embedding IS NOT NULL
ORDER BY rank LIMIT 50
),
keyword AS (
SELECT id, ROW_NUMBER() OVER (ORDER BY ts_rank(search_vec, q) DESC) AS rank
FROM ticket, websearch_to_tsquery('simple', :qtext) q
WHERE search_vec @@ q
ORDER BY rank LIMIT 50
)
SELECT t.id, t.subject,
COALESCE(1.0 / (60 + s.rank), 0)
+ COALESCE(1.0 / (60 + k.rank), 0) AS rrf_score
FROM semantic s
FULL JOIN keyword k ON k.id = s.id
JOIN ticket t ON t.id = COALESCE(s.id, k.id)
ORDER BY rrf_score DESC
LIMIT 20;
解读:1 / (60 + rank) 就是 RRF 的全部——只看每路排名不看分数,天然回避了两路分数量纲不可比的问题;两路都命中的文档分数叠加,自然排到最前。RRF 有效性的系统论证见 SIGIR 2009 的原始论文。MySQL 侧没有向量类型,融合通常放应用层:两路各查 50 条,十几行 Python 拼起来即可。
一句话记住两路分工:语义路管"换个说法也搜得到",关键词路管"型号与错误码一击即中"。
6、迁移工程与回滚方案
三级台阶是技术路径,能否安全上线取决于迁移工程。核心思路:新旧搜索同库共存,用配置开关决定流量走向,任何时刻都能拨回去。状态流转如下:
解读:回填与双写解决数据就绪,影子查询解决效果验证,灰度控制爆炸半径,回滚是兜底保险。各阶段的动作与退出条件如下:
| 阶段 | 关键动作 | 退出条件 |
|---|---|---|
| 存量回填 | 分批补向量列 | 向量列 NULL 占比归零 |
| 双写期 | 新数据同时写全文与向量 | 双写连续一周无报错 |
| 影子查询 | 新搜索只记日志不返回 | 对比评测指标达标 |
| 灰度切换 | 按百分比切流量到新搜索 | 核心指标连续不劣化 |
工程上有两个容易忽视的细节。一是双写失败的处理:embedding 服务超时时,向量列暂置 NULL、由补偿任务异步补齐,绝不能让"提交一条工单"因为这个外部依赖而失败。二是 LIKE 查询全程保留,它就是天然的降级通道——向量服务故障、HNSW 索引重建期间,开关拨回 LIKE,搜索能力退回从前,而不是直接不可用。
回滚的最高境界是"删掉一个 if":新旧两条查询链路并存,回滚等于改一行配置,分钟级完成,不存在数据层面的倒退。
7、新旧搜索效果验收
升级效果要拿数据说话,离线与在线两套口径都要有。离线评测的前提是评测集:从搜索日志抽样几百条真实查询,人工标注每条的相关工单,新旧搜索在同一集合上跑分。常用指标与典型的示意口径如下:
| 指标 | 口径说明 | LIKE 常见水平 | 混合检索目标 |
|---|---|---|---|
| 召回率 | 目标工单进 top 20 的比例 | 四成上下 | 八成以上 |
| 零结果率 | 查询无任何返回的占比 | 一成上下 | 百分之三以内 |
| 首条点击率 | 首条结果被点击的比例 | 两成上下 | 四成以上 |
| 查询延迟 | P95 端到端耗时 | 秒级 | 三百毫秒内 |
表中数值是示意口径,具体基线以自家评测为准;关键在比较方法:同一评测集、同一批标注,只换检索链路。在线验证靠灰度期间的 A/B:新旧各留一半流量,比首条点击率、搜索放弃率、翻页深度这些行为指标。还有一个便宜的人工办法——盲评:新旧搜索的 top 5 匿名并排,让一线客服选哪列更有用,一周就能攒出有说服力的结论。
8、实战复盘:工单搜索升级
把全流程在一个典型工单系统上走一遍。背景设定:客服团队二十人,Postgres 单库,工单表存量约四百万行、年增二十万,搜索是两条 LIKE,P95 延迟秒级。最大的痛点不是慢,而是重复建单——客服搜不到既有解法,同一个问题反复建单、反复处理。
8.1、决策:留在现有库内
团队没有向量库的运维人力,评估后决定三级台阶全部落在现有 Postgres 上:全文用 zhparser 加 GIN,向量用 pgvector,融合沿用第 5 节的 SQL。判断依据有二:四百万行在 pgvector HNSW 的舒适区内,单机毫秒级够用;不引新组件,意味着不新增故障域与值班对象。
8.2、三周落地
第 1 周上台阶一:装 zhparser、建生成列与 GIN 索引,开关从 LIKE 切到全文查询,当天 P95 从秒级降到几十毫秒,客服当周就能感知改善。第 2 周上台阶二:接 embedding 服务,回填脚本跑两个晚上补齐存量,应用改为双写。第 3 周上台阶三:混合查询先以影子模式跑五天,离线评测召回率从约四成升到八成五,随后按 10%、50%、100% 三档灰度切换。
8.3、结果与教训
上线一个月的行为数据:首条点击率明显上升,重复建单率下降约三成,搜索放弃率减半。三条教训值得抄走。第一,中文分词先于一切:zhparser 没配好时全文索引几乎无效,是影子期的对比数据暴露了问题。第二,embedding 服务必须有降级预案:某晚服务超时,双写按"暂置 NULL 加异步补偿"滑过,无一条工单写入失败。第三,别急着删 LIKE:HNSW 索引重建的那两个小时,降级开关立了功。
9、总结
LIKE 的问题不在写法而在能力边界:字节匹配撑不起检索系统。升级路径是四级台阶——全文索引治慢与乱序,向量列治语义盲,混合检索集两家之长;迁移安全靠双写、影子、灰度与回滚开关四件套保障。整条路径每一步都在现有库内完成,不推倒、不搬家。
练习三题:
- 给手头一张用 LIKE 搜索的表加全文索引,对比切换前后的 EXPLAIN 与延迟。
- 写一个按主键分批、可断点续跑的 embedding 回填脚本,验证中断重跑的正确性。
- 构造五十条查询的评测集,分别用 LIKE、全文、混合跑召回率,亲眼看一看三级差距。
对于仍在 LIKE 的存量系统,建议本周就启动台阶一:它是纯库内改造、当天可上线,而且是后续所有台阶的地基。


