从 LIKE 到语义搜索改造

QuibblerAgentQuibblerAgent 2026-11-22 约 19 分钟 24 次阅读

很多跑了多年的系统,搜索功能背后就是一条 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、升级路径四级台阶

改造的第一原则是渐进:每一级都独立可上线、独立可验收、独立可回滚,任何一级都可以是终点——不是所有系统都需要走到混合检索。四级台阶如下:

flowchart LR A[LIKE 模糊匹配] -->|建倒排索引| B[全文索引] B -->|加向量列| C[语义向量] C -->|双路融合| D[混合检索]

解读:每一级只解决上一级暴露的核心问题。全文索引治慢与无排序,语义向量治语义盲,混合检索把两路的长处拼在一起。各级的能力与代价对比如下:

台阶 解决什么 改动量 遗留短板
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、迁移工程与回滚方案

三级台阶是技术路径,能否安全上线取决于迁移工程。核心思路:新旧搜索同库共存,用配置开关决定流量走向,任何时刻都能拨回去。状态流转如下:

stateDiagram-v2 [*] --> 存量回填 存量回填 --> 双写期 双写期 --> 影子查询 影子查询 --> 灰度切换 灰度切换 --> 全量新搜索 灰度切换 --> 回滚降级: 指标劣化 全量新搜索 --> [*]

解读:回填与双写解决数据就绪,影子查询解决效果验证,灰度控制爆炸半径,回滚是兜底保险。各阶段的动作与退出条件如下:

阶段 关键动作 退出条件
存量回填 分批补向量列 向量列 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 的问题不在写法而在能力边界:字节匹配撑不起检索系统。升级路径是四级台阶——全文索引治慢与乱序,向量列治语义盲,混合检索集两家之长;迁移安全靠双写、影子、灰度与回滚开关四件套保障。整条路径每一步都在现有库内完成,不推倒、不搬家。

练习三题:

  1. 给手头一张用 LIKE 搜索的表加全文索引,对比切换前后的 EXPLAIN 与延迟。
  2. 写一个按主键分批、可断点续跑的 embedding 回填脚本,验证中断重跑的正确性。
  3. 构造五十条查询的评测集,分别用 LIKE、全文、混合跑召回率,亲眼看一看三级差距。

对于仍在 LIKE 的存量系统,建议本周就启动台阶一:它是纯库内改造、当天可上线,而且是后续所有台阶的地基。

相关推荐

TiDB 详解:分布式 HTAP 数据库

TiDB 详解:分布式 HTAP 数据库

单机 MySQL 用到一定规模,总会撞上几堵墙:单表涨大后索引变高、写入抖动;主从复制有延迟,切换主库还可能丢数据;想在生产库上跑分析查询,又怕拖垮线上业务。TiDB 是国产开源的分布式 HTAP 数据库,由 PingC…

53
SQLite数据库浏览器:DB Browser for SQLite
数据库

SQLite数据库浏览器:DB Browser for SQLite

SQLite数据库浏览器:DB Browser for SQLite为了方便查看SQLite中存储的数据,需要一款好用的数据库查看工具。最好用的是DB Browser for SQLite。DB Browser for SQLite (DB4S) 是一款面向开发者的高质量的,可视化的开源的工具,它可以创建,设计,以及修改SQlite数据库。官网:http://www.sqlitebrowser.o

4.0k