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
我们可以查阅 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
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 是如何实现 ACID 中的 D 的？- 柳树
https://zhuanlan.zhihu.com/p/98778890
结尾
相信看了上面的各种日志讲解, 大家已经对前言中提出的问题有了答案, 本篇文章讲解了 MySQL 的 log 问题, 希望大家有所收获
我是 loger, 扫描下方二维码, 更多程序员知识分享等你来看, 每次打开都有新收获, 不妨来关注一下哦, 兄弟们 👍
