把学习当成一种习惯
选择往往大于努力,越努力越幸运
MySQL 事务日志
Sep 22, 2020

MySQL的日志文件 --- 前言

  我们知道,MySQL的事务四要素(ACID)为 : 原则性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability),但四要素不是本文要讲的重点,本文要讲的重点是 : MySQL是如何实现原子性和持久性的呢?答案就是日志 : redo log 、 undo log 、 bin log, redo log 和 undo log 都是来自于InnoDB存储引擎,而不是MySQL的提供的,平时称它们为事务日志文件;而 bin log 来自于MySQL的Server层,是MySQL特有的,是一种二进制日志文件.
  那什么是日志文件呢 : 日志嘛,无非就是记录,例如有的人会每天在自己笔记本里写上每天需要做的事情,或者有的家庭会记录每个月的消费记录等等.所以同样的道理,redo log 、 undo log 、 bin log 都是用来记录,那它们有什么区别呢 :

  • redo log属于物理日志,又叫重做日志,是事务日志,记录的是数据页的物理修改(记录数据页的修改),它可以用来恢复数据(PS : 缓冲池中因为宕机而没刷盘的数据页(脏页),此时redo log有记录该数据页的修改记录),这里需要说明一下,redo log没有恢复数据的能力(没有刷盘的能力),它只是记录数据页的修改.(PS:什么是脏页 : 缓冲池中的数据页没刷到磁盘的数据页就是脏页)
  • bin log属于二进制日志,用于记录用户对数据库操作的SQL语句(除了数据查询语句)信息,它是基于行格式的记录方式,其本质也还是逻辑的SQL设置,如该行记录的每列的值是多少;还有,二进制日志先于redo log被写完的(个人理解的bin log比redo log先写完的意思是: redo log 上记录的是commit状态的数据页表示的是事务已完成提交了,也就是说该事务在redo log已经是写完了,而bin log是在redo log 之前写完的,所以此时该事务在bin log上是完整的,其实redo log上记录是commit状态的话,那么对于bin log上已经写完了,那么恢复数据的时候该事务是需要提交的,或者也有一种情况就是redo log上不是commit状态,而bin log已经写完了,该事务在恢复数据的时候也需要提交的,这里涉及到了二阶段提交,看不懂的暂时先跳过,会独立一篇文章来写);redo log 和 bin log 涉及到了一个知识点 : 阶段性提交(二阶段提交、三阶段提交,本文暂不详细说明,会独立一篇文章);对于redo log,可以说是实现持久性的关键,即使数据库宕机了,也可以恢复数据;
  • undo log属于逻辑日志,也叫回滚日志,跟redo log一样都是事务日志,是记录每行记录增删改操作(也可以称之为每行记录有多个版本记录),当事务执行失败或者调用rollback导致事务回滚时,此时的数据需要回滚到之前原先的数据,就需要用到undo log来回滚,所以,undo log是实现原子性的关键,它提供了回滚操作,保证了数据的原子性.

  其实结合缓冲池这篇文章,我们回忆一下,MySQL为了减少对磁盘文件的I/O随机读写,所以对数据库的DML操作其实是对缓冲池的DML操作,然后记录到 redo log 、 undo log 、 bin log;缓冲池是个内存组件,假如数据库宕机了,内存上没有刷到磁盘的数据不就没了嘛 : 不会, 因为redo log 上有记录,保证了数据的持久性.所以,redo log 的核心作用就是体现在这里.

事务日志文件

