如何优化 like '%keyword%' 查询避免全表扫描使用全文索引

LIKE '%keyword%' 无法利用普通 B+ 树索引，必然导致全表扫描；优化方案是改用全文索引（需配置 ngram 解析器支持中文）或通过生成列反转字符串建立索引。

先说结论：LIKE 左侧带通配符时 B+ 树索引失效，中文场景需启用 ngram 全文索引或建立反转列索引。

先定位：使用 EXPLAIN 确认查询类型为 ALL 且 key 为 NULL。
先做：根据场景选择全文索引（适合自然语言搜索）或反转列索引（适合固定后缀匹配）。
再验证：再次执行 EXPLAIN 确认索引命中，并检查查询响应时间。

命令速用版

以下是两种主流优化方案的快速实施命令，根据业务场景选择其一。

方案一：启用 ngram 全文索引（适合中文内容搜索）

-- 1. 确认 ngram 插件已加载
SHOW PLUGINS;
-- 2. 创建表时指定 parser 或重建索引
ALTER TABLE logs ADD FULLTEXT(content) WITH PARSER ngram;
-- 3. 查询语法（关键词长度需≥ngram_token_size）
SELECT * FROM logs WHERE MATCH(content) AGAINST('错误日志' IN NATURAL LANGUAGE MODE);

方案二：反转列索引（适合后缀匹配如邮箱域名）

-- 1. 新增反转列
ALTER TABLE users ADD COLUMN email_reversed VARCHAR(255);
-- 2. 创建触发器自动维护反转值
CREATE TRIGGER trg_email_rev BEFORE INSERT ON users FOR EACH ROW SET NEW.email_reversed = REVERSE(NEW.email);
-- 3. 在反转列建索引
CREATE INDEX idx_email_rev ON users(email_reversed);
-- 4. 查询时将关键词反转并使用前缀匹配
SELECT * FROM users WHERE email_reversed LIKE 'moc.liamg%';

为什么会这样

B+ 树索引按字典序从左到右排序，仅支持前缀匹配，无法定位左侧带通配符的查询起点。

当使用 LIKE '%keyword%' 时，数据库无法利用索引的有序性确定扫描起始位置，只能逐行读取并判断，导致 type: ALL 全表扫描。即使字段上有普通索引，优化器也会直接放弃。全文索引通过倒排索引机制解决此问题，但 MySQL 原生全文索引对中文支持需依赖 ngram 插件进行分词，否则会将整段中文视为一个词导致匹配失败。

分步处理

步骤 1：确认当前索引失效情况

适用场景：所有 LIKE 模糊查询优化前。

操作动作：执行 EXPLAIN SELECT * FROM table WHERE field LIKE '%keyword%'。

验证结果：若 type 显示为 ALL 且 key 显示为 NULL，确认索引未生效。

风险边界：无。

步骤 2：选择优化方案并实施

适用场景：中文文本搜索选全文索引；固定后缀匹配选反转列。

操作动作：若选全文索引，需在配置文件中添加 ft_parser=ngram 并重启，或建索引时指定 WITH PARSER ngram。若选反转列，需新增列并配置触发器同步数据。

验证结果：索引创建成功，无报错。

风险边界：全文索引需重建表；反转列需确保触发器与主字段事务一致。

步骤 3：调整查询语句

适用场景：索引创建完成后。

操作动作：全文索引查询必须用 MATCH() AGAINST() 语法；反转列查询需对关键词使用 REVERSE() 函数处理后再拼接 %。

验证结果：SQL 语法执行无误。

风险边界：全文索引关键词长度需≥ngram_token_size（默认 2），查单字会失败。

怎么验证是否生效

再次执行 EXPLAIN 分析优化后的查询语句。

对于全文索引查询，type 列应显示为 fulltext，key 列显示创建的全文索引名。对于反转列查询，type 列应显示为 range 或 ref，key 列显示反转列索引名，且 rows 扫描行数应显著低于全表行数。

同时观察查询响应时间，在数据量较大时，优化后的查询应避免出现秒级以上的延迟。

常见坑

全文索引中文分词失败：未指定 WITH PARSER ngram 会导致中文整段被视为一个词，查询返回空结果。
关键词长度限制：ngram_token_size 默认为 2，查询单字如'错'会失败，需调整配置或避免单字搜索。
反转列查询写法错误：查询时若仍写 LIKE '%keyword' 或在列上使用函数 REVERSE(email)，索引会再次失效。
大小写敏感问题：若排序规则为 utf8mb4_0900_as_cs，反转列大小写不一致会导致查不到数据，需统一大小写策略。
多字段 OR 查询失效：多字段 LIKE 查询使用 OR 连接可能导致优化器放弃索引，建议改用 UNION ALL 分别查询。

常见问题

单字搜索为什么全文索引查不到？

因为 ngram 插件默认 token_size 为 2，单字无法构成有效索引项。

反转列方案支持中缀模糊查询吗？

不支持，反转列仅能将后缀匹配转化为前缀匹配，无法解决 '%keyword%' 中缀问题。

MybatisPlus 中多字段模糊查询如何优化？

避免使用 OR 连接多个 LIKE 条件，建议拆分为多个单独查询后用 UNION ALL 合并结果。

全文索引会影响写入性能吗？

会，全文索引需要在写入时维护倒排索引，高并发写入场景下需评估开销。

参考来源

LIKE'%keyword%'导致全表扫描的全文索引 / ngram 替代方案
如何优化 MySQL 中的 Like 后缀匹配 (%keyword) 查询_利用反转字符串索引技巧
mysql 如何优化 LIKE 全模糊查询的性能_全文索引 FULLTEXT 应用
MySQL 索引优化实战避免全表扫描的 10 个关键策略
MybatisPlus 模糊查询性能优化：当 like 遇上多值匹配，如何避免全表扫描？