系列文章

• 原来一条 select 语句在 MySQL 是这样执行的《死磕 MySQL 系列 一》
• 毕生挚友 redo log、binlog《死磕 MySQL 系列 二》

上期依据一条查问语句查问流程剖析 MySQL 的整体架构。同样，本期也应用一条查问 SQL 语句来做引子。能够必定的是，查问语句执行的流程更新语句同样也会执行。

因而本期的着重点就不在 MySQL 架构图上，文章题目也给出了大家重点，就是要理解 redo log、binlog。

一、redo log

第一步，创立一个表 user，主键是 id，上面是创立语句。

CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `age` tinyint(4) NOT NULL,
 `time` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

插入一条数据

insert into user (`name`,`age`,`time`) values ("咔咔","25",unix_timestamp(now()))

若要将插入的这条数据的 age 改为 26，则须要执行语句

update user set age = 26 where id = 1;

第一期文章中提到一条查问语句的执行流程，该流程与更新语句雷同。这里将那幅图拿过去在相熟一下。

每个模块的性能能够回到第一期文章去查看。

在 MySQL8.0 中 redo log、binlog 日志文件都位于 /var/lib/mysql 此目录下，如图

文件名为 ib_logfile 的是重做日志，undo 结尾的就是回滚日志，对于回滚日志前期进行具体的探讨。

redo log（重做日志）是实现事务持久性必备因素，当一个事务提交后，并非间接批改数据库的数据，而是首先保障在 redo log 中记录相干的操作。

Innodb 存储引擎中的 redo log 大小是固的，上图显示配置了一组两个文件，每个文件大小默认为 48M，应用 innodb_log_file_size 参数来管制单个文件大小，在 MySQL5.6.8 以及之后版本都默认为 48M。

而后 redo log 能够记录 48M 的操作，redo log 是一个闭环的循环写。所设定的文件个数和文件大小不再减少。

write pos 将记录以后地位，同时向后挪动，在 ib-log-file- 3 文件开端后，而后返回 ib-logfilg- 0 文件开始写。

check point 记录的是以后擦除的地位，要使文件循环写入，必须一边擦除。分明数据的前提是要将记录更新到数据文件。

下面的绿色局部就是可写的局部，假如如果 writepos 追上了 checkpoint，那该怎么办？

你必须了解 write pos 的推动是因为在执行更新操作，这样就不能再执行更新操作，直到记录更新到数据文件，而后 check point 进行擦除后才能够继续执行更新操作。

对于 innodb_log_file_size 的设置也是有一些计算规定的，上面将为你介绍。

若 innodb_log_file_size 设置太小，将导致 redo log 文件频繁切换，频繁的触发数据库的检查点（check point），导致记录更新到数据文件的次数减少，从而影响 IO 性能。

同样，如果有一个大的事务，并且所有 redo log 日志都已写满，然而还没有实现，将导致日志无奈切换，从而导致 MySQL 间接堵死。

innodb_log_file_size 设置太大，尽管极大地提高了 IO 性能，然而在 MySQL 重启或宕机时，复原工夫会因为 redo log 文件过大而缩短。而这种复原工夫通常是无法控制的。

在设置正当的 redo log 大小和数量后，Innodb 可能保障，即便数据库产生异样重启，以前提交的记录也不会失落，这一点也称为 crash-safe。

在这里，对 crash-safe 的了解先不提及它是什么，前面的文章会让你明确。

二、如何依据我的项目状况设置 innodb_log_file_size

对于参数 innodb_log_files_in_group 设置 3~4 个就够用了，不必进行优化。

着重探讨 innodb_log_file_size 的大小设置或优化设置。

在 MySQL8.0 之前，通常是计算在一段时间内生成的事务日志 (redo log) 大小，而 MySQL 日志文件最小应承载一小时的业务日志量。

此处的 一段时间 必须视本人的业务状况而定，外界有用 1 分钟的日志量也有 1 小时的日志量来计算。

首先看一下 MySQL 客户端的一个命令 pager，在 MySQL 日常操作中，通过设置 pager 的显示方式，能够大大提高工作效率。

目前，要查看 sequence 在一分钟之内的值，您就能够执行 pager grep sequence，它对 mysql> show engine innodb status\ G select sleep (60); show engine innodbstatus\ G; 返回的后果。

