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

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

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


有问题欢迎评论区交流,明天见!

相关文章
|
SQL 算法 关系型数据库
MySQL InnoDB中的锁-自增锁(AUTO-INC Locks)
MySQL InnoDB 锁 自增锁AUTO-INC Locks
3137 0
|
6月前
|
SQL 关系型数据库 MySQL
【MySQL百日打怪升级第14天】 LIMIT 分页的性能优化:深分页到底慢在哪?
本文深入剖析MySQL深分页(如`LIMIT 100000,20`)性能瓶颈:本质是OFFSET导致全量扫描与丢弃,页码越深,扫描行数线性增长。详解三种实战优化方案——游标分页(高效稳定,需有序唯一字段)、延迟关联(兼容OFFSET,索引覆盖减回表)、范围分页(极简但场景受限),并附EXPLAIN对比与避坑指南。(239字)
569 6
|
6月前
|
SQL 算法 关系型数据库
【MySQL百日打怪升级第10天】JOIN的底层原理与优化:NLJ、Hash Join 与 Merge Join
本文系统解析MySQL三大JOIN算法:NLJ(含Simple/Index/Block变体)、8.0.18引入的Hash Join(O(N+M)复杂度,专治无索引大表连接),以及面试常考但MySQL原生不支持的Sort-Merge Join,附实战EXPLAIN识别与优化指南。(239字)
514 5
|
5月前
|
人工智能 NoSQL 关系型数据库
【MySQL百日打怪升级第22天】事务死锁的成因与避免 —— 死锁检测、锁等待超时
【MySQL死锁精讲】第22天干货:剖析死锁成因(循环加锁)、检测机制(等待图算法)、四大场景(转账/外键/Gap Lock等)及实战避坑方案——统一加锁顺序、缩短事务、合理选隔离级别,附AI诊断工具箱。
411 0
|
5月前
|
人工智能 运维 监控
非技术创始人选Agent平台的踩坑实录:低代码看起来简单,坑都在后面
本文深度剖析企业级多智能体系统选型的五大核心陷阱:混淆“多Agent”与“多聊天机器人”、低估系统性成本、忽视执行确定性、缺乏治理体系、回避失败处理。强调选型关键不在Agent数量,而在能否安全接入真实系统、实现可治理、算清长期“闭环成本”。(239字)
|
6月前
|
SQL 缓存 关系型数据库
【MySQL百日打怪升级第18天】事务隔离级别详解:RC、RR、Serializable 到底怎么选?
本系列第18天详解MySQL事务隔离级别:RC(读已提交)、RR(可重复读,默认)、Serializable。厘清脏读、不可重复读、幻读三大问题,剖析InnoDB下RR如何借MVCC+Gap Lock基本防幻读,并给出RC与RR选型建议及真实避坑指南。
612 0
|
7月前
|
SQL 关系型数据库 MySQL
MySQL IN 里塞 10000 个值?90% 开发者都踩过的坑,底层原理 + 全场景解决方案一次讲透
本文深入剖析MySQL IN子句的底层执行逻辑,纠正常见误区(如“1000值硬限制”“IN=OR”),揭示大IN列表引发的性能、稳定性及安全风险,并提供分批查询、临时表JOIN、关联/EXISTS查询、流式查询四类场景化解决方案,附实测性能对比与生产避坑指南。
1096 4
|
5月前
|
SQL 中间件 关系型数据库
读写分离中间件怎么选?ProxySQL 落地踩坑与选型对比
ProxySQL是一款轻量高性能MySQL中间件,原生支持读写分离、自动故障切换与查询路由,相比MyCAT、ShardingSphere更专注、易用、低损耗,特别适合仅需主从读写分离的场景。
|
8月前
|
SQL BI 数据库
企业智能问数平台的真正分水岭:本体语义层与预置指标层到底差在哪?
企业智能问数平台成败关键不在大模型或界面,而在于底层数据治理逻辑:是构建“预置指标层”(稳态可控、适合成熟BI体系),还是打造“本体语义层”(弹性扩展、支撑跨域复杂分析)。选型需权衡建设成本、维护负担与长期演进能力。
|
6月前
|
SQL 关系型数据库 MySQL
【MySQL百日打怪升级第20天】行锁 vs 表锁 —— InnoDB 什么时候锁行、什么时候锁表?
【第20天】MySQL锁机制精讲!深入解析InnoDB行锁与表锁的本质区别:行锁依赖索引,无索引则全表扫描→实际锁全表;详解Record/Gap/Next-Key三种行锁、意向锁作用及锁诊断实战(PS视图+INNODB STATUS)。避坑指南:UPDATE/DELETE务必走索引!
434 3