Mysql专栏 - mysql、innodb存储引擎、binlog的工作流程

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: Mysql专栏 - mysql、innodb存储引擎、binlog的工作流程

这是我参与8月更文挑战的第28天,活动详情查看:8月更文挑战

前言

这次新开了一个个人的mysql专栏,专门用于总结mysql的一些细节以及相关的案例总结,同时也包括了一些mysql的底层实现,在后续的篇章则是根据《mysql技术内幕innodb存储引擎》(第二版)来深入了解mysql中用的最多的存储引擎的内部细节。

什么是数据库驱动?

mysql的驱动相信每一个做开发的人员都很熟悉,在通常的情况下我们只需要引入一个mysql的依赖同时使用jdbc的模板代码就能完成一次mysql的连接。

一次网络连接必须要让线程来进行处理

毫无疑问,一次网络连接不管是在tomcat还是在mysql的内部都是通过一个单独的线程进行管理的,当然tomcat的线程和mysql的线程没有任何的关系。

我们通常情况下都是直接使用tomcat的servlet Api进行开发,同时Servlet的每一次访问都是一个单独的线程,意味着每个用户的访问都是相互隔离的,但是如果我们数据库驱动连接是单线程的并且只能有一个用户连接,当之前的线程还在连接处理数据,后面的线程进行连接那么之前的处理数据的数据库连接就会断开,这样肯定是不行的,同时如果线程用完之后就被丢弃,这样也会造成资源的浪费甚至有可能出现空闲线程明明有很多但是系统由于某种情况无法回收等问题,最终导致线程等待数据库的连接请求等问题,基于这些问题数据库有了连接池的概念。

数据库连接池

连接池的作用就是维持数据库当中多个数据库连接,当有线程访问的时候就会从连接池当中分配一个连接给对应的线程。另外mysql架构当中的第一个重要体系概念就是连接池,值得注意的是连接池并不是单方面的的,而是在连接方和mysql数据库驱动方都会存在一个数据库的连接池

根据上面的介绍,我们绘制出下面的一张图,可以看到其实在tomcat的内部以及mysql服务器的内部都有一个对应的连接池进行对应,有了数据库连接池之后,每一个用户处理完数据库的请求会把线程归还给线程池,这样不仅提高了线程的复用率又保证了每一个用户的数据库连接和操作是独立的。

image.png

疑问:
1. mysql数据库连接池最多可以开启多少个连接?
2. 如何控制mysql的连接数?
解答:
1、查看最大连接数
show variables like '%max_connections%';
但是这里有一个参数叫做 'max_user_connections'的参数,这个参数和'max_connections'有什么关系呢?
max_connections 是指整个mysql服务器的最大连接数;
max_user_connections 是指每个MySQL用户的最大连接数
2、修改最大连接数
set GLOBAL max_connections = 200;

mysql的基本结构

下面我们来看下mysql的基本结构是什么,从外层来看,整个请求的处理似乎都是在mysql服务器里面进行处理,然而实际上mysql设计肯定不会这样简单,在mysql的内部,他会被拆分为下面这几个组件:

sql解析器:解析用户发来的sql语言,负责分析语法,对于sql语句进行拆解等相关操作。

查询优化器:查询优化器负责解析之后的sql语法进行逻辑的优化,保证sql可以按照最短路径来执行

存储引擎:存储引擎是用于真正执行sql的组件,存储引擎按照sql语句进行内存和硬盘上的数据更新操作。同时使用对外的接口进行操作。

执行器:执行器就是负责真正调用存储引擎上接口的组件。

下面我们按照上面的顺序通过图画来描述mysql内部是如何运作的:

sql解析器:

当我们通过tomcat发送一条请求的时候,数据库连接会干啥,他会根据tomcat发来的查询语句,通过一个叫做sql解析器的东西进行解析,其实数据库服务器本身也不知道这条语句干啥用的,所以sql解释器负责管理这个东西,在通过sql接口接受到请求之后,会传递个sql解析器去进行解析动作。

select id,name,age from users where id=1
个查询解析器(Parser)就是负责对SQL语句进行解析的,比如对上面那个SQL语句进行一下拆解,拆解成以下几个部分: 我们现在要从“users”表里查询数据
查询“id”字段的值等于1的那行数据
对查出来的那行数据要提取里面的“id,name,age”三个字段

通过上面的案例我们可以知道了,Sql解析其实就是分解sql语法,通过sql语法分析出具体要做什么事情。

查询优化器

既然知道了如何解析,下面就应该了解如何进行查询优化了。查询优化器顾名思义就是对于查询的路径进行优化和选择,比如下面的sql语句有两种选择(当然数据库真实执行肯定不是这样的,但是我们可以模糊的理解)

  • 查询id=?的记录,并且提取对应的字段
  • 找到需要的三个字段的所有数据,并且从中选择id=?的数据
    查询优化器就是用来优化查询逻辑的,会用最短的查询路径对于查询进行优化
    当然这种优化是有限的,更多情况下还是要看sql语句的查询逻辑是否复杂以及sql的质量是否足够好。

