MySql之日志

逃离我推掉我的手 2023-10-14 13:05 6阅读 0赞

Buffer Pool  
Buffer Pool （缓冲池）是 InnoDB 存储引擎中非常重要的内存结构，顾名思义，缓冲池其实就是类似 Redis 一样的作用，起到一个缓存的作用，因为我们都知道 MySQL 的数据最终是存储在磁盘中的，如果没有这个 Buffer Pool 那么我们每次的数据库请求都会磁盘中查找，这样必然会存在 IO 操作，这肯定是无法接受的。但是有了 Buffer Pool 就是我们第一次在查询的时候会将查询的结果存到 Buffer Pool 中，这样后面再有请求的时候就会先从缓冲池中去查询，如果没有再去磁盘中查找，然后在放到 Buffer Pool 中，如下图（每次加载16kb大小的数据页到Buffer Pool中的16kb大小的缓存页中）

![在这里插入图片描述][2c1fcacd56454db798b158c7dfc21aea.png]  
按照上面的那幅图，这条 SQL 语句的执行步骤大致是这样子的

innodb 存储引擎会在缓冲池中查找 id=1 的这条数据是否存在  
发现不存在，那么就会去磁盘中加载，并将其存放在缓冲池中  
该条记录会被加上一个独占锁（总不能你在修改的时候别人也在修改吧，这个机制本篇文章不重点介绍，以后会专门写文章来详细讲解）

undo 日志文件：记录数据被修改前的样子（回滚日志）  
mvcc  
异常时的回滚，保证事务的原子性  
归属于InnoDB存储引擎  
逻辑日志  
在操作任何数据之前，首先将数据备份到一个地方，这个存储数据备份的地方称之为undo log。然后进行数据的修改，如果出现了错误或者用户执行了rollback语句，系统可以利用undo log中的备份将数据恢复到事务开始之前的状态。

注意：undo log是逻辑日志（记录的是逆向回滚的逻辑，而不是实际的值），可以理解为：

当delete一条记录的时候，undo log中会记录一条对应的insert记录  
当insert一条记录的时候，undo log中会记录一条对应的delete记录  
当update一条记录的时候，undo log中会记录一条对应的update记录  
看图说话  
![在这里插入图片描述][19f4980cdc414c31bad8c88bb0cd3a72.png]  
到这一步，我们的执行的 SQL 语句已经被加载到 Buffer Pool 中了，然后开始更新这条语句，更新的操作实际是在Buffer Pool中执行的。

redo 日志文件：记录数据被修改后的样子（前滚日志）  
保证事务的持久性  
归属于InnoDB存储引擎  
物理日志  
和undo log相反，redo log日志记录的是新数据的备份。在事务提交前，数据只需要在redo log中持久化即可，系统可以根据redo log的内容，将所有的数据恢复到最新的状态。

![在这里插入图片描述][892d51fd20f44d69a2258aa81cadb930.png]  
截至目前，我们应该都熟悉了 MySQL 的执行器调用存储引擎是怎么将一条 SQL 加载到缓冲池和记录哪些日志的，流程如下：

准备更新一条 SQL 语句  
MySQL（innodb）会先去缓冲池（Buffer Pool）中去查找这条数据，没找到就会去磁盘中查找，如果查找到就会将这条数据加载到缓冲池（Buffer Pool）中  
在加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 undo 日志文件中  
innodb 会在 Buffer Pool 中执行更新操作  
更新后的数据会记录在 redo log buffer 中  
上面说的步骤都是在正常情况下的操作，但是程序的设计和优化并不仅是为了这些正常情况而去做的，也是为了那些临界区和极端情况下出现的问题去优化设计的

这个时候如果服务器宕机了，那么缓存中的数据还是丢失了。真烦，竟然数据总是丢失，那能不能不要放在内存中，直接保存到磁盘呢？很显然不行，因为在上面也已经介绍了，在内存中的操作目的是为了提高效率。

此时，如果 MySQL 真的宕机了，那么没关系的，因为 MySQL 会认为本次事务是失败的，所以数据依旧是更新前的样子，并不会有任何的影响。

好了，语句也更新好了那么需要将更新的值提交啊，也就是需要提交本次的事务了，因为只要事务成功提交了，才会将最后的变更保存到数据库，在提交事务前仍然会具有相关的其他操作。

