先把结论放在前面：在 InnoDB 的 B+ 树索引里，LIKE '%关键词%'这个写法让索引失效的根因，是它破坏了从根节点逐层定位的能力，而不是 MySQL 傻。想让字段两边都能加%还能用上索引，现实情况中主要有几条路可以走：改查询条件、建覆盖索引、上全文索引、用反向生成列。今天这篇就把这几条路的原理、建索引姿势和实际的踩坑点说清楚。
上周同事拿着一张执行计划截图来找我，满脸困惑：表里 name 字段明明建了索引，怎么WHERE name LIKE '%张%'的时候 EXPLAIN 还是type=ALL，rows 十几万，拖了好几秒？他说网上搜了一圈，答案都是“前导 % 会导致索引失效”，可业务上就是需要前后都能匹配，总不能改需求。这个问题太典型了，凡是有后台列表搜索、标签筛选、模糊查询场景的人，基本都遇到。我前后试过覆盖索引绕、全文索引、反向生成列各种办法，今天把能实操落地的几条路掰开揉碎讲一遍。
1. 先搞清楚：LIKE '%关键字%' 为什么会被 MySQL 抛弃索引
1.1 B+ 树的左前缀依赖：索引不是字典，是纸质的偏旁部首目录
InnoDB 的二级索引底层是 B+ 树，数据在叶子节点里按照索引列的值排序存储。B+ 树能加速的前提，是你能提供一个“确定的前缀范围”，比如查name LIKE '张%'，MySQL 可以从树的根节点开始，找到“张”字区间的左边界，再顺着叶子节点的链表向右扫，只读取一小段索引页。这个操作叫 range scan 或者 index range scan，效率很高。
但LIKE '%张%'没有给出任何起始字符，理论上任何位置都有可能以“张”开头，B+ 树无法知道应该从哪个叶子节点开始扫。如果非要走索引，只能从第一个索引键值开始，把所有叶子节点全部扫一遍，再逐行回表判断。MySQL 优化器计算成本之后，往往直接选择全表扫描，理由是“扫全表比扫索引再回表更便宜”。
你可以把 B+ 树索引理解成一本按偏旁部首排列的字典：查“张”开头的字很容易，翻到对应的偏旁目录就行；但查“第二个字是张”的词语，你就只能整本翻。这不是 MySQL 的缺陷，是 B+ 树这种数据结构天然带“左前缀依赖”。
1.2 explain 实测：没用索引之前长什么样
空口无凭，先看一个常见反例。假设表结构如下：
CREATE TABLE `user` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `email` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name` (`name`) ) ENGINE=InnoDB;当我们执行：
EXPLAIN SELECT * FROM user WHERE name LIKE '%张%';得到的结果大概率是：
| id | select_type | table | type | possible_keys | key | rows | Extra |
|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | user | ALL | NULL | NULL | 98567 | Using where |
type=ALL代表全表扫描，possible_keys明明显示了idx_name，但优化器没有使用它。原因很简单：在这个查询里，即使扫描整个二级索引，也只是找到一批主键值，还需要回表读取完整数据，成本比顺序读全表还要高。所以 MySQL 做了一个“聪明”的决定：扫表。
1.3 别把 ICP 和覆盖索引混为一谈
很多文章提到Index Condition Pushdown，也就是索引条件下推，说 MySQL 5.6 之后能把 LIKE 条件下推到存储引擎层，从而加速模糊查询。这句话本身没毛病，但有个前提：查询必须已经开始走索引。如果type=ALL，ICP 一点忙都帮不上，存储引擎压根没有机会在索引上过滤。
还有人会问：那我建一个(name, age, email)联合索引，LIKE '%张%'会不会走？答案是有可能走type=index，也就是全索引扫描。这个“index”不是定位到某个范围，而是遍历了整棵索引树，但因为它包含了查询需要的列，不需要回表，所以被称为“覆盖索引扫描”。很多人容易把type=index误以为“索引生效了”，其实它只是从全表扫变成了全索引扫，本质没有缩小检索范围。但正因如此，它成了第一个可落地的优化方案。
2. 方案一：用覆盖索引把全表扫描骗成索引全扫描
2.1 覆盖索引的原理与成立条件
覆盖索引指查询执行时，不需要通过二级索引回表，就能拿到全部目标字段。InnoDB 的二级索引叶子节点除了索引列，还包含主键值。假如你只查询SELECT name FROM user，并且name本身就是索引列，那优化器可以直接通过扫描idx_name得到结果，全程不回表。此时即使LIKE '%张%'扫的是整棵索引树，但索引树通常比聚簇索引表小很多，而且不需要随机 IO 回表，整体成本有可能比全表扫更低，优化器就会选择type=index。
“骗”这个词不够准确，更准确地说，是在不改变数据结构的前提下，把成本曲线压到让优化器甘愿选索引的程度。覆盖索引的成立条件很严格：查询结果列必须被索引覆盖，查询条件列也必须是索引列的一部分。如果 SQL 里有SELECT *，优化器发现光靠索引拿不到完整行，大概率直接放弃。
2.2 一个可行的建索引姿势：把要查的字段全部塞进索引
继续用上面那张user表举例。假设业务只需要展示用户的 id 和 name，搜索还是name LIKE '%张%'，可以这样建索引：
ALTER TABLE `user` ADD INDEX idx_name_id(name, id);注意，id本来就是主键，二级索引已经隐式包含主键，所以理论上只建立idx_name(name)就够了。这里我们目标是覆盖：
EXPLAIN SELECT id, name FROM user WHERE name LIKE '%张%';结果会变成类似这样：
| id | select_type | table | type | possible_keys | key | rows | Extra |
|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | user | index | idx_name | idx_name | 98567 | Using where; Using index |
type=index表示“扫描全部索引页”，Extra=Using index表示不需要回表。相比之前的ALL，虽然 rows 没有变小，但内存和 IO 的开销通常明显下降。这个方案最大的优点：不用改业务 SQL，能兼容LIKE '%张%'。
实测里，如果表数据量压在几百万行以内，且查询列只有两三个，这种方案的性能完全能接受，响应时间可能从几秒降到几百毫秒。但注意，它没有从本质上减少“需要检查的行数”，所以当表数据量进一步膨胀到千万行，性能依然会崩。
2.3 这个方案的硬限制：不能 select *，也不能范围任性
覆盖索引的硬伤有两个。第一是“列不要太多”。如果你把 name、email、age、address 全部塞进索引，索引体积可能已经非常接近数据表本身，扫描索引的成本不会比扫描全表低多少，更新索引的代价反而变大。第二是“不能SELECT *”。如果业务就是要展示所有字段，想用覆盖索引又不想回表，基本不可能，除非你建一个超级宽的索引，把所有字段都塞进去，但这在绝大多数场景下不是合理设计。
我见过有同事为了优化一个后台导出功能，把一张 20 多个字段的表所有列都加进索引，结果写入直接变慢 3 倍。等业务需求变更加个字段，又要改索引。所以覆盖索引更适合“高频搜索但只展示少数关键字段”的场景，比如用户列表页只显示 id、name、手机号。
3. 方案二：全文索引才是真正意义上支持两边 % 的解法
3.1 全文检索为什么能支持任意位置命中
如果你需要搜索的是文章正文、备注、描述这类大字段，覆盖索引完全不够用，因为字段太大不能无限塞索引。这时要上全文索引。全文索引的核心是倒排索引，存储的是一张“关键词 -> 文档列表”的映射表。例如把“MySQL 索引优化”分词成“MySQL”、“索引”、“优化”，再记录每个词出现在哪些文档中。你搜索“索引”时，直接查倒排表就能拿到包含该词的文档 ID 列表，和这个词出现在文档的第几个字完全无关。
所以全文索引和LIKE '%索引%'从查询语义上有本质区别：LIKE 是字符串子串匹配，全文索引是分词匹配。但正因为分词把文档拆成了词粒度的单元，它能做到任意的词位置命中，如果你接受这种语义差异，它就是解决“两边 %”的正规解法。
3.2 MySQL 全文索引建法与 MATCH AGAINST 用法
在 InnoDB 上创建全文索引非常简单，以article表为例：
CREATE TABLE `article` ( `id` int NOT NULL AUTO_INCREMENT, `title` varchar(200) NOT NULL, `content` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB; ALTER TABLE article ADD FULLTEXT INDEX ft_title_content(title, content);查询时用MATCH ... AGAINST代替LIKE：
SELECT id, title FROM article WHERE MATCH(title, content) AGAINST('数据库' IN NATURAL LANGUAGE MODE);还可以用布尔模式做更精细的控制：
SELECT id, title FROM article WHERE MATCH(title, content) AGAINST('+数据库 -入门' IN BOOLEAN MODE);上面这条命令的含义是：必须出现“数据库”，且不能出现“入门”。这种精确控制是原生LIKE做不到的。需要注意的是，全文索引默认只对长度超过一定阈值的词进行索引，在 InnoDB 上默认innodb_ft_min_token_size=3，如果搜索“AB”这种短词，可能直接被忽略，需要在配置里调整。
3.3 中文场景必须注意 ngram 解析器和分词粒度
全文索引英文文本很顺利，因为空格天然分词。到了中文，整段文字没有空格，如果直接用默认解析器，MySQL 会把整个中文句子当成一个 token，搜索根本命中不了。解决办法是指定ngram解析器：
ALTER TABLE article ADD FULLTEXT INDEX ft_content(content) WITH PARSER ngram;ngram会按照连续 N 个字符进行切分。配置项ngram_token_size默认是 2，也就是“数据库”会被切成“数据”、“据库”。如果你要搜索单字，比如“张”，就必须把ngram_token_size设为 1。这个参是 MySQL 启动参数，不是 session 级别能随便改的，需要在 my.cnf 中提前规划：
[mysqld] ngram_token_size=1改成 1 之后索引体积会膨胀很多，因为单字分词产生的 token 数量远大于双字。如果你的业务大多搜成语、专业词，双字粒度反而更好；如果业务上有单字搜索的刚需，就得接受存储开销。
3.4 全文索引的缺点：性能波动和相关性排序
全文索引最大的问题是：它不是银弹。首先，倒排索引的维护成本很高，频繁插入、更新文本会让全文索引的重建开销非常明显。其次，MATCH AGAINST的排序基于全文相关度（词频、逆文档频率），可能给用户一种“明明包含关键词，却排到后面”的感觉；如果业务要求严格按时间排序，你还需要显式ORDER BY create_time，这回让性能打折扣。此外，全文索引不处理停用词，比如英文“the”、“is”，默认会被过滤，中文 ngram 没有停用词问题，但标点符号需要额外处理。
所以我的建议是：对短文本、枚举值、代码片段，不要盲目上全文索引；对长文本搜索场景，不要用 LIKE 硬扛，全文索引值得适当引入，但前提是接受分词语义差异。
4. 方案三：反向生成列——专门优化后缀匹配
4.1 思路：把后缀匹配翻转为前缀匹配
前面提到 B+ 树只擅长前缀匹配。那如果业务上要求“名字以某个字结尾”呢？例如查询姓名结尾是“明”的人，SQL 一般是WHERE name LIKE '%明'，后缀匹配同样无法走索引。解决思路很朴素：既然 B+ 树只能快速锁定开头，那我把字段反转过来存，让结尾变成开头。
比如name='张明'，REVERSE(name)得到'明张'。原来想查LIKE '%明'，现在可以查reversed_name LIKE '明%'。因为'明%'是标准前缀匹配，完全满足 B+ 树的左前缀依赖，索引自然就生效。同理，如果是既要前缀匹配又要后缀匹配的组合场景，可以分别建普通索引和反向列索引，两个条件各走各的最优路径。
4.2 用生成列 + 索引建出来的完整示例
MySQL 5.7 以后支持生成列，MySQL 8.0 对生成列索引的支持也更完善。可以不要维护冗余列，让数据库自动维护反转结果：
CREATE TABLE `customer` ( `id` int PRIMARY KEY, `name` varchar(50) NOT NULL, `rev_name` varchar(50) GENERATED ALWAYS AS (REVERVE(`name`)) VIRTUAL ); ALTER TABLE `customer` ADD INDEX idx_rev_name(rev_name);需要注意语法是REVERSE不是REVERVE，拼写别错。生成列可以选择 VIRTUAL 或 STORED，索引可以建立在 VIRTUAL 列上，这样不会额外占表空间，只是索引本身会占空间。然后查询就可以这样写：
-- 等价于 name LIKE '%明' SELECT * FROM customer WHERE rev_name LIKE '明%';如果还想同时匹配“前缀是张”和“后缀是明”，就合并条件：
SELECT * FROM customer WHERE name LIKE '张%' OR rev_name LIKE '明%';这条语句虽然没法在一个索引上同时加速两个分支，但每个分支都能各用各的索引，最终走index_merge，通常比全表扫描快得多。
4.3 这个方案能做什么，不能做什么
必须强调：反向生成列并不能让“任意位置包含”走索引。如果你查询LIKE '%张%'，反转后变成LIKE '%张%'，前导 % 还在，照样失效。它只对后缀匹配有效，比如“以某个字结尾”的查询。因此它更适合这些场景：身份证号后几位匹配、订单号后缀查询、姓名末字搜索、手机号尾号搜索等。
另外，如果你本身要查的是“包含某关键词”，但关键词在字段中可能出现的位置毫无规律，反向生成列也帮不上忙。此时还是老老实实考虑全文索引，或者接受全表扫描并配合缓存、分页、数据归档等手段缓解。永远不要神化某个技巧，先问业务需要的是“前缀、后缀，还是任意位置”。
5. 按业务选型：不是所有查询都值得上重型方案
5.1 三个方案的适用场景与资源开销对比
为了方便直接对比，我把三种方案放进一张表：
| 方案 | 核心原理 | 典型查询 | 资源开销 | 适用场景 |
|---|---|---|---|---|
| 覆盖索引 | 二级索引免回表扫描 | SELECT name FROM user WHERE name LIKE '%张%' | 索引体积可控，写放大一般 | 数据量百万级以内，查询列少 |
| 全文索引 | 倒排索引分词匹配 | MATCH(title, content) AGAINST('数据库') | 倒排表存储与 DML 开销大 | 长文本、搜索场景 |
| 反向生成列 | 后缀转前缀 | rev_name LIKE '明%' | 生成列+新建索引，开销低 | 明确是后缀匹配 |
5.2 选型决策表：根据数据量、查询特点、更新频率挑选
实际操作中，可以按下面的流程快速决策：
- 如果查询结果是只需少量字段，且数据量在百万级以内，优先试覆盖索引。零业务改动，收益最直接。
- 如果字段是长文本（文章正文、备注、描述），使用 LIKE 进行任意位置匹配再叠加
%，直接考虑全文索引，不要心疼存储。 - 如果查询本质是“后缀匹配”，无论长短，反向生成列都是成本和收益最平衡的方案。
- 如果数据量特别大（几千万行以上），MySQL 本身再做索引优化也有限，建议上专门的搜索引擎，但这已经不在本文讨论范围。
- 如果搜索频率很低，比如后台每天就查询几次，全表扫描也只要一两秒，那就不要折腾索引，保持业务简单。
5.3 我踩过的几个坑：查询排序、区分大小写、复合索引顺序
第一，覆盖索引和排序不一定兼容。假设你建了idx_name(name, create_time)，但查询是WHERE name LIKE '%张%' ORDER BY create_time DESC。覆盖索引可能命中，但ORDER BY create_time的顺序正好和索引顺序相反，MySQL 可能还要走 filesort，Extra里会出现Using filesort。这时需要把排序字段的方向也考虑进去，或者业务上换一种排序方式。
第二，全文索引的MATCH AGAINST匹配经常受排序规则影响。如果表的字符集是utf8mb4_unicode_ci，某些中文词汇相关度计算结果会和预期不一致。建议在开发环境先在真实数据上测试几个用例，不要刚上线就被运营反馈“搜索排序不对”。
第三，反向生成列建索引时要小心key_len的变化。VARCHAR加上排序规则和反转移都可能影响索引长度，比如utf8mb4一个字符最多占 4 字节，VARBINARY和VARCHAR的 key_len 差异很大。建索引后用EXPLAIN看一眼 key_len，确认它确实被用上，而不是被隐式函数转换废掉。
第四，不要对只有几十行的小表搞全套优化。优化器有自己的成本模型，数据量太小它宁可扫表，索引建了也是摆设。DBA 最怕的不是慢查询，而是“为了优化而优化”的过度设计。
6. 实操调优：从 explain 到慢查询日志的验证路径
6.1 如何验证索引是否真的没失效
任何优化完成后，不能只看执行时间，要用EXPLAIN验证执行计划。重点关注四个字段：
type：至少要达到index，理想是range或ref，ALL代表全表扫描。key：不能是NULL，必须显示实际使用的索引名。rows：估算扫描的行数，越小越好。Extra：出现Using index说明有覆盖索引加成；出现Using filesort要考虑排序优化；出现Using temporary要小心临时表。
还可以用EXPLAIN FORMAT=JSON看更细的成本数据：
EXPLAIN FORMAT=JSON SELECT id, name FROM user WHERE name LIKE '%张%';JSON 输出中的cost_info能告诉你优化器估算的全表扫描成本是多少、索引扫描成本是多少，这比只看 type 更接近真相。
6.2 慢日志与 profiling 定位细微性能差距
EXPLAIN只是优化器的估算，真实执行时间还是要靠 profile。开启 profiling 后，可以精确看到每个阶段耗时：
SET profiling = 1; SELECT id, name FROM user WHERE name LIKE '%张%'; SHOW PROFILES;SHOW PROFILES会返回查询的 Query_ID 和 Duration。之后执行：
SHOW PROFILE FOR QUERY 1;可以进一步看到 Sending data、Statistics、Creating sort index 等阶段的耗时占比。生产环境不建议长时间开着 profiling，但压测或者排查单条慢 SQL 时非常有用。
结合慢日志也可以做长期观察。比如开启慢查询日志：
SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1;后续超过 1 秒的查询都会记录下来，再用mysqldumpslow -s t聚合相同的慢 SQL，看优化后是否真的从慢日志里消失。
6.3 如果实测仍然走全表扫描，优先排查这几件事
第一种情况是索引没有真正创建成功。用SHOW INDEX FROM user查看索引列表，确认索引名、列名、顺序没有问题。第二种情况是字符集不一致。表字段是utf8mb4，连接字符集是latin1，MySQL 会在索引列上做隐式转换，导致索引失效。典型的例子是手写 SQL 时习惯在数字字段加引号，比如WHERE phone = '13800000000'可能无碍，但WHERE name = 12345可能会出问题。
第三种情况是查询条件被函数包裹了。WHERE REVERSE(name) LIKE '%明'这种写法必然失效，因为 MySQL 无法对函数运算结果使用 B+ 树索引。这也解释了为什么反向生成列方案里，要单独建一个生成列来存储REVERSE(name)，而不是在查询时临时反转。
第四种情况是统计信息不准。执行ANALYZE TABLE user刷新统计信息后，优化器有可能重新选择索引。第五种情况是 MySQL 优化器认为全表扫描可能更快。这时EXPLAIN会显示type=ALL，possible_keys有索引但没用。你可以用FORCE INDEX临时验证方案可行性：
SELECT id, name FROM user FORCE INDEX(idx_name) WHERE name LIKE '%张%';如果FORCE INDEX后执行时间明显下降，说明优化器估值不准或成本模型不适合当前数据分布。需要评估是不是统计信息、内存分配、缓冲池命中率的问题，而不是立刻在业务 SQL 中长期加FORCE INDEX，因为它会让优化器失去灵活性。
最后再说一个容易被忽略的点：如果你想用覆盖索引方案，但又必须查询大字段，比如content TEXT，TEXT 字段无法直接作为二级索引的普通列，前 768 字节前缀索引也无法覆盖完整内容。这种场景下不要纠结，直接换全文索引更省事。
我个人的建议是，能改业务查询前缀就用前缀，改不了前缀再试覆盖索引，真正要任意位置包含，果断上全文索引。反向生成列适合那种业务上特别明确就查尾号的场景，别把它当成通用模糊搜索的万能解。上面这些方法我都实际跑过，优化效果和数据量、查询模式高度相关，千万不要照抄，先在你的真实表结构上依次验证。希望这篇经验能帮你少走点弯路。
