MySQL深度解析---日志系统

一、Redo log （重做日志）

Ⅰ.功能作用

InnoDB引擎特有的日志，用来临时的记录数据库的更新操作。记录的是在某个数据页上做了什么修改。

Ⅱ.内部结构

redo log 是物理日志，它分为两部分，一部分是在内存中的日志缓冲redo log buffer，另一部分是在磁盘上的重做日志文件 redo log file。而每次把日志从内存持久化到磁盘都需要经过操作系统内核空间os buffer 并且调用一次操作系统的fsync()操作，调用fsync()的作用就是将OS buffer中的日志刷到磁盘上的log file中

Ⅲ.存储原理

基本存储单元–日志块

1.日志块的概念：

innoDB引擎中，rodolog以日志块的形式存储，每个块占512字节，称为redo log block。
不管是内存中的logbuffer、OSbuffer还是磁盘中的logfile，都是以512字节的块存储的。

2.日志块的组成：

每个redolog block由3部分组成：

• 日志块头
  大小12字节，包括：
  • log_block_hdr_no：（4字节）该日志块在redolog buffer中的位置ID
  • log_block_hdr_data_len：（2字节）该logblock中已记录的log大小。写满该logblock时为0x200，表示512字节。
  • log_block_first_rec_group：（2字节）该logblock中第一个log的开始偏移位置。
  • lock_block_checkpoint_no：（4字节）写入检查点信息的位置。
• 日志主体
  大小496字节，包括：
  • redo_log_type：占用1个字节，表示redo log的日志类型。
  • space：表示表空间的ID，采用压缩的方式后，占用的空间可能小于4字节。
  • page_no：表示页的偏移量，同样是压缩过的。
  • redo_log_body：表示每个重做日志的数据部分，恢复时会调用相应的函数进行解析。例如insert语句和delete语句写入redo log数据部分的内容是不一样的。
• 日志块尾
  大小4字节，只有一个部分：
  • log_block_trl_no ，该值和块头的 log_block_hdr_no 相等。

关于log block块头的第三部分 log_block_first_rec_group，因为有时候一个数据页产生的日志量超出了一个日志块，这是需要用多个日志块来记录该页的相关日志。
例如，某一数据页产生了552字节的日志量，那么需要占用两个日志块，第一个日志块占用492字节，第二个日志块需要占用60个字节，那么对于第二个日志块来说，它的第一个log的开始位置就是73字节(60+12)。如果该部分的值和log_block_hdr_data_len 相等，则说明该log block中没有新开始的日志块，即表示该日志块用来延续前一个日志块。

PS：redo log buffer 或者redo log file on disk 中，就是由很多log block组成，如下图

---

磁盘上的logfile组合–日志组（log group）

1.日志组的概念

log group表示的是redo log group 一组由多个大小相同的redologfile组成。组内redologfile的数量由变量innodb_log_files_group决定，默认值为2，即两个redologfile组成一个组。
这里的组只是一个逻辑概念，并没有真正的文件来表示这是一个组，但是可以通过变量 innodb_log_group_home_dir 来定义组的目录，redo log file都放在这个目录下，默认是在datadir下。

2.日志组的内部结构

日志组是由多个redologfile组成的，所以首先来看一下redologfile的结构：
redologfile是由logblock组成的，但是每个redologfile的前2KB（4个block的大小）都会空出来，以备后续使用。
而日志组是由多个redologfile组成的，在每个组的第一个redologfile文件会记录该组的一些相关信息，剩下的redologfile文件的前2KB就会空着。

PS：redo log file的大小对innodb的性能影响非常大。设置的太大，恢复的时候就会时间较长；设置的太小，就会导致在写redo log的时候循环切换redo log file。

Ⅳ.日志刷盘（持久化）规则

刷日志（从内存）到磁盘有以下几种规则：

1. 发出commit动作时。已经说明过，commit发出后是否刷日志由变量 innodb_flush_log_at_trx_commit 控制。

2. 每秒刷一次。这个刷日志的频率由变量 innodb_flush_log_at_timeout 值决定，默认是1秒。要注意，这个刷日志频率和commit动作无关。

3. 当log buffer中已经使用的内存超过一半时。

4. 当有checkpoint时，checkpoint在一定程度上代表了刷到磁盘时日志所处的LSN位置。

Ⅴ. InnoDB的恢复行为

在启动innodb的时候，不管上次是正常关闭还是异常关闭，总是会进行恢复操作。

• 重启innodb时，checkpoint表示已经完整刷到磁盘上data page上的LSN，因此恢复时仅需要恢复从checkpoint开始的日志部分。例如，当数据库在上一次checkpoint的LSN为10000时宕机，且事务是已经提交过的状态。启动数据库时会检查磁盘中数据页的LSN，如果数据页的LSN小于日志中的LSN，则会从检查点开始恢复。

• 还有一种情况，在宕机前正处于checkpoint的刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度。这时候一宕机，数据页中记录的LSN就会大于日志页中的LSN，在重启的恢复过程中会检查到这一情况，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。

