MySQL系列之redo log、undo log和binlog详解

事务的实现

redo log保证事务的持久性，undo log用来帮助事务回滚及MVCC的功能。

InnoDB存储引擎体系结构

redo log

Write Ahead Log策略

事务提交时，先写重做日志再修改页；当由于发生宕机而导致数据丢失时，就可以通过重做日志来完成数据的恢复。

• InnoDB首先将重做日志信息先放到重做日志缓存
• 按一定频率刷新到重做日志文件

重做日志文件： 在默认情况，InnoDB存储引擎的数据目录下会有两个名为ib_logfile1和ib_logfile2的文件。每个InnoDB存储引擎至少有1个重做日志文件组(group)，每个文件组下至少有2个重做日志文件。

下面图一，很好说明重做日志组以循环写入方式运行，InnoDB存储引擎先写ib_logfile1，当达到文件最后时，会切换至重做日志文件ib_logfile2.

而图2，增加一个OS Buffer，有助于理解fsync过程。

关于log group，称为重做日志组，是一个逻辑上的概念。InnoDB存储引擎实际只有一个log group。

log group中第一个redo log file，其前2KB部分保存4个512字节大小块：

重做日志缓冲刷新到磁盘

下面三种情况刷新：

• Master Thread每一秒将重做日志缓冲刷新到重做日志文件
• 每个事务提交时会将重做日志缓冲刷新到重做日志文件
• 当重做日志缓冲池剩余空间小于1/2时，重做日志刷新到重做日志文件

补充上述三种情况第二种，触发写磁盘过程由参数innodb_flush_log_at_trx_commit控制，表示提交(commit)操作时，处理重做日志的方式。

参数innodb_flush_log_at_trx_commit有效值有0、1、2

• 0表示当提交事务时，并不将事务的重做日志写入磁盘上日志文件，而是等待主线程每秒刷新。
• 1表示在执行commit时将重做日志缓冲同步写到磁盘，即伴有fsync的调用
• 2表示将重做日志异步写到磁盘，即写到文件系统的缓存中。不保证commit时肯定会写入重做日志文件。

0，当数据库发生宕机时，部分日志未刷新到磁盘，因此会丢失最后一段时间的事务。
2，当操作系统宕机时，重启数据库后会丢失未从文件系统缓存刷新到重做日志文件那部分事务。

下图有助于理解

重做日志块

在InnoDB存储引擎中，重做日志都是以512字节进行存储的。意味着重做日志缓存、重做日志文件都是以块(block)的方式进行保存的，每块512字节。

重做日志头12字节，重做日志尾8字节，故每个重做日志块实际可以存储492字节。

重做日志格式

redo log是基于页的格式来记录的。默认情况下，innodb的页大小是16KB(由 innodb_page_size变量控制)，一个页内可以存放非常多的log block(每个512字节)，而log block中记录的又是数据页的变化。

log body的格式分为4部分：

• redo_log_type：占用1个字节，表示redo log的日志类型。
• space：表示表空间的ID，采用压缩的方式后，占用的空间可能小于4字节。
• page_no：表示页的偏移量，同样是压缩过的。
• redo_log_body表示每个重做日志的数据部分，恢复时会调用相应的函数进行解析。例如insert语句和delete语句写入redo log的内容是不一样的。

如下图，分别是insert和delete大致的记录方式。

redo日志恢复

下面LSN(Log Sequence Number)代表checkpoint，当数据库在LSN为10000时发生宕机，恢复操作仅恢复LSN10000-LSN13000范围内日志

undo log

undo log的作用

undo是逻辑日志，只是将数据库逻辑地恢复到原来的样子；所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能不大相同。

undo log有两个作用：提供回滚和多个行版本控制(MVCC)。

InnoDB存储引擎回滚时，对于每个INSERT，会完成一个DELETE；对于每个DELETE，会执行一个INSERT；对于每个UPDATE，会执行一个相反的UPDATE，将修改前的行放回去。

MVCC： 当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。

undo log的存储方式

innodb存储引擎对undo的管理采用段的方式。rollback segment称为回滚段，每个回滚段中有1024个undo log segment。

在以前老版本，只支持1个rollback segment，这样就只能记录1024个undo log segment。后来MySQL5.5可以支持128个rollback segment，即支持128*1024个undo操作，还可以通过变量 innodb_undo_logs (5.6版本以前该变量是 innodb_rollback_segments )自定义多少个rollback segment，默认值为128。

undo log默认存放在共享表空间中。

事务提交undo log处理过程

当事务提交时，InnoDB存储引擎会做以下两件事：

• 将undo log放入一个列表中，以供之后的purge使用，是否可以最终删除undo log及所在页由purge线程来判断
• 判断undo log 所在的页是否可以重用，若可以，分配给下个事务使用

当事务提交时，首先将undo log放入链表中，然后判断undo页的使用空间是否小于3/4，若是，则表示该undo页可以被重用，之后新的undo log记录在当前undo log的后面

undo log分为：

• insert undo log
• update undo log

因为事务隔离性，insert undo log对其他事务不可见，所以该undo log可以在事务提交后直接删除，不需要进行purge操作。

update undo log记录的是对delete和update操作产生的undo log。该undo log可能需要提供MVCC机制，因此不能提交时就进行删除

update分为两种情况：

