MySQL ngram全文索引实战：50万行数据替代LIKE查询的性能对比与选型指南
做后端这行的，基本都会遇到一个场景：业务方开口就是"搜索"，打开 App 输入框敲几个字，要的是又快又准。我最近接手的一个内部系统，数据量不大不小，单表 50 万行，存的是商品名称和备注。刚开始用 LIKE '%词%' 还能凑合跑，等数据一上来，一次搜索动不动两三秒，接口超时、锁等待、CPU 飙高全来了。同事过来看了一眼说："MySQL 搜不动就上 ES 呗。" 说实话我没急着接这个话茬。上 ES 不是不行，但一个内部工具系统，为了几个搜索框就得维护一套 Elasticsearch 集群，索引同步、分词器、部署运维全都要跟上，成本不是零。我当时想的是：MySQL 从 5.7 开始就内置了 ngram 全文索引，专门给中文场景用。与其猜，不如先在 50 万行数据上跑一轮真实测试，看清楚它到底能扛住什么量级的请求、有什么坑、什么时候真的得换 ES。
1. 为什么我把目光停在 MySQL 自带的 ngram 全文索引
1.1 背景：LIKE 查询为什么扛不住
先说清楚原本的痛点。传统写法是：
这个写法在数据量小的时候看不出问题，但一旦数据量到了几十万行，每次查询都要做全表扫描。MySQL 没法用 B+ Tree 索引加速这类模糊匹配，因为 %词% 的前导百分号导致前缀不可知，索引直接失效。更头疼的是 LIKE 扫描时还伴随字符串逐字节比较，纯 CPU 密集型操作，我实测 50 万行的 1.1GB 表做一次 LIKE '%智能手环%'，耗时稳定在 2.8 秒到 3.5 秒之间，加上并发一上来，别说用户体验，数据库自身都快被拖垮了。
1.2 为什么先不急着上 ES
Elasticsearch 是分布式搜索的标杆，这个没争议。但对我来说，ES 引入后带来的是一整套复杂度：数据同步方案（binlog 监听、MQ 异步写入、定时全量重建）、ES 的节点运维与 JVM 调优、搜索词与数据库数据的一致性保障。对一个小团队、一个小数据集来说，这套成本太高。而 MySQL 自带的 ngram 全文索引天然存储在 InnoDB 表内部，事务一致、无需同步、零额外组件，如果它能满足业务需要，就是最省事的一条路。我给自己定的标准很简单：50 万行数据集上，单次搜索响应 500ms 以内、结果相关性可接受、并发 20 以下不崩，那就值得用。
2. 实验环境与测试数据准备
2.1 建表与数据灌入
测试不能只靠几张临时表，数据要尽量贴近真实业务。我建了一张商品表，字段包括自增主键、商品标题、商品描述、分类 ID、创建时间。标题和描述都是真实中文文本，模拟电商后台数据。
灌数据用的是 MySQL 存储过程，循环往 title 和 description 里拼中文词。为了贴近真实场景，我准备了一批商品词库，比如"华为手环""小米手环""智能手表""运动跑步""蓝牙耳机"等，再随机组合标题。最终表里落了 50 万行，数据文件约 1.1GB，倒排索引加全文索引占额外空间约 300MB。这一步有个经验：字符集一定要用 utf8mb4，中文 emoji 和生僻字都依赖它，而且 ngram 本身是基于 Unicode 字符切词的，字符集不对会导致切词结果混乱。
2.2 全文索引怎么建
关键语句就一行：
这里有个点容易被忽略：ngram 是 MySQL 全文索引的解析器插件，建索引时如果不写 WITH PARSER ngram，默认使用的是内置分词器，对中文基本是按空格和标点切分，一整句中文会被当成一个 token，搜索效果很差。加上 WITH PARSER ngram 后，MySQL 会把中文按固定长度切成一个个 token 存进倒排索引。默认 ngram_token_size = 2，也就是说每次切两个字符，比如"智能手环"会被切成"智能""能手""手环"三个 token。这个参数是全局配置，在 my.cnf 的 [mysqld] 段里设置，需要重启 MySQL 才能生效。因为默认值 2 对 2 个字的搜索词场景足够，我测试时直接用了默认值，没有额外调整。
innodb_ft_min_token_size 要说明一下：这个是 InnoDB 全文索引的最小 token 长度，默认是 3。中文场景如果 ngram_token_size 设为 2，token 长度就是 2，如果这个参数不调低，2 字符的 token 会被直接忽略，索引等于白建。所以这两个参数必须配套调整。
3. ngram 全文索引到底是怎么工作的
3.1 切词机制与倒排索引
ngram 解析器的核心逻辑很简单：把一个字符串按指定长度连续滑动切分。以"运动蓝牙耳机"为例，token_size=2 时切出来的 token 是"运动""动蓝""蓝牙""牙耳""耳机"。每个 token 都会记录它出现在哪一行、哪个字段、在字段内的位置等元信息，构建成倒排索引。正因为是固定长度的预切分，搜索时用户输入"蓝牙耳机"，系统会先拿"蓝牙""牙耳""耳机"这些 token 去倒排索引里找匹配行，再按照位置信息做短语匹配和排序。这就不需要像 LIKE 那样逐行扫描。
3.2 自然语言模式与布尔模式怎么选
MySQL 的全文索引查询语法是 MATCH(col1, col2) AGAINST (expr [search_modifier])。最常用的两种模式是 IN NATURAL LANGUAGE MODE 和 IN BOOLEAN MODE。
自然语言模式的逻辑是：把搜索词同样按 ngram 切开，然后统计每个 token 在索引中的出现频率，出现频率越低，权重越高，最后按相关度降序返回结果。这个模式适合用户输入一句话，系统自己理解核心词。布尔模式更直白：支持 + 必须包含、- 必须排除、* 通配、双引号精确短语这类操作符，语法上和 Elasticsearch 的 bool query 感官接近。我在测试中主要用布尔模式，因为业务需求是明确的关键词过滤而非全句语义理解。
4. 50 万行数据上的真实性能对比
4.1 测试方法与关键参数
我的测试脚本用 Python 压了 30 轮取平均值，每轮随机换一批关键词，数据落在 5 类查询上：单关键词搜索、双关键词强制包含、长短语搜索、模糊前缀搜索（关键词*）和 LIKE '%关键词%' 对照。表数据固定 50 万行，MySQL 版本 8.0.34，InnoDB 缓冲池设为 1G，全文索引相关缓存按上一节配置。测试前先 ANALYZE TABLE 刷新统计信息，避免执行计划因为过期统计跑偏。
4.2 实测数据汇总
直接上结果：
| 查询类型 | LIKE 平均耗时 | ngram 全文索引平均耗时 | 加速比 |
|---|---|---|---|
| 单关键词"手环" | 2.94s | 52ms | 56倍 |
| 双关键词"+运动 +手环" | 3.12s | 61ms | 51倍 |
| 短语"运动蓝牙耳机" | 3.30s | 138ms | 23倍 |
| 前缀"智能*" | 3.05s | 48ms | 63倍 |
| 带符号的商品编码"AB-123" | 2.88s | 95ms | 30倍 |
结论很明确：在 50 万级别数据集上，ngram 全文索引完全能替代 LIKE，而且提升接近两个数量级。我原本最担心的短语搜索，因为 token 切分和位置匹配多了一步计算，耗时到了 138ms，这个数据照样比业务要求的 500ms 低一半以上。不过这个测试也暴露了一个痛点——并发能力还需要另外验证。我单独用 50 个线程同时压了 30 秒，MySQL 的 CPU 占用到 70%，平均响应涨到了 180ms，还是有少量慢查询到 400ms，但至少没有出现连接打满的情况。如果并发冲到 200，我预计全文索引的 CPU 消耗会显著升高，这时就得考虑上 ES 了。
5. 实操中绕不开的坑与排查心得
5.1 全文索引"没生效"，一查是停用词表作怪
测试中途有个关键词"一款"怎么都搜不到结果。我一开始以为索引坏了，后来才查到 InnoDB 全文索引默认会过滤停用词。默认的停用词表 innodb_ft_server_stopword_table 包含很多中文高频虚词，比如"一款""可以""以及"。业务搜索里这些词往往是无关词，过滤掉反而合理。但问题是，如果我搜的就是一个看似"停用词"的产品型号或者品牌关键词，就会被误杀。解决办法是自定义停用词表，或者直接设置一个空表：
注意这个参数只对之后新建的全文索引生效，改完需要重建索引。这里有个更隐蔽的问题：ft_stopword_file 这个参数控制的是 MyISAM 引擎的全文索引，InnoDB 全文索引默认忽略它，很多网上的老教程混着写，实验前一定确认自己用的是 InnoDB。
5.2 单字和超短词搜不到
默认 ngram_token_size=2 意味着所有 token 都是双字组合。用户搜单字，比如搜"鞋"或者"袜"，ngram 会把 MATCH AGAINST('鞋') 当成一个长度不足的 token，在索引里直接匹配不到。因为 ngram_token_size=2 和 innodb_ft_min_token_size=1 是两个不同的参数，前者决定索引怎么切，后者决定索引保留多短的 token。如果业务确实需要支持单字搜索，可以把 ngram_token_size 改成 1，但这会导致索引体积暴增，倒排列表数量会成倍扩大，查询性能和磁盘占用都要重新评估。我的建议是：核心业务字段支持单字搜索就改，否则维持 2；搜索单字的需求可以在应用层做 LIKE 兜底，毕竟偶尔跑一次全表扫描影响不大。
5.3 布尔模式中 + 和 - 的语义和习惯不符
用 MATCH AGAINST('+运动 -手环' IN BOOLEAN MODE) 时，+ 表示必须包含，- 表示必须排除，这和 Elasticsearch 里 must、must_not 很像。但 MySQL 和 Lucene 的语法有个不同点：MySQL 的布尔模式里，空格分开的词默认是 OR 关系，也就是只要任意一个词匹配就算命中。业务方如果默认所有输入词都必须出现，就会出现搜"运动手环"却返回大量只包含"运动"的数据。解决方式是应用层把用户输入的词全部拼成 + 前缀，不依赖用户的空格习惯。
5.4 相关性排序不如预期
ngram 的排序算法和 Lucene 的 BM25 不是一回事。实际表现为：搜索结果匹配的词频权重偏高，但短语整体匹配的加分不够。比如用户搜"运动手环"，一条标题是"运动运动运动手环"的数据因为"运动"重复出现很多次，得分可能比"智能运动手环"更高。这确实是 MySQL 全文索引的固有短板，降级方案是业务层二次排序，或者把结果按创建时间做加权排序。我在测试中会定期把全文搜索的返回 id 集合拿回来，再用主表 order by created_at desc 二次排序，效果比纯相关度排序更贴合业务场景。
5.5 索引维护与重建
全文索引是在文档写入时同步更新的，插入量大时会拖慢写性能。50 万行全量灌数据时，我明显看到插入速度从每秒 3000 行掉到每秒 800 行，因为每个新文档都要做 ngram 切词并写倒排列表。遇到这种场景，我的做法是先建表、不带全文索引，批量灌数据完成后 ALTER TABLE ADD FULLTEXT 一次性建索引。MySQL 8.0 支持 INPLACE 算法建全文索引，路径是在 ALTER TABLE ... ADD FULLTEXT INDEX 时自动选择，不需要手动指定，但它会持有表的 MDL 锁，虽然允许并发 DML，但耗时较长，生产环境建议放维护窗口。
6. 那到底什么场景该用 ngram，什么场景该上 ES
我把这次测试的实际体会总结成一张选择对照表，方便大家按自己的场景估算。
| 场景 | MySQL ngram 全文索引 | Elasticsearch |
|---|---|---|
| 数据量 100 万以下 | 能用，性能好，零运维成本 | 杀鸡用牛刀 |
| 数据量千万级以上 | 不推荐，索引膨胀大且查询并发吃力 | 适合，分片能力天然契合 |
| 中文分词质量（如"北京市"拆词） | 固定 ngram，没有语义理解，生成很多弱相关 token | IK 分词器、自定义词典，效果更可控 |
| 短语精确匹配与高亮 | 有位置信息但高亮要应用层处理 | 内置高亮和短语查询，体验好 |
| 聚合分析、模糊搜错字、同义词 | 基本做不到 | 这是 ES 的主场 |
| 业务与事务强一致 | 和业务表同一事务，同步天然一致 | 需要额外同步链路，存在短暂不一致 |
以我这次的 50 万行测试结果来看，MySQL ngram 全文索引作为中小数据集场景的"搜索替代方案"，性价比确实高，部署成本几乎为零，查询响应稳定在几十到一百毫秒。但如果业务未来有明确增长预期，比如会超过千万级，或者搜索体验需要"模糊纠错""相关推荐""聚合筛选"，那我建议还是尽早规划 ES。Elasticsearch 的同步机制可以用 binlog 监听或者应用层双写，网上方案很多，但真正难的是 ES 集群的调优和后续维护。技术选型从来不是选一个绝对最好的，而是选一个当下最适合团队和业务阶段的。很多系统死在过度设计上，而不是死在方案不够先进上。
最后分享一个我做性能评测常用的习惯：不要只看平均值，一定要看 P95 和 P99。这次 ngram 全文索引的平均耗时是 52ms，但压测时 P95 到了 210ms，P99 偶尔冲到 380ms。如果只关注平均值，你会误判系统能力。建议用 performance_schema 或者简单的应用日志把百分位耗时拉出来看。另外，MySQL 全文索引的 IN NATURAL LANGUAGE MODE 有一个隐藏的 50% 阈值规则，一个 token 在超过半数的行里出现，这个词会被当成噪音忽略。如果业务搜索词过短且太大众，比如搜索"产品"两个字且超过一半行都包含"产品"，自然语言模式会直接查不到结果。优先用布尔模式，能绕开这个限制。
如果你手上也有一张几十万行的表正被 LIKE 拖得难受，我建议你把 ngram 全文索引当做一个先试试的选项。建完索引跑一轮压测，拿 P95 数据说话，再决定要不要上 ES。能用一个 MySQL 自带功能解决的事，别急着给系统加一个分布式搜索引擎的复杂度。搜索这条路，永远没有银弹，只有适配场景的合理取舍。