索引失效的 10 种场景：MySQL EXPLAIN 逐一验证与根因分析

叙事框架：现象 → 排查过程 → 根因 → 修复 → 预防

问题现象

MySQL 索引失效是导致慢查询最常见的原因之一。本文基于一个电商订单表的测试环境，系统验证了 10 种索引失效场景：隐式类型转换、函数操作列、左模糊匹配、OR 条件连接、联合索引最左前缀不满足、列运算、不等于操作、IS NOT NULL、NOT IN 子查询以及数据分布不均导致优化器弃用索引。每种场景均提供错误写法与正确写法的 EXPLAIN 输出对比，结合 B+Tree 结构图和优化器成本模型分析根因。

排查过程：十种场景逐一验证

场景 1：隐式类型转换

第一个验证的也是最容易犯的——隐式类型转换。

-- 错误写法（传入数字）EXPLAINSELECT*FROMordersWHEREorder_no=123456;-- 正确写法（传入字符串）EXPLAINSELECT*FROMordersWHEREorder_no='ORD20240601001';

两行 SQL 差别只在于order_no传的值是数字还是字符串，但 EXPLAIN 的结果完全不同。

错误写法走了type=ALL全表扫描，虽然possible_keys有idx_order_no，但key=NULL——优化器认为这个索引不能用。原因很简单：order_no是varchar类型，MySQL 在比较时做了隐式类型转换，相当于CAST(order_no AS int)，函数包裹索引列导致索引失效。

正确写法走了type=ref，key=idx_order_no，rows=1，效率天差地别。

这个场景在实际生产中极其常见——ORM 框架自动生成的 SQL 尤其容易踩坑。比如 MyBatis 的#{}和${}混用，或者从 HTTP 参数直接传入数字类型的查询条件。

场景 2：函数操作列

第二个场景和第一个有相似之处——都是对索引列"动了手脚"。

-- 错误写法EXPLAINSELECT*FROMordersWHEREDATE(created_at)='2024-06-01';-- 正确写法EXPLAINSELECT*FROMordersWHEREcreated_at>='2024-06-01 00:00:00'ANDcreated_at<'2024-06-02 00:00:00';

DATE(created_at)这个函数包裹后，possible_keys直接变成NULL，索引完全不可用。改成范围查询后，type=range、key=idx_created_at、rows从 510 降到 10。

其他函数也一样：LENGTH(mobile)、CONCAT(order_no, '')、YEAR(created_at)——任何函数操作在索引列上都会让 B+Tree 无法正常遍历。

场景 3：左模糊匹配

第三个场景是 LIKE 查询的老问题。

-- 全表扫描EXPLAINSELECT*FROMordersWHEREorder_noLIKE'%01001';-- 走索引EXPLAINSELECT*FROMordersWHEREorder_noLIKE'ORD%';

%在左边时type=ALL，在右边时type=range、key=idx_order_no。B+Tree 的索引结构决定了它只能从左向右匹配前缀，左模糊意味着无法确定起始位置，只能全表扫描。

场景 4：OR 条件连接

OR 是个特殊场景——它的行为取决于两边列是否都有索引。

-- 两边都有索引 → 走 index_mergeEXPLAINSELECT*FROMordersWHEREstatus=1ORemail='user001@example.com';-- 有一边没索引 → 全表扫描EXPLAINSELECT*FROMordersWHEREstatus=1ORtotal_amount>1000;

第一行 SQL 中status和email都有索引，MySQL 选择了index_merge（索引合并），分别从两个索引取结果再合并。第二行中total_amount没有索引，possible_keys只有idx_status，但key=NULL——优化器判断全表扫描比走单边索引再过滤另一边更划算。

OR 问题的标准改法是拆成 UNION：

EXPLAINSELECT*FROMordersWHEREstatus=1UNIONALLSELECT*FROMordersWHEREtotal_amount>1000;

UNION 改写后两边都能独立走索引。

场景 5：不满足最左前缀

联合索引idx_user_status(user_id, status)的验证让刘老师格外关注。

-- 只查 status → 不走联合索引EXPLAINSELECT*FROMordersWHEREstatus=1;-- 带上 user_id → 走联合索引EXPLAINSELECT*FROMordersWHEREuser_id=1001ANDstatus=1;

第一行查status = 1时，possible_keys有idx_status（单列索引），但idx_user_status不可用——因为联合索引(user_id, status)的最左列是user_id，跳过它直接查status无法利用这个索引。

第二行带上user_id = 1001后，key=idx_user_status、key_len=5（int 各 4 字节 + 1 字节可空）、rows=2。

最左前缀的规则是：索引(a, b, c)可以匹配(a)、(a, b)、(a, b, c)，但(b)、(c)、(b, c)不行。设计联合索引时通常把选择性高的列放最左边。

