MySQL ngram全文索引实战：50万行数据中文模糊搜索性能测试

1. 为什么我会盯上 MySQL 自带的 ngram 全文索引

“MySQL 搜不动就上 ES”，这句话在圈子里流传太久了，久到很多人把它当成了默认架构决策。我最初也是这么想的——只要业务里出现模糊搜索、关键词匹配、日志检索，第一反应就是“加个 Elasticsearch 吧”。但真到了落地阶段，问题就来了：多维护一套集群、多一套数据同步链路、多一份运维成本，数据量还没到千万级，ES 的资源开销反而成了负担。

所以这次我做了个反向验证：在 50 万行真实业务数据上，把 MySQL 自带的 ngram 全文索引压到极限，看它到底能不能扛住中文模糊搜索的场景。结论先放这儿——不是所有“搜不动”都要上 ES，ngram 全文索引在很多场景下被严重低估了。

这篇文章适合谁看？如果你手上有几十万到几百万行的文本数据，业务需要中文分词搜索、关键词匹配、LIKE 模糊查询，但又不想引入 ES 这套重型武器，那这篇内容就是写给你的。我会把选型逻辑、参数计算、建索引实操、性能对比、踩坑记录全部摊开讲，代码和 SQL 都能直接抄。

先说清楚一个前提：ngram 全文索引不是万能的，它有明确的适用边界。我测下来，50 万行、平均每行 200 字左右的文本，ngram 全文索引的查询响应能稳定在几十毫秒级别，这个成绩已经能覆盖大部分中小型业务的中文搜索需求了。但如果你要做的是跨字段相关性排序、聚合分析、海量日志实时检索，那 ES 依然是更合适的选择。关键是要搞清楚分界线在哪。

2. ngram 全文索引到底是怎么工作的

2.1 从 LIKE '%关键词%' 的性能陷阱说起

大部分人第一次遇到搜索需求，写的都是 LIKE '%关键词%'。这条 SQL 在数据量小的时候没感觉，一旦表里有了几十万行，全表扫描的代价就暴露出来了。我实测过，50 万行的表上跑 LIKE '%测试%'，冷启动查询要 1.2 秒以上，而且随着数据增长是线性劣化的。

原因很简单：前置通配符 % 让 B+ 树索引彻底失效。MySQL 只能一行一行地读、一行一行地匹配，CPU 和 IO 全打满。你可以理解为，这相当于让数据库把整本书从头到尾读一遍，只为了找出现“测试”两个字的段落。

那有没有办法让 MySQL 用上索引来做模糊匹配？有，就是全文索引。而中文场景下，MySQL 原生支持的方案就是 ngram 解析器。

2.2 ngram 的分词原理：把中文切成固定长度的片段

MySQL 的全文索引默认用的是英文分词逻辑，按空格和标点切词。中文没有空格，所以默认解析器对中文基本没用。ngram 解析器就是为解决这个问题而生的——它把文本按固定长度 N 切成连续的字符片段。

举个例子，假设 ngram_token_size = 2，文本是“数据库优化”：

• 切出来的 token 是：数据、据库、库优、优化

查询的时候，比如搜“据库”，它也会被切成“据库”，然后去倒排索引里匹配。这就是 ngram 的核心机制：用固定长度的字符片段建立倒排索引，查询时同样切片后查索引。

ngram_token_size 这个参数很关键，它决定了切片的粒度：

token_size	切分示例（数据库优化）	适用场景	索引体积
1	数、据、库、优、化	单字搜索	最大
2	数据、据库、库优、优化	通用中文搜索（推荐）	中等
3	数据库、据库优、库优化	短语精确匹配	较小

我选的是 ngram_token_size = 2，这是中文搜索的甜点值。为什么？因为中文里双字词占比最高，切成 2-gram 既能保证召回率，索引体积也可控。切成 1 的话索引膨胀太厉害，切成 3 的话很多双字词搜不到。

注意：ngram_token_size 是只读参数，修改后必须重启 MySQL 实例，而且已有全文索引需要重建才生效。这个坑我踩过，改完参数没重建索引，查询结果一直不对，排查了半天。

2.3 倒排索引 vs B+ 树：两种索引的本质差异

很多人搞不清楚全文索引和普通索引的区别。简单说：

• B+ 树索引：适合等值查询和范围查询，比如 WHERE id = 100、WHERE age > 18。它的前提是你能给出一个明确的前缀。
• 倒排索引：适合文本内容检索，建立的是“词 → 文档 ID 列表”的映射。你给它一个词，它直接告诉你哪些行包含这个词。