• date的列如果不是主键列，在undo log中直接反向记录是如何update的。即update是直接进行的。
• update主键的操作可以分为两步：
• 首先将原主键记录标记为已删除，因此需要产生一个类型为TRX_UNDO_DEL_MARK_REC的undo log
• 之后插入一条新的记录，产生一个类型为TRX_UNDO_INSERT_MARK_REC的undo log

InnoDB purge时，会先从history列表找undo log，然后再从undo page中找undo log；可以避免大量随机读取操作，从而提高purge效率。

MVCC(多版本并发控制)

MVCC其实就是在每一行记录后面增加两个隐藏列，记录创建版本号和删除版本号，而每一个事务在启动的时候，都有一个唯一的递增的版本号。

MVCC只在REPEATABLE READ 和READ COMMITTED两个隔离级别下工作。读未提交不存在版本问题，序列化则对所有读取行加锁。

示例：

插入操作：记录的创建版本号就是事务版本号

如插入一条记录，事务id假设是1，则创建版本号也是1

id name create version delete version 1 test 1

更新操作：先标记旧版本号为已删除，版本号就是当前版本号，再插入一条新的记录

如事务2把name字段更新
update table set name = 'new test' where id = 1;

原来的记录被标记删除，删除版本号为2，并插入新记录，创建版本号为2

id name create version delete version 1 test 1 2 1 new test 2

删除操作：把事务版本作为删除版本号

如事务3把记录删除
delete from table where id = 1;

id name create version delete version 1 test 2 3

查询操作

需满足以下两个条件的记录才能被事务查询出来：

• InnoDB只查找版本早于当前事务版本的数据行
• 行的删除版本要么未定义，要么大于当前版本号，这可以确保事务读取到的行，在事务未开始之前未被删除

MVCC好处：减少锁的争用，提升性能

binlog

二进制文件概念及作用

二进制文件(binary log)记录了对MySQL数据库执行更改的所有操作(不包含SELECT、SHOW等，因为对数据没有修改)

二进制文件主要几种作用：

• 恢复：某些数据的恢复需要二进制日志
• 复制： 通过复制和执行二进制日志使一台远程的MySQL(slave)与另一台MySQL数据库(master)进行实时同步
• 审计： 用户可以通过二进制日志中信息来进行审计，判断是否有对数据库进行注入的攻击

二进制文件三个格式

MySQL 5.1开始引入binlog_format参数，该参数可设值有STATEMENT、ROW和MIX

• STATEMENT： 二进制文件记录的是日志的逻辑SQL语句
• ROW：记录表的行更改情况。如果设置了ROW模式，可以将InnoDB事务隔离级别设为READ_COMMITTED，以获得更好的并发性
• MIX：MySQL默认采用STATEMENT格式进行二进制文件的记录，但在一些情况下会使用ROW，可能的情况有：
• 表的存储引擎为NDB，这时对表DML操作都以ROW格式进行
• 使用了UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT()等不确定函数
• 使用了INSERT DELAY语句
• 使用了用户定义函数
• 使用了临时表

redo log和二进制文件区别

(二进制文件用来进行POINT-IN-TIME(PIT))的恢复及主从复制环境的建立。

• 二进制文件会记录所有与MySQL数据库有关的日志记录，包括InnoDB、MyISAM等其他存储引擎的日志。而InnoDB存储引擎的重做日志只记录有关该存储引擎本身的事务日志。
• 记录的内容不同，无论用户将二进制日志文件记录的格式设为STATEMENT、ROW或MIXED，其记录的都是关于一个事务的具体操作内容，即该日志是逻辑日志。而InnoDB存储引擎的重做日志文件记录的是关于每个页的更改的物理情况。
• 此外，写入的时间页不同，二进制日志文件仅再事务提交前进行提交，即只写磁盘一次，不论这时该事务多大。而在事务进行的过程中，却不断有重做日志条目(reod entry)被写入到重做日志文件中。

group commit

若事务为非只读事务，则每次事务提交时需要进行一次fsync操作，以此保证重做日志都已经写入磁盘。但磁盘fsync性能有限，为提高磁盘fsync效率，当前数据库都提供group commit功能，即一次可以刷新确保多个事务日志被写入文件。

对InnoDB group commit，进行两阶段操作：

• 修改内存中事务对应的信息，并且将日志写入重做日志缓冲
• 调用fsync将确保日志都从重做日志缓冲写入磁盘

InnoDB1.2前，开启二进制文件，group commit功能失效问题：

开启二进制文件后，其步骤如下：
1）当事务提交时，InnoDB存储引擎进行prepare操作
2）MySQL数据库上层写入二进制文件
3）InnoDB将日志写入重做日志文件

a）修改内存中事务对应的信息，并将日志写入重做日志缓冲b）调用fsync将确保日志都从重做日志缓冲写入磁盘

其中在保证MySQL数据库上层二进制文件的写入顺序，和InnoDB事务提交顺序一致，MySQL内部使用了prepare_commit_mutex锁，从而步骤3）中a）步不可以在其他事务执行步骤b）时进行，从而导致roup commit功能失效。

解决方案便是BLGC(Binary Log Group Commit)

MySQL 5.6 BLGC实现方式分为三个阶段：

• Flush阶段：将每个事务的二进制文件写入内存
• Sync阶段：将内存中的二进制刷新到磁盘，若队列有多个事务，那么仅一次fsync操作就完成了二进制日志的写入，这就是BLGC
• Commit阶段：leader根据顺序调用存储引擎层事务提交，由于innodb本就支持group commit，所以解决了因为锁 prepare_commit_mutex 而导致的group commit失效问题。