将 redo log buffer 中的数据持久化到磁盘中，就是将 redo log buffer 中的数据写入到 redo log 日志文件中，一般情况下，redo log buffer 数据写入磁盘的策略是立即刷入磁盘（具体策略情况在下面小总结出会详细介绍）,上图

![在这里插入图片描述][a880a55142d845fcb5f8610f9644c90d.png]  
如果 redo log Buffer 刷入磁盘后，数据库服务器宕机了，那我们更新的数据怎么办？此时数据是在内存中，数据岂不是丢失了？不，这次数据就不会丢失了，因为 redo log buffer 中的数据已经被写入到磁盘了，已经被持久化了，就算数据库宕机了，在下次重启的时候 MySQL 也会将 redo 日志文件内容恢复到 Buffer Pool 中（这边我的理解是和 Redis 的持久化机制是差不多的，在 Redis 启动的时候会检查 rdb 或者是 aof 或者是两者都检查，根据持久化的文件来将数据恢复到内存中）

到此为止，从执行器开始调用存储引擎接口做了哪些事情呢？

1.准备更新一条 SQL 语句

2.MySQL（innodb）会先去缓冲池（BufferPool）中去查找这条数据，没找到就会去磁盘中查找，如果查找到就会将这条数据加载 到缓冲池（BufferPool）中

3.在加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 undo 日志文件中

4.innodb 会在 Buffer Pool 中执行更新操作

5.更新后的数据会记录在 redo log buffer 中

6.MySQL 提交事务的时候，会将 redo log buffer 中的数据写入到 redo log日志文件中 。刷磁盘的策略可以通过 innodb\_flush\_log\_at\_trx\_commit 参数来设置

0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。

1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。

2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。

7.myslq 重启的时候会将 redo log恢复到缓冲池中

截止到目前位置，MySQL 的执行器调用存储引擎的接口去执行【执行计划】提供的 SQL 的时候 InnoDB 做了哪些事情也就基本差不多了，但是这还没完。下面还需要介绍下 MySQL 级别的日志文件bin log

bin log 日志文件：记录整个操作过程  
归属于MySql Server  
逻辑日志  
上面介绍到的redo log是 InnoDB 存储引擎特有的日志文件，而bin log属于是 MySQL 级别的日志。 redo log记录的东西是偏向于物理性质的，如：“对什么数据，做了什么修改”。bin log是偏向于逻辑性质的，类似于：“对 students 表中的 id 为 1 的记录租了更新操作” 两者的主要特点总结如下:

![在这里插入图片描述][e3667288a10b4c919d56d44853ee1ba0.png]

bin log文件是如何刷入磁盘的?  
bin log的刷盘是有相关的策略的，策略可以通过sync\_bin log来修改，默认为0，表示先写入 os cache，也就是说在提交事务的时候，数据不会直接到磁盘中，这样如果宕机bin log数据仍然会丢失。所以建议将sync\_bin log设置为 1 表示直接将数据写入到磁盘文件中。

刷入bin log有以下几种模式

1、 STATMENT

基于 SQL 语句的复制(statement-based replication, SBR)，每一条会修改数据的 SQL 语句会记录到 bin log 中

【优点】：不需要记录每一行的变化，减少了 bin log 日志量，节约了 IO , 从而提高了性能

【缺点】：在某些情况下会导致主从数据不一致，比如执行sysdate()、slepp()等

2、ROW

基于行的复制(row-based replication, RBR)，不记录每条SQL语句的上下文信息，仅需记录哪条数据被修改了

【优点】：不会出现某些特定情况下的存储过程、或 function、或 trigger 的调用和触发无法被正确复制的问题

【缺点】：会产生大量的日志，尤其是 alter table 的时候会让日志暴涨

3、MIXED

基于 STATMENT 和 ROW 两种模式的混合复制( mixed-based replication, MBR )，一般的复制使用 STATEMENT 模式保存 bin log ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 bin log

那既然bin log也是日志文件，那它是在什么记录数据的呢？

其实 MySQL 在提交事务的时候，不仅仅会将 redo log buffer 中的数据写入到redo log 文件中，同时也会将本次修改的数据记录到 bin log文件中，同时会将本次修改的bin log文件名和修改的内容在bin log中的位置记录到redo log中，最后还会在redo log最后写入 commit 标记，这样就表示本次事务被成功的提交了。

![在这里插入图片描述][b4c0e5e03f10433b9d5a234aa7499ae5.png]  
如果在数据被写入到bin log文件的时候，刚写完，数据库宕机了，数据会丢失吗？

