300 万行数据 × 2 万个字符串模糊匹配：一次"绕开 MySQL"的批量查询优化实录

当 LIKE '%xxx%' 遇上 2 万个关键词，单条查询 30 秒、全量跑完要一周 —— 换一种思路，把匹配交给 grep，10 分钟搞定。

一、问题背景

某业务表 data 数据量 300 万+，共 22 个字段，MySQL 版本 5.6（不支持在线加全文索引等新特性）。核心需求是：

给出 2 万个指定字符串，找出表中 cname 字段包含其中任意一个字符串的所有数据行。

表结构示意（只列出关键字段）：

CREATE TABLE `data` (
  `id`       bigint(20) NOT NULL AUTO_INCREMENT,
  `cname`    varchar(50) DEFAULT NULL COMMENT '关键字段',
  -- ... 其余 19 个字段省略
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

关键词列表（约 2 万个）：

# /tmp/keys.txt，每行一个
abc
foo_bar
hello world
...

二、为什么不能直接在 SQL 里做？

最直觉的写法是：

SELECT * FROM `data` WHERE cname LIKE '%abc%';

实测单条查询耗时约 30 秒。我们来算笔账：

方案	耗时估算
单条 LIKE 查询	~30 秒
2 万个关键词逐个跑	30s × 20000 ≈ 166 小时（约 7 天）
2 万个关键词 OR 拼接	SQL 超长、执行计划爆炸，无法落地

问题的根源在于：LIKE '%xxx%' 无法命中 B+ 树索引，只能全表扫描。300 万行 × 2 万次扫描，等于把整张表扫了 2 万遍，时间自然是指数级的。

还有人会想到 REGEXP 或全文索引，但：

• MySQL 5.6 的 REGEXP 同样不走索引，2 万个 pattern 拼接后性能更差；
• FULLTEXT 索引做的是分词匹配，对"包含任意子串"这种需求无能为力（abc 可能是 xxabcxx 的中间片段）。

三、破局思路：把问题从"数据库"搬到"文本处理"

核心洞察：匹配逻辑本身不复杂，只是"在 300 万个字符串里找包含 2 万个关键词的"，这正是 grep 的看家本领。

方案整体分四步：

① 导出数据为 TSV（纯文本）
        ↓
② 准备关键词文件（每行一个）
        ↓
③ grep -F -f 一次完成 2 万 × 300 万 的匹配
        ↓
④ 把结果导回 MySQL 建临时表

第 1 步：导出全表数据为 TSV

mysql -h host -u user -p \
  --default-character-set=utf8mb4 \
  --quick \
  -B \
  -e "SELECT * FROM data" \
  > /tmp/data.tsv

参数说明：

• -B：--batch 模式，输出制表符分隔的纯文本（TSV），无表格框线、无表头；
• --quick：强制流式输出，不缓存整个结果集。300 万行数据如果一次性缓冲进内存，客户端很容易 OOM，加了这个参数可以边查边写文件；
• --default-character-set=utf8mb4：与表字符集保持一致，避免中文乱码。

第 2 步：准备关键词文件

把 2 万个指定字符串放入 /tmp/keys.txt，每行一个：

# 用换行符直接分隔即可
abc
foo_bar
hello world

第 3 步：grep 一次完成全部匹配

grep -F -f /tmp/keys.txt /tmp/data.tsv > /tmp/matched.tsv

这里有两个关键参数，缺一不可：

参数	作用
-F	把关键词当固定字符串（fixed string）处理，不做正则解析。2 万个 pattern 如果走正则引擎，慢且容易出错（关键词里若含 .、* 等符号会被误解义）
-f	从文件读取 pattern 列表，一次加载 2 万个关键词，一遍扫描 300 万行全部匹配完

为什么快？grep 内部会把这 2 万个固定字符串编译成 Aho-Corasick 自动机，扫描一遍输入的同时匹配所有模式，复杂度与"匹配 1 个关键词"几乎相同。这正是 2 万 × 300 万组合拳的底气所在。

第 4 步：结果导回 MySQL

先克隆表结构，再批量导入：

CREATE TABLE tmp_matched LIKE `data`;

LOAD DATA LOCAL INFILE '/tmp/matched.tsv'
  INTO TABLE tmp_matched
  CHARACTER SET utf8mb4
  FIELDS TERMINATED BY '\t'
  LINES TERMINATED BY '\n'
  (id, cname, ...);  -- 建议显式列出全部 22 个字段，避免列错位

导入完成后，tmp_matched 就是你要的"包含任意指定字符串"的命中数据，后续业务直接查这张表即可。

四、还可以再快一点：进阶优化

优化 1：只导出需要的列

如果后续只用到部分字段，SELECT * 换成指定列，文件体积和 IO 时间都能砍掉大半：

mysql ... -e "SELECT id, cname, other_col FROM data WHERE cname IS NOT NULL" > /tmp/data.tsv

顺带用 WHERE cname IS NOT NULL 排除空值行，减少无效扫描。

优化 2：grep 加速三板斧

# 1) 用 LC_ALL=C 跳过 locale 处理（纯 ASCII 关键词时收益明显）
LC_ALL=C grep -F -f /tmp/keys.txt /tmp/data.tsv > /tmp/matched.tsv

# 2) 按行拆文件，多进程并行
seq 1 8 | xargs -P 8 -I{} sh -c \
  "awk 'NR % 8 == {}' /tmp/data.tsv | LC_ALL=C grep -F -f /tmp/keys.txt >> /tmp/part_{}.tsv"
cat /tmp/part_*.tsv > /tmp/matched.tsv

优化 3：换用 ripgrep（强烈推荐）

grep 已经够快，但如果追求极致，直接上 rg——Rust 编写的现代搜索工具，天然多线程、走 SIMD 优化，对 300 万行的文件通常是毫秒级：

rg -F -f /tmp/keys.txt /tmp/data.tsv > /tmp/matched.tsv

一行替换，无需改其他逻辑。数据量越大、关键词越多，速度优势越明显。

优化 4：验证与兜底

• 关键词文件注意去重、去空行，避免重复匹配和全行误命中（空 pattern 会匹配所有行）；
• 数据中若含 \t 或换行符，TSV 导入会错位，建议导出前用 REPLACE() 清洗或改用 FIELDS ENCLOSED BY '"'；
• 结果量很大时，导入前先 TRUNCATE tmp_matched，防止重复导入数据翻倍。

五、方案对比总结

方案	预估耗时	优点	缺点
2 万条 LIKE 逐个查	~7 天	实现最简单	时间不可接受
2 万关键词 OR 拼接	无法落地	—	SQL 超长、执行计划爆炸
全表导出 + grep	分钟级	一次扫描全匹配、内存占用可控、逻辑透明	需要磁盘空间存放 TSV（约与表数据等量）
全表导出 + ripgrep	秒~分钟级	多线程 SIMD，最快	需安装 rg（brew install ripgrep）

几点注意事项

1. 磁盘空间：300 万行 × 22 字段的 TSV 可能数 GB，导出前确认 /tmp 空间充足；
2. 导出与匹配期间的数据一致性：grep 看到的是导出那一刻的快照，若业务在持续写入，需评估数据时效性是否可接受；
3. 本质是"换工具"而非"换思路"：当单条 LIKE 全表扫描无法避免时，把匹配从数据库引擎挪到专用文本匹配工具，用空间换时间，往往比在 SQL 里硬扛高效得多。

---

延伸思考：如果业务是长期、高频的"包含匹配"需求，更稳妥的长期方案是在业务侧引入 ES（Elasticsearch） 或 MySQL 8.0 的 FULLTEXT（配合合适的分词器），把"匹配"做成索引而非扫描。grep 方案适合一次性、低频、批量的离线场景，两者定位不同，可以按需选用。

---

本文方法适用于 MySQL 5.6 / 5.7 等不支持新特性的老版本数据库。如果你有更好的批量匹配方案，欢迎在评论区交流。