SQL执行流程与Binlog

- 日理万妓 2023-09-29 12:28 9阅读 0赞

MySQL中，一条语句的执行要经过很多个步骤才能完成，MySQL将这些步骤分为两个大的部分：Server层和存储引擎层，其中Server层负责SQL语法解析、优化，然后调用存储引擎的查询接口，而存储引擎只需要提供统一的入口即可。

内部组件如下：  
![17ad9c1072744183b223135720066877.png][]

通过这种图，我们来具体分析MySQL中一条SQL的执行过程。

### 一、Server层 ###

Server层主要包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

#### 1.1 连接器 ####

根据MySQL的User表，对username对应的Host和Password进行校验，校验通过则建立连接，管理连接与权限校验

连接器负责跟客户端建立连接、获取权限、维持和管理连接

连接命令：

mysql -h host[数据库地址] -u root[用户] -p root[密码] -P 3306

在完成经典的 TCP 握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。

连接建立之后，需要在内存中为当前连接分配一块内存空间，算是Session会话的空间。里面会缓存当前用户的所有权限信息，Session会话空间也需要存储客户端执行的一些命令。

当使用普通用户建立起连接后，如果通过ROOT用户修改普通用户的权限，Session会话缓存中的用户权限并没有发生变化，只有当前连接断开，重新建立一个连接时，之前修改的用户权限才会生效

连接完成后，如果没有后续的动作，这个连接就处于空闲状态，可以使用`show processlist`命令查看，然后使用`kill [id]`可以关闭指定连接。

客户端如果长时间不发送command到Server端，连接器就会自动将它断开。这个时间是由参数 wait\_timeout 控制的，默认值是 8 小时。

开发中，大多数时候用的是长连接，把连接放入连接池进行管理，但是长连接有时候会导致MySQL占用内存涨的太快，这是因为MySQL在执行过程中临时使用的内存是在连接对象里面管理的，而这些资源只有在连接断开的时候才释放。  
所以，当长连接积累下来，可能导致内存占用太大，被系统强行杀掉(OOM),才现象看就是MySQL异常重启了  
**解决办法：**  
1、定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。

2、如果使用的是 MySQL 5.7 以上版本，可以在每次执行一个比较大的操作后，通过执行`mysql_reset_connection`来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

#### 1.2 查询缓存 ####

默认关闭，MySQL8.0已经废除该功能。在MySQL服务刚启动时，SQL查询后，会把SQL语句作为KEY,把查询结果作为VALUE进行缓存，后续再次查询时，可以直接从缓存取。  
大多数情况查询缓存就是个鸡肋，查询缓存的失效非常频繁，只要对一个表进行更新，这个表所有的缓存都会被清空。因此很可能导致缓存的结果还没使用呢，就被一个更新全部清空了。对于更新频繁的数据库，查询缓存的命中率是非常低的。  
静态表里可以使用查询缓存，因为这种表极少更新。比如：系统配置表、字典表，那这种表上的查询才适合使用查询缓存。好在 MySQL 也提供了这种“按需使用”的方式。可以将**my.cnf**参数`query_cache_type`设置成 `DEMAND`。  
配置如下(**my.cnf**):

-- query_cache_type有3个值 0代表关闭查询缓存OFF，1代表开启ON，2（DEMAND）代表当sql语句中有SQL_CACHE关键词时才缓存
    query_cache_type=2

可以通过命令`show status like '%Qcache%'`查看当前查询缓存的一些情况：  
![22eb1e537cf0481daea0b616302eb4c1.png][]

**Qcache\_free\_blocks**：表示查询缓存中目前还有多少剩余的blocks，如果该值显示较大，则说明查询缓存中的内存碎片过多了，可能在一定的时间进行整理。

**Qcache\_free\_memory**：查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，DBA可以根据实际情况做出调整。

**Qcache\_hits**：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。

**Qcache\_inserts**：表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数，次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。

**Qcache\_lowmem\_prunes**：该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小。

**Qcache\_not\_cached**：表示因为query\_cache\_type的设置而没有被缓存的查询数量。