redo log

  redo log 有两种类型的日志:缓存日志(redo log buffer) 和磁盘日志(redo log file),先写缓存日志后再根据一定的规则把缓存日志刷到磁盘上(redo log file),有点类似于缓冲池,先写内存后,再一定的规则(触发checkpoint规则)把数据页刷到磁盘上.redo log buffer是根据什么规则去写入到磁盘上的呢: 通过操作系统(OS Buffer)的 fsync()系统调用 , 即 redo log buffer 要写入到磁盘上,需要通过操作系统内核空间的OS buffer,并且调用 fsync() 将OS buffer中的缓存日志刷到磁盘上的redo log file中.
  OS Buffer 作用 : 有了它,我们就可以控制每次commit都不用去把redo log buffer写入到磁盘.
  其实OS Buffer相当于 Log Buffer 与 Log File 的中间人,每次把缓存日志写入到磁盘日志,都需要OS Buffer来执行,如下图:

以下三种情况体现了OS Buffer的不同作用 :

  • 1 : 每次commit都会将log buffer写入到os buffer,然后os buffer调用 fsync() 把log file buffer写入到磁盘上,也就是说每次commit都会进行一次写磁盘的操作,性能较低,但是保证了每次都把事务日志写入到磁盘中.
  • 0 : 每次commit都只写入在log buffer中,然后暂不写入os buffer,而是把log buffer上的缓存日志每秒写入到os buffer中,并且调用 fsync() 把缓存日志写入到磁盘上,也就是说每次在log buffer上堆积1秒的缓存文件,就会把这些缓存文件写入到os buffer并且调用一次fsync()操作写入到磁盘中.(当MySQL服务崩溃,会丢失1秒钟的数据),PS ：这里的每秒写入os buffer由 innodb_flush_log_at_timeout 变量 控制,默认是1秒,这个变量与 commit 无关,commit只与 值为 1 有关.
  • 2 : 每次commit写入log buffer后,再直接写入到os buffer中,然后每秒通过os buffer调用 fsync() 操作写入到磁盘中.也就是说每次在os buffer上堆积1秒的缓存日志,就会调用一次fsync()操作写入到磁盘中.(当操作系统崩溃,会丢失1秒钟的数据),PS ：这里的每秒把os buffer上的缓存日志写入到磁盘是由 innodb_flush_log_at_timeout 变量 控制,默认是1秒,这个变量与 commit 无关,commit只与 值为 1 有关.

我们可以把这三种情况总结为

  • 1 : 每次commit都写磁盘;这种每次都会写入磁盘一次,影响性能,但是系统奔溃的情况下保证了每次commit都写入到了磁盘中.
  • 0 : 每次都在log buffer中堆积1秒的缓存日志,并且把这1秒的缓存日志刷到磁盘中;当系统崩溃,会丢失1秒钟的数据,但提高了性能.
  • 2 : 每次写入log buffer,然后直接写入到os buffer中,并且每秒调用一次os buffer;当系统崩溃,会丢失1秒钟的数据,但提高了性能.

  innodb_flush_log_at_trx_commit 在设置为0,2的情况下,都是每秒执行一次写磁盘的操作,但都与事务的commit无关,而是与innodb_flush_log_at_timeout 变量有关,默认是1秒.
  而这三种操作情况,MySQL提供了一个变量来决定使用哪种操作 : innodb_flush_log_at_trx_commit ,这个值默认的情况下是 1,现在我们来测试一下不同值的操作性能的怎么样的.

innodb_flush_log_at_trx_commit变量 

#创建表
DROP TABLE IF EXISTS words;
CREATE TABLE `words` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
`word` VARCHAR ( 64 ) DEFAULT NULL,
PRIMARY KEY ( `id` )) ENGINE = INNODB;

#创建批量插入数据的存储过程
#执行批量插入100000条数据
delimiter;;
CREATE PROCEDURE idata () BEGIN
	DECLARE
		i INT;
	SET i = 0;
	WHILE
			i < 100000 DO
            start transaction;
			INSERT INTO words ( word )
		VALUES
			(i);
            commit;
		SET i = i + 1;
	END WHILE;
END;;
delimiter;
CALL idata ();

下图中为innodb_flush_log_at_trx_commit 三种不同值的执行性能:

innodb_flush_log_at_trx_commit 为 1,即每次commit都会进行一次写磁盘的操作,如下图 : 需要2分钟