存储引擎

既然查询优化器还不是执行查询的地方,那么我们接下来再来看下查询优化器处理完之后如何处理。

我们都知道数据库无非是由一种特殊的数据结构构造的文件罢了,只是对外提供特定的接口以及需要按照mysql的语法才能对数据进行crud,既然是操作数据,那肯定逃不过硬盘和内存,既然存储引擎是直接和这个数据结构打交道的,那么通过查询优化器优化过的的查询语句肯定是要交给存储引擎的,既然执行语句的操作是交给存储引擎的,换句话说就是决定如何处理sql的最终执行权在存储引擎手上

执行器

存储引擎可以执行sql语句,但是谁来操作存储引擎的接口呢?实际上调用存储引擎接口的是执行器,执行器会按照存储引擎的接口按照一定的逻辑对sql进行执行。那么要如何调用接口呢?执行器会根据当前的存储引擎配套一份执行计划,然后调用存储引擎完成sql的语句对于数据进行增删改查。

根据上面的介绍,我们可以发现实际上mysql最核心的部分是 存储引擎,它是真正在做事的一个组件。所以下面将会介绍innodb存储引擎的结构:

innodb存储引擎和结构

这里跳过innodb的介绍,我们直接来了解内部的具体结构:

缓冲池

innodb使用缓冲池来缓解磁盘操作的压力,尽量让数据的操作在内存中进行,同时使用不定时脏数据刷盘的操作将内存的数据同步到磁盘,和多数的缓存作用理解一样,在进行查询的时候如果发现命中了缓冲池则会查缓存的数据,否则查磁盘的数据。在查缓存的时候为了防止其他的线程把缓冲池的数据变脏,会对数据进行加锁的操作。

undo日志

undo日志作用和我们平常的ctrl+z的原理是一样的,在更新数据之前innodb需要先把修改的前的数值放到undo日志里面,然后在执行后续的操作,这个undo日志也实现事务回滚的组件,了解mysql我们都知道数据要么在事务里面要么在事务执行之前的undo日志,一旦事务操作失败或者手动调用回滚rollback,此时mysql就需要根据undo日志的内容把数据进行回滚。

redo缓冲池和redo日志文件

redo日志翻译过来就是重做日志,他的结构类似缓冲池和硬盘的链接方式,所以从理解上来看需要看作两部分缓冲池和日志文件,整个redo的作用就是记录哪一行的数据进行了修改并且在事务提交将缓存刷新到日志文件进行同步。(说白了还是磁盘io)

了解了基本redo作用之后我们来思考一下如果mysql服务操作数据的时候发生了数据丢失的情况,会出现什么情况?

为了解决这个问题,innodb在准备执行事务之前会事先操作一遍redo log buffer的缓冲区,这个缓冲区用来存放哪一行文件的那个字段被修改了,记录数据所在的位置以及改动的数据内容等。

事务没有提交,宕机了如何处理?

数据库当中每次执行一条sql都是一个事务的提交。如果数据库崩溃则会出现redo日志丢失的情况,时候mysql如何处理?

这个问题很简单,事务都没有提交,说明数据压根没有修改,而且此时虽然缓冲池的数据变了,但是磁盘数据行的内容没有变,所以redo缓冲池的数据丢失了也没有影响,当宕机重启恢复之后将会通过undo日志把内容进行还原

提交事务之后将redo日志写入磁盘

事务提交之后,此时redo会使用一定的策略把redo_log_buffer 的数据刷新到磁盘文件中,可以通过一个配置:innodb_flush_log_at_trx_commit更改策略

当这个值为0的时候不会把redo_log_buffer的内容刷新到磁盘,此时如果mysql宕机,内存的文件就全部丢失了:

如果把这个值设置为1:就会在事务提交成功之后把redo缓存数据刷新到日志文件当中,并且文件当中也会存在一条你修改了xxx行那个字段的记录。

如果此时缓冲池的内容更新了,redo日志的内容也更新了,redo文件里面的内容也记录了记录的修改内容,此时mysql突然宕机了会导致磁盘文件的磁盘数据的不一致么? 肯定不会的,如果此时一旦宕机,缓冲池就会去redo日志恢复之前的修改操作

如果innodb_flush_log_at_trx_commit 设置为2,会是什么情况? 此时提交事务的时候会把redo日志写到os cache里面,而不是写入磁盘,可能会是1秒之后才会把os cache的数据写入到磁盘文件。 但是此时需要注意这个日志文件是在cache里面的,所以意味着一旦宕机os cache的日志内容也没了。

三种redo日志刷盘策略到底选择哪一种?

提交事务的时候,我们对redo日志的刷盘策略应该选择哪一种?每一种刷 盘策略的优缺点分别是什么?为什么?

如果对于数据特殊要求一般情况下选1就可以了,如果选0,mysql一旦宕机就失效了,如果选2,同样虽然数据是写入到系统缓存的当中。但是还是在内存里面,只要断电宕机,数据同样会丢失。

Binlog 日志

我们先看下binlog日志在整个mysql执行过程中的过程步骤:

