MySQL索引失效排查与治理：从EXPLAIN到慢查询优化实战

1. 先还原一场压测事故：一条DATE()函数让接口延迟翻了20倍

90%这个数字多少有点标题党，但换成"九成新手会在索引上至少踩一个坑"，我觉得一点不夸张。先给你讲个真实发生在我身边的压测事故，因为这个例子几乎把所有索引失效的坑串在了一起。

某业务系统的订单查询接口，日常P95延迟在80ms左右。促销前压测，流量一上来，P95直接飙到2100ms，紧接着就是数据库连接池告急、慢查询堆积。当时大家第一反应是缓存没命中、SQL太复杂，结果把慢查询日志捞出来一看，清一色是同一句：

SELECT id, order_no, user_id, amount FROM orders WHERE DATE(create_time) = '2024-05-20' AND status = 1 ORDER BY id DESC LIMIT 20;

表上明明建了idx_create_time (create_time)和idx_status (status)两个单列索引，EXPLAIN出来却走的是全表扫描，扫了八百多万行。根因就是DATE(create_time)这个函数调用——索引列被套上函数之后，B+树里存的是原始时间值，优化器没法直接拿"函数运算后的结果"去做二分查找，只能放弃索引。

那次事故排查完，团队成员总结了三条教训：第一，写SQL前先问一句"索引列有没有被表达式包裹"；第二，任何查询上线前必须过一遍EXPLAIN；第三，压测不是只测QPS，必须盯慢查询日志的变化。这三条看起来简单，但现实中能做到的人真不多。下面我把这些年积累的索引失效场景、优化器行为、以及一套可落地的排查流程，一次讲清楚。

2. 六类让索引失效的SQL写法，你中了哪几条

2.1 对索引列套函数：最容易被忽略的失效率

开头那个事故就是典型。除了DATE()，你在实际业务里还会见到YEAR(create_time)、MONTH(created_at)、CONCAT(a, b)、price * 0.8 > 100这类写法。它们的本质都一样：B+树索引中的键值是按列原始值组织的，函数或表达式改变了比较的目标，索引的排列顺序就派不上用场。

正确的做法是改写成范围查询。拿日期来说：

-- 错误：无法使用 idx_create_time SELECT * FROM orders WHERE DATE(create_time) = '2024-05-20'; -- 正确：使用索引范围扫描 SELECT * FROM orders WHERE create_time >= '2024-05-20 00:00:00' AND create_time < '2024-05-21 00:00:00';

MySQL从8.0.13开始支持函数索引，但那是另一套设计思路。如果你还在用5.7或者想保持SQL的可移植性，改写范围查询永远是最稳的方案。

2.2 隐式类型转换：int和varchar互转的隐蔽陷阱

很多同学没意识到，字符串字段和数字字段比较时，MySQL会触发隐式类型转换。比如phone字段是VARCHAR(20)，你写：

SELECT * FROM users WHERE phone = 13800138000;

优化器会把phone列 CAST 成数字再比较。这一转，索引列的原始值形态被改变，索引直接失效。反过来，如果字段本身是INT，你写WHERE id = '10086'，MySQL把字符串转成数字，索引不受影响，但也没必要刻意写字符串。

判断方法很简单：EXPLAIN里看到type从const或ref变成ALL，并且Extra列出现Using where，基本就是类型转换或函数包装导致的。修复方式是把参数改成和字段一致的类型，从源头消除转换。

2.3 前缀模糊匹配：LIKE '%keyword' 的代价

LIKE是另一个高频失效点。WHERE name LIKE '张%'是可以走索引的，因为MySQL可以用"张"作为范围起点去B+树里扫。但WHERE name LIKE '%张%'或WHERE name LIKE '%张'就不行——起始位置不确定，无法利用索引的有序性。

这类模糊搜索，如果只是低并发的小表，全表扫描勉强能接受。但表一大，就得考虑几条路：业务允许的话改成前缀匹配；用全文索引（MySQL的FULLTEXT，或者外部的搜索中间件）；再不然建一个专门的反向列或映射表。最忌讳的是明知前缀匹配会全表扫，还硬生生在name列上建个索引自我安慰——那个索引根本不会被执行器选中。

2.4 联合索引最左前缀误用：顺序错位的根本原理

联合索引(a, b, c)，B+树先按a排序，a相同再按b排序，最后按c。于是下列查询的"实际能用到的列数"差异很大：