首先可以确定的是，只要redo log最后没有 commit 标记，说明本次的事务一定是失败的。但是数据是没有丢失了，因为已经被记录到redo log的磁盘文件中了。在 MySQL 重启的时候，就会将 redo log 中的数据恢复（加载）到Buffer Pool中。

好了，到目前为止，一个更新操作我们基本介绍得差不多，但是你有没有感觉少了哪件事情还没有做？是不是你也发现这个时候被更新记录仅仅是在内存中执行的，哪怕是宕机又恢复了也仅仅是将更新后的记录加载到Buffer Pool中，这个时候 MySQL 数据库中的这条记录依旧是旧值，也就是说内存中的数据在我们看来依旧是脏数据，那这个时候怎么办呢？

其实 MySQL 会有一个后台线程，它会在某个时机将我们Buffer Pool中的脏数据刷到 MySQL 数据库中，这样就将内存和数据库的数据保持统一了。  
![在这里插入图片描述][0cd3b2fee34842ce8c654f980bd06546.png]  
总结

到此，关于Buffer Pool、Redo Log Buffer 和undo log、redo log、bin log 概念以及关系就基本差不多了。

我们再回顾下

1.Buffer Pool 是 MySQL 的一个非常重要的组件，因为针对数据库的增删改操作都是在 Buffer Pool 中完成的  
2.Undo log 记录的是数据操作前的样子  
3.redo log 记录的是数据被操作后的样子（redo log 是 Innodb 存储引擎特有）  
4.bin log 记录的是整个操作记录（这个对于主从复制具有非常重要的意义）  
从准备更新一条数据到事务的提交的流程描述

1.首先执行器根据 MySQL 的执行计划来查询数据，先是从缓存池中查询数据，如果没有就会去数据库中查询，如果查询到了就将其放到缓存池中  
2.在数据被缓存到缓存池的同时，会写入 undo log 日志文件  
3.更新的动作是在 Buffer Pool 中完成的，同时会将更新后的数据添加到 redo log buffer 中  
4.完成以后就可以提交事务，在提交的同时会做以下三件事：  
1、将redo log buffer中的数据刷入到redo log文件中，prepare阶段（二阶段提交）

2、将本次操作记录写入到 bin log文件中

3、将bin log文件名字和更新内容在 bin log 中的位置记录到redo log中，同时在 redo log 最后添加 commit 标记，commit阶段（二阶段提交）

至此表示整个更新事务已经完成

relay log 日志文件：从库用来保存主库发过来bin log日志内容的文件  
从服务器I/O线程将主服务器的bin log日志读取过来记录到从服务器本地文件relay log，然后从服务器SQL线程会读取relay log日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。

![在这里插入图片描述][5fd22608383d4da380606860b6c26ea1.png]  
主库db的更新事件(update、insert、delete)被写到binlog  
从库发起连接，连接到主库  
此时主库创建一个binlog dump thread，把binlog的内容发送到从库  
从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log  
还会创建一个SQL线程，从relay log里面读取内容，从Exec\_Master\_Log\_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db

[2c1fcacd56454db798b158c7dfc21aea.png]: https://img-blog.csdnimg.cn/2c1fcacd56454db798b158c7dfc21aea.png
[19f4980cdc414c31bad8c88bb0cd3a72.png]: https://img-blog.csdnimg.cn/19f4980cdc414c31bad8c88bb0cd3a72.png
[892d51fd20f44d69a2258aa81cadb930.png]: https://img-blog.csdnimg.cn/892d51fd20f44d69a2258aa81cadb930.png
[a880a55142d845fcb5f8610f9644c90d.png]: https://img-blog.csdnimg.cn/a880a55142d845fcb5f8610f9644c90d.png
[e3667288a10b4c919d56d44853ee1ba0.png]: https://img-blog.csdnimg.cn/e3667288a10b4c919d56d44853ee1ba0.png
[b4c0e5e03f10433b9d5a234aa7499ae5.png]: https://img-blog.csdnimg.cn/b4c0e5e03f10433b9d5a234aa7499ae5.png
[0cd3b2fee34842ce8c654f980bd06546.png]: https://img-blog.csdnimg.cn/0cd3b2fee34842ce8c654f980bd06546.png
[5fd22608383d4da380606860b6c26ea1.png]: https://img-blog.csdnimg.cn/5fd22608383d4da380606860b6c26ea1.png