第三章、MySQL日志详解

MySQL日志详解，主要讲解bin log，redo log，undo log日志，还有buffer pool缓冲池原理，包括一条SQL语句怎么执行的，执行经过了哪些操作。

第一章、MySQL基础架构
第二章、buffer pool缓冲池详解
第三章、MySQL日志详解
第四章、Hash表和B+Tree详解
第五章、全面解析MySQL索引
第六章、InnoDB引擎详解
第七章、MySQL事务的脏读，不可重复读，幻读
第八章、关于MySQL各种锁的详解

---

前言

MySQL定义日志的一个原则，都是贯彻能使用内存，就尽量使用内存。

Mysql的日志分为错误日志，二进制日志，查询日志和慢查询日志

错误日志，服务运行过程中发生的严重错误日志，数据库无法启动时，就可以看看具体不能启动的原因是什么

查询日志，记录来自客户端的所有语句

二进制日志，BinLog，记录事务提交的执行日志

慢查询日志，记录所有响应时间超过阀值的SQL语句，默认是关闭状态，需要手动打开，参数为long_query_time，默认值是10s

binLog日志

什么是binLog日志？

BinLog即二进制日志，也叫逻辑日志，归档日志，主要记录了引起数据库改变事件，更新表结构，更新表数据（除了查询语句不记录），之所以叫归档日志，是因为binLog不会像redoLog那样擦除记录，而是追加写入，追加写入指的是binLog写到单个日志的最大值（默认1G，通过变量max_binlog_size设置），就会另起一个binLog文件记录。

