前言

本篇文章简单了解一下MySQL语句的执行过程，有助于我们对SQL有比较深入和细致的理解，提高我们的数据库理解能力。

MySQL语句执行过程

我们先简单总结一下mysq语句的执行过程：

1、在客户端发起一个SQL查询；

2、连接器判断用户登录以及用户权限；

3、查询缓存，缓存命中，直接返回查询结果，缓存没命中继续往下走；

3、到达分析器，对SQL语句进行分析，包括预处理与解析过程；

4、优化器，对SQL语句进行优化；

5、执行器，调用存储引擎，执行具体的SQL操作；

6、通过索引查找数据；

7、写入binlog日志，innodb还会写入redo log；

8、返回数据；

一、连接器

连接器就是负责与客户端连接，其通信协议采用 半双工模式（一般来说，不需要去理解Mysql通讯协议内部实现细节，只需要大致理解通讯协议是如何工作的），这就意味着某一固定时刻只能由客户端向服务器请求或者服务器向客户端发送数据，而不能同时进行。这种协议让mysql通信简单快速，但也限制了mysql，一个明显的限制是，这意味着没办法进行流量限制。一旦一端开始发生消息，另一端要接收完整个消息才能响应他。

这里简单说一下TCP/IP的三种通信方式：单工通信，半双工，全双工。

全双工： 打电话。电话在接到声音的同时也会传递声音。在一个时刻，线路上允许两个方向上的数据传输。网卡也是双工模式。在接收数据(比如一直在下载东西)的同时，又发送数据(比如请求网页)

半双工： 在同一个时刻只能进行一个动作。在一个时刻，线路上只允许一个方向上的数据传输。

单工： 典型例子就是电视遥控器。接收端和发送端已经固定了的。接收端只能接收数据。没有发送数据的功能。发送端只有发送的功能。没有接收的功能.特点是,线路上的数据流是永远是单方向,固定方向。这样理解的话，显示器与电脑主机之间的工作方式也是单工模式的。

连接mysql：

[root@VM-0-12-centos ~]# mysql -uroot -p
Enter password:
mysql>

校验用户名及密码
校验通过后，在权限表中查询该用户的权限

MySQL中存在4个控制权限的表： 分别为 user表，db表，tables_priv表，columns_priv表：

user表：存放用户账户信息以及全局级别（所有数据库）权限，决定了来自哪些主机的哪些用户可以访问数据库实例

db表：存放数据库级别的权限，决定了来自哪些主机的哪些用户可以访问此数据库

tables_priv表：存放表级别的权限，决定了来自哪些主机的哪些用户可以访问数据库的这个表

columns_priv表：存放列级别的权限，决定了来自哪些主机的哪些用户可以访问数据库表的这个字段

MySQL权限表的验证过程为：

1、先从user表中的Host，User，Password这3个字段中判断连接的IP、用户名、密码是否存在，存在则通过验证。

2、通过身份认证后，进行权限分配，按照user，db，tables_priv，columns_priv的顺序进行验证。即先检查全局权限表user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db，tables_priv，columns_priv；如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限；如果db中为N，则检查tables_priv中此数据库对应的具体表，取得表中的权限Y，以此类推。

3、如果在任何一个过程中权限验证不通过，都会报错。

通过 show processlist 可以查看客户端连接的状态：

mysql> show processlist;
+-------+------+-----------+------+---------+------+-------+------------------+
| Id    | User | Host      | db   | Command | Time | State | Info             |
+-------+------+-----------+------+---------+------+-------+------------------+
| 17602 | root | localhost | NULL | Query   |    0 | init  | show processlist |
+-------+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.03 sec)

mysql>

二、查询缓存

当连接建立完成之后，为了提高完全相同的 select 查询语句的响应速度，MySQL会对查询语句进行hash计算后，把得到的hash值与查询的结果集对应存放在查询缓存中。

如果没有匹配，将这个hash值存放在一个hash链表中，并将查询的结果集存放到缓存中。
如果通过hash值匹配到了一样的查询，则直接将缓存中相应的查询结果集返回给客户端。
目前MySQL 缓存只会缓存 select 语句，其他类似show ，use的语句不会被缓存。

