mysql 执行过程日志详解，Innodb 引擎（undo,redo,binlog）

1、undo log 与 MVCC

undo log是 Innodb 引擎专属的日志，是记录每行数据事务执行前的数据。主要作用是用于实现MVCC版本控制，保证事务隔离级别的读已提交和读未提交级别。

2、redo log 与 Buffer Pool

InnoDB 内部维护了一个缓冲池，用于减少对磁盘数据的直接IO操作，并配合 redo log、内部的 change buffer 来实现异步的落盘，保证程序的高效执行。redo log 大小固定，采用循环写

 write pos 表示当前正在记录的位置，会向后记录， checkpoint 表示数据落盘的边界，也就是 checkpoint 与 write pos中间是已记录的，当 write pos写完 id_logfile_3后，会回到id_logfile_0循环写，而追上 checkpomnit 后则需要先等数据进行落盘，等待 checkponit向后面移动一段距离再写。redo log存储的内容是对数据页的修改逻辑。

3、bin log(Server 层)

redo log 因为大小固定，所以不能存储过多的数据，它只能用于未更新的数据落盘，而数据操作的备份恢复、以及主从复制是靠 bin log（如果数据库误删需要还原，那么需要某个时间点的数据备份以及bin log）。5.7默认记录的是操作语句涉及的每一行修改前后的行记录。

在更新到数据页缓存或者 Change Buffer 后，首先进行 redo log 的编写，编写完成后将 redo log 设为 prepare 状态，随后再进行 binlog 的编写，等到 binlog 也编写完成后再将 redo log 设置为 commit 状态。这是为了防止数据库宕机导致 binlog 没有将修改记录写入，后面数据恢复、主从复制时数据不一致。在断电重启后先检查 redo log 记录的事务操作是否为 commit 状态：

1、如果是 commit 状态说明没有数据丢失，判断下一个。

2、如果是 prepare 状态，检查 binlog 记录的对应事务操作（redo log 与 binlog 记录的事务操作有一个共同字段 XID，redo log 就是通过这个字段找到 binlog 中对应的事务的）是否完整（这点在前面 binlog 三种格式分析过，每种格式记录的事务结尾都有特定的标识），如果完整就将 redo log 设为 commit 状态，然后结束；不完整就回滚 redo log 的事务，结束。

三种格式：

1、Row（5.7默认）。记录操作语句对具体行的操作以及操作前的整行信息。缺点是占空间大。优点是能保证数据安全，不会发生遗漏。

2、Statement。记录修改的 sql。缺点是在 mysql 集群时可能会导致操作不一致从而使得数据不一致（比如在操作中加入了Now()函数，主从数据库操作的时间不同结果也不同）。优点是占空间小，执行快。

3、Mixed。会针对于操作的 sql 选择使用Row 还是 Statement。缺点是还是可能发生主从不一致的情况。

三个日志的比较（undo、redo、bin）

1、undo log是用于事务的回滚、保证事务隔离级别读已提交、可重复读实现的。redo log是用于对暂不更新到磁盘上的操作进行记录，使得其可以延迟落盘，保证程序的效率。bin log是对数据操作进行备份恢复（并不能依靠 bin log 直接完成数据恢复）。

2、undo log 与 redo log 是存储引擎层的日志，只能在 InnoDB 下使用；而bin log 是 Server 层的日志，可以在任何引擎下使用。

3、redo log 大小有限，超过后会循环写；另外两个大小不会。

4、undo log 记录的是行记录变化前的数据；redo log 记录的是 sql 的数据页修改逻辑以及 change buffer 的变更；bin log记录操作语句对具体行的操作以及操作前的整行信息（5.7默认）或者sql语句。

5、单独的 binlog 没有 crash-safe 能力，也就是在异常断电后，之前已经提交但未更新的事务操作到磁盘的操作会丢失，也就是主从复制的一致性无法保障，而 redo log 有 crash-safe 能力，通过与 redo log 的配合实现 "三步提交"，就可以让主从库的数据也能保证一致性。

6、redo log 是物理日志，它记录的是数据页修改逻辑以及 change buffer 的变更，只能在当前存储引擎下使用，而 binlog 是逻辑日志，它记录的是操作语句涉及的每一行修改前后的值，在任何存储引擎下都可以使用。

 

为什么 redo log 具有 crash-safe 的能力，而 binlog 没有？

redo log 是什么？

一个固定大小，“循环写”的日志文件，记录的是物理日志——“在某个数据页上做了某个修改”。

binlog 是什么？

一个无限大小，“追加写”的日志文件，记录的是逻辑日志——“给 ID=2 这一行的 c 字段加1”。

redo log 和 binlog 有一个很大的区别就是，一个是循环写，一个是追加写。也就是说 redo log 只会记录未刷盘的日志，已经刷入磁盘的数据都会从 redo log 这个有限大小的日志文件里删除。binlog 是追加日志，保存的是全量的日志。

当数据库 crash 后，想要恢复未刷盘但已经写入 redo log 和 binlog 的数据到内存时，binlog 是无法恢复的。虽然 binlog 拥有全量的日志，但没有一个标志让 innoDB 判断哪些数据已经刷盘，哪些数据还没有。

举个栗子，binlog 记录了两条日志：

给 ID=2 这一行的 c 字段加1
给 ID=2 这一行的 c 字段加1
在记录1刷盘后，记录2未刷盘时，数据库 crash。重启后，只通过 binlog 数据库无法判断这两条记录哪条已经写入磁盘，哪条没有写入磁盘，不管是两条都恢复至内存，还是都不恢复，对 ID=2 这行数据来说，都不对。

但 redo log 不一样，只要刷入磁盘的数据，都会从 redo log 中抹掉，数据库重启后，直接把 redo log 中的数据都恢复至内存就可以了。这就是为什么 redo log 具有 crash-safe 的能力，而 binlog 不具备。

当数据库 crash 后，如何恢复未刷盘的数据到内存中？

根据 redo log 和 binlog 的两阶段提交，未持久化的数据分为几种情况：

1、change buffer 写入，redo log 虽然做了 fsync 但未 commit，binlog 未 fsync 到磁盘，这部分数据丢失。
2、change buffer 写入，redo log fsync 未 commit，binlog 已经 fsync 到磁盘，先从 binlog 恢复 redo log，再从 redo log 恢复 change buffer。
3、change buffer 写入，redo log 和 binlog 都已经 fsync，直接从 redo log 里恢复。