binLog每一个binLog记录的是一个完整的事务，因此可能存在当前文件已经达到1G，而日志还在写入，此时不会换一个文件，而是继续写入到事务完成，所以会存在定义的max_binlog_size与binLog日志大小不一致的情况`

binLog 是在service层实现的，因此跟引擎不存在关系。binLog 只记录事务提交的更新SQL语句，其他事务回滚的不做记录


binLog日志存储的内容

• 日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件，简单说，记录的就是原始语句，如

  -- 这种
  insert into t value("张三",20);

• 索引文件（文件名后缀为.index）用于记录哪些日志文件正在被使用
  
  

binLog的主要作用

1. 数据库数据的恢复或者备份，不小心删库了
2. 主从数据库的数据同步，主库上有一个log dump线程，会将binLog 发送给从库。从库有两个线程，一个I/O线程，一个SQL线程，I/O线程读取主库传过来的binlog内容并写入到relay log,SQL线程从relay log里面读取内容，写入从库的数据库。
   
   

binLog 为什么不具备 crash safe功能？

binLog 是在事务提交之后写入，存在场景，将字段A从1改为2，当事务提交数据更新之后，将要写入binLog的时候，数据库崩了。所以当前数据库的数据就变成A = 2，但是binLog 的日志，并没有 “将A=1改为2” 这条SQL。所以当拿binLog去做主从数据同步的时候，就会造成主从数据不一致的问题。

所以binLog 不具备灾后恢复功能，是因为从设计上就不支持，场景没有考虑进去。

Bin Log的写入机制

Bin Log在事务执行过程中，先把日志写入到binlog cache中，事务提交的时候，再把binlog cache写到binlog文件中，并清空binlog cache。

一个事务的BinLog是不能被拆开的，因此不论事务多大，都要确保一次性写入。系统给每一个binlog cache分配了一块内存，每个线程是一个，参数binlog_cache_size用于控制内存的大小，如果超过这个大小，就要暂存在磁盘上。

数据存储在binlog cache上，什么时候进行刷盘操作呢？

刷盘也叫写入磁盘（fsync），主要操作的参数有两个

binlog_cache_size: 二进制日志缓存的大小，默认值32k
sync_binlog:  表示写缓冲多少次，刷一次盘,默认值为0，即由操作系统决定每隔一段时间刷盘

写入磁盘的操作主要由sync_binlog控制

1. sync_binlog = 0的时候，表示每次提交事务都只写入binlog文件中，刷盘由操作系统决定每隔一段时间进行，性能最好
2. sync_binlog = 1的时候，表示每次提交事务都写入到磁盘中，保证持久性，但是性能最差，一般不建议
3. sync_binlog = N (N>1) 的时候，表示每次提交事务都写入binlog文件中，当事务达到N的时候，再写入到磁盘中。但是如果存在异常宕机，可能会导致数据丢失，因为内存数据没有了。

除了这个设置，被动刷盘也有，内存不足，其他事务提交，也会导致被动刷盘操作。

sync_binlog设置多少最好？

默认是0，隔一段时间进行刷盘，但是如果出现IO瓶颈的情况下，可以设置sync_binlog = N，指定事务到达多少再进行刷盘操作，优点是，能够提升性能，但是考虑实际的场景，不允许设置过大，常见设置100-1000中某个值。因为一旦主机异常重启的情况，可能会丢失最近N个事务的binlog 日志

crash safe（灾后恢复能力）

出现前景：

binLog日志能够在事务提交，保存修改数据库数据的语句到日志中。但是不能保证，在提交前服务器宕机等极端情况下出现的数据丢失。而binLog又是作为主从同步的重要日志，就会出现主从数据不一致的情况


解决

InnoDB通过redoLog日志，保证了crash safe，而为了保证数据的一致性，使用了两阶段式提交

redoLog日志

先考虑一个问题，有了binLog，为什么还需要redoLog呢？

看了crash safe的解释，心里大概有个底了，哦，redoLog的出现，是InnoDB存储引擎为了实现灾后恢复特有的日志，结合binLog一起使用，同时使用两阶段式提交，保证了数据的一致性。接下来，讨论redoLog日志概念

redoLog日志概念

前面说过了，MySQL的原则尽可能使用内存，减少磁盘IO的读写操作，同样，redoLog也遵循这个操作，也就是说，MySQL中，更新操作不会马上就写入到磁盘中，而是先写入到redoLog (redoLog buffer)之中，并更新到内存（buffer pool）中，更新操作就完成了，注意：更新操作并不会马上刷入到磁盘中。而是InnoDB会在适当的时候（系统空闲的时候），将内存中的数据更新到磁盘中。（刷脏页操作）

redoLog日志存储内容

redoLog 是InnoDB存储引擎特有的日志，又称为重做日志文件；redoLog是循环写日志，记录的是数据页的物理修改，而不是某一行或者某几行修改成怎么样。可以用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)

redoLog日志写入机制

redoLog的大小是固定的，redolog采用循环写的方式记录，当写到结尾时，会回到开头循环写日志

将redo Log看做一个固定大小的块时，

write pos表示当前记录的位置

check point表示将日志记录的修改写进磁盘，完成数据落盘，数据落盘后checkpoint会将日志上的相关记录擦除掉，

即write pos->checkpoint之间的部分是redo log空着的部分，用于记录新的记录，checkpoint->write pos之间是redo log待落盘的数据修改记录。当writepos追上checkpoint时，得先停下记录，先推动checkpoint向前移动，空出位置记录新的日志。

这里的擦除完成是：将内存中的数据写入到磁盘中

redo Log的有关的几个参数

innodb_log_files_in_group: # 日志文件数量默认2个

innodb_log_file_size # 每个日志文件大小,默认是5M

# 为了控制redo log的写入策略，持久化到磁盘的设置有三种:
innodb_flush_log_at_trx_commit
# 0表示每次事务提交时都只是把redo log留在redo log buffer中;
# 默认是1，表示每次事务的redo log都直接持久化到磁盘，这样可以保证MySQL异常重启之后数据不丢失。性能最差
# 2表示每次事务提交时都只是把redo log写到page cache。

注意，持久化到磁盘操作，是不能再接收新的更新请求，所以有可能会导致MySQL卡顿，因此设置一个合理的日志文件大小是非常重要的！

redo log持久化到磁盘操作

默认后台InnoDB有一个后台线程，每隔1秒，就会把redo log buffer中的日志，调用write写到文件系统的page cache，然后调用fsync持久化到磁盘。 除此之外还有另外两种

1. redo log buffer占用的空间即将达到innoDB_log_buffer_size一半的时候，后台线程会主动写盘。

   由于这个事务没有提交，所以这个写盘动作只是write，而没有调用fsync，也就是只留在了文件系统的page cache。

2. 并行事务提交的时候，顺便把这个事务的redo log buffer持久化到磁盘。

   假设一个事务A执行到一半，已经写了一些redo log到buffer上，这时候另外一个线程的事务B提交了，如果innoDB_flush_log_at_trx_commit设置的是1，那么按照这个参数的逻辑，事务B要把redo log buffer的日志全部持久化到磁盘，也就是说，会把事务A已经写到buffer里的日志也持久化到磁盘上。
   
   

双1操作

如果innodb_flush_log_at_trx_commit设置成1，那么redo log在prepare阶段就要持久化一次，因为有个崩溃恢复逻辑是依赖prepare阶段的redo和binlog来恢复的。

所以也就是说，在prepare阶段持久化到磁盘，redo log buffer的数据就清空了。在commit阶段的redo log，write的时候，只是写入到page cache中。

只有等到每秒刷盘的时候，才会真正fsync到磁盘中。

而双1操作，指的就是sync_binlog和innodb_flush_log_at_trx_commit都设置成 1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是redo log（prepare 阶段），一次是binlog。

redoLog的好处

1. 最重要的保证了灾后恢复的能力
2. 将随机写IO，改为顺序写IO，提高了性能，提高并发量
   
   

redo Log刷盘机制

MySQL利用WAL机制（先写日志，再写磁盘），将随机写IO改为顺序写IO，提升了数据库性能的同时，也带来了内存脏页的问题。

脏页：当内存数据页和磁盘数据页不一致的时候，将内存数据页称为脏页

干净页：内存数据写入到磁盘后，内存和磁盘上的数据页数据一致了，这种称之为干净页

有时候MySQL执行更新操作，感觉变慢了，其实是在执行刷脏页（flush）操作

触发数据库的flush操作有

1. 内存（redo Log）满了，再有数据过来，系统需要停止所有更新操作，然后将内存的数据页写入到磁盘中。
2. 内存满了，需要新的内存页的时候，如果有干净页，就直接写干净页，如果没有，那么就执行刷脏页操作；那么可以不执行写入内存操作，直接写入磁盘吗？？?不太理解
   1. 读取的时候，内存存在，直接返回内存
   2. 读取的时候，内存不存在，redo Log文件是正确的数据，读入内存后返回
3. MySQL认为系统空闲的时候，就会系统的进行刷脏页操作
4. MySQL正常关闭的时候，会将所有的脏页刷入到磁盘中

InnoDB用缓存池（buffer pool）管理内存，缓冲池中的内存页有三种状态

1. 还没有使用的数据页
2. 使用了但是数据已经刷入磁盘的干净页
3. 使用了但是数据还存在的脏页

InnoDB的策略是尽可能使用内存，减少磁盘IO的读写操作，但是对于一个长时间运行的库来说，没有被使用的页很少，也就是内存的意思。

当要读入的数据页没有内存时，向缓冲池申请一个数据页。有几种情况

1. 最久没有被使用的数据页从内存淘汰掉，如果淘汰的是一个干净页，就直接释放出来使用
2. 如果是脏页的话，刷到磁盘中变成干净页再使用

刷脏页如果出现以下两种情况，则需要优化

1. 一个查询如果要淘汰的脏页个数太多，会导致查询的响应时间变长
2. 日志写满，更新堵塞，这种情况对于敏感业务时无法接受的。
   
   

bin Log 和redo Log 的区别

1. Bin Log是Mysql的Service层实现的，所有引擎都可以使用；Redo Log是InnoDB引擎特有的
2. Bin Log是逻辑日志，记录这个语句的原始逻辑，如：“ID = 2 这一行的 C 字段加1”；Redo Log是物理日志，记录的是，“在某个数据页做了什么修改”
3. Bin Log是追加写，写到一定大小切换下一个，不会覆盖以前的；Redo Log是循环写，空间固定会用完，通过write pos和checkpoint来维持循环写入。
4. Bin Log 不支持灾后恢复，Redo Log主要用于灾后恢复。
   
   

执行器和InnoDB引擎处理 binLog和redoLog的操作

二阶段式提交的操作

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

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

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

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

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

   

深色的表示执行器，浅色的表示引擎

关于Bin Log和Redo Log的一些思考

为什么Bin Log和Redo Log要分两阶段式提交？

因为Bin Log和Redo Log是两个独立的逻辑，如果不用两阶段式提交的话，数据备份恢复的时候，会出现两个库数据不一致的情况。

如：

先写Bin Log，再写Redo Log的情况，将C从0改为1，Bin Log写入之后宕机，因为Redo Log还没写入，宕机后恢复，RedoLog记录C的值为0，但是Bin Log已经记录了该值为 “把C从0改为1的情况”，所以这时候如果将Bin Log进行备份的话，那么临时库读取到该数据，C的值就变成了1，与原库的值是0，数据不一致。

先写Redo Log，再写Bin Log的情况，将C从0改为1，Redo Log写完之后，Bin Log还没写完宕机，通过Redo Log恢复日志，所以恢复后的C的值是1，但是，Bin Log还没写完就宕机了，所以Bin Log并不存在这条 "将C从0改为1"的语句，所以如果将Bin Log作为备份的话，那么临时库读取到该数据，此时C的值是0，原库的值是1，数据不一致。

两阶段式提交的作用，是让BinLog日志和Redo Log日志保持逻辑上的一致

另外小知识点：需要学习

高并发情况下，两阶段式提交顺序不一样，会导致主从数据的不一致，早期版本使用 prepare_commit_mutex 加锁解决，一个事务获取到锁才能进入prepare阶段，高并发场景下，争夺锁影响到性能。

5.6版本之后，使用组提交方式BLGC。

数据恢复流程

事务提交的过程中，MySQL进程突然崩溃的情况，重启后怎么保证数据不丢失？

1. 读取redo log的记录
2. 先恢复内存，从checkpoint开始，重新在内存中执行对数据页的修改？
3. 检查redo log哪些事务是完整的并且处于prepare阶段
4. 根据XID对照binlog的事务
5. 是否完整事务，是的话，重新commit redo log 完成事务提交
6. 不是完整事务，根据XID找到undo log的事务进行回滚。

简单说：redolog 是否回滚在于，在于XID是否能够在binlog找到commit标志的事务，如果找不到，则事务未提交通过undo log文件回滚即可。

undoLog日志

反向操作，当删除一条数据的时候，记录的是insert一条数据；当修改一条数据的时候，记录的是删除后新增的操作。这样，当事务进行回滚的时候，就可以从undo Log日志中读取相应的内容，进行回滚操作。同时也可以根据 undo Log日志，获取到一条被修改后的数据，方便问题定位

总结：

日志名称	Bin Log	Redo Log	Undo Log
出现场景	事务提交的操作	存在服务器宕机的情况，导致Bin Log记录不到数据，主从同步数据不一致	事务回滚之后，数据的还原操作
彼此的作用.	保存事务提交的每次修改数据库的操作，select和show不保存	每次执行修改操作，都会保存执行语句到日志中，用于宕机后数据的恢复	保存事务回滚所需要的执行语句，如delete，在该日志中就是insert，反向记录记录数据。