Mysql执行过程

Mysql 执行流程

https://tc.ctq6.cn/tc/mysql%E6%89%A7%E8%A1%8C%E8%BF%87%E7%A8%8B%20%282%29.jpg

大致流程描述:

• MySQL客户端通过协议将SQL语句发送给MySQL服务器。
• 服务器会先检查查询缓存中是否有执行过这条SQL，如果命中缓存，则将结果返回，否则进入下一个环节（查询缓存默认不开启）。
• 服务器端进行SQL解析，预处理，然后由查询优化器生成对应的执行计划。
• 服务器根据查询优化器给出的执行计划，再调用存储引擎的API执行查询。
• 将结果返回给客户端，如果开启查询缓存，则会备份一份到查询缓存中。

流程图详解

连接器

连接器的主要功能如下：

• 负责与客户端的通信,是半双工模式,这就意味着某一固定时刻只能由客户端向服务器请求或者服务器向客户端发送数据,而不能同时进行,其中mysql在与客户端连接TC/IP的
• 验证请求用户的账户和密码是否正确,如果账户和密码错误,会报错:Access denied for user ‘root’@‘localhost’ (using password: YES)
• 如果用户的账户和密码验证通过,会在mysql自带的权限表中查询当前用户的权限

mysql中存在4个控制权限的表，分别为user表，db表，tables_priv表，columns_priv表, mysql权限表的验证过程为：

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

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

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

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

Procs_priv表：存放存储过程和函数级别的权限

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、如果在任何一个过程中权限验证不通过,都会报错

查询缓存

mysql的缓存主要的作用是为了提升查询的效率，缓存以key和value的哈希表形式存储，key是具体的sql语句，value是结果的集合。如果无法命中缓存,就继续走到分析器的下一步,如果查询命中该缓存时，MySQL会立刻返回结果，跳过了解析、优化和执行阶段 。

不过需要注意的是在mysql的8.0版本以后，缓存被官方删除掉了。之所以删除掉,是因为要求SQL和参数都是一样，同时查询缓存系统会跟踪查询中涉及的每一个表，如果这些表发生变化，则该表相关的所有缓存数据均会失效，查询缓存的失效非常频繁, 如果在一个写多读少的环境中,缓存会频繁的新增和失效。

对于某些更新压力大的数据库来说，查询缓存的命中率会非常低,mysql为了维护缓存可能会出现一定的伸缩性的问题,目前在5.6的版本中已经默认关闭了，比较推荐的一种做法是将缓存放在客户端，性能大概会提升5倍左右。

分析器

分析器的主要作用是将客户端发过来的sql语句进行分析，这将包括预处理与解析过程，在这个阶段会解析sql语句的语义，并进行关键词和非关键词进行提取、解析，并组成一个解析树。

得到解析数之后，还需要做预处理，预处理则进一步检查解释树是否合法，以及进行一些优化，比如检查数据表和列是否存在，如果有计算，会将计算的结果算出来等等。

具体的关键词包括不限定于以下：

select/update/delete/or/in/where/group by/having/count/limit等, 如果分析到语法错误，会直接给客户端抛出异常:ERROR:You have an error in your SQL syntax.

比如：select * from user where userId =1234;

在分析器中就通过语义规则器将select from where这些关键词提取和匹配出来, mysql会自动判断关键词和非关键词，将用户的匹配字段和自定义语句识别出来。

这个阶段也会做一些校验:

比如校验当前数据库是否存在user表，同时假如User表中不存在userId这个字段同样会报错：unknown column in field list.

查询优化器

能够进入到优化器阶段表示sql是符合mysql的标准语义规则的并且可以执行的，此阶段主要是进行sql语句的优化，会根据执行计划进行最优的选择,匹配合适的索引,选择最佳的执行方案。

比如一个典型的例子是这样的：

