一条UPDATE语句的完整执行史：从解析、加锁到崩溃恢复

很多开发者在写UPDATE语句的时候脑子里通常只有一件事把某条数据的某个字段改成新值。至于这条语句从发出去到最终落盘数据库到底做了多少事情哪些环节可能卡住、锁住甚至把表搞崩很多人其实说不清楚。我在接手数据库运维之后才真正意识到“UPDATE”远不是一行SQL那么简单。这篇文章我想从一条最简单的UPDATE开始把执行过程层层拆开覆盖解析、优化、加锁、修改数据页、写日志、崩溃恢复以及常见的慢更新和锁等待问题。不管你是后端开发、DBA还是刚接触数据库的运维同学看完之后至少能明白为什么同样一条UPDATE有时候毫秒级返回有时候几十秒不结束为什么少带一个WHERE条件能把整个库拖垮为什么数据库崩溃后这条UPDATE要么完全生效要么完全没生效。我尽量用口语化的方式讲遇到难懂的概念会拿生活里的场景来类比。文章里的例子以MySQL InnoDB为主同时会穿插Oracle和PostgreSQL的差异因为很多团队是多种数据库混用的只看一种容易踩坑。1. 初识UPDATE一条语句在数据库里到底经历了什么1.1 从一条最简单的UPDATE说起先看这条语句UPDATE users SET status 1 WHERE id 100;它做的事很直白把users表里id等于100的那行数据的status字段改成1。但如果你认为数据库就是“找到这行把值一改返回成功”那就把问题想简单了。实际上数据库执行这条UPDATE的完整过程大致要经过客户端发送SQL到服务端、SQL解析与语法检查、语义检查、权限校验、生成执行计划、执行器执行、存储引擎定位记录、加锁、读取或修改数据页、写入undo log、写入redo log、写入binlog、提交事务、返回结果。任何一步出错这条UPDATE的最终状态都可能完全不同。我在排查线上问题的时候最喜欢把这整个过程分成两个大的阶段一个是“SQL真正被MySQL优化器理解”的阶段也就是解析和执行计划生成另一个是“存储引擎真正把数据页改掉”的阶段包括加锁、写undo、写redo、提交。两个阶段的分工不一样出问题时表现也不一样。第一阶段出问题通常报语法错误或权限错误或者SQL执行计划选得很差导致慢第二阶段出问题通常表现为锁等待、死锁、事务回滚、崩溃恢复异常。1.2 执行过程的整体阶段划分下面这个表格把核心阶段列出来后面每一节我会挑重点细讲阶段主要参与组件做了什么事常见问题连接与协议处理连接器、网络层建立连接、认证、获取会话变量连接超时、max_connections打满解析解析器词法分析、语法树构建语法错误、关键字冲突预处理分析器表/字段校验、权限校验表不存在、无权限优化优化器选择索引、决定连接顺序、生成执行计划选错索引导致慢SQL执行执行器调用存储引擎接口逐行获取/修改与存储引擎交互开销引擎层InnoDB定位记录、加锁、修改数据页、写undo/redo锁等待、死锁、锁升级日志与提交InnoDB、binlog两阶段提交保证一致性提交失败、崩溃恢复问题这个表格只是给我自己理思路用的实际生产环境中任何一环都可能成为瓶颈。很多人只关注“优化器有没有走索引”却忽略了“加锁范围有多大”、“binlog刷盘策略是什么”导致问题定位不到点上。接下来我们就按这条主线索把每个环节展开说透。2. 写前准备解析、优化与执行计划是怎么诞生的2.1 语法解析与语义检查客户端发来的UPDATE语句其实是“一串字符串”。数据库服务器拿到这串字符串之后第一步是把它拆成一个个“单词”也就是词法分析。比如UPDATE、users、SET、status、、1、WHERE、id、、100都会被打上标签。紧接着进行语法分析检查这些单词组合在一起是否符合SQL语法规则UPDATE后面必须跟表名SET后面必须跟字段赋值WHERE如果写了就必须是条件表达式。我经常看到新人写错的一个点是UPDATE语句里如果同时修改多个字段字段之间要用逗号分隔但不能有多余的逗号。比如SET status 1, name abc是对的SET status 1, name abc,就报错。这个阶段数据库就能拦截下来根本不会去碰数据。语义检查比语法检查更进一步。数据库要确认users这张表确实存在status和id这两个字段确实在表里status 1这个赋值类型是否兼容。如果表名写错或者字段名写错也会在这里报错。最后还会检查权限当前用户对users表有没有UPDATE权限。这些检查都在真正动数据之前完成所以错误SQL不会产生任何数据影响。2.2 优化器的作用通过语法和语义检查后SQL会交给优化器。优化器的工作是决定“怎么以最小代价完成这条UPDATE”。别小看这一步同样一条SQL用不同的执行方式性能可能差几十倍甚至上百倍。对于UPDATE users SET status 1 WHERE id 100如果id是主键优化器会直接选择主键索引定位到那一行。但如果条件改成WHERE status 0而表里status字段没有索引优化器很可能选择全表扫描。全表扫描的意思是把整张users表的每一行都过一遍检查status是否等于0。表小的时候没感觉表里有几千万行时一次全表扫描消耗的时间和IO就非常可观了。优化器选择执行计划的依据是“代价估算”。它会根据表的记录数、索引区分度、内存参数等估算每个方案需要扫描多少行、产生多少次随机IO。这也是为什么有时候明明有索引优化器却不走索引因为索引区分度太低比如一个字段只有0和1两种值走索引可能还不如全表扫描快。遇到这种情况想强制走索引可以用FORCE INDEX但最好还是先分析为什么索引失效。2.3 执行计划生成与选择执行计划是优化器最终给出的一棵“操作树”。在UPDATE场景里它通常包含“如何读取满足WHERE条件的记录”和“如何更新这些记录”两部分。MySQL的UPDATE可以看作“先读后写”读取部分走的执行计划跟SELECT很像但写入部分还会涉及到锁。你可以在执行UPDATE之前先用EXPLAIN查看这条语句的读取计划。比如EXPLAIN UPDATE users SET status 1 WHERE id 100;这一步非常有用。我遇到过一种情况应用里跑着一条UPDATE通过主键更新一行正常应该毫秒级但线上突然变慢。用EXPLAIN一看发现type变成了ALL也就是全表扫描。原因是WHERE条件里对索引字段做了函数操作比如WHERE DATE(created_at) 2024-01-01导致索引失效。这类问题在解析和优化阶段不会报错只有看执行计划才能发现。3. 真正动手改数据存储引擎层发生了什么解析优化做完后最终动作发生在存储引擎层。MySQL默认使用InnoDB它要完成定位记录、加锁、修改数据页、记录undo等操作。这一层的细节最多也是线上问题的高发地。3.1 定位目标行到底要不要全表扫描按照执行计划执行器会调用存储引擎接口比如engine-update_row()。如果走主键索引InnoDB会根据id100在主键索引的B树里定位到那条记录。B树搜索的代价很小类似从一本书的目录里快速翻到具体页码。如果走二级索引InnoDB先在二级索引上找到对应的主键值再回主键索引查一次这叫做“回表”。如果执行计划是全表扫描InnoDB会从表的第一页开始把每一页里的每一条记录依次读出来逐行判断是否满足WHERE条件。这里要注意UPDATE的全表扫描比SELECT的全表扫描更危险因为每读一行满足条件的记录都要对那一行加锁。这不只会拖慢当前语句还会阻塞其他事务修改这些行导致大面积锁等待。3.2 加锁行锁、间隙锁与锁升级InnoDB的行锁机制是理解UPDATE的核心。数据库为了保证并发更新不混乱会在修改一行之前先给这行加锁。默认的隔离级别是REPEATABLE READ可重复读在这种级别下UPDATE加的是什么锁呢分情况看。如果WHERE条件命中了唯一索引或主键那么只需要对这唯一的一行加“记录锁Record Lock”。比如WHERE id 100只锁id100这条记录。如果WHERE条件没有命中任何索引或者命中的是普通索引且范围很大InnoDB不光会锁住满足条件的记录还会在索引记录之间的间隙加“间隙锁Gap Lock”防止其他事务在间隙里插入新数据。间隙锁跟记录锁合在一起叫“Next-Key Lock”也就是“临键锁”。用一个生活场景来类比假设教室里有10排座位编号1到10。你说“我要改3号座位的人”那把3号锁住就行。但如果你说“我要改所有穿红衣服的人”结果你不知道哪些人穿红衣服就得把整个教室扫一遍而且为了防止同时有人换座位你还得把每排之间的过道也堵住。这个“堵过道”就是间隙锁。间隙锁锁住的不是已有记录而是“不允许别人在这里插入新记录”这正是可重复读隔离级别下避免幻读的手段。所以一条UPDATE影响的行数越多锁的范围就越大阻塞其他事务的概率就越高。这也就解释了很多DBA反复强调的那句话UPDATE一定要保证WHERE条件能走索引尤其是唯一索引。3.3 修改数据页change buffer与普通更新定位到记录并加锁之后InnoDB会把包含这条记录的数据页读入内存Buffer Pool。如果目标记录刚好在内存里直接修改如果不在就先从磁盘读入内存再修改。这里有一个容易被忽略的机制Change Buffer变更缓冲。当你要修改的二级索引页不在内存中时InnoDB不会立刻把索引页从磁盘读出来改掉而是把这次修改缓存在Change Buffer里等下次这个索引页被读到内存时再把变更合并进去。这样做的目的是减少随机IO提升写性能。但Change Buffer也不是万能的。它在写入密集、二级索引多、但数据又不经常被查询的场景下效果很好。如果二级索引的改动非常频繁而相关数据页又长时间不被访问Change Buffer可能会占用太多内存。更关键的是如果一条UPDATE同时修改了多个二级索引每个索引都可能涉及不同的数据页全部要做“缓存变更”或“即时修改”开销会成倍增长。所以不要以为给表加越多索引就越好索引是空间换读性能写性能会打折。普通的数据页修改则相对直接把内存里数据页的记录字段改成新值同时维护一些数据字典信息。修改完之后这个页就变成“脏页”脏页最终会通过后台线程刷到磁盘。3.4 事务与MVCC快照、undo log与当前读咱们平时写UPDATE默认是开启事务的。InnoDB的MVCC多版本并发控制机制让读写不互相阻塞普通SELECT是“快照读”读到的是某个时间点的历史版本而UPDATE属于“当前读”读到的是最新版本并且会对读到的行加锁。这种设计的实现根基是undo log。在修改一行数据之前InnoDB会先把这一行的旧值写入undo log。undo log里记录了“修改之前长什么样”。一旦事务需要回滚就可以根据undo log把数据恢复成旧值。同时undo log还承担了提供多版本快照的功能。其他事务在执行普通的SELECT时如果发现当前行的最新版本不是自己应该看到的版本就会顺着undo log往回找到符合自己事务可见性的旧版本。我动手验证过这个机制开两个事务事务A先UPDATE一行但不提交事务B在同一时间SELECT这行只要隔离级别是REPEATABLE READ事务B读到的是更新前的值。这个“旧值”就是从undo log里构造出来的。所以哪怕数据页已经被事务A改成新值事务B依然能读到旧快照。这就是为什么“快照读”和“当前读”结果不同。很多人在学MVCC时容易晕记不住一行结论UPDATE是当前读必读最新值并且要加锁SELECT默认是快照读读旧版本不加锁。4. 一条UPDATE也不简单redo log、binlog与崩溃恢复你以为数据页改完、事务提交就万事大吉如果数据库在提交瞬间宕机怎么办这就是日志系统要解决的问题。4.1 两阶段提交与redo logInnoDB的redo log重做日志记录的是“物理修改”比如“在哪个数据页的哪个偏移量上把某字段的值改成了1”。数据页可以不在提交时立刻写盘但redo log必须持久化。这样即使数据页还没刷盘数据库崩溃后也能通过redo log把修改重做回来。MySQL里一条UPDATE的提交涉及两个日志InnoDB的redo log和MySQL服务层的binlog。为了保证两份日志的一致性InnoDB使用了两阶段提交协议。简单说事务提交时会先把redo log写入并标记为prepare状态然后写入binlog最后再把redo log标记为commit状态。为什么要分两步因为如果把redo log一上来就标记为commit万一binlog没写成功主从复制时从库就少了这个事务反过来如果先写binlog再写redo log万一redo log没写成功主库就没有这条修改但binlog里有主从数据又不一致。两阶段提交在这两者之间找到一个安全的平衡点。提示如果你遇到过“数据库半夜宕机恢复后某条UPDATE数据丢没丢”的争论本质就是在讨论redo log和binlog的落盘策略。比如innodb_flush_log_at_trx_commit设为0时每次提交不主动刷redo log到磁盘性能高但可能丢最后一秒的数据设为1时每次提交都刷盘最安全但相对慢。线上银行类业务建议设为1缓存类业务可以妥协。4.2 binlog记录的是什么binlog是MySQL的“逻辑日志”记录的是SQL语句或者行级别变化。对于UPDATEbinlog会记录这一行修改前后的值格式有两种STATEMENT和ROW。如果用的是STATEMENT格式binlog直接记录原始SQL语句如果用的是ROW格式binlog记录的是每一行变更的“前镜像”和“后镜像”。我强烈建议生产环境使用ROW格式因为STATEMENT格式在遇到UPDATE带上LIMIT、函数、不确定操作时从库重放可能和主库结果不一致。很多人好奇“UPDATE执行完后binlog里到底长什么样”在ROW格式下二进制内容可以解析成类似“表ID、变更前字段值、变更后字段值”的结构。这也是很多数据同步工具比如Canal能工作的基础。所以一条UPDATE不只改了业务数据它身后还跟着binlog这条线要么用于主从复制要么用于数据恢复要么被下游大数据平台消费。4.3 崩溃恢复如何保证不丢不重如果数据库在执行UPDATE的过程中崩溃恢复逻辑大致如下先检查redo log里面有处于prepare状态的事务再检查对应的binlog是否完整。如果binlog完整说明这个事务已经提交可以重做如果binlog不完整说明事务没有成功提交要回滚。这也是为什么说“commit是一个瞬间状态”而“崩溃恢复是很多异常状态的仲裁”。我在一次故障排查中看到过某台实例在提交过程中强杀进程恢复后有一行数据的更新丢失了。后来排查确认是部署配置把sync_binlog设成了0binlog没有来得及落盘。所以说日志刷盘策略直接决定了数据安全级别这里不能拍脑袋调。5. 实战排查慢UPDATE与锁等待问题怎么定位5.1 一个慢UPDATE的排查思路线上遇到UPDATE变慢我会按下面几步排查先用EXPLAIN看执行计划。确认是否走索引、扫描行数是多少。如果type是ALL或扫描行数几十万大概率是索引没建好或条件写得不合适。看锁等待状态。用SHOW ENGINE INNODB STATUS查看最近的事务和锁信息。如果显示LOCK WAIT说明这条UPDATE正在等待其他事务释放锁。查正在运行的事务。information_schema.innodb_trx表可以看到当前所有事务包括事务开始时间、执行语句、锁等待时间。如果一个事务开了很久还不提交它持有的锁就会一直堵住其他UPDATE。检查是否每条UPDATE都走了相同索引。如果同一张表被多线程同时更新同一行锁竞争就必然存在只能靠业务层做排队或合并更新。有一次我遇到一条UPDATE每秒只能执行几次一开始怀疑SQL写得差逐项排查后才发现是有个程序在循环里逐条UPDATE同一行而且每一条都独立开启事务没提交前又发起下一次更新。等于同一个行锁被自己反复竞争活活把性能拖垮。后来改成一次性事务批量更新立刻好了。5.2 常见锁等待场景场景表现处理建议长事务不提交其他UPDATE一直处于LOCK WAIT定位并杀掉长事务或者优化业务减少事务时长间隙锁范围过大对某范围批量UPDATE插入操作被阻塞尽量用唯一索引精确锁定目标行死锁两个事务互相持有对方需要的锁设置innodb_lock_wait_timeout捕获死锁后重试事务自增锁冲突多条INSERT触发自增锁等待调整innodb_autoinc_lock_mode分离业务场景死锁这个问题值得多说一句MySQL的死锁检测是自动的检测到死锁后会回滚其中代价较小的事务。所以应用层代码里如果写入操作报“Deadlock found when trying to get lock”不要光想着改数据库更常见的做法是在业务代码里捕获这个异常并重试两三次。5.3 UPDATE写成这样性能直接翻车我总结过几个最容易写翻车的UPDATE写法列出来给大家避坑不带WHERE条件的UPDATE。比如直接UPDATE users SET status 1这会全表加锁并更新所有行。生产环境千万别这么干。WHERE条件里对索引列做运算或函数。比如WHERE id 1 100索引会失效。大范围IN条件。比如WHERE id IN (几万个id)锁的散列范围大执行计划可能退化成全表扫描。同一事务里更新大量行。内存、undo、锁、日志齐刷刷飙升最好拆分成小批量。UPDATE和SELECT混在同一个长事务里。因为MVCC的快照版本可能在undo log里累积导致undo膨胀甚至出现undo log too large问题。6. 不同数据库的UPDATE差异与踩坑记录很多团队不只是用MySQL。我在带项目的时候见过不少在Oracle里写习惯UPDATE的人切换到MySQL后各种不适。相反也有从MySQL转Oracle的。把这几个常用数据库的核心差异讲清楚会少踩不少坑。6.1 MySQL默认开启自动提交小心大事务MySQL默认是自动提交模式也就是说你执行一条不带显式事务的UPDATE它会立即提交。自动提交模式的好处是简单坏处是如果你在一个循环里发了1000个UPDATE实际上就是1000个小事务每个都要同步日志、释放锁和重新加锁性能很差。所以需要批量的数据更新一定要手动用事务包起来。还有一个MySQL特有的烦恼表结构变更时比如ALTER TABLE在某些版本里会触发全量copy阻塞写操作。如果你刚好在业务高峰期对一个几千万行的表执行ALTER那些UPDATE会被堵在MDL锁上。我在一次版本升级前做过一张大表的DDL用的工具是gh-ost这样可以不阻塞业务写避免了一次大事故。6.2 Oracle多表关联UPDATE的写法与回滚段Oracle支持多表关联更新写法是UPDATE (SELECT ...) SET ... WHERE ...或者用MERGE INTO。如果不小心写错关联条件可能出现“ORA-01779: cannot modify a column which maps to a non key-preserved table”这类错误意思是子查询或者视图中包含的行与主表不是一对一关系Oracle不允许直接更新。Oracle的UNDO机制跟MySQL类似但它使用回滚段管理旧数据。更要注意的是Oracle在UPDATE时对没有索引的关联更新很容易产生行迁移和行链接性能下降明显。因此做多表关联UPDATE之前我会先检查关联字段的索引和统计信息并且建议用MERGE替代复杂的旧式子查询更新语义更清晰执行计划也更稳定。6.3 PostgreSQL行版本制与HOT更新PostgreSQL的UPDATE实现和MySQL差异很大。PostgreSQL更新一行时实际上不会在原记录上就地修改而是生成一条新版本的行。旧版本继续保留直到VACUUM回收。这种方式天然支持了MVCC但代价是垃圾版本累积表膨胀。PostgreSQL里有一个专门为此设计的优化叫HOTHeap-Only Tuple更新如果更新不涉及被索引的列新版本可以放在同一个数据块里不需要额外维护索引项这样能明显降低UPDATE带来的索引写开销。如果涉及索引列的更新情况就麻烦了旧索引项和旧行版本要等VACUUM清理。所以PostgreSQL里频繁UPDATE的无用索引、监控和VACUUM策略非常重要。我在使用云数据库PostgreSQL时都会把autovacuum打开并为大表设置合理的vacuum_age阈值否则表会越涨越大查询性能直线下滑。7. 最后一个实用技巧UPDATE语句怎么写出高性能7.1 批量更新如何控制一次影响的行数有时候业务就是需要对几百万行做状态流转比如把一批过期订单标记为已关闭。一把梭直接UPDATE orders SET status closed WHERE expire_time NOW()很可能把数据库搞崩。正确做法是分批更新比如每次只更新1000行UPDATE orders SET status closed WHERE expire_time NOW() AND status closed LIMIT 1000;循环执行直到影响行数为0。这样做的好处是每次锁的范围小事务时长可控不会积累大量undo和binlog即使执行过程中出错也能在断点继续。注意LIMIT在UPDATE里配合排序时最好加上ORDER BY id保证分批是有序的不会重复处理或漏处理。7.2 注意索引失效场景像前文说的索引列上做函数运算会导致索引失效。除了函数还有隐式类型转换。比如字段是varchar类型WHERE条件里却传了数字WHERE phone 13800000000MySQL可能无法直接使用索引。我在排查慢查询时发现很多问题根源都是类型不一致而且因为语法不报错特别难发现。如果你用的是Oracle还要注意空字符串会被当作NULL处理在WHERE条件里用field 查不到数据进而影响更新范围。这类坑每个数据库都有关键是写完UPDATE以后看完影响行数再提交心里要有数这次更新影响100行还是100万行是否和预期一致。7.3 排序与limit在UPDATE中的应用MySQL的UPDATE支持ORDER BY和LIMIT这个能力在修复数据时非常有用。比如要删除并修正同一张表里重复数据的前几条可以先按时间排序只对前N条更新UPDATE task SET status processing WHERE status pending ORDER BY created_at ASC LIMIT 10;这种写法很适合实现简单的“队列领取”场景同一时刻多个消费者各自抢最新的待处理记录。但要注意这个UPDATE可能产生较宽的间隙锁范围。如果并发量很大用LIMIT抢任务会引发锁竞争更稳妥的做法是利用唯一索引直接定位每一条要抢的任务ID比如先SELECT出任务ID列表再逐条UPDATE。我在实际项目中还试过在UPDATE语句里加上“期望条件”来实现并发安全比如UPDATE task SET statusprocessing WHERE id10 AND statuspending。如果影响行数是1说明抢到影响行数是0说明已经被别人改过。这就是所谓的乐观锁思路能大幅降低死锁概率。最后再分享一个小技巧无论什么场景执行UPDATE之前先估算影响行数。可以用SELECT COUNT或EXPLAIN看扫描行数先心里有数再动手。要是线上数据量很大重点看一下WHERE条件里的字段是否有合适索引以及目标行会不会触发宽锁。养成这个习惯能帮你避开大多数UPDATE引发的“事故”。