禁止 pager 设置执行 nopager，如果不执行该命令，则只有等到下一次重新启动该命令才会生效。

此处咔咔是在虚拟机上做的操作，能够看到一分钟内是没有任何操作，所以值前后雷同，你能够在测试服务器做测试。

这样计算出来的 select (后边数据 - 后面的数据)/1024/1024*60 asMB_per_hour; 值是一个小时后 redo log 的大小

然而用这种办法计算肯定是不适合的，在一分钟内业务忙碌或者业务闲暇工夫计算出的值都会产生较大误差。

适合的办法是在一天中确定几个工夫点，用一个脚本定时执行，而后记录相应的值，再取平均值，计算出的误差将减至最小。

什么是 sequece?
当每个 binlog 生成时，该值从 1 开始，而后递增，每减少一个事务，sequenumber 就加上 1。

二、binlog

您能够从总体上理解到 MySQL 架构分为两层，一个是 server 层，另一个是存储引擎层。

server 层当然是负责性能方面的，而存储引擎层则负责解决与存储相干的操作。

而且下面提到的 redo log 是 Innodb 存储引擎层特有的，其它存储引擎是不具备的，而 server 层也有本人的日志记录，就是将要聊到的 binlog。

redo log 和 binlog 的区别

redo log 是 Innodb 引擎特有的，而 binlog 是 MySQLserver 层特有的，所有引擎都能够应用。

redo log 是物理日志，它记录的是一条更新操作所做的批改，binlog 是逻辑日志，记录的是一条更新语句执行逻辑

redo log 是循环写的，并且空间是固定的，比方下面配置 4 个 1GB 的 redo log 文件，binlog 是追加写的，这个文件写完了，换下一个文件，不会笼罩以前的日志。这也就是你常常看到只有你有残缺的 binlog 文件就能够给你复原到你想要的数据。

MySQL 为什么会有俩份日志呢？

在没有 Innodb 存储引擎之前，MySQL 默认存储引擎是 MyIsam，但 MyIsam 是没有重启恢复能力的，binlog 日志也仅用于归档。

Innodb 是另一家公司以插件的模式引入到 Mysql，既然 binlog 没有重启复原的能力，那么我就应用 redo log 来实现重启复原的性能。

这就导致了当你应用 Innodb 存储引擎时会写俩份日志。

三、什么是两阶段提交

对 redo log、binlog 有了肯定的意识后再来看看一条更新语句的执行流程。

update user set age = age + 1 where id = 1;

• 执行器先到引擎层找到 id = 1 这一行，因为 ID 是主键，所以会在主键索引树找到这一行。如果 ID= 2 这一行所在的数据页原本就在内存中，就间接返回给执行器。否则，须要先从磁盘中读入内存，而后再返回。
• 执行器拿到存储引擎返回 id = 2 后果后，给 age 加上 1，原来是 25，当初就是 26, 在调用引擎接口写入这行新数据。
• 引擎将这行数据先更新到内存中，同时将这个更新操作记录到 redo log 中，此时 redo log 处于 prepare 状态。而后告知执行器执行实现了，随时能够提交事务。
• 接着执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
• 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交 commit 状态，更新实现。

到这里你应该就清晰了，一条更新 SQL 会先写 redo log 再写 binlog，这也就是题目为什么叫 毕生挚友 redo log、binlog。

四、为什么须要两阶段提交

是为了让 redo log 跟 binlog 两份日志之间的逻辑统一，看上面俩种状况。

先写 redo log 后写 binlog

• 更新语句为 age = age +1
• 将数据写入 redo log，MySQL 过程异样重启
• 此时 binlog 还没有开始写
• 零碎重启后进行数据恢复此时的值为 26
• 须要搭建从库时须要拿 binlog 进行复原数据，但此时 age = age +1 这行的操作是没有记录到 binlog 的
• 那么此时的从库就会少这一次的更新，复原进去的 age 仍然是 25，造成于主库数据不统一。

先写 binlog 后写 redo log

• 更新语句为 age = age +1
• 将数据写入 binlog，MySQL 异样重启
• 此时 redo log 还没写
• MySQL 零碎重启，这个更新操作是对于 redo log 是不存在的，所以重启后的值仍然是 25
• 但 binlog 中的值已将是 26 了
• 须要搭建从库时，从库的值是 26，主库的值是 25，造成主从数据不统一

