Mysql实战之日志系统:一条SQL更新语句是如何执行的
2023-02-24 05:13:58   来源:腾讯云

1.前言

上一篇咱们了解了MySQL 的执行过程,其中设计连接器、分析器、优化器、执行器和存储引擎,接下来我将给大家讲解一下在MySQL中一条更新语句是如何执行。我相信大家可能听公司的DBA提起过,可以将数据恢复到半个月内任意时间的状态,是不是感觉很高大上,很厉害呢,下面我就将这个谜底一步一步解开


(资料图)

2. 首次分析更新语句执行

例如我们存在如下一下update 语句

update order set status = 2 where id = 10;

根据上一章,我们可以知道它的执行流程是如下图

在这里插入图片描述

客服端先通过连接器连接数据库,然后通过分析器发现是更新的SQL语句,优化器针对SQL语句进行优化,使用id索引,最后执行器执行SQL语句

到这里大家会说:这不是和查询语句一样吗,都是这些流程,其实不然,更新语句还设计两个比较重要的模块,那就是我们今天主要介绍的内容:redo log(重做日志)和binlog(归档日志)

3.先看redo log

再说将redo log之前,先给大家讲一个关于孔乙己的故事,酒店掌柜有一个粉板,专门用来记录客人的赊账记录。如果赊账的人不多,那么他可以把顾客名和账目写到板上。但是如果赊账的人多了,粉板总会有记不下的时候,这个时候掌柜一定还有一个专门记录赊账的账本。

如果有人要赊账或者还账的话,掌柜一般有两种做法:

直接把账本拿出来,把此次赊的账加上或者扣除掉将此次赊的账记录到粉板上,等打烊了在把账本拿出来,把今天的粉板的账记录到账本

如果是在生意比较火的时候,掌柜肯定会选择第二种方式,因为第一种操作太麻烦了,每次都要从账本里面找到对应人的账,进行计算,然后更新账本。相比之下,先把账记录到粉板上,等打烊在一块统计到账本上,更省时。

MySQL同样也存在类似的问题,就是如果每次更新操作都需要写入磁盘,然后磁盘也要找到对应的记录,然后更新,整个过程IO成本和查找成本都很高。为了解决这个问题,MySQL设计者就采取了类似掌柜粉板的思路来提升更新的效率

当有更新操作执行的时候,InnoDB引擎就会先把记录写到redo log里面,并更新内存,这样更新操作就算结束了。InnoDB引擎会在适当的时候,将这个操作记录更新到磁盘,这个写磁盘的操作一般都是在MySQL比较空闲的时候执行。

这里存在一个问题,就是redo log不可能是无限大的,总有因为频繁操作,将redo log打满的情况,InnoDB的设计人员也考虑到了这一点,将redo log设定成了固定大小,默认是一组四个文件,一个文件1G,也就是说redo log总大小是4GB,下图就是redo log的设计图

在这里插入图片描述

wirte pos是记录当前的位置,一边写一遍后移,写到第三号文件的末尾就回到0号文件开头。checkpoint 是当前要察除的位置,也是往后推移并且循环的,察除记录前要把记录更新到数据文件。

wirte pos和checkpoint 之间还空着的部分,可以用来记录新的操作。如果wirte pos追上checkpoint,标识redo log已经满了,此时不能在执行更新操作,需要将内存的数据同步到磁盘中了。

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

4.再看binlog

上一篇文章我们讲过数据库架构分为两层,一个是server层,一个是存储引擎层,而binlog就属于server层的日志,而redo log是InnoDB独有的日志

说道这里,大家肯定会有一个疑惑,为啥会有两个日志系统呢?

因为一开始MySQL并没有InnoDB引擎,MyISAM是MySQL自带的引擎,但是MyISAM没有日志系统的功能,只存在server层的binlog 归档日志,InnoDB是后来集成到mysql里面去的

下面看一下两种日志的不同点

redo log是InnoDB引擎独有的;binlog是mysql共用的redo log是物理日志,记录的是“在某个数据页上做了什么修改”;binlog是逻辑日志,记录的是这个语句的原始逻辑,比如给id=2这一行的c字段加1.redo log日志是循环写的,空间固定会用完;binlog日志是追加写的,不会覆盖以前的日志

5.再次分析更新语句执行

上面我们对两种日志做了概念性讲解,下面看一下执行器和InnoDB引擎在执行update语句是的内部流程

在这里插入图片描述
执行器先找引擎取到id=2的这一行,因为id是主键,直接可以通过主键索引查到这一行,如果id=2这一行所在的数据页本来就在内存中,执行器直接放回结果,如果不在,在需要将磁盘的数据,写到内存在返回结果执行器会将id=2的这一行的c字段进行加1操作,然后会更新当前行InnoDB引擎将当前行更新到内存后,redo log日志会记录当前更新操作,此时redo log日志处于prepare的状态。然后告知执行器执行完成,可以提交事务了binlog日志会记录当前update语句,并且把binlog写入磁盘执行器调用引擎提交事务,引擎把刚刚写入的redo log改成提交状态(commit),更新完成

大家可以看到我们对redo log日志做了两次提交,也就是我们平常说的两阶段提交

6.两阶段提交

问:为啥需要进行两阶段提交

答:两阶段提交是为了让binlog和redo log可以保持数据一致

问:如果不使用两阶段提交,会产生什么样的问题?

答:如果不存在两阶段提交,那边就会有两种情况。

先写redo log日志,后写binlog日志。如果我们写完了redo log日志,但是binlog日志还没有写,发生了宕机,我们在重启MySQL的时候用binlog恢复数据的话,id=2这一行的数据没有完成加1操作,和之前库的数据不一致了先写binlog日志,后写redo log日志。如果我们写完了binlog日志,但是redo log日志还没有写,发生了宕机,此时事务没有生效,我们通过binlog日志恢复数据,发现id=2这一行已经完成加1操作了,和之前库的数据不一致了

7.小结

今天主要讲了MySQL最重要的两个日志,一个是server层的binlog归档日志,一个是InnoDB引擎的redo log重做日志

redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候,表示每次事务的 redo log 都直接持久化到磁盘。这个参数我建议你设置成 1,这样可以保证 MySQL 异常重启之后数据不丢失。

sync_binlog 这个参数设置成 1 的时候,表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1,这样可以保证 MySQL 异常重启之后 binlog 不丢失。

我还跟你介绍了与 MySQL 日志系统密切相关的“两阶段提交”。两阶段提交是跨系统维持数据逻辑一致性时常用的一个方案,即使你不做数据库内核开发,日常开发中也有可能会用到。

标签: 云数据库 Server

上一篇:全球新消息丨Excel中如何提取两组数据中完全相同的部分_如何提取另一个excel单元格中的相同数据
下一篇:最后一页