MySQL：SQL没命中索引时InnoDB直接退化为表级锁，高并发下瞬间雪崩，怎么提前发现这种“无索引更新”？

目录

一、引言：一次“无索引更新”如何演化为雪崩

在生产事故复盘中，有一类故障的触发条件极其朴素：某条 UPDATE 语句的 WHERE 条件没有可用索引。单看这条语句，执行计划显示为全表扫描，耗时可能只有几十毫秒，测试环境毫无异常；但一旦并发上来，写入吞吐会断崖式下跌，随后是锁等待堆积、连接池耗尽、上游超时，最终表现为“数据库没挂，业务先挂”。这类故障的隐蔽性在于：它不报错，只是变慢，而变慢会被重试放大。

MySQL 官方文档在 InnoDB 锁章节中明确说明：UPDATE ... WHERE 语句通常会对扫描到的每一行设置 next-key 锁（间隙锁与记录锁的组合），而“扫描到的行数”取决于执行计划选择的访问路径（MySQL Reference Manual, InnoDB Locking）。这意味着锁的粒度并不由语句语义决定，而由优化器的访问路径决定。当访问路径退化为全表扫描时，锁的范围也随之扩散到整张表。

本文的主线可以概括为一句话：锁范围 = 扫描范围。围绕这条主线，本文要回答三个工程问题：第一，退化在存储引擎层面究竟如何发生；第二，雪崩的因果链条如何被量化观测；第三，如何在故障发生前，用数据库自身的元数据与运行时视图把“无索引更新”识别出来。笔者认为，把这条主线当作设计监控与治理流程的第一性原理，比记住若干条“最佳实践”更有迁移价值。

二、背景与问题定义：从行锁到“事实上的表锁”

2.1 概念界定：什么是“退化为表级锁”

严格来说，InnoDB 并没有“表级锁”这一显式锁类型用于普通 DML（LOCK TABLES 除外）。所谓“退化为表级锁”，是一种效果层面的描述：当一条 DML 的访问路径为全表扫描时，InnoDB 会对扫描经过的每一个索引记录加锁，在可重复读（REPEATABLE READ）隔离级别下还会加间隙锁，其覆盖范围等价于整张表的索引区间。对并发的其他写入而言，可用的锁空间被压缩到接近零，表现与表锁无异。

本文评述：把“退化”理解为“锁覆盖范围扩张”而非“锁类型切换”，是理解后续所有检测手段的前提。因为锁类型没有变，变的是加锁的记录集合，所以检测的抓手必然是“扫描行数”与“锁等待对象”，而不是去查某张系统表里有没有“表锁”标记。

2.2 问题边界：哪些场景会触发锁范围扩张

并非所有“没走索引”的更新都会造成同等危害。锁范围扩张的严重程度取决于三个变量：扫描行数、隔离级别、并发写入密度。下表给出常见场景的定性对照，用于工程上快速判断风险等级。

表中最后一行值得单独强调：索引失效比“没有索引”更危险，因为开发者往往“以为有索引”。典型诱因包括列类型与参数类型不一致导致的隐式转换、在索引列上使用函数、前导通配的 LIKE、以及字符集排序规则不一致。这些场景下执行计划可能显示 type=ALL，但由于表上确实存在索引，人工 review 时极易漏过。

三、原理拆解：锁范围为什么等于扫描范围

3.1 加锁发生在“访问路径”之上

InnoDB 的行级锁是加在索引记录上的，这一点在官方文档与源码注释中均有体现：锁对象是索引项（index record），而非物理行。因此，一条 DML 实际加锁的集合，等于执行器通过存储引擎接口读取到的索引记录集合。当优化器选择全表扫描时，执行器沿聚簇索引从头到尾读取，每读到一条满足（或不满足）WHERE 条件的记录，都可能触发加锁动作。本文评述：这解释了为什么“加索引”能治本——它改变的不是 SQL 语义，而是访问路径，从而直接收缩了加锁集合。

需要区分两个容易混淆的概念：扫描行数与加锁行数。在 READ COMMITTED 隔离级别下，MySQL 会对不匹配的行释放锁（半一致性读的语义优化），加锁集合更接近“实际匹配行”；而在 REPEATABLE READ 下，为保证可重复读与防止幻读，间隙锁会保留，加锁集合更接近“扫描区间”。这构成了隔离级别与锁冲突之间的直接因果链。

3.2 一个可复现的最小实验

下面给出一个可在测试库复现的最小实验，用于观察锁范围随访问路径变化的过程。实验使用两张结构相同的表，一张在过滤列上有索引，一张没有。

-- 会话 A：构造数据
CREATE TABLE t_idx (id INT PRIMARY KEY, k INT, v INT, KEY idx_k(k)) ENGINE=InnoDB;
CREATE TABLE t_noi (id INT PRIMARY KEY, k INT, v INT) ENGINE=InnoDB;
INSERT INTO t_idx SELECT n, n%100, 0 FROM (SELECT @r:=@r+1 n FROM information_schema.columns,(SELECT @r:=0) x LIMIT 10000) s;
INSERT INTO t_noi SELECT * FROM t_idx;

