博客的站内搜索一直是个摆设。
原来的实现简单粗暴:
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 条(相关性明显更好) |
| 表体积 | 380MB | 980MB |
查询从 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 万篇时重新评估方案"。
不解决问题,但至少知道问题会在哪里发生。
评论(0)
暂无评论,快来抢沙发~
评论功能仅对会员开放,请先登录
登录