博客的站内搜索一直是个摆设。

原来的实现简单粗暴:

SELECT id, title, excerpt FROM blog_posts
WHERE title LIKE '%关键词%' OR content LIKE '%关键词%'
ORDER BY created_at DESC LIMIT 20

数据量小的时候,几百条记录,这个查询 20ms 就回来了,用着也没觉得有问题。

但博客写到四千多篇之后,问题就出来了:这个查询的耗时直接跳到了 4.2 秒。而且经常把 MySQL 的连接池拖慢,影响别的请求。

上周花了三个晚上,把它从 LIKE 改造成了倒排索引。中间踩了三个坑,今天把它们记下来。

先看清楚为什么慢

在动手改之前,我先跑了 EXPLAIN,结论很清楚:

type: ALL,rows: 4792,Extra: Using where; Using filesort

也就是说,它对 4792 行做了全表扫描,每一行都要把 content 字段(平均 8KB)读出来做一次字符串匹配,然后再排序。

4.2 秒里,绝大部分时间花在两个地方:

1. 读 content 字段——4792 行 × 平均 8KB ≈ 38MB 的文本要从磁盘读进内存再丢掉。

2. 排序——匹配完还要按时间做 filesort。

而且 LIKE '%关键词%' 前面的百分号意味着无法使用任何索引。这是 B+Tree 索引的天然限制:它只能做前缀匹配,做不了中间匹配。

所以问题不在 SQL 写得好不好,而在这套方案从原理上就不适合全文搜索。

方案选择:FULLTEXT 还是自建倒排索引

想清楚之后,摆在面前的有三条路:

方案 A:MySQL 原生 FULLTEXT 索引
优点:不用引入新组件,建个索引就能用,自带相关性打分。
缺点:中文分词需要 ngram,索引体积大,写入开销高。

方案 B:自建倒排索引表
优点:完全可控,分词、权重、排序都能自己定。
缺点:要自己维护,代码量大,容易出 bug。

方案 C:引入 Elasticsearch / Meilisearch
优点:功能最强,分词和搜索效果最好。
缺点:为了一个个人博客再养一个服务,太重了。

考虑到博客是单机部署、数据量不到五千篇、服务器内存有限,我最后选了 方案 A + 一点方案 B 的补充:用 MySQL 的 FULLTEXT 做主力,标题和标签单独做加权。

理由很简单:能用现有组件解决的事,不要为了"更专业"再加一个新服务。

坑一:中文分词——默认解析器根本不分词

这是第一个坑,也是最费时间的。

我按常规做法建了索引:

ALTER TABLE blog_posts ADD FULLTEXT(title, content);

然后测试:

SELECT id, title FROM blog_posts
WHERE MATCH(title, content) AGAINST('数据库' IN NATURAL LANGUAGE MODE);

结果:0 条。

但我明明有几十篇文章标题里带"数据库"。

查了半天文档才明白:MySQL 默认的 FULLTEXT 解析器是按空格和标点切词的,它天生是为英文设计的。中文句子没有空格,一整句话会被当成一个词,导致根本匹配不上。

解决办法是换成 ngram 解析器:

ALTER TABLE blog_posts ADD FULLTEXT idx_ft(title, content) WITH PARSER ngram;

ngram 的做法是把中文按 n-gram 切开。默认 ngram_token_size = 2,也就是两字一组。

比如"数据库连接池"会被切成:数据 / 据库 / 库连 / 连接 / 接池。

这意味着:搜索"数据库"能命中(因为"数据"这个 bigram 存在),但搜索单个字"库"就命中不了。

对博客搜索来说,两字粒度够用(用户一般不会只搜一个字),所以我没改 ngram_token_size。

但这里有个隐藏成本:ngram 索引的体积会比原来大很多。我建完索引看表体积,blog_posts 从 380MB 涨到了 980MB。这是第二个坑的伏笔。

坑二:建索引把磁盘写满了

第一个坑解决之后,我直接在本地建索引,准备测效果。

建到一半,报错了:

ERROR 1114 (HY000): The table 'blog_posts' is full

"表已满"这个报错通常不是表满了,而是磁盘满了。

我查了一下:本地磁盘剩余空间只剩 200MB 左右,而建 ngram 索引需要的额外空间是原表 content 总大小的 2-3 倍。

更麻烦的是,InnoDB 建 FULLTEXT 索引的时候会创建一批临时表,空间占用是叠加的。

所以建到一半,空间不够,就报错退出了。

处理办法有两个:

1. 先清理磁盘,腾出空间——我把一批历史备份和日志清掉,腾了 3GB。

