上周帮同事看一条慢 SQL，他的第一句话是：“索引我加了啊。”
表上有索引，EXPLAIN一看type是ALL，key是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))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;后面所有例子都基于这张表。你可以自己灌几万条数据，观察会更明显（数据量太小时优化器倾向于直接全表扫描，这是正常现象，下面第七点会讲）。
二、情况 1：违反最左前缀原则（联合索引的头丢了）
EXPLAINSELECT*FROMt_orderWHEREstatus=1\Gidx_user_status是(user_id, status)这个顺序建的。查询条件只给了status，相当于把索引的第一列跳过去了。B+ 树是先按user_id排序、再按status排序的，光知道status无法定位起始位置，这个索引就废了。
反过来：
EXPLAINSELECT*FROMt_orderWHEREuser_id=100\G-- 命中EXPLAINSELECT*FROMt_orderWHEREuser_id=100ANDstatus=1\G-- 命中这两条都能用上。注意第二条里status也参与了过滤，但只有user_id那一段用于“定位”（key_len会变长，这是判断的好办法）。
口诀：联合索引像电话簿，先姓后名。你只知道名，查不了。
顺带一提，OR和范围查询会截断前缀：
-- user_id 能用，status 用不上（范围之后的列失效）SELECT*FROMt_orderWHEREuser_id=100ANDstatus>1ANDcreate_time>NOW();这里status > 1是范围条件，它后面的列无法继续用于索引定位（但在 5.7+ 的 ICP 优化下，status仍可在存储引擎层做过滤，Extra会显示Using index condition）。
三、情况 2：索引列上套了函数或表达式
-- 看着很自然，但索引废了SELECT*FROMt_orderWHEREDATE(create_time)='2024-06-01';-- 这样写才能走 idx_create_timeSELECT*FROMt_orderWHEREcreate_time>='2024-06-01 00:00:00'ANDcreate_time<'2024-06-02 00:00:00';同理：
WHEREuser_id+1=1001-- 废WHEREuser_id=1001-1-- 能走（常量表达式会被优化器先算掉）WHEREamount*100>5000-- 废WHEREamount>5000/100-- 能走**原则很简单：索引列必须“裸着”出现在比较运算符的一侧。**任何函数、运算、隐式转换都会破坏 B+ 树的有序性。这条规则几乎适用于所有关系型数据库，不止 MySQL。
四、情况 3：隐式类型转换（最阴间的一种）
-- user_id 是 BIGINTSELECT*FROMt_orderWHEREuser_id='100';-- 能走，字符串转数字安全SELECT*FROMt_orderWHEREorder_no=123456;-- 大概率废掉第二种情况：order_no是VARCHAR，你传了个数字。MySQL 会把索引列转成数字再做比较，等价于WHERE CAST(order_no AS SIGNED) = 123456，回到情况 2。
这种问题在 ORM 和 MyBatis 里特别容易藏住——Java 里的Long和String混着用，编译期不报错，运行时也不报错，就是查询慢。
排查方法：EXPLAIN看到type=ALL且Extra里有Using where，而条件列明明有索引时，第一件事就是去对比字段定义类型和传入值的类型是否完全一致。顺便检查字符集（utf8vsutf8mb4）和排序规则（collation）不一致也会触发类似问题。
五、情况 4：LIKE 前置通配符
SELECT*FROMt_orderWHEREorder_noLIKE'ORD%';-- 能走SELECT*FROMt_orderWHEREorder_noLIKE'%1234';-- 废掉SELECT*FROMt_orderWHEREorder_noLIKE'%1234%';-- 废掉B+ 树是前缀有序的，后缀没有序可言。如果业务确实需要模糊匹配后缀，可以考虑：
- 倒序存储一份冗余字段，再建索引（把后缀变前缀）；
- 改用 Elasticsearch 等搜索引擎做全文检索；
- 小表的话，全表扫描未必比回表慢，别急着优化。
补充一点：LIKE 'ORD%'这种前缀匹配是能走索引的，而且是range级别，很多人误以为只要带%就失效。
六、情况 5：OR 条件有一边没索引
SELECT*FROMt_orderWHEREuser_id=100ORremark='加急';remark上没有索引，整条语句就会退化成全表扫描——即使user_id那边本来能命中。因为最终结果集要做并集，一边扫全表，另一边就没必要走索引了。
解法：
SELECT*FROMt_orderWHEREuser_id=100UNIONALLSELECT*FROMt_orderWHEREremark='加急'ANDuser_id<>100;拆成两条各自走索引，再拼起来。（注意UNION会去重并产生临时表，能确定不重复就用UNION ALL。）
同理，NOT IN、<>、IS NOT NULL这些否定类条件，通常会让优化器放弃索引——但不是绝对。如果走的是覆盖索引（见第八点），它仍然可能选择索引扫描。所以别死记“用了 != 就一定失效”，要以EXPLAIN为准。
七、情况 6：优化器算下来觉得全表更便宜（索引在，但不用）
这一条是很多人卡壳的地方：写法没问题，索引也没问题，可它就是不用。
常见原因：
| 原因 | 说明 |
|---|---|
| 数据量太小 | 几十几百行，全表扫描一次 IO 比回表还少 |
| 选择性太差 | status只有 0/1/2 三个值，命中一半以上的行，回表成本高于全表 |
| 回表代价高 | SELECT *要回主键索引取所有列，列越多越亏 |
| 统计信息过期 | ANALYZE TABLE一下可能就好了 |
| 聚簇因子差 | 索引顺序和物理行顺序差异大，随机 IO 多 |
验证方法很简单：把SELECT *改成只查索引列。
-- 原来 type=ALLSELECT*FROMt_orderWHEREstatus=1;-- 改成覆盖索引，大概率 type=index 或 range，Extra=Using indexSELECTuser_id,statusFROMt_orderWHEREstatus=1;这就是覆盖索引的威力：查询所需的所有列都在索引树上，不需要回主键索引（回表）。这也是为什么线上经常看到“冗余单列索引”——不是为了多一种检索路径，而是为了凑出覆盖索引。
如果确认优化器选错了，可以用FORCE INDEX(idx_name)强制指定，但这是权宜之计。更好的做法是改写 SQL、补联合索引，或者用ANALYZE TABLE刷新统计信息。
八、情况 7：ORDER BY / GROUP BY 和索引方向打架
-- 能利用索引排序，Extra 为空（没有 Using filesort）SELECT*FROMt_orderWHEREuser_id=100ORDERBYstatus;-- 一个升一个降，索引排序失效，出现 Using filesortSELECT*FROMt_orderWHEREuser_id=100ORDERBYstatusDESC,create_timeASC;几个细节：
ORDER BY的列如果不是索引的最左前缀，或者中间隔了非等值条件，就无法利用索引排序；- 多表 JOIN 时，
ORDER BY的列不属于驱动表，基本也要 filesort； filesort不等于慢。内存里的 quicksort 很快，真正拖垮性能的是“排序的数据量太大导致落盘”。关注sort_merge_passes这个状态变量，以及ORDER BY ... LIMIT组合（这个组合其实很高效，因为只需排前 N 条）。
九、情况 8：JOIN 关联字段类型/字符集不一致
SELECTo.*,u.nameFROMt_order oJOINt_user uONo.user_id=u.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 | 候选索引。有值但key=NULL，说明优化器放弃了，多半是第七点 |
key_len | 索引使用的字节数。用来判断联合索引用到了几列（越长用得越多） |
Extra | 关键信息集中地：Using index=覆盖索引（好）；Using where=需要回表或额外过滤；Using filesort=外部排序（警惕）；Using temporary=临时表（高度警惕） |
一个经验：rows × rows的乘积大致反映 JOIN 的扫描量级。多表 JOIN 时每一行的rows都要看，不能只看第一行。
另外两个很有用的变体：
EXPLAINFORMAT=JSONSELECT...\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那五个字段，比你盲目加十个索引管用得多。
