【第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% 的行，索引扫描范围接近全表，锁住的行覆盖大半个表，效果接近表锁

批量操作的正确姿势

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

-- 错误：一次删太多，锁范围太大
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，保证主从删的是同一批。

-- 错误：一次更新全表
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，下一批直接从它之后继续：

-- 第一轮：找到前 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

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知识点，百日打怪升级》

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