MySQL 的各种日志

用户头像
超超不会飞
关注
发布于: 2020 年 06 月 01 日
MySQL的各种日志

《MySQL实战45讲》笔记。

1. redo log——只是一块粉板



孔乙己又来酒馆喝酒,兜里没钱手机也没电了,只能向掌柜的赊账。掌柜有一块粉板,当客人要赊账的时候就往上写一笔,等客人少的时候或者粉板写满了就记到账本里去。还好有这块粉板,不然每次客人要赊账,掌柜都要翻看账本,在密密麻麻的账本里找到赊账客人的名字绝对不是一件容易的事,有了粉板,掌柜只要往粉板上记一笔:“孔乙己 赊 两文”,空闲的时候再更新到账本里去,简单多了。



同样的,MySQL也有一块“粉板”—— redo log。更新的时候,先写到 redo log 和内存里,这次更新就算是结束了。等到合适的时机再写到磁盘里,大大减小了写磁盘的次数。



redo log 是固定大小、“循环写”的,就像粉板一样,顶多也就记个十几二十条,多了就记不下了,这时会把粉板上的帐都写到账本里,再擦掉粉板,从头开始记。假设 redo log 配置了4组文件,每个文件 1G ,一共可记录 4G 的操作,写满了就会擦掉一部分记录。



redo log 是物理日志,记录的是“在某个数据页上做了什么修改”。



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



2. binlog



binlog 是 MySQL 的 Server 层实现的,所有引擎都可以使用。



binlog 是逻辑日志,记录的是这个语句的原始逻辑,比如”给 ID=2 这一行的 c 字段加1“。



binlog 是“追加写”的,一个文件写完了会切换到下一个,不会覆盖以前的日志。



为什么有了 redo log 还需要 binlog?



其实 redo log 才是那个新来的仔。MySQL 自带了 binlog 日志用于归档,没有 crash-safe 的能力。InnoDB 引擎以插件的形式引入 MySQL 时,为了能够实现 crash-safe 的能力,引入了 redo log 。



一般我们用 binlog 做主从复制,数据恢复等操作。



binlog 是如何做数据恢复的?



一般我们做数据库备份是一周一备,一天一备,也可能一月一备。



假设今天中午12点,我们发现部分数据被误删了。需要恢复到昨天晚上8点这个时间段。但是数据库是每天凌晨3点的时候备份,离我们最近的一份备份数据已经缺失,只能恢复到昨天凌晨3点。这个时候我们就可以拿出昨天凌晨3点到晚上8点这个时间段的 binlog,重放到数据缺失前的那个时刻。在把这份数据恢复到线上数据库去。



3. 更新操作的执行流程



了解了 redo log 和 binlog 这两个日志的概念,我们再来看看执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程。



  1. 执行器先找引擎取 ID=2 这一行。如果数据在内存就直接返回,如果不在内存就先从磁盘读入内存,再返回。

  2. 执行器拿到数据,给这行的 c 值加 1。

  3. 引擎将这行数据的改动更新到内存中,同时将这个更新操作记录到 redo log 里面,此时 redo log 处于prepare 状态。然后告知执行器执行完成了,随时可以提交事务。

  4. 执行器生成这个操作的 binlog,并把 binlog 写入磁盘。

  5. 执行器调用引擎的提交事务接口,引擎把刚刚写入的 redo log 改成 commit 状态,更新完成。



下图出自《MySQL实战45讲》,浅色框表示是在 InnoDB 内部执行的,深色框表示实在执行器中执行的。





4. redo log 和 binlog 的两阶段提交



为什么需要两阶段提交?



我们先假设没有两阶段提交时,可能会有以下两种情况:



  1. redo log 提交成功了,这时候数据库挂掉导致 binlog 没有成功写入。数据库重启之后通过 redo log 把数据恢复回来,但是 binlog 没有成功写入,导致我们在做主从复制或者数据恢复的时候,数据不一致。

  2. binlog 提交成功了,这时候数据库挂掉导致 redo log 没有成功写入。数据库重启之后,无法恢复崩溃之前提交的那个事务,这部分数据更改在主库缺失。但是 binlog 已经成功写入了,从库反而有了该事务的改动,导致数据不一致。



综上我们知道,redo log 和 binlog 必须同时成功或同时失败,才能保证数据一致性。



两阶段提交是如何保证 redo log 和 binlog 同时成功或同时失败的?





假设已经有了两阶段提交,分析一下以下两种情况:



  1. 假设在上图的时刻A,redo log 处于 prepare 之后,写 binlog 之前,数据库挂掉了。由于此时 binlog 还没有写,redo log 也还没有提交,所以崩溃恢复后,这个事务会回滚。这时候 binlog 还没写,所以也不会传到备库。

  2. 假设在上图的时刻B,写 binlog 之后,redo log 还没有 commit 前发生 crash。那么崩溃恢复时,MySQL 会做以下判断:

