MySQL InnoDB redo与undo20201222

阅读量：4173 次

发布时间：2019-05-26

本文共 11561 字，大约阅读时间需要 38 分钟。

MySQL InnoDB redo与undo

概述

一 MySQL redo log重做日志

重做日志用来实现事务的持久性，即事务ACID中的D。其由两部分组成：一是内存中的重做日志缓冲（redo log buffer），其是易失的：二是重做日志文件（redo log file），其是持久的。

redo日志是物理日志，也被称为重做日志，对数据库中表的操作进行记录，可以用于系统崩溃时的数据恢复。

1先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝。2生成-条重做日志并写入redo log buffer,记录的是数据被修改后的值。

3当事务commit时，将redo log buffer中的内容刷新到redo log file, 对redo log file采用追加写的方式。

4定期将内存中修改的数据刷新到磁盘中。

数据库崩了之后:先要做前滚( redo log+旧的数据块)，再做回滚( undo、rollback )，保护做了修改又没刷到磁盘的脏页。

1.1 rolling forward(前滚) :

数据库启动时,然后通过online redologs中的重做日志,重现实例崩溃前对数据库的修改操作。在恢复过程中对于已经提交的事务,但尚未写入数据文件的那部分数据全部写入数据文件。

1.2 rolling back(回滚) :

rolling forward之后,虽然已经提交的修改操作更改的数据都已经被写入数据文件,但在实例崩溃时,部分未提交的事务操作的数据也被写入到数据文件,这些事务必须被撤销。

1.3 redo作用

1重做日志用来实现事务的持久性.

2 提高数据库写性能

事务提交后，必须将事务对数据页的修改刷(fsync)到磁盘上，才能保证事务的ACID特性。这个刷盘，是一个随机写，随机写性能较低，如果每次事务提交都刷盘，会极大影响数据库的性能。随机写性能差，有什么优化方法呢？先写日志(write log first)，将随机写优化为顺序写，将每次写优化为批量写；将对数据的修改先顺序写到日志里，这个日志就是redo log。

假如某一时刻，数据库崩溃，还没来得及将数据页刷盘，数据库重启时，会重做redo log里的内容，以保证已提交事务对数据的影响被刷到磁盘上。

一句话，redo log是为了保证已提交事务的ACID特性，同时能够提高数据库性能的技术。

二 MySQL redo深入

2.1 如何写MySQL redo log 重做日志

redo log info > redo log buffer > redo log files

redo log buffer :

1.日志会先写到redo log buffer，根据制定条件刷新到redo log file

2.由log block组成

3.每个log block 512字节, 所以不需要double write ,因为每次刷新都是原子的。

重做日志都是以512字节进行存储的

即:重做日志缓冲,重做日志文件都是以块(block)的方式进行保存,统称为重做日志块(redo log block) ,大小: 512字节.

2.2 日志块

innodb存储引擎中，redo log以块为单位进行存储的，每个块占512字节，这称为redo log block。所以不管是log buffer中还是os buffer中以及redo log file on disk中，都是这样以512字节的块存储的。

每个redo log block由3部分组成：日志块头、日志块尾和日志主体。其中日志块头占用12字节，日志块尾占用8字节，所以每个redo log block的日志主体部分只有512-12-8=492字节。

2.3 重做日志格式

各个部分详细释义如下：

type：该条redo日志的类型

space ID：表空间

page number：页号

data：该条redo日志的具体内容

2.4 1og buffer往log file里写redo log的触发机制

innodb_flush_log_at_timeout 参数，可以设置刷新间隔，默认为1。

01.每1s写;

02.大于log buffer空间的1/2的时候;

03.commit的时候;

innodb_ flush_log_at_trx_commit 1最安全，0最快。

