从update语句看mysql的三种日志

写在前面

先大概了解一下mysql的三种日志

我们都知道mysql中有三种日志非常重要，undo log 、redo log 和binlog，简单来说，其中undo log和redo log与事务也有着密切关系。

undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。
redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；
binlog （归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制；

在介绍这三种日志前，我们结合一下实际场景，来说明三种日志的作用、记录顺序。

update语句的大概执行顺序：

1、连接器收到客户端update语句

2、因为是update语句，这里不再查询缓存（server层的查询缓存），直接到解析器，解析器解析出mysql关键字，如update、表名、where条件等，构建出语法树，并进行语法分析，判断语法是否合法。

3、解析出语法树，交给下一步预处理器，判断表字段和表是否存在。

4、优化器指定执行计划，决定是否使用索引，使用哪个索引。

5、执行器执行语句，进行更新。

这就是update语句具体的执行顺序了，当然这里面还涉及到三种日志的生成，接下来我们先具体了解下三种日志的概念。

undo log

undo log又被叫做是回滚日志，主要记录的就是执行语句的反义，比如如果是update语句，就记录update前的数据，delete语句就记录成insert数据，insert语句就记录成delete数据，总之undo log就是要记录和你操作相反的操作。

为什么要这样做？

为了保证事务的回滚！

要知道，mysql每次增删改操作都是会默认（隐式）开启一个事务的，是否自动开启事务由参数(autocommit)决定，默认也是开启的，这时候就要考虑，如果事务执行过程中失败了怎么办？为了保证数据的原子性，肯定是要进行数据回滚的，所以undolog才要记录相反操作，当需要进行事务回滚，就执行undo log中记录的操作即可。

除了保证事务还有其他作用么

undo log结合read view 实现MVCC。

undo log记录的时候每条记录都会有隐藏列(一个 roll_pointer 指针和一个 trx_id 事务id)

roll_pointer 指针：将undo log串联起来，形成版本链。

trx_id 事务id：记录当前记录被哪个事务修改的。

对事务隔离级别是可重复读（RR）和读已提交（RC）来说，他们的快照读都是通过Undo log +read view来实现的，两种隔离级别的区别就在于read view的生成时机不同：

RR：每次事务开启生成一次read view

RC：每次select都生成一次read view

具体可参考《mysql中的事务隔离级别》–待后续补充哈。

undo log的两大作用

至此，undo log的两大作用已经很清晰了

1、保证事务原子性，用于事务回滚。

2、实现MVCC的重要组成部分

undo log的刷盘

上面已经知道的undo log的作用，但只是作用，想一下，undo log日志内容肯定不可能是存在内存中的，不可能mysql重启下，把undo log给重启没了，所以就要对undo log进行持久化（刷盘），那具体是怎么刷盘的，是直接写入磁盘文件么？

undo log物理文件

针对undo log物理文件这块，不同的mysql版本有不同的设计，可以先查看一下自己mysql对undo log的参数设置：

show VARIABLES like '%innodb_version%'

我这里的mysql版本是5.7.18，关于undo log日志的设置如下：

show VARIABLES like '%undo%'
   
Variable_name               Value   
innodb_max_undo_log_size	1073741824（1G）
innodb_undo_directory	     ./
innodb_undo_log_truncate	OFF
innodb_undo_logs	        128
innodb_undo_tablespaces	    0

• innodb_undo_directory ： undo log文件存放位置

• innodb_max_undo_log_size： 设置单个独立 undo log 表空间大小。

• innodb_undo_log_truncate：：开关状态控制 purge 线程进行空间回收和 undo file 的重新初始化，该线程触发会受 innodb_max_undo_log_size 、truncate 频率等影响。
  必要条件：已设置独立表空间且独立表空间个数大于等于2个。

