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 10086MySQL把字符串转成数字索引不受影响但也没必要刻意写字符串。判断方法很简单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 -- 只用 ac 无法定位 WHERE b 2 AND c 3 -- 完全无法使用 WHERE a 1 AND b 2 -- 只用到 ab 用于过滤但不参与索引定位很多人背口诀最左前缀但没理解背后的排序逻辑于是写出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 PushdownICP。它的作用是在扫描二级索引的过程中直接把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几个关键字段这样对应着看字段含义常见值我的判断逻辑type访问方式constrefrangeindexALLALL必须警惕key实际用的索引NULL代表没走索引结合possible_keys看有没有有索引不用的情况key_len用到的索引字节数数字越大说明联合索引中用到的列越多判断是否只用了前缀列rows预估扫描行数越小越好如果rows基本等于表行数说明快全表扫了Extra附加信息Using index、Using index condition、Using filesort、Using temporaryUsing filesort/Using temporary代表还有优化空间注意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 100log_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先加后删别在高峰期动刀索引治理也有发布节奏我的标准操作顺序是这样的先在低峰期给目标表ANALYZE TABLE确认统计信息是最新状态避免误判需要加索引。在测试环境导入生产脱敏数据跑同样的业务SQL确认rows从几十万降到几千。生产环境用在线DDL方式添加索引。MySQL 5.6支持ALGORITHMINPLACE大部分加索引操作不阻塞读写如果表特别大、写入压力又高用开源无锁DDL工具更稳妥。加索引后观察至少一个业务周期对比慢查询数量和写入延迟确认没有负面影响。旧索引的删除不要和新增同时进行。先加新的、观察没问题再过一周删旧的给自己留出回退空间。这几步看起来繁琐但能避免线上刚删掉一个索引发现还有历史SQL依赖它的惨案。我曾经就因为删索引前没做全量慢查询分析导致一个定时报表跑了四十分钟才出数。最后再说两个小技巧索引优化做到最后拼的不是某个SQL的技巧而是对数据量、业务模式和成本的理解。我自己的习惯是任何核心SQL写完之后都强制自己跑一次EXPLAIN并且定期用performance_schema的events_statements_summary_by_digest做一次全量SQL体检。这套动作坚持下来比临时抱佛脚排查慢查询要香得多。另外如果你在排查一个走了索引但还是慢的SQL先别急着加索引优先确认rows预估和实际扫描行数是否一致、是否回表过量、有没有Using filesort。很多时候问题不在缺少索引而在索引用错了列。把联合索引的列顺序按照等值在前、范围次之、排序列靠前的原则重新设计一遍往往比堆新索引更治本。