一条 UPDATE 语句写下去MySQL 背后到底干了多少活很多同学写 SELECT 已经轻车熟路一碰到 UPDATE 就心里发虚为什么我明明建了索引还是慢为什么两条互不相关的 UPDATE 会互相锁住为什么 rows affected 显示 0业务日志里却显示改了一行这些问题往深了挖全都指向同一个方向——你对 UPDATE 在 MySQL 内部的完整执行链路了解得还不够细。我按线上排查经验把这条链路完整捋了一遍从一条 UPDATE 进入数据库开始连接、解析、优化、执行、加锁、写日志、提交一步不落再把锁、事务、MVCC、binlog 这些机制拆开讲透。适合刚接触 MySQL 的后端开发也适合正在维护生产库的 DBA。读完你至少能回答一条 UPDATE 怎么找到行、怎么锁行、怎么保证不丢数据又为什么会把自己搞这么慢。1. UPDATE语句的完整生命周期从连接建立到数据落盘1.1 第一站连接管理与SQL文本接收当你通过 JDBC、Navicat 或者 mysql 命令行执行一条 UPDATEMySQL 做的第一件事不是解析 SQL而是建立连接。服务端连接器负责校验用户名和密码检查客户端 IP 是否在授权范围内并从权限表里确认你是否有访问目标库的粗粒度权限。注意这里只是粗粒度校验细到列级别的权限要等到预处理或执行阶段才会真正生效。连接建立后这个会话会被分配一个线程线程会挂在线程池里复用。连接池的意义就在这里省掉 TCP 三次握手和权限校验的开销。生产环境别频繁新建连接否则 UPDATE 本身只要几毫秒握手反而占了大头。我见过一个项目用了最原始的直连方式每次请求都新建物理连接高峰期大部分时间都耗在握手和认证上。会话就绪后SQL 文本通过 MySQL 协议到达服务端。这里有个容易忽略的点UPDATE 和查询缓存完全无缘。在 MySQL 8.0 之前有查询缓存任何一条 UPDATE 都会让它所在表的所有缓存全部失效8.0 干脆移除了查询缓存。所以 UPDATE 从进入服务端那一刻起就注定了要完整走一遍解析、优化、执行的流程没有任何投机取巧的空间。1.2 解析与预处理SQL从字符串变成语法树服务端拿到 SQL 文本第一件事是解析。解析器Parser分两步走词法分析把文本拆成一个个 token比如 UPDATE、user、SET、status语法分析再按 MySQL 的语法规则把这些 token 组装成解析树。这一阶段如果出现语法错误比如把 SET 写成 SEET解析器会直接报错SQL 根本没有机会往下走。很多人不知道的是解析器只负责语法不负责语义。表名、字段名是否存在解析器完全不关心这些校验发生在预处理器Preprocessor阶段。所以当你写错表名时MySQL 报的是「Table xxx doesnt exist」并不是语法错误原因就在这。预处理器还会校验表和列是否存在、别名是否正确并做基本权限检查。对 UPDATE 来说预处理器还会确认目标表为后续加锁做好铺垫。这个阶段有两个实用细节。第一MySQL 8.0 引入数据词典后预处理器直接查数据词典速度比以前查 frm 文件更快也避免了文件系统和字典不一致的奇怪问题。第二如果你习惯用关键字做列名比如 order、desc 这类预处理阶段会因为歧义直接报错。所以写建表语句时给列名加反引号是个好习惯——你永远不知道 SQL 里会不会踩到保留字的坑。1.3 优化器决策更新行的“找法”如何确定解析和预处理之后MySQL 进入优化器阶段。这一步的核心不是“如何更新”而是“如何找到要更新的行”。找行的路径直接决定了加锁范围和扫描代价这往往是 UPDATE 性能的分水岭。优化器会分析 WHERE 条件里的每个列看哪些列有索引再根据索引的区分度Cardinality基数估算需要扫描多少行才能命中目标。举个例子WHERE phone 13800138000如果 phone 列有二级索引 idx_phone优化器大概率走索引通过 B 树快速定位二级索引叶子节点拿到主键后再回表读取完整行。但如果 WHERE 写的是 status 1而 status 只有 0 和 1 两个取值区分度太差优化器会认为返回的行数占全表的比例太高直接选择全表扫描可能更快。一个容易被忽略的点UPDATE 的执行计划不能只看“找行”成本还要算“改行”成本。如果被更新的列本身是二级索引列比如把 phone 从旧号改成新号InnoDB 除了要改聚簇索引里的数据行还要删除旧的二级索引条目、插入新的二级索引条目维护成本明显更高。这些代价优化器都会算进总成本里。回表的场景下优化器还可能选择索引条件下推ICP来减少回表次数这些动作会体现在 EXPLAIN 的 Extra 列中。1.4 执行器与InnoDB的落地动作优化器生成执行计划后执行器按计划逐项调用存储引擎接口。对 InnoDB 而言一次 UPDATE 的落地动作大致是这样执行器根据执行计划让 InnoDB 定位到第一条需要更新的行。InnoDB 对该行加排他锁X 锁如果行已被别的事务锁住则进入锁等待。加锁成功后InnoDB 先把该行的旧值写入 undo log用于事务回滚和 MVCC 快照读。更新目标行在 Buffer Pool 中的内存页如果页不在内存里需要先从磁盘读入内存再在内存中完成修改。与此同时把本次修改以追加写的方式写入 redo log buffer事务提交时再落盘。如果本次 UPDATE 修改了二级索引列InnoDB 还要同时维护二级索引旧条目标记删除新条目后续插入。服务器层确认所有行处理完毕把事务提交事件写入 binlog。最后通过两阶段提交机制让 redo log 与 binlog 达成一致事务正式提交释放所有锁。第 3、4、5 步连在一起就是大家常说的 WALWrite-Ahead Logging先写日志再改内存。崩溃恢复时已提交事务靠 redo log 重放找回未提交事务靠 undo log 回滚清掉。理解这个顺序后面很多 UPDATE 的性能问题就都能串起来了。2. 锁、事务与MVCCUPDATE并发安全的三驾马车2.1 为什么UPDATE一定要加锁当前读与并发控制先说一个最基础的问题UPDATE 为什么一定要加锁假设两个会话同时读到某一行 balance100都执行 balance balance - 50如果都不加锁最终结果可能是 50但两个事务各自扣一次正确结果应该是 0。这就是丢失更新。所以 UPDATE 必须采用当前读读取行的最新版本同时对这行加排他锁禁止其他事务并发修改。理解“当前读”和“快照读”的差异是理解 UPDATE 的一把钥匙。普通 SELECT 是快照读不加锁MVCC 机制让它在 REPEATABLE READ 隔离级别下读到一致性快照其他事务改了行也能读到旧版本。而 UPDATE、DELETE、INSERT、SELECT ... FOR UPDATE 都属于当前读读的是最新版本并且必须加锁。这也解释了一个经典困惑同一事务里先 SELECT 再 UPDATE为什么可能读出不一样的数据因为 SELECT 走快照UPDATE 走当前读。一个常见幻觉是“我的 WHERE 条件命中唯一索引只影响一行肯定不会锁住别的行”。在 REPEATABLE READ 下没这么简单继续看锁粒度。2.2 行锁和间隙锁RR隔离级别下UPDATE的锁范围InnoDB 默认隔离级别是 REPEATABLE READ。在这个级别下UPDATE 加的通常不是单纯的行锁而是行锁与间隙锁配合形成的临键锁Next-Key Lock。举个例子表里有 id1、2、3 三条记录执行 UPDATE ... WHERE id 2InnoDB 会锁住 id3 这条记录的行锁同时锁住 id3 到正无穷的间隙锁。间隙锁锁的不是具体行而是“这个区间内不允许插入新记录”目的就是防止其他事务插入 id4造成当前事务出现幻读。如果 WHERE 条件命中唯一索引的等值匹配比如 WHERE id2InnoDB 会退化为只加记录锁Record Lock因为唯一索引本身就能防止幻读。但如果是二级索引上的等值条件即使理论上只会命中一行InnoDB 也倾向于加临键锁因为二级索引不是唯一的不能完全消除幻读风险。更麻烦的是全表扫描的 UPDATE没有可用索引时InnoDB 会对扫描到的每一行都加锁实际上是整张表所有行和所有间隙都被锁住了。这种“隐形表锁”往往就是线上那些 UPDATE 卡停的元凶。我在排查时见过一个非常隐蔽的案例一条 UPDATE 只更新了 100 行却把整个表的 INSERT 都堵住了。最后定位发现 WHERE 条件用的二级索引过滤度太低优化器虽然走了索引但命中的范围太大临键锁覆盖了一大段区间。解决办法是把 SQL 拆窄或者让条件走主键用小范围查询替代宽范围查询。2.3 长事务与死锁锁的持有与释放锁不是 UPDATE 执行完就立刻释放的。在 InnoDB 中UPDATE 加上的锁会一直持有到事务提交或回滚。这是事务原子性的必然要求如果更新完就放锁事务中途回滚时别的会话可能已经把基于新值的数据读走了。所以同一个事务里如果先 UPDATE再跑一堆其他 SQL或者应用代码迟迟不提交这些行锁会被一直攥在手里其他线程的 UPDATE 只能干等直到报 1205 锁等待超时。死锁则是两个事务互相持有对方需要的锁。经典场景事务 A 先更新 id1事务 B 先更新 id2接着 A 去更新 id2 被 B 阻塞B 去更新 id1 被 A 阻塞双方都在等形成环。InnoDB 有死锁检测机制会周期性扫描等待图发现环后选一个回滚代价较小的事务回滚另一个事务则报 ERROR 1213 Deadlock found。所以从业务视角看死锁偶尔发生是正常的关键是让 SQL 访问表和行的顺序尽量固定事务时间尽量短把死锁概率压到最低。2.4 MVCC回滚段与undo log更新前的旧数据去了哪每次 UPDATE 都会把旧值写进 undo log那这些旧值到底用来干什么两个用途一个是事务回滚一个是 MVCC 快照读。先看回滚。如果事务里 UPDATE 了 10 行然后执行 ROLLBACKInnoDB 不需要“反向执行 UPDATE”它直接读 undo log 中保存的旧记录把行恢复到修改前的状态。这比反向执行 SQL 可靠得多也是因为 undo log 的存在一个事务的修改对另一个未提交前完全不可见。再看 MVCC。REPEATABLE READ 下事务第一次执行快照读时会生成一个 Read View。如果另一个事务的 UPDATE 尚未提交它的新版本对其他事务不可见其他事务要读旧值就得顺着 undo log 的版本链往回找直到找到符合 Read View 的版本。这就是为什么长事务会让 undo log 越拖越长旧事务还可能读取很久之前的版本导致 undo log 无法被 purge 线程清理Undo 表空间持续膨胀最终拖慢 UPDATE 效率。这条链路解释了第二章节的核心UPDATE 当场写新版本旧版本留在 undo 里靠着这条版本链其他读请求才能拿到一致性快照。3. 记录真实的执行过程EXPLAIN与状态变量透视3.1 EXPLAIN UPDATE怎么读我排查线上 UPDATE 慢 SQL第一步永远是 EXPLAIN。MySQL 从 5.6.3 开始才支持 EXPLAIN UPDATE之前只能先改写 SELECT 再分析非常痛苦8.0 里还可以用 EXPLAIN ANALYZE 直接看执行耗时和实际行数。用一个实际表说明CREATE TABLE user ( id INT NOT NULL AUTO_INCREMENT, phone VARCHAR(20) DEFAULT NULL, status TINYINT NOT NULL DEFAULT 0, balance DECIMAL(12,2) NOT NULL DEFAULT 0.00, update_time DATETIME DEFAULT NULL, PRIMARY KEY (id), KEY idx_phone (phone) ) ENGINEInnoDB;EXPLAIN UPDATE user SET status 1 WHERE phone 13800138000;执行计划里type 列是 refkey 列是 idx_phonerows 大约为 1。这说明 MySQL 打算先通过 idx_phone 在二级索引中定位然后回表读取对应的主键行再对这行执行更新。如果 EXPLAIN 显示 typeALLkeyNULLrows 接近全表行数那意味着 MySQL 准备从头到尾扫全表对每一行都加锁再判断是否要更新这种 UPDATE 的代价和风险都是灾难级的。要特别强调EXPLAIN 展示的只是“找行”计划不是“更新”计划。真正执行时InnoDB 还会因为二级索引变更、间隙锁等产生额外代价EXPLAIN 里看不到。所以 EXPLAIN rows 很小不代表 UPDATE 就一定快还得结合锁范围一起判断。3.2 rows affected的两套语义匹配行数还是变更行数执行 UPDATE 后mysql 命令行会返回类似信息Query OK, 2 rows affected (0.00 sec)这个 2 到底代表什么是个巨大的坑。MySQL 返回的 rows affected 是“匹配到的行数”还是“实际发生变更的行数”取决于客户端是否设置了 CLIENT_FOUND_ROWS 标志位。mysql 命令行客户端默认不设置这个标志所以显示的是实际发生变更的行数你把 status 从 1 改成 1即使 WHERE 匹配了 10 行也返回 0 rows affected。但 JDBC 默认的 useAffectedRowsfalse驱动会设置 CLIENT_FOUND_ROWS 标志executeUpdate() 返回的是匹配行数。于是同一条 UPDATEMySQL 命令行显示 0 rows affectedJava 代码里却返回 1两边对不上很多人排查半天才发现是客户端标志位的差异。MySQL 8.0.30 之后命令行执行 UPDATE 会直接打印 Rows matched、Changed、Warnings 三行信息再也不用靠猜。3.3 定位锁等待innodb_trx与SHOW ENGINE INNODB STATUS当 UPDATE 被锁阻塞应用要么卡住不动要么直接报 1205。定位阻塞链路我自己常用的三步先看当前事务SELECT * FROM information_schema.innodb_trx;重点看 trx_state、trx_started、trx_wait_started找到长时间未提交的事务。再看锁等待关系SELECT * FROM performance_schema.data_lock_waits;8.0 用 data_lock_waits5.7 里对应的是 innodb_lock_waits 视图。这里能看出谁在等谁。最后用SHOW ENGINE INNODB STATUS看 LATEST DETECTED DEADLOCK 段死锁现场会把两个事务各持有什么锁、等待什么锁、执行的 SQL 都列出来排查时几乎所有信息都在里面。实操心得是锁等待问题十有八九不是当前 SQL 的问题而是上游还有一条更早的未提交事务。先把长时间运行的事务揪出来确认它为什么没提交比反复调 UPDATE 本身有效得多。4. UPDATE性能优化与批量更新实践4.1 让定位更精准索引与WHERE条件的三个前提UPDATE 慢绝大多数原因是“定位慢”优化核心和 SELECT 完全一致让 WHERE 条件尽量走索引并且是选择性高的索引。我总结成三个前提。第一WHERE 条件列上必须有索引且类型匹配。phone 列是 VARCHAR查询却写成 phone 13800138000MySQL 会对列做隐式类型转换索引因此失效扫描从 B 树定位退化成全表扫描。第二不要对索引列使用函数。WHERE DATE(update_time) 2024-01-01 这种写法update_time 有索引也用不上改成 update_time 2024-01-01 AND update_time 2024-01-02 才能命中。第三优化器认为“用这个索引还不如全表扫描快”时即使有索引也不会用典型就是性别、状态这类低基数列。碰到这种情况要么改业务需求比如分批更新要么强制索引但强制索引不是银弹要谨慎。4.2 避免大事务批量更新的拆解方法生产环境中我踩过最大的坑就是一次性 UPDATE 几十万行。一条 SQL 更新 50 万行表面上看很快但 InnoDB 要为每行加锁、写 undo事务长时间持有大量行锁期间任何相关写操作都被堵住undo 还会膨胀回滚段扛不住主从架构下binlog 传到从库后从库回放这条大事务一样慢主从延迟瞬间拉高。正确姿势是拆批。假设要更新 user 表里 status0 的 100 万行按主键范围切成多个小事务-- 每批处理 5 万行循环执行 UPDATE user SET status 1 WHERE status 0 AND id BETWEEN 1 AND 50000;或者用 LIMIT 分批UPDATE user SET status 1 WHERE status 0 AND id IN (SELECT id FROM (SELECT id FROM user WHERE status 0 ORDER BY id LIMIT 5000) t);注意这里 IN 子查询必须套一层 SELECT做成临时表否则 MySQL 会报「You cant specify target table for update in FROM clause」——这是 UPDATE 不能直接在同一张表的子查询中更新的著名限制。分批间隔可以用应用层 sleep 控制也可以用存储过程循环。核心指标是单批 UPDATE 耗时控制在几百毫秒内锁持有时间短对其他事务影响小主从延迟也容易追平。4.3 子查询与关联更新的正确写法更新一张表又带业务逻辑时很多人习惯写UPDATE order_info oi SET oi.total_amount ( SELECT SUM(amount) FROM order_item oi2 WHERE oi2.order_no oi.order_no ) WHERE ...这种写法隐患很多。子查询可能返回 NULL直接覆盖业务数据如果目标列有非空约束SQL 直接报错关联子查询还要对 order_item 反复扫描性能很差。MySQL 8.0.19 之前优化器对关联子查询的处理也不算聪明。更好的写法是用 JOINUPDATE order_info oi JOIN ( SELECT order_no, SUM(amount) AS total_amount FROM order_item GROUP BY order_no ) t ON t.order_no oi.order_no SET oi.total_amount t.total_amount WHERE oi.total_amount IS NULL;网上有个说法叫“UPDATE 建议添加 EXISTS 子句避免空值更新”本质就是这个意思用 JOIN 或 EXISTS 把关联条件写进去只有确实有明细数据的订单才被更新避免子查询返回 NULL 把正常数据覆盖掉。我见过真实事故一条 UPDATE 把主表所有订单的金额都改成了 NULL就是因为子查询对部分订单没有匹配项返回了 NULL而 WHERE 又没有加任何过滤。加一层“关联存在”的条件损失就能限定在有效数据范围内。5. 排障实录UPDATE相关的典型问题与处理经验5.1 “明明条件有索引还是全表更新”索引失效排查现象是 EXPLAIN 显示 typeALLrows 好几十万。排查顺序我一般固定成四步先确认索引是否存在再看 WHERE 条件列有没有隐式类型转换比如 phone 13800138000、id 123再看有没有函数包裹索引列最后看统计信息是否过期用ANALYZE TABLE user;刷新基数统计。我遇到最离谱的一个案例是开发给查询字段加了 COLLATE 指定排序规则导致索引匹配路径改变优化器直接放弃索引。这类问题用 EXPLAIN 一眼就能看出端倪难的是 SQL 往往已经上线运行了很久没人会主动执行 EXPLAIN。5.2 锁等待超时ERROR 1205处理步骤ERROR 1205: Lock wait timeout exceeded处理路径相对固定。第一步查information_schema.innodb_trx确认哪个事务持有锁重点看 trx_started超过几秒甚至几分钟就有问题。第二步查performance_schema.data_lock_waits或sys.innodb_lock_waits视图定位阻塞源头然后根据情况终止事务KILL thread_id。这里有个操作细节KILL 的是连接的 thread id不是 trx id要先用SELECT trx_mysql_thread_id FROM information_schema.innodb_trx拿到正确线程 ID 再 KILL直接 KILL trx id 不会生效。KILL 之后还要观察 Binlog 和连接池的状态因为被终止事务可能会在应用层触发重试重试风暴有时候比原问题更麻烦。5.3 更新后主从延迟大事务在备库的回放主库执行一条 UPDATE 只要 2 秒从库却延迟了 10 分钟这类问题通常跟两个因素相关一条 UPDATE 更新了大量行产生的 binlog 事件非常庞大或者从库自身的同步线程被其他长查询阻塞。排查时先看SHOW SLAVE STATUS\G里的 Seconds_Behind_Master再结合 relay log 的读取位置判断事件进度。解决方案分两头从库侧可以开并行复制比如 8.0 里调整 replica_parallel_workers 加速回放但根子还在主库必须控制大事务的规模。很多团队把拆分 UPDATE 作为上线铁律原因就在这。这里我不建议只在从库调参那只治标不治本。5.4 误更新全表sql_safe_updates与变更规范最后说一个所有开发都应该知道的保命设置在 MySQL 会话里执行SET sql_safe_updates 1之后不带 WHERE 或 WHERE 不带索引的 UPDATE、DELETE 会被直接拒绝提示「You are using safe update mode」。开发和测试环境我建议默认开着生产环境的变更脚本统一走审核平台尽量避免直连生产库执行 UPDATE。真遇到需要全表更新的场景也要写出明确的范围条件比如 WHERE id 0让审核的人一眼看出你的真实意图而不是盯着一个光秃秃的 UPDATE 提心跳胆。另外排查 UPDATE 问题时常遇到“业务方坚称只执行了一条 UPDATE”实际代码里循环发了几千条小 UPDATE这种问题只能靠日志还原。开启通用查询日志或用 performance_schema 的 events_statements_history把当时的完整 SQL 抓出来现场就不会变成罗生门。最后分享一个我坚持了很多年的习惯任何 UPDATE 上线前先把它改写成 SELECT 跑一遍用 SELECT 确认影响行数和结果集再执行更新更新完立刻用 SELECT 验证。这套流程看着笨但大部分在线事故都是因为“以为只影响几行”才发生的。UPDATE 这条链路不长每一环都值得认真对待。
MySQL UPDATE执行全链路：从加锁到日志的底层原理与优化
一条 UPDATE 语句写下去MySQL 背后到底干了多少活很多同学写 SELECT 已经轻车熟路一碰到 UPDATE 就心里发虚为什么我明明建了索引还是慢为什么两条互不相关的 UPDATE 会互相锁住为什么 rows affected 显示 0业务日志里却显示改了一行这些问题往深了挖全都指向同一个方向——你对 UPDATE 在 MySQL 内部的完整执行链路了解得还不够细。我按线上排查经验把这条链路完整捋了一遍从一条 UPDATE 进入数据库开始连接、解析、优化、执行、加锁、写日志、提交一步不落再把锁、事务、MVCC、binlog 这些机制拆开讲透。适合刚接触 MySQL 的后端开发也适合正在维护生产库的 DBA。读完你至少能回答一条 UPDATE 怎么找到行、怎么锁行、怎么保证不丢数据又为什么会把自己搞这么慢。1. UPDATE语句的完整生命周期从连接建立到数据落盘1.1 第一站连接管理与SQL文本接收当你通过 JDBC、Navicat 或者 mysql 命令行执行一条 UPDATEMySQL 做的第一件事不是解析 SQL而是建立连接。服务端连接器负责校验用户名和密码检查客户端 IP 是否在授权范围内并从权限表里确认你是否有访问目标库的粗粒度权限。注意这里只是粗粒度校验细到列级别的权限要等到预处理或执行阶段才会真正生效。连接建立后这个会话会被分配一个线程线程会挂在线程池里复用。连接池的意义就在这里省掉 TCP 三次握手和权限校验的开销。生产环境别频繁新建连接否则 UPDATE 本身只要几毫秒握手反而占了大头。我见过一个项目用了最原始的直连方式每次请求都新建物理连接高峰期大部分时间都耗在握手和认证上。会话就绪后SQL 文本通过 MySQL 协议到达服务端。这里有个容易忽略的点UPDATE 和查询缓存完全无缘。在 MySQL 8.0 之前有查询缓存任何一条 UPDATE 都会让它所在表的所有缓存全部失效8.0 干脆移除了查询缓存。所以 UPDATE 从进入服务端那一刻起就注定了要完整走一遍解析、优化、执行的流程没有任何投机取巧的空间。1.2 解析与预处理SQL从字符串变成语法树服务端拿到 SQL 文本第一件事是解析。解析器Parser分两步走词法分析把文本拆成一个个 token比如 UPDATE、user、SET、status语法分析再按 MySQL 的语法规则把这些 token 组装成解析树。这一阶段如果出现语法错误比如把 SET 写成 SEET解析器会直接报错SQL 根本没有机会往下走。很多人不知道的是解析器只负责语法不负责语义。表名、字段名是否存在解析器完全不关心这些校验发生在预处理器Preprocessor阶段。所以当你写错表名时MySQL 报的是「Table xxx doesnt exist」并不是语法错误原因就在这。预处理器还会校验表和列是否存在、别名是否正确并做基本权限检查。对 UPDATE 来说预处理器还会确认目标表为后续加锁做好铺垫。这个阶段有两个实用细节。第一MySQL 8.0 引入数据词典后预处理器直接查数据词典速度比以前查 frm 文件更快也避免了文件系统和字典不一致的奇怪问题。第二如果你习惯用关键字做列名比如 order、desc 这类预处理阶段会因为歧义直接报错。所以写建表语句时给列名加反引号是个好习惯——你永远不知道 SQL 里会不会踩到保留字的坑。1.3 优化器决策更新行的“找法”如何确定解析和预处理之后MySQL 进入优化器阶段。这一步的核心不是“如何更新”而是“如何找到要更新的行”。找行的路径直接决定了加锁范围和扫描代价这往往是 UPDATE 性能的分水岭。优化器会分析 WHERE 条件里的每个列看哪些列有索引再根据索引的区分度Cardinality基数估算需要扫描多少行才能命中目标。举个例子WHERE phone 13800138000如果 phone 列有二级索引 idx_phone优化器大概率走索引通过 B 树快速定位二级索引叶子节点拿到主键后再回表读取完整行。但如果 WHERE 写的是 status 1而 status 只有 0 和 1 两个取值区分度太差优化器会认为返回的行数占全表的比例太高直接选择全表扫描可能更快。一个容易被忽略的点UPDATE 的执行计划不能只看“找行”成本还要算“改行”成本。如果被更新的列本身是二级索引列比如把 phone 从旧号改成新号InnoDB 除了要改聚簇索引里的数据行还要删除旧的二级索引条目、插入新的二级索引条目维护成本明显更高。这些代价优化器都会算进总成本里。回表的场景下优化器还可能选择索引条件下推ICP来减少回表次数这些动作会体现在 EXPLAIN 的 Extra 列中。1.4 执行器与InnoDB的落地动作优化器生成执行计划后执行器按计划逐项调用存储引擎接口。对 InnoDB 而言一次 UPDATE 的落地动作大致是这样执行器根据执行计划让 InnoDB 定位到第一条需要更新的行。InnoDB 对该行加排他锁X 锁如果行已被别的事务锁住则进入锁等待。加锁成功后InnoDB 先把该行的旧值写入 undo log用于事务回滚和 MVCC 快照读。更新目标行在 Buffer Pool 中的内存页如果页不在内存里需要先从磁盘读入内存再在内存中完成修改。与此同时把本次修改以追加写的方式写入 redo log buffer事务提交时再落盘。如果本次 UPDATE 修改了二级索引列InnoDB 还要同时维护二级索引旧条目标记删除新条目后续插入。服务器层确认所有行处理完毕把事务提交事件写入 binlog。最后通过两阶段提交机制让 redo log 与 binlog 达成一致事务正式提交释放所有锁。第 3、4、5 步连在一起就是大家常说的 WALWrite-Ahead Logging先写日志再改内存。崩溃恢复时已提交事务靠 redo log 重放找回未提交事务靠 undo log 回滚清掉。理解这个顺序后面很多 UPDATE 的性能问题就都能串起来了。2. 锁、事务与MVCCUPDATE并发安全的三驾马车2.1 为什么UPDATE一定要加锁当前读与并发控制先说一个最基础的问题UPDATE 为什么一定要加锁假设两个会话同时读到某一行 balance100都执行 balance balance - 50如果都不加锁最终结果可能是 50但两个事务各自扣一次正确结果应该是 0。这就是丢失更新。所以 UPDATE 必须采用当前读读取行的最新版本同时对这行加排他锁禁止其他事务并发修改。理解“当前读”和“快照读”的差异是理解 UPDATE 的一把钥匙。普通 SELECT 是快照读不加锁MVCC 机制让它在 REPEATABLE READ 隔离级别下读到一致性快照其他事务改了行也能读到旧版本。而 UPDATE、DELETE、INSERT、SELECT ... FOR UPDATE 都属于当前读读的是最新版本并且必须加锁。这也解释了一个经典困惑同一事务里先 SELECT 再 UPDATE为什么可能读出不一样的数据因为 SELECT 走快照UPDATE 走当前读。一个常见幻觉是“我的 WHERE 条件命中唯一索引只影响一行肯定不会锁住别的行”。在 REPEATABLE READ 下没这么简单继续看锁粒度。2.2 行锁和间隙锁RR隔离级别下UPDATE的锁范围InnoDB 默认隔离级别是 REPEATABLE READ。在这个级别下UPDATE 加的通常不是单纯的行锁而是行锁与间隙锁配合形成的临键锁Next-Key Lock。举个例子表里有 id1、2、3 三条记录执行 UPDATE ... WHERE id 2InnoDB 会锁住 id3 这条记录的行锁同时锁住 id3 到正无穷的间隙锁。间隙锁锁的不是具体行而是“这个区间内不允许插入新记录”目的就是防止其他事务插入 id4造成当前事务出现幻读。如果 WHERE 条件命中唯一索引的等值匹配比如 WHERE id2InnoDB 会退化为只加记录锁Record Lock因为唯一索引本身就能防止幻读。但如果是二级索引上的等值条件即使理论上只会命中一行InnoDB 也倾向于加临键锁因为二级索引不是唯一的不能完全消除幻读风险。更麻烦的是全表扫描的 UPDATE没有可用索引时InnoDB 会对扫描到的每一行都加锁实际上是整张表所有行和所有间隙都被锁住了。这种“隐形表锁”往往就是线上那些 UPDATE 卡停的元凶。我在排查时见过一个非常隐蔽的案例一条 UPDATE 只更新了 100 行却把整个表的 INSERT 都堵住了。最后定位发现 WHERE 条件用的二级索引过滤度太低优化器虽然走了索引但命中的范围太大临键锁覆盖了一大段区间。解决办法是把 SQL 拆窄或者让条件走主键用小范围查询替代宽范围查询。2.3 长事务与死锁锁的持有与释放锁不是 UPDATE 执行完就立刻释放的。在 InnoDB 中UPDATE 加上的锁会一直持有到事务提交或回滚。这是事务原子性的必然要求如果更新完就放锁事务中途回滚时别的会话可能已经把基于新值的数据读走了。所以同一个事务里如果先 UPDATE再跑一堆其他 SQL或者应用代码迟迟不提交这些行锁会被一直攥在手里其他线程的 UPDATE 只能干等直到报 1205 锁等待超时。死锁则是两个事务互相持有对方需要的锁。经典场景事务 A 先更新 id1事务 B 先更新 id2接着 A 去更新 id2 被 B 阻塞B 去更新 id1 被 A 阻塞双方都在等形成环。InnoDB 有死锁检测机制会周期性扫描等待图发现环后选一个回滚代价较小的事务回滚另一个事务则报 ERROR 1213 Deadlock found。所以从业务视角看死锁偶尔发生是正常的关键是让 SQL 访问表和行的顺序尽量固定事务时间尽量短把死锁概率压到最低。2.4 MVCC回滚段与undo log更新前的旧数据去了哪每次 UPDATE 都会把旧值写进 undo log那这些旧值到底用来干什么两个用途一个是事务回滚一个是 MVCC 快照读。先看回滚。如果事务里 UPDATE 了 10 行然后执行 ROLLBACKInnoDB 不需要“反向执行 UPDATE”它直接读 undo log 中保存的旧记录把行恢复到修改前的状态。这比反向执行 SQL 可靠得多也是因为 undo log 的存在一个事务的修改对另一个未提交前完全不可见。再看 MVCC。REPEATABLE READ 下事务第一次执行快照读时会生成一个 Read View。如果另一个事务的 UPDATE 尚未提交它的新版本对其他事务不可见其他事务要读旧值就得顺着 undo log 的版本链往回找直到找到符合 Read View 的版本。这就是为什么长事务会让 undo log 越拖越长旧事务还可能读取很久之前的版本导致 undo log 无法被 purge 线程清理Undo 表空间持续膨胀最终拖慢 UPDATE 效率。这条链路解释了第二章节的核心UPDATE 当场写新版本旧版本留在 undo 里靠着这条版本链其他读请求才能拿到一致性快照。3. 记录真实的执行过程EXPLAIN与状态变量透视3.1 EXPLAIN UPDATE怎么读我排查线上 UPDATE 慢 SQL第一步永远是 EXPLAIN。MySQL 从 5.6.3 开始才支持 EXPLAIN UPDATE之前只能先改写 SELECT 再分析非常痛苦8.0 里还可以用 EXPLAIN ANALYZE 直接看执行耗时和实际行数。用一个实际表说明CREATE TABLE user ( id INT NOT NULL AUTO_INCREMENT, phone VARCHAR(20) DEFAULT NULL, status TINYINT NOT NULL DEFAULT 0, balance DECIMAL(12,2) NOT NULL DEFAULT 0.00, update_time DATETIME DEFAULT NULL, PRIMARY KEY (id), KEY idx_phone (phone) ) ENGINEInnoDB;EXPLAIN UPDATE user SET status 1 WHERE phone 13800138000;执行计划里type 列是 refkey 列是 idx_phonerows 大约为 1。这说明 MySQL 打算先通过 idx_phone 在二级索引中定位然后回表读取对应的主键行再对这行执行更新。如果 EXPLAIN 显示 typeALLkeyNULLrows 接近全表行数那意味着 MySQL 准备从头到尾扫全表对每一行都加锁再判断是否要更新这种 UPDATE 的代价和风险都是灾难级的。要特别强调EXPLAIN 展示的只是“找行”计划不是“更新”计划。真正执行时InnoDB 还会因为二级索引变更、间隙锁等产生额外代价EXPLAIN 里看不到。所以 EXPLAIN rows 很小不代表 UPDATE 就一定快还得结合锁范围一起判断。3.2 rows affected的两套语义匹配行数还是变更行数执行 UPDATE 后mysql 命令行会返回类似信息Query OK, 2 rows affected (0.00 sec)这个 2 到底代表什么是个巨大的坑。MySQL 返回的 rows affected 是“匹配到的行数”还是“实际发生变更的行数”取决于客户端是否设置了 CLIENT_FOUND_ROWS 标志位。mysql 命令行客户端默认不设置这个标志所以显示的是实际发生变更的行数你把 status 从 1 改成 1即使 WHERE 匹配了 10 行也返回 0 rows affected。但 JDBC 默认的 useAffectedRowsfalse驱动会设置 CLIENT_FOUND_ROWS 标志executeUpdate() 返回的是匹配行数。于是同一条 UPDATEMySQL 命令行显示 0 rows affectedJava 代码里却返回 1两边对不上很多人排查半天才发现是客户端标志位的差异。MySQL 8.0.30 之后命令行执行 UPDATE 会直接打印 Rows matched、Changed、Warnings 三行信息再也不用靠猜。3.3 定位锁等待innodb_trx与SHOW ENGINE INNODB STATUS当 UPDATE 被锁阻塞应用要么卡住不动要么直接报 1205。定位阻塞链路我自己常用的三步先看当前事务SELECT * FROM information_schema.innodb_trx;重点看 trx_state、trx_started、trx_wait_started找到长时间未提交的事务。再看锁等待关系SELECT * FROM performance_schema.data_lock_waits;8.0 用 data_lock_waits5.7 里对应的是 innodb_lock_waits 视图。这里能看出谁在等谁。最后用SHOW ENGINE INNODB STATUS看 LATEST DETECTED DEADLOCK 段死锁现场会把两个事务各持有什么锁、等待什么锁、执行的 SQL 都列出来排查时几乎所有信息都在里面。实操心得是锁等待问题十有八九不是当前 SQL 的问题而是上游还有一条更早的未提交事务。先把长时间运行的事务揪出来确认它为什么没提交比反复调 UPDATE 本身有效得多。4. UPDATE性能优化与批量更新实践4.1 让定位更精准索引与WHERE条件的三个前提UPDATE 慢绝大多数原因是“定位慢”优化核心和 SELECT 完全一致让 WHERE 条件尽量走索引并且是选择性高的索引。我总结成三个前提。第一WHERE 条件列上必须有索引且类型匹配。phone 列是 VARCHAR查询却写成 phone 13800138000MySQL 会对列做隐式类型转换索引因此失效扫描从 B 树定位退化成全表扫描。第二不要对索引列使用函数。WHERE DATE(update_time) 2024-01-01 这种写法update_time 有索引也用不上改成 update_time 2024-01-01 AND update_time 2024-01-02 才能命中。第三优化器认为“用这个索引还不如全表扫描快”时即使有索引也不会用典型就是性别、状态这类低基数列。碰到这种情况要么改业务需求比如分批更新要么强制索引但强制索引不是银弹要谨慎。4.2 避免大事务批量更新的拆解方法生产环境中我踩过最大的坑就是一次性 UPDATE 几十万行。一条 SQL 更新 50 万行表面上看很快但 InnoDB 要为每行加锁、写 undo事务长时间持有大量行锁期间任何相关写操作都被堵住undo 还会膨胀回滚段扛不住主从架构下binlog 传到从库后从库回放这条大事务一样慢主从延迟瞬间拉高。正确姿势是拆批。假设要更新 user 表里 status0 的 100 万行按主键范围切成多个小事务-- 每批处理 5 万行循环执行 UPDATE user SET status 1 WHERE status 0 AND id BETWEEN 1 AND 50000;或者用 LIMIT 分批UPDATE user SET status 1 WHERE status 0 AND id IN (SELECT id FROM (SELECT id FROM user WHERE status 0 ORDER BY id LIMIT 5000) t);注意这里 IN 子查询必须套一层 SELECT做成临时表否则 MySQL 会报「You cant specify target table for update in FROM clause」——这是 UPDATE 不能直接在同一张表的子查询中更新的著名限制。分批间隔可以用应用层 sleep 控制也可以用存储过程循环。核心指标是单批 UPDATE 耗时控制在几百毫秒内锁持有时间短对其他事务影响小主从延迟也容易追平。4.3 子查询与关联更新的正确写法更新一张表又带业务逻辑时很多人习惯写UPDATE order_info oi SET oi.total_amount ( SELECT SUM(amount) FROM order_item oi2 WHERE oi2.order_no oi.order_no ) WHERE ...这种写法隐患很多。子查询可能返回 NULL直接覆盖业务数据如果目标列有非空约束SQL 直接报错关联子查询还要对 order_item 反复扫描性能很差。MySQL 8.0.19 之前优化器对关联子查询的处理也不算聪明。更好的写法是用 JOINUPDATE order_info oi JOIN ( SELECT order_no, SUM(amount) AS total_amount FROM order_item GROUP BY order_no ) t ON t.order_no oi.order_no SET oi.total_amount t.total_amount WHERE oi.total_amount IS NULL;网上有个说法叫“UPDATE 建议添加 EXISTS 子句避免空值更新”本质就是这个意思用 JOIN 或 EXISTS 把关联条件写进去只有确实有明细数据的订单才被更新避免子查询返回 NULL 把正常数据覆盖掉。我见过真实事故一条 UPDATE 把主表所有订单的金额都改成了 NULL就是因为子查询对部分订单没有匹配项返回了 NULL而 WHERE 又没有加任何过滤。加一层“关联存在”的条件损失就能限定在有效数据范围内。5. 排障实录UPDATE相关的典型问题与处理经验5.1 “明明条件有索引还是全表更新”索引失效排查现象是 EXPLAIN 显示 typeALLrows 好几十万。排查顺序我一般固定成四步先确认索引是否存在再看 WHERE 条件列有没有隐式类型转换比如 phone 13800138000、id 123再看有没有函数包裹索引列最后看统计信息是否过期用ANALYZE TABLE user;刷新基数统计。我遇到最离谱的一个案例是开发给查询字段加了 COLLATE 指定排序规则导致索引匹配路径改变优化器直接放弃索引。这类问题用 EXPLAIN 一眼就能看出端倪难的是 SQL 往往已经上线运行了很久没人会主动执行 EXPLAIN。5.2 锁等待超时ERROR 1205处理步骤ERROR 1205: Lock wait timeout exceeded处理路径相对固定。第一步查information_schema.innodb_trx确认哪个事务持有锁重点看 trx_started超过几秒甚至几分钟就有问题。第二步查performance_schema.data_lock_waits或sys.innodb_lock_waits视图定位阻塞源头然后根据情况终止事务KILL thread_id。这里有个操作细节KILL 的是连接的 thread id不是 trx id要先用SELECT trx_mysql_thread_id FROM information_schema.innodb_trx拿到正确线程 ID 再 KILL直接 KILL trx id 不会生效。KILL 之后还要观察 Binlog 和连接池的状态因为被终止事务可能会在应用层触发重试重试风暴有时候比原问题更麻烦。5.3 更新后主从延迟大事务在备库的回放主库执行一条 UPDATE 只要 2 秒从库却延迟了 10 分钟这类问题通常跟两个因素相关一条 UPDATE 更新了大量行产生的 binlog 事件非常庞大或者从库自身的同步线程被其他长查询阻塞。排查时先看SHOW SLAVE STATUS\G里的 Seconds_Behind_Master再结合 relay log 的读取位置判断事件进度。解决方案分两头从库侧可以开并行复制比如 8.0 里调整 replica_parallel_workers 加速回放但根子还在主库必须控制大事务的规模。很多团队把拆分 UPDATE 作为上线铁律原因就在这。这里我不建议只在从库调参那只治标不治本。5.4 误更新全表sql_safe_updates与变更规范最后说一个所有开发都应该知道的保命设置在 MySQL 会话里执行SET sql_safe_updates 1之后不带 WHERE 或 WHERE 不带索引的 UPDATE、DELETE 会被直接拒绝提示「You are using safe update mode」。开发和测试环境我建议默认开着生产环境的变更脚本统一走审核平台尽量避免直连生产库执行 UPDATE。真遇到需要全表更新的场景也要写出明确的范围条件比如 WHERE id 0让审核的人一眼看出你的真实意图而不是盯着一个光秃秃的 UPDATE 提心跳胆。另外排查 UPDATE 问题时常遇到“业务方坚称只执行了一条 UPDATE”实际代码里循环发了几千条小 UPDATE这种问题只能靠日志还原。开启通用查询日志或用 performance_schema 的 events_statements_history把当时的完整 SQL 抓出来现场就不会变成罗生门。最后分享一个我坚持了很多年的习惯任何 UPDATE 上线前先把它改写成 SELECT 跑一遍用 SELECT 确认影响行数和结果集再执行更新更新完立刻用 SELECT 验证。这套流程看着笨但大部分在线事故都是因为“以为只影响几行”才发生的。UPDATE 这条链路不长每一环都值得认真对待。