场景 6：列运算

-- 全表扫描EXPLAINSELECT*FROMordersWHEREid+1=101;-- const 查找EXPLAINSELECT*FROMordersWHEREid=100;

id + 1 = 101这种写法对列做了运算，possible_keys=NULL，全表扫描。改成id = 100就能走主键const查找。

这个场景最简单的修复就是"把运算移到等号右边"：id + 1 = 101→id = 100。

场景 7：!= 或 <> 操作

不等于查询的行为取决于数据分布。

-- status != 0 → 全表扫描（83% 的行匹配）EXPLAINSELECT*FROMordersWHEREstatus<>0;-- status != 4 → 走 range 索引（匹配少部分行）EXPLAINSELECT*FROMordersWHEREstatus!=4;

status <> 0匹配约 83% 的行，优化器认为回表成本太高，选择全表扫描。但status != 4时——如果status=4（已取消）的订单很少——优化器评估回表成本后有可能走索引。

场景 8：IS NOT NULL

NULL 在 MySQL 索引中有特殊处理方式。

EXPLAINSELECT*FROMordersWHEREupdated_atISNOTNULL;EXPLAINSELECT*FROMordersWHEREdeleted_atISNULL;

两个查询都是type=ALL。IS NULL和IS NOT NULL通常不走索引，因为 NULL 在 B+Tree 中的存储位置与其他值不同，且 MySQL 索引默认不存储全 NULL 值的行（某些存储引擎有差异）。

优化方案：尽量用默认值替代 NULL。比如deleted_at可以用'1970-01-01'表示"未删除"，这样查询就能走索引。

场景 9：NOT IN / NOT EXISTS

-- 全表扫描EXPLAINSELECT*FROMordersWHEREstatusNOTIN(3,4);-- 走 range 索引EXPLAINSELECT*FROMordersWHEREstatusIN(0,1,2);

NOT IN 全表扫描，IN 走 range 索引。子查询 NOT IN 更严重——外层表必然全表扫描。

-- 子查询 NOT IN（全表扫描）EXPLAINSELECT*FROMordersWHEREidNOTIN(SELECTidFROMordersWHEREstatus=4);

NOT IN 的替代方案是LEFT JOIN + IS NULL：

SELECTo.*FROMorders oLEFTJOIN(SELECTidFROMordersWHEREstatus=4)tONo.id=t.idWHEREt.idISNULL;

场景 10：数据分布不均

最后这个场景和张工的日常工作最相关——优化器"弃用"索引。

-- status=0 占 108/610 ≈ 18%EXPLAINSELECT*FROMordersWHEREstatus=0;

status的数据分布基本均匀（每种状态约 100 行），所以查 status=0 走了type=ref。但如果数据倾斜——status=0 占了 80%——优化器就会选择全表扫描。

这不是索引"失效"，而是优化器基于成本估算的判断：走索引需要 200 万次随机读回表，不如直接顺序扫描 1000 万行数据。

可以用FORCE INDEX验证成本差异：

EXPLAINSELECT*FROMordersFORCEINDEX(idx_status)WHEREstatus=0;

根因分析

子原因 1：B+Tree 索引结构限制

B+Tree 索引是按索引列的值有序存储的，查询时通过二分查找定位。但如果对索引列做了任何"变形"（函数、运算、类型转换），MySQL 无法预知变形后的值在树中的位置，只能放弃索引。

这和查字典一样——你知道拼音就能快速定位，但如果要求"把所有拼音反转后的词找出来"，字典就没法用了。

子原因 2：优化器成本估算

优化器的决策基于成本模型：全表扫描成本 vs 索引扫描 + 回表成本。当查询返回的行数超过总行数的一定比例（通常 15-20%），优化器倾向全表扫描。

不等于、NOT IN、IS NOT NULL 之所以不走索引，本质上都是因为优化器预估回表成本高于全表扫描。数据分布越均匀、索引选择性越低，优化器越倾向于全表扫描。

子原因 3：NULL 值在索引中的特殊处理

MySQL 索引对 NULL 值有特殊规则：IS NULL、IS NOT NULL、= NULL都不走索引（某些特定场景下IS NULL可能走，取决于版本和优化器）。设计表结构时，能用NOT NULL + DEFAULT就不要允许 NULL。

修复方案

第一步：识别风险 SQL

每次写 SQL 前先跑 EXPLAIN，重点关注三点：

• type=ALL的必须审查
• possible_keys有值但key=NULL的——优化器认为索引"没用"
• rows超过预期数量级的

第二步：建立正确写法规范

陈哥整理了一份索引失效对照表，总结每种场景的错误写法和正确写法。

团队决定：以后所有查询类 SQL 变更必须附带 EXPLAIN 输出。张工还计划在 CI 流程中加入 SQL 规范检查工具。