a. innodb_ flush_log_at_trx_commit=0 :事务提交时,不刷新redo log buffer，0:每秒1次写入到log file中，同时会进行文件系统到磁盘的同步操作，但每个事务的提交不会从log buffer到log file。速度快，不安全，出现故障会丢失一秒的事务，比如游戏数据库建议设置为0，不会触发文件系统到磁盘的同步。

b. innodb_flush_log_at_trx_commit=1 :事务提交时，将redo log buffer刷新到磁盘(由于redo没有0_direct ,也必须经过操作系统缓存,然后fsync到磁盘)，1：每个事务的提交commit会从log buffer到LOG file。同时触发文件系统到磁盘的同步操作，同时触发文件系统到磁盘的同步操作。最安全。

c. innodb_flush_log_at_trx_commit=2 :事务提交时,将redo log buffer刷新到os的缓存，2：每个事务的提交commit会从log buffer到LOG file，不会触发文件系统到磁盘的同步。不会触发文件系统到磁盘的同步。但是每秒会有一次文件系统到磁盘的同步。

04.log buffer到了1M的时候;

2.5 日志innodb_log_buffer_size（日志缓冲区）

默认是16M就够了。

show variables like '%innodb_log_buffer_size%';

+------------------------+----------+

| Variable_name | Value |

+------------------------+----------+

| innodb_log_buffer_size | 16777216 |

+------------------------+----------+

1 row in set (0.00 sec)

2.6日志文件大小innodb_log_file_size 需要设置

指定重做日志大小,数据库挂了以后的操作。

5.5以前最大是4G，5.6>512G。

小业务256M够了，中型业务不能超过1G，大型业务不能超过2G。

innodb log顾名思义：即innodb存储引擎产生的日志，也可以称为重做日志文件，默认在innodb_data_home_dir下面有两个文件ib_logfile0和ib_logfile1。MySQL官方手册中将这两个文件叫文InnoDB存储引擎的日志文件；

innodb log的作用：当MySQL的实例和介质失败的时候，Innodb存储引擎就会使用innodb log文件进行恢复，保证数据库的完整性；

innodb log的写原理：

看红色框框的那部分

每个InnDB存储引擎至少有1个重做日志文件组（group），每个文件组下至少有两个重做日志文件，默认的为ib_logfile0、ib_logfile1；

日志组中每个重做日志的大小一致，并循环使用；

InnoDB存储引擎先写重做日志文件，当文件满了的时候，会自动切换到日志文件2，当重做日志文件2也写满时，会再切换到重做日志文件1；

为了保证安全和性能，请设置每个重做日志文件设置镜像，并分配到不同的磁盘上面；

（发现以上特性跟ORACLE的连接重做日志文件简直是一样的）

2.6.1 innodb log的相关参数

运行脚本：show variables like 'innodb%log%'; 查看重做日志的相关参数

mysql> show variables like 'innodb%log%';

常用设置的参数有：

innodb_mirrored_log_groups 镜像组的数量，默认为1，没有镜像；

innodb_log_group_home_dir 日志组所在的路径，默认为data的home目录；

innodb_log_files_in_group 日志组的数量，默认为2；

innodb_log_file_size 日志组的大小,默认为5M；

innodb_log_buffer_size 日志缓冲池的大小，图上为30M;

2.6.2 参数的相关调优

重做日志文件的大小设置跟ORACLE一样，面临的问题是相似的。

当innodb log设置过大的时候，可能会导致系统崩溃后恢复需要很长的时间；

当innodb log设置过小的时候，当一个事务产生大量的日志的时候，需要多次切换重做日志文件，会产生类似如下的报警；

130702 12:53:13 InnoDB: ERROR: the age of the last checkpoint is 2863217109,

InnoDB: which exceeds the log group capacity 566222311.

InnoDB: If you are using big BLOB or TEXT rows, you must set the

InnoDB: combined size of log files at least 10 times bigger than the

2.6.3重做日志与二进制日志的区别

1 记录的范围不同：二进制日志会记录MySQL的所有存储引擎的日志记录（包括InnoDB、MyISAM等)，

