MySQL – 几种 log 的深入解析

前言

本篇文章已收录到 GitHub 仓库 https://github.com/logerJava/loger‍

前面在 MySQL基础概念 中我们有提到几种日志, 本篇文章就来详细了解以下 MySQL 的几种 log, 可能大家会困惑, log 应该只是程序运行的记录, 应该没有什么必要学习, 那么 loger 就出几道 MySQL 数据库的面试题, 我们来带着疑问一起看一下 log 到底在 MySQL 中干了什么 ?

• 了解过 MySQL 的 ACID, 那么你知道 ACID 在 MySQL 中是怎么实现的吗 ?

• MySQL 在写入数据时, 先写入内存, 再写入磁盘, 那么如果在内存数据还未同步到磁盘时掉电了, MySQL 会怎么处理 ?

• 在工作中一般怎么确认生产环境的慢 SQL ?

常见的 log

在 MySQL基础概念 loger 说认为有用的只有四种日志, 但是本篇文章既然是讲解日志, 那么肯定不能只讲四种, 下面是 MySQL 的七种日志, 我会有侧重的逐一分析

• 重做日志 (redo log)

• 回滚日志 (undo log)

• 二进制日志 (binlog)

• 错误日志 (errorlog)

• 慢查询日志 (slow log)

• 一般查询日志 (general log)

• 中继日志 (relay log)

redo log

1

redo log 的概念

在了解 redo log 之前我们先要补充一个概念知识 :

• MySQL 是按页为单位来读取数据的，一个页里面有很多行记录，从内存刷数据到磁盘，也是以页为单位来刷

现在我们考虑一个场景, 在数据库执行如下 SQL 语句 :

UPDATE student SET name = 'loger' WHERE studentId = 3

在执行这条 SQL 语句时, MySQL 的操作如下 :

• 首先判断内存中有没有 studentId = 3 的数据

• 如果没有就去磁盘找到这条数据所在的页, 将整页数据加载到内存

• 然后找到 studentId = 3 的行数据, 将内存中的 name 修改为 loger

此时, 就会发现数据存在不一致的情况, 内存中的数据为正确数据, 修改过后的新的数据, 而磁盘中的数据为未修改的旧的数据, 这时磁盘对应的页的数据称为 脏页

回到上面提到的第二题, 此时如果掉电了, 怎么办 ? 掉电了数据就没了, 那数据不就不一致了

基于这种场景, MySQL 的解决方案是, 将你对页的操作, 修改了什么内容, 记录下来, 保存到磁盘, 也就是这里要讲的 redo log 中

在 redo log 写入成功后, MySQL 就认为事务已经提交成功了, 数据已经持久化, 并会在空闲时间, 将内存数据刷入磁盘

如果此时掉电, 那么解决起来也简单了, 只需要在重启后将 脏页 数据加载到内存中, 然后利用 redo log 脏页就会修正了

没错, 我已经猜到了, 伙伴们, 你一定会独立思考然后提出这个问题, 如果我写入内存成功了, 但是 redo log 刷到磁盘失败了呢 ? 这个疑问先留着一会解决

2

redo log 如何存储

我们可以查阅 MySQL 的官方文档 :

我们可以大致分析出如下几个点 :

• redo log 记录了 SQL 语句以及其他 API 对表产生的变化, 也就是物理变化

• redo log 存储在磁盘, 用于 crash recovery 后修正数据, 也就是处理宕机, 掉电等问题

• redo log 默认有两个文件

• redo log 采取循环写方式 (circular)

也就是说实际上 redo log, 默认是在 ib_logfile0 和 ib_logfile1 循环来回写的, 那么既然用了默认这个词肯定就有下文了, 冷知识讲解 :

• innodb_log_file_size：设置每个 redo log 文件的大小，默认是 50331648 byte，也就是 48 MB

• innodb_log_files_in_group：设置 redo log 文件的数量，默认是 2，最大值是 100

还有一件事, redo log 在写入磁盘时并不是随机 I/O 的, 而是顺序 I/O 所以写入速度很快, 而 redo log 文件体积又很小, 恢复速度很快

来, 现在回手掏一下, 事务有 ACID 的四个特性, 那么回顾上面讲的 redo log, InnoDB 就是利用 redo log 来实现持久性的

binlog

1

binlog 的概念

还是上面的例子 :

UPDATE student SET name = 'loger' WHERE studentId = 3

在执行这条 SQL 语句时, 其实仅仅生成了 redo log, 还生成了 binlog

binlog 的概念 : binlog 记录了数据库表结构和表数据变更, 比如 insert, delete, update, create 等, 不会记录查询

不同于 redo log, binlog 是 MySQL Server 层的功能, 而 redo log 则是 InnoDB 存储引擎的功能, 也就是说, redo log 只要使用了 InnoDB 作为存储引擎就会有, 而 binlog 只要使用的是 MySQL 就会有

2

binlog 的作用