-- 会话 A：无索引更新，事务不提交
BEGIN;
UPDATE t_noi SET v = 1 WHERE k = 42;

-- 会话 B：更新另一条 k 不同的记录，观察是否被阻塞
BEGIN;
UPDATE t_noi SET v = 2 WHERE k = 7;   -- 预期被阻塞
-- 查看锁等待
SELECT * FROM performance_schema.data_lock_waits;

在笔者的复现中（MySQL 8.0，REPEATABLE READ），会话 B 会被阻塞，data_lock_waits 中可见等待关系；把同样的语句换到 t_idx 上，会话 B 可立即完成。这一对照实验直接验证了“锁范围 = 扫描范围”的主线。需要说明的是，实验数据为本地构造的模拟数据，仅用于机制演示，不代表任何生产负载特征。

3.3 执行计划与锁范围的映射关系

把 EXPLAIN 输出中的 type 与 rows 两列组合起来，可以近似估计锁范围。经验上，type=ALL 且 rows 接近表行数时，锁范围即为全表；type=index（全索引扫描）同样危险，因为它扫描的是整棵二级索引。这一点常被忽略：“用到了索引”不等于“锁范围小”，全索引扫描的加锁集合同样可能覆盖全表记录。

四、雪崩链路：从锁等待到连接池耗尽的因果推演

4.1 五个阶段的放大效应

单条无索引更新本身只是“慢”，雪崩来自正反馈放大。把链路拆成五个阶段，可以更清楚地定位干预点：

1. 锁范围扩张：全表扫描导致大量记录与间隙被加锁，并发写入的可用锁空间趋近于零。
2. 锁等待堆积：后续事务进入 innodb_lock_wait_timeout（默认 50 秒）等待队列，活跃事务数上升。
3. 连接占用：每个等待事务占用一个连接，连接池可用连接数下降。
4. 上游超时与重试：应用侧超时后重试，重试请求再次进入等待队列，形成正反馈。
5. 级联失败：连接池耗尽后，读请求也无法获取连接，故障从写扩散到读，表现为整体不可用。

这条链路的关键在于第 4 阶段：重试是放大器。如果应用层对超时做无退避的立即重试，原本 1 倍的并发会被放大为 2 倍甚至更多，把“慢”变成“死”。笔者认为，治理无索引更新与治理重试策略应当同步推进，前者收缩锁范围，后者切断正反馈。

4.2 用指标刻画雪崩前夜

雪崩不是瞬间发生的，在锁等待堆积阶段会有明确的可观测信号。下表列出可用于预警的核心指标及其含义，数据来源为 MySQL 官方文档中 SHOW ENGINE INNODB STATUS 与 performance_schema 的公开定义。

需要提醒的是，上述指标中“扫描行数 / 返回行数”来自 events_statements_summary_by_digest 的聚合统计，属于运行时采样数据，存在统计误差，适合做趋势判断而非精确计量。本文评述：预警系统的价值在于“趋势”，把单点误差当作告警依据容易产生误报，工程上应设置持续窗口而非瞬时阈值。

五、主动发现：可落地的检测 SQL 与监控指标

5.1 第一层：从语句摘要中筛出高扫描比 DML

MySQL 8.0 的 performance_schema 会按语句摘要（digest）聚合执行统计，其中包含扫描行数与返回行数。对 UPDATE/DELETE 而言，扫描行数远大于返回/影响行数，是“无索引更新”的强信号。

SELECT
  DIGEST_TEXT,
  COUNT_STAR                                   AS exec_count,
  SUM_ROWS_EXAMINED                            AS rows_examined,
  SUM_ROWS_AFFECTED                            AS rows_affected,
  ROUND(SUM_ROWS_EXAMINED / GREATEST(SUM_ROWS_AFFECTED,1), 2) AS scan_ratio,
  ROUND(SUM_TIMER_WAIT / 1e12, 2)              AS total_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE 'UPDATE %' OR DIGEST_TEXT LIKE 'DELETE %'
ORDER BY scan_ratio DESC
LIMIT 20;

scan_ratio 显著大于 1 的 UPDATE 语句应进入人工复核清单。需要注意，该视图有容量上限，摘要可能被淘汰，因此建议配合定期落库做趋势留存。官方文档对 performance_schema 各表的字段含义有完整说明，可作为实现时的权威依据（MySQL Reference Manual, Performance Schema Statement Digests）。

5.2 第二层：从锁等待反查“肇事语句”

当锁等待已经发生时，data_lock_waits 与 data_locks 可以定位阻塞者与被阻塞者，再关联 innodb_trx 与 processlist 拿到具体 SQL。这是事后定位的标准路径，也可作为实时巡检脚本。