全文索引底层就是倒排索引。当你执行 MATCH(col) AGAINST('关键词') 时，MySQL 先把关键词用 ngram 切片，然后去倒排索引里找交集，最后回表取数据。整个过程不需要全表扫描，这就是它比 LIKE 快几个数量级的根本原因。

3. 50 万行实测：环境搭建与数据准备

3.1 测试环境与参数配置

先把环境交代清楚，不然数据没法复现。我的测试机配置：

• CPU：4 核
• 内存：8 GB
• 磁盘：SSD
• MySQL 版本：8.0.35
• 数据量：50 万行，平均每行文本约 200 字

关键参数配置如下：

这里解释几个参数的选择逻辑：

ngram_token_size = 2 前面说过了，中文双字词最优。innodb_ft_enable_stopword = OFF 是关掉停用词过滤，因为中文的停用词处理跟英文完全不同，默认的停用词表对中文没意义，反而会误杀一些正常词汇。innodb_ft_min_token_size 设成 1 是为了确保短词也能被索引到。

提示：生产环境改这些参数前，一定要在测试库验证。尤其是 ngram_token_size，改了要重启，重启要重建索引，重建索引期间全文检索不可用。

3.2 建表与造数据

建表语句我简化了一下，核心是文本字段加全文索引：

注意 WITH PARSER ngram 这个子句，不加的话全文索引对中文无效。这是最容易漏掉的一步。

造 50 万行测试数据，我用的是存储过程循环插入，模拟真实文章内容：

50 万行插完大概花了 8 分钟，这个速度可以接受。真实业务数据内容更复杂，但结构类似。

3.3 全文索引构建耗时与体积观察

建完数据后加全文索引，这一步的耗时很关键，因为它直接决定了上线时的停机窗口：

实测下来，50 万行、每行 200 字，建索引耗时约 95 秒。索引体积方面，原表数据约 380 MB，全文索引额外占了约 210 MB，膨胀比大概 1.55 倍。这个体积增长在可接受范围内。

对比一下，如果上 ES，同样 50 万行数据，光是 JVM 堆内存就得预留 2 GB 以上，再加上索引本身的存储，资源占用完全不是一个量级。这也是我坚持先测 ngram 的原因——能用轻量方案解决的，就别上重型架构。

4. 查询性能对比：LIKE vs MATCH AGAINST

4.1 三种查询写法的实测数据

我在同一张 50 万行表上跑了三组查询，每组跑 10 次取平均值，结果如下：

查询方式	SQL 示例	平均响应时间	是否走索引
LIKE 前置通配	WHERE content LIKE '%性能优化%'	1180 ms	否
LIKE 后置通配	WHERE content LIKE '性能优化%'	320 ms	部分
ngram 全文索引	WHERE MATCH(content) AGAINST('性能优化')	45 ms	是

差距非常明显。ngram 全文索引比前置通配的 LIKE 快了 26 倍，比后置通配也快了 7 倍。而且数据量越大，这个差距越夸张，因为 LIKE 是全表扫描，复杂度是 O(n)，而全文索引走倒排，接近 O(log n)。

4.2 自然语言模式与布尔模式的选择

MySQL 全文检索有两种模式，用错了效果差很多：

自然语言模式（默认）：

它会把查询词切片后求相关性得分，适合“搜索引擎式”的模糊匹配。

布尔模式：

布尔模式支持 +（必须包含）、-（必须排除）、*（通配）等操作符，适合精确控制查询逻辑。

我的经验是：面向用户的搜索框用自然语言模式，后台的精确筛选用布尔模式。自然语言模式容错性好，用户输入“数据库优化方案”也能匹配到相关内容；布尔模式则适合“必须包含 A 且不能包含 B”这种硬性条件。

4.3 相关性排序的坑

自然语言模式会返回一个相关性得分，很多人想按这个得分排序：

这里有个坑：全文索引的相关性得分计算跟文档长度强相关。短文档里出现关键词，得分会偏高；长文档里出现同样的词，得分会被稀释。这导致排序结果有时候不符合直觉。

我的处理办法是，如果业务对排序要求高，不要完全依赖 MySQL 的 score，可以在应用层做二次加权，比如结合发布时间、点击量等业务字段综合排序。MySQL 的 score 只作为一个粗排依据。

5. 实操中的性能调优与参数计算

5.1 ngram_token_size 的量化选择

前面说 ngram_token_size = 2 是甜点值，这里给出量化依据。假设文本平均长度 L，token_size 为 N，那么每行产生的 token 数量约为 L - N + 1。

以 L=200、N=2 计算，每行约 199 个 token；N=1 时是 200 个，N=3 时是 198 个。看起来差别不大，但倒排索引的条目数差异很大：