而InnoDB存储引擎的重做日志只会记录其本身的事务日志。

2记录的内容不同：二进制日志文件记录的格式可以为STATEMENT或者ROW也可以是MIXED，其记录的都是关于一个事务的具体操作内容。

InnoDB存储引擎的重做日志文件记录的关于每个页的更改的物理情况。

3 写入的时间也不同：二进制日志文件是在事务提交前进行记录的，而在事务进行的过程中，不断有重做日志条目被写入到重做日志文件中。

show variables like '%innodb_log_file%';

+---------------------------+-----------+

| Variable_name | Value |

+---------------------------+-----------+

| innodb_log_file_size | 209715200 |

| innodb_log_files_in_group | 2 |

+---------------------------+-----------+

2 rows in set (0.00 sec)

2.7事务刷新 innodb_flush_log_at_trx_commit

(控制事务的提交方式，控制日志刷新到硬盘的方式)

show variables like '%innodb_flush_log_at_trx_commit%';

+--------------------------------+-------+

| Variable_name | Value |

+--------------------------------+-------+

| innodb_flush_log_at_trx_commit | 1 |

+--------------------------------+-------+

1 row in set (0.00 sec)

有3个值:0,1,2默认是1

0:每秒1次写入到log file中，同时会进行文件系统到磁盘的同步操作，但每个事务的提交不会从log buffer到log file。速度快，不安全，出现故障会丢失一秒的事务，比如游戏数据库建议设置为0，不会触发文件系统到磁盘的同步。

1：每个事务的提交commit会从log buffer到LOG file。同时触发文件系统到磁盘的同步操作，同时触发文件系统到磁盘的同步操作。最安全。

2：每个事务的提交commit会从log buffer到LOG file，不会触发文件系统到磁盘的同步。不会触发文件系统到磁盘的同步。但是每秒会有一次文件系统到磁盘的同步。

2.8 innodb_flush_method

lfinnodb_flush_method is set to NULL on a Unix-like system, the fsync option is used by default lf innodb_flush_method is set to NULL on Windows, the async_unbuffered option is used by default.

show variables like '%innodb_flush_method%';

+---------------------+-------+

| Variable_name | Value |

+---------------------+-------+

| innodb_flush_method | |

+---------------------+-------+

1 row in set (0.00 sec)

2.9 redo日志特点

1.redo log的物理文件，—般有2个,大小可配置

2.innodb_log_file_size配置，根据实际情况可大可小，但不能太小

3.redo类型是物理逻辑日志，记录的是对页的操作，页内是逻辑的内容，我们可认为就是物理日志，记录的是对页的操作，

比如∶用户线程连接数据库，进行dml操作，操作数据块，产生redo log(对哪个数据文件space no、哪个数据页page no、哪个数据行row no、做的什么dml、value )

【不看事务;只盯着脏页﹔按照时间顺序记录】

insert—条记录，那么大致内容就会记录:

对space id=3 , page no=4 , offset aa,日志内容xx（主键索引)对space id=3 , page no=8 , offset bb,日志内容yy(二级索引)

另外:undo日志本身也要写入到redo里面去，这一点非常重要。

4redo log是循环覆盖的，写完一个，写另一个，它只能保证∶脏页没有写到磁盘上，对应的redo log是不能被覆盖的。

5.mysql里redo log只能做崩溃恢复，只针对innodb这些表起作用。

6.redo log的第一个文件的头部，会记录两个512字节的记录，分别是︰checkpoint1和checkpoint2，轮询写入互为备份，checkpoint是写在redo日志里面的。

7.redo里面记录的是日志的写入，里面有个很重要的概念叫做Isn

2.10 checkpoint

如果重做日志可以无限地增大，同时缓冲池也足够大，那么是不需要将缓冲池中页的新版本刷新回磁盘。因为当发生宕机时，完全可以通过重做日志来恢复整个数据库系统中的数据到宕机发生的时刻。