WHERE a = 1 AND b = 2 AND c = 3 -- 三列全用上 WHERE a = 1 AND b = 2 -- 用 a、b WHERE a = 1 AND c = 3 -- 只用 a，c 无法定位 WHERE b = 2 AND c = 3 -- 完全无法使用 WHERE a > 1 AND b = 2 -- 只用到 a，b 用于过滤但不参与索引定位

很多人背口诀"最左前缀"，但没理解背后的排序逻辑，于是写出WHERE b = 2 AND c = 3这种"最左列缺失"的查询，然后困惑为什么建了联合索引却不生效。

修正思路是：把等值条件的列放在联合索引靠左的位置。比如订单表高频查询是status和create_time组合，那就建(status, create_time)，不要建成(create_time, status)。因为status是精确等值，create_time是范围，精等列在前，范围列在后，索引效率才最高。

2.5 OR条件让优化器左右为难

WHERE user_id = 10086 OR status = 1，如果user_id和status都有单列索引，MySQL 5.0之后有一定概率走index_merge（索引合并），把两组结果取并集。但如果两边只有一边有索引，或者合并成本高于全表扫描，优化器直接放弃索引。

这不是说OR绝对不能用，而是你要意识到它的不确定性。业务上如果两个条件覆盖的数据范围都很大，建议拆成两条SQL用UNION合并，或者干脆让应用层处理。依赖优化器做"灵机一动"的决策，生产环境迟早会踩雷。

2.6 范围查询把后续索引列"挤"出查找路径

前面提过的(create_time, status)索引，如果查询写成：

WHERE create_time >= '2024-01-01' AND create_time < '2024-02-01' AND status = 1;

虽然create_time可以用到索引范围扫描，但status没办法继续在B+树里定位了——因为范围之后的列，其有序性被打破，只能对扫描出来的每行做内存过滤。这是联合索引设计的经典考点：范围条件列之后的所有列，对"减少扫描行数"没有贡献。

遇到这类需求，我的建议是尽量把等值列提前，改成(status, create_time)索引。如果无法调整，就要接受status属于"索引内过滤"这一现实，并配合覆盖索引、索引下推等手段降低回表开销（下一章细讲）。

3. 回表与覆盖索引：命中了索引，查询为什么仍然慢

3.1 从InnoDB的物理结构理解回表

InnoDB的索引分两类：聚簇索引（主键索引）和二级索引。聚簇索引的叶子节点直接存整行数据；二级索引的叶子节点存的是"索引列值 + 主键值"。所以当你查二级索引，并且SELECT的列里包含索引之外的其他字段，比如只建了idx_user_id (user_id)，却SELECT user_id, nickname，数据库必须先走二级索引找到主键，再拿着主键回聚簇索引取nickname——这个"二次查找"就是回表。

回表不是免费午餐。对于高并发低延迟的查询，每次回表至少多一次随机IO。如果二级索引扫描了1万行，回表1万次，性能可想而知。很多人只看EXPLAIN里key非空就觉得万事大吉，忽略了Extra里没有Using index，其实依然存在大量回表。

3.2 覆盖索引：让一条SQL少访问一半数据

覆盖索引的意思是：查询的所有列，都已经包含在某个索引里，于是存储引擎可以直接从二级索引叶子节点返回结果，完全不用回表。EXPLAIN里表现为Extra: Using index。

举个例子：

-- 已有索引 idx_user_time (user_id, create_time) SELECT user_id, create_time FROM orders WHERE user_id = 10086 ORDER BY create_time DESC;

查询列user_id和create_time都在这棵索引里，MySQL直接从索引拿数据，省掉回表。生产环境里，我会把"高频查询用到的列"尽可能塞进合适的联合索引，用空间换速度。但这里有个度：索引不是越大越好，后面讲冗余索引时会算这笔账。

3.3 索引下推：InnoDB里被低估的优化器帮手

MySQL 5.6引入了索引条件下推（Index Condition Pushdown，ICP）。它的作用是在扫描二级索引的过程中，直接把WHERE条件里那些"不能用索引定位、但可以通过索引列值判断"的条件，交给存储引擎层提前过滤，减少回表次数。

举一个非常常见的例子，联合索引(name, age)，查询WHERE name = '张三' AND age > 20。由于name是等值，age是范围，理论上age这列在定位阶段起不了作用，但它仍然存在于索引中。没有ICP时，MySQL会把所有name='张三'的行都回表，拿到完整的行再过滤age；有ICP时，存储引擎在扫描索引时就顺手把age > 20过滤掉，只有满足条件的少部分行才回表。