事务日志具有幂等性，所以多次操作得到同一结果的行为在日志中只记录一次。而二进制日志不具有幂等性，多次操作会全部记录下来，在恢复的时候会多次执行二进制日志中的记录，速度就慢得多。例如，某记录中id初始值为2，通过update将值设置为了3，后来又设置成了2，在事务日志中记录的将是无变化的页，根本无需恢复；而二进制会记录下两次update操作，恢复时也将执行这两次update操作，速度比事务日志恢复更慢。因为redo
log记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如二进制日志)要快很多。而且，innodb自身也做了一定程度的优化，让恢复速度变得更快。

Ⅵ.扩展

innodb_flush_log_at_trx_commit
参数概述
MySQL支持用户自定义在commit时如何将redolog buffer中的日志刷redolog file中。具体来说，当产生一条更新记录时，InnoDB引擎会根据参数innodb_flush_log_at_trx_commit的值来决定刷新策略。

• 当值为1时，每次提交事务都会先将事务操作写到redolog buffer中，然后从redolog buffer写入 OS buffer，再调用fsunc() 把日志刷到磁盘（redolog file）。相当于每次提交都会将事务操作写入磁盘。
• 当值为0时，每次提交事务会将事务操作写到redolog buffer 中，事务结束。redolog 会每秒写入OS buffer 并调用fsync() 写入到redolog file。相当于提交后写到内存，而内存以一秒一更新的频率来持久化日志数据。
• 当值为2 时，每次提交事务会直接将事务操作写到OS buffer，事务结束。OS buffer 会每秒调用fsync() 把日志文件刷到磁盘（redolog file）。

参数分析

• 当值为1时，每次提交都会将事务写入磁盘。这样一来日志数据肯定不会丢失，但是会导致IO性能较差。
• 当值为0和2时，每秒更新数据。当系统崩溃时，会丢失一秒的数据。IO性能较好。

PS： 在MYSQL峰值时，一秒也可能有4-5W条数据，丢失会造成严重后果，所以建议把innodb_flush_log_at_trx_commit设置为1，同时尽可能的降低commit的次数来平衡性能和高可用

innodb_log_buffer_size
设置 log buffer的大小，默认8M
innodb_log_file_size
设置事务日志的大小，默认5M
innodb_log_files_group =2
设置事务日志组中的事务日志文件个数，默认2个
innodb_log_group_home_dir =./
事务日志组路径，当前目录表示数据目录
innodb_mirrored_log_groups =1
指定事务日志组的镜像组个数，但镜像功能好像是强制关闭的，所以只有一个log group。在MySQL5.7中该变量已经移除。

---

二、binlog（归档日志，二进制日志）

Ⅰ.功能作用

逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”这个操作

Ⅱ.特性

binlog 由MySQL的Server层实现 ，可以追加写入（指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。）

三、日志与更新语句的执行

update语句的执行流程图
图中浅色框表示是在InnoDB内部执行的，深色框表示是在执行器中执行的

1. 执行器先找引擎取ID=2这一行。ID是主键，引擎直接用树搜索找到这一行。如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
2. 执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。
4. 执行器生成这个操作的binlog，并把binlog写入磁盘。
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

两阶段提交

上图中只有redolog和binlog全都写入完成了才算提交，这样的目的在于保持两个日志逻辑的一致性。
如果任意一个日志写入成功就算提交状态，这样在数据库异常重启时恢复时，使用redolog和binlog就会得到两个不同的数据状态。

---

四、MySQL抖动的原因和处理

1.原因

大部分情况下更新操作都是写完内存和日志就结束，速度比较快。但偶尔也会执行刷脏页（flush）操作。这时候执行速度就会比较慢。

2.数据库flush的条件

1. redo log 满了。这时系统会停止所有更新操作，把check point 往前推进，这样使 redo log 留出空间再继续写日志。

2. 系统内存不足。需要新的数据页而内存不够用时，就要淘汰一些不常用的数据页，空出内存给别的数据页使用。

   • 如果淘汰的是脏页，就要先将脏页的数据刷到磁盘再释放内存。
   • 如果淘汰干净页，就直接释放内存。

3. MySQL空闲时刷脏页。

4. MySQL正常关闭时 flush 脏页

刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：

1. 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
2. 日志写满，更新全部堵住，写性能跌为0，这种情况对敏感业务来说，是不能接受的。

所以，InnoDB需要有控制脏页比例的机制，来尽量避免上面的这两种情况。

3.InnoDB刷脏页的控制策略

innodb_io_capacity ：写盘速度，告诉InnoDB你的磁盘能力。建议设置成磁盘的IOPS。

磁盘的IOPS可以通过fio这个工具来测试
下面的语句是用来测试磁盘随机读写的命令：
fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

innodb_max_dirty_pages_pct ：设置脏页比例上限，默认值是75%。

# 获取脏页比例
mysql> select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;

MySQL刷脏页还有个连坐机制：在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。

innodb_flush_neighbors ：用来控制连坐行为。1开启/0关闭

• 对于机械硬盘，“连坐”可以减少很多随机IO。可以提升系统性能。
• 对于固态硬盘SSD，建议把 innodb_flush_neighbors 设置为 0 ，更快地执行完必要的刷脏页操作，减少SQL语句响应时间