understand mysql log

Understanding MySQL Log

本文讨论的MySQL的存储引擎为InnoDB的基础上。

mysql-log-1

这是一条很简单的更新 SQL，从 MySQL 服务端接收到 SQL 到落盘，先后经过了MySQL Server 层和 InnoDB 存储引擎。

MySQL Server层就像产品经理一样，分析需求给出执行计划。

InnoDB存储引擎就像程序猿一样实现产品的需求。

你可能很好奇，Buffer Pool是什么，右边那么多的log文件又有什么用呢？我像你一步步介绍。

分层结构

20220603134952

你是否跟我一样好奇，为什么MySQL要采用分层的结构？

聪明的你，可能已经想到了：就是为了”解藕”。

Server层和存储引擎各司其职，分工明确。用户也可以根据不同的需求去使用不同的存储引擎。

Buffer Pool

Buffer Pool 就是将磁盘 IO 转换成了内存操作，节省了时间，提高了效率。

假设我们执行下面的sql

事务 A：update user set age = 2
事务 B：update user set age = 3
事务 C：update user set age = 4

如果没有Buffer Pool，我们每次更新数据都需要从磁盘读取数据到InnoDB工作引擎的内存中（1次IO），然后更新回磁盘中（1次IO）。一次更新操作就需要2次IO，总共三次更新需要6次IO。

如果引入Buffer Pool，我只需要首次更新数据的时候从磁盘读取数据到内存中，在第三次更新完数据之后，写回磁盘中。只需要2次IO，节省了4次的IO操作。

这里只是非常简单的描述Buffer Pool其中的一小块功能，Buffer Pool比这复杂的多。

这里我们需要引入其他的问题，确实Buffer Pool提高了我们的操作效率。但是Buffer Pool是基于内存的操作，假设我们断电或者宕机了，在内存还没有写回到磁盘的数据，我们全部都丢失了。

MySQL是我们最为核心并且最可靠的中间件之一，InnoDB是如何做到数据不丢失的呢？

这里就开始引入我们的第一个日志：redo log

Redo log（恢复）

redo顾名思义是重做的意思。

那我们看下redo log是如何做到数据不丢失的？

从开局的第一张Gif图片我们可以知道，整个更新流程的过程。就是在修改数据之后，立刻把在Buffer Pool内存中的数据同步到redo log磁盘文件当中。即使断电，在Buffer Pool中所有数据丢失。在MySQL重启之后，优先从redo log恢复到Buffer Pool中。这样既利用了Buffer Pool的内存高效性，也保证了数据不会丢失。

这时候老板，你可以要问了？

既然你都写redo log的磁盘文件中，为什么不直接数据磁盘文件当中呢？

这边是关于磁盘的写的问题，redo log的磁盘文件是顺序写的，而数据磁盘文件是随机写的。磁盘的顺序写比随机写性能高效很多。

这种先预写日志后面再把数据刷盘的机制，有一个很高大上的名次-WAL（Write-ahead-logging）。翻译为中文：预写式日志。

虽然磁盘顺序写的方式效率很高了，但是和内存的操作比起来还是有一定差距的？

那还有什么办法进一步优化呢？

答案是有的！就是增加一个redo log buffer，作用跟buffer pool一样在内存级别的操作。用这种套娃的方式进一步提高性能。

那redo log buffer具体是怎么配合刷盘的？

这时候就要重点介绍：innodb_flush_log_at_trx_commit参数了。

innodb_flush_log_at_trx_commit=1：实时写，实时刷
innodb_flush_log_at_trx_commit=0：延迟写，延迟刷
innodb_flush_log_at_trx_commit=2：实时写，延迟刷

这里的写，我们可以理解为从redo log buffer写到os cache（系统缓存）。

这里刷，我们可以理解为把os cache刷到磁盘文件。

此时我们在网上看到很多关于这个参数的设置，推荐innodb_flush_log_at_trx_commit=2。对此我不赞同。

上一秒钟所有事务数据才可能丢失，MySQL是我们系统最可靠的中间件系统。不允许这样的事情发生！对此我认为次参数应该innodb_flush_log_at_trx_commit=1，我们系统也是这样配置。

华为云数据RDS参数解析

undo log（回滚）

undo 翻译成中文是撤销、回滚的意思，undo log 的主要作用也就是回滚数据。

一条数据已经在Buffer Pool和磁盘文件都已经被更新了，那数据怎么做回滚呢？

其实MySQL的实现方案很简单，就是把在数据变更之前的数据在undo log当中存储一份。需要回滚的时候，直接读取出来。

需要注意的是，undo log 默认存在全局表空间里面，你可以简单的理解成 undo log 也是记录在一个 MySQL 的表里面，插入一条 undo log 和插入一条普通数据是类似。

也就是说，写 undo log 的过程中同样也是要写入 redo log 的。

bin log （归档）

undo log 记录的是修改之前的数据，提供回滚的能力。

redo log 记录的是修改之后的数据，提供崩溃恢复的能力。

那bin log是做什么的？

bin log记录的是数据之后的数据，用于归档。

bin log和redo log日志类似，也有自己的刷盘策略，通过sync_binlog参数控制。

sync_binlog=0：每次提交事务前将bin log写入os cache，由操作系统控制什么时候刷到磁盘。
sync_binlog=1：采用同步写磁盘的方式来写bin log，不使用os cache来写bin log
sync_binlog=N：当进行n次事务提交之后，调用一次 fsync 将 os cache 中的 binlog 强制刷到磁盘

推荐参数设置为sync_binlog=1。

这时候，读者就要问了，既然bin log和redo log都是记录修改后的值，这两者有什么区别呢？redo log可以取代bin log吗？

binlog 是逻辑日志，记录的是对哪一个表的哪一行做了什么修改；redo log 是物理日志，记录的是对哪个数据页中的哪个记录做了什么修改，如果你还不了解数据页，你可以理解成对磁盘上的哪个数据做了修改。
binlog 是追加写；redo log 是循环写，日志文件有固定大小，会覆盖之前的数据。
binlog 是 Server 层的日志；redo log 是 InnoDB 的日志。如果不使用 InnoDB 引擎，是没有 redo log 的。

上面我们回答了二者的区分，那redo log可以取代bin log吗？

我觉得是可以的，并且MySQL也是这样的能力的，但是为什么MySQL官方没有这样做呢？

我认为最大的原因是”没必要”！！为什么要做一件费力不讨好的事情呢。