MySQL 既然将 binlog 放在了 Server 层, 那么就代表 binlog 提供了通用的能力, 主要有两个作用数据恢复, 主从复制

想一想你们 DBA 平时是怎么恢复数据, 还原数据的, 我们回到 binlog, 是不是只需要找到前一时间点的 binlog 重放一下就可以还原了

在主从复制的场景也是同一个原理, master 将 binlog 发送给 slave, slave 执行 binlog 那么就复制了

redo log 与 binlog

通过上面的分析, 我们已经了解了这两种 log, 大家会发现, redo log 和 binlog 都可以作为恢复手段, 但其实他们的细节部分还是不一样的, 我们来看一下他们的区别

1

存储内容不同

binlog 记录的是 insert, delete, update, create 等 SQL 语句, 而 redo log 记录的是物理修改内容

可以理解为, binlog 记录的是逻辑变化, redo log 记录的是物理变化

2

功能不同

• redo log 写入内存, 如果数据库宕了, 我们可以通过 redo log 恢复内存还没有刷盘的数据, 可以恢复宕掉之前的内存数据

• binlog 可以保持主从一致性, 如果整个数据库都被删除了, binlog 存储着所有数据的变更情况, 可以通过回放 binlog 进行恢复

需要注意的是, 如果整个数据库都被删除了, redo log 是无法恢复的, 因为 redo log 并不会记录历史的所有数据, 文件内容会被覆盖

3

写入细节

刚才有提到 redo log 和 binlog 都会在执行 update 的时候写入, 那么他应该是怎么写入的呢 ?

• 写入 : redo log (prepare)

• 写入 : binlog

• 写入 : redo log（commit)

为什么写入 redo log 需要两段式提交, 而不是一次性写入呢 ? 我们可以分为两种情况分析一下

先写入 redo log, 再写入 binlog

• 如果 redo log 写入成功, 写入 binlog 失败了, 此时出现宕机情况, 我们根据上面的知识, master 库采用 redo log 恢复数据, 但是因为 binlog 写入失败了, 没有 binlog, 那么从库就没有这些数据, 导致主从不一致现象

先写入 binlog, 再写入 redo log

• 与上面的类似, 只不过会反过来, binlog 存在而 redo log 不存在, 会导致从库的数据是最新的, 而主库出现问题

两段式提交 :

• 先写入 redo log, 如果失败了, 回滚, 不再继续写入 binlog

• 如果 redo log 写入成功, binlog 写入失败, 回滚, 删除无效的 binlog

• 只有当 redo log 和 binlog 都写入成功了, 此次事务才会提交

也就是说, MySQL 需要保证 redo log 和 binlog 是一致的

undo log

undo log 主要有两个作用 : 回滚, 多版本控制(MVCC)

这里简单讲解一下 undo log 的作用, 详细的会放到 MVCC 篇去讲解, 因为多版本控制其实还是挺复杂的, 要讲很多东西

undo log 实际上存储的也是逻辑日志, 在执行 update 时, 不仅记录上面的两个日志, undo log 也会进行记录, 可以这样理解, 比如你进行了插入操作, 那么 undo log 就会记录一条删除操作, 如果你将 A 修改为了 B, 那么 undo log 里面就会记录一条 B 修改为 A 的记录

目的就是为了保证回滚, 这些 undo log 存储的记录就相当于之前 SQL 前的一个版本, 回滚时直接返回上一个版本, 就一点毛病没有

errorlog

从名字就可以看出来, 是错误日志, 记录着 MySQL 启动, 运行期间, 停止时的错误相关信息, 默认情况下这个是关闭的

我们可以指定 errorlog 的输出路径 :

• 编辑 my.cnf 写入 log-error = [path]

• 通过命令参数错误日志 mysqld_safe –user=mysql –log-error=[path] &

general query log

普通查询日志, 记录了 MySQL 接收到的所有查询或命令操作, 无论是正确还是错误的都会进行记录, 因为记录的比较频繁, 产生开销较大所以默认是关闭的

slow query log

慢查询日志记录的是执行时间超过 long_query_time 和没有使用索引的查询语句, 只记录成功的语句

相关参数 :

• slow_query_log : 1. 开启; 0. 关闭

• long_query_time : 慢查询阈值

• log_output : 输出方式

我们可以通过如下方式配置慢查询 :

show variables like '%slow_query_log%';
set global slow_query_log=1;
show variables like '%slow_query_log%';

要注意的是, 此处修改只对当前数据库生效, 在 MySQL 重启后会失效, 如果需要配置永久生效需要修改 my.cnf 文件

参考

• MySQL 是如何实现 ACID 中的 D 的？- 柳树

  https://zhuanlan.zhihu.com/p/98778890

结尾

相信看了上面的各种日志讲解, 大家已经对前言中提出的问题有了答案, 本篇文章讲解了 MySQL 的 log 问题, 希望大家有所收获

我是 loger, 扫描下方二维码, 更多程序员知识分享等你来看, 每次打开都有新收获, 不妨来关注一下哦, 兄弟们 👍