MySQL进阶 InnoDB日志

前言

我们都经常写sql语句的增删改查 

今天我们就来详细介绍一条sql是怎么去执行的

主要介绍最有代表性的update语句

事务的acid四大特性

原子性  主要是保证做的操作是原子的 依赖于undo日志来失败回滚 

隔离性  事务并发的时候 其他事务不能干扰 依赖于锁和mvcc来实现的

一致性   主要是依赖于其他的三个特性实现

持久性   主要是依赖于redo日志和bin日志实现的

知道了这些你肯定好奇,这些日志底层又是怎么操作的吧

下面我们一一展开讲明白 

对于mvcc相关的知识可以看我的博客

MySQL进阶 分页优化 表连接原则 mvcc原理-CSDN博客

一条update语句是怎么执行的

我们先介绍总体流程再根据流程来讲解对应的日志是如何操作的吧

首先我们是经过

客户端 

连接器(校验用户的参数) 

查询缓存(8.0删除)

语法分析器(分析对应的关键词形成语法树)   

语法树大概是这样的

SQL优化器(分析cost决定是都走索引)

执行器(分析权限是否满足)

InnoDB执行引擎负责执行

查询缓存

至于这里的查询缓存为什么删除是因为这里的查询缓存保存的是sql语句

一段时间内查询两条相同的语句是命中率极低的

而且一次update就会直接去清除对应的缓存 

但是也不是一无是处的

我们在面对不怎么更新的比如字典表等等可以使用这样的查询缓存

更新sql举例

假设我这里有一个name字段

我想给他从zhangsan更新成zhangsan666

我们这里就会遵循上面的流程

首先通过连接器  管理连接和校验缓存

然后就是词法分析器 进行对应的词法分析形成语法树

优化器会计算查询消耗等等查看是否使用索引

执行器负责执行

这里以上都是server层做的事情

执行器会将数据加载到一个叫做Buffered Pool的缓存池中

然后会写对应的undo日志

因为假设这里的事务执行失败了就得进行对应的回滚操作

比如执行到一半没有commit就挂了,会回滚 

然后在缓存池中进行更新更新字段为zhangsan666

注:对应的undo日志是执行相反的语句,insert对应的就是delete

然后更新到缓存池之后就会再将对应的sql写入redo日志中

这里的日志是保证持久化的,为了提升效率而产生的

是在mysql启动一开始就开辟了一块很大的空间,支持顺序读写的

将缓存中的数据转移到redo buffer pool 

这块其实还有一个选项咱们后面再说

一般默认情况从redo的缓存转到对应的redo日志中

这里记录的就是在磁盘的什么位置修改了什么数据是物理上的修改

然后再向binLog写对应的数据

binLog主要是一个逻辑上的持久化数据

在写完之后给对应的redoLog打上一个commit标记

这个时候就可以做提交了

其实这里的redo日志和binLog日志是一个事务的机制

第一阶段相当于自己写自己的文件

第二阶段是两个日志都写完了,提交成功

有一个没写成功就提交失败了

这里空闲的时候会有线程将对应的真实数据再刷写到磁盘中

为什么要有redo日志

这是因为redo日志是支持顺序读写的

相对来说性能更高,比如一个业务需要同时修改十个表

这里十个表一起更新对应机械硬盘磁头定位的效率就是十分低下的

而使用redoLog准备阶段分配好的大块空间顺序读写这样效率就很高了

而且如果对应的bufferedlog 没有写入磁盘 

但是数据丢失了,比如重启了

我们可以使用redoLog来帮助我们恢复ibd文件

写入策略

我们虽然有图,但是还是得解释一下

这里我们能看到有一个叫

innodb_flush_log_at_trx_commit的参数

这个参数主要有三个取值0   1   2

取值为0的话就是直接从buffer缓存中直接就提交,根本没有后面的事儿了

取值为1就是先写入page_cache,是基于操作系统的一个缓存

下面简单解释一下

我们都知道cpu 内存之间引入了一个高速缓存的结构来解决其中的速度差异问题

这里对于磁盘和内存速度差异我们就依赖这里的page_cache来解决,几乎是一样的道理

我们接着说,写入完了就结束 我们可以理解为这里的刷写磁盘内容交给了操作系统去做

最后的一个参数2就是多了一步从os缓存中读写到磁盘的过程

为啥需要binLog

他们两个文件主要功能都是做一些数据恢复的功能

假设已经commit且空闲线程已经更新到数据库

这个时候一个程序员给你删库跑路了,如果你有保存对应的binLog

就可以实现对应的数据恢复

这里的主要有三种binLog保存方式

1.statment

2.row

3.mixed

第一种是保存的sql语句,假设这里涉及到主从复制的情况

对应的时间日期函数在从数据库上的展示就和主数据库不同

第二种就解决了这种问题,它是行数据的拷贝

第三种是安全性最高的

面对时间函数等使用第二个方式

面对其他的使用第一个方式 

主要保存的是二进制文件

我们可以通过以下的参数来查看对应的真实文件

日志怎么使用于数据恢复

binlog常用参数

上面我们也说了binLog可以解决数据恢复的问题

这里我们简单说一下思路

由于其是二进制文件

我们直接打开没啥用

这里我么使用了mysql的一个工具来处理

注:使用前注意配置环境变量 

这里的复制方式主要有两种

依赖偏移量进行复制,依赖时间进行复制

这里的写入磁盘机制与redoLog相似,咱们不做过多介绍

数据的恢复首先我们得找到对应的binlog文件

使用mysqlbinlog指令  后面对应上起始偏移量结束偏移量即可  记得对应上binlog文件

下面我们做一个简单的示范

在示范之前我们说一个删库跑路的备份方案

我们可以使用binlog记录短时间的数据

然后每天整一个定时任务,在业务量小的时候进行备份数据库

假设是夜里两点

然后最好是搞时间长一点的binlog保存比如三四天

不然万一哪天定时任务失效的时候无法及时发现

然后我们就可以使用脚本+binlog的方式恢复数据库了

ok做一个简单的示范恢复数据

首先我们配置对应的ini文件来书写binlog

实际上在有binlog之前我就写了四条数据

开启之后只加上了一条aaa

现在我们删掉他

使用cmd开查看binlog日志

这里就是对应的偏移量以及对应的sql

我们可以让mysql按照对应的文件以及偏移量进行再次操作一下

这里我们使用偏移量进行sql的重新执行

这里就能恢复对应的数据啦