Mysql 更新语句执行过程
一、概述
一条 select 语句的执行流程大致如下:
那么一条 Update 语句,期间会发生什么呢?由于更新语句会涉及到 3 个比较重要的日志(undo log、redo log、binlog),所以我们需要先了解一下这三个日志的作用分别是什么
- undo log: InnoDB 存储引擎层生成的日志,实现了事务的原子性,主要用于事务回滚和 MVCC(多版本并发控制)
- redo log: InnoDB 存储引擎层生成的日志,实现了事务的持久性,主要用于故障恢复(cash-safe)
- binlog: Mysql Server 层生成的日志,主要用于归档和主从复制
二、undo log
我们在执行一条 DML 语句的时候,虽然没有显示的开启事务(begin/start transaction)和提交事务(commit),但是 Mysql 会帮我们隐式的开启事务来执行(增、删、改)操作,执行完成之后自动提交事务,这样就能保证执行完 DML 语句之后,我们可以及时的在数据库、表中看到 DML 的结果
如果一条 DML 语句开启了事务之后,在执行的过程中如果还没有进行提交,这个时候 Mysql 崩溃了,那么要怎么恢复到事务之前的数据呢?
如果我们每次在事务执行过程中,都记录下回滚时所需要的信息到一个日志里,那么在事务执行过程中发生了 Mysql 奔溃后,就不用担心无法回滚到事务之前的数据,我们可以通过这个日志将数据恢复到事务开启之前时的样子,这个日志就是我们常说的 undo log(回滚日志),它保证了事务 ACID 特性中的原子性
undo log 是一种用于撤销回退的日志,在事务没有提交之前,Mysql 会先记录更新前的数据到 undo log 文件中,当事务需要回滚时,可以利用 undo log 来进行回滚
整个 undo log 的工作流程大致如下:
每当 InnoDB 存储引擎对一条记录进行 (Insert、Update、Delete) 时,都要把回滚时所需要的信息都记录到 undo log 里
- Insert: 在插入一条记录时,先要把这条记录的主键值记录下来,回滚时只需要把这个主键值对应的记录删除就好了(Insert 的回滚操作对应的是 Delete)
- Update: 在更新一条记录时,先要把更新列的旧值记录下来,回滚时再把这些列更新为旧值就好了(Update 的回滚操作对应的是反向 Update)
- Delete: 在删除一条记录时,先要把这条记录的所有内容都记录下来,回滚时再把由这些内容组成的记录插入到数据表中就好了(Delete 的回滚操作对应的是 Insert)
在发生回滚时,InnoDB 存储引擎就读取 undo log 里面的数据,然后做与原操作相反的操作,不同的操作需要记录的内容也是不同的,所以不同类型的操作(Insert、Update、Delete)产生的 undo log 的格式也是不同的
一条记录的每一次更新操作产生的 undo log 格式都有一个 roll_pointer 指针和一个 trx_id(事务 id),除了实现回滚事务之外,undo log 还有一个作用,就是通过 ReadView + undo log 版本链实现 MVCC(多版本并发控制)
undo log 是存在 Buffer Pool(内存中),那么 undo log 是如何持久化的
undo 页和其它缓存页的刷盘策略是一样的,都需要通过 redo log 保证持久化
Buffer Pool 中存在 undo log 缓存页,在执行事务的过程中会产生 undo log,同时也会产生相应的 undo log 对应的 redo log,有了 redo log 就能保证 undo 页持久化到磁盘
undo log 作用总结
- 实现事务回滚,保证事务的原子性,事务处理过程中,如果出现了异常/用户执行了 rollback 语句,Mysql 可以利用 undo log 中的历史记录将数据恢复到事务开始之前的状态
- 通过 ReadView + undo log 版本链实现 MVCC
三、redo log
为什么需要 redo log
在执行 Sql 操作时,如果需要操作的缓存页在 Buffer Pool 中,那么直接操作对应的缓存页就行了,如果缓存页不在内存中则需要先从磁盘将对应的数据页读入 Buffer Pool 中,然后才能进行后续操作,这样可以减少磁盘 IO,提升读写性能
Buffer Pool 是提高了读写效率没错,但是问题来了,Buffer Pool 是基于内存的,如果 Mysql 发生故障导致意外关闭、Linux 服务器宕机、直接掉电等都会导致没有落盘的脏页数据丢失
为了防止上述的意外情况导致数据丢失的问题,当有一条记录需要更新的时候,InnoDB 存储引擎就会先把记录写到 redo log 里面,然后再更新内存(同时标记为脏页),这个时候更新就算完成了,后续 InnoDB 引擎会在适当的时候,由后台线程先将 redo log 刷新到磁盘上,然后再将脏页落盘,这就是 Mysql 的 WAL 机制
WAL(Write-Ahead Logging): 预写日志系统,其主要是指 Mysql 在执行写操作的时候并不是将脏页立刻更新到磁盘上,而是先记录在日志中,之后在合适的时间更新到磁盘中,Mysql 中的 redo log 就是采用了 WAL 机制
为什么 redo log 需要 WAL 机制
脏页刷盘是随机 IO,比较消耗性能,而 redo log 是以循环追加的形式写入的,可以认为是顺序写,性能较高,只要能保证 redo log buffer 中的缓存页能正确的写入 redo log file 中,即便是因各种意外导致脏页未落盘,也可以通过 redo log 恢复内存,保证数据的准确性
整个大致的流程如下:
什么是 redo log
redo log 是物理日志,记录的是对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量处做了 AAA 修改,每当执行一个事务就会产生这样的一条或多条物理日志
在事务提交时,只要现将 redo log 持久化到磁盘即可,可以不需要等到将缓存在 Buffer Pool 中的脏页持久化到磁盘
当系统崩溃时,虽然脏页没有被持久化,但是 redo log 已经持久化,接着 Mysql 重启后,可以根据 redo log 进行重放,将所有已经提交的数据恢复出来
被修改的 undo 页,需要记录对应的 redo log 吗
开启事务之后,InnoDB 层更新记录前,首先要记录相应的 undo log,如果是更新操作,需要把被更新的列的旧值记录下来,也就是要生成一条 undo log,undo log 会写入 Buffer Pool 中的 undo 页面
不过,在内存修改了该 undo 页之后,需要记录对应的 redo log,因为 undo 页是存在内存中的,没有持久化到磁盘,如果发生故障,内存中的数据就丢失了,无法利用 undo log 中的原始数据进行回滚操作(例如回滚操作进行到一半的时候,Mysql 宕机了,后续重启 Mysql 的时候该如何将数据真正回滚)
undo log 和 redo log 在事务开启过程中扮演的角色分别是什么
- undo log 记录了此次事务 开始前 的数据状态,记录的是更新之前的值
- redo log 记录了此次事务 完成后 的数据状态,记录的是更新之后的值
事务提交之前如果发生了崩溃,重启后会通过 undo log 回滚恢复数据,事务提交之后发生了崩溃,重启后会通过 redo log 重做恢复数据
整个流程大致如下:
所以有了 redo log,InnoDB 就可以保证及时数据库发生异常重启,之前已经提交的记录都不会丢失,这个能力称为 crash-safe(崩溃恢复).可以看出 redo log 能保证事务四大特性 ACID 中的持久性
为什么脏页不直接落盘而要先写 redo log 呢,这样不就多此一举了吗
写入 redo log 是以追加的方式进行的,可以认为是磁盘顺序写操作,而脏页落盘需要先定位到写入的位置,然后才能写入磁盘,是随机写,随机 IO 的性能较低,当有大量脏页数据要落盘时,会产生大量的随机 IO,数据库性能较低,并且失败的概率也更大
这也是 WAL 机制的另外一个有点,写入 redo log 将磁盘的随机写变成了顺序写,提升语句的执行性能,这是因为 Mysql 的写操作并不是立即更新到磁盘上,而是先记录在日志上,然后在合适的时机再更新到磁盘上
redo log 作用总结
- 将写操作由随机写变成顺序写
- 实现事务的持久性,让 Mysql 有了 crash-safe(崩溃恢复)的能力,能够保证 Mysql 在任何时间段突然崩溃,重启后之前已提交的记录都不会丢失
那么 Buffer Pool 中的 redo log buffer 是立即写入 redo log file 中的吗
不是的,实际上在执行一个事务的过程中,产生的 redo log 也不是直接写入磁盘的,因为这样会产生大量的 IO 操作,而且磁盘的运行速度远低于内存,所以 redo log 也有自己的缓存(redo log buffer),每当产生一条 redo log 时,会先写入 redo log buffer,后续采用一定的策略持久化到磁盘上的 redo log file 中
redo log buffer 默认大小 16 MB,可以通过 innodb_log_Buffer_size 参数动态的调整大小,增大它的大小可以让 Mysql 处理大事务时不必写入磁盘,进而提升 IO 性能
大事务产生的 redo log 比较大,如果 redo log buffer 空间不够,不足以存储产生的 redo log 时会强制将刷盘操作
show variables like '%innodb_log_Buffer_size%';
我们生产上就增大了 redo log buffer 的大小,设置成了 64KB(67108864 / 1024 / 1024 = 64)
缓存在 redo log buffer 中的 redo log 还是存在于内存中的,它是什么时候进行刷盘操作的呢
redo log 刷盘时机:
- Mysql 正常关闭时,会触发 redo log 刷盘
- redo log buffer 中记录的写入量大于 redo log buffer 内存总大小的一半,会触发 redo log 刷盘
- InnoDB 的后台线程每隔 1 秒,将 redo log buffer 持久化到磁盘
- 每次事务提交后通过 innodb_flush_log_at_trx_commit 参数来控制,以不同的策略进行 redo log 刷盘
innodb_flush_log_at_trx_commit 参数控制的落盘策略有哪些
innodb_flush_log_at_trx_commit 的取值有 0、1、2
| 参数值 | 含义 | 说明 |
| 0 | 每次提交事务时,将 redo log 写入 redo log buffer 中,然后每秒把缓存在 redo log buffer 中的 redo log 通过调用 write() 函数写入到操作系统的 Page Cache,然后调用 fsync() 函数将数据刷新到磁盘上的 redo log file 中 | 延时写操作系统缓存,延时刷磁盘,如果 Mysql 奔溃 / Linux 宕机都会丢失一秒钟的数据 |
| 1(默认策略) | 每次事务提交时,将 redo log 写入 操作系统的 Page Cache,并立即调用 fsync() 将数据刷新到磁盘上的 redo log file 中 | 实时写操作系统缓存,实时刷磁盘,如果 Mysql 奔溃 / Linux 宕机不会丢失任何数据 |
| 2 | 每次事务提交时,将 redo log 写入 操作系统的 Page Cache,然后每秒调用 fsync() 函数将数据刷新到磁盘上的 redo log file 中 | 实时写操作系统缓存,延时刷磁盘,如果 Mysql 奔溃不会丢失数据,但是 Linux 服务器宕机会丢失一秒钟的数据 |
可以通过下面图示理解一下
这三个参数的应用场景是什么
写入性能: 参数 0 > 参数 2 > 参数 1
数据安全性: 参数 1 > 参数 2 > 参数 0
写入性能和数据安全性不能同时兼顾,需要根据自身实际情况选择不同的策略
在一些对数据安全性不计较高的场景中,需要将 innodb_flush_log_at_trx_commit 设置为 1
在一些可以容忍数据库崩溃时丢失 1s 数据的场景中,可以将 innodb_flush_log_at_trx_commit 设置为 0
安全性和写入性能折中的方案就将 innodb_flush_log_at_trx_commit 设置为 2,这种策略下,虽然没有没有参数 0 的写入性能高,但是只要 Linux 服务器不宕机,即使数据库崩溃了,也不会丢失数据,同时写入性能比 参数 1 高
建议生产上将 innodb_flush_log_at_trx_commit 设置为 2
show variables like '%innodb_flush_log_at_trx_commit%';
Redo Log File 写满了怎么办
默认情况下,InnoDB 存储引擎有一个重做日志文件组(Redo Log Group),重做日志文件组由 2 个 redo log 文件组成,这两个 redo log 的文件名分别为 ib_logfile0 和 ib_logfile1
在重做日志组中,每个 Redo Log File 的大小是固定且一致的,假设每个 Redo Log File 设置的上限是 1 GB,那么总共就可以记录 2GB 的日志
重做日志文件组是以追加循环写的方式工作的,从头开始写,写到末尾就又回到开头,相当于一个环形
所以 InnoDB 存储引擎会先写 ib_logfile0 文件,当 ib_logfile0 文件被写满的时候,会切换至 ib_logfile1 文件,当 ib_logfile1 文件也被写满时,会切换回 ib_logfile0 文件
我们知道 redo log 是为了防止 Buffer Pool 中的脏页丢失而设计的,那么如果随着系统运行,Buffer Pool 的脏页刷新到了磁盘中,那么 redo log 对应的记录也就没用了,这时候我们需要擦除这些旧记录,腾出空间记录新的更新操作,redo log 是循环写的方式,相当于一个环形
write pos 和 checkpoint 的移动都是顺时针方向
write pos ～ checkpoint 之间的部分(图中的红色部分): 可以用来记录新的更新操作
check point ～ write pos 之间的部分(图中蓝色部分): 待落盘的脏数据页记录
如果 write pos 追上了 checkpoint,就意味着 redo log 文件满了,这时 Mysql 不能再执行新的更新操作,也就是说 Mysql 会被阻塞(因此所以针对并发量大的系统,适当设置 redo log 的文件大小非常重要),此时会停下来将 Buffer Pool 中的脏页刷新到磁盘中,然后标记 redo log 哪些记录可以被擦除,接着对旧的 redo log 记录进行擦除,等擦除完旧记录腾出了空间,checkpoint 就会往后移动(图中顺时针)然后 Mysql 恢复正常运行,继续执行新的更新操作
所以,一次 checkpoint 的过程就是脏页刷新到磁盘中变成干净页,然后标记 redo log 哪些记录可以被覆盖的过程
四、binlog
为什么需要 binlog
前面介绍的 undo log 和 redo log 这两个日志都是 InnoDB 存储引擎生成的
Mysql 在完成一条更新操作后,Server 层还会生成一条 binlog,等之后事务提交的时候,会将该事物执行过程中产生的所有 binlog 统一写 入 binlog 文件
binlog 文件是记录了所有数据库表结构变更和表数据修改的日志(DDL 和 DML),不会记录查询类的操作,比如 SELECT 和 SHOW 操作
为什么有了 binlog,还要有 redo log
这个问题跟 Mysql 的时间线有关系,最开始 Mysql 里并没有 InnoDB 引擎,Mysql 自带的引擎是 MyISAM,但是 MyISAM 没有 crash-safe 的能力,bin log 只能用于归档,而 InnoDB 是另一个公司以插件形式引入 Mysql 的,既然只依靠 bin log 是没有 crash-safe 能力的,所以 InnoDB 使用 redo log 来实现 crash-safe 能力
redo log 和 binlog 有什么区别
1、适用对象不同
bin log 是 Mysql 的 Server 层实现的日志,所有存储引擎都可以使用
redo log 是 InnoDB 存储引擎实现的日志
2、文件格式不同
bin log 有 3 种格式类型,分别是 STATEMENT(默认格式)、ROW、 MIXED，区别如下：
STATEMENT: 每一条修改数据的 SQL 都会被记录到 bin log 中(相当于记录了逻辑操作,所以针对这种格式,binlog 可以称为逻辑日志),主从复制中 slave 端再根据 SQL 语句重现.但 STATEMENT 有动态函数的问题,比如你用了 uuid 或者 now 这些函数,你在主库上执行的结果并不是你在从库执行的结果,这种随时在变的函数会导致主库和从库的数据不一致(优点: 日志量小,缺点:动态函数导致主从数据不一致)
ROW: 记录行数据最终被修改成什么样了(这种格式的日志,就不能称为逻辑日志了)不会出现 STATEMENT 下动态函数的问题.但 ROW 的缺点是每行数据的变化结果都会被记录,比如执行批量 update 语句,更新多少行数据就会产生多少条记录,使 bin log 文件过大,而在 STATEMENT 格式下只会记录一个 update 语句而已(优点: 主从数据保持一致,缺点: 日志量巨大)
MIXED: 包含了 STATEMENT 和 ROW 模式,它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式
redo log 是物理日志,记录的是在某个数据页做了什么修改,比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新
3、写入方式不同
bin log 是追加写,写满一个文件,就创建一个新的文件继续写,不会覆盖以前的日志,保存的是全量的日志
redo log 是循环写,日志空间大小是固定,全部写满就从头开始,保存未被刷入磁盘的脏页日志
4、用途不同：
binlog 用于备份恢复、主从复制
redo log 用于掉电等故障恢复
如果不小心整个数据库的数据被删除了,能使用 redo log 文件恢复数据吗
不可以使用 redo log 文件恢复,只能使用 binlog 文件恢复,因为 redo log 文件是循环写,是会边写边擦除日志的,只记录未被刷入磁盘的数据的物理日志,已经刷入磁盘的数据都会从 redo log 文件里擦除
bin log 文件保存的是全量的日志,也就是保存了所有数据变更的情况,理论上只要记录在 binlog 上的数据,都可以恢复,所以如果不小心整个数据库的数据被删除了,得用 binlog 文件恢复数据
主从复制是怎么实现
Mysql 的主从复制依赖于 bin log ,也就是记录 Mysql 上的所有变化并以二进制形式保存在磁盘上.复制的过程就是将 binlog 中的数据从主库传输到从库上,这个过程一般是异步的,也就是主库上执行事务操作的线程不会等待复制 bin log 的线程同步完成
MySQL 主从复制过程
Mysql 集群的主从复制过程梳理成 3 个阶段
- 写入 bin log: 主库写 bin log 日志,提交事务,并更新本地存储数据
- 同步 bin log: 把 bin log 复制到所有从库上,每个从库把 bin log 写到暂存日志中
- 回放 bin log：回放 bin log,并更新从库存储引擎中的数据
具体详细过程如下：
Mysql 主库在收到客户端提交事务的请求之后,会先写入 bin log,再提交事务,更新存储引擎中的数据,事务提交完成后,返回给客户端操作成功的响应
从库会创建一个专门的 I/O 线程,连接主库的 log dump 线程,来接收主库的 bin log 日志,再把 bin log 信息写入 relay log 的中继日志里,再返回给主库复制成功的响应。
从库会创建一个用于回放 bin log 的线程,去读 relay log 中继日志,然后回放 bin log 更新存储引擎中的数据,最终实现主从的数据一致性
在完成主从复制之后,你就可以在写数据时只写主库,在读数据时只读从库,这样即使写请求会锁表或者锁记录,也不会影响读请求的执行
从库是不是越多越好
不是的,因为从库数量增加,从库需要连接到主库的 I/O 线程也比较多,主库也要创建同样多的 log dump 线程来处理复制的请求,对主库资源消耗比较高,同时还受限于主库的网络带宽。
所以在实际使用中,一个主库一般跟 2 ~ 3 个从库(1 套数据库配置: 1 主 2 从 1 备主)这就是一主多从的 Mysql 集群结构
Mysql 主从复制有哪些模型
同步模式是数据库系统中一个非常重要的概念,它用来控制数据在多个节点之间的互相同步.在 Mysql 中,同步模式有三种,它们分别是异步复制、半同步复制和全同步复制
1、全同步复制
在全同步复制中,主库在完成一次更新操作后,必须等待所有从库将更新操作写入本地的日志文件并返回更新成功的响应后,才能提交事务
全同步复制的优点是非常高的数据同步一致性,因为在同步操作时会等待从库的响应.缺点是它会增加网络通信的延迟和服务器内存的使用,从而会影响数据库系统的性能
2、异步复制(默认模式)
异步复制是 Mysql 默认的同步模式,它指的是主库将更新操作记录到二进制日志中后,就认为该操作已经完成,不需要等待从库的响应,也就是说,主库和从库之间的同步是异步的,主库不会等待从库的更新操作完成
异步复制的优点是同步操作速度快,因为主库可以立即自由执行更新操作,而不需要等待从库的响应.但是,异步复制的缺点也非常明显,就是存在数据丢失的风险.因为从库的更新操作可能在主库的更新操作完成后才能完成,如果发生故障,那么从库上的数据就可能存在落后于主库的情况.对于一些对数据同步一致性非常重要的应用来说,异步复制的数据丢失风险是不可接受的
3、半同步复制
半同步复制是一种在异步复制基础上增加了同步机制的同步模式,在半同步复制中,主库在完成一次更新操作后,必须先等待至少一个从库将更新操作写入本地的日志文件后,才能提交事务.只有等到至少一个从库返回更新成功的响应后,主库才会认为该更新操作已经同步到从库,并提交事务
半同步复制的优点是能够降低数据丢失风险,因为在同步操作时会等待从库的响应,但是,半同步复制的缺点也非常明显,就是同步操作比异步复制慢,因为主库需要等待从库的响应.处理请求的性能受到限制,并且主库与从库之间的网络延迟增加了更新的总时间
bin log 什么时候刷盘
事务执行过程中,先把日志写到 binlog cache(Server 层的 cache),事务提交的时候,再把 binlog cache 写到操作系统缓存中
Mysql 给 bin log cache 分配了一片内存,每个线程一个,参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小.如果超过了这个参数规定的大小,就要暂存到磁盘
什么时候 binlog cache 会写到操作系统缓存中
在事务提交的时候,执行器把 binlog cache 里的完整事务写入到 binlog 文件中,并清空 binlog cache
虽然每个线程有自己 binlog cache,但是最终都写到同一个 binlog 文件
图中的 write,指的就是指把日志写入到操作系统的文件系统中,但是并没有把数据持久化到磁盘,因为数据还缓存在文件系统的 page cache 里,write 的写入速度还是比较快的,因为不涉及磁盘 I/O
图中的 fsync,才是将数据持久化到磁盘的操作,这里就会涉及磁盘 I/O,所以频繁的 fsync 会导致磁盘的 I/O 升高
Mysql 提供一个 sync_binlog 参数来控制数据库的 binlog 刷到磁盘上的频率
- sync_binlog = 0 的时候,表示每次提交事务都只 write,不 fsync,后续交由操作系统决定何时将数据持久化到磁盘
- sync_binlog = 1 的时候,表示每次提交事务都会 write,然后马上执行 fsync
- sync_binlog = N(N>1)的时候,表示每次提交事务都 write,但累积 N 个事务后才 fsync
在 Mysql 中系统默认的设置是 sync_binlog = 0,也就是不做任何强制性的磁盘刷新指令,这时候的性能是最好的,但是风险也是最大的.因为一旦 Mysql 主机发生异常重启,在 binlog cache 中的所有 binlog 日志都会被丢失
而当 sync_binlog 设置为 1 的时候,是最安全但是性能损耗最大的设置.因为当设置为 1 的时候,即使主机发生异常重启,也最多丢失 binlog cache 中未完成的一个事务,对实际数据没有任何实质性影响,就是对写入性能影响太大
如果能容少量事务的 binlog 日志丢失的风险,为了提高写入的性能,一般会 sync_binlog 设置为 100~1000 中的某个数值
我们生产就是将 sync_binlog 设置成了 128
show variables like '%sync_binlog%';
五、两阶段提交
具体更新一条记录 UPDATE t_user SET name = 'xiaolin' WHERE id = 1 的流程如下:
1、执行器负责具体执行,会调用存储引擎的接口,通过主键索引树搜索获取 id = 1 这一行记录
如果 id=1 这一行所在的数据页本来就在 buffer pool 中,就直接返回给执行器更新
如果记录不在 buffer pool,将数据页从磁盘读入到 buffer pool,返回记录给执行器
2、执行器得到聚簇索引记录后,会看一下更新前的记录和更新后的记录是否一样
如果一样的话就不进行后续更新流程
如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层,让 InnoDB 真正的执行更新记录的操作
3、开启事务,InnoDB 层更新记录前,首先要记录相应的 undo log,因为这是更新操作,需要把被更新的列的旧值记下来,也就是要生成一条 undo log,undo log 会写入 Buffer Pool 中的 Undo 页面,不过在修改该 Undo 页面前需要先记录对应的 redo log,所以 先记录修改 Undo 页面的 redo log,然后再真正的修改 Undo 页面
4、InnoDB 层开始更新记录,根据 WAL 技术,先记录修改数据页面的 redo log
5、然后再真正的修改数据页面.修改数据页面的过程是修改 Buffer Pool 中数据所在的页,然后将其页设置为脏页,为了减少磁盘 I/O,不会立即将脏页写入磁盘,后续由后台线程选择一个合适的时机将脏页写入到磁盘
6、至此,一条记录在内存中就更新完了
7、在一条更新语句执行完成后,然后开始记录该语句对应的 binlog,此时记录的 binlog 会被保存到 binlog cache,并没有刷新到硬盘上的 binlog 文件,在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘
8、事务提交,剩下的就是 两阶段提交 的事情了,接下来就讲这个
为什么需要两阶段提交
事务提交后,redo log 和 binlog 都要持久化到磁盘,但是这两个是独立的逻辑,可能出现半成功的状态,这样就造成两份日志之间的逻辑不一致
举个例子,假设 id = 1 这行数据的字段 name 的值原本是 'jay'，然后执行 UPDATE t_user SET name = 'xiaolin' WHERE id = 1; 如果在持久化 redo log 和 binlog 两个日志的过程中,出现了半成功状态,那么就有两种情况：
如果在将 redo log 刷入到磁盘之后,Mysql 突然宕机了,而 binlog 还没有来得及写入,Mysql 重启后,通过 redo log 能将 Buffer Pool 中 id = 1 这行数据的 name 字段恢复到新值 xiaolin,但是 binlog 里面没有记录这条更新语句,在主从架构中,binlog 会被复制到从库,由于 binlog 丢失了这条更新语句,从库的这一行 name 字段是旧值 jay,与主库的值不一致性
如果在将 binlog 刷入到磁盘之后,Mysql 突然宕机了,而 redo log 还没有来得及写入.由于 redo log 还没写,崩溃恢复以后这个事务无效,所以 id = 1 这行数据的 name 字段还是旧值 jay,而 binlog 里面记录了这条更新语句,在主从架构中,binlog 会被复制到从库,从库执行了这条更新语句,那么这一行 name 字段是新值 xiaolin,与主库的值不一致性
可以看到,在持久化 redo log 和 binlog 这两份日志的时候,如果出现半成功的状态,就会造成主从环境的数据不一致性.这是因为 redo log 影响主库的数据,binlog 影响从库的数据,所以 redo log 和 binlog 必须保持一致才能保证主从数据一致
两阶段提交的过程是怎样的
在 Mysql 的 InnoDB 存储引擎中,开启 binlog 的情况下,Mysql 会同时维护 binlog 日志与 InnoDB 的 redo log,为了保证这两个日志的一致性,Mysql 使用了内部 XA 事务(是的,也有外部 XA 事务,跟本文不太相关),内部 XA 事务由 binlog 作为协调者,存储引擎是参与者。
当客户端执行 commit 语句或者在自动提交的情况下,Mysql 内部开启一个 XA 事务,分两阶段来完成 XA 事务的提交,如下图
两阶段提交
从图中可看出,事务的提交过程有两个阶段,就是将 redo log 的写入拆成了两个步骤(prepare 和 commit),中间再穿插写入 binlog,具体如下：
- prepare 阶段：将 XID(内部 XA 事务的 ID)写入到 redo log,同时将 redo log 对应的事务状态设置为 prepare,然后将 redo log 刷新到硬盘
- commit 阶段：把 XID 写入到 binlog,然后将 binlog 刷新到磁盘,接着调用引擎的提交事务接口,将 redo log 状态设置为 commit(将事务设置为 commit 状态后,刷入到磁盘 redo log 文件,所以 commit 状态也是会刷盘的)
异常重启会出现什么现象
我们来看看在两阶段提交的不同时刻,Mysql 异常重启会出现什么现象?下图中有时刻 A 和时刻 B 都有可能发生崩溃
不管是时刻 A(已经 redo log，还没写入 binlog),还是时刻 B(已经写入 redo log 和 binlog,还没写入 commit 标识)崩溃,此时的 redo log 都处于 prepare 状态
在 MySQL 重启后会按顺序扫描 redo log 文件,碰到处于 prepare 状态的 redo log,就拿着 redo log 中的 XID 去 binlog 查看是否存在此 XID
如果 binlog 中没有当前内部 XA 事务的 XID,说明 redolog 完成刷盘,但是 binlog 还没有刷盘,则回滚事务.对应时刻 A 崩溃恢复的情况
如果 binlog 中有当前内部 XA 事务的 XID,说明 redolog 和 binlog 都已经完成了刷盘,则提交事务,对应时刻 B 崩溃恢复的情况
可以看到,对于处于 prepare 阶段的 redo log,即可以提交事务,也可以回滚事务,这取决于是否能在 binlog 中查找到与 redo log 相同的 XID,如果有就提交事务,如果没有就回滚事务.这样就可以保证 redo log 和 binlog 这两份日志的一致性了
所以说,两阶段提交是以 binlog 写成功为事务提交成功的标识,因为 binlog 写成功了,就意味着能在 binlog 中查找到与 redo log 相同的 XID
处于 prepare 阶段的 redo log 加上完整 binlog,重启就提交事务,Mysql 为什么要这么设计
binlog 已经写入了,之后就会被从库(或者用这个 binlog 恢复出来的库)使用,所以,在主库上也要提交这个事务.采用这个策略,主库和备库的数据就保证了一致性
事务没提交的时候,redo log 会被持久化到磁盘吗
会的,事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的,这些缓存在 redo log buffer 里的 redo log 也会被「后台线程」每隔一秒一起持久化到磁盘
也就是说,事务没提交的时候,redo log 也是可能被持久化到磁盘的
有的同学可能会问,如果 mysql 崩溃了,还没提交事务的 redo log 已经被持久化磁盘了,mysql 重启后,数据不就不一致了?
放心,这种情况 mysql 重启会进行回滚操作,因为事务没提交的时候,binlog 是还没持久化到磁盘的
所以,redo log 可以在事务没提交之前持久化到磁盘,但是 binlog 必须在事务提交之后,才可以持久化到磁盘
两阶段提交有什么问题
两阶段提交虽然保证了两个日志文件的数据一致性,但是性能很差,主要有两个方面的影响：
1、磁盘 I/O 次数高：对于 双1 配置,每个事务提交都会进行两次 fsync(刷盘),一次是 redo log 刷盘,另一次是 binlog 刷盘
2、锁竞争激烈：两阶段提交虽然能够保证[单事务]两个日志的内容一致,但在[多事务]的情况下,却不能保证两者的提交顺序一致,因此,在两阶段提交的流程基础上,还需要加一个锁来保证提交的原子性,从而保证多事务的情况下,两个日志的提交顺序一致
为什么两阶段提交的磁盘 I/O 次数会很高
binlog 和 redo log 在内存中都对应的缓存空间,binlog 会缓存在 binlog cache,redo log 会缓存在 redo log buffer,它们持久化到磁盘的时机分别由下面这两个参数控制,一般我们为了避免日志丢失的风险,会将这两个参数设置为 1
当 sync_binlog = 1 的时候,表示每次提交事务都会将 binlog cache 里的 binlog 直接持久到磁盘
当 innodb_flush_log_at_trx_commit = 1 时,表示每次事务提交时,都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘
可以看到,如果 sync_binlog 和 当 innodb_flush_log_at_trx_commit 都设置为 1,那么在每个事务提交过程中, 都会至少调用 2 次刷盘操作,一次是 redo log 刷盘,一次是 binlog 落盘,所以这会成为性能瓶颈
为什么锁竞争激烈
在早期的 Mysql 版本中,通过使用 prepare_commit_mutex 锁来保证事务提交的顺序,在一个事务获取到锁时才能进入 prepare 阶段,一直到 commit 阶段结束才能释放锁,下个事务才可以继续进行 prepare 操作,
通过加锁虽然完美地解决了顺序一致性的问题,但在并发量较大的时候,就会导致对锁的争用,性能不佳
关于 redolog 和 binlog 额外补充几点
可以只用 binlog 来支持崩溃恢复,又能支持归档吗
不可以,binlog 不具备崩溃恢复 Crash-Safe 能力,没法做数据的恢复
首先要清楚,InnoDB 引擎使用的是 WAL 技术,执行事务的时候,写完内存和日志,事务就算完成了,内存中的脏页会有后台任务根据一些机制刷入磁盘,如果发生崩溃,要依赖于日志来恢复数据页,但是 binlog 没法用来恢复内存中的数据页
1、binlog 是逻辑日志,记录的是这个语句的原始逻辑,不能直接应用于内存(不能把内存恢复到宕机前的状态)；
2、重放 binlog 的时候,你不知道哪些日志对应的修改已经写入磁盘,也就是虽然 binlog 拥有全量的日志,但没有一个标志让 innoDB 判断哪些数据已经刷盘,哪些数据还没有
恰恰以上两点，redolog都具备，
1、redolog 是物理日志,记录的是,在某个数据页上做了什么修改,可以用来恢复内存
2、redolog 的循环写特性就天然的具备 判断对数据的修改是否落盘 的标志(已经刷入磁盘的数据都会从 redolog 删除---Checkpoint移动)
只用 redolog,不要 binlog 可以吗
如果只从崩溃恢复的角度来讲是可以的,你可以把 binlog 关掉,这样就没有两阶段提交了,但系统依然是具有 Crash-safe 的,但是,如果你了解一下业界各个公司的使用场景的话,就会发现在正式的生产库上 binlog 都是开着的,因为binlog有着redolog无法替代的功能
redolog 是不能用来归档的,redolog 是循环写,写到末尾是要回到开头继续写的,这样历史日志没法保留的,自然 redolog 也就起不到归档的作用
Mysql 系统依赖于 binlog,binlog 作为 Mysql 一开始就有的功能,被用在了很多地方,其中,Mysql 系统高可用的基础,就是 binlog 复制,还有很多公司有异构系统(比如一些数据分析系统)这些系统就靠消费 Mysql 的 binlog 来更新自己的数据,关掉 binlog 的话,这些下游系统就没办法正常运行了
总之,由于现在包括 Mysql 高可用在内的很多系统机制都依赖于 binlog,所以 "鸠占鹊巢" redolog 还做不到
分布式事务也有"两阶段提交"的解决方案,怎么理解 Mysql 中的 "两阶段提交"
两阶段提交是经典的分布式系统问题,并不是 Mysql 独有的
redolog 是在 InnoDB 引擎内操作的,而 binlog 是在 Server 层操作的,我们就可以把引擎层和 Server 层看成两个分布式服务,那他们要分别进行两个相关联的操作,就意味着要实现分布式事务,而两阶段提交,就是其中的一种解决方案
因为这两个日志属于 Mysql 的不同层,因此还是会存在一个成功一个失败的情况,就像分布式事务中不同分布式系统操作的数据源不同,并且可靠性也不同,就一定需要考虑 A 成功 B 失败的情况
有个前提---对于 InnoDB 引擎来说,如果 redolog 提交完成了,事务就不能回滚(如果这都 commit 了还允许回滚,就可能覆盖掉别的事务的更新)(持久化到磁盘之后就不能回滚)
因此,如果不用两阶段,redolog 直接提交,然后 binlog 写入的时候失败,这种情况下 InnoDB 又回滚不了,那结果就是主库数据和 binlog 日志又不一致了,肯定是不行的,最起码的问题就是主从就不一致了
两阶段提交也可以理解成给每个系统一个确认的机会,当每个人都说 “我ok” 的时候,再一起提交,如果前置就发现某个系统还没准备好时,就不用提交了
六、总结
具体更新一条记录 UPDATE t_user SET name = 'xiaolin' WHERE id = 1 的流程如下:
1、执行器负责具体执行,会调用存储引擎的接口,通过主键索引树搜索获取 id = 1 这一行记录
如果 id=1 这一行所在的数据页本来就在 buffer pool 中,就直接返回给执行器更新
如果记录不在 buffer pool,将数据页从磁盘读入到 buffer pool,返回记录给执行器
2、执行器得到聚簇索引记录后,会看一下更新前的记录和更新后的记录是否一样：
如果一样的话就不进行后续更新流程
如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层,让 InnoDB 真正的执行更新记录的操作
3、开启事务,InnoDB 层更新记录前,首先要记录相应的 undo log,因为这是更新操作,需要把被更新的列的旧值记下来,也就是要生成一条 undo log,undo log 会写入 Buffer Pool 中的 Undo 页面,不过在修改该 Undo 页面前需要先记录对应的 redo log,所以 先记录修改 Undo 页面的 redo log,然后再真正的修改 Undo 页面
4、InnoDB 层开始更新记录,根据 WAL 技术,先记录修改数据页面的 redo log,然后再真正的修改数据页面.修改数据页面的过程是修改 Buffer Pool 中数据所在的页,然后将其页设置为脏页,为了减少磁盘I/O,不会立即将脏页写入磁盘,后续由后台线程选择一个合适的时机将脏页写入到磁盘
5、至此,一条记录更新完了
6、在一条更新语句执行完成后,然后开始记录该语句对应的 binlog,此时记录的 binlog 会被保存到 binlog cache,并没有刷新到硬盘上的 binlog 文件,在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘
7、事务提交(为了方便说明,这里不说组提交的过程,只说两阶段提交)
prepare 阶段：将 redo log 对应的事务状态设置为 prepare,然后将 redo log 刷新到硬盘
commit 阶段：将 binlog 刷新到磁盘,接着调用引擎的提交事务接口,将 redo log 状态设置为 commit(将事务设置为 commit 状态后，刷入到磁盘 redo log 文件)
至此,一条更新语句执行完成
转载:
https://zhuanlan.zhihu.com/p/522277628?utm_id=0
https://blog.csdn.net/qq_34827674/article/details/125389035