在MySQL的8.0版本以后，缓存被官方删除掉了。之所以删除掉，是因为查询缓存的失效非常频繁，如果在一个写多读少的环境中，缓存会频繁的新增和失效。对于某些更新压力大的数据库来说，查询缓存的命中率会非常低，MySQL为了维护缓存可能会出现一定的伸缩性的问题，目前在5.6的版本中已经默认关闭了，

三、分析器

分析器的主要作用是将客户端发过来的SQL语句进行词法分析和语法分析。

词法解析：识别 SQL 语句
语法解析：判断是否符合 MySQL语法

MySQL通过识别字符串中的列名、表名、where、select/update/insert 等MySQL关键词，根据语法规则判断sql是否符合语法要求，最终形成抽象的语法树(AST)。

MySQL分析器使用MySQL语法规则进行验证和分析查询，例如验证是否使用错误的关键字，或者使用关键字的顺序是否正确，或者验证引号是否正确。

四、优化器

优化器主要分为两个阶段，首先是逻辑查询优化，这个阶段主要是使用关系代数对SQL语句做一些等价变换，对条件表达式进行等价谓词重写、条件简化，对视图进行重写，对子查询进行优化，对连接语义进行了外连接消除、嵌套连接消除等。其次是物理查询优化。物理查询优化一般分为两种：

基于规则的优化（RBO，Rule-Based Optimizer）
- 这种方式主要是基于一些预置的规则对查询进行优化。
基于代价的优化（CBO，Cost-Based Optimizer）
- 这种方式会根据模型计算出各个可能的执行计划的代价，然后选择代价最少的那个。它会利用数据库里面的统计信息来做判断。

1、逻辑查询优化

查询优化器在逻辑优化阶段主要解决的问题是: 如何找出SQL语句等价的变换形式，使得SQL执行更高效。

1.1 逻辑查询优化思路。

一条SQL查询语句结构复杂，包含多种类型的字句，优化操作依赖于表的一些属性(如索引和约束等)。可用于优化的思路包括:

字句局部优化： 每种类型字句都可能存在优化方式，如等价谓词重写、where和having条件化简中的大部分情况，都属于这种字句范围内的优化。

字句间关联优化： 字句与字句之间关联的语义存在优化的可能，如外连接消除、连接消除、子查询优化、视图重写等都属于字句间的关联优化，因为他们的优化都需要借助其他字句、表定义或列属性等信息进行。

局部与整体的优化:： 需要协同考虑局部表达式和整体的关系，如OR重写并集规则需要考虑UNION操作(UNION师变换后的整体的形式)的花费和OR操作(OR是局部表达式)的花费。

形式变化优化： 多个字句存在嵌套，可以通过形式的变化完成优化，如嵌套连接消除。
语义优化:根据完整性约束、SQL表达的含义等信息对语句进行语义优化。

2.2 查询重写规则

传统的联机事务处理(OLTP)使用基于选择(SELECT)、投影(PROJECT)、连接(JOIN)3种基本操作相结合的查询，这种查询称为SPJ查询。
数据库在查询优化的过程中，会对这3种基本操作进行优化。优化的方式如下:

选择操作： 对应的是限制条件(格式类似field consant，field表示列对象，op是操作符，如=，>等)，优化方式是选择操作下推，目的是尽量减少连接操作前的远组数，使得中间临时关系尽量少(元组数少，连接得到的远组数就少)，这样可减少IO和CPU的消耗，节约内存空间。

投影操作： 对应的SELECT查询的目的列对象，优化方式是投影操作下推，目的是尽量减少连接操作前的列数，使得中间临时关系尽量小(特别注意差别:选择操作是使元组的个数"尽量少"，投影操作是使一条元组"尽量小")，这样虽然不能减少IO(多数数据库存储方式是行存储，元组是读取的最基本单位，所以要想操作列则必须读取一行数据)，但可以减少连接后中间关系的元组大小，节约内存空间)。

连接关系： 对应的是连接条件(格式类似field_1, field_2，field_1和field_2表示不同表上的列对象，op是操作符，如=，>等)，表示两个表连接的条件。这里涉及以下两个子问题:
多表连接中每个表被连接的顺序决定着效率。如果一个查询语句只有一个表，则这样的语句很简单；但如果有多个表，则会涉及表之间以什么样的顺序连接效率最高效(如A、B、C三表连接，如果ABC、ACB、BCA等连接后的结果集一样，则计算哪种连接次序的效率最高，是需要考虑的问题)。
多表连接每个表被连接的顺序由用户语义决定。查询语句多表连接有着不同的语义(如笛卡尔积、内连接、还是外连接中的左外连接等)，这决定着表之间的额前后连接次序是不能随意更换的，否则结果集中数据是不同的。因此，表的前后连接次序是不能随意交换的。
根据SQL语句的形式特点，可以针对SPJ的查询优化，如基于选择、投影、连接3种基本操作相结合的查询。