image.png

Bin log 日志是什么

在具体介绍之前,我们还是从redo log开始,redo log可以看作是保证事务一致性和数据正确的核心组件,在事务提交之前会将提交记录写入到redo log防止mysql宕机丢失数据的问题。

但是这一切都是基于存储引擎innodb处理的,其实在存储引擎的前一步执行器也有一个日志,那就是 bin log日志。

结论:Binlog 是属于 mysql server中执行器实现事务的重要内容。

binlog的工作流程

其实就是innodb存储引擎在redo log 准备好数据并且刷新到磁盘之后,执行器会把数据写入到一个binlog的日志文件进行存储。

执行器是非常核心的组件,需要配合存储引擎进行一个sql在磁盘和内存的全部操作。

binlog的刷盘策略

关键参数:sync_binlog 控制刷盘策略,默认值为0。bin写入磁盘之后,不是直接进入文件,而是和redo_log的参数设置2一样(innodb_flush_log_at_trx_commit = 2)写入到os缓冲池当中,当然会出现宕机的时候丢失缓冲池数据的问题。

如上图所示的5,6开始,就是你提交事务的阶段了。此时如果发生宕机,会和之前的redo_log 一样丢失内存的文件。

如果把 sync_binlog参数设置为1,则会出现在提交事务的时候,强制把缓冲的数据刷新到binlog磁盘里面。

写入commit标记

为什么有了redo_log 还需要binlog呢?

整个事务需要在binlog 完成了文件和写入到位置记录之后并且在redolog里面写入commit标记之后才进行处理。所以有了binlog之后,能保证整个事务的最终一致性和完整性。

redolog里面写入commit标记有什么意义?

本质上是为了保证redolog和binlog一致性用的。

image.png

相关面试题

【腾讯二面】5s内建立多少个mysql连接?:blog.51cto.com/u_15127515/…

总结

我们从数据库的驱动介绍开始,介绍了整个mysql的工作流程,同时介绍了innodb存储引擎的工作流程,最后我们介绍了执行器的一个关键日志binlog日志的作用以及实际的运行效果。

写在最后

下一节将会根据一些生产的案例来看看如何进行mysql的优化。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
17天前
|
存储 安全 关系型数据库
Mysql 的binlog日志的优缺点
MySQL的binlog(二进制日志)是一个记录数据库更改的日志文件,它包含了所有对数据库执行的更改操作,如INSERT、UPDATE和DELETE等。binlog的主要目的是复制和恢复。以下是binlog日志的优缺点: ### 优点: 1. **数据恢复**:当数据库出现意外故障或数据丢失时,可以利用binlog进行点恢复(point-in-time recovery),将数据恢复到某一特定时间点。 2. **主从复制**:binlog是实现MySQL主从复制功能的核心组件。主服务器将binlog中的事件发送到从服务器,从服务器再重放这些事件,从而实现数据的同步。 3. **审计**:b
|
26天前
|
SQL 关系型数据库 MySQL
mysql的binlog恢复数据
mysql的binlog恢复数据
28 0
|
1月前
|
存储 关系型数据库 MySQL
MySQL InnoDB数据存储结构
MySQL InnoDB数据存储结构
|
1月前
|
存储 缓存 关系型数据库
MySQL的varchar水真的太深了——InnoDB记录存储结构
varchar(M) 能存多少个字符,为什么提示最大16383?innodb怎么知道varchar真正有多长?记录为NULL,innodb如何处理?某个列数据占用的字节数非常多怎么办?影响每行实际可用空间的因素有哪些?本篇围绕innodb默认行格式dynamic来说说原理。
834 6
MySQL的varchar水真的太深了——InnoDB记录存储结构
|
2月前
|
存储 SQL 安全
浅谈MySQL Binlog
浅谈MySQL Binlog
45 0
|
14天前
|
存储 关系型数据库 MySQL
MySQL引擎对决:深入解析MyISAM和InnoDB的区别
MySQL引擎对决:深入解析MyISAM和InnoDB的区别
29 0
|
3月前
|
存储 SQL 关系型数据库
系统设计场景题—MySQL使用InnoDB,通过二级索引查第K大的数,时间复杂度是多少?
系统设计场景题—MySQL使用InnoDB,通过二级索引查第K大的数,时间复杂度是多少?
47 1
系统设计场景题—MySQL使用InnoDB,通过二级索引查第K大的数,时间复杂度是多少?
|
4月前
|
存储 缓存 关系型数据库
⑩⑧【MySQL】InnoDB架构、事务原理、MVCC多版本并发控制
⑩⑧【MySQL】InnoDB架构、事务原理、MVCC多版本并发控制
109 0
|
3月前
|
存储 SQL 关系型数据库
Mysql系列-4.Mysql存储引擎-InnoDB(下)
Mysql系列-4.Mysql存储引擎-InnoDB
46 0
|
2月前
|
存储 缓存 关系型数据库
MySQL - 存储引擎MyISAM和Innodb
MySQL - 存储引擎MyISAM和Innodb