后台搜索框用 LIKE 前置通配查 40 万行的文章表,单次 2.7 秒,CPU 冲到 80%。白天两个人同时搜,列表页就开始转圈。
第一反应是加索引,在 title 上建了个普通 B+ 树索引。EXPLAIN 显示还是全表扫,通配符在前把索引用废了。这是常识,但当时就是想先试一把,花五分钟排掉一个可能。
改成只匹配前缀,体验立刻变差。搜「带宽」搜不到「香港带宽怎么选」,运营不接受。也试过把标题拆词存到一张搜索表,维护成本太高,作废。
最后用 MySQL 8.0 自带的 ngram 全文索引。分词器按二元切,中文不用另装插件。这条语句在 40 万行的大表上跑了 6 分钟:
ALTER TABLE article ADD FULLTEXT ft_title(title) WITH PARSER ngram;
SELECT id, title FROM article
WHERE MATCH(title) AGAINST('带宽 线路' IN BOOLEAN MODE) LIMIT 20;
查询必须走 MATCH AGAINST,写成 LIKE 还是全表扫。布尔模式里空格是或的关系,加双引号才是短语匹配,这点和直觉相反,第一次查出来的结果多了一倍。
执行时间 2.7 秒降到 60ms,EXPLAIN 里 type 变成 fulltext。索引文件多了 38MB,写入从 1.2ms 涨到 3.4ms,后台一天几百篇的量,可以接受。
建索引期间写入会锁表,一开始想在生产直接跑,被 DBA 拦了。改成在线改表工具,跑了 11 分钟,业务无感,代价是多占四成磁盘临时空间。
大小写也会影响结果。ngram 对英文按字符切,搜 SDK 和 sdk 出来的条数不一样,查询前统一转小写再传,不必改表结构。
坑在短词。ngram_token_size 默认 2,搜单个字比如「云」直接返回空。前端加了最少两个字的校验,提示写成「至少输入两个字」,比让用户对着空列表猜要好。
还有停用词表。默认那份主要过滤英文虚词,中文词不受影响,但合并自定义词表时要留意,别把常用字带进去,带进去之后搜什么都是空。
现在搜索 p95 是 90ms,之前因为超时被砍掉的搜索日志功能也加回来了。
