一条 update 语句的生命历程

Bertha 。 2022-12-21 06:10 62阅读 0赞

在有关 MySQL 的面试时，是不是曾经有被问到过：一条 update 语句的生命历程是怎样的？今天就来聊聊这一面试题。

这篇文章通过这条语句进行讲解：

update test set a=5 where id = 10;

一条 SQL 语句在的执行，总的来说可以分为：Server 层和存储引擎层（本节只聊 InnoDB），下面来仔细聊聊这些过程。

### 1 Server 层 ###

![5a6b7fd1afcfc03589d241617f3f012e.png][]

1.1 连接层

负责跟客户端建立连接、账号密码验证、获取权限、维持和管理连接。

1.2 分析器

在通过验证以后，分析器会对该语句分析，判断是否语法有错误等。

1.3 优化器

选择索引，生成执行计划。

1.5 执行器

根据优化器生成的执行计划，调用存储引擎 API 执行 SQL。

### 二 、InnoDB 引擎层 ###

![894ee1ab31fdd17e0d0564343968a479.png][]

2.1 事务执行

1.  读取数据页面
    
    进入 InnoDB 引擎层后，首先会判断该 SQL 涉及到的数据页是否存在于 BP(buffer pool)中；
    
    如果不存在则通过 B+Tree 读取到磁盘的数据页，然后加载到 BP：
    
    通过二分法查找该页对应的记录
    
     *  通过 space id 和 page no 哈希计算之后把 索引页加载到指定的 buffer pool instance 中
     *  判断 free list 是否存在可用空闲页( Innodb\_buffer\_pool\_pages\_free、 Innodb\_buffer\_pool\_wait\_free )，没有则淘汰脏页或者 lru list 的 old 页
     *  把数据页 copy 到 free list 中，然后加载到 lru list 的 old 区的 midpoint（头部）
2.  加锁
    
    尝试给对应行记录加上排他锁，过程如下：
    
     *  对应行记录的行锁是否被其他事务占用，占用则进入锁等待；
     *  进入锁等待之后，同时判断会不会由于自己的加入导致了死锁；
     *  检测到没有锁等待和不会造成死锁之后，行记录加上排他锁；
3.  写逻辑 undo log
    
    将修改前的记录写入undo中；
    
    修改当前行的值，填写事务编号；
    
    使用回滚指针指向 undo log 中的修改前的行，构建回滚段，用于回滚数据和实现 MVCC 的多版本。
4.  写 redo log buffer
    
    先判断 redo log buffer 是否够用，不够用则等待，可通过 Innodb\_log\_waits 值查看;
    
    对应行记录的字段值做更新操作，并把修改操作记录到 redo log buffer 中；
    
    对应数据页面加入 flush list 链表中。
5.  写 binlog cache
    
    修改的信息会以对应 event 格式写入 binlog cache 中。
6.  写 change buffer
    
    如果此次 update 操作涉及到二级索引的修改，则写入 change buffer page 。

2.2 事务提交

InnoDB 存储引擎事务提交分为 prepare、commit 两阶段提交

1.  redo log prepare
    
    将 redo log buffer 刷新到磁盘文件中，用于崩溃恢复；刷盘的方式由 innodb\_flush\_log\_at\_trx\_commit 决定（未标记commit），存储引擎层处于 prepare 状态.
2.  binlog write & fsync
    
    执行器把 binlog cache 里的完整事务和 redo log prepare 中的 XID event 写入到 binlog 中；
    
    发送 binlog\_cache 里的 event 到 slave 并等待（异步模式不等待） slave ack ；
    
    执行 fsync 刷盘(大事务的话这步非常耗时)，并清空 binlog cache；*\# binlog 刷盘的方式由  sync\_binlog 决定。*
3.  redo log commit
    
    commit 阶段，由于之前该事务产生的 redo log 已经 sync 到磁盘了，所以这步只是在 redo log 里标记 commit，表明事务提交成功。半同步模式下如果收不到 slave ack 此步骤会处于等待状态*。*
4.  事务提交成功，释放行记录持有的排他锁。
5.  刷新脏页
    
    数据库按照一定的策略执行刷脏页的操作。

2.3 事务回滚

如果事务因为异常或者被显式的回滚了，则借助 undo log 中的数据来进行恢复:

*  对于 in-place(原地)更新，将数据回滚到最老版本；
 *  对于 delete + insert 方式进行的，标记删除的记录清理删除标记，同时把插入的聚集索引和二级索引记录也会被直接删除。

[5a6b7fd1afcfc03589d241617f3f012e.png]: /images/20221120/e18d378b791b42299ef95df9d07fbd18.png
[894ee1ab31fdd17e0d0564343968a479.png]: /images/20221120/0b24861f3d5d4972bf24831876304249.png