2.3 启发式规则再逻辑优化阶段的应用

逻辑优化阶段使用的启发式规则通常包括如下两类：

2.3.1 一定能带来优化效果的，主要包括:

优先做选择和投影(选择条件在查询树上下推)
子查询的消除
嵌套连接的消除
外连接的消除
连接的消除
使用等价谓词重写，对条件化简
语义优化
剪掉冗余操作(一些剪枝优化技术)、最小化查询块。

2.3.2 变换未必会带来性能的提高，需根据代价选择，主要包括:

分组的合并
借用索引优化分组、排序、DISTINCT等操作
对视图的查询变为基于表的查询
连接条件的下推
分组的下推
连接提取公共表达式
谓词的上拉
用连接取代集合操作
用UNIONALL取代OR操作

2、物理查询优化

查询优化器在物理优化阶段，主要解决的问题如下:

从可选的单表扫描方式中，挑选什么样的单表扫描方式是最优的？
对于两个表连接时，如何选择是最优的？
对多个表连接，连接顺序有多种组合，是否要对每种组合都探索？如果不全部探索，怎么找到最优的一种组合？
在查询优化器实现的早期，使用的是逻辑优化技术，即使用关系代数规则和启发式规则对查询进行优化后，认为生成的执行计划就是最优的。

在引入了基于代价的查询优化方式后，对查询执行计划做了定量的分析，对每一个可能的执行方式进行评估，挑出代价最小的作为最优的计划。
目前数据库的查询优化器通常融合这两种方式。

2.1 查询代价估算

查询代价估算的重点是代价估算模型，这是物理查询优化的依据。除了代价模型外，选择率对代价求解也起着重要作用。

2.2 单表扫描算法

单表扫描需要从表上获取元组，直接关联到物理IO的读取，所以不同的单表扫描方式，有不同的代价。

2.3 索引

索引是建立在表上的，本质上是通过索引直接定位表的物理元组，加快数据获取的方式，所以索引优化的手段应该归属到物理查询优化阶段。

2.4 两表连接算法

关系代数的一项重要操作是连接运算，多个表连接是建立在两表之间连接的基础上的。研究两表连接的方式，对连接效率的提高有着直接的影响。

2.5 多表连接算法

多表连接算法实现的是在查询路径生成的过程中，根据代价估算，从各种可能的候选路径中找出最优的路径(最优路径是代价最小的路径)。
多表连接算法需要解决两个问题:

多表连接的顺序: 表的不同连接顺序，会产生许多不同的连接路径；不同的连接路径有不同的效率。
多表连接的搜索空间:因为多表连接的顺序不同，产生的连接组合会有多种，如果这个组合的数据巨大，连接次数会达到一个很高的数量级，最大可能的连接次数是N！(N的阶乘)。比如N=5，连接次数是120；N=10，连接次数是362880。所有的连接可能构成一个巨大的"搜索空间"。如何将搜索空间限制在一个可接受的时间范围内，并高效地生成查询执行计划将成为一个难点。

3、优化器小结

MySQL整个查询优化器从代码层面看，逻辑结构不是很清晰，但是从技术层面看，还是能够分为两个阶段，一是逻辑查询优化阶段，二是物理查询优化阶段。

逻辑查询优化阶段： 主要依据关系代数可以推知的规则和启发式规则，对SQL语句进行等价变换。MySQL淋漓尽致地使用了关系代数中可推定的各项规则，对投影、选择等操作进行句式的优化；对条件表达式进行了谓词的优化、条件化简；对连接语义进行了外连接、嵌套连接的优化；对集合、GROUPBY等尽量利用索引、排序算法进行优化。另外还利用子查询优化、视图重写、语义优化等技术对查询语句进行了优化。

物理查询优化阶段： 通过贪婪算法，并依据代价估算模型，在求解多表连接顺序的过程中，对多个连接的表进行排序并探索连接方式，找出花费最小的路径，据此生成查询执行计划。在这个阶段，对于单表扫描和两表连接的操作，高效地使用了索引，提高了查询语句的执行速度。物理查询优化的最终结果是生成最终物理查询执行计划。

