上周帮同事看一条慢 SQL他的第一句话是“索引我加了啊。”表上有索引EXPLAIN一看type是ALLkey是NULL。他盯着屏幕看了半天说“这不科学。”其实很科学。MySQL 的优化器不是看见索引就必须用它要做成本估算。而且更多时候索引确实“在”只是你的写法让它没法被用上。这篇文章把常见的索引失效情况整理了一遍附上可复现的 SQL 和EXPLAIN输出。下次遇到慢查询可以按这个清单逐条过一遍八九不离十。一、先建个能跑的实验环境CREATETABLEt_order(idBIGINTPRIMARYKEYAUTO_INCREMENT,order_noVARCHAR(32)NOTNULL,user_idBIGINTNOTNULL,statusTINYINTNOTNULLDEFAULT0,amountDECIMAL(10,2),create_timeDATETIMENOTNULL,remarkVARCHAR(100),KEYidx_user_status(user_id,status),KEYidx_create_time(create_time),KEYidx_order_no(order_no))ENGINEInnoDBDEFAULTCHARSETutf8mb4;后面所有例子都基于这张表。你可以自己灌几万条数据观察会更明显数据量太小时优化器倾向于直接全表扫描这是正常现象下面第七点会讲。二、情况 1违反最左前缀原则联合索引的头丢了EXPLAINSELECT*FROMt_orderWHEREstatus1\Gidx_user_status是(user_id, status)这个顺序建的。查询条件只给了status相当于把索引的第一列跳过去了。B 树是先按user_id排序、再按status排序的光知道status无法定位起始位置这个索引就废了。反过来EXPLAINSELECT*FROMt_orderWHEREuser_id100\G-- 命中EXPLAINSELECT*FROMt_orderWHEREuser_id100ANDstatus1\G-- 命中这两条都能用上。注意第二条里status也参与了过滤但只有user_id那一段用于“定位”key_len会变长这是判断的好办法。口诀联合索引像电话簿先姓后名。你只知道名查不了。顺带一提OR和范围查询会截断前缀-- user_id 能用status 用不上范围之后的列失效SELECT*FROMt_orderWHEREuser_id100ANDstatus1ANDcreate_timeNOW();这里status 1是范围条件它后面的列无法继续用于索引定位但在 5.7 的 ICP 优化下status仍可在存储引擎层做过滤Extra会显示Using index condition。三、情况 2索引列上套了函数或表达式-- 看着很自然但索引废了SELECT*FROMt_orderWHEREDATE(create_time)2024-06-01;-- 这样写才能走 idx_create_timeSELECT*FROMt_orderWHEREcreate_time2024-06-01 00:00:00ANDcreate_time2024-06-02 00:00:00;同理WHEREuser_id11001-- 废WHEREuser_id1001-1-- 能走常量表达式会被优化器先算掉WHEREamount*1005000-- 废WHEREamount5000/100-- 能走**原则很简单索引列必须“裸着”出现在比较运算符的一侧。**任何函数、运算、隐式转换都会破坏 B 树的有序性。这条规则几乎适用于所有关系型数据库不止 MySQL。四、情况 3隐式类型转换最阴间的一种-- user_id 是 BIGINTSELECT*FROMt_orderWHEREuser_id100;-- 能走字符串转数字安全SELECT*FROMt_orderWHEREorder_no123456;-- 大概率废掉第二种情况order_no是VARCHAR你传了个数字。MySQL 会把索引列转成数字再做比较等价于WHERE CAST(order_no AS SIGNED) 123456回到情况 2。这种问题在 ORM 和 MyBatis 里特别容易藏住——Java 里的Long和String混着用编译期不报错运行时也不报错就是查询慢。排查方法EXPLAIN看到typeALL且Extra里有Using where而条件列明明有索引时第一件事就是去对比字段定义类型和传入值的类型是否完全一致。顺便检查字符集utf8vsutf8mb4和排序规则collation不一致也会触发类似问题。五、情况 4LIKE 前置通配符SELECT*FROMt_orderWHEREorder_noLIKEORD%;-- 能走SELECT*FROMt_orderWHEREorder_noLIKE%1234;-- 废掉SELECT*FROMt_orderWHEREorder_noLIKE%1234%;-- 废掉B 树是前缀有序的后缀没有序可言。如果业务确实需要模糊匹配后缀可以考虑倒序存储一份冗余字段再建索引把后缀变前缀改用 Elasticsearch 等搜索引擎做全文检索小表的话全表扫描未必比回表慢别急着优化。补充一点LIKE ORD%这种前缀匹配是能走索引的而且是range级别很多人误以为只要带%就失效。六、情况 5OR 条件有一边没索引SELECT*FROMt_orderWHEREuser_id100ORremark加急;remark上没有索引整条语句就会退化成全表扫描——即使user_id那边本来能命中。因为最终结果集要做并集一边扫全表另一边就没必要走索引了。解法SELECT*FROMt_orderWHEREuser_id100UNIONALLSELECT*FROMt_orderWHEREremark加急ANDuser_id100;拆成两条各自走索引再拼起来。注意UNION会去重并产生临时表能确定不重复就用UNION ALL。同理NOT IN、、IS NOT NULL这些否定类条件通常会让优化器放弃索引——但不是绝对。如果走的是覆盖索引见第八点它仍然可能选择索引扫描。所以别死记“用了 ! 就一定失效”要以EXPLAIN为准。七、情况 6优化器算下来觉得全表更便宜索引在但不用这一条是很多人卡壳的地方写法没问题索引也没问题可它就是不用。常见原因原因说明数据量太小几十几百行全表扫描一次 IO 比回表还少选择性太差status只有 0/1/2 三个值命中一半以上的行回表成本高于全表回表代价高SELECT *要回主键索引取所有列列越多越亏统计信息过期ANALYZE TABLE一下可能就好了聚簇因子差索引顺序和物理行顺序差异大随机 IO 多验证方法很简单把SELECT *改成只查索引列。-- 原来 typeALLSELECT*FROMt_orderWHEREstatus1;-- 改成覆盖索引大概率 typeindex 或 rangeExtraUsing indexSELECTuser_id,statusFROMt_orderWHEREstatus1;这就是覆盖索引的威力查询所需的所有列都在索引树上不需要回主键索引回表。这也是为什么线上经常看到“冗余单列索引”——不是为了多一种检索路径而是为了凑出覆盖索引。如果确认优化器选错了可以用FORCE INDEX(idx_name)强制指定但这是权宜之计。更好的做法是改写 SQL、补联合索引或者用ANALYZE TABLE刷新统计信息。八、情况 7ORDER BY / GROUP BY 和索引方向打架-- 能利用索引排序Extra 为空没有 Using filesortSELECT*FROMt_orderWHEREuser_id100ORDERBYstatus;-- 一个升一个降索引排序失效出现 Using filesortSELECT*FROMt_orderWHEREuser_id100ORDERBYstatusDESC,create_timeASC;几个细节ORDER BY的列如果不是索引的最左前缀或者中间隔了非等值条件就无法利用索引排序多表 JOIN 时ORDER BY的列不属于驱动表基本也要 filesortfilesort不等于慢。内存里的 quicksort 很快真正拖垮性能的是“排序的数据量太大导致落盘”。关注sort_merge_passes这个状态变量以及ORDER BY ... LIMIT组合这个组合其实很高效因为只需排前 N 条。九、情况 8JOIN 关联字段类型/字符集不一致SELECTo.*,u.nameFROMt_order oJOINt_user uONo.user_idu.id;如果o.user_id是BIGINT而u.id是VARCHAR(20)或者一个是utf8一个是utf8mb4驱动表和被驱动表的索引都可能失效。这种问题在分库分表迁移、老系统合并时极其常见而且EXPLAIN里往往看不出来只能靠人工核对表结构。建议建表规范里写死一条——所有表的自增主键统一类型、统一字符集、统一 collation。前期省下的事后期全是债。十、把 EXPLAIN 读薄只看五个字段EXPLAIN输出十几列日常排查真正有用的就这几个字段看什么type访问类型从上到下依次变差system const eq_ref ref range index ALL。线上 SQL 至少要到ref/range出现ALL就要警觉key实际用到的索引。NULL就是没用索引possible_keys候选索引。有值但keyNULL说明优化器放弃了多半是第七点key_len索引使用的字节数。用来判断联合索引用到了几列越长用得越多Extra关键信息集中地Using index覆盖索引好Using where需要回表或额外过滤Using filesort外部排序警惕Using temporary临时表高度警惕一个经验rows × rows的乘积大致反映 JOIN 的扫描量级。多表 JOIN 时每一行的rows都要看不能只看第一行。另外两个很有用的变体EXPLAINFORMATJSONSELECT...\G-- 看优化器的详细决策、cost、chosen 索引EXPLAINANALYZESELECT...\G-- 8.0真实执行一遍给出实际行数和耗时EXPLAIN ANALYZE是排查“估计不准”的神器它能告诉你优化器预估的行数和实际返回的行数差了多少倍——差距过大基本就是统计信息的问题。十一、一份可抄的检查清单遇到慢查询按这个顺序过有没有索引看possible_keys。没有就考虑加。有没有被用上看key。NULL就往下逐条排查。是不是最左前缀断了检查联合索引列顺序和条件列。索引列是不是“裸”的找函数、运算、隐式转换。类型和字符集是否完全一致尤其 ORM 传参和 JOIN 关联字段。有没有%xxx、OR、NOT IN 这类结构尝试改写。是不是SELECT *导致回表太贵试试缩小列看能否变成覆盖索引。数据量和选择性是否合理跑ANALYZE TABLE看看是不是统计信息过期。能不能改成覆盖索引这是性价比最高的优化手段之一。最后才考虑FORCE INDEX并且要注释写明原因和日期。写在最后回到开头那个同事的问题。他那条 SQL 的问题出在第三点order_no是VARCHAR代码里传了个Long发生了隐式类型转换。改完类型之后type从ALL变成ref耗时从 3.2s 降到 18ms。他改完说了一句挺有意思的话“原来索引不是加了就有用。”对索引是一种需要被“配合”的结构。它不会主动工作它只在你的查询条件和它的组织方式对齐时才生效。所以调优的第一原则从来不是“加索引”而是先看执行计划再动表结构。花五分钟读懂EXPLAIN那五个字段比你盲目加十个索引管用得多。