但是这需要两个前提条件︰

1、缓冲池可以缓存数据库中所有的数据;

2、重做日志可以无限增大，但这不可能。

因此checkpoint（检查点)技术就诞生了，目的是解决以下几个问题︰

1、缩短数据库的恢复时间;

2、缓冲池不够用时，将脏页刷新到磁盘;

3、重做日志不可用时，刷新脏页。

如果没有checkpoint的时候∶数据库脏页都存放在内存中，如果这时候数据库挂了，那redo就需要从头到尾开始恢复，非常慢。

如果有checkpoint的时候:会按照一定的算法进行data page脏页的刷新，减少数据库恢复的时间。

当数据库发生宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁盘。数据库只需对checkpoint后的重做日志进行恢复，这样就大大缩短了恢复的时间。

2.10.1 checkpoint 刷新

sharp checkpoint 当MySQL正常关闭的时候，需要将所有的脏页都刷新。

fuzzy checkpoint为了考虑数据库的性能，MySQL按照一定算法之刷新部分脏页。

fuzzy checkpoint触发条件定时刷新:每10秒，或者每1秒，从脏页列表中去刷新部分脏页。

脏页刷新比例∶75%

参数: innodb_max_dirty_pages_pct

当脏页数量超过这个比例时候，会强制进行checkpoint

2.11 lsn日志的序列号

lsn=Log sequeuce number日志序列号,主要用于Mysql重启的恢复。

show engine innodb status\G;

---

LOG

---

Log sequence number 212539103 -- redo log buffer的lsn

Log flushed up to 212539103 -- redo log file的lsn

Pages flushed up to 212539103 -- data page的lsn,数据磁盘中的lsn，表示这之前的logfile里的日志可以被覆盖了。

Last checkpoint at 212539094 -- - checkpoint的lsn，存放在redo log file里面的第一个文件的头部。表示最后一次检查点的log位置。它的值表示系统启动时从哪个点去恢复，redo log做崩溃恢复时指定的起点。去做崩溃恢复时，终点是最新的一条logfile，起点就是checkpoint，记录的最早脏的点。

如上图所示，Innodb的一条事务日志共经历4个阶段：

1创建阶段：事务创建一条日志，写到redo Log buffer中

2日志刷盘：日志写入到磁盘上的日志文件；

3数据刷盘：日志对应的脏页数据写入到磁盘上的数据文件；

4写CKP：日志被当作Checkpoint写入日志文件；

对应这4个阶段，系统记录了4个日志相关的信息，用于其它各种处理使用：

Log sequence number（LSN1）：当前系统LSN最大值，新的事务日志LSN将在此基础上生成（LSN1+新日志的大小）；

Log flushed up to（LSN2）：当前已经写入日志文件的LSN；

Oldest modified data log（LSN3）：当前最旧的脏页数据对应的LSN，写Checkpoint的时候直接将此LSN写入到日志文件；

Last checkpoint at（LSN4）：当前已经写入Checkpoint的LSN；

对于系统来说，以上4个LSN是递减的，即： LSN1>=LSN2>=LSN3>=LSN4.

2.12 重做日志怎么恢复（算法）

假设:redo log file的lsn = 150，checkpoint的lsn = 100

这个时候mysql挂了，重启之后恢复流程如下:

1 checkpoint的lsn = 100之前的redo日志，不需要恢复。因为checkpoint之前的日志已经可以确保刷新完成。

2 那么100 <= redo LOG file的lsn <=150 (100~150)之间的日志需要结合data page里面的lsn来判读哪些是要重做，哪些不重要。

第一种情况：如果redo LOG file的lsn <= data page里面的lsn，证明全部刷新，这些日志不需要重做，因为page已经是最新的。

第二种情况：如果redo LOG file的lsn >= data page里面的lsn，证明没有刷新，这些日志需要应用到page里面去，这个操作叫恢复。

