先把结论放在前面在 InnoDB 的 B 树索引里LIKE %关键词%这个写法让索引失效的根因是它破坏了从根节点逐层定位的能力而不是 MySQL 傻。想让字段两边都能加%还能用上索引现实情况中主要有几条路可以走改查询条件、建覆盖索引、上全文索引、用反向生成列。今天这篇就把这几条路的原理、建索引姿势和实际的踩坑点说清楚。上周同事拿着一张执行计划截图来找我满脸困惑表里 name 字段明明建了索引怎么WHERE name LIKE %张%的时候 EXPLAIN 还是typeALLrows 十几万拖了好几秒他说网上搜了一圈答案都是“前导 % 会导致索引失效”可业务上就是需要前后都能匹配总不能改需求。这个问题太典型了凡是有后台列表搜索、标签筛选、模糊查询场景的人基本都遇到。我前后试过覆盖索引绕、全文索引、反向生成列各种办法今天把能实操落地的几条路掰开揉碎讲一遍。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) ) ENGINEInnoDB;当我们执行EXPLAIN SELECT * FROM user WHERE name LIKE %张%;得到的结果大概率是idselect_typetabletypepossible_keyskeyrowsExtra1SIMPLEuserALLNULLNULL98567Using wheretypeALL代表全表扫描possible_keys明明显示了idx_name但优化器没有使用它。原因很简单在这个查询里即使扫描整个二级索引也只是找到一批主键值还需要回表读取完整数据成本比顺序读全表还要高。所以 MySQL 做了一个“聪明”的决定扫表。1.3 别把 ICP 和覆盖索引混为一谈很多文章提到Index Condition Pushdown也就是索引条件下推说 MySQL 5.6 之后能把 LIKE 条件下推到存储引擎层从而加速模糊查询。这句话本身没毛病但有个前提查询必须已经开始走索引。如果typeALLICP 一点忙都帮不上存储引擎压根没有机会在索引上过滤。还有人会问那我建一个(name, age, email)联合索引LIKE %张%会不会走答案是有可能走typeindex也就是全索引扫描。这个“index”不是定位到某个范围而是遍历了整棵索引树但因为它包含了查询需要的列不需要回表所以被称为“覆盖索引扫描”。很多人容易把typeindex误以为“索引生效了”其实它只是从全表扫变成了全索引扫本质没有缩小检索范围。但正因如此它成了第一个可落地的优化方案。2. 方案一用覆盖索引把全表扫描骗成索引全扫描2.1 覆盖索引的原理与成立条件覆盖索引指查询执行时不需要通过二级索引回表就能拿到全部目标字段。InnoDB 的二级索引叶子节点除了索引列还包含主键值。假如你只查询SELECT name FROM user并且name本身就是索引列那优化器可以直接通过扫描idx_name得到结果全程不回表。此时即使LIKE %张%扫的是整棵索引树但索引树通常比聚簇索引表小很多而且不需要随机 IO 回表整体成本有可能比全表扫更低优化器就会选择typeindex。“骗”这个词不够准确更准确地说是在不改变数据结构的前提下把成本曲线压到让优化器甘愿选索引的程度。覆盖索引的成立条件很严格查询结果列必须被索引覆盖查询条件列也必须是索引列的一部分。如果 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 %张%;结果会变成类似这样idselect_typetabletypepossible_keyskeyrowsExtra1SIMPLEuserindexidx_nameidx_name98567Using where; Using indextypeindex表示“扫描全部索引页”ExtraUsing 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) ) ENGINEInnoDB; ALTER TABLE article ADD FULLTEXT INDEX ft_title_content(title, content);查询时用MATCH ... AGAINST代替LIKESELECT 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_size3如果搜索“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_size1改成 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 可能还要走 filesortExtra里会出现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或refALL代表全表扫描。key不能是NULL必须显示实际使用的索引名。rows估算扫描的行数越小越好。Extra出现Using index说明有覆盖索引加成出现Using filesort要考虑排序优化出现Using temporary要小心临时表。还可以用EXPLAIN FORMATJSON看更细的成本数据EXPLAIN FORMATJSON 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连接字符集是latin1MySQL 会在索引列上做隐式转换导致索引失效。典型的例子是手写 SQL 时习惯在数字字段加引号比如WHERE phone 13800000000可能无碍但WHERE name 12345可能会出问题。第三种情况是查询条件被函数包裹了。WHERE REVERSE(name) LIKE %明这种写法必然失效因为 MySQL 无法对函数运算结果使用 B 树索引。这也解释了为什么反向生成列方案里要单独建一个生成列来存储REVERSE(name)而不是在查询时临时反转。第四种情况是统计信息不准。执行ANALYZE TABLE user刷新统计信息后优化器有可能重新选择索引。第五种情况是 MySQL 优化器认为全表扫描可能更快。这时EXPLAIN会显示typeALLpossible_keys有索引但没用。你可以用FORCE INDEX临时验证方案可行性SELECT id, name FROM user FORCE INDEX(idx_name) WHERE name LIKE %张%;如果FORCE INDEX后执行时间明显下降说明优化器估值不准或成本模型不适合当前数据分布。需要评估是不是统计信息、内存分配、缓冲池命中率的问题而不是立刻在业务 SQL 中长期加FORCE INDEX因为它会让优化器失去灵活性。最后再说一个容易被忽略的点如果你想用覆盖索引方案但又必须查询大字段比如content TEXTTEXT 字段无法直接作为二级索引的普通列前 768 字节前缀索引也无法覆盖完整内容。这种场景下不要纠结直接换全文索引更省事。我个人的建议是能改业务查询前缀就用前缀改不了前缀再试覆盖索引真正要任意位置包含果断上全文索引。反向生成列适合那种业务上特别明确就查尾号的场景别把它当成通用模糊搜索的万能解。上面这些方法我都实际跑过优化效果和数据量、查询模式高度相关千万不要照抄先在你的真实表结构上依次验证。希望这篇经验能帮你少走点弯路。