Mysql日志-RedoLog、UndoLog和BinLog的关系捋顺

朱雀 2022-10-16 07:23 186阅读 0赞

一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块，最后到达存储引擎。  
那么，一条更新语句的执行流程又是怎样的呢？以及MySQL可以恢复到半个月内任意一秒的状态，这是怎样做到的呢？

InnoDB是事务的存储引擎,其通过Force Log at Commit机制实现事务的持久性,即当事务提交(COMMIT)时,必须先将该事务的所有日志写入到重做日志文件进行持久化,待事务的 COMMIT操作完成才算完成。这里的日志是指重做日志,在InnoDB存储引擎中,由两部分组成,即**redo log和undo log**。redo log用来保证事务的持久性,undo log用来帮助事务回滚及Mvcc的功能。redo log基本上都是顺序写的,在数据库运行时不需要对redo log的文件进行读取操作。而undo log是需要进行随机读写的。

## WAL: Write-Ahead Logging ##

先写日志，再写磁盘。具体说，当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log里面，并更新内存，这个时候更新计算完成了。同时InnoDB引擎会在在系统比较空闲的时候，将这个操作记录更新到磁盘里。  
WAL机制主要得益于两个方面  
1.redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快；  
2.组提交机制，可以大幅度降低磁盘的 IOPS 消耗。  
只要redo log和binlog保证持久化到磁盘，就能确保MySQL异常重启后，数据可以恢复。

## REDO LOG（redo log保证事务的持久性） ##

redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。

redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。

InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么总共就可以记录4GB的操作。从头开始写，写到末尾就又回到开头循环写。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NTE5NTg3_size_16_color_FFFFFF_t_70]  
write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。  
write pos和checkpoint之间的是还空着的部分，可以用来记录新的操作。如果write pos追上checkpoint，表示日志满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint推进一下。  
有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为**crash-safe**。

## checkpoint机制 ##

**checkpoint**：将缓冲池中的脏页刷回到磁盘。  
InnoDB存储引擎内部，两种checkpoint，分别为:  
1.Sharp Checkpoint  
2.Fuzzy Checkpoint  
**Sharp Checkpoint**发生在数据库关闭时，将所有的脏页都刷新回磁盘，这是默认的工作方式，即参数：innodb\_fast\_shutdown=1。  
在数据库运行时，InnoDB存储引擎内部采用Fuzzy Checkpoint，只刷新一部分脏页。  
**Fuzzy Checkpoint**的几种情况:  
1.MasterThread Checkpoint  
异步刷新，每秒或每10秒从缓冲池脏页列表刷新一定比例的页回磁盘。异步刷新，即此时InnoDB存储引擎可以进行其他操作，用户查询线程不会受阻。  
2.FLUSH\_LRU\_LIST Checkpoint  
InnoDB存储引擎需要保证LRU列表中差不多有100个空闲页可供使用。在InnoDB 1.1.x版本之前，用户查询线程(mysql5.6之后放在了单独的进程Page Cleaner中进行)会检查LRU列表是否有足够的空间操作。如果没有，根据LRU算法，溢出LRU列表尾端的页，如果这些页有脏页，需要进行checkpoint。  
设置参数：innodb\_lru\_scan\_dept：控制LRU列表中可用页的数量，该值默认1024  
3.Async/Sync Flush Checkpoint  
指重做日志不可用的情况，需要强制刷新页回磁盘，此时的页时脏页列表选取的。  
这种情况是保证重做日志的可用性，说白了就是，重做日志中可以循环覆盖的部分空间太少了，换种说法，就是极短时间内产生了大量的redo log。

## UNDO LOG(undo log保证事务的一致性) ##

重做日志记录了事务的行为,可以很好地通过其对页进行“重做”操作。但是事务有时还需要进行回滚操作,这时就需要undo。因此在对数据库进行修改时, InnoDB存储引擎不但会产生redo,还会产生一定量的undo。  
redo存放在重做日志文件中,与redo不同,undo存放在数据库内部的一个特殊段(segment)中,这个段称为undo段(undo segment)。undo段位于共享表空间内。  
除了回滚操作,undo的另一个作用是MVCC,即在 InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时,若该记录已经被其他事务占用,当前事务可以通过undo读取之前的行版本信息,以此实现非锁定读取。  
**undo log会产生redo log,也就是undo log的产生会伴随着 redo log的产生,这是因为undo log也需要持久性的保护。**

## BINLOG ##

Server层也有自己的日志，称为binlog(归档日志)。  
binlog是MySQL数据库的二进制日志，用于记录用户对数据库操作的SQL语句（(除了数据查询语句）信息。

**binlog的三种格式对比**  
statement、row、mixed(前两种格式的混合)。  
如果要在表中删除一行数据的话，我们来看看这个delete语句的binlog是怎么记录的。  
statement->记录的是sql原文  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NTE5NTg3_size_16_color_FFFFFF_t_70 1]  
优点：不需要记录每一条SQL语句与每行的数据变化，日志会比较少，减少了磁盘IO，提高性能。  
缺点：在某些情况下会导致master-slave中的数据不一致  
row格式:![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NTE5NTg3_size_16_color_FFFFFF_t_70 2]  
运行这条delete命令产生了一个warning，原因是当前binlog设置的是statement格式，并且语句中有limit，所以这个命令可能是unsafe的。  
在主库执行这条SQL语句的时候，用的是索引a；而在备库执行这条SQL语句的时候，却使用了索引t\_modified。  
优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。  
缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨。

## REDO LOG 和 BINLOG 日志有以下三点不同: ##

1.redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。  
2.redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。  
3.redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

\[资料来源\]  
1.Mysql实战45讲-丁奇  
2.MySQL技术内幕

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NTE5NTg3_size_16_color_FFFFFF_t_70]: /images/20221014/d06a9fa8cbfe4988a24cbfd39b0a72d7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NTE5NTg3_size_16_color_FFFFFF_t_70 1]: /images/20221014/6cd59fa0d0124fcba8e1eb83b4df1e82.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1NTE5NTg3_size_16_color_FFFFFF_t_70 2]: /images/20221014/7a0ff5b99f7e448093eda0a1a278d5f9.png