1. 如果 redo log 里面的事务是完整的,也就是已经有了 commit 标识,则直接提交;

2. 如果 redo log 里面的事务只有完整的 prepare,则判断对应的事务 binlog 是否存在并完整:

a. 如果是,则提交事务;

b. 否则,回滚事务。



那么 MySQL 是怎么知道 binlog 是否完整的?



一个事务的 binlog 是有完整的格式的:



  • statement 格式的 binlog,最后会有 COMMIT;

  • row 格式的 binlog,最后会有一个 XID event。



5. change buffer



什么是 change buffer ?



当需要更新一个数据时,如果数据页在内存里就直接更新了,如果数据页不在内存里,InnoDB 会将这些更新操作缓存在 change buffer 中,这样就不需要读磁盘了。在下次查询需要访问到这个数据页的时候,将数据页读入内存,然后执行 change buffer 中与这个页有关的操作。



如果能够将更新操作先记录在 change buffer, 减少读磁盘,更新操作变快。而且数据读入内存是需要占用 buffer pool 的,所以这种方式还能够避免占用内存,提高内存利用率。



change buffer 是可以持久化的数据,change buffer 在内存中有拷贝,也会被写入到磁盘中。



将 change buffer 中的操作应用到原数据页,得到最新结果的过程称为 merge。以下情况会触发 merge:



  1. 访问数据页

  2. 系统有后台线程定期 merge

  3. 数据库正常关闭也会触发 merge



为什么普通索引比唯一索引效率高?



  1. 查询时:

1. 普通索引查出数据页,数据页读入内存,判断是否有相等的数据,返回数据。

2. 唯一索引查出数据页,数据页读入内存,直接返回数据。

3. 虽然普通索引多了一步判断,但是数据是以页为单位读入内存的,判断大概率是内存操作,消耗很小,可以忽略。

  1. 更新时:

1. 普通索引直接更新内存或者缓存到 change buffer 中,结束。

2. 唯一索引更新时需要判断是否有数据冲突,所以无法利用 change buffer,当数据页不在内存时,必须读磁盘写入内存再做判断,效率低于普通索引。



什么情况下不适合使用 change buffer?



如果某个业务更新后马上做查询,即使我们把更新先记录在 change buffer,读取操作也会马上把数据读入内存,而且立即触发 merge 操作。这种情况下,随机访问磁盘的次数没有减少,反而增加了 change buffer 的维护代价。所以对于这种业务,change buffer 反而起到了反作用。



6. change buffer 和 redo log



插入时





  1. 插入的数据页刚好在内存中,直接更新内存中的数据页(上图1)。

  2. 数据页不在内存中,在 change buffer 里记录下对该数据页的改动(上图2)。

  3. 将上述两个动作记入 redo log 中(上图3,4)。



我们可以看到,执行这条语句的成本很低,写了两处内存(内存和change buffer),写了一处磁盘(redo log,两次操作合在一起写磁盘),而且还是顺序写(直接写日志文件)。



同时,图中两个虚线箭头,是后台操作(异步操作,空闲时间就刷的那种),不影响该语句的响应时间。



查询时





  1. 数据在内存时,直接读取。

  2. 数据不在内存时,从磁盘读入内存,然后应用 change buffer 里的操作日志,在内存生成一个最新的数据。



比较



从上面两个案例我们可以看出:



  1. redo log 主要节省的是随机写磁盘的 IO 消耗(把更新时的随机写磁盘转成顺序写)。

  2. change buffer 主要节省的是随机读磁盘的 IO 消耗(减少更新时读磁盘的次数)。



7. binlog 和 redo log 的持久化



binlog 的写入机制



binlog 的写入逻辑:事务执行过程中,先把日志写到 binlog cache,事务提交的时候,再把 binlog cache 写到 binlog 文件中。



一个事务的 binlog 是不能被拆开写的,因此不论这个事务多大,也要确保一次性写入。



系统给 binlog cache 分配了一片内存,每个线程一个,参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小,就要暂存的磁盘中。



事务提交时,执行器把 binlog cache 里的完整事务写到 binlog file 和 磁盘中,并清空 binlog cache。状态如下图所示:





  1. 图中的 write,指的是日志写入到文件系统的 page cache,并没有把数据持久化到磁盘,速度比较快。

  2. 图中的 fsync,指的是日志最终持久化到磁盘,速度慢。

  3. write 和 fsync 的时机,由参数 sync_binlog 控制:

