在生产环境中，我们偶尔会遇到这样一种“诡异”的现象：一条UPDATE语句的WHERE条件明明包含了索引字段，EXPLAIN也显示会走索引，但实际执行时却走了全表扫描，导致大量行被锁定，最终引发锁等待超时。本文将从一个真实案例出发，深入分析这一问题的成因、排查思路和解决方案。
一、问题现象
1.1 慢日志表现
从慢日志中发现了以下异常UPDATE语句：
UPDATE partner_common_repay_plan
SET org_id='XXX', product_id='XXX', outer_loan_id='XXX', cur_term='6', effective_date='2025/06/09 00:00:00.000',principal='5.83', principal_repay='5.83', int_total='0.77', int_repay='0.77', pnl_int_total='0.0', pnl_int_repay='0.0', term_status='5', clear_date='2025/06/09 00:00:00.000'
WHERE org_id='XXX' AND product_id='XXX' AND outer_loan_id='XXX' AND cur_term='6';
慢日志关键指标：
| 指标 | 数值 |
|---|---|
| Query_time | 1573.29秒（超26分钟） |
| Lock_time | 1421.84秒（超23分钟） |
| Rows_examined | 39,415,873行（近4000万） |
1.2 InnoDB状态分析
从InnoDB状态日志中看到，对应的事务持有大量行锁：
mysql tables in use 1, locked 1
390158 lock struct(s), heap size 37282000, 25610952 row lock(s), undo log entries 307
关键发现：该UPDATE语句锁定了超过2500万行数据，明显是走了全表扫描。
1.3 最终结果
errno=1205, error: lock wait timeout exceeded; try restarting transaction
事务因锁等待超时而失败回滚，未计入binlog（意味着数据未被修改）。
二、矛盾点：EXPLAIN显示走索引
2.1 表结构与索引
表结构简化和索引信息：
CREATE TABLE `partner_common_repay_plan` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,`org_id` varchar(10) NOT NULL,`product_id` varchar(10) NOT NULL,`outer_loan_id` varchar(128) NOT NULL,`cur_term` smallint(6) DEFAULT NULL,`effective_date` date NOT NULL,-- 其他字段省略PRIMARY KEY (`id`),KEY `idx_outer_loan_id` (`outer_loan_id`, `org_id`, `product_id`) USING BTREE,KEY `idx_effective_date` (`effective_date`, `org_id`, `product_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.2 EXPLAIN结果
EXPLAIN UPDATE partner_common_repay_plan
SET ...
WHERE org_id='XXX' AND product_id='XXX' AND outer_loan_id='XXX' AND cur_term='6';
结果显示：
| type | key | rows | Extra |
|---|---|---|---|
| range | idx_outer_loan_id | 4 | Using where |
按理说应该走索引，为什么实际执行时却扫描了全表？
三、排查过程
3.1 检查索引文件是否损坏
CHECK TABLE partner_common_repay_plan;
执行结果：status = OK，表状态正常。
3.2 检查错误日志
查看MySQL错误日志，未发现任何索引相关的报错信息，索引功能正常。
3.3 分析同一时间段的其他UPDATE
从binlog和慢日志分析，发现同一时间段内存在两种UPDATE行为：
| 时间点 | UPDATE类型 | 执行结果 |
|---|---|---|
| 13:38:43 | 正常UPDATE（走索引） | 成功，5000条共1.8秒 |
| 13:38:43.404 | 问题UPDATE（全表扫描） | 锁超时失败 |
| 约5分钟后 | 后续UPDATE | 恢复正常 |
关键发现：正常UPDATE的WHERE条件字段与问题UPDATE完全一致，只是条件值不同。两种类型的UPDATE在时间上存在重叠。
3.4 死锁分析
从InnoDB状态日志中发现了死锁信息：
*** (1) TRANSACTION:
UPDATE `partner_common_repay_plan`
SET `cur_date` = '2025/07/08 00:00:00.000'
WHERE (`product_id` = 'XXX') AND (`outer_loan_id` = 'XXX') AND (`cur_term` = 1)*** (2) TRANSACTION:
update partner_common_repay_plan set org_id='XXX' ...
where org_id='XXX' and product_id='XXX'
and outer_loan_id='XXX' and cur_term='6'
关键发现：两种类型的UPDATE，走二级索引回表的顺序与全表扫描的顺序出现交叉，导致死锁。
四、根因分析
4.1 为什么会走全表扫描？
在MySQL中，优化器选择执行计划时，会基于成本估算来决定。以下情况可能导致优化器放弃使用索引：
| 可能原因 | 说明 | 本案例符合度 |
|---|---|---|
| 索引统计信息过期 | outer_loan_id字段分布不均，统计信息认为该值匹配大量行 |
⭐⭐⭐ |
| 死锁导致的执行计划退化 | 死锁发生后，优化器可能选择保守的全表扫描策略 | ⭐⭐⭐⭐ |
| 索引下推失效 | 某些条件下ICP（Index Condition Pushdown）无法使用 | ⭐⭐ |
| 事务隔离级别影响 | REPEATABLE-READ级别下，MVCC可能导致索引选择变化 | ⭐⭐ |
4.2 本案例的核心原因
结合死锁信息和时间线分析，最可能的原因是：
- 并发冲突触发：正常UPDATE（走二级索引）与问题UPDATE（全表扫描）的加锁顺序产生交叉
- 死锁发生后：问题UPDATE事务检测到死锁/长时间等待，优化器重新评估执行计划
- 执行计划退化：由于锁竞争严重，优化器认为“全表扫描+全表锁”比“索引扫描+大量回表”成本更低
- 雪崩效应：全表扫描锁定了全部近4000万行，导致后续所有UPDATE都被阻塞
4.3 执行计划选择流程图
flowchart TDA[UPDATE请求到达] --> B{优化器评估}B -->|正常情况| C[选择二级索引 idx_outer_loan_id]B -->|统计信息异常| D[全表扫描]B -->|死锁/锁竞争| DC --> E[锁住少量行 4-12行]D --> F[锁住全表 4000万行]E --> G[快速执行成功]F --> H[锁等待超时失败]H --> I[事务回滚<br/>未计入binlog]
五、解决方案
5.1 紧急处理（线上故障时）
-- 1. 查看当前阻塞的会话
SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM information_schema.INNODB_LOCKS;-- 2. 找出执行全表扫描的UPDATE对应的线程ID
-- 3. 终止该会话（谨慎操作）
KILL <thread_id>;-- 4. 临时调整锁等待超时时间
SET GLOBAL innodb_lock_wait_timeout = 5; -- 快速失败，避免长时间阻塞
5.2 根本解决措施
| 措施 | 具体操作 | 优先级 |
|---|---|---|
| 更新统计信息 | ANALYZE TABLE partner_common_repay_plan; |
⭐⭐⭐ |
| 优化索引 | 创建覆盖索引，避免回表 | ⭐⭐⭐ |
| 限制扫描行数 | 使用LIMIT分批更新 |
⭐⭐ |
| 强制使用索引 | UPDATE ... FORCE INDEX(idx_xxx) |
⭐⭐ |
| 优化应用逻辑 | 避免大批量UPDATE与高频UPDATE并发 | ⭐⭐⭐ |
5.2.1 创建覆盖索引
-- 创建覆盖所有WHERE条件和SET字段的索引
ALTER TABLE partner_common_repay_plan
ADD INDEX idx_covering (`org_id`, `product_id`, `outer_loan_id`, `cur_term`);
5.2.2 分批更新方案
-- 分批更新，每次处理1000行
UPDATE partner_common_repay_plan
SET ...
WHERE org_id='XXX' AND product_id='XXX' AND outer_loan_id='XXX' AND cur_term='6'
LIMIT 1000;
5.2.3 强制使用索引
-- 强制优化器使用指定索引
UPDATE partner_common_repay_plan
FORCE INDEX (idx_outer_loan_id)
SET ...
WHERE org_id='XXX' AND product_id='XXX' AND outer_loan_id='XXX' AND cur_term='6';
5.3 预防措施
| 措施 | 说明 |
|---|---|
| 定期更新统计信息 | 每周执行ANALYZE TABLE，避免统计信息过期 |
| 监控慢日志 | 设置阈值，及时发现扫描行数异常的SQL |
| 死锁监控告警 | 配置死锁告警，及时介入处理 |
| 代码Review | 检查是否存在大量并发UPDATE同表的场景 |
| 索引使用率监控 | 定期检查索引使用情况，及时优化未使用的索引 |
六、知识点总结
6.1 EXPLAIN显示走索引，实际不走的原因
| 原因 | 说明 |
|---|---|
| 统计信息过期 | 索引统计信息与实际数据分布偏差过大 |
| 锁竞争 | 大量锁等待导致优化器选择保守策略 |
| 资源限制 | 内存不足，无法加载索引 |
| 死锁回滚 | 死锁后执行计划退化 |
| 隐式类型转换 | WHERE条件字段类型不匹配，索引失效 |
6.2 排查命令速查
-- 查看表状态和统计信息
SHOW TABLE STATUS LIKE 'partner_common_repay_plan';-- 查看索引统计信息
SELECT * FROM mysql.innodb_index_stats
WHERE table_name = 'partner_common_repay_plan';-- 查看当前事务
SELECT * FROM information_schema.INNODB_TRX\G-- 查看锁信息
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;-- 查看死锁信息
SHOW ENGINE INNODB STATUS\G-- 分析表
ANALYZE TABLE partner_common_repay_plan;-- 强制更新统计信息
OPTIMIZE TABLE partner_common_repay_plan;
七、总结与建议
7.1 核心结论
| 问题 | 结论 |
|---|---|
| 为什么会走全表扫描？ | 死锁/锁竞争导致优化器执行计划退化 |
| 为什么EXPLAIN显示走索引？ | EXPLAIN在执行时没有锁竞争，使用的是正常执行计划 |
| 如何快速恢复？ | KILL阻塞会话，调整锁等待超时 |
| 如何彻底解决？ | 更新统计信息 + 创建覆盖索引 + 优化应用并发逻辑 |
7.2 经验教训
- 不要盲目信任EXPLAIN：EXPLAIN的结果是“理想状态”下的执行计划，不代表实际运行时的行为
- 并发场景下的执行计划可能不同：锁竞争、死锁等并发问题会影响优化器决策
- 全表扫描的代价极大：一次全表扫描可能阻塞整个表的后续操作
- 监控扫描行数比执行时间更敏感：扫描行数突然激增是异常的先兆
- 分批处理是王道：大批量操作拆分为小批量，避免长时间锁表
📌 互动环节：你在生产环境中遇到过类似“有索引却走全表扫描”的问题吗？欢迎在评论区分享你的排查经验！
附录：相关参数说明
| 参数 | 默认值 | 说明 |
|---|---|---|
innodb_lock_wait_timeout |
50 | InnoDB事务等待行锁的超时时间（秒） |
optimizer_search_depth |
62 | 优化器搜索深度 |
optimizer_prune_level |
1 | 优化器剪枝级别 |