五、执行器

通过以上阶段所产生的 执行计划 进行操作。在执行之前需要判断用户是否具备权限。如果没有，返回权限错误，如果具备权限就会执行并返回结果。
执行器会根据表的引擎定义，调用存储引擎API对表进行读写。存储引擎Api只是抽象接口，下面还有一层存储引擎层，具体实现还要看表选择的存储引擎。

1、存储引擎

最常用的存储引擎还是 MyISAM 和 InnoDB ，下面罗列了MySQL部分存储引擎：

MyISAM： Mysql 5.5之前的默认数据库引擎，最为常用。拥有较高的插入，查询速度，但不支持事务；

InnoDB： 事务型速记的首选引擎，支持ACID事务，支持行级锁定，MySQL5.5成为默认数据库引擎；

Memory： 所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在MYSQL重新启动是会丢失；

Archive： 非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive 拥有高效的插入速度，但其对查询的支持相对较差；

Federated： 将不同的 MySQL 服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用；

CSV： 逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个 .csv 文件。这是一种普通文本文件，每个数据行占用一个文本行。CSV 存储引擎不支持索引。

BlackHole： 黑洞引擎，写入的任何数据都会消失，一般用于记录 binlog 做复制的中继；

ERFORMANCE_SCHEMA： 该引擎主要用于收集数据库服务器性能参数；

Mrg_Myisam Merge： 是一组MyIsam的组合，也就是说，他将MyIsam引擎的多个表聚合起来，但是他的内部没有数据，真正的数据依然是MyIsam引擎的表中，但是可以直接进行查询、删除更新等操作；

2、SQL执行状态

可以通过命令：show full processlist，展示所有的处理进程，主要包含了以下的状态，表示服务器处理客户端的状态，状态包含了从客户端发起请求到后台服务器处理的过程，包括加锁的过程、统计存储引擎的信息，排序数据、搜索中间表、发送数据等。囊括了所有的MySQL的所有状态,其中具体的含义如下图：

3、SQL执行顺序

事实上，SQL并不是按照我们的书写顺序来从前往后、左往右依次执行的，它是按照固定的顺序解析的，主要的作用就是从上一个阶段的执行返回结果来提供给下一阶段使用，SQL在执行的过程中每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。这些虚拟表对调用者（客户端应用程序或者外部查询）不可用，只是最后一步生成的表才会返回给调用者。SQL的执行顺序一般是按照如下顺序：

MySQL会先执行 from，根据 on 关键字去筛选表，筛选出的结果再进行 join，生成一个临时表。然后去执行 where 条件去筛选临时表。然后就可以对数据进行 group by 进行分组，执行 having 对数据进行进一步筛选，然后 select 去筛选目标列了，之后需要使用 distinct 进行去重这样一个表的查询基本就结束了。然后就是对数据进行排序的 order by ，排完顺序自然就是取分页 limit了。这样就会将一个完整的SQL执行完毕了。

例：

select distinct s.id 
from T t 
join S s on t.id=s.id 
where t.name="Yrion" 
group by t.mobile 
having count(*)>2 
order by s.create_time 
limit 5;

from

第一步就是选择出from关键词后面跟的表，这也是SQL执行的第一步：表示要从数据库中执行哪张表。

实例说明：在这个例子中就是首先从数据库中找到from后的表T、表S，对两张表进行笛卡尔积，产生第一张虚拟表Temp1（注意：这里会选择相对小的表作为基础表）。

on

根据on的条件对表T表进行筛选，将筛选后的结果保存到虚拟表Temp2。

实例说明：对虚拟表Temp1进行ON筛选，然后找到表T的id和表S的id相同的部分组成成虚拟表Temp2，Temp2里面包含着表T和表S id相等的所有数据。

注意： 这里的这里如果还有第三个表与之关联，会用T2与第三个表进行笛卡尔乘积生产Temp3表，继续重复 on步骤生成Temp4表，不过下面的顺序讲解暂时不针对这里的Temp3和Temp4，只是从一个表关联查询Temp2继续说）

join