1. sync_binlog=0 时,表示每次提交事务都只 write,不 fsync;

2. sync_binlog=1 时,表示每次提交事务都会执行 fsync;

3. sync_binlog=N(N>1) 时,表示每次提交事务都 write,但累积 N 个事务后才 fsync。

  1. 将 sync_binlog 设置为 N,对应的风险是:如果主机发生异常重启,会丢失最近 N 个事务的 binlog 日志(没有持久化到磁盘,主机挂了就丢失了)。



redo log 的写入机制





  1. 事务在执行过程中,生成的 redo log 会先写到 redo log buffer 中。

  2. 写入到 page cache 的速度也很快,写入到磁盘的速度慢。

  3. innodb_flush_log_at_trx_commit 参数用来控制 redo log 的写入策略:

1. 设为 0 时,表示每次事务提交时都只是把 redo log 留在 redo log buffer 中;

2. 设为 1 时,表示每次事务提交时都将 redo log 直接持久化到磁盘;

3. 设为 2 时,表示每次事务提交时都只是把 redo log 写到 page cache。

  1. InnoDB 有一个后台线程,每隔 1 秒,就会把 redo log buffer 中的日志,调用 write 写到文件系统的 page cache,然后调用 fsync 持久化到磁盘。

  2. 事务执行过程中写入 redo log buffer 的记录,也会随着其他事务的提交或者定时写入过程持久化到磁盘中。也就是说有些还未提交的事务的 redo log 也会被持久化。

  3. redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候,也会触发持久化操作。



分组提交



为了降低写磁盘的次数,redo log 把 write 和 fsync 拆成两个步骤,当有并发时,事务A写完 page cahce,事务B也写完了 page cache,事务A触发 fsync 的时候,会把两个事务的 redo log 并在一组,一起写磁盘。



并且为了能让更多的事务加入同一个组,InnoDB 让 redo log 和 binlog 的 write 和 fsync 交替执行,分组提交的优化,redo log 和 binlog 都有。





WAL 机制是减少磁盘写,但每次提交事务都要写 redo log 和 binlog,写磁盘的次数好像没有减少?



  1. redo log 和 binlog 都是顺序写,磁盘的顺序写比随机写速度快;(日志写磁盘都是顺序写的,事务提交后直接把数据写磁盘就是随机访问);

  2. 组提交机制可以大幅降低磁盘的 IOPS 消耗。



MySQL 是如何保证 crash-safe 的。



redo log 是如何保证 crash-safe 的。

写到 redo log buffer 不能保证 crash-safe,写到 fs cache 也不能保证 crash-safe,只有 redo log 写入磁盘之后,数据库异常重启,从磁盘中的 redo log 拿出未执行的日志进行恢复,才算是 crash-safe。

这也说明了多个事务提交之后才写磁盘,还是会有事务丢失。只有每个事务提交后都进行写磁盘才能保证数据完全不丢失。



binlog 为什么无法保证 crash-safe?



  1. 如果 binlog 写入成功了,数据还没写入磁盘,数据库异常崩溃,重启后主库没有这部分数据,而通过 binlog 同步的从库却有了这部分配置,导致主从数据不一致。

  2. 如果 数据写入磁盘,binlog 写入失败了,数据库异常崩溃,重启后主库有这部分数据,而通过 binlog 同步的从库没有这部分数据,导致主从数据不一致。



能否只使用 binlog 或 redo log 单个日志保证 crash-safe?



我的理解是:并不是单个 log 无法保证 crash-safe,而是 binlog 本身无法保证 crash-safe,因为 InnoDB 无法重新设计 binlog,所以引入了 redo log。并且花了很大力气来保证 redo log 和 binlog 的一致性。

如果重新设计 MySQL,可以使用 redo log 实现 binlog 的功能,也可以把 binlog 设计成 crash-safe 的,这样就只需要一种 log 了。



参考



02 | 日志系统:一条SQL更新语句是如何执行的?-极客时间



09 | 普通索引和唯一索引,应该怎么选择?-极客时间



12 | 为什么我的MySQL会"抖"一下?-极客时间



15 | 答疑文章(一):日志和索引相关问题-极客时间



23 | MySQL是怎么保证数据不丢的?-极客时间



本文首发于我的个人博客 https://chaohang.top

作者 张小超

公众号【超超不会飞】

转载请注明出处



欢迎关注我的微信公众号 【超超不会飞】,获取第一时间的更新。



发布于: 2020 年 06 月 01 日 阅读数: 50
用户头像

超超不会飞

关注

【超超不会飞】公众号作者 2017.11.30 加入

Java & Node.js

评论

发布
暂无评论
MySQL的各种日志