EXPLAIN的Extra里出现Using index condition就是ICP生效。很多DBA看到这个会觉得"既然有ICP，是不是索引设计就无所谓了"——完全不是。ICP只能减少回表，不能减少索引扫描的范围。想要真正快，核心还是靠合理索引把扫描行数压下来，ICP只是锦上添花。

4. 优化器"不选"索引的真相：统计信息、区分度与成本估算

4.1 统计信息过期：索引明明在，优化器却当它不存在

MySQL优化器决定是否用索引，靠的是成本估算，而成本估算依赖表统计信息（如行数、索引区分度、页面数）。当数据发生大量增删改后，统计信息没能及时更新，优化器就可能做出错误判断：明明索引更优，它却选择了全表扫描。

我曾经排查过一个案例：某流水表一天内从500万行涨到2000万行，一条WHERE status = 2的查询，status上有索引，EXPLAIN却显示type: ALL。手动执行ANALYZE TABLE之后，优化器马上改用索引，查询时间从1.8秒降到80ms。

如果你发现"索引明明存在、前阵子还走索引、现在突然不走"，优先怀疑统计信息过期。日常工作流里，对高频变更的大表，可以排一个低峰期的定时任务定期做ANALYZE TABLE。注意ANALYZE TABLE是只读操作，不阻塞业务读写，比OPTIMIZE TABLE温和得多。

4.2 区分度不足：性别字段的索引为什么没人爱

索引的本质是"按值缩小范围"。如果一个列的取值就两三个（gender、status、is_deleted），等值条件命中表中半数以上的行，优化器一算账：走索引要来回查B+树、回表拿行，还不如直接全表扫描顺序读，反正都要读大半张表。

所以"这个列有索引"和"这个列值得建索引"是两回事。像status这种低区分度列，如果想优化查询，单列索引基本没用，正确做法是让它成为联合索引的前缀列，配合高区分度列（user_id、order_no）一起使用。比如(status, create_time)，用status过滤掉大部分业务无关数据，再按时间范围取需要的数据。

4.3 小表大索引：优化器觉得"不值得"

还有一种情况：表只有几百行，任何字段都建了索引。你说索引没用吗？它有用，但优化器算下来，全表扫描只需要读两三个数据页，为了用索引还得先读索引页、再逐个回表，一来一回开销反而更大。于是无论怎么磨，EXPLAIN都是ALL。

这种属于正常现象，不该为了EXPLAIN非走索引而去折腾SQL。反过来提醒一点：给大表建索引之前，先在真实数据量上验证，别拿几万行的测试库做"性能结论"。测试库走索引、生产库不走索引的悲剧，多半是统计信息或数据规模不一致导致的。

4.4 排序和分组场景下的filesort抉择

ORDER BY create_time DESC LIMIT 20，如果有(create_time)索引，MySQL可以利用索引自身的有序性直接从头扫20条，效率极高。但如果查询还有WHERE条件，且WHERE过滤和ORDER BY字段不在同一个索引上，优化器就得在"用索引过滤+临时排序"和"全表扫描直接排序"之间选。

典型坑点：你建了(user_id, create_time)联合索引，想着既能过滤又能排序，但SQL里WHERE user_id = 10086 ORDER BY amount DESC，排序字段变成了amount，联合索引帮不上排序，只能Using filesort。如果amount高区分度且排序场景极高频，可以考虑单独建(amount)索引；如果业务能接受，尽量让排序字段和过滤字段共处一个联合索引，顺序按"等值列 -> 排序列"排列。比如WHERE user_id = ? ORDER BY create_time，就建(user_id, create_time)，正反排序都能用上。

5. 索引越多越稳？冗余索引和写入放大是这样拖垮业务库的

5.1 冗余索引的三个典型形态

很多团队加索引只看查询，不看现有索引结构，结果索引越堆越多，性能没升反降。冗余索引常见三种：

• 完全冗余：(a, b, c)和(a, b)，后者的所有列都能被前者覆盖，纯粹浪费空间。
• 部分冗余：(a, b, c)和(a)，(a)可以被前者前缀覆盖，除非(a)很短且单独查询频率极高，否则也是冗余。
• 前缀冗余：(a, b)和(a, c)不是完全冗余，但存在共同前缀，要小心评估a列的宽度，a越长浪费越明显。

我见过一张表上有近20个索引，光索引文件占的空间都快赶上数据文件了。这样的表，INSERT一条记录要维护20棵B+树，慢是必然的。

5.2 写入放大：被忽视的隐性成本

