一条sql语句在mysql中的执行过程_mysql一条sql语句的执行流程

一、总览

话不多说，直接上图，让大家理解一下一条sql语句执行的大概流程。

从上图中我们可以看到，这些东西大致分为三部分，分别是：客户端、server层和存储引擎。
1、Client层
我们使用mysql的自带的命令行工具，我们的springboot使用jdbc链接我们的数据库，或者sqlyog等连接工具都归纳为mysql客户端(Client)，主要用于发送执行sql语句的请求。
2、Server层
主要包括连接器、缓存查询、分析器、优化器、执行引擎等，涵盖mysql大部分功能以及所有内置函数（日期、数学和加密函数等），所有跨引擎的功能都在这一层实现，例如视图、存储过程、触发器等。
3、Store层
主要负责数据的存储和提取，支持InnoDB，MyISAM，Memory等多种存储引擎，其中InnoDB是目前大部分版本的默认存储引擎。

二、执行流程

在了解了大致流程后，现在来聊一下，一条sql语句在每个步骤执行的细节。
1、连接器
连接器位于Server层，作用是负责跟所有客户端的连接进行交互，并对连接进行鉴权管理连接等等，每来一个新连接，连接器都会为这个session开辟一个专属这个session的内存空间，来执行该session的后续操作。

[root@192 ~]# mysql ‐h localhost ‐uroot ‐p
1

在你敲下这个命令输入密码完成后，客户端就开始与服务端建立TCP长连接，服务端负责处理客户端连接的就是连接器。这时，连接器会校验你的host、username、password等信息，这些信息校验通过后，连接器会去权限表里面把你的权限拉出来，之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。这也就意味着一旦连接建议成功，不管管理员对权限表做什么样的修改，都不会影响该连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。用户的权限表在系统表空间的mysql的user表中。

连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist; 命令中看到它。文本中这个 图是 show processlist 的结果，其中的 Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接。

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

show global variables like "wait_timeout";
1

2、缓存查询
当连接简历成功后，select语句就会来到缓存查询这一块，尝试从缓存中拿到数据。以前执行过的sql语句和结果会以K-V的形式存储，sql是K，查询的结果是V。如果拿到了结果会直接返回，如果没拿到结果则会继续走后面的流程拿到数据返回。
但是大多数情况下，该缓存很鸡肋，为什么这样说，假设我们缓存中一张表存在很多缓存，一旦该表执行了update语句，因此所有缓存数据都会失效，对于更新压力大的数据表命中率会很低。在mysql8.0版本已经将缓存功能移除。

一般建议大家在静态表里使用查询缓存，什么叫静态表呢？就是一般我们极少更新的表。比如，一个系统配置表、字典 表，那这张表上的查询才适合使用查询缓存。好在 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
1
2
3

这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下 面这个语句一样：

 select SQL_CACHE * from test where ID=5；
1

查看当前mysql实例是否开启缓存机制

show global variables like "%query_cache_type%";
1

3、分析器
如果没有命中缓存，那么就来到了分析器，mysql需要知道你想做什么。

分析器首先会进行词法分析，你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是 什么，代表什么。 MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符 串“ID”识别成“列 ID”。

然后进行语法分析，根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句 是否满足 MySQL 语法。 如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒。

语法分析结束后，会生成一棵语法树，大致如下：

4、优化器
经过了分析器，mysql就知道你需要做什么了，在开始执行之前，mysql还需要对它做一些优化。
优化器是在表里面有多个索引决定使用哪个索引，或者多表关联join的时候，是先执行左边的逻辑还是右边的逻辑等做一系列的处理。

select * from test1 join test2 using(ID) where test1.name="haha" and test2.name="hehe";
1

既可以先从表 test1 里面取出 name=haha的记录的 ID 值，再根据 ID 值关联到表 test2，再判断 test2 里面 name的 值是否等于hehe。

也可以先从表 test2 里面取出 name=hehe的记录的 ID 值，再根据 ID 值关联到 test1，再判断 test1 里面 name 的值是否等于 haha。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。优化器阶段 完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

5、执行引擎
开始执行的时候，要先判断一下你对这个表 T有没有执行查询的权限，如果没有，就会返回没有权限的错误，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限。

 select * from test where id=1;
1

如果有权限，就打开表继续执行。

打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。 比如我们这个例子中的表 test 中，ID 字段没有索引，那么执行器的执行流程是这样的：

1、调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 1，如果不是则跳过，如果是则将这行存在结果集中；
2、调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
3、 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

至此，这个语句就执行完成了。对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。你会在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加 的。在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。

三、BinLog归档日志

删库是不需要跑路的哈，因为我们的SQL执行时，会将sql语句的执行逻辑记录在我们的bin-log当中，binlog是Server层实现的二进制日志,他会记录我们的cud操作。Binlog有以下几个特点：

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

如果，我们误删了数据库,可以使用binlog进行归档!要使用binlog归档，首先我们得记录binlog，因此需要先开启MySQL的 binlog功能。
编辑my.cnf配置文件

# 配置开启binlog  
log‐bin=/usr/local/mysql/data/binlog/mysql‐bin 
#注意5.7以及更高版本需要配置本项：server‐id=123454（自定义,保证唯一性）
#binlog格式，有3种statement,row,mixed 
binlog‐format=ROW 
#表示每1次执行写入就与硬盘同步，会影响性能，为0时表示，事务提交时mysql不做刷盘操作，由系统决定 
sync‐binlog=1
1
2
3
4
5
6
7