**cache\_queries\_in\_cache**：当前缓存中缓存的查询数量。

**Qcache\_total\_blocks**：当前缓存的block数量。

`查询缓存的相关信息只需要了解即可，完全没必要深入了解，毕竟在新版本已经被启用了，MySQL可以通过其他缓存方式来提高效率。`

#### 1.3 词法分析器 ####

在执行一条SQL的时候，首先，MySQL需要知道要做什么，因此需要对SQL语句进行解析。

分析器先做词法分析，一条SQL语句由多个字符串和空格构成，MySQL需要识别里面的每个字符串分别是什么，代表什么。例如：从输入的`select`关键字识别出这是一个查询语句，也需要把字符串`T`识别成表名`T`,把字符串`ID`识别成`列ID`  
词法分析完成之后，就要进行语法分析了。语法分析会根据MySQL的语法规则，判断输入的SQL语句是否符合语法，如果语句不对，就会收到错误提示

You have an error in your SQL syntax

词法分析器分成6个主要步骤来完成对SQL语句的分析：

*  词法分析
 *  语法分析
 *  语义分析
 *  构造执行树
 *  生成执行计划
 *  计划的执行

其中词法分析的步骤如下：  
![729abf36a9344f01a86aabc73837eb38.png][]

MySQL的词法分析由MySQLLex【MySQL自己实现的】完成，语法分析由Bison生成。  
关于语法树可以参考：https://en.wikipedia.org/wiki/LR\_parser  
除了Bison外，Java中也有开源的词法结构分析工具如Antlr4,ANTLR从语法生成一个解析器，可以构建和遍历解析树，可以在IDEA中安装插件：antlr v4 grammar plugin  
经过Bison语法分析之后，会生成一个如图所示的语法树，至此，分析器的任务就圆满完成了，接下来进入到优化器阶段。  
![5dc0af3a3118467496dac2394143d8d2.png][]

#### 1.4 优化器 ####

经过词法分析器之后，MySQL就知道要做什么了。但是在开始执行前，还要经过优化器的处理(查询成本计算)。  
优化器是在表里面存在多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联(join)的时候，决定各个表的关联顺序。  
以下面的SQL语句为例：

select * from test1 join test2 using(ID) where test1.name=lizhi and test2.name=zhuyuzhu;

既可以先从`test1`表里面根据**name=lizhi**取出记录的ID值，然后根据ID值关联到表`test2`，在判断`test2`里面**name**的值是否等于**zhuyuzhu**。  
也可以先从`test2` 里面取出ID值，再根据ID关联到`test1`表，判断`test2`里面的**name**值是否等于**lizhi**。  
这两种方式的执行逻辑是一样的，但是执行的效率可能会有所不同。而优化器的作用就是决定选择使用哪一个方案。优化器阶段完成后，这个SQL语句的执行方案就确定下来了，然后进入执行器阶段。

#### 1.5 执行器 ####

执行器在执行计划的时候，首先要判断一个对这个表有没有执行查询的权限，如果没有就会返回权限错误的信息。如果有权限，就打开表继续执行。

打开表的时候，执行器会根据表的引擎定义，去使用该引擎对应的接口。以下面的SQL语句为例：

select * from test where id=1;

在表test中，ID字段并没有索引，那么执行器的流程大概如下：  
1、调用InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这个记录添加到结果集中  
2、调用引擎接口取“**下一行**”，重复相同的判断逻辑，直到取到这个表的最后一行。  
3、执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。  
至此，这个语句就执行完成了。对于没有索引的表，执行的逻辑也差不多，第一次调用的是“取满足条件的第一行”这个接口，之后循环去调用“满足条件的下一行”这个接口，这些接口都是存储引擎已经定义好的。  
在数据库的慢查询日志中看到一个`rows_examined`的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。  
但在有些场景下，执行器调用一次，引擎内部则有可能扫描多行，因此引擎扫描行数跟`rows_examined`并不是完全相同的。

### 二、存储引擎 ###