这一步主要是添加外部行，如果是左连接 left join on，那么会先遍历左表中的每一行，然后不在Temp2表中的记录将会被插入到Temp2，其余字段会置为null，形成虚拟表Temp3。如果是右连接 right join on，那么会先遍历右表中的每一行，然后不在Temp2表中的记录将会插入到Temp2，其余字段会置为null。如果是内连接 inner join on 的话，则不会添加外部行。所产生的Temp3表和Temp2表是完全相同的。

where

根据 where 后面的条件进行过滤，按照指定的字段的值（如果有and连接符会进行联合筛选）从虚拟表Temp2中筛选需要的数据，注意如果在此阶段找不到数据，会直接返回客户端，不会往下进行。这个过程会生成一个虚拟表Temp3。

注意： 在where中不可以使用聚合函数，聚合函数主要是（min\max\count\sum等函数）。

实例说明：在虚拟表Temp2集合中找到T表的name="Yrion"的数据，找到数据后会成虚拟表Temp3，Temp3里包含name列为"Yrion"的所有表数据。

group by

group by是进行分组，对where条件过滤后的虚拟表Temp3按照固定的字段进行分组，产生虚拟表Temp4，这个过程只是数据的顺序发生改变，而数据总量不会变化，表中的数据以组的形式存在。

实例说明：在虚拟表Temp3数据中对mobile进行分组，查找出mobile一样的数据，然后放到一起，产生虚拟表Temp4。

having

对虚拟表Temp4进行聚合，这里可以为 count 等计数，然后产生虚拟表Temp5，在此阶段可以使用select 中的别名。

实例说明：在虚拟表Temp4中找出条数大于2的数据，如果小于2直接被舍弃掉，然后生成虚拟表 Temp5。

select

对分组聚合完的表挑选出需要查询的数据，如果为*会解析为所有数据，此时会产生虚拟表Temp6。

实例说明：在此阶段就是对虚拟表Temp5中S表中的id进行筛选产生Temp6，此时Temp6就只包含有s表的id列数据，并且name=“Yrion”，通过mobile分组数量大于2的数据。

distinct

distinct 对所有的数据进行去重，此时如果有 min、max 函数会执行字段函数计算，然后产生虚拟表Temp7。

实例说明：此阶段对Temp5中的数据进行去重，引擎API会调用去重函数进行数据的过滤，最终只保留id第一次出现的那条数据，然后产生虚拟表Temp7。

order by

会根据Temp7进行顺序排列或者逆序排列，然后插入虚拟表Temp8，这个过程比较耗费资源。

实例说明：这段会将所有Temp7中的数据按照创建时间（create_time）进行排序，这个过程也不会有列或者行损失。

limit

limit 对虚拟表Temp8进行分页，产生虚拟表Temp9，返回给客户端。

实例说明：在Temp7中排好序的数据，然后取前五条插入到Temp9这个虚拟表中，最终返回给客户端。

4、记录日志

在执行完以后会将具体的操作记录到binlog中，需要注意的一点是：select不会记录到binlog中，只有update/delete/insert才会记录到binlog中。而update会采用两阶段提交的方式，记录都redo log中（redo log为innodb独有）。

简单讲一下 redo log 和 binlog 的区别：
redo log 是 InnoDB 引擎特有的； binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用，MySQL数据库中的任何存储引擎对于数据库的更改都会产生binlog。
redo log 是物理日志，记录的是 “ 在某个数据页上做了什么修改 ” ； binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如 “ 给 ID=2 这一行的 c 字段加 1 ”。
redo log 是循环写的（类似一个循环队列），因为它的空间固定会用完； binlog 是可以追加写入的。 “ 追加写 ” 是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
redo log 在事务执行过程中会不断的写入，而 binlog 是在事务最终提交前写入的。

六、总结

本篇博客简单讲了MySQL语句的执行过程，理解这些有助于我们对SQL语句进行优化，以及明白MySQL中的SQL语句从写出来到最终执行的轨迹，有助于我们对SQL有比较深入和细致的理解，提高我们的数据库理解能力。同时，对于复杂SQL的执行过程、编写都会有一定程度的意义。

本篇文章参考了许多博客，整合了部分内容，在此基础上做了细微改动，加了一点自己的理解。侵权必删。

本文参考链接：
MySQL执行过程
 深入理解MySQL执行过程及执行顺序
优化器部分内容转载于：MySQL优化器_MySQL查询优化器。

原文链接：https://blog.csdn.net/weixin_43844718/article/details/127323343