表T,对A、B、C列建立联合索引，在进行查询的时候，当sql查询到的结果是:select xx where B=x and A=x and C=x. 很多人会以为是用不到索引的，但其实会用到,虽然索引必须符合最左原则才能使用,但是本质上,优化器会自动将这条sql优化为:where A=x and B=x and C=X, 这种优化会为了底层能够匹配到索引，同时在这个阶段是自动按照执行计划进行预处理,mysql会计算各个执行方法的最佳时间,最终确定一条执行的sql交给最后的执行器

查询优化器是整个流程中重要的一环。查询优化器会将预处理之后的解析树转化成执行计划。

一条查询可以有多种执行方法，最后均会返回相同结果。

查询优化器的作用就是找到这其中最好的执行计划。

生成执行计划的过程会消耗较多的时间，特别是存在许多可选的执行计划时。

如果在一条SQL语句执行的过程中将该语句对应的最终执行计划进行缓存，当相似的语句再次被输入服务器时，就可以直接使用已缓存的执行计划，从而跳过SQL语句生成执行计划的整个过程，进而可以提高语句的执行速度。

通常所讲的优化SQL，其实就是想让查询优化器，按照我们的思路,帮我们选择最优的执行方案。

查询执行计划

在执行器的阶段,此时会调用存储引擎的API,API会调用存储引擎，主要有一下存储的引擎，不过常用的还是myisam和innodb:

https://tc.ctq6.cn/tc/执行器.png

引擎以前的名字叫做:表处理器负责对具体的数据文件进行操作,对sql的语义比如select或者update进行分析,执行具体的操作。

在执行完以后会将具体的操作记录到binlog中,需要注意的一点是:select不会记录到binlog中,只有update/delete/insert才会记录到binlog中。

而update会采用两阶段提交的方式,记录都redolog中

查询执行计划，就是MySQL查询中的执行计划，比如是执行where语句还是from语句，最先执行的总是FROM操作，最后执行的是LIMIT操作。其中每一个操作都会产生一张虚拟的表，这个虚拟的表作为一个处理的输入，只是这些虚拟的表对用户来说是透明的，但是只有最后一个虚拟的表才会被作为结果返回。

如果没有在语句中指定某一个子句，那么将会跳过相应的步骤。

执行的状态

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

囊括了所有的mysql的所有状态,其中具体的含义如下图：

https://tc.ctq6.cn/tc/执行状态.png

执行顺序

事实上,sql并不是按照我们的书写顺序来从前往后、左往右依次执行的,它是按照固定的顺序解析的,主要的作用就是从上一个阶段的执行返回结果来提供给下一阶段使用，sql在执行的过程中会有不同的临时中间表，一般是按照如下顺序：

https://tc.ctq6.cn/tc/执行顺序.png

• FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
• ON: 对虚表VT1进行ON筛选，只有那些符合的行才会被记录在虚表VT2中。
• JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3， 如果 from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。
• WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。
• GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.
• CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6.
• HAVING： 对虚拟表VT6应用having过滤，只有符合的记录才会被 插入到虚拟表VT7中。
• SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中。
• DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9.
• ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10.
• LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回。

例子: 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执行的第一步:表示要从数据库中执行哪张表。 实例说明:在这个例子中就是首先从数据库中找到表T
• join on join是表示要关联的表，on是连接的条件。通过from和join on选择出需要执行的数据库表T和S,产生笛卡尔积,生成T和S合并的临时中间表Temp1。 on:确定表的绑定关系,通过on产生临时中间表Temp2. 实例说明：找到表S,生成临时中间表Temp1,然后找到表T的id和S的id相同的部分组成成表Temp2,Temp2里面包含着T和Sid相等的所有数据
• where 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这个临时表中，最终返回给客户端

查询执行引擎

执行计划会传给查询执行引擎，执行引擎选择存储引擎来执行计划，到磁盘中的文件中去查询。 影响这个查询性能最根本的原因是什么? 其实是硬盘的机械运动，也就是我们平时熟悉的IO，所以一条查询语句是快还是慢，就是根据这个时间的IO来确定的。那怎么执行IO又是什么来确定的?就是传过来的这一份执行计划.