存储引擎负责数据的存储和提取，采用插件式的架构。  
支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。  
也就是说如果我们在**create table**时不指定表的存储引擎类型,默认会给你设置存储引擎为`InnoDB`。

### 三、Binlog归档日志 ###

在执行SQL的时候，很可能出现误操作，将整张表的数据都更新或删除了，这个时候，就可以通过`bin-log`日志来恢复，因为在执行SQL时，MySQL会把语句的执行逻辑记录在`bin-log`中，那么什么是bin-log呢？  
`binlog`是server层实现的二进制日志，会记录所有**cud**操作，`binlog`有如下几个特点：

1、Binlog在server层实现(引擎共用)
    2、Binlog是逻辑日志，记录的是一条语句的原始逻辑
    3、Binlog不限大小，追加写入，不会覆盖以前的日志

#### 3.1 开启binlog ####

bin-log在5.7默认是关闭状态，8.0默认开启，可以通过命令查看  
所以需要先开启binlog功能，配置my.cnf:

// 配置开启binlog
    log-bin=/usr/local/mysql/data/binlog/mysql-bin

注意5.7以及更高版本需要配置本项：**server-id=123454（自定义,保证唯一性）**;

// binlog格式，有3种statement,row,mixed
    // statement标识记录操作的sql语句
    // row记录SQL语句操作后的结果
    // mixed 综合上面两种情况；推荐使用row
    binlog-format=ROW

// 表示每1次执行写入就与硬盘同步，会影响性能，为0时表示，事务提交时mysql不做刷盘操作，由系统决定
    sync-binlog=1

#### 3.2 binlog常用命令 ####

1、查看binlog是否开启

show variables like '%log_bin%';

2、添加一个最新的bin-log日志

flush logs;

3、查看最后一个bin-log日志的相关信息

show master status;

4、清空所有的bin-log日志

reset master;

#### 3.3 查看binlog内容 ####

查看binlog内容的方式：

mysqlbinlog --no-defaults /var/lib/mysql/binlog.000002

`binlog`日志的内容并不具备可读性，所以需要自己判断恢复的逻辑点。看重点信息，比如**begin,commit**这种关键字信息，只要在`binlog`中看到了，就可以理解为**begin-commit**之间的信息是一个完整的事务逻辑，然后再根据位置`position`判断恢复即可  
我们以test表为例，插入了两条数据，然后查看binlog记录，可以看到图上标注出来的**begin**和**commit**组合，以及对应的`position`：  
![79b792130040466ebd60ac92567c2fbe.png][]

#### 3.4 数据归档 ####

在上一步，在test表里面创建了两条数据，我们删除第二条数据，然后第二条数据对应的开始和结束位置分别为**600**和**738**，所以可以通过归档操作恢复数据

mysqlbinlog --no-defaults --start-position="600" --stop-position="738" /var/lib/mysql/binlog.000002 |mysql -u root -p test(数据库名)

**注：后面最后的`test`是数据库名称**  
除了通过起止位置的方式恢复外，还可以通过开始和结束时间来归档：把`position`改为`date`

--stop-date= "2022-03-28 12:00:00"  --start-date= "2022-03-02 11:55:00"

也可以不指定参数，意味着归档整个`binlog`文件的操作

[17ad9c1072744183b223135720066877.png]: https://img-blog.csdnimg.cn/17ad9c1072744183b223135720066877.png
[22eb1e537cf0481daea0b616302eb4c1.png]: https://img-blog.csdnimg.cn/22eb1e537cf0481daea0b616302eb4c1.png
[729abf36a9344f01a86aabc73837eb38.png]: https://img-blog.csdnimg.cn/729abf36a9344f01a86aabc73837eb38.png
[5dc0af3a3118467496dac2394143d8d2.png]: https://img-blog.csdnimg.cn/5dc0af3a3118467496dac2394143d8d2.png
[79b792130040466ebd60ac92567c2fbe.png]: https://img-blog.csdnimg.cn/79b792130040466ebd60ac92567c2fbe.png