innodb_flush_log_at_trx_commit 为 0,即每秒才刷到os buffer和磁盘,如下图 : 只需要2秒多

innodb_flush_log_at_trx_commit 为 2,即每次提交都刷新到os buffer,但每秒才刷入磁盘中,如下图 : 只需要4秒多

三种的执行性能差别很明显

  • 在值为1的情况下,执行效率最低,但是最安全,在mysqld 服务崩溃或者服务器主机crash的情况下,bin log 只有可能丢失最多一个语句或者一个事务.
  • 在值为0的情况下,虽然执行效率最快,但是在mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失.
  • 在值为2的情况下,虽然执行效率也较快,比0安全,只有在操作系统崩溃或者系统断电的情况下,上一秒钟所有事务数据才可能丢失.
  • 在值为0或2的情况下,会出现丢失1秒日志数据的情况,这是不能接受的,但是在1的情况下,效率极低,怎么办?其实还是有办法的,把执行存储过程改变一下:
delimiter;;
CREATE PROCEDURE idata () BEGIN
	DECLARE
		i INT;
	SET i = 0;
	start transaction;
	WHILE
			i < 100000 DO
			INSERT INTO words ( word )
		VALUES
			(i);
		SET i = i + 1;
	END WHILE;
 commit;
END;;
delimiter;

也就是说多次commit改为一次commit,如下图:保证了每次提交都能写入到磁盘日志中

  通过上面的简单介绍,我们知道,在innodb_flush_log_at_trx_commit 变量值为1的情况下,保证了每次commit都能写入redo log中,且日志记录到redo log中的执行顺序是 : 首先会写入到redo log buffer,接着写入到OS buffer ,然后 OS Buffer调用 fysnc() 把缓存日志写入到磁盘上,也就是说最终磁盘上会存放着redo log文件,接下来就来了解磁盘上的redo log.
  MySQL存储在磁盘上的redo log文件的名称是以ib_logfile为前缀,默认是2个文件来存储,分别是 : ib_logfile0,ib_logfile1,两个文件的大小一样,且由 innodb_log_file_size 变量定义的值,此时就有一个疑问了,两个文件会有写满的情况吗?其实把缓存日志写入到磁盘日志是以循环轮询的方式写入的,即第一个文件(ib_logfile0)写满后,会继续追加写入到第二个文件(ib_logfile1),第二个文件写满后会清空一部分第一个ib_logfile0继续写入,我们可以想象成两个文件围成一个环形去写入日志;那擦除的日志怎么办呢?擦除的日志其实就是把缓冲池中的脏数据刷盘(flush操作),毕竟redo log只是记录内存中哪些数据页修改了,还没真正落盘,所以你擦除掉的内容有可能需要刷盘的就要落盘;redo log还涉及到了checkpoint机制(下边再详细说明),这个机制叫做检查点就是脏页的刷盘操作.
  接下来我们来看看磁盘上的redo log文件的几个核心变量:

show variables like "innodb_log%";

+------------------------------------+----------+
| Variable_name                      | Value    |
+------------------------------------+----------+
| innodb_log_buffer_size             | 1048576  |
| innodb_log_checksums               | ON       |
| innodb_log_compressed_pages        | ON       |
| innodb_log_file_size               | 50331648 |
| innodb_log_files_in_group          | 2        |
| innodb_log_group_home_dir          | .\       |
| innodb_log_spin_cpu_abs_lwm        | 80       |
| innodb_log_spin_cpu_pct_hwm        | 50       |
| innodb_log_wait_for_flush_spin_hwm | 400      |
| innodb_log_write_ahead_size        | 8192     |
+------------------------------------+----------+

innodb_log_buffer_size : 日志内存大小
innodb_log_file_size : 磁盘上每个ib_logfile大小
innodb_log_files_in_group : 组,默认是2个,即2个ib_logfile文件
innodb_log_group_home_dir ： 磁盘上redo log 文件存放路径

