MySQL 的索引失效这问题我太熟了。业务刚上线时慢查询还不明显等数据量涨到几百万行翻车的 SQL 一条接一条冒出来。很多人第一反应是“加索引”结果一查执行计划发现索引根本没走。这篇文章不谈虚的直接把我这些年踩过、排查过的索引失效场景逐一拆开配合 SQL 示例和优化方案你看完就能对着自己的慢查询去排查。1. 索引为什么会失效先看懂 B 树怎么干活要搞懂索引失效你不能光背“哪些情况会失效”得像看地图一样明白 MySQL 的索引到底怎么找数据。InnoDB 的索引结构是 B 树这棵树有几个关键规则左小右大、叶子节点有序、叶子节点之间用双向链表串起来。1.1 最左前缀法则的本质联合索引是一棵“复合排序”的树很多人背过“联合索引要遵守最左前缀”但不知道为什么。我举个例子你有联合索引(a, b, c)它在 B 树里的排序规则是先按 a 排序a 相同再按 b 排序b 相同再按 c 排序。这就好比字典的目录先按拼音首字母排首字母相同再看第二个字母以此类推。当你查询条件写成WHERE b 1 AND c 2时MySQL 拿到了b1但它不知道去哪棵子树找因为树的第一层排序键是 a。它只能把整棵树的叶子节点全扫一遍然后过滤出b1的记录。这就是最左前缀失效的本质——b和c脱离了前置字段a就像你想用字典目录查“没有首字母、只有第二个字母是 e”的汉字只能翻完整个目录。这里有个很容易被坑的细节WHERE a 1 AND c 2这种查询a 会走索引c 不会。因为 a 能定位到具体子树但在这棵子树里叶子数据按 b 排序c 是乱序的无法二分查找只能在命中的 a1 范围内做过滤。很多人看执行计划发现key有值就以为全走了索引其实key_len长度暴露了真相——key_len只算到 a 的长度c 根本没进索引。1.2 索引选择性优化器凭什么“拒绝”你的索引还有种情况你没违反任何规则优化器就是不选索引——它觉得索引还不如全表扫描快。这涉及一个概念叫“索引选择性”即COUNT(DISTINCT 索引列) / COUNT(*)。性别列的选择性极低一个 100 万行的表只有 0男和 1女两种值你建了索引它也不会用因为走索引要回表 50 万次每次随机 I/O比直接全表顺序扫描慢一个数量级。那什么时候优化器会“回心转意”当你给 SQL 加上LIMIT 10的时候。查 10 条和查 50 万条的成本天差地别优化器是成本计算器它用统计信息估算各方案代价选择最低的那个。理解这点很重要因为后面很多“失效”场景其实是优化器基于成本做的理性选择不是索引真的坏了。也正因如此排查问题时EXPLAIN里的type、rows、key三项一定要连在一起看别只盯着key有没有值。2. 十大典型失效场景从 SQL 写法到表结构逐个拆给你看下面这些场景是我在业务代码和慢查询日志里实际抓出来的每一个都配了 SQL 示例和优化写法。你自查的时候拿这些当清单比对就行。2.1 违反最左前缀联合索引不是“有几个字段建几个索引”联合索引(a, b, c)以下写法都会踩雷-- 失效跳过了第一列 a SELECT * FROM t WHERE b 1 AND c 2; -- 部分失效a 走了索引c 没走 SELECT * FROM t WHERE a 1 AND c 2; -- 部分失效a 走了索引b 没走b 的范围查询阻断后续字段 SELECT * FROM t WHERE a 1 AND b 10 AND c 2;第三个例子值得多说两句。b 10是范围条件一旦 b 开始做范围匹配c 就没法用索引了。原因也好理解在 a1 的子集里数据先按 b 排序命中的是 b 在 10 到正无穷这个区间这个区间里 c 是无序的没法继续二分。这是我见过最多的坑有人不管查询条件组合上来就把所有查询列塞进一个索引。正确做法是先梳理业务里出现频率最高的等值查询组合把等值查询列放前面范围查询列放最后。比如先跑WHERE a ? AND c ?的查询远多于WHERE a ? AND b ?那就该把索引设计成(a, c, b)。2.2 索引列参与运算或函数操作你把索引的“有序性”搞坏了这是优化器最无语的场景。B 树里存储的是原始列值你查询时给列套了函数MySQL 得先把每行的列值算完才能比较树的有序结构直接作废。-- 失效对索引列做了函数处理 SELECT * FROM t WHERE DATE(create_time) 2024-01-15; -- 失效对索引列做了隐式运算 SELECT * FROM t WHERE id 1 100;解决办法是“把计算搬到等号右边”-- 优化把函数从索引列上剥掉 SELECT * FROM t WHERE create_time 2024-01-15 00:00:00 AND create_time 2024-01-16 00:00:00; -- 优化先算好再比较 SELECT * FROM t WHERE id 99;这里有个细节必须要说MySQL 8.0 虽然支持函数索引CREATE INDEX idx_fn ON t((DATE(create_time)))但函数索引不解决“索引列参与函数”的查询场景你需要建的是“把函数表达式本身作为索引键”的结构。做法是CREATE INDEX idx_create_date ON t ((DATE(create_time)));不过我不太推荐业务里动不动就上函数索引它有几个硬伤占存储空间、写入时有额外算力消耗、优化器不一定活用。能改写 SQL 的优先改写。2.3 隐式类型转换字符串列用数字去匹配这个坑隐蔽性极高尤其容易出现在 ID、手机号这类列上。-- 假设 phone 是 varchar(20) SELECT * FROM user WHERE phone 13800138000;MySQL 的隐式类型转换规则是phone 13800138000会把 phone 列的值转换成数字再比较这本质上就是对索引列做了一次 CAST 操作索引自然就失效了。我见过一个线上事故就是在用户表上查手机号时漏了引号导致全表扫描一个本来 20ms 的查询变成 2 秒多。排查技巧也顺手送你MySQL 里有一条隐式转换的“阵营规则”——字符串和数字比较时MySQL 倾向把字符串转成数字除非字符串是字段且无法转换会反过来。所以你写WHERE phone 13800138000相当于在 phone 列上执行了CAST(phone AS SIGNED)无论你怎么纠结索引就是没了。优化写法非常简单数字加上引号变成字符串SELECT * FROM user WHERE phone 13800138000;如果业务上确实总用数字去查那干脆把列定义成BIGINT从源头上消除转换。2.4 LIKE 模糊查询以通配符开头走不了索引的三种情况先说结论LIKE abc%可以用索引LIKE %abc用不了LIKE %abc%也用不了。B 树叶子节点按字典序排列abc%意味着找 a 开头、b 第二个、c 第三个的区间这是连续范围能二分定位。但%abc意味着结尾是 abc前缀任意你没法在字典里查“所有以 abc 结尾的词”只能全量扫。-- 失效的写法 SELECT * FROM t WHERE name LIKE %张; -- 走索引的写法 SELECT * FROM t WHERE name LIKE 张%; -- 某些场景下可以改写 SELECT * FROM t WHERE INSTR(name, 张) 0;最后那个INSTR改写也是全表扫描但它解决了语义问题如果你就是想查“名字里包含张的人”别指望索引能救你。这类需求只有一个根治方案——上全文索引或者用 Elasticsearch。MySQL 的全文索引对中文支持不算漂亮而且有大量%中%查询的场景全文索引的表达能力也很有限。另外说句实在的name LIKE %张%如果表只有几千行走个全表扫描也没啥大不了别为了“必须走索引”而过度设计。2.5 OR 条件包含非索引列优化器只能做并集扫描OR是很多新手的重灾区-- 假设只有 idx_a 在 a 列上 SELECT * FROM t WHERE a 1 OR b 2;执行计划会怎么做它有两种选择一是对 a1 走索引、b2 全表扫然后做并集二是直接全表扫。MySQL 实际通常选择后者因为 OR 的两边要取并集而 b 没有索引意味着 b2 那部分必须全表扫描。既然都得全表扫了不如省点事。优化手段是最经典的把 OR 改成 UNIONSELECT * FROM t WHERE a 1 UNION ALL SELECT * FROM t WHERE b 2;不过要注意UNION 自带去重逻辑如果两边的结果集不可能有交集例如a1和b2的语义上不会同一条记录同时满足用 UNION ALL 更好避免没必要的排序去重损耗。当 OR 两边都是索引列时type会显示为index_merge这是 MySQL 把两个索引的扫描结果合并的一种优化比全表扫描好但通常还是不如一个合适的复合索引来得利落。2.6 NOT、!、 不等于类查询索引的有序性救不了你不等于的语义是要排除一个点听起来好像可以走索引实际并非如此。B 树索引最擅长的是“定位一段区间”。a ! 1意味着要找所有不等于 1 的值也就是从负无穷到 1 的区间再加 1 到正无穷的区间——排除一个点的代价反而要扫描几乎整棵树。-- 全表扫描 SELECT * FROM t WHERE status ! 1; -- 有些场景可以改写为范围查询 SELECT * FROM t WHERE status 1 OR status 1;我说句大实话不等于本身不代表业务有问题。如果status只有 0、1、2 三个值业务要查status ! 1那命中 2/3 的行走全表扫描其实是合理的。真正要走索引你得改写语义比如业务核心是“查待处理记录”那就建一个WHERE status 0的索引然后把“不等于 1”的查询改成“等于 0 或等于 2”的等值查询分别跑。2.7 IS NULL / IS NOT NULL空值的特殊索引规则在 InnoDB 中NULL 值在索引里是可以存储的但优化器对IS NULL和IS NOT NULL的决策完全不同。IS NULL查询很多时候能走索引尤其是通过索引下推但IS NOT NULL往往会导致全表扫描。原因和二八定律相关大多数表的 NULL 值占比很小查 NULL 只扫一小段而IS NOT NULL意味着“除了那些少数 NULL我全要”这和全表扫描基本没区别。-- 可能走索引 SELECT * FROM t WHERE deleted_at IS NULL; -- 基本走过场全表扫 SELECT * FROM t WHERE deleted_at IS NOT NULL;更麻烦的是如果索引列存在 NULL 值查询优化器需要额外处理空值判断。一个比较实用的经验在业务表里能用0/-1做默认值代替 NULL 的就尽量别用 NULL。尤其是一些有唯一性约束的字段比如手机号你给 NULL 加唯一索引MySQL 大概率允许存多行 NULL业务上会出妖。用空的默认值字符串或 0 值能规避一堆奇奇怪怪的索引优化问题。2.8 范围查询阻断联合索引后续字段这其实就是 2.1 里提到的那个场景的延伸但太常见值得单独列一个场景。联合索引(a, b, c)查询条件是WHERE a 1 AND b 10 AND c 2。执行过程是这样的先用 a1 定位子树再在子树里用 b 的等值和范围找到 b 的区间然后 c2 就“断”了。因为 b 的范围区间内有多个不同的 b 值每个 b 值下 c 是有序的但跨 b 值情况下 c 整体无序。这个场景的优化思路有几种把范围查询列放在联合索引最后如果等值查询a和c是高频则索引设计为(a, c, b)这样WHERE a1 AND c2可以完全命中索引b 做一次过滤。使用 MySQL 8.0 的SKIP SCAN优化某些情况下虽然跳过了前置列但优化器通过扫描不同前缀值来模拟“跳过”不过限制条件很多别指望它。还有个偏门的技巧是“范围拆分”把b 10 AND c 2拆成b 10 AND c 2 OR b 11 AND c 2 ...这显然不现实别这么干。2.9 排序与分组时没按索引顺序filesort 与临时表的代价上过班的人都知道SQL 写得好不好排序是大头。ORDER BY如果没用到索引MySQL 会调用 filesort这不仅是排序有时还会把数据放到临时表里。-- 假设有联合索引 (a, b) -- 能走索引排序 SELECT * FROM t WHERE a 1 ORDER BY b; -- 走不了索引排序 SELECT * FROM t WHERE a 1 ORDER BY c;WHERE a 1 ORDER BY b能走索引是因为在 a1 的子树里b 天然有序MySQL 按序遍历叶子节点就能直接返回有序结果连排序都省了。但ORDER BY c就麻烦了——c 在该子树里是无序的必须先查出来再用 filesort 排序。两个常见问题排序方向不一致也不行ORDER BY a ASC, b DESC这种混合方向MySQL 8.0 之前的版本基本走不了索引排序8.0 支持降序索引建索引时ORDER BY a ASC, b DESC但比较小众。分组也一样GROUP BY b如果能走索引可以减少临时表。但如果你 SELECT 了非分组的其它列MySQL 在 ONLY_FULL_GROUP_BY 模式下直接报错不报错那也是随机取值。建议排查慢查询时看到Extra字段里有Using filesort第一反应就是“索引顺序和排序字段对不上”去调整索引顺序而不是加内存参数扛。2.10 表连接字段字符集不一致索引对不上号的隐藏杀手这个场景特别隐形排查起来非常痛苦。两个表用 JOIN 关联时如果关联字段的字符集不一样比如一个是utf8mb4一个是latin1MySQL 在连接时会对字段做隐式字符集转换索引直接作废。-- user 表的 id_card 是 varchar(50) utf8mb4 -- id_card_log 表的 id_card 是 varchar(50) latin1 SELECT * FROM user u JOIN id_card_log l ON u.id_card l.id_card;这个 SQL 的执行计划里通常是l表全表扫描。因为 MySQL 得把每一行的l.id_card从 latin1 转成 utf8mb4 再和u.id_card比较转换发生在连接字段上索引没用。解决思路是“统一字符集”建表时统一utf8mb4新项目直接默认老表通过ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4迁移。另外排序规则也得一致utf8mb4_unicode_ci和utf8mb4_general_ci虽然在大多数现代版本中差异不算大但连接字段必须一致否则同样可能导致索引失效。3. 用好 EXPLAIN排查失效场景的唯一硬标准上面列了这么多场景实际排查时你不会靠猜的——你靠的是 EXPLAIN 输出。学会看执行计划比背场景清单有用十倍。3.1 执行计划关键字段速查手册一张表把重点字段给你理清楚字段名关注点含义与优化方向typesystem const eq_ref ref range index ALL从好到差出现 ALL 就是全表扫描key实际使用的索引名NULL 表示没走索引key_len索引使用的字节数辅助印证联合索引用了几个字段rows预估扫描行数数字越大越危险ExtraUsing index / Using filesort / Using temporary / Using whereUsing filesort 有排序Using temporary 有临时表这里有个很多人会误读的点type为index时并不代表“走索引了”就万事大吉。index表示“索引全扫描”MySQL 遍历了整棵索引树而不是回表查数据。这通常发生在SELECT COUNT(*) FROM t或者SELECT id FROM t这种覆盖索引查询场景比ALL好一些但如果索引树很大也谈不上快。ref和range才是常规的最优状态。3.2 实际排查三步走从抓到慢 SQL 到确认根因我先说个我常规的排查流程你按这个节奏走基本不会漏第一步抓慢查询。MySQL 开启慢查询日志SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;线上环境用 Performance Schema 或者监控平台的慢查询采集更友好。拿到慢 SQL 列表后按执行次数 x 单次耗时排序先处理“热慢查”。第二步看执行计划。对每条重点 SQL 执行EXPLAINEXPLAIN SELECT u.id, u.name, l.remark FROM user u LEFT JOIN id_card_log l ON u.id_card l.id_card WHERE u.status 1 ORDER BY u.created_at DESC LIMIT 20;重点看key是否为 NULL、type是否为ALL、Extra有没有filesort或temporary。如果key明明有值但查询还是慢检查key_len是否符合预期——比如联合索引只用了第一列说明后面的字段没派上用场。第三步用ANALYZE TABLE和SHOW INDEX检查统计信息与索引基数。执行计划有时因为统计信息过旧而误判ANALYZE TABLE t;可以重新收集统计信息。再看SHOW INDEX FROM t;里的Cardinality字段如果远低于预期比如唯一性很强的列数值却很低那统计信息可能不准或者索引设计本身有问题。3.3 覆盖索引让查询“不出树”的高级优化有一种“不是避免失效而是压根不出树”的优化思路——覆盖索引。当 SQL 要查询的列全部包含在索引中时MySQL 不需要回表直接扫描索引就拿到结果。这类查询的Extra会显示Using index。-- 假设有联合索引 (status, created_at) SELECT status, created_at FROM t WHERE status 1;这条 SQL 的查询列 status 和 created_at 全在索引里直接走索引扫描返回结果。哪怕没有 where 条件导致索引全扫描也比回表快。我优化报表统计类 SQL 时覆盖索引是我的首选因为它能把 I/O 降一个量级。但要注意覆盖索引不解决一切问题。如果你想SELECT *索引里不可能包含所有字段该回表还是回表。这时你需要考量的就是“回表成本”和“扫描行数”的平衡。4. 实战排查一个慢查询的完整翻车与修复记录光讲理论没用我拿一个之前优化过的真实慢查询给你完整走一遍。当时的业务是一个订单后台列表页要展示订单号、用户昵称、订单金额、下单时间、支付状态并且支持按条件筛选。4.1 原始 SQL 与执行计划SELECT order_id, user_name, amount, created_at, pay_status FROM orders WHERE pay_status 1 AND amount 100 AND DATE(created_at) CURDATE() ORDER BY created_at DESC LIMIT 30;orders 表当时 500 多万行这个查询在高峰期跑了接近 3 秒把数据库 CPU 打到 90% 以上。我加了索引(pay_status, amount, created_at)居然没用执行计划显示全表扫描type是ALLrows是 580 万。4.2 问题定位的三个线索当时我盯着执行计划逐一复盘线索一DATE(created_at) CURDATE()。这就是 2.2 节的典型场景——对索引列 created_at 套了函数。MySQL 必须先算出每行的DATE(created_at)索引里的原始时间戳在这条 SQL 里完全失去了定位作用。线索二范围查询在联合索引中间。我设计的索引是(pay_status, amount, created_at)amount 用了范围查询后面的 created_at 就算没被函数“废掉”也会在 amount 范围条件之后断掉没办法用于排序和定位。线索三LIMIT 30 无法掩盖大范围扫描。就算把 created_at 的函数去掉WHERE pay_status 1 AND amount 100能筛选出的行数也是百万量级回表成本极高。4.3 最终改写与索引调整我做的处理是把 SQL 拆成两步语义第一把“今天”这个时间窗口固化成区间范围SELECT order_id, user_name, amount, created_at, pay_status FROM orders WHERE pay_status 1 AND amount 100 AND created_at CURDATE() AND created_at CURDATE() INTERVAL 1 DAY ORDER BY created_at DESC LIMIT 30;第二调整联合索引为(pay_status, created_at, amount)。为什么这么调因为筛选和排序的主要热点是“下过单的今天的记录按时间倒序”pay_status是等值条件放第一位created_at等值或范围并承担排序放第二位amount是附加过滤放最后。这样执行计划显示type变成了rangekey_len覆盖了前两个字段Extra不再出现Using filesort查询耗时从 3 秒降到 80 毫秒。4.4 这次优化教我的三件事第一任何对索引列的函数包裹都是自杀式写法。写 SQL 时养成一个习惯看到索引列前后有函数、运算、CAST立刻停下来改写法。第二执行计划里的rows是核心指标。一个 500 万行的表如果rows估算到了 400 万不管key是不是 NULL这条 SQL 都是“全表扫描级”的代价。真正要优化的不是“让它恰好走索引”而是“大幅缩小扫描范围”。第三慢查询发散思维。一条慢 SQL 的背后往往是索引设计没有结合真实业务查询模式。比如上面的查询核心是“今天 支付状态 时间排序”那索引就该围绕这个模式设置而不是把用户可能填的筛选条件全塞进去。5. 监控与预防别等线上炸了才想起索引失效这一节是给团队和线上环境做“提前干预”用的。索引失效排查是事后功夫日常监控和发布规范能把一半问题扼杀在摇篮里。5.1 慢查询治理的最小闭环我配置过很多次慢查询收集最简单有效的一套是-- 开启慢查询日志 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;注意log_queries_not_using_indexes它会把所有没走索引的查询都记下来这参数在开发环境很有用但在线上要慎开——因为有些 SQL 全表扫描比走索引更快这类查询会被大量记录淹没日志。线上建议只开long_query_time 1配合监控平台做告警。另外还要给慢查询日志做轮转否则日志文件能撑爆磁盘。Linux 下用logrotate或者 MySQL 自身的SET GLOBAL slow_query_log按时段开关都行。5.2 利用设计规范从源头规避失效在代码层面我推行过几条 SQL 规范效果非常显著WHERE 条件中的索引列禁止任何函数与运算这写在代码评审清单里。字符串与数字比较必须类型一致通过参数绑定的方式天然避免隐式转换。JOIN 关联字段必须保证字符集、排序规则一致建表规范统一 utf8mb4。索引设计必须有“联合索引最左前缀意识”每个索引都要有明确的服务对象对应哪几条核心 SQL。上线 SQL 必须附EXPLAIN结果type为ALL且rows大于某个阈值的DBA 有权拒绝执行。这些规范看起来“重”但对团队多人协作特别管用。因为索引失效往往不是一个人写得不好而是不同人改 SQL 时各改各的把前一版的索引设计意图破坏了。规范可以逼着每个人把意图显性化。5.3 定期做索引“体检测试”我每隔一段时间业务高峰期后或者大促前会做一次索引体检。方法是拿生产环境全量慢查询日志按 SQL 模板聚合找出 top 20 的 SQL人工过一次执行计划。别信自动优化工具的“量子优化”执行计划这东西还得靠人判断。然后针对每个问题 SQL确认三个问题的答案这条 SQL 的表数据量和扫描行数的差距能接受吗索引设计的字段顺序和 WHERE 等值、范围条件的排列匹配吗有没有可以改成覆盖索引的地方这个动作贵在坚持把它变成每月例行任务索引失效问题会大幅减少。6. 面试与自测拿这些题目当体检单最后顺手整理几个面试和自测常问的点也当给文章收个尾。这些题目并不意味着背答案而是帮你理一下有没有漏掉的知识盲区。问题一联合索引(a, b, c)查询WHERE b 1 AND a 2 AND c 3会走索引吗答案会优化器会做条件重排把等值条件按索引顺序调整。这是 MySQL 优化器内置的“等值条件交换”优化不违反最左前缀。但如果是WHERE b 1 AND c 3仍然失效。问题二WHERE a LIKE %abc%和WHERE a LIKE abc%走索引的差异在哪里答案前者无法定位起始区间全表扫描后者可以定位到abc开头的连续区间走 range 扫描。优化时可以配合覆盖索引把 LIKE 命中的区间缩小后再回表但%abc%是不可救药的。问题三为什么OR的查询比UNION慢那么多答案OR 在字段没有全部索引时优化器很可能选择全表扫描UNION 把两个查询拆开每个都能独立利用各自索引。但 UNION 有去重成本如果能确认不重用 UNION ALL。问题四索引列上有 NULL 值对查询有什么影响答案不等于NULL的条件在索引列上容易失效IS NULL 通常可以走索引但要看优化器的估算。设计上尽量用默认值替代 NULL尤其在唯一索引、频繁筛选的列上。我做了这么多年的数据库优化最深的体会是索引失效基本不是“偶发 bug”而是“SQL 写法 索引设计 数据分布”三方不合拍。你把这三件事打通九成问题都能自己解决。这篇里提到的坑我也都踩过今天能帮你少踩一个就值了。