写入放大的逻辑很直白：表上每多一个索引，INSERT时就要多写一棵B+树。UPDATE如果改了索引列，还要额外维护对应索引。DELETE就更不用说了。对读多写少的报表表，索引多几个问题不大；对订单、流水、日志这类写入密集的表，每多一个索引，TPS都可能往下掉一截。

我在压测里实际对比过：同样一张10万行的表，从5个索引增加到7个索引，纯写入QPS下降约12%。这个比例在高并发场景会被明显放大。所以加索引前必须问一句：这个索引能帮我消灭几次全表扫描？如果收益不明确，宁可不加。

5.3 用sys库和工具发现冗余索引

MySQL 5.7+带了一个sys库，直接查视图就能发现冗余索引：

SELECT * FROM sys.schema_redundant_indexes;

这个视图会输出冗余索引以及它对应的"可替代索引"，非常直观。另外也可以用开源工具做全实例扫描，比如基于Percona Toolkit的重复索引检查脚本。接入规范建议是：每个季度对核心业务库跑一次冗余索引检查，把"重复度100%"的索引列出来，走评审、低峰期删除。

6. 从Explain到上线灰度：一套完整的索引治理流程

6.1 Explain实战解读：把type、key、rows、Extra串起来看

排查索引问题，EXPLAIN永远是第一站。看一张简化版的执行计划：

EXPLAIN SELECT user_id, create_time FROM orders WHERE user_id = 10086 AND create_time >= '2024-05-01' ORDER BY create_time DESC\G

几个关键字段这样对应着看：

注意key_len很多人不看。比如联合索引(status, create_time)，key_len只等于status的长度，说明只用到了第一列。这样可以快速确认"索引被部分使用"。

6.2 慢查询日志配置：把你从"猜"变成"看"

慢查询日志是发现索引问题最直接的入口。MySQL配置：

[mysqld] slow_query_log = ON slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 log_queries_not_using_indexes = ON min_examined_row_limit = 100

log_queries_not_using_indexes会把所有未使用索引的查询都记下来，刚开始可能会很吵，但初期阶段非常有价值，能帮你把全表扫描的SQL全部揪出来。min_examined_row_limit可以过滤掉扫描行数太少的查询，避免噪声。

配合MySQL的sys库，还可以这样按耗时排序直接捞：

SELECT query_text, rows_examined, rows_sent, query_time FROM sys.statement_analysis ORDER BY query_time DESC LIMIT 20;

拿到慢SQL后，按这个套路处理：先看EXPLAIN，判断是全表扫描、回表过多、排序临时表，还是索引列失效；然后针对性地改SQL或调索引；最后在压测环境用生产数据量复测，确认P95延迟和扫描行数都有实质改善。

6.3 索引的上线SOP：先加后删，别在高峰期动刀

索引治理也有发布节奏，我的标准操作顺序是这样的：

1. 先在低峰期给目标表ANALYZE TABLE，确认统计信息是最新状态，避免误判"需要加索引"。
2. 在测试环境导入生产脱敏数据，跑同样的业务SQL，确认rows从几十万降到几千。
3. 生产环境用在线DDL方式添加索引。MySQL 5.6+支持ALGORITHM=INPLACE，大部分加索引操作不阻塞读写；如果表特别大、写入压力又高，用开源无锁DDL工具更稳妥。
4. 加索引后观察至少一个业务周期，对比慢查询数量和写入延迟，确认没有负面影响。
5. 旧索引的删除不要和新增同时进行。先加新的、观察没问题，再过一周删旧的，给自己留出回退空间。

这几步看起来繁琐，但能避免"线上刚删掉一个索引，发现还有历史SQL依赖它"的惨案。我曾经就因为删索引前没做全量慢查询分析，导致一个定时报表跑了四十分钟才出数。

最后再说两个小技巧

索引优化做到最后，拼的不是某个SQL的技巧，而是对数据量、业务模式和成本的理解。我自己的习惯是：任何核心SQL写完之后，都强制自己跑一次EXPLAIN，并且定期用performance_schema的events_statements_summary_by_digest做一次全量SQL体检。这套动作坚持下来，比临时抱佛脚排查慢查询要香得多。

另外，如果你在排查一个"走了索引但还是慢"的SQL，先别急着加索引，优先确认rows预估和实际扫描行数是否一致、是否回表过量、有没有Using filesort。很多时候，问题不在"缺少索引"，而在"索引用错了列"。把联合索引的列顺序按照"等值在前、范围次之、排序列靠前"的原则重新设计一遍，往往比堆新索引更治本。