SQL 索引失效排查：执行计划变红，不一定该加索引

SQL 索引失效排查执行计划变红不一定该加索引一、索引失效的误解先别急着建索引大家好我是朱大喜。数据团队里最常听到的一句话这个查询好慢加个索引吧。 然后索引加了一堆查询还是慢大家开始怀疑人生。索引失效是一个比建索引更重要的诊断能力。我今天就用一个真实的排查案例带你走一遍执行计划变红 → 定位根因 → 修复方案的完整链路。先摆一个容易犯错的场景订单表orders有 500 万行user_id和create_time都建了索引但下面这个查询就是慢得离谱-- 订单表有 user_id 和 create_time 的单列索引 -- 但查询依然很慢——为什么 SELECT order_id, user_id, amount, status FROM orders WHERE user_id 12345 AND create_time 2026-06-01 AND create_time 2026-07-01 ORDER BY amount DESC LIMIT 20;flowchart TD A[查询慢 → 先看 EXPLAIN] -- B{type 列是什么} B --|ALL / indexbr/全表/全索引扫描| C[⚠️ 索引未使用br/或无效] B --|range / refbr/走索引了| D{rows 估算值br/是否接近实际} C -- E[排查方向] E -- E1[函数/计算br/在索引列上] E -- E2[隐式类型转换br/字符串 vs 数字] E -- E3[联合索引br/顺序不对] E -- E4[优化器判断br/全表更快] D --|估算行数巨大| F[数据倾斜 / 统计信息过期] D --|估算合理| G[问题可能在排序br/filesort / 临时表] style C fill:#ffcdd2,stroke:#b71c1c style G fill:#fff3e0,stroke:#ef6c00为什么 EXPLAIN 是索引诊断的X 光而不是加索引的处方单EXPLAIN 告诉你的是MySQL 优化器怎么看你的查询不是答案。typeALL 确实说明全表扫描了但全表扫描不一定是错的——如果表只有 500 行数据全表扫描比回表查索引更快。EXPLAIN 的价值在于让你看到优化器的决策路径它选择了哪些索引估算要扫多少行有没有触发 filesort这些信息组合在一起才构成诊断。很多人看到 typeALL 就直接加索引跳过了诊断加了 10 个索引其中 5 个从来没被用过浪费存储和写入性能另外 5 个用上了但查询还是慢问题在排序或临时表不在索引。记住EXPLAIN 告诉你哪里可能出问题不是该加什么索引。二、六种经典索引失效场景及排查场景一索引列上做函数运算——最常见的坑-- ❌ 对索引列使用函数或运算索引失效 SELECT * FROM orders WHERE YEAR(create_time) 2026; SELECT * FROM orders WHERE amount * 1.1 100; SELECT * FROM orders WHERE LEFT(order_no, 4) 2026; -- ✅ 把函数移到值这边让索引列保持裸的状态 SELECT * FROM orders WHERE create_time 2026-01-01 AND create_time 2027-01-01; SELECT * FROM orders WHERE amount 100 / 1.1; -- 算在右边 -- 如何验证EXPLAIN 里的 key 列是否显示索引名 EXPLAIN SELECT * FROM orders WHERE YEAR(create_time) 2026; -- key: NULL ← 索引没被用 EXPLAIN SELECT * FROM orders WHERE create_time 2026-01-01 AND create_time 2027-01-01; -- key: idx_create_time ← 索引正常使用原理索引存储的是列的原始值。当你用YEAR(create_time)时MySQL 不知道这个函数的结果能对应哪些索引条目只能一行行计算——全表扫描。场景二隐式类型转换——静默杀手-- orders 表: order_no 是 VARCHAR(32)建了索引 -- 但查询时传入了数字 -- ❌ MySQL 会隐式把 order_no 从字符串转成数字再比较 -- 结果索引失效 SELECT * FROM orders WHERE order_no 202606151234; -- ✅ 传入字符串类型匹配走索引 SELECT * FROM orders WHERE order_no 202606151234; -- 怎么排查看 EXPLAIN 的 Extra 列 EXPLAIN SELECT * FROM orders WHERE order_no 202606151234; -- Extra: Using where ← 没有 Using index走了全表MySQL 的隐式类型转换规则是字符串 → 数字方向。当你传入数字时MySQL 把索引列的每个值都转成数字再比较索引自然失效。这是最隐蔽的索引杀手因为 SQL 不报错性能偷偷变差。场景三联合索引的最左前缀法则-- 建了联合索引: idx_user_time_status (user_id, create_time, status) -- 但查询条件跳过了前导列 -- ✅ 从最左列开始走索引 SELECT * FROM orders WHERE user_id 12345; SELECT * FROM orders WHERE user_id 12345 AND create_time 2026-06-01; SELECT * FROM orders WHERE user_id 12345 AND status 1; -- ❌ 跳过 user_id直接从 create_time 开始——索引失效 SELECT * FROM orders WHERE create_time 2026-06-01; -- ❌ 范围条件后的列也部分失效 -- create_time 用了范围查询(), 后面的 status 就不能走索引了 SELECT * FROM orders WHERE user_id 12345 AND create_time 2026-06-01 -- 范围条件之后索引断掉 AND status 1; -- 无法走索引只能做索引条件下推想看实际走索引的长度EXPLAIN SELECT * FROM orders WHERE user_id 12345 AND create_time 2026-06-01 AND status 1; -- key_len 只到了 create_time 就不再增加了 -- 说明 status 确实没有被用于索引查找场景四OR 条件——一个 OR 毁所有-- 两个条件各自有索引idx_user_id 和 idx_create_time -- 但如果用 OR 连接MySQL 可能选择放弃索引 -- ❌ OR 导致两个索引都无法独立使用 SELECT * FROM orders WHERE user_id 12345 OR create_time 2026-06-15 10:00:00; -- ✅ 改用 UNION ALL 分别走各自的索引 SELECT * FROM orders WHERE user_id 12345 UNION ALL SELECT * FROM orders WHERE create_time 2026-06-15 10:00:00; -- 对比 EXPLAIN 结果 -- OR 版本: typeALL (全表扫描) -- UNION 版本: typeref typeref (两次索引查找)场景五数据分布导致优化器选错索引-- 场景orders 表 90% 的数据 status1已完成 -- 查询 status1 的订单时优化器觉得走索引不如全表扫描 -- 优化器的判断逻辑 -- 走索引 → 先查索引再回表 → 90% 行要回表 → IO 开销大 -- 全表扫描 → 顺序读一次过去 → IO 可能更小 -- 但你的实际数据可能不是均匀分布的 -- 可以先更新统计信息让优化器重新评估 ANALYZE TABLE orders; -- 如果 ANALYZE 后优化器还是选全表扫描而且你的查询场景确定走索引更快 -- 可以使用 FORCE INDEX 提示谨慎使用 SELECT * FROM orders FORCE INDEX(idx_status) WHERE status 1 AND create_time 2026-06-01;场景六ORDER BY LIMIT 的排序陷阱-- 查询取金额最大的前 20 个订单 -- 明明 amount 有索引但 EXPLAIN 显示 Using filesort EXPLAIN SELECT order_id, amount FROM orders ORDER BY amount DESC LIMIT 20; -- Extra: Using filesort -- 为什么走索引了还要 filesort -- 因为这个查询需要按 amount 降序取前 20 条 -- 但如果用了其他索引比如 WHERE 用了 user_id 索引 -- ORDER BY 的 amount 就没法复用索引排序需要额外排序 -- ✅ 建一个覆盖索引同时满足 WHERE 和 ORDER BY CREATE INDEX idx_amount_order ON orders(amount, order_id); -- 或者如果只是要前 20不需要额外字段 SELECT order_id, amount FROM orders ORDER BY amount DESC LIMIT 20; -- 可以直接走 idx_amount_order不用 filesort为什么这六种场景加起来的占比超过 90%几乎所有索引失效的场景都可以归结为两个根本原因索引列被遮蔽函数运算、类型转换或索引结构无法匹配查询模式最左前缀违反、OR 条件、数据分布偏差。遮蔽类问题的本质是 MySQL 的 BTree 索引存储的是列的原始值——你对列做了函数包裹就是告诉 MySQL别找那个索引我自己算。而结构不匹配类问题的本质是索引的有序性和查询的无序性之间的矛盾——BTree 索引是顺序结构最左前缀法是这种顺序结构的必然要求。如果你跳过了最左列相当于跳过了一个有序字典的第一级目录不管后面怎么翻都找不到直接入口。理解这两个根因比背六种场景更重要遇到新场景也能自己推导出原因。三、排查工具箱EXPLAIN 分析快速上手指南-- 完整版 EXPLAIN看更多细节 EXPLAIN FORMATJSON SELECT o.order_id, o.amount, u.user_name FROM orders o JOIN users u ON o.user_id u.user_id WHERE o.create_time 2026-06-01 AND o.status 1 ORDER BY o.amount DESC LIMIT 20;重点关注 JSON 输出里的这几个字段JSON 字段含义危险信号query_cost优化器估算的查询成本 10000 需要关注access_type访问方式 (range/ref/ALL)ALL 全表扫描rows_examined_per_scan预估扫描行数 100万 考虑加索引using_filesort是否用了额外排序true 排序没有用上索引using_temporary是否用了临时表true GROUP BY/ORDER BY 无法用索引# Python 封装自动检查 EXPLAIN 中的危险信号 import pymysql import json def analyze_query_safety(connection, sql): 自动分析 SQL 的执行计划安全性 with connection.cursor() as cursor: cursor.execute(fEXPLAIN FORMATJSON {sql}) plan json.loads(cursor.fetchone()[0]) warnings [] cost plan[query_block].get(cost_info, {}).get(query_cost, 0) if cost 10000: warnings.append(f⚠️ 查询成本过高: {cost:.0f}) # 递归检查所有表的访问方式 def check_table(table): access table.get(access_type, ) if access ALL: warnings.append( f⚠️ 表 {table.get(table_name, ?)} 全表扫描 (rows: {table.get(rows_examined_per_scan, ?)}) ) if using_filesort in str(table): warnings.append(f⚠️ 使用文件排序 (filesort)) if using_temporary_table in str(table): warnings.append(f⚠️ 使用临时表) if table in plan[query_block]: check_table(plan[query_block][table]) return { safe: len(warnings) 0, cost: cost, warnings: warnings }为什么 EXPLAIN FORMATJSON 比普通 EXPLAIN 关键普通 EXPLAIN 输出是一张有 12 列的表query_cost这个最重要的数字——优化器认为查一次要花多少代价——不在表里在 JSON 里。而且 JSON 格式能递归展示嵌套查询的执行计划——如果你有一个子查询 两个 JOINJSON 会按执行层级逐层展开每个子查询的执行计划独立显示。普通 EXPLAIN 把多层查询的执行计划平铺在一张表里你根本分不清哪个步骤属于哪个子查询。另一个关键区别是used_key_parts它告诉你联合索引中 MySQL 实际使用了哪几列。如果key_len对不上你预期的索引列数JDON 的used_key_parts能精确告诉你MySQL 只用了索引的前两列第三列没用到——这是普通 EXPLAIN 做不到的。四、索引设计决策树graph TD A[查询慢 → EXPLAIN 分析] -- B{type ALLbr/全表扫描} B --|是| C{WHERE 条件br/是什么} B --|否| D{Extra 有br/filesort/temporary} C --|单列等值| E[建单列索引br/ref 访问] C --|多列等值| F[建联合索引br/最左前缀原则] C --|范围查询| G[范围列放联合索引最后] C --|OR 条件| H[拆分 UNION ALLbr/或建覆盖索引] D --|filesort| I[让 ORDER BY 列br/进索引] D --|temporary| J[让 GROUP BY 列br/进索引] D --|都没有| K[查询可能本身没问题br/检查数据量/硬件] style A fill:#e3f2fd,stroke:#1565c0 style B fill:#fff3e0,stroke:#ef6c00 style C fill:#fff3e0,stroke:#ef6c00 style K fill:#c8e6c9,stroke:#2e7d32为什么决策树的起点是typeALL而不是查询慢这个决策树隐含了一个关键判断typeALL全表扫描和 typeref/range走索引但还有问题是完全不同的两个诊断分支它们的根因几乎不重叠。全表扫描的问题在索引没有被激活——要么没建索引要么建了但被函数/转换/违反最左前缀屏蔽了。走了索引但还是慢的问题在索引不够好——排序没覆盖到、数据分布让优化器误判、临时表拖累。很多初级 DBA 的误区是把两类问题混在一起诊断看到 filesort 就去排查是不是函数运算导致了索引失效——这两者根因完全不同。先看 type 列做一个一分为二的判断再选对应的排查分支是最高效的诊断路径。踩坑提醒建了联合索引不代表所有列都会生效——很多人以为CREATE INDEX idx_a_b_c ON t(a,b,c)之后WHERE a1 AND c2也能走到索引的 a 和 c 两列。实际上由于最左前缀法则c 列在 b 列缺失的情况下完全不参与索引查找只是通过索引条件下推做了过滤——效率差距可能是 10 倍。验证方法看 EXPLAIN 的 key_len如果 key_len 只覆盖到 a 的长度说明 c 没有被用到。不要在千万级数据表上在线加索引——CREATE INDEX在高并发生产环境会锁表。MySQL 5.6 的 Online DDL 在线加索引虽然不阻塞读写但会消耗大量 IO 和 CPU可能把正常业务查询拖慢 50% 以上。正确做法使用pt-online-schema-change工具创建影子表并逐批复制数据切换瞬间只有毫秒级的锁。如果必须手动加先SET SESSION lock_wait_timeout1再跑。FORCE INDEX 是临时方案不是长久之计——用FORCE INDEX确实能立刻让查询变快但它的危险在于你的 FORCE INDEX 是根据今天的数据分布指定的三个月后数据分布变了原来好用的索引可能变成了坏选择但 FORCE INDEX 还在强制使用它查询又变慢了——而且这次的慢是被人为束缚住的慢EXPLAIN 根本看不出原因。每一条 FORCE INDEX 都应该在代码里加一个 TODO 注释预期什么时候移除、移除条件是什么。五、总结索引失效排查的核心心法是不是所有慢查询的解决方案都是加索引。很多时候问题出在你怎么用索引而不是有没有索引。六个高频场景按频率排序索引列被函数包裹占 30%——把函数移到值这边。隐式类型转换占 20%——保持类型一致。联合索引顺序不对占 20%——记住最左前缀法则。数据分布导致优化器放弃索引占 15%——ANALYZE TABLE 更新统计信息。OR 条件占 10%——改成 UNION ALL。ORDER BY 额外排序占 5%——让排序列进索引。下次同事再说加个索引吧先回一句先看 EXPLAIN 的 type 列是不是 ALL。如果 type 已经是 ref/range问题大概率不在索引上。—— 朱大喜EXPLAIN 里 typeALL 才值得建索引别瞎建。