# 【第16天】每天一个MySQL知识点，百日打怪升级

> UPDATE 与 DELETE 的最佳实践：锁升级到底是怎么发生的？

---

大家好，我是一名拥有10年以上经验的DBA老兵，没有那多。

做这个系列，源于一个朴素的愿望：把踩过的坑、总结的经验系统化输出，希望能帮到刚入行或想进阶的兄弟们。

让我们开始今天的第16天内容。

---

## 背景引入

> 💡 白天跑 DELETE 导致整个页面卡死的事，你遇到过吗？

有次一个同事跑过来，脸色有点不太对。

他说："我刚在线上跑了一条 DELETE，就删 10 万条数据，结果那个页面 5 分钟打不开了。"

我问："WHERE 条件走了索引吗？"

他愣了一下："啊？DELETE 还要看索引？" 😅

---

你没听错。很多人觉得 DELETE 和 SELECT 差不多——不就是找到数据删掉吗？

**区别大了。**

SELECT 找到一行读出来就走，不对行加锁。
DELETE 找到一行要锁住它、删掉它、记到 binlog 里——每多锁一行，后面的请求就多等一行。

他那条 DELETE 没走索引，直接全表扫了 200 万行，锁了其中 10 万行。后面所有的写入都在等锁释放。

**今天的目标**：搞懂 UPDATE/DELETE 的锁行为和正确姿势。

---

## 核心概念

### 为什么 UPDATE/DELETE 比 SELECT 危险得多？

SELECT 是"看一眼就走"，UPDATE/DELETE 是"找到了还要动手"。

InnoDB 的行锁机制决定了：**UPDATE/DELETE 扫到哪一行，就锁到哪一行。** 哪怕你最终只改 1 行，如果 WHERE 没走索引，MySQL 会扫描全表、锁住所有扫过的行。

拿一个公式来记：

```
锁的范围 = 扫描的行数（不是最终改的行数）
```

这个很多人不知道。常见的认知偏差是："我只改 10 行，凭什么锁了 100 万行？"

因为 WHERE 条件没走索引，MySQL 不知道哪 10 行是你要改的——它只能先扫一遍，扫到的行先锁住，判断是不是你要的，不是就释放，是就留着。

关键是：**锁住再判断，不是判断再锁住。** 顺序反了，代价就大了。

这里还有一个容易被忽视的前提——**隔离级别。**

上面说的"不符合就释放"，在 READ COMMITTED（RC）下成立：MySQL 确实会释放不满足条件的行的记录锁。
但在 REPEATABLE READ（RR，MySQL 默认级别）下就不一样了。全表扫描时，InnoDB 不但给扫过的行加记录锁，**行之间的间隙也会加 Gap Lock**。而且不管这行是否符合 WHERE 条件，**所有锁都会持有到事务结束才释放**。不符合条件的行，它的记录锁和间隙锁一样不会放。其他事务想往这些间隙里 INSERT？直接堵死。整张表变成"只读"。

如果你的大 DELETE/UPDATE 一定要跑，而且业务能接受，**改成 RC 隔离级别 + ROW 格式 binlog 可以减少间隙锁的副作用**。

---

### 锁升级的三种典型场景

锁升级本质上不是 InnoDB 主动把行锁"升级"成表锁。而是 WHERE 没用索引 → 全表扫描 → 扫过的行全部加锁 → 效果上等于锁了整张表。

**常见场景：**

1.  `DELETE FROM orders WHERE create_time < '2025-01-01'` —— `create_time` 没索引 → 全表锁
2.  `UPDATE user SET status = 1 WHERE age > 30` —— `age` 没索引 → 全表锁
3.  `DELETE FROM logs WHERE status = 'DONE'` —— `status` 有索引，但 `'DONE'` 占了 90% 的行，索引扫描范围接近全表，锁住的行覆盖大半个表，效果接近表锁

---

### 批量操作的正确姿势

既然问题出在"一次性扫太多、锁太多"，那解决方案就是：**拆成小批，慢慢来。**

```sql
-- 错误：一次删太多，锁范围太大
DELETE FROM orders WHERE create_time < '2025-01-01';

-- 正确：先查出 id，再按 id 分批删
DELETE o FROM orders o
INNER JOIN (
  SELECT id FROM orders 
  WHERE create_time < '2025-01-01' 
  ORDER BY id 
  LIMIT 500
) AS tmp ON o.id = tmp.id;

-- 在应用层循环执行，直到影响行数为 0
```

注意：不能直接用 `DELETE FROM t WHERE ... ORDER BY ... LIMIT` 这种写法。因为 binlog 如果是 STATEMENT 格式，从库回放时可能走不同索引、选不同行，导致主从数据不一致。先 SELECT id 出来再 DELETE，保证主从删的是同一批。