• innodb_undo_tablespaces：是否启用独立 undo log 表空间。= 0： 开启，此时 undo log 存放于数据文件 ibdata 中。= 1：不开启，此时 undo log 存放于 innodb_undo_directory 目录下如：undo001、undo002 的独立文件中。其大小由参数 innodb_max_undo_log_size 控制，truncate 默认 10MB。

  注意：到了MySQL5.7版本，可以在线truncate undo tablespace（之前版本安装数据库之后不能更改undo tablespace）

  在MySQL8.0中，InnoDB再进一步，对undo log做了进一步的改进：

  1. 从8.0.3版本开始，默认undo tablespace的个数从0调整为2，也就是在8.0版本中，独立undo tablespace被默认打开。修改该参数为0会报warning并在未来不再支持；
  2. 无需从space_id 1开始创建undo tablespace，这样解决了In-place upgrade或者物理恢复到一个打开了Undo tablespace的实例所产生的space id冲突。不过依然要求undo tablespace的space id是连续分配的；

• innodb_undo_logs：自定义 rollback segment（段）数量。

你以为看到上面的配置文件地址就是undo log直接刷盘到配置的位置了么？先了解一下buffer pool ！！

什么是buffer pool

mysql的数据最终都是存放在物理文件中的，试想一下每次读取都要从文件中进行读取么？怎么能提升读取效率？肯定是加缓存了，首先要明确一点，mysql读取数据是一条一条的读取么？不是的，每次读取其实都是读取一页（16K）的数据的！也就是说mysql会读取一页的数据，然后进行缓存，这样下次再进行读取的时候，如果数据已经存在缓存中，直接进行返回就可以了！这样肯定是要比去物理文件中读取效率要高很多的！

buffer pool就是用来缓存数据的，也就是innodb引擎设计的缓冲池！

当然buffer pool的作用不仅仅是缓存查询数据这么简单，其实修改数据也是在buffer pool的。

buffer pool中都有什么？

前面已经说了，mysql每次都是读取16k的数据到buffer pool，这也是因为在mysql启动的时候Innodb会申请一片连续空间，按照每页16KB的大小进行分页，也就是我们说的缓存页。

缓存页也是分很多种类的，主要有数据页、undo log页，索引页等等

看！这里有undo log页，也就是每次生成的undo log日志，其实是先缓存到buffer pool中去！也就是说，每次生成一条新的undo log，就会存储在buffer pool中的undo log页中，这里再说一下，每个缓存页都有脏页的概念，每次从buffer pool刷新到物理文件中，只需要将脏页进行刷新即可。这也是减少磁盘IO次数的一种应用，先将脏数据（和磁盘数据不一致）进行缓存，后续一次性将脏数据进行持久化。

到这里，我们知道了undo log的存储实际上是到了buffer pool这一步，那么问题又来了，buffer pool毕竟还是缓存，那缓存是什么时候被刷新到磁盘中了呢？

这里就要引入mysql 的第二种日志redo log 了！

WAL技术

因为内存总是不可靠的，修改数据记录undo log后，innodb先在buffer pool中进行缓存（标记为脏页），这时候其实还要进行日志的记录！也就是说这时候对数据页的修改是要以redo log的形式先记录下来，这时候才算是修改完成，然后后续由innodb引擎在适当的时机，对脏页进行刷盘，这也就是mysql中WAL（Write ahead logging）技术（任何操作先以日志的形式记录下来，然后在合适的时机进行刷盘），同时WAL技术对数据是顺序写，因此写入的速度可以得到保证。

Redo log是物理日志，主要记录了某个数据页中做了什么操作，每次事务执行都会产生一条或者多条redo log日志（每次修改操作都记录一次），这样在事务提交的时候，只需要将redo log进行持久化即可， 而不需要等undo log进行刷盘，这也是redo log为什么可以保证mysql数据的持久性的原因了。

redo log和undo log有什么关系？

undo log 记录的是数据修改前的数据，主要用来进行回滚。

redo log记录的是数据修改后的数据，主要用来数据的持久性。

crash-safe

如果是事务提交前mysql发生崩溃，重启后可以通过undo log进行数据恢复（回滚），如果事务提交后mysql发生崩溃，重启后可以通过redo log进行数据的恢复（持久性）。这个保障能力也就是mysql的crash-safe（崩溃恢复）。

前面已经知道了undo log的刷盘其实最终靠的是innodb的刷盘策略，不过在undo log刷盘前，就已经通过redo log进行了数据的持久化操作，那redo log是直接写到磁盘上了么？

