叙事框架现象 → 排查过程 → 根因 → 修复 → 预防问题现象MySQL 索引失效是导致慢查询最常见的原因之一。本文基于一个电商订单表的测试环境系统验证了 10 种索引失效场景隐式类型转换、函数操作列、左模糊匹配、OR 条件连接、联合索引最左前缀不满足、列运算、不等于操作、IS NOT NULL、NOT IN 子查询以及数据分布不均导致优化器弃用索引。每种场景均提供错误写法与正确写法的 EXPLAIN 输出对比结合 BTree 结构图和优化器成本模型分析根因。排查过程十种场景逐一验证场景 1隐式类型转换第一个验证的也是最容易犯的——隐式类型转换。-- 错误写法传入数字EXPLAINSELECT*FROMordersWHEREorder_no123456;-- 正确写法传入字符串EXPLAINSELECT*FROMordersWHEREorder_noORD20240601001;两行 SQL 差别只在于order_no传的值是数字还是字符串但 EXPLAIN 的结果完全不同。错误写法走了typeALL全表扫描虽然possible_keys有idx_order_no但keyNULL——优化器认为这个索引不能用。原因很简单order_no是varchar类型MySQL 在比较时做了隐式类型转换相当于CAST(order_no AS int)函数包裹索引列导致索引失效。正确写法走了typerefkeyidx_order_norows1效率天差地别。这个场景在实际生产中极其常见——ORM 框架自动生成的 SQL 尤其容易踩坑。比如 MyBatis 的#{}和${}混用或者从 HTTP 参数直接传入数字类型的查询条件。场景 2函数操作列第二个场景和第一个有相似之处——都是对索引列动了手脚。-- 错误写法EXPLAINSELECT*FROMordersWHEREDATE(created_at)2024-06-01;-- 正确写法EXPLAINSELECT*FROMordersWHEREcreated_at2024-06-01 00:00:00ANDcreated_at2024-06-02 00:00:00;DATE(created_at)这个函数包裹后possible_keys直接变成NULL索引完全不可用。改成范围查询后typerange、keyidx_created_at、rows从 510 降到 10。其他函数也一样LENGTH(mobile)、CONCAT(order_no, )、YEAR(created_at)——任何函数操作在索引列上都会让 BTree 无法正常遍历。场景 3左模糊匹配第三个场景是 LIKE 查询的老问题。-- 全表扫描EXPLAINSELECT*FROMordersWHEREorder_noLIKE%01001;-- 走索引EXPLAINSELECT*FROMordersWHEREorder_noLIKEORD%;%在左边时typeALL在右边时typerange、keyidx_order_no。BTree 的索引结构决定了它只能从左向右匹配前缀左模糊意味着无法确定起始位置只能全表扫描。场景 4OR 条件连接OR 是个特殊场景——它的行为取决于两边列是否都有索引。-- 两边都有索引 → 走 index_mergeEXPLAINSELECT*FROMordersWHEREstatus1ORemailuser001example.com;-- 有一边没索引 → 全表扫描EXPLAINSELECT*FROMordersWHEREstatus1ORtotal_amount1000;第一行 SQL 中status和email都有索引MySQL 选择了index_merge索引合并分别从两个索引取结果再合并。第二行中total_amount没有索引possible_keys只有idx_status但keyNULL——优化器判断全表扫描比走单边索引再过滤另一边更划算。OR 问题的标准改法是拆成 UNIONEXPLAINSELECT*FROMordersWHEREstatus1UNIONALLSELECT*FROMordersWHEREtotal_amount1000;UNION 改写后两边都能独立走索引。场景 5不满足最左前缀联合索引idx_user_status(user_id, status)的验证让刘老师格外关注。-- 只查 status → 不走联合索引EXPLAINSELECT*FROMordersWHEREstatus1;-- 带上 user_id → 走联合索引EXPLAINSELECT*FROMordersWHEREuser_id1001ANDstatus1;第一行查status 1时possible_keys有idx_status单列索引但idx_user_status不可用——因为联合索引(user_id, status)的最左列是user_id跳过它直接查status无法利用这个索引。第二行带上user_id 1001后keyidx_user_status、key_len5int 各 4 字节 1 字节可空、rows2。最左前缀的规则是索引(a, b, c)可以匹配(a)、(a, b)、(a, b, c)但(b)、(c)、(b, c)不行。设计联合索引时通常把选择性高的列放最左边。场景 6列运算-- 全表扫描EXPLAINSELECT*FROMordersWHEREid1101;-- const 查找EXPLAINSELECT*FROMordersWHEREid100;id 1 101这种写法对列做了运算possible_keysNULL全表扫描。改成id 100就能走主键const查找。这个场景最简单的修复就是把运算移到等号右边id 1 101→id 100。场景 7! 或 操作不等于查询的行为取决于数据分布。-- status ! 0 → 全表扫描83% 的行匹配EXPLAINSELECT*FROMordersWHEREstatus0;-- status ! 4 → 走 range 索引匹配少部分行EXPLAINSELECT*FROMordersWHEREstatus!4;status 0匹配约 83% 的行优化器认为回表成本太高选择全表扫描。但status ! 4时——如果status4已取消的订单很少——优化器评估回表成本后有可能走索引。场景 8IS NOT NULLNULL 在 MySQL 索引中有特殊处理方式。EXPLAINSELECT*FROMordersWHEREupdated_atISNOTNULL;EXPLAINSELECT*FROMordersWHEREdeleted_atISNULL;两个查询都是typeALL。IS NULL和IS NOT NULL通常不走索引因为 NULL 在 BTree 中的存储位置与其他值不同且 MySQL 索引默认不存储全 NULL 值的行某些存储引擎有差异。优化方案尽量用默认值替代 NULL。比如deleted_at可以用1970-01-01表示未删除这样查询就能走索引。场景 9NOT IN / NOT EXISTS-- 全表扫描EXPLAINSELECT*FROMordersWHEREstatusNOTIN(3,4);-- 走 range 索引EXPLAINSELECT*FROMordersWHEREstatusIN(0,1,2);NOT IN 全表扫描IN 走 range 索引。子查询 NOT IN 更严重——外层表必然全表扫描。-- 子查询 NOT IN全表扫描EXPLAINSELECT*FROMordersWHEREidNOTIN(SELECTidFROMordersWHEREstatus4);NOT IN 的替代方案是LEFT JOIN IS NULLSELECTo.*FROMorders oLEFTJOIN(SELECTidFROMordersWHEREstatus4)tONo.idt.idWHEREt.idISNULL;场景 10数据分布不均最后这个场景和张工的日常工作最相关——优化器弃用索引。-- status0 占 108/610 ≈ 18%EXPLAINSELECT*FROMordersWHEREstatus0;status的数据分布基本均匀每种状态约 100 行所以查 status0 走了typeref。但如果数据倾斜——status0 占了 80%——优化器就会选择全表扫描。这不是索引失效而是优化器基于成本估算的判断走索引需要 200 万次随机读回表不如直接顺序扫描 1000 万行数据。可以用FORCE INDEX验证成本差异EXPLAINSELECT*FROMordersFORCEINDEX(idx_status)WHEREstatus0;根因分析子原因 1BTree 索引结构限制BTree 索引是按索引列的值有序存储的查询时通过二分查找定位。但如果对索引列做了任何变形函数、运算、类型转换MySQL 无法预知变形后的值在树中的位置只能放弃索引。这和查字典一样——你知道拼音就能快速定位但如果要求把所有拼音反转后的词找出来字典就没法用了。子原因 2优化器成本估算优化器的决策基于成本模型全表扫描成本 vs 索引扫描 回表成本。当查询返回的行数超过总行数的一定比例通常 15-20%优化器倾向全表扫描。不等于、NOT IN、IS NOT NULL 之所以不走索引本质上都是因为优化器预估回表成本高于全表扫描。数据分布越均匀、索引选择性越低优化器越倾向于全表扫描。子原因 3NULL 值在索引中的特殊处理MySQL 索引对 NULL 值有特殊规则IS NULL、IS NOT NULL、 NULL都不走索引某些特定场景下IS NULL可能走取决于版本和优化器。设计表结构时能用NOT NULL DEFAULT就不要允许 NULL。修复方案第一步识别风险 SQL每次写 SQL 前先跑 EXPLAIN重点关注三点typeALL的必须审查possible_keys有值但keyNULL的——优化器认为索引没用rows超过预期数量级的第二步建立正确写法规范陈哥整理了一份索引失效对照表总结每种场景的错误写法和正确写法。团队决定以后所有查询类 SQL 变更必须附带 EXPLAIN 输出。张工还计划在 CI 流程中加入 SQL 规范检查工具。第三步日常索引诊断除了牢记 10 种失效场景日常维护中也要定期检查索引使用情况。SHOW INDEX FROM查看索引定义performance_schema.table_io_waits_summary_by_index_usage查看索引是否被使用过EXPLAIN FORMATJSON查看优化器成本明细。验证结果即时指标验证完成后陈哥用EXPLAIN重新检查了团队正在开发的导出 SQL——发现其中至少 4 处索引失效order_no传入数字隐式类型转换WHERE DATE(created_at)函数列OR 条件一边无索引status NOT IN子查询全部修正后全表扫描查询从原来的全部变为零预估扫描行数从 510 降到个位数。团队复盘陈哥在群里发了验证总结列出了 10 种场景的验证结论。刘老师感慨之前只知道加索引会快不知道为什么快、什么时候不快。现在至少能说出 10 种索引不走的原因了。CI 集成 SQL 规范检查的提议也得到了王哥的认可。避坑建议EXPLAIN 是基本功不是高阶技能每次写 SQL 都要跑 EXPLAIN养成习惯后一眼就能看出问题。不要等到慢查询告警了才看。ORM 框架不背锅但要注意参数类型MyBatis、JPA 生成的 SQL 可能因为参数类型推断错误导致隐式转换要检查实际执行的 SQL开启showSql或log4jdbc。测试环境数据量要和生产相当1000 行数据全表扫描只要几毫秒1000 万行就是灾难。在测试环境用 EXPLAIN 时要确保 rows 的数量级和生产一致。联合索引设计遵循最左前缀 高选择性在前索引(a, b, c)等价于走了(a)、(a, b)、(a, b, c)的查询(b)和(c)单独走不了。把选择性最高的列放最左边。NULL 能不用就不用设计字段时优先用默认值替代 NULL不仅节省存储空间还能避免 IS NULL/IS NOT NULL 不走索引的问题。≠ 和 NOT IN 不一定非要避免但要了解成本不等值查询在高选择性场景下也可能走索引关键是用 EXPLAIN 确认。如果不能走索引改用 LEFT JOIN 或拆分查询。函数列是最容易被忽略的DATE(created_at)、YEAR(created_at) 看着无害但函数一包索引就废。用范围查询替代日期函数。OR 连接时保证两边都有索引如果无法控制用 UNION ALL 拆开。附完整命令清单-- 建表 索引USEindex_test;SHOWINDEXFROMorders;SELECTCOUNT(*)FROMorders;-- 场景 1隐式类型转换EXPLAINSELECT*FROMordersWHEREorder_no123456;EXPLAINSELECT*FROMordersWHEREorder_noORD20240601001;-- 场景 2函数操作列EXPLAINSELECT*FROMordersWHEREDATE(created_at)2024-06-01;EXPLAINSELECT*FROMordersWHEREcreated_at2024-06-01 00:00:00ANDcreated_at2024-06-02 00:00:00;-- 场景 3左模糊匹配EXPLAINSELECT*FROMordersWHEREorder_noLIKE%01001;EXPLAINSELECT*FROMordersWHEREorder_noLIKEORD%;-- 场景 4OR 条件EXPLAINSELECT*FROMordersWHEREstatus1ORtotal_amount1000;EXPLAINSELECT*FROMordersWHEREstatus1UNIONALLSELECT*FROMordersWHEREtotal_amount1000;-- 场景 5最左前缀EXPLAINSELECT*FROMordersWHEREstatus1;EXPLAINSELECT*FROMordersWHEREuser_id1001ANDstatus1;-- 场景 6列运算EXPLAINSELECT*FROMordersWHEREid1101;EXPLAINSELECT*FROMordersWHEREid100;-- 场景 7不等于EXPLAINSELECT*FROMordersWHEREstatus0;EXPLAINSELECT*FROMordersWHEREstatus!4;-- 场景 8IS NOT NULLEXPLAINSELECT*FROMordersWHEREupdated_atISNOTNULL;-- 场景 9NOT INEXPLAINSELECT*FROMordersWHEREstatusNOTIN(3,4);EXPLAINSELECT*FROMordersWHEREstatusIN(0,1,2);-- 场景 10数据分布SELECTstatus,COUNT(*)FROMordersGROUPBYstatus;EXPLAINSELECT*FROMordersWHEREstatus0;EXPLAINSELECT*FROMordersFORCEINDEX(idx_status)WHEREstatus0;