```sql
-- 错误：一次更新全表
UPDATE user SET status = 1 WHERE age > 30;

-- 正确：用 JOIN 精确限定范围，分批更新
UPDATE user 
INNER JOIN (
  SELECT id FROM user 
  WHERE age > 30 
  ORDER BY id 
  LIMIT 500
) AS tmp ON user.id = tmp.id
SET user.status = 1;
```

不过上面这种写法有个隐藏成本：**每次循环都要重新全表扫描 + 排序去找前 500 个 id**，删 10 次就是 10 次全表扫。如果 `create_time` 这类过滤字段没索引，循环次数多了仍然很慢。

更好的方式是**游标式（cursor）分批**——记下上一批最后一条的 id，下一批直接从它之后继续：

```sql
-- 第一轮：找到前 500 个要删的 id
SELECT id FROM orders 
WHERE create_time < '2025-01-01' 
ORDER BY id 
LIMIT 500;
-- 假设最后一条 id = 10500

-- 第二轮：从 10500 之后继续找
DELETE o FROM orders o
INNER JOIN (
  SELECT id FROM orders 
  WHERE create_time < '2025-01-01' 
    AND id > 10500       -- ← 记住上批最后的位置
  ORDER BY id 
  LIMIT 500
) AS tmp ON o.id = tmp.id;
```

原理很简单：**主键有序，扫描可以断点续传。** 每次从上次结束的 id 位置往后扫，从头到尾只遍历主键索引一次，而不是每轮都从头扫。配合 `ORDER BY id`，每一轮就是一次范围扫描 + 500 次主键 lookup，效率高得多。

如果 `create_time` 上有索引，按时间范围切分更自然——`WHERE create_time >= '2025-01-01' AND create_time < '2025-01-02'`，一天一天删，节奏完全可控。

---

## 实战案例

### 归档旧数据

有一回凌晨的归档任务，DELETE 删 3 个月前的日志，跑了几分钟没结束，业务写入开始超时报警。

我登上去看了一眼——`DELETE FROM audit_log WHERE create_time < NOW() - INTERVAL 3 MONTH` —— `create_time` 没索引。

改成分批删，用的就是上面说的游标式推进：

```
-- 应用层循环，每次记下最后删除的 id
@last_id = 0;
WHILE (1=1)
  DELETE o FROM audit_log o
  INNER JOIN (
    SELECT id FROM audit_log 
    WHERE create_time < NOW() - INTERVAL 3 MONTH
      AND id > @last_id    -- ← 从上次位置继续
    ORDER BY id 
    LIMIT 500
  ) AS tmp ON o.id = tmp.id;

  @last_id = 本次删除的最大 id;  -- 记下来，下次用
  IF 影响行数 = 0 THEN BREAK;
  SLEEP 0.5;                    -- 歇一下，给业务喘口气
END WHILE;
```

那晚我蹲在机房地上改的代码，旁边机柜的散热风扇嗡嗡转着。凌晨三点，整个楼层就我一个人。改完之后，任务跑了 12 分钟平稳结束，报警也消了。

后来这个脚本就成了归档任务的标配模板。

---

## 避坑指南

> ⚠️ 真实踩过的坑：

1. **UPDATE/DELETE 的 WHERE 条件必须走索引**
   - 没有索引 → 全表扫描 → 全表锁
   - 上线前用 `EXPLAIN` 看一眼是不是 `type: range` 或 `ref`，别出现 `ALL`

2. **缺索引就补索引——不用怕，用在线 DDL**
   - 如果 WHERE 字段就是没索引，建议先在线加上再删。建索引期间对业务影响可控，收益却很大
   - MySQL 5.6+ 支持 `ALGORITHM=INPLACE, LOCK=NONE`，加索引时不阻塞读写
   - 一次索引红利，后面所有删除都受益——这笔投资值得做

3. **`LIMIT` 不是万能的——它限的是结果行数，不是锁的行数**
   - `DELETE ... LIMIT 10000` 只删一万行，但扫描过程中锁了多少行取决于 WHERE 能不能走索引
   - 核心是索引，不是 LIMIT

4. **避免在 DELETE/UPDATE 上直接使用 ORDER BY LIMIT**
   - `DELETE FROM t WHERE ... ORDER BY id LIMIT 500` 这种写法，如果 binlog 是 STATEMENT 格式，从库回放时可能因为索引或数据分布不同，选到不同的行来删
   - 正确做法：先 SELECT id 出来，再用多表 JOIN 语法 DELETE/UPDATE