• N=1：每个字都建索引，常用字（如“的”“是”）会出现在几乎所有文档里，倒排链极长，查询时求交集慢。
• N=2：双字组合数量远大于单字，倒排链更短，查询更快。
• N=3：召回率下降，很多双字词搜不到。

所以 N=2 是在召回率和性能之间的最佳平衡点。这个结论我在多个数据集上验证过，基本稳定。

5.2 索引字段的拆分策略

我一开始把 title 和 content 放在一个全文索引里，后来发现有问题：标题的权重应该高于正文，但合在一起索引没法区分。

改进方案是拆成两个全文索引：

查询时分别匹配，然后在应用层加权：

标题权重乘 2，是我根据业务反馈调出来的经验值。你可以根据自己业务调整，核心思路是让重要字段的得分占比更高。

5.3 查询缓存与冷热数据分离

全文索引查询也有冷热之分。热数据（最近常查的关键词）可以走应用层缓存，比如 Redis 缓存查询结果，设置 5 分钟过期。冷查询才落到 MySQL。

我实测过，加了应用层缓存后，整体 QPS 提升了 3 倍以上。因为搜索场景有明显的长尾效应，20% 的关键词覆盖了 80% 的查询量，缓存命中率很高。

注意：缓存 key 要包含查询模式和分页参数，不然会串结果。比如 search:natural:性能优化:page1 和 search:boolean:+性能+优化:page1 必须区分开。

6. 常见问题与排查技巧实录

6.1 搜不到结果的五种原因

这是我最常被问到的问题：“明明数据里有这个词，为什么 MATCH AGAINST 搜不到？”排查下来，原因基本集中在这几类：

问题现象	根本原因	解决方法
中文完全搜不到	建索引时没加 WITH PARSER ngram	重建索引，加上解析器
短词搜不到	token_size 大于词长	调小 ngram_token_size 并重建
部分词搜不到	停用词过滤	关闭 innodb_ft_enable_stopword
改了参数没效果	索引未重建	执行 OPTIMIZE TABLE 或重建索引
布尔模式语法错	操作符使用不当	检查 +、-、* 的位置和转义

我印象最深的一次，是改了 ngram_token_size 之后忘了重建索引，查询结果跟预期完全对不上，排查了两个小时才发现是索引没更新。记住：全文索引的参数变更，必须重建索引才生效。

6.2 全文索引与事务的配合

InnoDB 的全文索引在事务提交时才更新，这意味着同一事务内插入的数据，在事务提交前查不到。这个特性在批量导入场景下要特别注意。

如果你在做数据同步，比如从别的库批量导入，建议分批提交，每批 1000 行左右。批太大，全文索引的更新会拖慢事务；批太小，整体吞吐上不去。1000 行是我测下来比较均衡的值。

6.3 什么时候真的该上 ES

说了这么多 ngram 的好处，也得说清楚它的边界。以下场景，我建议还是上 ES：

• 数据量超过 500 万行：ngram 索引体积和维护成本开始变得不划算。
• 需要复杂聚合分析：比如按关键词分组统计、多维筛选，ES 的聚合能力远超 MySQL。
• 需要近实时搜索：ES 的刷新间隔可以做到 1 秒，MySQL 全文索引有事务提交延迟。
• 需要跨库跨表联合搜索：ES 天然适合做统一搜索层。
• 需要分词器定制：比如专业领域词典、同义词扩展，ES 的 analyzer 生态更成熟。

分界线大概在百万行级别。低于这个量级，ngram 全文索引完全够用；高于这个量级，再考虑 ES 不迟。别一上来就堆架构，先把 MySQL 的能力榨干。

7. 我在这次实测中的几点真实体会

第一个体会是，技术选型要看数据量级，不要看技术热度。ES 确实强大，但它的强大是有代价的。50 万行的数据，用 ngram 全文索引 45 毫秒就能出结果，非要上 ES，多出来的运维成本和资源开销完全不值。

第二个体会是，参数调优要基于实测，不要凭感觉。ngram_token_size 到底设 2 还是 3，网上的说法五花八门，但只有在自己数据上跑一遍才知道哪个合适。我建议你也搭个小环境，用真实数据测一轮，比看十篇文章都管用。

第三个体会是，索引重建这个环节一定要提前规划。全文索引重建期间查询会退化，生产环境要做灰度切换。我的做法是先在从库建好索引，再切流量，避免影响线上。

最后分享一个实用技巧：如果你的搜索场景里，用户输入的关键词经常是短语（比如“数据库性能优化方案”），可以在应用层先把短语拆成多个双字词，再用布尔模式的 + 组合查询，召回率和准确率都会更好。这个预处理逻辑不复杂，但效果立竿见影。