原来一条select语句在MySQL是这样执行的《死磕MySQL系列 一》

2021年11月26日 阅读数:2
这篇文章主要向大家介绍原来一条select语句在MySQL是这样执行的《死磕MySQL系列 一》,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

系列文章

前言

咔咔闲谈

咔咔闲谈数据库

上期根据一条查询语句查询流程分析MySQL的总体架构。一样,本期也使用一条查询SQL语句来作引子。能够确定的是,查询语句执行的流程更新语句一样也会执行。缓存

所以本期的着重点就不在MySQL架构图上,文章标题也给出了你们重点,就是要了解redo log、binlog。安全

1、redo log

第一步,建立一个表 user,主键是 id,下面是建立语句。服务器

CREATE TABLE `user` (
 `id` int(11NOT NULL AUTO_INCREMENT,
 `name` varchar(255NOT NULL,
 `age` tinyint(4NOT NULL,
 `time` int(11NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

插入一条数据架构

insert into user (`name`,`age`,`time`values ("咔咔","25",unix_timestamp(now()))

若要将插入的这条数据的age改成26,则须要执行语句并发

update user set age = 26 where id = 1;

第一期文章中提到一条查询语句的执行流程,该流程与更新语句相同。这里将那幅图拿过来在熟悉一下。性能

每一个模块的功能能够回到第一期文章去查看。学习

在MySQL8.0中redo log、binlog日志文件都位于/var/lib/mysql此目录下,如图

文件名为ib_logfile的是重作日志,undo开头的就是回滚日志,对于回滚日志后期进行详细的讨论。

redo log(重作日志)是实现事务持久性必备要素,当一个事务提交后,并不是直接修改数据库的数据,而是首先保证在 redo log中记录相关的操做。

Innodb存储引擎中的redo log大小是固的,上图显示配置了一组两个文件,每一个文件大小默认为48M,使用innodb_log_file_size参数来控制单个文件大小,在MySQL5.6.8以及以后版本都默认为48M。

而后redo log能够记录48M的操做,redo log是一个闭环的循环写。所设定的文件个数和文件大小再也不增长。

write pos将记录当前位置,同时向后移动,在ib-log-file-3文件末尾后,而后返回ib-logfilg-0文件开始写。

check point记录的是当前擦除的位置,要使文件循环写入,必须一边擦除。清楚数据的前提是要将记录更新到数据文件。

上面的绿色部分就是可写的部分,假设若是 writepos追上了 checkpoint,那该怎么办?

你必须理解write pos的推动是由于在执行更新操做,这样就不能再执行更新操做,直到记录更新到数据文件,而后check point进行擦除后才能够继续执行更新操做。

对于innodb_log_file_size的设置也是有一些计算规则的,下面将为你介绍。

若innodb_log_file_size设置过小,将致使redo log文件频繁切换,频繁的触发数据库的检查点(check point),致使记录更新到数据文件的次数增长,从而影响IO性能。

一样,若是有一个大的事务,而且全部 redo log日志都已写满,可是尚未完成,将致使日志没法切换,从而致使 MySQL直接堵死。

innodb_log_file_size设置太大,虽然极大地提升了 IO性能,可是在 MySQL重启或宕机时,恢复时间会由于 redo log文件过大而延长。而这种恢复时间一般是没法控制的。

在设置合理的redo log大小和数量后,Innodb可以保证,即便数据库发生异常重启,之前提交的记录也不会丢失,这一点也称为crash-safe。

在这里,对crash-safe的理解先不说起它是什么,后面的文章会让你明白。

2、如何根据项目状况设置innodb_log_file_size

对于参数innodb_log_files_in_group设置3~4个就够用了,不用进行优化。

着重讨论innodb_log_file_size的大小设置或优化设置。

在 MySQL8.0以前,一般是计算在一段时间内生成的事务日志(redo log)大小,而 MySQL日志文件最小应承载一小时的业务日志量。

此处的一段时间必须视本身的业务状况而定,外界有用1分钟的日志量也有1小时的日志量来计算。

首先看一下 MySQL客户端的一个命令 pager,在 MySQL平常操做中,经过设置 pager的显示方式,能够大大提升工做效率。

目前,要查看 sequence在一分钟以内的值,您就能够执行 pager grep sequence,它对mysql> show engine innodb status\ G select sleep (60); show engine innodbstatus\ G;返回的结果。

禁止 pager设置执行 nopager,若是不执行该命令,则只有等到下一次从新启动该命令才会失效。

此处咔咔是在虚拟机上作的操做,能够看到一分钟内是没有任何操做,因此值先后相同,你能够在测试服务器作测试。

这样计算出来的select (后边数据-前面的数据)/1024/1024*60 asMB_per_hour;值是一个小时后 redo log的大小

可是用这种方法计算必定是不合适的,在一分钟内业务繁忙或者业务空闲时间计算出的值都会产生较大偏差。

合适的方法是在一天中肯定几个时间点,用一个脚本定时执行,而后记录相应的值,再取平均值,计算出的偏差将减至最小。

什么是 sequece? 当每一个 binlog生成时,该值从1开始,而后递增,每增长一个事务, sequenumber就加上1。

2、binlog

您能够从整体上了解到 MySQL架构分为两层,一个是 server层,另外一个是存储引擎层。

server层固然是负责功能方面的,而存储引擎层则负责处理与存储相关的操做。

并且上面提到的redo log是Innodb存储引擎层特有的,其它存储引擎是不具有的,而server层也有本身的日志记录,就是将要聊到的binlog。

redo log和binlog的区别

redo log是Innodb引擎特有的,而binlog是MySQLserver层特有的,全部引擎均可以使用。

redo log是物理日志,它记录的是一条更新操做所作的修改,binlog是逻辑日志,记录的是一条更新语句执行逻辑

redo log是循环写的,而且空间是固定的,好比上面配置4个1GB的redo log文件,binlog是追加写的,这个文件写完了,换下一个文件,不会覆盖之前的日志。这也就是你常常看到只要你有完整的binlog文件就能够给你恢复到你想要的数据。

MySQL为何会有俩份日志呢?

在没有Innodb存储引擎以前,MySQL默认存储引擎是MyIsam,但MyIsam是没有重启恢复能力的,binlog日志也仅用于归档。

Innodb是另外一家公司以插件的形式引入到Mysql,既然binlog没有重启恢复的能力,那么我就使用redo log来实现重启恢复的功能。

这就致使了当你使用Innodb存储引擎时会写俩份日志。

3、什么是两阶段提交

对redo log、binlog有了必定的认识后再来看看一条更新语句的执行流程。

update user set age = age + 1 where id = 1;

  • 执行器先到引擎层找到id = 1这一行,因为ID是主键,因此会在主键索引树找到这一行。若是ID=2这一行所在的数据页原本就在内存中,就直接返回给执行器。不然,须要先从磁盘中读入内存,而后再返回。

  • 执行器拿到存储引擎返回id = 2结果后,给age加上1,原来是25,如今就是26,在调用引擎接口写入这行新数据。

  • 引擎将这行数据先更新到内存中,同时将这个更新操做记录到redo log中,此时redo log处于prepare状态。而后告知执行器执行完成了,随时能够提交事务。

  • 接着执行器生成这个操做的binlog,并把binlog写入磁盘。

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

到这里你应该就清晰了,一条更新SQL会先写redo log再写binlog,这也就是标题为何叫一辈子挚友redo log、binlog

4、为何须要两阶段提交

是为了让redo log跟binlog两份日志之间的逻辑一致,看下面俩种状况。

先写redo log后写binlog

  • 更新语句为age = age +1

  • 将数据写入redo log,MySQL进程异常重启

  • 此时binlog尚未开始写

  • 系统重启后进行数据恢复此时的值为26

  • 须要搭建从库时须要拿binlog进行恢复数据,但此时age = age +1 这行的操做是没有记录到binlog的

  • 那么此时的从库就会少这一次的更新,恢复出来的age依然是25,形成于主库数据不一致。

先写binlog后写redo log

  • 更新语句为age = age +1

  • 将数据写入binlog,MySQL异常重启

  • 此时redo log 还没写

  • MySQL系统重启,这个更新操做是对于redo log是不存在的,因此重启后的值依然是25

  • 但binlog 中的值已将是26了

  • 须要搭建从库时,从库的值是26,主库的值是25,形成主从数据不一致

因此说,若是不使用两阶段提交,那么原库和用它的binlog日志恢复出来的库数据是不一致的。

5、《孔乙己》让你明白redo log是什么

来看一个初中九年级语文课文中《孔乙己》这篇文章,就算不记得内容,标题总记得哈!

这个案例也是看丁老师文章中提到的,为何丁老能够灵活的使用这个案例来说redo log而咱们想不到呢?

其本质缘由是对知识点没有理解透彻,使用生活案例来解释技术是让人最容易理解并不难遗忘的。

《孔乙己》中的主人公就叫他酒店掌柜,掌柜的有俩件法宝让比其余老板工做效率高不少。一个是小黑板另外一个是帐本。

试想一下若是有客人要赊帐,是直接写到黑板效率高,仍是翻密密麻麻的帐原本的快呢?

掌柜确定会选择先记录到黑板上,等人少或者不忙时再把黑板的记录写到帐本中。

反之老板没有黑板的话,只能在密密麻麻的帐本中先找到赊帐人的名字,若是以前有赊帐记录追加,找了一遍发现没有才进行新增。

这个过程不只繁琐并且效率低的让人难以接受,若是酒店客人多老板是记录不过来的。

一样,在MySQL中也会存在这个问题,每次执行更新语句都须要先找到那条记录,而后再更新,整个过程IO成本、查找成本都很高。因此MySQL也利用了酒店掌柜的智慧使用黑板来提高执行效率。

画一幅图让你们能更好的理解掌柜、黑板、在MySQL中的对应关系。

酒店掌柜于MySQL对应的关系

酒店掌柜于MySQL对应的关系

6、redo log参数详解

事务的持久性就是经过重作日志来实现的。

当提交事务以后,并非直接修改数据库的数据的,而是先保证将相关的操做记录到redo日志中。

数据库会根据相应的机制将内存的中的脏页数据刷新到磁盘中。

重作日志写入流程

重作日志写入流程

上图是一个简单的重作日志写入流程。

在上图中提到俩个陌生概念,Buffer pool、redo log buffer,这个俩个都是Innodb存储引擎的内存区域的一部分。

而redo log file是位于磁盘位置。

也就说当有DML(insert、update、delete)操做时,数据会先写入Buffer pool,而后在写到重作日志缓冲区。

重作日志缓冲区会根据刷盘机制来进行写入重作日志中。

这个机制的设置参数为innodb_flush_log_at_trx_commit,参数分别为0,1,2

刷盘策略

刷盘策略

上图即为重作日志的写入策略。

  • 当这个参数的值为0的时,提交事务以后,会把数据存放到redo log buffer中,而后每秒将数据写进磁盘文件

  • 当这个参数的值为1的时,提交事务以后,就必须把redo log buffer从内存刷入到磁盘文件里去,只要事务提交成功,那么redo log就必然在磁盘里了。

  • 当这个参数的值为2的状况,提交事务以后,把redo log buffer日志写入磁盘文件对应的os cache缓存里去,而不是直接进入磁盘文件,1秒后才会把os cache里的数据写入到磁盘文件里去。

服务器异常中止对事务如何应对(事务写入过程)

  • 当参数为0时,前一秒的日志都保存在日志缓冲区,也就是内存上,若是机器宕掉,可能丢失1秒的事务数据。

  • 当参数为1时,数据库对IO的要求就很是高了,若是底层的硬件提供的IOPS比较差,那么MySQL数据库的并发很快就会因为硬件IO的问题而没法提高。

  • 当参数为2时,数据是直接写进了os cache缓存,这部分属于操做系统部分,若是操做系统部分损坏或者断电的状况会丢失1秒内的事务数据,这种策略相对于第一种就安全了不少,而且对IO要求也没有那么高。

小结

关于性能:0>2>1

关于安全:1>2>0

根据以上结论,因此说在MySQL数据库中,刷盘策略默认值为1,保证事务提交以后,数据绝对不会丢失。

坚持学习、坚持写做、坚持分享是咔咔从业以来所秉持的信念。愿文章在偌大的互联网上能给你带来一点帮助,我是咔咔,下期见。