2、《日志系统》

02 | 日志系统：一条SQL更新语句是如何执行的？

 

前言

    你可能经常听 DBA 同事说，MySQL 可以恢复到半个月内任意一秒的状态，惊叹的同时，你是不是心中也会不免会好奇，这是怎样做到的呢？

    日志系统，这不免让我联想到了redis的aof日志存储。

 

    无论是查询语句，还是更新语句，他们都会走一遍第一章里面的架构流程图。

    只不过，更新语句要额外涉及两个最重要的日志模块：redo log（重做日志）、binlog (归档日志)。

 

一、重要的日志模块：redo log (重做日志：InnoDB引擎层特有的日志)

 

        每次更新语句，不会立即去磁盘进行数据更新，因为磁盘的IO成本、查找成本很高。为了解决这个问题，MySQL采用了WAL技术——Write-Ahead Logging（ 预写式日志记录），它的关键思路就是先写日志，后写磁盘。

 

        具体来说，一条更新语句，InnoDB引擎会将记录写在redo log中，并且更新内存，更新操作就结束了。当系统空闲时候，再将这个操作记录更新到磁盘中。

 

        redo log的大小是固定的， 比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

                           

                                                redo log环形结构图

        write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

        有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

 

二、重要的日志模块：binlog(归档日志：Server层的日志)

    

    为什么会有两份日志呢？

    因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

 

三、redo log和binlog的不同

 

• redo log是InnoDB引擎特有的日志，只有InnoDB可以使用；binlog是服务层的日志，所有引擎都可以使用binlog。
• redo log是物理日志，记录了“某个数据页上做了什么修改”；而binlog是逻辑日志， Binlog有两种模式，statement 格式的话是记sql语句， row格式会记录行的内容，记两条，更新前和更新后都有。比如“给id=2这一行的c字段加1”.
• redo log的空间有限，是环形结构循环读写；而binlog是追加写，当binlog文件达到一定大小之后会切换到下一个，并不会覆盖以前的日志。

 

四、update语句内部执行流程

 

 

 

 

                        update 语句执行流程图

 

 redo log有点绕，在第三步中拆分了两个操作：prepare 和 commit，这就是"两阶段提交"，是为了保证redo log和binlog保证一致性的问题。

 

 

 

五、两阶段提交

 

    反证法：假如说不使用两阶段提交，而是redo log写完了再写binlog，或者反过来，那么将是下面的情况：

• 先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
• 先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

 

        可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

 

        简单说，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

 

 

 

 

六、小结

    redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数我建议你设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

 show variables like 'innodb_flush_log_at_trx_commit';

innodb_flush_log_at_trx_commit={0|1|2} # 指定何时将事务日志刷到磁盘，默认为1。 

0表示每秒将"log buffer"同步到"os buffer"且从"os buffer"刷到磁盘日志文件中。 

1表示每事务提交都将"log buffer"同步到"os buffer"且从"os buffer"刷到磁盘日志文件中。

2表示每事务提交都将"log buffer"同步到"os buffer"但每秒才从"os buffer"刷到磁盘日志文件中。

 

    sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。

 show variables like 'sync_binlog';

 

七、个人总结

 

1、redo log为prepare状态--------> 2、binlog日志记录---------> 3、redo log为commit状态

@：当执行1崩溃失败，重启后本事务丢失，redolog 和binlog都没有记录。一致性ok

@：当执行2崩溃，重启后binlog中没记录，redo log中发现为prepare状态，就会回滚事务。一致性ok

@：当执行3崩溃，重启后，虽然redo log为prepare，但是binlog中有记录，所以会继续commit。最终redo log为commit，binlog中有记录。一致性ok