MySQL - 日志

1. 回滚日志（undo log）

作用：保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

内容：逻辑格式的日志（当delete一条记录是，记录一条对应的insert记录，反之亦然），在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态

释放：当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，通过判断是否由其他事务在使用undo的版本信息，决定是否可以清理

1）保证原子性，在数据修改的时候，不仅记录了redo log，还记录了对应的undo，如果因为某些原因事务失败而回滚，可以借助该undo进行回滚。

2）当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚

3）当读取带某一行的其他事务锁定时，它可以从undo log中分析出改行记录以前的数据是什么，从而提供该行版本信息，让用户实现MVCC

2. 重做日志（redo log）

作用：确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启MySQL时，根据redo log进行重做，从而达到事务的持久性这一特性

内容：物理格式的日志，记录的是物理数据页面的修改的信息

产生：事务开始之后就产生redo log，不是随着事务的提交才写入的

释放：当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，redo log的空间就可以重用（被覆盖）

3. 二进制日志（bin log）

作用：用于在主从复制中，从库利用主库上的binlog进行重播，实现主从同步；用于数据库的基于时间点的还原。

内容：逻辑格式的日志，可以简单认为就是执行过的事务中的sql语句。 而且还包括了执行的sql语句（增删改）反向的信息

产生：事务提交的时候，一次性将事务中的sql语句（一个事务可能对应多个sql语句）按照一定的格式记录到binlog中（对于较大事务的提交，可能会变得比较慢一些）

注意：与redo log的差异：redo log不是在事务提交的时候刷新到磁盘，因此对于事务的提交，即便是较大的事务，提交（commit）都是很快的

释放：binlog的默认是保持时间由参数expire_logs_days配置，在生成时间超过配置的天数之后，会被自动删除。

4. redo log和bin log对比

1）redo log是属于innoDB层面，binlog属于MySQL Server层面的

2）redo log是物理日志，记录该数据页更新的内容；binlog是逻辑日志，记录的是这个更新语句的原始逻辑

3）redo log是循环写，日志空间大小固定；binlog是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖

4）binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用

5. redo log buffer

一块内存，写redo log时，先修改内存redo log buffer，再【按时】，或者当【事务提交】时写入磁盘

问题：redo log buffer的内容未刷到磁盘，会丢吗

如果事务执行期间MySQL发生异常重启，那这部分日志就丢了，但是由于事务并没有提交，所以这时日志丢了也不会有损失

问题：事务还没提交的时候，redo log buffer中的部分日志可能被持久化到磁盘呢

1）后台线程每秒一次的轮询操作

2）redo log buffer占用的空间即将达到一定阈值时，后台线程会主动写盘

3）并行的事务提交的时候，顺带将这个事务的redo log buffer持久化到磁盘

6. MySQL 的“双 1”配置

1）sync_binlog设置成1

0表示每次提交事务都只write，不fsync（write指把日志写入到文件系统的page cache，并没有持久化到磁盘）

1表示每次提交事务都会执行fsync

N(N>1) 时表示每次提交事务都write，但累积N个事务后才fsync

2）innodb_flush_log_at_trx_commit设置成 1

0表示每秒触发一次缓冲日志写磁盘操作，写入效率最高，数据安全最低。

1表示立即将redo log Buffer写入redo log file，数据安全最高，性能受到影响（最安全，推荐）

2表示将redo log Buffer刷新到OS Cache（内核缓冲区Page Cache），然后依托OS每秒刷新一次写入redo log file

7. 两阶段提交

redo log先prepare完成，再写binlog，最后才进入redo log commit阶段

预提交：当一个事务执行UPDATE、INSERT或DELETE等修改操作时，MySQL将这些操作记录到redo log缓冲区中，并将该事务状态设置为“prepare”状态

1）时刻A MySQL重启

由于此时binlog还没写，redo log也还没提交，所以崩溃恢复的时候，这个事务会回滚

2）时刻B MySQL重启

如果redo log里面的事务是完整的，也就是已经有了commit标识，则直接提交；

如果redo log里面的事务只有完整的prepare，则判断对应的事务binlog是否存在并完整：

1）如果是，则提交事务

2）否则，回滚事务

五、一条更新语句执行的顺序

update T set c=c+1 where ID=2;

a.执行器先找引擎取ID=2这一行，ID是主键，如果这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

b.执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

c.引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。

d.执行器生成这个操作的binlog，并把binlog写入磁盘。

e.执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

六、

七、在两阶段提交的不同时刻MySQL出现异常，重启后会出现什么情况

假设在redo log处于prepare阶段，写binlog之前为时刻A，在写binlog之后，redo log处于commit阶段之前为时刻B

如果在时刻A发生crash，由于binlog还没有写，redo log也还没有提交，所以崩溃恢复的时候，这个事务会回滚

如果在时刻B发生crash，这时候binlog写完，redo log还没有commit，那么在崩溃恢复时MySQL会做以下判断规则：

1、如果redo log里面的事务是完整的，也就是已经有了commit标识，则直接提交；

2、如果redo log里面的事务只有完整的prepare，则判断对应的事务binlog是否存在并完整：

如果是，则提交事务；否则，回滚事务。