PS : 因为Innodb存储引擎存储数据的单元是页,所以redo log也是基于页的格式来记录的.默认情况下，Innodb存储引擎的页大小是16KB(由 innodb_page_size 变量控制),一个页内可以存放非常多的log block(每个512字节),而log block中记录的又是数据页的变化,所以可以说一张页由多个日志块(log block)组成.

现在我们来总结一下什么场景下才会刷日志到磁盘:

  • 1.在innodb_flush_log_at_trx_commit值为1的情况下,每次commit都会继续一次刷盘操作,此操作保证了每次的commit,log buffer都会落盘.
  • 2.在innodb_flush_log_at_trx_commit值为0或2的情况下,此时日志的落盘与commit无关了,而是跟innodb_flush_log_at_timeout 有关了,默认是1秒.
  • 3.当log buffer中已经使用的内存超过一半时.
  • 4.当触发checkpoint机制时.

缓冲池中脏页刷盘(flush操作) : checkpoint机制

  再次回忆一下这篇文章缓冲池,MySQL为减少磁盘的随机读写操作,对数据的DML操作不是直接对磁盘DML操作,而是对内存做DML操作,也就是说缓冲池上的数据才是最新的数据,而MySQL为了保证数据的持久性,最终的数据是保存在磁盘上的,所以缓冲池上的脏数据(脏页)必须刷盘(flush操作).PS : 脏页 : 缓冲池中没有落盘的数据页称为脏页.
  缓冲池上的脏页必须刷盘,而落盘是基于Innodb的checkpoint机制去进行落盘的(flush操作).

还有一点,只要触发checkpoint机制后,不止会将buffer中脏数据页磁盘,脏日志页也会刷到磁盘.(数据和日志都以页的形式存在，所以脏页表示脏数据和脏日志);既然知道checkpoint机制是把内存中的脏数据刷到磁盘的,那么checkpoint机制在什么情况下会触发呢:

  • 当缓冲池内存不足的时候,根据LRU算法会溢出最近最少使用的脏页,将脏页也就是页的新版本刷回磁盘.
  • redo log 不够用的时候,需要擦除一部分空间出来,而擦除的这部分内容的数据页有些就需要刷盘.
  • 当数据库发生宕机时,数据库不需要重做所有的日志,因为checkpoint之前的页都已经刷新回磁盘.数据库只需对checkpoint后的redo log进行恢复,这样就大大缩短了恢复的时间.

其实说白了,要么是缓冲池内存不足或者缓冲池LRU算法淘汰数据页,要么就是redo log空间不足或者数据库宕机后,基于redo log依靠checkpoint机制恢复数据,也就是说 缓冲池 或 redo log 会触发checkpoint机制.

现在知道是缓冲池或者redo log会触发checkpoint机制,再细看MySQL会在什么时候触发checkpoint机制 :

  1. MySQL服务器关闭时(sharp checkpoint),全部页写入磁盘:
    • 数据库正常关闭时,会触发把所有的脏页都写入到磁盘上(这时候logfile的日志就没用了,脏页已经写到磁盘上了).
  2. MySQL运行时(fuzzy checkpoint),部分页写入磁盘(4种情况):
    • Master Thread Checkpoint : 以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘,这个过程是异步的,此时InnoDB存储引擎可以进行其他的操作,用户查询线程不会阻塞.(异步,不影响业务,每次刷的数据页不多,变量innodb_io_capacity越高,每次刷的页越多)
    • FLUSH_LRU_LIST Checkpoint : InnoDB存储引擎需要保证LRU列表中需要有差不多100个空闲页可供使用.(innodb_lru_scan_depth变量来控制每次保证LRU列表中多个个空闲页可供使用)
    • Async/Sync Flush Checkpoint :redo log 快满了,会批量的触发数据页回写,这个事件触发的时候又分为异步和同步,不可被覆盖的redolog占log file的比值：75%--->异步、90%--->同步.(当这两个事件中的任何一个发生的时候,都会记录到errlog中,一旦errlog出现这种日志提示,一定需要加大logfile)PS : Async/Sync Flush Checkpoint是为了保证重做日志的循环使用的可用性
    • dirty page too much checkpoint : 缓冲池中脏页太多就会触发checkpoint,为了保证buffer pool的空间可用性的一个检查点.
    • PS :Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total：表示计算脏页在缓冲池的占比.

