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

一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块，最后到达存储引擎。 那么，一条更新语句的执行流程又是怎样的呢？以及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的操作。从头开始写，写到末尾就又回到开头循环写。

在这里插入图片描述

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原文

在这里插入图片描述

优点：不需要记录每一条SQL语句与每行的数据变化，日志会比较少，减少了磁盘IO，提高性能。 缺点：在某些情况下会导致master-slave中的数据不一致 row格式:

在这里插入图片描述

运行这条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技术内幕