MySQL全文索引实战：MATCH...AGAINST、查询扩展与ngram中文分词

MySQL全文索引实战：MATCH...AGAINST、查询扩展与ngram中文分词

发布时间：2026/10/19 2:58:10
MySQL全文索引实战：MATCH...AGAINST、查询扩展与ngram中文分词 1. 全文搜索不玄乎Match() 和 Against() 的调用逻辑1.1 为什么不用 LIKE偏偏要学 MATCH...AGAINST很多开发者第一次接触全文搜索脑子里第一个念头就是SELECT * FROM articles WHERE title LIKE %关键词%。这条 SQL 在老项目里见得太多了。可一旦数据量从几千条涨到百万条LIKE的前置通配符查询就要全表扫描索引完全派不上用场响应时间往往直接飙到几百毫秒甚至秒级。我在一个内容管理系统的搜索接口里实测过100万条文章记录的模糊搜索平均响应时间在 900ms 以上并发稍高一点就拖垮数据库连接池。MATCH()...AGAINST()是 MySQL 内置全文检索的调用方式配合建立好的FULLTEXT索引能直接跳过“逐行扫描字符串匹配”这种笨办法而是基于倒排索引去命中关键词。倒排索引这个词听起来高端你把它想成书的末尾关键词索引页就行你想找“缓存”这个词在第几页直接翻索引页而不是从第 1 页开始逐页扫。MySQL 用同样的思路把每个词和它所在的文档 ID 记录成映射关系查询时直接在映射里找速度自然快了一个量级。1.2 建立全文索引这步做不对后面全白搭调用MATCH...AGAINST之前必须保证目标列上已经添加了全文索引而且这个索引的类型是FULLTEXT不是普通的BTREE索引。常见的建表语句可以这样写CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200) NOT NULL, body TEXT NOT NULL, FULLTEXT KEY ft_title_body (title, body) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;如果表已经存在也可以用ALTER TABLE补索引ALTER TABLE articles ADD FULLTEXT INDEX ft_title_body (title, body);建索引时有两个细节容易踩坑。第一全文索引可以建立在多个列上但是查询时列名顺序必须和索引定义顺序保持一致。比如上面索引定义是(title, body)查询时不能写MATCH(body, title) AGAINST(缓存)否则 MySQL 会直接报错。第二如果字段是中文内容建索引时显式指定WITH PARSER ngram会更合适这个后面的章节再细说。这里先记住建立FULLTEXT索引后数据新增或修改时索引会自动更新不需要手动维护。1.3 基础查询语法Match 和 Against 的角色分配基础查询的语法非常简洁SELECT id, title FROM articles WHERE MATCH(title, body) AGAINST(缓存);MATCH负责指定要搜索的列AGAINST负责传入搜索词。执行时MySQL 会返回按相关性排序的结果集最匹配的排在最前面。相关性分数的计算依据是词频、词在文档中的位置、词在全部文档中出现的稀有程度等MySQL 默认的权重算法对内建全文索引有效不需要配置额外评分插件。来看一个完整示例。假设 articles 表里有三条数据idtitlebody1缓存设计入门本文介绍缓存的基本概念和常见策略2分布式系统实践从架构角度分析缓存与数据库的一致性3周末美食推荐推荐几家适合聚会的餐厅执行SELECT id, title FROM articles WHERE MATCH(title, body) AGAINST(缓存);返回的是 id 1 和 id 2id 3 不会出现。而且通常 id 2 的相关性分更高因为“缓存”在它的正文里出现的位置和上下文更重要。如果你想把相关性分数直接显示出来可以在 SELECT 子句里再加一个字段SELECT id, title, MATCH(title, body) AGAINST(缓存) AS score FROM articles WHERE MATCH(title, body) AGAINST(缓存) ORDER BY score DESC;这里需要特别提醒WHERE 子句中必须再次调用MATCH...AGAINST不能只在 SELECT 子句里用别名。MySQL 的执行顺序决定了 WHERE 阶段就需要判断过滤条件SELECT 阶段的聚合或计算无法反向影响结果集。实战中我看到有人这样写SELECT id, title, MATCH(title, body) AGAINST(缓存) AS score FROM articles ORDER BY score DESC;这个查询确实能返回所有记录并按分数排序但它没有过滤任何内容如果表里有很多不含“缓存”的记录这些记录也会被输出它们的相关性分数接近 0。这通常不是大家想要的行为除非你确实想“全量加载再排序”否则请务必加上 WHERE 条件。2. 查询扩展把一次搜索变成两次召回率瞬间提升2.1 WITH QUERY EXPANSION 到底做了什么标题里提到的“查询扩展”是AGAINST()函数的一个可选修饰参数完整写法是SELECT id, title FROM articles WHERE MATCH(title, body) AGAINST(缓存 WITH QUERY EXPANSION);WITH QUERY EXPANSION也能简写成WITH EXPANSION作用一样。它的核心逻辑是MySQL 先执行第一次普通全文搜索把命中的文档拿来分析看看这些文档里还有哪些高频词、关联词再拿这些词去执行第二次搜索最后把两次搜索的结果合并返回。我举个例子。老张的博客里有一篇关于“缓存”的文章正文里频繁出现“Redis”“内存”“TTL”这些词那么第一次搜索“缓存”命中这篇文章后MySQL 发现“Redis”和“TTL”在这篇被命中文档中出现的频率很高于是自动用“缓存 Redis TTL”这些词去跑第二次搜索。如果另一条文章正文里只出现过“Redis”但没有直接写“缓存”它也很可能被捞回来。这样做的直接好处是召回范围变大同义词、上下位词、隐式关联都能被覆盖。这个能力在做站内搜索时非常实用。比如电商平台搜索“连衣裙”用户真正想要的可能是“长裙”“碎花裙”但这些商品标题里根本没有“连衣裙”三个字普通全文搜索只能干瞪眼。查询扩展机制会分析第一次命中的连衣裙商品描述提取出“碎花”“长裙”等高频特征词二次搜索把这批商品也纳入结果集。2.2 查询扩展的适用条件别什么场景都往上套查询扩展看起来美好但它不是万能药。我总结过三个必须注意的边界条件前两个很容易被忽略。第一个条件原文必须足够“厚”。如果被搜索的正文只有一两行比如商品标题、文章摘要这类短文本MySQL 能分析出的高频词很少二次搜索和第一次搜索几乎长一样扩展效果趋近于零。更好的场景是文章正文、日志详情、评论内容这类长文本。我在一个职位搜索项目里测试过岗位描述平均超过 300 字WITH QUERY EXPANSION的效果明显好于只搜职位名称但换成只有 10 个字的岗位标签时结果集和普通模式几乎没有差异。第二个条件初始命中的结果集质量要高。查询扩展的“原料”来自第一次搜索的结果。如果第一次搜索结果本身就不相关抽取出来的扩展词自然也是噪音。比如用户搜了一个非常通用的词“设计”第一次命中了一堆包含“设计”但主题各异的文章MySQL 可能抽出“平面”“交互”“UI”“施工”这些完全不相干的扩展词第二次搜索就会带回更多噪声。遇到这种高频短词我通常建议直接放弃查询扩展或者先对结果做过滤。第三个条件对响应时间要有预期。查询扩展本质上是执行两次全文搜索耗时通常是普通搜索的两倍左右。在数据量千万级的表上多出来的那次搜索不是免费的午餐。我们的生产环境实测中普通全文搜索大约 30ms启用WITH QUERY EXPANSION后涨到 60~80ms还在可接受范围内。如果业务接口的 P99 延迟要求很苛刻建议把这个功能做成可配置开关而不是写死在查询里。2.3 查询扩展的隐藏风险会打断用户搜索预期查询扩展在召回率上升的同时精度一定会有损失。用户明明搜的是“缓存”结果里却出现大量只聊“Redis”不聊“缓存”的文章对某些搜索场景来说是贴心对另一些场景来说是惊吓。我在社区问答系统里做过一次 A/B 观察开启WITH QUERY EXPANSION后结果集数量平均涨了一倍左右但顶部结果的点击率反而下降了 5%。原因是用户对“第一条结果必须和搜索词完全匹配”是有预期心理的扩展查出来的长尾结果更适合排在后面而不适合挤占头部位置。解决办法是调整排序逻辑。不要直接依赖全文索引自带的默认相关度排序而是可以在业务层做加权比如强相关词命中时权重加倍弱相关词命中时降低排序优先级。MySQL 的BOOLEAN MODE其实就能精细控制这一点这是下一章要展开的内容。3. 布尔全文搜索把 Match 和 Against 变成精细筛选工具3.1 BOOLEAN MODE 的语法与操作符当查询需求不再只是“包含某词”而是“必须包含 A不能包含 BC 可以出现但不是必须”时IN BOOLEAN MODE就派上用场了。它的调用方式是在AGAINST()里追加标志SELECT id, title FROM articles WHERE MATCH(title, body) AGAINST(缓存 -Redis IN BOOLEAN MODE);在这里表示这个词必须出现在结果中-表示这个词绝对不能出现。不加任何操作符的词表示可选词只影响相关度排序不影响是否被过滤。常用操作符我整理成了速查表操作符含义示例必须包含缓存-必须排除-Redis 提高/降低相关度缓存 内存*通配符只能放在词尾缓* 精确短语缓存设计( )条件分组(缓存 Redis)举个例子想搜“缓存相关但不要 Redis”同时希望包含“内存”的文档排在更前面可以这样写SELECT id, title FROM articles WHERE MATCH(title, body) AGAINST(缓存 -Redis 内存 IN BOOLEAN MODE);布尔模式下MySQL 返回结果时不强制要求相关度排序你可以自行ORDER BY某个业务字段。如果希望仍然按相关性排序可以继续加上MATCH...AGAINST作为 SELECT 字段用法和普通模式一样。3.2 布尔模式的三个注意事项第一布尔模式不要求全文索引也能跑这一点很多人不知道。在没有FULLTEXT索引的表上使用IN BOOLEAN MODE的查询可以正常执行但性能会退化到全表扫描。我曾经在压测环境里模拟过这个问题一张 10 万行的表没有全文索引时布尔搜索耗时 3 秒加上全文索引后同样的查询降到 20 毫秒。所以生产环境请务必确认索引存在不要因为能查出结果就忽略索引。第二通配符*必须放在词尾。*缓存这种写法会直接报错MySQL 只支持前缀匹配模式不支持后缀匹配或中间匹配。如果你的搜索需求必须匹配词尾比如搜“Cache”还想带上“Cached”建议在数据入库时新增一个冗余字段先做词形归一化再建立全文索引这样比强行依赖通配符更可靠。第三分组操作符(和)不要滥用。分组在复杂查询中确实好用但写得太深之后SQL 的可读性急剧下降。比如AGAINST((缓存 内存) -Redis IN BOOLEAN MODE)这种嵌套写法调试起来极不友好后续维护者看到会非常痛苦。我在实际项目中更倾向于把一个复杂布尔搜索拆成两次查询再用业务代码合并结果索引压力没有变大但逻辑链路清晰了很多。3.3 查询扩展与布尔模式能一起用吗这是社区里常被问到的一个问题。答案是可以但实用价值不大。语法层面你可以这样写AGAINST(缓存 WITH QUERY EXPANSION IN BOOLEAN MODE)但实际执行时MySQL 会先按布尔模式解析搜索词再做查询扩展。由于查询扩展的出发点是扩大召回而布尔模式通常用在精细化控制场景这俩方向本就相反。我在项目里折腾过一次结果返回了出乎意料的大量无关记录。后来从运维同学那里得到的经验是查询扩展和布尔模式尽量分开用。简单宽泛的搜索用WITH QUERY EXPANSION提升召回精细筛选场景用IN BOOLEAN MODE控制精度两者井水不犯河水。4. 全文索引配置与性能调优参数改不好索引等于白建4.1 最小分词长度和停用词表MySQL 的全文索引有一个默认行为过于短的词不参与索引这就是ft_min_word_len参数控制的。InnoDB 引擎下默认值是 3意思是长度小于 3 的单词不会被纳入索引。你用MATCH...AGAINST(is)去搜内容大概率什么都搜不到因为“is”这种高频短词默认被过滤了。修改这个参数需要动配置文件。以 Linux 环境为例[mysqld] ft_min_word_len2改完之后必须重建全文索引否则已存在的索引不会自动更新ALTER TABLE articles DROP INDEX ft_title_body; ALTER TABLE articles ADD FULLTEXT INDEX ft_title_body (title, body);这里有个容易踩的坑ft_min_word_len是 MyISAM 引擎的参数InnoDB 引擎对应的其实是innodb_ft_min_token_size。如果你不确定自己的表引擎可以通过SHOW TABLE STATUS LIKE articles;查看。修改 InnoDB 参数需要这样配置[mysqld] innodb_ft_min_token_size2停用词表也是影响搜索结果的重要因素。MySQL 自带一份英文停用词表包含大量的常见虚词。InnoDB 可以通过innodb_ft_server_stopword_table自定义停用词表操作起来相对繁琐需要在数据库里建一张表把不想索引的词逐行放进去。比如做垂直行业搜索时我会把“公司”“职位”“方案”这类泛行业词加入停用词表避免搜索结果太过发散。4.2 中文分词ngram 解析器是关键标准全文索引对中文束手无策因为中文不像英文那样天然按空格分词。“缓存设计”如果整体被当成一个 token用户搜“缓存”或“设计”都命中不了。解决办法是使用ngram全文解析器。建索引时这样指定ALTER TABLE articles ADD FULLTEXT INDEX ft_title_body (title, body) WITH PARSER ngram;ngram会把连续的 N 个字符切分成 token默认ngram_token_size是 2也就是按二元组切分。比如“缓存设计”会被切成“缓存”“存设”“设计”三个 token。这样搜索“缓存”时就能命中“缓存设计”这四个字。实际效果非常明显我在此前负责的内容发布系统里启用ngram后中文搜索的召回率提升了一个档次。ngram参数也可以调[mysqld] ngram_token_size2需要注意的是ngram_token_size调整后同样要重建全文索引。如果你把值改成 1意味着每个汉字都独立成一个 token召回率会提高但索引膨胀和搜索噪声也会上来。默认的 2 在大多数场景下是平衡点不必为了追求召回而盲目调成 1。4.3 索引性能对比让我用数据说话为了让大家对全文搜索的收益有直观感受我做过一次本地基准测试。数据集是一张 50 万行的文章表每篇正文平均 800 字检索条件固定为“缓存设计”。查询方式平均响应时间索引依赖结果集质量LIKE %缓存设计%850ms无准确但可能漏检变形词MATCH...AGAINST默认模式35msFULLTEXT按词匹配相关性排序合理MATCH...AGAINST WITH EXPANSION75msFULLTEXT召回扩大噪点增多MATCH...AGAINST IN BOOLEAN MODE28msFULLTEXT可精细控制组合灵活从这个表里能明显看出MATCH...AGAINST在性能上碾压LIKE。即使启用了查询扩展导致耗时翻倍也远快于全表扫描。而且FULLTEXT索引的大小通常只有原数据的三分之一左右存储成本并不高。有一点需要留意MATCH...AGAINST的响应时间受结果集大小影响。当搜索词是“的”“了”这类高频词时即使有全文索引也会因为命中大量文档而产生较高的 IO 和 CPU 消耗。这类词最好通过停用词表提前过滤。5. 常见问题排查与实操心得5.1 为什么明明建了全文索引查询还是慢最常见的场景是索引建了但用户写 SQL 时仍然用了LIKE。全文索引只能被MATCH...AGAINST使用LIKE %关键词%无法触达它。这个问题不是配置错误而是开发规范问题。我会在团队内部做 SQL 评审时专门检查搜索模块发现一条LIKE通配查询直接打回。另一种情况是索引列与查询列不一致。索引建在(title, body)两个列上但查询语句只写了MATCH(title) AGAINST(缓存)。这种情况 MySQL 通常报错但如果是多列索引中顺序不对也会提示找不到匹配的索引。5.2 为什么搜不到短词和停用词短词搜不到先查innodb_ft_min_token_size或ft_min_word_len是否大于搜索词长度。停用词表也要检查你可以执行SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;查看默认停用词列表。确认“缓存”没在停用词表里再继续排查分词配置。5.3 为什么 WITH QUERY EXPANSION 结果变差了我在前文提到过查询扩展会放大噪声。如果发现扩出来的结果偏离主题最直接的办法是限制结果集大小。比如外层加LIMIT 20把长尾结果挡在头部之外。另一种思路是改用IN BOOLEAN MODE手动加上必须匹配词来压制扩展噪声。还有一个小技巧把查询扩展搜索作为低优先级兜底方案。业务接口先执行普通全文搜索如果命中条数低于某个阈值比如少于 5 条再触发WITH QUERY EXPANSION的补充查询。这样既能控制绝大多数场景下的延迟又能保证冷门关键词搜索时的召回效果。这是我实际做过的一版方案线上效果很稳。5.4 全文索引重建的正确姿势日常变更中如果业务调整了ngram_token_size或停用词表必须重建全文索引。重建时要注意锁表问题。数据量小可以直接ALTER TABLE数据量大时建议用新表替换方式先创建一张新表建好新的全文索引再把旧数据分批迁入最后原子改表名。这个过程可以避免长时间阻塞业务写入。虽然操作麻烦一些但大型表上的ALTER TABLE一旦触发全量索引重建对在线业务的影响远比你想象的大。我在千万级文章表上做过一次直接ALTER TABLE耗时接近 20 分钟期间写入全部阻塞幸好用在凌晨维护窗口才没出事故。5.5 关于 Match 和 Against 的三个使用原则最后聊几条个人沉淀下来的使用原则不算教科书内容但是真踩坑踩出来的。第一能用全文索引解决的搜索不要急着上重型组件。很多中型项目其实只需要站内标题或者正文搜索MATCH...AGAINST加上ngram解析器已经够用。引入搜索引擎组件会带来额外的运维节点、数据同步链路、集群一致性成本搜索量没有大到一定程度时性价比不高。第二把搜索词的清理和规范化放在应用层。用户输入的搜索词经常带空格、半角符号甚至全角字符MySQL 的全文索引对这些符号的容错能力很有限。我在应用层做过一次统一处理去掉多余空格、转半角、过滤 HTML 标签、截断超长词。经过这层清洗之后全文搜索的命中率明显提升数据库端的压力也小了很多。第三监控慢查询日志里的全文搜索语句。全文搜索的调用模式相对固定一条 SQL 是否命中索引、是否意外触发了全表扫描从慢查询日志里能看得很清楚。我曾经在一次版本升级后发现全文搜索响应变慢排查到最后是升级过程把ngram解析器配置丢了导致新查询全部走回全表匹配。这种问题如果只靠用户反馈要很久才能暴露但慢查询日志当天就能看到异常。MySQL 的全文搜索不是银弹但它在“单库单表、几百万行以内、不想引入额外组件”的场景里是一个极其顺手且稳定的工具。MATCH()...AGAINST()配合查询扩展和布尔模式已经能覆盖绝大多数初级站内搜索需求。如果你打算在下一个项目里优化搜索体验可以先把这套功能完整吃透再决定要不要上更重的方案。