面试题里只要出现“索引失效”十有八九会拿 LIKE 来开头。很多同学背答案背得滚瓜烂熟——“左模糊导致索引失效加右模糊没问题”可真到面试官追问一句“为什么左模糊就走不了索引原理是什么”或者“你说失效就失效什么情况下 LIKE 也能走索引”就卡住了。这篇文章我把 MySQL 里 LIKE 和索引之间的那些事彻底拆开讲清楚从 B 树的查找机制说起覆盖覆盖索引、ICP优化、字符集排序规则这些容易忽略的坑最后还有一套排查索引失效的实战方法。1. 先说结论LIKE 到底什么时候会失效1.1 一条 SQL 引发的思考先看一条最典型的查询SELECT * FROM user WHERE name LIKE %张三%;这条 SQL 在 name 字段有普通索引的情况下大概率是不会走索引的而是全表扫描。很多人就把“LIKE 以 % 开头 索引失效”这句话记死了。实际上面试官的考点远不止这一层他更想听到的是为什么 % 开头就失效如果查询条件是LIKE 张%呢能不能走索引如果 SELECT 的字段都在索引里呢结果会不会不一样在 InnoDB 和 MyISAM 下结果一样吗LIKE 失效和函数操作、隐式转换失效底层原因是不是同一个这些如果能一环扣一环讲清楚面试基本就稳了。下面一步步拆。1.2 模糊匹配的三种形态LIKE 查询按通配符位置可以分成三类写法匹配含义索引使用情况LIKE 张%以“张”开头后面任意可能走索引范围扫描LIKE %三以“三”结尾前面任意无法走索引除非覆盖索引LIKE %张%中间包含“张”无法走索引除非覆盖索引很多人以为只有三种实际还有一种特殊情况LIKE %它等价于全表数据MySQL 优化器通常会直接选择全表扫描因为回表代价太大了。那为什么“张%”能走而“%张”不能走这里涉及 B 树索引的物理存储结构下面专门讲。2. 索引失效的底层原理B 树的查找逻辑2.1 B 树索引是怎么组织的InnoDB 的索引结构是 B 树叶子节点按索引列的顺序排列且叶子节点之间用双向指针连接。这个“有序”是索引发挥作用的核心前提。当我们执行WHERE name LIKE 张%时优化器能利用索引的有序性快速定位到第一个以“张”开头的记录然后沿着叶子节点的链表向右顺序扫描直到遇到不是“张”开头的记录为止。这就是典型的索引范围扫描range scan效率很高。但如果换成LIKE %张问题就来了目标记录的起点无法确定。以“张”结尾的记录可能分散在整棵 B 树的任何位置索引的有序性在这个条件下帮不上忙优化器只能选择从第一个叶子节点开始全量扫描再把每条记录的 name 取出来做匹配。这个代价等同于全表扫描优化器干脆直接走全表扫描可能还更快。这里要说明一个关键点索引失效不是索引坏了而是优化器认为在特定条件下索引扫描的代价高于全表扫描所以放弃使用索引。理解“代价估算”这个思路比死记硬背哪种写法失效更重要。2.2 一个容易忽略的例外覆盖索引很多教程说“%开头一定失效”这话不够严谨。当 SELECT 所需的列全部包含在索引中时即使LIKE %张%也能走索引因为此时不需要回表扫描索引的成本远低于扫描全表。举个例子-- name 和 age 都有索引或者建立联合索引 (name, age) SELECT name, age FROM user WHERE name LIKE %张%;如果 name 和 age 都在联合索引里这条 SQL 就会走索引扫描因为索引里已经包含了所需数据无需回表。这个技术叫覆盖索引Covering Index是 MySQL 优化 LIKE 查询的重要手段。所以面试里如果你能主动讲出“覆盖索引可以让左模糊查询走索引”这个深度就比普通背答案的候选人高出一截。2.3 实操验证方法想知道一条 SQL 到底有没有走索引最简单的办法是用 EXPLAINEXPLAIN SELECT * FROM user WHERE name LIKE %张%; EXPLAIN SELECT * FROM user WHERE name LIKE 张%; EXPLAIN SELECT name FROM user WHERE name LIKE %张%;重点看type字段const/ref索引等值查询range索引范围扫描LIKE 右模糊一般会显示这个index全索引扫描走了索引但效率一般ALL全表扫描没走索引再看Extra字段Using index说明是覆盖索引不需要回表Using index condition说明使用了索引条件下推ICPUsing where说明数据回表后再次过滤建议手头有数据库的自己建一张几万条数据的表试一下光看理论容易忘。3. 影响 LIKE 走索引的隐藏因素3.1 字符集和排序规则的影响这是很多同学忽略的坑。MySQL 索引的排序依赖字符集的排序规则如果索引列和查询条件的字符集、排序规则不一致可能导致无法使用索引。比如表的 name 列是 utf8mb4_general_ci但连接字符集是 utf8mb4_unicode_ci或者字段本身用了 utf8mb4_bin优化器在做范围比较时需要做转换一旦索引列参与了表达式运算索引就失效了。实际项目中建表时统一字符集连接时统一SET NAMES很少会踩这个坑。但面试官问“LIKE 为什么失效”你可以补充这一点说明你不仅知道表面原因还知道深层影响因素。3.2 查询条件里带函数或运算LIKE 条件本身不算函数操作但如果 LIKE 的参数是从某个函数计算出来的比如WHERE name LIKE CONCAT(%, 张, %);这种写法在 MySQL 5.7 及以下版本通常会导致索引失效因为索引列上做了隐式计算。到了 MySQL 8.0.13 以上优化器增加了对CONCAT([, prefix, %)这类模式的优化部分情况下可以走索引但为了保险起见建议直接把拼接逻辑写在应用层传入完整参数。更常见的失效场景是WHERE LEFT(name, 1) 张; -- 索引列套了函数 WHERE name 张三 OR name 李四; -- 其实这个能走索引3.3 优化器基于成本的判断有时候 SQL 写法完全没问题但优化器就是不走索引原因是选择性太低。如果表里有 100 万条记录其中 90 万条的 name 都以“张”开头那LIKE 张%走索引扫描 90 万条再回表还不如全表扫描来得快。优化器会估算索引扫描的行数、回表次数、IO 开销最终选择成本最低的方案。所以“能不能走索引”是一个动态判断跟数据分布强相关。可以通过ANALYZE TABLE更新统计信息或者用FORCE INDEX强制走索引来验证EXPLAIN SELECT * FROM user FORCE INDEX(idx_name) WHERE name LIKE %张%;如果强制走索引后反而更慢说明优化器的选择是有道理的。4. 慢查询优化实战LIKE 覆盖索引 ICP4.1 什么是索引条件下推MySQL 5.6 引入了索引条件下推Index Condition PushdownICP把 WHERE 条件的过滤下推到存储引擎层减少回表次数。举个例子SELECT * FROM user WHERE name LIKE 张% AND age 25;假设联合索引是 (name, age)。没有 ICP 时InnoDB 用索引定位到 name 以“张”开头的所有记录然后回表取出完整行再在 Server 层过滤 age 25。有 ICP 时InnoDB 在索引遍历过程中直接用 age 条件过滤只有同时满足 name LIKE 张% 和 age 25 的记录才回表。这个机制对 LIKE 左模糊查询也有帮助当LIKE %张%配合其他索引条件时先利用其他条件缩小范围再用 LIKE 过滤可能整体走索引。4.2 实际优化案例我工作中遇到过一条慢查询表结构大致如下CREATE TABLE product ( id INT PRIMARY KEY, product_code VARCHAR(32), product_name VARCHAR(128), category_id INT, status TINYINT, INDEX idx_category_status (category_id, status), INDEX idx_product_name (product_name) ) ENGINEInnoDB;业务需求是SELECT product_code, product_name FROM product WHERE category_id 100 AND product_name LIKE %手机% AND status 1;实际执行这种 LIKE 全模糊查询product_name 本身的索引帮不上忙。优化方案是调整索引把查询条件里面可以等值匹配的列放在联合索引前面ALTER TABLE product ADD INDEX idx_cat_status_name (category_id, status, product_name);改完之后再看 EXPLAINtype 不再是 ALL数据先通过 category_id 和 status 定位到一个小范围再在索引内部完成 product_name 的 LIKE 过滤回表次数大幅减少慢查询耗时从 800ms 降到了 30ms 左右。这个案例给我们的启发是不要盯着 LIKE 本身想办法而是在 WHERE 条件里加点“限定条件”让索引的选择性变高LIKE 才有机会搭上顺风车。4.3 索引设计规范建议如果业务确实需要频繁做LIKE %关键字%查询可以考虑引入全文索引FULLTEXT或外部搜索引擎ElasticsearchMySQL 的 LIKE 本质上不是为全文检索设计的。如果右侧模糊查询LIKE 张%是高频查询可以在 name 字段上建普通索引配合覆盖索引可以做到非常高效。联合索引设计时遵循“等值条件放前面、范围条件放后面”的原则LIKE 的右模糊可以放在联合索引末尾利用其范围扫描能力。谨慎使用FORCE INDEX它只能让 SQL 走索引但如果索引选择性和数据分布不合适反而让查询更慢。5. 常见问题与排查技巧实录5.1 问题一EXPLAIN 显示走了索引但查询还是很慢这种情况通常是索引扫描范围过大导致的。比如LIKE 张%匹配到了大量数据虽然走了 range但回表次数巨大。解决思路是缩小返回行数加上必要条件做二次过滤或者把大字段拆到附属表。5.2 问题二联合索引中 LIKE 放在中间位置后面字段的索引失效联合索引遵循最左前缀原则如果查询条件是WHERE name LIKE 张% AND age 20;联合索引是 (name, age, status)优化器可以先按 name 做范围扫描再在索引内部过滤 age20整体是能走索引的。但如果 name 条件是LIKE %张%那 name 本身无法用于快速定位联合索引中后面的 age、status 也很难发挥作用。这时候只能靠覆盖索引或调整查询条件。5.3 问题三如何快速定位哪些 SQL 存在索引失效我常用的排查 SQLSELECT db, query, total_latency, rows_examined, rows_sent FROM sys.statements_with_full_table_scans ORDER BY total_latency DESC LIMIT 20;以及查看慢查询日志SHOW VARIABLES LIKE slow_query_log; SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;通过慢查询日志定位到高耗时 SQL再用 EXPLAIN 逐一分析是否走了索引、是否回表过多。5.4 排查索引失效的完整检查清单使用EXPLAIN查看执行计划确认type和key字段检查索引列是否参与了函数运算或隐式类型转换检查 WHERE 条件中是否使用了OR连接两个不同索引列某一侧无索引可能导致整体失效检查 LIKE 模糊匹配的位置和匹配量的占比检查字符集排序规则是否统一检查统计信息是否过期使用Optimizer Trace查看优化器选择过程的细节SET optimizer_traceenabledon; SELECT * FROM user WHERE name LIKE %张%; SELECT * FROM information_schema.OPTIMIZER_TRACE;5.5 Oracle 迁移 MySQL 场景的特别提醒从 Oracle 迁到 MySQL 的同事很容易踩一个坑Oracle 的LIKE对%和_的转义处理比较宽容而 MySQL 里%匹配任意多字符和_匹配单个字符需要严格转义。如果业务数据里本身包含这些字符查不到数据体验还好说做 LIKE 时误传%导致索引无法匹配是新手最常犯的事。正确写法是SELECT * FROM user WHERE name LIKE 张\%三 ESCAPE \\;5.6 一次经典的全表扫描事故记录之前线上有个订单查询页面用户输入订单号模糊搜索SELECT * FROM order_info WHERE order_no LIKE %20230901%;一开始订单表只有几万条全表扫描没问题。后来数据涨到 2000 万这条 SQL 直接拖垮了主库。排查发现 order_no 字段是 VARCHAR但由于历史原因部分数据是通过数字类型写入的字段定义留下了 id 自增主键类型不一致的隐性转换隐患——查询参数是字符串字段是 VARCHAR本身没问题但由于 LIKE 左模糊索引根本用不上。最终方案是分成两步先按精确时间条件缩小范围再在应用层做二次过滤。这个案例说明大表上的全模糊查询任何索引优化都不如业务层面的方案调整来得立竿见影。6. 回答面试官的标准话术结构面试官问到这个题建议按下面这个思路组织答案先说结论LIKE 能否走索引取决于模糊匹配的位置和数据分布。右模糊如LIKE 张%在选择性足够高时走索引范围扫描左模糊和全模糊默认不走索引但在覆盖索引场景下可以走索引扫描。讲原理索引是 B 树结构叶子节点按键值排序。右模糊能利用排序特性快速定位起始位置左模糊无法确定起点索引排序特性失效优化器放弃索引。讲例外覆盖索引、ICP、加辅助条件缩小范围这些手段可以让看似失效的 LIKE 重新利用索引。讲实战结合自己做过的慢查询优化案例说明优化过程和结果。体现思考深度提到优化器的成本估算逻辑以及索引选择性对执行计划的影响。按这个结构答面试官能明显感觉到你不仅有经验而且对 MySQL 底层机制有系统性理解。说到底索引失效这个问题从来不是孤立存在的它背后是索引数据结构的特性与优化器决策逻辑的博弈。能把这一层想明白面试里大部分索引题都能触类旁通。我在实际团队带新人的过程中通常要求他们写一条 SQL 前先想三个问题这条查询的数据基数大概是多少、索引列的选择性如何、SELECT 出来的列能不能被索引覆盖。这三个问题想清楚了LIKE 也好、函数也罢都不太可能写出离谱的慢查询。