Mysql日志-RedoLog、UndoLog和BinLog
一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块,最后到达存储引擎。
那么,一条更新语句的执行流程又是怎样的呢?以及MySQL可以恢复到半个月内任意一秒的状态,这是怎样做到的呢?
InnoDB是事务的存储引擎,其通过Force Log at Commit机制实现事务的持久性,即当事务提交(COMMIT)时,必须先将该事务的所有日志写入到重做日志文件进行持久化,待事务的 COMMIT操作完成才算完成。这里的日志是指重做日志,在InnoDB存储引擎中,由两部分组成,即redo log和undo log。redo log用来保证事务的持久性,undo log用来帮助事务回滚及Mvcc的功能。redo log基本上都是顺序写的,在数据库运行时不需要对redo log的文件进行读取操作。而undo log是需要进行随机读写的。
WAL: Write-Ahead Logging
先写日志,再写磁盘。具体说,当有一条记录需要更新的时候,InnoDB引擎就会先把记录写到redo log里面,并更新内存,这个时候更新计算完成了。同时InnoDB引擎会在在系统比较空闲的时候,将这个操作记录更新到磁盘里。
WAL机制主要得益于两个方面
1.redo log 和 binlog 都是顺序写,磁盘的顺序写比随机写速度要快;
2.组提交机制,可以大幅度降低磁盘的 IOPS 消耗。
只要redo log和binlog保证持久化到磁盘,就能确保MySQL异常重启后,数据可以恢复。
REDO LOG(redo log保证事务的持久性)
redo log通常是物理日志,记录的是数据页的物理修改,而不是某一行或某几行修改成怎样怎样,它用来恢复提交后的物理数据页(恢复数据页,且只能恢复到最后一次提交的位置)。
redo log包括两部分:一是内存中的日志缓冲(redo log buffer),该部分日志是易失性的;二是磁盘上的重做日志文件(redo log file),该部分日志是持久的。
InnoDB的redo log是固定大小的,比如可以配置为一组4个文件,每个文件的大小是1GB,那么总共就可以记录4GB的操作。从头开始写,写到末尾就又回到开头循环写。
write pos是当前记录的位置,一边写一边后移,写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置,也是往后推移并且循环的,擦除记录前要把记录更新到数据文件。
write pos和checkpoint之间的是还空着的部分,可以用来记录新的操作。如果write pos追上checkpoint,表示日志满了,这时候不能再执行新的更新,得停下来先擦掉一些记录,把checkpoint推进一下。
有了redo log,InnoDB就可以保证即使数据库发生异常重启,之前提交的记录都不会丢失,这个能力称为crash-safe。
checkpoint机制
checkpoint:将缓冲池中的脏页刷回到磁盘。
InnoDB存储引擎内部,两种checkpoint,分别为:
1.Sharp Checkpoint
2.Fuzzy Checkpoint
Sharp Checkpoint发生在数据库关闭时,将所有的脏页都刷新回磁盘,这是默认的工作方式,即参数:innodb_fast_shutdown=1。
在数据库运行时,InnoDB存储引擎内部采用Fuzzy Checkpoint,只刷新一部分脏页。
Fuzzy Checkpoint的几种情况:
1.MasterThread Checkpoint
异步刷新,每秒或每10秒从缓冲池脏页列表刷新一定比例的页回磁盘。异步刷新,即此时InnoDB存储引擎可以进行其他操作,用户查询线程不会受阻。
2.FLUSH_LRU_LIST Checkpoint
InnoDB存储引擎需要保证LRU列表中差不多有100个空闲页可供使用。在InnoDB 1.1.x版本之前,用户查询线程(mysql5.6之后放在了单独的进程Page Cleaner中进行)会检查LRU列表是否有足够的空间操作。如果没有,根据LRU算法,溢出LRU列表尾端的页,如果这些页有脏页,需要进行checkpoint。
设置参数:innodb_lru_scan_dept:控制LRU列表中可用页的数量,该值默认1024
3.Async/Sync Flush Checkpoint
指重做日志不可用的情况,需要强制刷新页回磁盘,此时的页时脏页列表选取的。
这种情况是保证重做日志的可用性,说白了就是,重做日志中可以循环覆盖的部分空间太少了,换种说法,就是极短时间内产生了大量的redo log。
UNDO LOG(undo log保证事务的一致性)
重做日志记录了事务的行为,可以很好地通过其对页进行“重做”操作。但是事务有时还需要进行回滚操作,这时就需要undo。因此在对数据库进行修改时, InnoDB存储引擎不但会产生redo,还会产生一定量的undo。
redo存放在重做日志文件中,与redo不同,undo存放在数据库内部的一个特殊段(segment)中,这个段称为undo段(undo segment)。undo段位于共享表空间内。
除了回滚操作,undo的另一个作用是MVCC,即在 InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时,若该记录已经被其他事务占用,当前事务可以通过undo读取之前的行版本信息,以此实现非锁定读取。
undo log会产生redo log,也就是undo log的产生会伴随着 redo log的产生,这是因为undo log也需要持久性的保护。
BINLOG
Server层也有自己的日志,称为binlog(归档日志)。
binlog是MySQL数据库的二进制日志,用于记录用户对数据库操作的SQL语句((除了数据查询语句)信息。
binlog的三种格式对比
statement、row、mixed(前两种格式的混合)。
如果要在表中删除一行数据的话,我们来看看这个delete语句的binlog是怎么记录的。
statement->记录的是sql原文
优点:不需要记录每一条SQL语句与每行的数据变化,日志会比较少,减少了磁盘IO,提高性能。
缺点:在某些情况下会导致master-slave中的数据不一致
row格式:
运行这条delete命令产生了一个warning,原因是当前binlog设置的是statement格式,并且语句中有limit,所以这个命令可能是unsafe的。
在主库执行这条SQL语句的时候,用的是索引a;而在备库执行这条SQL语句的时候,却使用了索引t_modified。
优点:不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。
缺点:会产生大量的日志,尤其是alter table的时候会让日志暴涨。
REDO LOG 和 BINLOG 日志有以下三点不同:
1.redo log是InnoDB引擎特有的;binlog是MySQL的Server层实现的,所有引擎都可以使用。
2.redo log是物理日志,记录的是“在某个数据页上做了什么修改”;binlog是逻辑日志,记录的是这个语句的原始逻辑,比如“给ID=2这一行的c字段加1 ”。
3.redo log是循环写的,空间固定会用完;binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个,并不会覆盖以前的日志。
[资料来源]
1.Mysql实战45讲-丁奇
2.MySQL技术内幕