很多人在面试中被问到：“MySQL 的 UPDATE 语句什么时候会锁全表？”第一反应往往是“没有索引的时候”。这个答案对了一半，但远不够完整。实际上，导致 UPDATE 锁全表的场景有好几种，底层原因也各不相同。这篇文章把这个问题彻底讲清楚。
先理解 InnoDB 的锁机制
在分析原因之前，需要先明确一个前提：MySQL 的锁行为取决于存储引擎。MyISAM 只支持表锁，任何 UPDATE 都会锁全表——但这不是我们讨论的重点。面试中问的通常是 InnoDB，因为 InnoDB 支持行锁，但某些情况下行锁会“升级”为表级锁定。
InnoDB 的行锁是加在索引上的，而不是加在数据行本身。这是一个关键认知：如果 UPDATE 语句没有走索引，InnoDB 就无法精确定位到需要加锁的行，只能扫描全表并对所有扫描到的记录加锁。
更准确地说，InnoDB 在 RC（Read Committed）和 RR（Repeatable Read）隔离级别下，对于 UPDATE 操作会使用当前读，加的是排他锁（X 锁）。如果无法通过索引快速定位，就需要全表扫描，逐行加锁，最终效果等同于锁全表。
原因一：WHERE 条件没有索引
这是最常见的情况。假设有一张用户表：
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
email VARCHAR(100)
) ENGINE=InnoDB;
执行：
UPDATE users SET age = 18 WHERE name = '张三';
如果 name 字段上没有索引，InnoDB 只能通过聚簇索引（主键索引）全表扫描，对每一条扫描到的记录都加上 X 锁。由于扫描范围覆盖了整张表，所有行都被锁定。
在 RR 隔离级别下更严重：InnoDB 不仅锁定扫描到的行，还会对间隙（gap）加锁，形成 next-key lock。这意味着其他事务不仅无法更新任何一行，甚至无法插入新记录——整张表在锁释放前基本处于不可写状态。
原因二：索引失效导致全表扫描
即使 WHERE 条件中的字段建了索引，也可能因为写法问题导致索引失效，退化为全表扫描。典型场景包括：
- 对索引列使用函数：
WHERE YEAR(created_at) = 2024 - 隐式类型转换：字段是 VARCHAR，却用数字查询
WHERE phone = 13800138000 - 前导模糊匹配：
WHERE name LIKE '%张三' - 使用 OR 连接非索引列：
WHERE id = 1 OR age = 20（age 无索引时） - 违反最左前缀原则：联合索引
(a, b, c)，查询条件只有b和c
这些情况下，优化器判断走索引的成本高于全表扫描，最终选择全表扫描，锁范围随之扩大到整张表。
原因三：UPDATE 条件命中大量数据
有时候索引是有效的，但 WHERE 条件本身筛选出的数据量太大。比如：
UPDATE users SET status = 1 WHERE age > 0;
如果 age 上有索引，但几乎所有人的 age 都大于 0，优化器可能认为全表扫描更快（因为回表成本高），于是放弃索引。即便走了索引，锁定的行数也接近全表。
这里涉及一个优化器的判断逻辑：当预估扫描行数占总行数的比例过高（通常超过 20%~30%）时，优化器倾向于选择全表扫描而非索引扫描。这不是 bug，而是基于成本的优化决策。
原因四：MDL 元数据锁
这是一个容易被忽略的场景。当你执行 UPDATE 时，MySQL 会自动给表加上 MDL 读锁。如果此时有另一个事务持有该表的 MDL 写锁（比如正在执行 ALTER TABLE），UPDATE 就会被阻塞。
反过来，如果 UPDATE 事务长时间不提交，它持有的 MDL 读锁会阻塞后续的 DDL 操作。更危险的是，DDL 操作一旦被阻塞，它会阻塞后续所有对该表的请求（包括 SELECT），造成“表被锁住”的现象。
虽然这不是严格意义上的“锁全表”，但在实际表现上，整张表的所有操作都被卡住了。
原因五：显式锁表或锁升级
某些情况下，DBA 或开发人员可能显式执行了 LOCK TABLES ... WRITE，或者在 MyISAM 引擎下操作。另外，当 InnoDB 的行锁数量超过 innodb_lock_table_threshold（默认 200）时，虽然不会真正升级为表锁，但锁管理开销会急剧增加，性能表现接近表锁。
如何避免 UPDATE 锁全表
理解了原因，解决方案就很清晰了：
- 确保 WHERE 条件走索引。用
EXPLAIN检查执行计划，确认 type 不是ALL。 - 避免索引失效的写法。不在索引列上做函数运算、类型转换，避免前导模糊匹配。
- 控制更新范围。大批量更新拆分成多个小批次，每批几千行，减少单次锁持有时间。
- 缩短事务时间。UPDATE 后尽快 COMMIT，不要在事务中夹杂耗时操作。
- 合理使用隔离级别。RC 级别下没有间隙锁，锁范围比 RR 小，对并发更新更友好。
- 关注 MDL 锁。执行 DDL 前检查是否有长事务，避免 DDL 被阻塞引发连锁反应。
面试回答要点
如果面试中被问到这个问题，可以这样组织回答：
InnoDB 的 UPDATE 锁全表，根本原因是行锁加在索引上。当 WHERE 条件没有索引、索引失效、或优化器判断全表扫描成本更低时，InnoDB 会扫描全表并对所有扫描到的记录加锁，效果等同于锁全表。在 RR 隔离级别下还会加间隙锁，进一步扩大锁范围。此外，MDL 元数据锁在 DDL 与 DML 冲突时也会造成表级阻塞。避免的关键是让 UPDATE 走索引，并控制事务粒度。
这个回答覆盖了核心原理、具体场景和解决方案，通常足以应对大多数面试追问。
未经允许不得转载：任鹏个人博客 » MySQL 为什么在更新时有时会锁全表