SELECT
  r.trx_id            AS waiting_trx,
  r.trx_mysql_thread_id AS waiting_thread,
  r.trx_query         AS waiting_query,
  b.trx_id            AS blocking_trx,
  b.trx_mysql_thread_id AS blocking_thread,
  b.trx_query         AS blocking_query
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID
JOIN information_schema.innodb_trx r ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID;

把 blocking_query 与第一层的 scan_ratio 清单做交集，可以快速锁定“既高扫描比、又在制造锁等待”的语句，这是性价比最高的排查路径。笔者认为，两层检测的组合比单一手段更可靠：第一层发现潜在风险，第二层确认实际危害。

5.3 第三层：静态扫描代码与慢日志

运行时检测有滞后性，静态扫描可以前移防线。可行做法包括：对 ORM 生成的 SQL 做规则匹配，识别缺少 WHERE 或 WHERE 列无索引的 UPDATE；开启慢查询日志并设置 log_queries_not_using_indexes（需注意该参数会放大日志量，生产环境应配合限流与采样）。此外，sys.schema_unused_indexes 与 sys.statements_with_full_table_scans 是官方 sys schema 提供的现成视图，可直接用于巡检。

六、治理闭环：从发现到修复的工程路径

6.1 四步操作路径

发现只是起点，治理需要可执行的步骤。结合前文的检测手段，可以形成如下闭环：

1. 建立基线：采集一周的 digest 统计，按 scan_ratio 排序，形成高风险语句清单。
2. 执行计划复核：对清单内语句逐条 EXPLAIN，确认 type 与 rows，区分“无索引”与“索引失效”。
3. 修复与验证：补索引或改写 SQL（消除隐式转换、函数包裹），在影子库验证执行计划变化与锁范围收缩。
4. 回归与固化：把检测 SQL 纳入定时巡检，把高风险模式纳入代码评审规则，防止回潮。

6.2 补索引的边界条件

补索引并非无成本。二级索引会增加写放大与存储开销，联合索引的列顺序需要匹配查询模式，长字符串列建索引还可能触及索引长度限制。此外，在线 DDL 虽然支持并发 DML，但在高负载下仍可能引起来回切换与短暂锁表。本文评述：治理的目标不是“消灭所有全表扫描”，而是让高频、高并发的写路径落在小范围索引上；低频的批量任务即使全表扫描，只要错峰执行，风险可控。

七、对比与局限：边界条件与常见误判

7.1 隔离级别带来的差异

在 READ COMMITTED 下，InnoDB 对不匹配行的锁会提前释放，间隙锁也基本不保留，因此同样一条无索引更新，其锁冲突程度显著低于 REPEATABLE READ。这意味着“退化”的严重性并非常量，而是隔离级别的函数。工程上如果业务允许，评估将部分写路径切换到 READ COMMITTED 是收缩锁范围的一种手段，但需要同步评估半一致性读对业务语义的影响，不能一概而论。

7.2 常见误判清单

实践中，以下判断容易出错，需要特别留意：其一，把“执行计划用了索引”等同于“锁范围小”，忽略了全索引扫描；其二，只看单条 SQL 耗时，忽略并发下的锁竞争放大；其三，把 rows 估算值当作精确值，而它来自统计信息采样，存在偏差；其四，忽略外键与触发器带来的隐式加锁。笔者认为，把这些边界条件写进团队的事故复盘模板，比单纯强调“要加索引”更能减少重复踩坑。

7.3 前沿演进

从社区与厂商的公开资料看，MySQL 8.0 在可观测性上持续增强，performance_schema 的锁视图与语句摘要为主动发现提供了基础能力；云厂商的数据库自治服务则在此基础上叠加了自动索引推荐与慢 SQL 归因。需要客观看待的是，自动推荐仍依赖统计信息与负载采样，对突发流量与业务语义变化存在滞后，不能替代人工复核。本文评述：工具的价值在于把“发现”从人工巡检变成持续信号，而“判断与决策”仍需要工程经验。

八、结论与展望

回到主线：锁范围 = 扫描范围。无索引更新之所以危险，不在于单条语句慢，而在于它把并发写入的锁空间压缩到接近零，再经由重试放大为雪崩。提前发现的关键，是把“扫描行数”与“锁等待”作为一等公民指标：用 digest 统计筛出高扫描比 DML，用锁等待视图反查肇事语句，用静态扫描前移防线，最后以补索引与 SQL 改写完成闭环。

展望未来，随着数据库可观测性能力的增强，这类问题的发现会越来越自动化，但“为什么扫描范围会扩张”的机制理解仍不可替代。笔者认为，工程团队真正需要沉淀的，不是一份索引清单，而是一套围绕“访问路径—加锁集合—并发影响”的分析方法，以及把它固化为巡检与评审流程的组织能力。

主要参考文献