2.13 计算每小时redo log重做日志文件的日志量

pager grep -i "Log sequence number"

1分钟日志变化的情况

mysql> show engine innodb status\G select sleep(60);show engine innodb status\G

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 6

Current database: *** NONE ***

Log sequence number 212542442

1 row in set (0.00 sec)

1 row in set (1 min 0.00 sec)

Log sequence number 288337334

1 row in set (0.00 sec)

select round((288337334-212542442)/1024/1024) as MB;

72MB

每小时为60*72MB=4GB

这样得到innodb_log_file_size的大小为至少700M（正常情况下每个日志大小保证10分钟左右的量最好，4000/60/10)

文件最大不能超过2GB。

三 MySQL undo深入

redo log是为了持久化数据，在数据还没从内存刷新到磁盘时，如果发生故障，可读取该日志持久化到磁盘，用于前滚。

binlog 是为了复制和恢复数据的，即Mysql从服务器可以读取主服务器的binlog复制数据，数据库数据丢失，也可以读取binlog恢复。

undo log是为了保证原子性的，undo记录的是对事务的逆向操作，用于回滚。是逻辑日志，通过undo日志数据库可以实现MVCC，除此之外，系统崩溃恢复时，也确保数据库状态能恢复到一致。

3.1 undo表空间参数

mysql5.7>从系统表空间独立出来的undo回滚表空间

show variables like '%undo%';

Connection id: 4

Current database: mysql

+--------------------------+------------+

| Variable_name | Value |

+--------------------------+------------+

| innodb_max_undo_log_size | 2147483648 | ##最大20G

| innodb_undo_directory | ./ |

| innodb_undo_log_truncate | ON | ##开启自动清理

| innodb_undo_logs | 128 | ##128回滚段

| innodb_undo_tablespaces | 3 | ##三个表空间

show variables like '%truncate%';

| innodb_purge_rseg_truncate_frequency | 128 | ##

| innodb_undo_log_truncate | ON | ##

innodb_undo_directory，指定单独存放undo表空间的目录，默认为.（即datadir），可以设置相对路径或者绝对路径。该参数实例初始化之后虽然不可直接改动，但是可以通过先停库，修改配置文件，然后移动undo表空间文件的方式去修改该参数；

innodb_undo_tablespaces，指定单独存放的undo表空间个数，例如如果设置为3，则undo表空间为undo001、undo002、undo003，每个文件初始大小默认为10M。该参数我们推荐设置为大于等于3，原因下文将解释。该参数实例初始化之后不可改动；

innodb_undo_logs，指定回滚段的个数（早期版本该参数名字是innodb_rollback_segments），默认128个。每个回滚段可同时支持1024个在线事务。这些回滚段会平均分布到各个undo表空间中。该变量可以动态调整，但是物理上的回滚段不会减少，只是会控制用到的回滚段的个数。

mysql 8.0> innodb_undo_logs参数被废除，改为:innodb_rollback_segments

innodb_undo_tablespaces>=2。因为truncate undo表空间时，该文件处于inactive状态，如果只有1个undo表空间，那么整个系统在此过程中将处于不可用状态。为了尽可能降低truncate对系统的影响，建议将该参数最少设置为3；

innodb_undo_logs>=35（默认128）。因为在MySQL 5.7中，第一个undo log永远在系统表空间中，另外32个undo log分配给了临时表空间，即ibtmp1，至少还有2个undo log才能保证2个undo表空间中每个里面至少有1个undo log；

innodb_max_undo_log_size，undo表空间文件超过此值即标记为可收缩，默认1G，可在线修改；

innodb_purge_rseg_truncate_frequency,指定purge操作被唤起多少次之后才释放rollback segments。当undo表空间里面的rollback segments被释放时，undo表空间才会被truncate。由此可见，该参数越小，undo表空间被尝试truncate的频率越高。