2. 分批建索引——如果空间实在不够,可以先只给高频命中的字段建(比如只给 title 和新文章建),然后逐步扩。

我最后是先腾空间,然后一次性建完。整个建索引过程跑了 11 分钟,期间数据库 CPU 一直在 70% 以上,不能在这个时间窗口做别的重活。

这也是一个教训:DDL 操作要在低峰期做,而且要提前算好磁盘余量。

坑三:相关性排序很糟糕

索引建好了,搜索能出结果了,但排序很怪。

搜"数据库"的时候,排在最前面的是一篇文章,标题里根本没有这个词,只是因为正文里提到了十几次;而标题就叫《数据库连接池耗尽事故复盘》的那篇排在第三。

MySQL 的 FULLTEXT 相关性打分(MATCH ... AGAINST 返回的 score)是按词频和文档长度算的,它不知道标题比正文重要。

所以得自己做加权。我的做法是分两次查,然后合并:

-- 第一路:标题命中,权重高
SELECT id, title, MATCH(title) AGAINST(?) * 3 AS score FROM blog_posts
WHERE MATCH(title) AGAINST(?)

UNION

-- 第二路:正文命中,权重低
SELECT id, title, MATCH(content) AGAINST(?) * 1 AS score FROM blog_posts
WHERE MATCH(content) AGAINST(?)

ORDER BY score DESC

与此同时,还加了两个补充规则:

1. 标签匹配加 2 分——因为标签是人工标的关键词,准确度高。

2. 时间衰减——同一分数下,越新的文章排越前。这个不是为了让新文章占便宜,而是因为旧文章的技术内容可能已经过时(比如 PHP 7 的写法)。

还有一个细节:MySQL 的 FULLTEXT 有个"50% 阈值"规则——如果一个词在超过 50% 的文档里出现,它会被认为是"无意义词"而不参与匹配。博客里"PHP""代码"这种词出现频率很高,很容易被过滤掉。

解决办法是用 IN BOOLEAN MODE 代替 NATURAL LANGUAGE MODE,布尔模式没有这个阈值限制。

改完之后的效果

改造前后对比:

指标改造前(LIKE)改造后(FULLTEXT)
平均查询耗时4.2 秒38ms
P95 耗时6.8 秒92ms
扫描行数4792 行全表索引直接命中
搜索"数据库"结果47 条(含正文无关命中)31 条(相关性明显更好)
表体积380MB980MB

查询从 4.2 秒降到 38ms,快了 110 倍。代价是表体积涨了 2.6 倍,以及写入时多了一次索引维护(实测新增一篇文章的写入耗时从 12ms 涨到 34ms)。

对于一个"读多写少"的博客来说,这个交换是划算的。

几点体会

1. 慢查询的根因往往不是 SQL 写得不好,而是方案用错了。

LIKE 这条 SQL 本身没有任何毛病,写得干净、逻辑清楚。但它从一开始就不适合做全文搜索。优化 SQL 只能让它快 10%,换方案能快 110 倍。

2. 引入新组件之前,先问一句"现有的够不够用"。

Elasticsearch 当然更好,但它意味着多一个进程、多一份内存、多一套运维。对四千篇文章来说,MySQL 的 FULLTEXT 已经绰绰有余。这个决定帮我省了大量后续维护成本。

3. 每个"提升"都有代价,要算清楚账。

表体积涨 2.6 倍、写入慢 3 倍,这两条都不是小事。如果这是个高频写入的业务表,我可能就会选方案 B(自建倒排表,可以异步更新)。但对博客来说,一天写几篇,完全无所谓。

4. DDL 操作要当成"生产事故"来准备。

我这次就因为磁盘空间不够,建索引建到一半失败,白等了 6 分钟。如果这是线上环境、如果是在业务高峰期,后果就更严重了。

后来我给自己定了规矩:任何 ALTER TABLE 之前,先查磁盘余量、先估索引体积、先挑低峰期、先准备好回滚。

写在最后

搜索功能现在能用了,虽然还不算完美——它没有拼音搜索、没有错词纠正、没有高亮片段,但至少能做到"输入关键词,300ms 内出相关结果"。

对一个个人博客来说,这就够了。

这次改造让我重新认识了一件事:很多东西看起来"能用",是因为数据量还不够大。四千篇是个坎,那两万篇呢?到时候可能真得换 Elasticsearch 了。

所以我把这个改造记在了技术债清单里,标注了一句话:"数据量超过 2 万篇时重新评估方案"。

不解决问题,但至少知道问题会在哪里发生。