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 / index<br/>全表/全索引扫描"| C["⚠️ 索引未使用<br/>或无效"] B -->|"range / ref<br/>走索引了"| 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 优化器怎么看你的查询，不是"答案"。type=ALL 确实说明全表扫描了，但全表扫描不一定是错的——如果表只有 500 行数据，全表扫描比回表查索引更快。EXPLAIN 的价值在于让你看到优化器的决策路径：它选择了哪些索引？估算要扫多少行？有没有触发 filesort？这些信息组合在一起才构成诊断。很多人看到 type=ALL 就直接加索引，跳过了诊断，加了 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 版本: type=ALL (全表扫描) -- UNION 版本: type=ref + type=ref (两次索引查找)

场景五：数据分布导致优化器"选错索引"

-- 场景：orders 表 90% 的数据 status=1（已完成） -- 查询 status=1 的订单时，优化器觉得走索引不如全表扫描 -- 优化器的判断逻辑： -- 走索引 → 先查索引再回表 → 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 的 B+Tree 索引存储的是列的原始值——你对列做了函数包裹，就是告诉 MySQL"别找那个索引，我自己算"。而"结构不匹配"类问题的本质是索引的有序性和查询的无序性之间的矛盾——B+Tree 索引是顺序结构，最左前缀法是这种顺序结构的必然要求。如果你跳过了最左列，相当于跳过了一个有序字典的第一级目录，不管后面怎么翻，都找不到直接入口。理解这两个根因比背六种场景更重要：遇到新场景也能自己推导出原因。

三、排查工具箱：EXPLAIN 分析快速上手指南

-- === 完整版 EXPLAIN：看更多细节 === EXPLAIN FORMAT=JSON 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 输出里的这几个字段：

# Python 封装：自动检查 EXPLAIN 中的危险信号 import pymysql import json def analyze_query_safety(connection, sql): """自动分析 SQL 的执行计划安全性""" with connection.cursor() as cursor: cursor.execute(f"EXPLAIN FORMAT=JSON {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 FORMAT=JSON 比普通 EXPLAIN 关键？普通 EXPLAIN 输出是一张有 12 列的表，query_cost这个最重要的数字——"优化器认为查一次要花多少代价"——不在表里，在 JSON 里。而且 JSON 格式能递归展示嵌套查询的执行计划——如果你有一个子查询 + 两个 JOIN，JSON 会按执行层级逐层展开，每个子查询的执行计划独立显示。普通 EXPLAIN 把多层查询的执行计划平铺在一张表里，你根本分不清哪个步骤属于哪个子查询。另一个关键区别是used_key_parts：它告诉你联合索引中 MySQL 实际使用了哪几列。如果key_len对不上你预期的索引列数，JDON 的used_key_parts能精确告诉你"MySQL 只用了索引的前两列，第三列没用到"——这是普通 EXPLAIN 做不到的。

四、索引设计决策树

graph TD A["查询慢 → EXPLAIN 分析"] --> B{"type = ALL<br/>全表扫描？"} B -->|"是"| C{"WHERE 条件<br/>是什么？"} B -->|"否"| D{"Extra 有<br/>filesort/temporary？"} C -->|"单列等值"| E["建单列索引<br/>ref 访问"] C -->|"多列等值"| F["建联合索引<br/>最左前缀原则"] C -->|"范围查询"| G["范围列放联合索引最后"] C -->|"OR 条件"| H["拆分 UNION ALL<br/>或建覆盖索引"] 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

为什么决策树的起点是"type=ALL"而不是"查询慢"？这个决策树隐含了一个关键判断：type=ALL（全表扫描）和 type=ref/range（走索引但还有问题）是完全不同的两个诊断分支，它们的根因几乎不重叠。全表扫描的问题在"索引没有被激活"——要么没建索引，要么建了但被函数/转换/违反最左前缀屏蔽了。走了索引但还是慢的问题在"索引不够好"——排序没覆盖到、数据分布让优化器误判、临时表拖累。很多初级 DBA 的误区是把两类问题混在一起诊断：看到 filesort 就去排查是不是函数运算导致了索引失效——这两者根因完全不同。先看 type 列做一个一分为二的判断，再选对应的排查分支，是最高效的诊断路径。

踩坑提醒

1. 建了联合索引不代表所有列都会生效——很多人以为CREATE INDEX idx_a_b_c ON t(a,b,c)之后，WHERE a=1 AND c=2也能走到索引的 a 和 c 两列。实际上由于最左前缀法则，c 列在 b 列缺失的情况下完全不参与索引查找，只是通过索引条件下推做了过滤——效率差距可能是 10 倍。验证方法：看 EXPLAIN 的 key_len，如果 key_len 只覆盖到 a 的长度，说明 c 没有被用到。

2. 不要在千万级数据表上在线加索引——CREATE INDEX在高并发生产环境会锁表。MySQL 5.6+ 的 Online DDL 在线加索引虽然不阻塞读写，但会消耗大量 IO 和 CPU，可能把正常业务查询拖慢 50% 以上。正确做法：使用pt-online-schema-change工具创建影子表并逐批复制数据，切换瞬间只有毫秒级的锁。如果必须手动加，先SET SESSION lock_wait_timeout=1再跑。

3. FORCE INDEX 是临时方案，不是长久之计——用FORCE INDEX确实能立刻让查询变快，但它的危险在于：你的 FORCE INDEX 是根据"今天的数据分布"指定的，三个月后数据分布变了，原来好用的索引可能变成了坏选择，但 FORCE INDEX 还在强制使用它，查询又变慢了——而且这次的慢是"被人为束缚住"的慢，EXPLAIN 根本看不出原因。每一条 FORCE INDEX 都应该在代码里加一个 TODO 注释：预期什么时候移除、移除条件是什么。

五、总结

索引失效排查的核心心法是：不是所有慢查询的解决方案都是"加索引"。很多时候问题出在"你怎么用索引"，而不是"有没有索引"。

六个高频场景按频率排序：

1. 索引列被函数包裹（占 30%）——把函数移到值这边。
2. 隐式类型转换（占 20%）——保持类型一致。
3. 联合索引顺序不对（占 20%）——记住最左前缀法则。
4. 数据分布导致优化器放弃索引（占 15%）——ANALYZE TABLE 更新统计信息。
5. OR 条件（占 10%）——改成 UNION ALL。
6. ORDER BY 额外排序（占 5%）——让排序列进索引。

下次同事再说"加个索引吧"，先回一句："先看 EXPLAIN 的 type 列是不是 ALL"。如果 type 已经是 ref/range，问题大概率不在索引上。

—— 朱大喜，EXPLAIN 里 type=ALL 才值得建索引，别瞎建。