第三步：日常索引诊断

除了牢记 10 种失效场景，日常维护中也要定期检查索引使用情况。

SHOW INDEX FROM查看索引定义，performance_schema.table_io_waits_summary_by_index_usage查看索引是否被使用过，EXPLAIN FORMAT=JSON查看优化器成本明细。

验证结果

即时指标

验证完成后，陈哥用EXPLAIN重新检查了团队正在开发的导出 SQL——发现其中至少 4 处索引失效：

• order_no传入数字（隐式类型转换）
• WHERE DATE(created_at)函数列
• OR 条件一边无索引
• status NOT IN子查询

全部修正后，全表扫描查询从原来的全部变为零，预估扫描行数从 510 降到个位数。

团队复盘

陈哥在群里发了验证总结，列出了 10 种场景的验证结论。刘老师感慨之前只知道"加索引会快"，不知道为什么快、什么时候不快。现在至少能说出 10 种索引不走的原因了。

CI 集成 SQL 规范检查的提议也得到了王哥的认可。

避坑建议

1. EXPLAIN 是基本功，不是高阶技能：每次写 SQL 都要跑 EXPLAIN，养成习惯后一眼就能看出问题。不要等到慢查询告警了才看。

2. ORM 框架不背锅，但要注意参数类型：MyBatis、JPA 生成的 SQL 可能因为参数类型推断错误导致隐式转换，要检查实际执行的 SQL（开启showSql或log4jdbc）。

3. 测试环境数据量要和生产相当：1000 行数据全表扫描只要几毫秒，1000 万行就是灾难。在测试环境用 EXPLAIN 时，要确保 rows 的数量级和生产一致。

4. 联合索引设计遵循"最左前缀 + 高选择性在前"：索引(a, b, c)等价于走了(a)、(a, b)、(a, b, c)的查询，(b)和(c)单独走不了。把选择性最高的列放最左边。

5. NULL 能不用就不用：设计字段时优先用默认值替代 NULL，不仅节省存储空间，还能避免 IS NULL/IS NOT NULL 不走索引的问题。

6. ≠ 和 NOT IN 不一定非要避免，但要了解成本：不等值查询在高选择性场景下也可能走索引，关键是用 EXPLAIN 确认。如果不能走索引，改用 LEFT JOIN 或拆分查询。

7. 函数列是最容易被忽略的：DATE(created_at)、YEAR(created_at) 看着无害，但函数一包索引就废。用范围查询替代日期函数。

8. OR 连接时保证两边都有索引：如果无法控制，用 UNION ALL 拆开。

附：完整命令清单

-- 建表 + 索引USEindex_test;SHOWINDEXFROMorders;SELECTCOUNT(*)FROMorders;-- 场景 1：隐式类型转换EXPLAINSELECT*FROMordersWHEREorder_no=123456;EXPLAINSELECT*FROMordersWHEREorder_no='ORD20240601001';-- 场景 2：函数操作列EXPLAINSELECT*FROMordersWHEREDATE(created_at)='2024-06-01';EXPLAINSELECT*FROMordersWHEREcreated_at>='2024-06-01 00:00:00'ANDcreated_at<'2024-06-02 00:00:00';-- 场景 3：左模糊匹配EXPLAINSELECT*FROMordersWHEREorder_noLIKE'%01001';EXPLAINSELECT*FROMordersWHEREorder_noLIKE'ORD%';-- 场景 4：OR 条件EXPLAINSELECT*FROMordersWHEREstatus=1ORtotal_amount>1000;EXPLAINSELECT*FROMordersWHEREstatus=1UNIONALLSELECT*FROMordersWHEREtotal_amount>1000;-- 场景 5：最左前缀EXPLAINSELECT*FROMordersWHEREstatus=1;EXPLAINSELECT*FROMordersWHEREuser_id=1001ANDstatus=1;-- 场景 6：列运算EXPLAINSELECT*FROMordersWHEREid+1=101;EXPLAINSELECT*FROMordersWHEREid=100;-- 场景 7：不等于EXPLAINSELECT*FROMordersWHEREstatus<>0;EXPLAINSELECT*FROMordersWHEREstatus!=4;-- 场景 8：IS NOT NULLEXPLAINSELECT*FROMordersWHEREupdated_atISNOTNULL;-- 场景 9：NOT INEXPLAINSELECT*FROMordersWHEREstatusNOTIN(3,4);EXPLAINSELECT*FROMordersWHEREstatusIN(0,1,2);-- 场景 10：数据分布SELECTstatus,COUNT(*)FROMordersGROUPBYstatus;EXPLAINSELECT*FROMordersWHEREstatus=0;EXPLAINSELECT*FROMordersFORCEINDEX(idx_status)WHEREstatus=0;