1. 数据操作的本质：UPDATE 和 DELETE 背后的行锁定机制
很多刚接触 SQL 的开发者，最早学会的几条语句就是 SELECT、INSERT、UPDATE 和 DELETE。表面上看，UPDATE 是“改数据”、DELETE 是“删数据”，语法也不复杂。但实际上一旦放到生产环境，这两条语句往往是事故高发区：一条不带 WHERE 条件的 UPDATE 能瞬间锁死整张表，一条 DELETE 能把主库拖垮，更别提在事务隔离级别和并发写入的双重作用下，行锁、间隙锁、临键锁是怎么互相纠缠的。
要真正理解 UPDATE 和 DELETE，不能只看语法，得从“数据修改的本质”入手。UPDATE 本质上是一个“读-改-写”过程：先定位目标行，拿到当前值，在内存里修改，再写回磁盘。DELETE 同样不是直接把数据从磁盘上抹掉，而是先标记删除，再由后台清理机制（如 InnoDB 的 purge 线程）物理回收空间。这个“标记删除”的设计，是理解 MVCC（多版本并发控制）和事务隔离级别的关键。
另一个不能忽略的事实是：UPDATE 和 DELETE 一旦执行，就会对涉及的行加上排他锁（X Lock）。写锁的意义在于——禁止其他事务同时修改同一行，同时也禁止其他事务对这一行加上共享读锁。这直接决定了生产环境里的并发表现：一个长时间运行的 UPDATE，会让后续所有涉及相同行数据的 SELECT 都堵塞在锁等待上。
很多没有深入了解数据库原理的朋友会问：为什么数据库不把“改数据”做成交叉复制式的“原地变更”呢？为什么要有这么多锁机制？答案很简单：数据的一致性。一个事务里可能包含多条 UPDATE，比如转账场景里 A 账户扣钱、B 账户加钱，这两步必须组成一个原子操作。如果不加锁，并发情况下就会出现 A 扣了钱但 B 没到账这类灾难性结果。所以，任何说“UPDATE 就是 set 字段值”的解释，都是只看到了冰山一角。
这篇文章适合的读者，是那些已经能熟练写出 SELECT 查询，但 UPDATE 和 DELETE 还停留在“照着模板写”阶段的开发者和运维人员。我会从锁机制、执行过程、性能优化、常见故障排查四个方向，把这些看似简单、实则暗坑无数的语句讲透。内容不依赖某个具体数据库版本，但涉及 MySQL(InnoDB) 的细节会明确标注；SQL Server 和 PostgreSQL 的差异也会在关键节点提出来。
2. 加锁读的意义：SELECT 与 UPDATE 在并发场景下的联动
很多人对“UPDATE 和 DELETE 会加锁”这句话没有直观感受，直到真正遇到线上事故。我曾经处理过一个案例：业务高峰期，某个订单表上一条 UPDATE 语句因为要更新上万行数据，每行都需要持有锁，执行时间拉长到十几秒。结果所有针对该表的读操作全部堵塞，应用层连接池被打满，服务直接雪崩。
这场事故的根因不在这条 UPDATE 本身，而在于读写之间的锁竞争。InnoDB 的默认隔离级别是可重复读（REPEATABLE READ），普通 SELECT 走的是快照读（MVCC 版本链），不申请锁，因此理论上不会和 UPDATE 冲突。但一旦在事务里使用了SELECT ... FOR UPDATE这类加锁读，情况就完全不同了。加锁读会申请与 UPDATE/DELETE 相同类型的排他锁，这就像在已有车流的单行道上又插入一辆逆行车，堵塞是必然的。
日常开发中，SELECT ... FOR UPDATE最常见的用法是“先查后改”的并发控制。比如库存扣减场景，代码里先查询库存剩余数量，判断是否充足，再执行 UPDATE 扣减。这个“判断+扣减”如果不放在同一个事务里，且查询时不加锁，就会出现超卖。加了FOR UPDATE后，同一行数据在事务提交前，其他事务都无法修改，也就避免了判断期间的数据变化。
但很多人没意识到的是，FOR UPDATE的加锁范围可能比预期大得多。当查询条件命中二级索引时，InnoDB 不只锁命中的二级索引记录，还会锁定对应的聚簇索引记录；如果查询条件没有索引可用，就会退化为全表扫描，这时锁的不只是目标行，而是扫描过程中经过的每一行。换句话说，一个没走索引的FOR UPDATE查询，等于给整张表上了写锁。
这里有个实用的排查技巧：查看EXPLAIN输出的key字段，如果显示NULL，说明查询没走索引。加锁读场景下，这类型查询必须改造，要么补索引，要么缩小扫描范围。我曾经接手过一个订单统计功能，每次运行都要扫描上百万行，就是因为在 WHERE 条件里用了DATE(create_time) = CURDATE()这种写法，导致 create_time 索引失效。改成create_time >= '2024-01-01 00:00:00' AND create_time < '2024-01-02 00:00:00'后，扫描行数从百万级降到了千级，锁竞争问题迎刃而解。
3. 深入 UPDATE 内部：从语法细节到性能影响的全面拆解
3.1 不带 WHERE 的 UPDATE：从“改一行”到“全表锁定”
UPDATE 语句的基础语法是UPDATE table SET col = value WHERE condition。理论上，WHERE 条件是可选的，但省略 WHERE 意味着对所有行执行修改。这在开发环境也许没什么问题，但一旦在生产环境误执行，恢复数据的成本极高。
为什么全表 UPDATE 会这么危险？不只是数据被整体修改，更重要的是执行过程中的锁行为。没有 WHERE 条件或 WHERE 条件无法使用索引时，InnoDB 会扫描全表，对扫描到的每一行加锁并修改。这个过程中，所有对这些行有写需求的并发事务都会阻塞。如果表里有一百万行，等于这一百万行在被修改期间全部处于“绑定”状态。
我见过最典型的一次事故：运维同事在测试库执行了UPDATE user SET status = 1，本意是想把所有用户状态改为启用。但当时这个命令是通过生产环境的跳板机误执行的，整张用户表被瞬间改掉。幸好提前做了全量备份，最终通过备份文件和 binlog 回放恢复了数据。但这个过程耗时四个小时，业务中断四个小时。
这类事故的防御手段，业内常用的大概有这么几种：
- 在 MySQL 客户端强制开启
--safe-updates模式，该模式下不带 WHERE 的 UPDATE 和 DELETE 会被直接拒绝执行。这个配置在开发机上尤其推荐。 - 执行数据变更前先执行
SELECT COUNT(*) FROM table WHERE condition，确认影响行数符合预期。不要把“我猜大概是几行”当成执行依据。 - 对核心表启用“先备份后修改”的流程，要么对目标行做
CREATE TABLE backup AS SELECT ...备份，要么把变更语句放到事务里，执行后先不提交，用另一个会话查询验证，再决定 COMMIT 还是 ROLLBACK。
3.2 SET 子句的赋值顺序和表达式陷阱
UPDATE 的 SET 子句在某些数据库里允许“从左到右”的赋值顺序。比如UPDATE t SET a = b, b = a，不同数据库对这个语句的解释不一样。在 MySQL 中，SET 子句的赋值顺序是“从左到右”的，也就是先把 b 的旧值赋给 a，再用 a 的新值赋给 b。而标准 SQL（以及 PostgreSQL）的行为是“一次性求值”，即所有表达式都基于更新前的行值计算，等价于a = b, b = b。这两种行为可能导致完全不同的结果。
举个实际例子，表中有两列 x 和 y，当前值为 x=1, y=2。执行UPDATE t SET x = y, y = x：
- MySQL 执行结果是 x=2, y=2。第一步 x 被设置为 y 的当前值 2，第二步 y 被设置为 x 的新值 2。
- PostgreSQL 执行结果是 x=2, y=1。因为两个赋值都基于更新前的旧值。
这个差异如果不了解，跨数据库迁移时很容易踩坑。解决办法：需要交换两列值时，不要直接使用SET a = b, b = a这种写法，而是先在 SELECT 里把旧值取出来，再显式写入。或者使用中间变量（比如SET @tmp = x），把逻辑拆成两步。
SET 子句也支持表达式计算，比如SET price = price * 0.8。这种基于自身旧值更新的操作，配合“读-改-写”的事务机制，天然存在并发安全风险。两个事务同时对 price 做price = price + 1，如果不加锁，最终可能只加了一次而不是两次。但 InnoDB 的行锁机制会保证这两个 UPDATE 串行执行，所以事务内不会有问题。真正需要注意的，是业务层面“先读后写”的非原子操作，比如代码里先 SELECT price，判断 price 大于某个值后再 UPDATE，这中间就存在时间窗口。
这类“先读后写”操作的正确姿势，我在前面加锁读部分已经提过：要么用FOR UPDATE，要么直接把条件写进 UPDATE 的 WHERE 子句，比如UPDATE t SET price = price - 10 WHERE id = 100 AND price >= 10，利用数据库的单语句原子性避免并发覆盖。
3.3 UPDATE 与 JOIN：一次修改多张表的两种写法
在实际业务里，UPDATE 经常需要关联其他表取条件或取值。比如“把订单表中所有已支付订单的优惠券状态改为已使用”，这个优惠券状态在另一张表里。两种主流写法：一种是 UPDATE ... JOIN 语法，另一种是子查询。
MySQL 支持UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.status = 'done' WHERE t2.type = 'paid'这种语法。SQL Server 用的是UPDATE t1 SET t1.status = 'done' FROM t1 JOIN t2 ON ... WHERE ...，PostgreSQL 则推荐UPDATE t1 SET status = 'done' FROM t2 WHERE t1.id = t2.id AND t2.type = 'paid'。语法各不相同，但核心思路一致：通过连接确定要修改的行集合，然后执行更新。
这种 JOIN UPDATE 的性能要点在于连接字段的索引。如果 ON 条件的关联字段没有索引，连接过程就是嵌套循环扫描，小表驱动大表还好，大表驱动大表就是灾难。我在优化一个报表系统时遇到过一条 UPDATE，三张表做 JOIN，关联字段都没有索引，执行需要四十分钟。给中间表的关联字段补上索引后，执行时间降到三秒。优化效果立竿见影，也再次证明索引对数据修改语句的重要性。
子查询写法的典型场景是“根据另一张表的最大值/最新值来更新”。比如把用户表的最新登录时间，更新到用户统计表中：UPDATE user_stats SET last_login = (SELECT MAX(login_time) FROM login_log WHERE login_log.user_id = user_stats.user_id)。这种写法要注意子查询里的相关引用，同时对子查询中 login_log 表的 user_id 字段建索引，否则每更新一行都触发一次全表扫描。
性能上，JOIN 和子查询没有绝对的谁优谁劣，主要看执行计划。优化器会把相关子查询改写为 JOIN，也可能把 JOIN 物化为临时表。判断依据还是EXPLAIN里的访问类型和扫描行数，不实测就不要轻易下结论。
3.4 单条 UPDATE 与批量 UPDATE：性能取舍和长事务问题
UPDATE 一次处理多少行，对性能影响很大。一条 UPDATE 更新一万行，和更新一行，执行计划差异不小。行数越多，锁持有的时间越长，换句话说，事务把“吃”进肚子里的锁数量越多，释放得也越慢。在并发写入的场景下，批量大更新很容易成为阻塞源头。
很多运维同学会建议把大 UPDATE 拆成小批次执行，比如一次只更新一千行，循环执行十次。这样做的目的是减少单次事务的持锁时间，让其他事务有机会穿插执行。拆分方式有很多种：按主键范围拆分、按 id 取模拆分、按时间字段拆分。但要注意，单纯依赖LIMIT加上 WHERE 条件循环更新时，如果 WHERE 条件没有能区分“已更新”和“未更新”的字段，就会出现重复更新的情况。比如UPDATE t SET status = 1 WHERE status = 0 LIMIT 1000，每次执行都会重新扫到 status=0 的行，直到最后一次全部改完。这种写法效率不高，因为 LIMIT 本身也需要扫描到足够多的行才能停下。
更好的拆分方式：一次查询出目标行的主键范围，再按范围分段执行。比如先SELECT MIN(id), MAX(id) FROM t WHERE status = 0 AND id BETWEEN ? AND ?，然后每次更新一整段主键区间，完成后记录下当前进度，下轮继续。这样每轮扫描的行数可控，锁的范围也小得多。
长事务问题同样值得警惕。一个事务里如果包含多个大 UPDATE，事务持续时间可能长达数十秒甚至数分钟。在这期间，事务持有的锁不会释放。更重要的是，长事务会导致 undo 日志膨胀，因为 MVCC 需要保留旧版本数据供其他事务的快照读使用。undo 膨胀到一定程度，磁盘空间被吃满，数据库可能直接拒绝写入。所以，但凡涉及 UPDATE 和 DELETE 的生产变更，都应该问自己一句话：这个事务能不能拆短？能拆就拆。
4. DELETE 的执行细节：物理删除、逻辑删除和空间回收
4.1 DELETE 的真正含义：标记删除与 purge 机制
DELETE 语句在 InnoDB 中并不是“立即物理删除”数据。执行 DELETE 时，记录会被标记为已删除，同时生成一条 delete-mark 的 undo 日志。之后这些“死亡”记录会由后台 purge 线程异步清理，真正释放索引和聚簇索引中的空间。
因为存在这个延迟回收机制，所以 DELETE 之后，表空间文件在系统层面可能不会立即变小。这是很多数据库初学者的困惑：删了几百万行数据，但磁盘空间一点都没少。如果删除后需要立刻释放空间给操作系统，需要执行OPTIMIZE TABLE（MySQL）或VACUUM FULL（PostgreSQL）来重建表，这会重新组织表数据，压缩碎片，最终把空闲空间交还给操作系统。但这个过程会锁表，并且耗时取决于表的大小，必须在低峰期执行。
批量 DELETE 同样需要考虑锁和性能。一次 DELETE 数百万行数据，即使有索引可用，也会因持续持有锁而影响在线业务。推荐分批删除，每批几千行到一万行不等，批次之间停顿几秒，让后台 purge 线程跟上节奏。如果批量删除的字段是时间字段（比如删除三个月前的日志），那么给时间字段建立合适的索引会大幅提升定位效率，否则每次都需要全表扫描来匹配 WHERE 条件，代价极高。
4.2 逻辑删除：用 UPDATE 代替 DELETE 的经典实践
开发中最稳妥的删除方式，其实是“不删除”。给表加一个deleted或is_deleted字段，删除操作变为UPDATE t SET deleted = 1 WHERE id = ?，所有查询都强制带上AND deleted = 0条件。这就是逻辑删除（软删除），在很多对数据完整性和可审计性要求高的场景里是标准做法。
逻辑删除的核心好处是：数据可恢复、操作可追溯、不会因误删除造成不可逆损失。代价是查询条件增多，代码里容易漏写deleted = 0，导致统计数据包含已删数据。为解决漏写问题，可以借助框架的全局拦截机制，比如 MyBatis-Plus 的逻辑删除配置，或者通过数据库视图只暴露未删除数据。
我也见过有些团队对“逻辑删除后唯一索引怎么办”这个问题纠结。比如用户表有手机号唯一索引，逻辑删除一个用户后，新用户用同一手机号注册，会因为旧行的唯一索引冲突而失败。常见解法是把删除标记和唯一索引做组合，比如unique_key索引改为(phone, deleted)，删除时把 deleted 设置为一个随机的非零值（比如主键 id），这样新旧数据不会冲突。这种设计的细节要充分测试，否则可能出现推送补单、数据错配等问题。
4.3 DELETE 与 TRUNCATE、DROP 的区别
DELETE 是 DML（数据操作语言），TRUNCATE 是 DDL（数据定义语言），DROP 也是 DDL。三者都涉及“删除”，但实际行为差异巨大。
- DELETE：逐行删除，返回受影响行数，可以通过事务回滚，不释放表空间（指 InnoDB 下）。删除时每行都会记录 undo 日志。
- TRUNCATE：删除表中所有行，相当于重建表结构，返回 0 行受影响，但在某些数据库（比如 MySQL）中隐式提交，不可回滚。TRUNCATE 会释放表空间给操作系统吗？分情况。MySQL 的 TRUNCATE 会重建表数据文件，基本相当于
DROP + CREATE，表空间会重置。表定义还在，但原有文件空间被释放。由于它是 DDL 级操作，速度远快于 DELETE。 - DROP：直接删除整张表，包括表结构、数据、索引、触发器，表完全消失。如果没提前备份，DROP 后只能用备份文件或 binlog 恢复。
生产环境里“清空表数据”应该选 DELETE 还是 TRUNCATE，关键看是否有事务回滚需求。如果确定不要这些数据，且数据量很大，TRUNCATE 更快；但如果担心误操作，还是 DELETE + 事务包裹更稳。很多新手不知道 TRUNCATE 在 MySQL 会隐式提交，导致想回滚时完全没法回滚，这个坑我见得太多了。
5. 影响 UPDATE 和 DELETE 执行效率的核心因素
5.1 索引策略：为什么 WHERE 条件设计的优先级高于一切
UPDATE 和 DELETE 的 WHERE 条件能否走索引，直接决定了语句的执行效率。所谓“走索引”，是指数据库能通过索引快速定位到需要修改的行，而不用遍扫全表。走索引时，扫描行数等于目标行数或接近目标行数；不走索引时，扫描行数等于全表行数。这两者在万级、百万级数据量下表现天差地别。
判断语句是否走索引，方法就是执行计划（EXPLAIN）。重点关注type列：const、ref、range属于比较好的访问方式，ALL则代表全表扫描，几乎必然会性能垫底。rows列是优化器估算的扫描行数，行数越大，执行成本越高。
常见的索引失效场景有很多，我挑几个 UPDATE 和 DELETE 场景下最常出现的说：
- 在 WHERE 条件字段上使用函数或表达式，比如
WHERE DATE(create_time) = '2024-01-01'，导致 create_time 索引失效。解决办法：改写为范围条件，维持字段原样。 - 隐式类型转换，比如手机号字段是 varchar，但查询条件传了数字
WHERE phone = 13800138000，MySQL 会把字符串字段转成数字再比较，索引失效。解决办法：参数保持和字段类型一致。 - 前模糊匹配，比如
WHERE name LIKE '%abc%'，这种写法无法利用普通 B+Tree 索引的前缀匹配特性。可以考虑全文索引或者 ES 之类的搜索引擎方案。
对 UPDATE 和 DELETE 语句而言，索引设计的目标是：让 WHERE 条件能够快速收缩范围，避免修改语句扫描大量无关行。核心表的修改场景，建议把 WHERE 条件中的字段组合成复合索引，并利用 EXPLAIN 验证优化器是否选择了合适索引。索引不是越多越好，因为每次 UPDATE 或 DELETE 都会同步更新相关索引，索引过多会拖慢修改速度。
5.2 表碎片化和页分裂：数据修改后的空间膨胀
UPDATE 和 DELETE 反复执行，表数据会逐渐碎片化。碎片化从何而来？当 UPDATE 修改某行的变长字段（如 varchar）时，如果新值比旧值更长，可能无法在原位置放下，InnoDB 会做“页分裂”，把数据分散到不同页。DELETE 会留出空闲页。随着时间推移，表数据页变得碎片化，扫描时需要读入更多数据页，执行效率就会下降。数据页就像抽屉里的文件夹，文件多了但不整齐，找东西自然慢。
碎片化对查询性能的影响，在小数据量时基本感知不到，但数据量到了百万、千万级别，差别就明显了。处理方式依然是重建表压缩空间：MySQL 的OPTIMIZE TABLE或ALTER TABLE ... FORCE，PostgreSQL 的VACUUM FULL。重建表会重写整个表的数据，锁表时间长，所以建议放在维护窗口执行，或者使用在线 DDL 功能（比如 MySQL 5.7 以上配合在线 DDL 特性）。
顺带提一个和碎片化相关的运维指标：使用 information_schema 的数据量统计，关注DATA_FREE字段，它表示表空间中空闲空间。DATA_FREE异常增大说明大量 DELETE 或 UPDATE 产生的碎片未被回收，占用了磁盘空间。定期巡检这个指标，对保持数据库健康很有帮助。
5.3 锁等待和死锁：修改语句最容易踩的并发雷区
并发场景下，UPDATE 和 DELETE 最容易碰到两类问题：锁等待超时和死锁。
锁等待超时，直观表现是执行 UPDATE 时报错“Lock wait timeout exceeded”。原因是这条语句需要修改的行已经被其他事务锁定，本事务只能等待。等待时间超过innodb_lock_wait_timeout（默认 50 秒）就报错。排查方法：查询information_schema.innodb_trx、innodb_lock_waits视图，找到阻塞源事务，分析它在做什么、持有哪些锁。更直观的办法是启用SHOW ENGINE INNODB STATUS查看锁等待信息，里面会列出等待锁和被等待锁的记录。
死锁是更麻烦的状况：两个事务各自持有对方需要的锁，互相等待，谁也无法继续。死锁发生后，InnoDB 会检测到并牺牲其中一个事务（回滚），让另一个继续执行。对业务而言，死锁通常表现为偶发性的 UPDATE/DELETE 执行失败。应对死锁的核心思路是“统一加锁顺序”：多个事务修改多行数据时，如果都按主键从小到大依次修改，就极大降低死锁概率。还有一种常见的死锁场景是批量更新时，两个事务更新同一批数据但顺序不同。解法：应用层保证同一批数据只能被一个事务处理，或者把大事务拆小，减少锁持有的时间窗口。
真实生产中，死锁无法完全消除，只能把发生频率降到很低。我的建议：核心数据修改操作必须设置重试机制，捕获死锁异常后延迟重试，比如 200 毫秒到 1 秒之间的随机退避，重试二到三次，基本能覆盖偶发死锁场景。
6. 事务、日志与一致性：修改语句背后的可靠性与恢复机制
6.1 事务边界内的 UPDATE 和 DELETE：不是“执行即生效”
很多人初学 SQL 时会默认语句执行成功就生效了。其实，在事务型数据库里，只有执行 COMMIT 之后修改才对其他事务可见；如果最终执行 ROLLBACK，所有修改都会被撤销。事务边界的存在，让 UPDATE 和 DELETE 获得了“后悔药”机制。
正因为如此，生产环境的变更逻辑应该封装在事务中。比如“先更新订单状态，再扣减库存”，这两步必须在一个事务里，要么都成功，要么都失败。如果拆成两个独立事务，第一步成功、第二步失败，就会留下订单状态和库存不一致的脏数据。
事务的隔离级别也直接影响修改行为。可重复读（REPEATABLE READ）和读已提交（READ COMMITTED）的主要差异在于：可重复读下，事务内多次执行相同 SELECT 得到的是相同快照，普通 SELECT 不受其他事务未提交修改的影响。这个特性能让事务内的“先查询、后修改”逻辑保持稳定视角。如果隔离级别是读未提交（READ UNCOMMITTED），就可能读到其他事务未提交的中间状态，这种脏读对修改逻辑非常危险。生产环境我强烈建议至少使用 READ COMMITTED，或保持数据库默认的 REPEATABLE READ（MySQL）。
6.2 预写日志（WAL）和 binlog：数据丢失的最后防线
可靠的事务机制依赖日志。InnoDB 的重做日志（redo log）负责持久化：事务提交前，修改操作已经记录到 redo log 里，即使数据库崩溃，重启后也能通过 redo log 恢复未写入磁盘的数据页。binlog 则是 MySQL 层面的逻辑日志，记录了导致数据变更的 SQL 语句或行映像，用于主从复制和数据恢复。
理解 redo log 和 binlog 的协作方式，能帮你明白为什么断电后数据不丢：事务提交时，redo log 必须先落盘（或者满足innodb_flush_log_at_trx_commit=1时同步落盘），binlog 也要写成功，数据库才会返回 COMMIT 成功。如果返回成功后数据库崩溃，重启后两套日志共同作用，保证已提交事务不丢，未提交事务回滚。
在实际运维里，binlog 是误操作恢复的最后希望。比如前面提到的误 UPDATE 整表，可以通过 binlog 的“时间点恢复”能力，把数据库恢复到误操作前的状态。这也是我反复强调“生产变更前先备份”的底气所在——即使没有备份文件，有 binlog 也大概率能救回来。
6.3 修改语句的原子性：避免“只改了一半”的中间状态
单条 UPDATE 和多条 UPDATE 打包在一个事务里，都具备原子性。所谓原子性，是指操作要么全部生效，要么全部不生效，不存在“执行了一部分”的中间状态。以银行转账为例：A 账户扣钱、B 账户加钱，任何一条 UPDATE 失败，事务回滚，两个账户都不变。这不只是业务要求，也是数据库事务的基本保障。
有人会问：如果事务执行到一半，数据库进程被 kill 了，是不是会出现半成品？不会。数据库崩溃恢复时会扫描 undo 日志，把未提交事务的修改全部回滚。这也是为什么长事务更危险：崩溃恢复时，回滚未提交事务需要读取并处理大量 undo 日志，恢复时间会变长。所以不要让事务长时间挂着，处理完立即提交。
实际编码时，要警惕“单条语句自动提交”的误区。如果关闭了自动提交（autocommit=0），单条 UPDATE 也会在事务中积累，后续没有 COMMIT 前，锁一直不释放。很多线上锁等待的故障，排查下来发现就是开发同学手动关闭了自动提交，执行一条修改后就忘记提交，导致锁被长时间占用。解决方法是明确事务边界，在代码中使用 try-finally 包住 COMMIT 和 ROLLBACK，确保最终一定结束事务。
7. 常见问题与排查技巧实录
7.1 批量更新卡死：锁等待和长事务的诊断
这类问题在调整数据量较大的报表表时特别常见。症状是 UPDATE 或 DELETE 执行时长时间不返回，应用层报超时。排查路径：
- 先看当前有哪些事务在运行。MySQL 下执行
SELECT * FROM information_schema.innodb_trx，关注trx_started（事务开始时间）、trx_state、trx_query。已经跑了很久的事务大概率持有锁。 - 查看锁等待关系。
SELECT * FROM sys.innodb_lock_waits（MySQL 5.7 及以上）能直接列出哪个事务在等哪个事务的锁。老版本可以查information_schema.innodb_lock_waits。 - 找到阻塞源后，分析它的 SQL 和事务代码。如果是人为开启事务没提交，可以直接
KILL对应连接，释放锁。如果阻塞源是合法业务，考虑优化它的执行效率，或者错峰执行。
还有一类“卡死”不是锁，而是 UPDATE 语句本身太慢，比如 WHERE 条件没走索引，全表扫描加逐行更新。这时 EXPLAIN 一下就知道原因了。
7.2 误 UPDATE/DELETE 的恢复方式：备份、binlog 和事务回滚
误操作是 DBA 最怕的事，但谁都不敢说永远碰不到。恢复手段优先级从高到低大概是：
- 如果误操作语句还来得及回滚，也就是执行前没有 COMMIT，直接 ROLLBACK。这是最轻量的方案，但要注意如果 autocommit=1，单条语句执行成功即自动提交，想回滚就晚了。所以生产环境执行高危语句前，手动
BEGIN包起来是最稳妥的。 - 如果已经提交，但操作发生前有全量备份，可以通过备份把整库恢复到备份时间点，然后用 binlog 回放到误操作前一刻。这就是“全量备份+binlog 增量”的经典恢复套路。
- 如果还有主从架构，考虑从延迟从库或“按时间点追 binlog”把对应库表恢复到误操作前，再导出数据导回主库。
平时就要做的事：核心表定期全量备份，binlog 保留期限至少一周，最好有专门用于恢复演练的从库。真出了事故，才不至于手忙脚乱。
7.3 SQL 注入风险下的 UPDATE 和 DELETE：参数化查询是最低要求
说到 WHERE 条件，必须提防 SQL 注入。UPDATE 和 DELETE 的注入危害比 SELECT 更大，原因很简单：注入点如果破坏了原有 WHERE 条件，攻击者可以让 UPDATE 修改整表数据，甚至让 DELETE 清空整表。比如后端代码拼接了"DELETE FROM user WHERE id = " + userId，userId 被传入"1 OR 1=1"，最终执行的语句就变成了删除所有用户。
防御 SQL 注入的最有效手段是参数化查询，也就是预编译语句。无论是 JDBC 的PreparedStatement、Python 的cursor.execute(sql, params)，还是 ORM 框架的查询参数绑定，都能让 SQL 语句结构在编译时定死，用户输入只能作为参数传入，无法改变语句结构。如果业务里有动态排序、动态表名的需求，需要做白名单校验，绝不允许直接拼接用户输入到 SQL 语句中。
我还见过一个有趣但危险的实践：某些框架在更新语句里支持UPDATE ... WHERE id IN (?...)，如果参数列表中间被注入恶意值，同样可能扩大影响范围。因此，不只是选择查询，修改语句的参数化更是核心指标。
7.4 UPDATE/DELETE 与存储过程、触发器的联动问题
存储过程和触发器会自动执行，这在 UPDATE 和 DELETE 时会带来“附带效果”。比如一个 AFTER UPDATE 触发器里又包含 UPDATE 其他表，这个连带修改可能在业务不可见的情况下执行，一旦触发器逻辑出错，排查起来特别麻烦。
我的建议是：触发器在核心业务表上慎用。真要保证多表一致，优先考虑在应用层的事务里统一编码。如果已经存在触发器，做数据变更前先检查SHOW TRIGGERS或者查看表定义，确认是否有隐蔽的联动逻辑。否则，你以为只改了 A 表，实际上 B 表 C 表的数据也被悄悄改了，出了问题又找不到根因。
8. 工具选型与实操建议：从 EXPLAIN 到慢查询日志
8.1 EXPLAIN 的正确打开方式：别只看 type 和 rows
EXPLAIN 是分析 UPDATE 和 DELETE 执行计划的最基础工具。但很多人只盯着type列，看到ALL才紧张，看到ref就放心了，这还不够。还要看key_len（实际使用索引的长度）、extra（是否用到临时表、文件排序等）。比如extra列出现Using temporary; Using filesort，说明这条修改语句的 WHERE 条件或排序需求触发了临时表和文件排序，在大数据量下性能必然堪忧。
对 UPDATE 和 DELETE 的执行计划分析，我更推荐使用EXPLAIN EXTENDED（MySQL 5.6 以下）或直接EXPLAIN后配SHOW WARNINGS，这样能看到优化器改写后的完整语句。有时候你以为自己写的 WHERE 条件很简单，优化器改写后却变成了子查询嵌套，这会影响索引选择。用真实改写后的语句再去优化索引，会更有的放矢。
8.2 慢查询日志和监控：让问题在爆发前暴露
慢查询日志记录了执行时间超过阈值的语句，是排查更新性能问题的重要入口。MySQL 中的slow_query_log配置可以打开，long_query_time设置阈值，比如 2 秒。之后定期分析慢日志，找出执行时间长的 UPDATE 和 DELETE，逐个优化。
光有慢日志还不够，生产环境建议配上监控工具。常见方案是 Prometheus + mysqld_exporter + Grafana，监控指标包括：慢查询数量、锁等待状态、事务运行时长、临时表使用情况、磁盘容量、以及 InnoDB 的行锁时间。当锁等待时间或慢查询数突增时，告警能在业务受影响前提醒你介入。
我个人比较喜欢把“高成本 SQL”抓取出来单独做一个清单，每周复盘一次。对于 UPDATE 和 DELETE，重点看三类：扫描行数超过一万行的、执行时间超过一秒的、锁等待次数大于零的。这三类语句基本能覆盖 90% 的修改性能问题。
8.3 日常开发中的防御性编码实践
最后说一下日常开发里，我积累的几条 UPDATE 和 DELETE 的防御性编码实践：
- 所有修改操作的 SQL 语句，都必须先写 WHERE 条件，再写 SET 或 DELETE。不要反过来写。手动写 SQL 时，我会先写
WHERE id = ?占位，再回去填 SET 内容，确保 WHERE 不会被遗漏。 - UPDATE 或 DELETE 涉及核心表时，在代码里加打印日志，记录影响行数。如果行数和预期不一致，立刻排查。比如预判影响 50 行，结果显示 5000 行，多半是条件写错。
- 修改数据前自动生成备份表备注。比如执行
CREATE TABLE user_bak_20250101 AS SELECT * FROM user WHERE id BETWEEN ... AND ...，确认修改没问题后再删掉备份。 - 批量操作必须用事务包裹，并在代码里显式提交。不要依赖数据库的自动提交模式，那样容易忽略事务边界。
- 定期运行数据一致性校验脚本，比如核对业务核心表中的记录数、金额字段的 SUM 值，和上游系统做比对。这些校验能在数据被错误修改后尽早发现异常。
9. 写在最后的一点个人体会
这些年处理过不少 UPDATE 和 DELETE 引发的故障，从锁等待导致的业务雪崩，到误更新整表后的连夜恢复，每个案例都在反复印证一个事实：这两条语句的难点从来不在语法，而在于对数据修改过程的完整性理解。你需要知道锁是怎么加的，索引是怎么用作定位的，事务是怎么保证原子性的，日志是怎么兜底的，这些知识拼在一起，才能在一行 SQL 执行前预测它的行为，也才能在一行 SQL 出问题时快速找到根因。
按照我个人的操作习惯，任何影响核心数据的 UPDATE 或 DELETE，我都会先在测试环境用近似的表结构和数据量模拟一遍，观察执行计划、实测执行时间、确认影响行数，再上生产。批量操作永远加上“幂等判断”，能拆小就不做大的，能加锁读就先锁定范围。这些习惯未必能让你写出更炫的 SQL，但一定能在关键时刻帮你少踩几个坑。数据是无价的，多花一点时间敬畏它，是值得的。