PS : 记录检查点的位置是在每次刷盘结束之后才在redo log中标记,所以每次刷盘的时候也会从redo log找到上次Checkpoint的位置开始基于LSN的判断来确定哪些数据页需要刷盘.

redo log 小结

  • 1.redo log 记录了物理页的修改,而且redo log也存在先写内存后刷盘,刷日志页的时候由 innodb_flush_log_at_trx_commit 变量控制,默认是1,即每次commit都会继续一次写磁盘,而0或1的情况下,与commit无关,而是与 innodb_flush_log_at_timeout 变量有关,默认是 1 秒,即每秒执行一次写磁盘操作,虽然在0或1 的情况下提高了执行效率,但是数据库宕机或者操作系统断电的情况下会丢失1秒的日志数据.
  • 2.脏页 : 数据和日志都以页的形式存在,所以脏页表示脏数据和脏日志(但是日志只是纪录物理页的修改,而不是存的整张页,毕竟一页好几k呢)
  • 3.checkpoint机制 : 把缓冲池中的脏页和脏日志刷盘.
  • 4.每次刷盘结束之后会在redo log中标记一个Checkpoint_LSN值,可以在触发Checkpoint机制的时候根据这个值可以判断哪些数据页是否刷盘.

undo log

  undo log 是原子性实现的关键,也就是说事务的回滚是基于undo log的,也与多个行版本控制(MVCC)、隔离性有极大的联系;如果因为某些原因导致事务失败而回滚了,可以借助该undo log进行回滚到原先的数据.
  undo log记录了这些回滚需要的信息,当事务执行失败或调用了rollback,导致事务需要回滚，便可以利用undo log中的记录信息将数据回滚到修改之前的样子.

PS ：undo log日志涉及的知识点可以到这篇文章Mysql的隔离级别(三)的目录 : 多版本控制并发(MVCC) ---- 实现原理

二进制日志文件

bin log

  bin log是MySQL特有的,存在于Server层,所以所有的存储引擎都可以用到bin log,并且binlog是顺序I/O写的.
  bin log记录了所有的DML、DLL操作,但不会记录SELECT和SHOW这类操作.
  对于支持事务的引擎如InnoDB而言,必须要提交了事务才会记录binlog,而对于不支持事务的存储引擎,都是直接纪录到bin log(这里涉及到了二阶段提交(XA),会独立一篇文章说明).
  bin log 什么时候刷新到磁盘跟参数 sync_binlog 相关,默认，sync_binlog=0,性能最好,由文件系统自己控制它的缓存的刷新,但是不安全,系统奔溃的时候会丢失数据;设置1是最安全的,表示每次事务提交,MySQL都会把binlog刷下去,但是性能较差,因为binlog虽然是顺序IO,如果设置sync_binlog=1,多个事务同时提交,同样很大的影响MySQL和IO性能.如果sync_binlog>0,表示每sync_binlog次事务提交,MySQL调用文件系统的刷新操作将缓存刷下去.

PS ：bin log日志最主要涉及的内容是(一、二、三阶段提交),本文暂时不详细说明,会独立一篇文章说明.

结束语

  本文的主角是redo log,并且简单介绍了undo log、bin log,后期还会说说阶段性提交、锁、MVCC、隔离性等等内容.
  希望看完这篇文章的你有所收获,文章中有错误的地方麻烦您指出来,谢谢!
  本文参考连接 : https://www.cnblogs.com/f-ck-need-u/archive/2018/05/08/9010872.html#auto_id_5

目录