redo log刷盘

实际上redo log也并不是每次都进行刷盘的，如果每次执行事务都进行redo log 的刷盘，那磁盘的IO操作无疑是很大的，redo log也有自己的缓存（redo log buffer)，也就是说每次生成一条redo log 日志，都先写入**redo log buffer(这个和buffer pool不是一个地方的！）**中，然后在合适的时机进行进行刷盘。

这里贴一张很经典的图啦

可以看到redo log要么直接刷盘，要么到buffer，要么到pageCache，那具体实现是怎么回事？

通过参数innodb_flush_log_at_trx_commit 控制！

show VARIABLES like '%innodb_flush_log_at_trx_commit%'
innodb_flush_log_at_trx_commit  2

innodb_flush_log_at_trx_commit

查看mysql数据库中该参数值，该参数值可以配置为0,1,2。

0：代表每次事务提交时，redo log缓存到redo log buffer

1：代表每次事务提交时，redo log直接进行刷盘

2：代表每次事务提交时，redo log 刷新到pageCache中，等待系统后续刷盘（由系统配置脏页的回写行为的参数控制）

innodb_flush_method（了解）

innodb_flush_method这个参数控制着innodb数据文件及redo log的打开、刷写模式。这个参数有三个值：fdatasync(默认)，O_DSYNC，O_DIRECT。
fdatasync：默认是fdatasync，调用fsync()去刷数据文件与redo log的buffer。写数据时，write这一步并不需要真正写到磁盘才算完成（可能写入到操作系统buffer中就会返回完成），真正完成是flush操作，buffer交给操作系统去flush,并且文件的元数据信息也都需要更新到磁盘。
O_DSYNC：为O_DSYNC时，innodb会使用O_SYNC方式打开和刷写redo log,使用fsync()刷写数据文件。写日志操作是在write这步完成，而数据文件的写入是在flush这步通过fsync完成
O_DIRECT：为O_DIRECT时，innodb使用O_DIRECT打开数据文件，使用fsync()刷写数据文件跟redo log。数据文件的写入操作是直接从mysql innodb buffer到磁盘的，并不用通过操作系统的缓冲，而真正的完成也是在flush这步,日志还是要经过OS缓冲

redo log的循环写

现在redo log已经成功走到buffer中，当然最终还是要进行刷盘，存储到物理文件中，那redo log物理文件是怎么存储的？有大小限制么？

redo log 是以循环写的方式工作的，从头开始写，写到末尾就又回到开头，相当于一个环形。

show VARIABLES like '%innodb_log_files_in_group%'
show VARIABLES like '%innodb_log_file_size%'

该值默认是2，表示重做日志文件组( redo log Group），该组由有 2 个 redo log 文件组成，这两个 redo 日志的文件名叫ib_logfile0和ib_logfile1…，每个日志文件大小是固定的，大小为1G，每次写满一个文件就写另一个文件，日志组都满了，就从头开始覆盖写入，如下图

• write pos 和 checkpoint 的移动都是顺时针方向；
• write pos ～ checkpoint 之间的部分（图中的红色部分），用来记录新的更新操作；
• check point ～ write pos 之间的部分（图中蓝色部分）：待落盘的脏数据页记录；

hold on，上面好想一直在说redo log的刷盘，那undo log到底是怎么刷盘的？

其实，mysql在事务提交时，主要针对redo log进行了提交（innodb_flush_log_at_trx_commit参数做刷盘策略），但是对undo log页并没有做强制性刷盘要求，也就是说buffer pool中的脏页刷盘其实并没有强制要求，那具体buffer pool中的脏页什么时候进行刷盘呢？对于脏页来说，其实更多的是参考innodb引擎的检查点机制和系统负载等因素。

检查点（Checkpoint）：在适当的时机（如系统空闲时、redo log空间不足时等），InnoDB会触发检查点操作，将部分或全部已提交事务的undo log从内存刷到磁盘，并回收不再需要的空间。这个过程有助于减少崩溃恢复时需要重做的日志量，同时释放缓冲池空间。

为什么undo log不随着事务提交进行刷盘？不进行刷盘，那mysql宕机，是不是就存在数据丢失不一致的情况？

这其实和上面说的undo log和redo log性质有关系，

redo log

如果事务尚未开始写入redo log（即没有到达prepare阶段），那么这部分事务的数据不会有任何记录在磁盘上，因此不会造成数据不一致。
如果事务已经写入了redo log但还没刷盘，InnoDB会在重启后扫描redo log，回放未提交的事务，恢复这些事务对数据页的修改，以达到一致性状态。但是，由于事务未提交，所以这部分回放的修改不会对外可见，直到事务被确认为已提交。

undo log

对于未提交的事务，其对应的undo log也不会被持久化。如果MySQL宕机，这部分信息丢失，意味着无法通过undo log回滚未完成的事务。
在恢复期间，由于redo log的信息不完整，InnoDB可能会发现某些事务的状态不明。在这种情况下，它会使用所谓的“部分回滚”（in-place rollback）机制，撤销那些没有完整redo log记录的事务对数据的影响。

总的来说，虽然MySQL宕机可能导致redo log和undo log未被完全持久化，但是InnoDB的崩溃恢复机制尽可能地减少了数据丢失的风险。如果想最大化确保数据安全性，innodb_flush_log_at_trx_commit参数也可以设置为1。

前面已经提到了redo log的prepare阶段，这个prepare阶段是干什么的？为什么会有这个阶段？

bin log基础概念

先了解一下mysql的最后一种日志bin log吧。

和redo log、undo log不一样，bin log属于是server层日志，也就说无论mysql的引擎是Innodb还是memory，都是会有bin log日志的。bin log日志属于逻辑日志，记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作。

redo log 和 binlog 有什么区别？

这两个日志有四个区别。

适用对象不同

• binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用；
• redo log 是 Innodb 存储引擎实现的日志；

文件格式不同：

• binlog 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED，区别如下：
  • STATEMENT：每一条修改数据的 SQL 都会被记录到 binlog 中（相当于记录了逻辑操作，所以针对这种格式， binlog 可以称为逻辑日志），主从复制中 slave 端再根据 SQL 语句重现。但 STATEMENT 有动态函数的问题，比如你用了 uuid 或者 now 这些函数，你在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致复制的数据不一致；
  • ROW：记录行数据最终被修改成什么样了（这种格式的日志，就不能称为逻辑日志了），不会出现 STATEMENT 下动态函数的问题。但 ROW 的缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使 binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句而已；要注意的是如果开启了mysql的主从复制，bin log的格式必须设置为row。
  • MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式；
• redo log 是物理日志，记录的是在某个数据页做了什么修改，比如对表空间中的X 数据页 Y 偏移量的地方做了T更新；

写入方式不同：

• binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。
• redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。

用途不同：

• binlog 用于备份恢复、主从复制；
• redo log 用于掉电等故障恢复。

bin log 刷盘

知道了bin log 是什么，看下bin log 是怎么刷盘的吧

简单来说，bin log也是有buffer的！mysql为每个线程都开辟了一块 bin log buffer（bin log cache)(位于server层)，每个事物执行过程中，将bin log写入bin log cahce中，事物提交时，再将cache写入到bin log文件中，要注意的是无论这个事务有多大，都会保证将产生的bin log 一次性写入，bin log是不能被拆开的！

bin log cache什么时候写入文件？

mysql参数binlog_cache_size中定义了bin log cache的大小（32Kb），超过这个大小就会触发写入操作，还有当事务提交时，也会将cache中的日志进行写入操作。

show VARIABLES like '%binlog_cache_size%';

bin log cache是直接写入磁盘么？

mysql参数sync_binlog定义了cache中的bin log怎么写入磁盘中。

show VARIABLES like '%sync_binlog%';

首先要明确cache中的bin log写入磁盘调用的write方法，这个方法只是说将cache中的内容写入到系统的page cache中，而系统的page cache到磁盘，调用的是fsync方法。

• sync_binlog = 0 (默认）的时候，表示每次提交事务都只 write，不 fsync，后续交由操作系统决定何时将数据持久化到磁盘；
• sync_binlog = 1 的时候，表示每次提交事务都会 write，然后马上执行 fsync；
• sync_binlog =N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

如果能容少量事务的 binlog 日志丢失的风险，为了提高写入的性能，一般会 sync_binlog 设置为 100~1000 中的某个数值。

mysql的两阶段提交

bin log了解完了，接着说redo log 的prepare阶段，prepare阶段其实就是mysql的二阶段提交。

看完前面的三种日志介绍，你会发现，其实刷盘的主要就是两种日志，bin log和redo log，但是你会发现，这两种日志的刷盘都是各自有各自的策略，那怎么保证日志都能够刷盘成功，毕竟如果出现某个日志刷盘不成功，可能就会造成数据的不一致情况。

比如如果bin log刷盘成功，mysql宕机，redo log 没有刷盘成功，mysql重启后，会根据redo log 进行数据恢复，因为redo log 没有刷盘成功，数据是会进行事务回滚的，但是bin log记录的是操作语句，因此从库是会执行的，这时候就会造成主从库的数据不一致情况。

比如如果redo log刷盘成功，mysql宕机，bin log没有刷盘成功，mysql重启后，根据redo log进行数据恢复，这时候事务是会进行提交的，但是因为没有bin log记录，从库数据也会和主库不一致。

那mysql是怎么解决这个问题的呢？

两阶段提交！

两阶段提交将单个事务的提交分成了两个阶段，准备(prepare)阶段和提交（commit)阶段。在开启bin log的情况下，mysql内部采用了XA事务：

prepare阶段：将事务ID（XID）写入到redo log中，将redo log的事务状态设置为prepare状态，同时开始redo log 的刷盘（innodb_flush_log_at_trx_commit 参数控制）。

commit阶段：将XID写入bin log ，然后将bin log刷盘（sync_binlog 控制），调用存储引擎提交事务接口，同时将redo log的状态改为commit。

两阶段中间发生mysql宕机会发生什么？

如果redo log状态已经是commit状态，代表此时redo log 和bin log 都已完成刷盘，此时宕机也无所谓了。

其他情况下redo log 都是prepare状态，那么此时就分两种情况了：bin log完成刷盘和bin log没有完成刷盘。

此时mysql重启，会读取redo log进行数据恢复，

如果此时redo log处于prepare状态，将会拿着XID去bin log中进行查找，如果查找到了，说明事务已完成提交，此时就可以提交事务，完成数据commit。

如果XID不存在bin log中，说明此时bin log没有进行提交，也就说事务没有提交成功，此时redo log进行事务回滚，保证和bin log中数据一致。

这就是mysql的XA两阶段提交，也可以说是分布式事务的实现基础了，后面有时间再介绍下分布式事务其他的实现方式。

update语句和日志的完整流程

1. 执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取 记录：
   • 如果记录所在的数据页本来就在 buffer pool 中，就直接返回给执行器更新；
   • 如果记录不在 buffer pool，将数据页从磁盘读入到 buffer pool，返回记录给执行器。
2. 执行器得到记录后，会看一下更新前的记录和更新后的记录是否一样：
   • 如果一样的话就不进行后续更新流程；
   • 如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正的执行更新记录的操作；
3. 开启事务， InnoDB 层更新记录前，首先要记录相应的 undo log，因为这是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面，不过在内存修改该 Undo 页面后，需要记录对应的 redo log。
4. InnoDB 层开始更新记录，会先更新内存（同时标记为脏页），然后将记录写到 redo log 里面，这个时候更新就算完成了。为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。这就是 WAL 技术，MySQL 的写操作并不是立刻写到磁盘上，而是先写 redo 日志，然后在合适的时间再将修改的行数据写到磁盘上。
5. 至此，一条记录更新完了。
6. 在一条更新语句执行完成后，然后开始记录该语句对应的 binlog，此时记录的 binlog 会被保存到 binlog cache，并没有刷新到硬盘上的 binlog 文件，在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。
7. 事务提交（为了方便说明，这里不说组提交的过程，只说两阶段提交）：
   • prepare 阶段：将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到硬盘；
   • commit 阶段：将 binlog 刷新到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit（将事务设置为 commit 状态后，刷入到磁盘 redo log 文件）；
8. 至此，一条更新语句执行完成。