Mysql专栏 - mysql、innodb存储引擎、binlog的工作流程
# 前言这次新开了一个个人的mysql专栏，专门用于总结mysql的一些细节以及相关的案例总结，同时也包括了一些mysql的底层实现，在后续的篇章则是根据《mysql技术内幕innodb存储引擎》（第二版）来深入了解mysql中用的最多的存储引擎的内部细节。
# 什么是数据库驱动？
mysql的驱动相信每一个做开发的人员都很熟悉，在通常的情况下我们只需要引入一个mysql的依赖同时使用jdbc的模板代码就能完成一次mysql的连接。
**一次网络连接必须要让线程来进行处理**
毫无疑问，一次网络连接不管是在tomcat还是在mysql的内部都是通过一个单独的线程进行管理的，当然tomcat的线程和mysql的线程没有任何的关系。
我们通常情况下都是直接使用tomcat的servlet Api进行开发，同时Servlet的每一次访问都是一个单独的线程，意味着每个用户的访问都是相互隔离的，但是如果我们数据库驱动连接是单线程的并且只能有一个用户连接，当之前的线程还在连接处理数据，后面的线程进行连接那么之前的处理数据的数据库连接就会断开，这样肯定是不行的，同时如果线程用完之后就被丢弃，这样也会造成资源的浪费甚至有可能出现空闲线程明明有很多但是系统由于某种情况无法回收等问题，最终导致线程等待数据库的连接请求等问题，基于这些问题数据库有了**连接池**的概念。
## 数据库连接池
连接池的作用就是维持数据库当中多个数据库连接，当有线程访问的时候就会从连接池当中分配一个连接给对应的线程。另外mysql架构当中的第一个重要体系概念就是连接池，值得注意的是**连接池并不是单方面的的，而是在连接方和mysql数据库驱动方都会存在一个数据库的连接池**。
根据上面的介绍，我们绘制出下面的一张图，可以看到其实在tomcat的内部以及mysql服务器的内部都有一个对应的连接池进行对应，有了数据库连接池之后，每一个用户处理完数据库的请求会把线程归还给线程池，这样不仅提高了线程的复用率又保证了每一个用户的数据库连接和操作是独立的。
```Java
疑问：
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执行过程中的过程步骤：
## 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一致性用的。
# 相关面试题
【腾讯二面】5s内建立多少个mysql连接？：(https://blog.51cto.com/u_15127515/2684815)
# 总结
我们从数据库的驱动介绍开始，介绍了整个mysql的工作流程，同时介绍了innodb存储引擎的工作流程，最后我们介绍了执行器的一个关键日志binlog日志的作用以及实际的运行效果。
# 写在最后
下一节将会根据一些生产的案例来看看如何进行mysql的优化。 小哥哥，小哥哥，我是你的小迷妹，嘤嘤嘤，爱你哟
页:
[1]