5. **DELETE/UPDATE 加子查询，低版本有隐藏陷阱**
   - MySQL 官方文档明确写了一条限制：对单表的 DELETE/UPDATE 语句，优化器**不会使用 semijoin 和物化优化**
   - 这意味着 `DELETE FROM t WHERE id IN (SELECT ...)` 在低版本（5.5/5.6）上，子查询可能被当作相关子查询逐行执行，性能灾难
   - 官方建议的 workaround 就是改写成多表 JOIN——恰好就是我们前面写的那个模式
   - 来源：[MySQL 5.7 Ref Manual - 8.2.2](https://dev.mysql.com/doc/refman/5.7/en/subquery-optimization.html)

6. **大表 DELETE 注意主从延迟**
   - 大批量 DELETE 产生的 binlog 量很大，从库回放时也可能锁住
   - 如果从库有查询在跑，可能直接导致复制延迟

7. **`pt-archiver` 是你的好朋友**
   - Percona Toolkit 的归档工具，自动分批、自动休眠、不锁表
   - 核心参数：`--limit 500`（每批行数）、`--sleep 1`（批次间休眠秒数）、`--txn-size 500`（每批事务大小）
   - 强制要求表有主键或唯一键——没有的话跑不起来
   - 一行命令搞定：`pt-archiver --source h=host,D=db,t=table --purge --where "create_time < '2025-01-01'" --limit 500 --sleep 1`

8. **不是所有操作都要一条 SQL 搞定——应用层循环有时更安全**
   - 很多人的第一反应是把所有逻辑塞进一条 SQL，但复杂 SQL 出了错很难排查
   - 应用分批操作有一个明显优势：**可控。** 可以加日志、加进度、加重试、加 sleep，随时停随时续
   - 比如用 Python/Java 写个循环：先 SELECT 500 个 id，DELETE 完记录日志，sleep 1 秒，再 SELECT 下一批——整个过程一目了然
   - 对于数据归档这类低频任务，应用层循环的可控性和可观测性远高于一条复杂 SQL

---

## 思考题

> 🤔 互动时间：

1. `DELETE FROM t WHERE id IN (1,2,3,4,5)` —— 如果 id 是主键，这条 DELETE 会锁多少行？
2. `UPDATE` 后在同一事务里直接 `SELECT`，能读到更新后的数据吗？跟隔离级别有关系吗？
3. 一张表 5000 万行，你要删掉 3000 万行旧的。说说你的方案——注意不能把业务拖垮。

---

## 总结

> 🎯 **面试考点**

- UPDATE/DELETE 的危险在于：**锁的范围 = 扫描的行数，不是最终改的行数**
- WHERE 条件不走索引 → 全表扫描 → 等效于表锁；**RR 模式下还有 Gap Lock 把 INSERT 全挡在外面**
- 大操作必须分批：**游标式分批，记下上一批最后的主键 id，下批从它后面继续，避免每轮重复全表扫**
- 缺索引就先在线加索引（`ALGORITHM=INPLACE, LOCK=NONE`），收益远大于成本
- 优先用 `pt-archiver` 做归档，自带流控，强制要求主键或唯一键
- 晚上跑大 DELETE/UPDATE 也不能掉以轻心——binlog 流量会拖慢主从同步

今天就试一下：找到线上一条 UPDATE 或 DELETE 语句，`EXPLAIN` 看看 `type` 字段。如果是 `ALL`，那就是一个等爆的雷。

---

**下期预告**：事务基础：ACID 特性 —— 面试必问！

**全本合集**：[《每天一个MySQL知识点，百日打怪升级》](https://github.com/martianzhang/mysql_100_days)  

---

*有问题欢迎评论区交流，明天见！*


做这个系列，源于一个朴素的愿望：把踩过的坑、总结的经验系统化输出，希望能帮到刚入行或想进阶的兄弟们。

【MySQL百日打怪升级第16天】UPDATE 与 DELETE 的最佳实践：锁升级到底是怎么发生的？

本文详解MySQL中UPDATE/DELETE的锁机制陷阱：WHERE无索引→全表扫描→行锁累积→等效表锁，尤其在RR隔离级下触发Gap Lock加剧阻塞。分享游标分批、索引优化、pt-archiver等安全实践，助你避开线上卡顿雷区。

数据库

探索MySQL中UPDATE与DELETE操作的锁机制问题，解析因未使用索引导致全表扫描引发的锁升级现象。提供分批处理策略与索引优化建议，避免线上业务卡顿，提升数据库操作效率与安全性。

数据归档

Python

MySQL

Java

腾讯云数据库特惠，新用户首单0.5折起


文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

【MySQL百日打怪升级第16天】UPDATE 与 DELETE 的最佳实践：锁升级到底是怎么发生的？-腾讯云开发者社区-腾讯云

【MySQL百日打怪升级第16天】UPDATE 与 DELETE 的最佳实践：锁升级到底是怎么发生的？

【MySQL百日打怪升级第16天】UPDATE 与 DELETE 的最佳实践：锁升级到底是怎么发生的？

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