所以说，如果不应用两阶段提交，那么原库和用它的 binlog 日志复原进去的库数据是不统一的。

五、《孔乙己》让你明确 redo log 是什么

来看一个初中九年级语文课文中《孔乙己》这篇文章，就算不记得内容，题目总记得哈！

这个案例也是看丁老师文章中提到的，为什么丁老能够灵便的应用这个案例来讲 redo log 而咱们想不到呢？

其本质起因是对知识点没有了解透彻，应用生存案例来解释技术是让人最容易了解并不难忘记的。

《孔乙己》中的主人公就叫他酒店掌柜，掌柜的有俩件法宝让比其余老板工作效率高很多。一个是小黑板另一个是账本。

试想一下如果有客人要赊账，是间接写到黑板效率高，还是翻稀稀拉拉的账原本的快呢？

掌柜必定会抉择先记录到黑板上，等人少或者不忙时再把黑板的记录写到账本中。

反之老板没有黑板的话，只能在稀稀拉拉的账本中先找到赊账人的名字，如果之前有赊账记录追加，找了一遍发现没有才进行新增。

这个过程不仅繁琐而且效率低的让人难以承受，如果酒店客人多老板是记录不过去的。

同样，在 MySQL 中也会存在这个问题，每次执行更新语句都须要先找到那条记录，而后再更新，整个过程 IO 老本、查找老本都很高。所以 MySQL 也利用了酒店掌柜的智慧应用黑板来晋升执行效率。

画一幅图让大家能更好的了解掌柜、黑板、在 MySQL 中的对应关系。

六、redo log 参数详解

事务的持久性就是通过重做日志来实现的。

当提交事务之后，并不是间接批改数据库的数据的，而是先保障将相干的操作记录到 redo 日志中。

数据库会依据相应的机制将内存的中的脏页数据刷新到磁盘中。

上图是一个简略的重做日志写入流程。

在上图中提到俩个生疏概念，Buffer pool、redo log buffer，这个俩个都是 Innodb 存储引擎的内存区域的一部分。

而 redo log file 是位于磁盘地位。

也就说当有 DML（insert、update、delete）操作时，数据会先写入 Buffer pool，而后在写到重做日志缓冲区。

重做日志缓冲区会依据刷盘机制来进行写入重做日志中。

这个机制的设置参数为innodb_flush_log_at_trx_commit ，参数别离为 0,1，2

上图即为重做日志的写入策略。

• 当这个参数的值为 0 的时，提交事务之后，会把数据寄存到 redo log buffer 中，而后每秒将数据写进磁盘文件
• 当这个参数的值为 1 的时，提交事务之后，就必须把 redo log buffer 从内存刷入到磁盘文件里去，只有事务提交胜利，那么 redo log 就必然在磁盘里了。
• 当这个参数的值为 2 的状况，提交事务之后，把 redo log buffer 日志写入磁盘文件对应的 os cache 缓存里去，而不是间接进入磁盘文件，1 秒后才会把 os cache 里的数据写入到磁盘文件里去。

服务器异样进行对事务如何应答（事务写入过程）

• 当参数为 0 时，前一秒的日志都保留在日志缓冲区，也就是内存上，如果机器宕掉，可能失落 1 秒的事务数据。
• 当参数为 1 时，数据库对 IO 的要求就十分高了，如果底层的硬件提供的 IOPS 比拟差，那么 MySQL 数据库的并发很快就会因为硬件 IO 的问题而无奈晋升。
• 当参数为 2 时，数据是间接写进了 os cache 缓存，这部分属于操作系统局部，如果操作系统局部损坏或者断电的状况会失落 1 秒内的事务数据，这种策略绝对于第一种就平安了很多，并且对 IO 要求也没有那么高。

小结

对于性能：0>2>1

对于平安：1>2>0

依据以上论断，所以说在 MySQL 数据库中，刷盘策略默认值为 1，保障事务提交之后，数据相对不会失落。

保持学习、保持写作、保持分享是咔咔从业以来所秉持的信念。愿文章在偌大的互联网上能给你带来一点帮忙，我是咔咔，下期见。