MySQL中SQL生命周期与执行流程

【1】MySQL执行流程

从MySQL的逻辑架构来看，如下图所示。

这里首先我们分析一下查询缓存，其在MySQL8中已经被抛弃。

① 查询缓存

Server如果在查询缓存中发现了这条SQL语句，就会直接将结果返回给客户端。如果没有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在MySQL8.0之后就抛弃了这个功能。

MySQL拿到一个查询请求后，会先到查询缓存检测之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式被直接缓存在内存中。key是查询的语句，value是查询的结果。如果你的查询能够直接在这个缓存中找到key，那么这个value就会被直接返回给客户端。如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。所以，如果查询命中缓存，MySQL不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

大多数情况查询缓存就是个鸡肋，为什么呢？

查询缓存是提前把查询结果缓存起来，这样下次不需要执行就可以直接拿到结果。需要说明的是，在MySQL中的查询缓存，不是缓存查询计划，而是查询对应的结果。这就意味着查询匹配的命中率大大降低，只有相同的查询操作才会命中查询缓存。两个查询请求再任何字符上的不同(例如：空格、注释、大小写)，都会导致缓存不会命中。

同时，如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表如mysql、information_schema、performance_schema数据库中的表，那这个请求就不会被缓存。以某些系统函数举例，可能同样的函数的两次调用会产生不一样的结果，比如函数NOW，每次调用都会产生最新的当前时间。如果在一个查询请求中调用了这个函数，那即使查询请求的文本信息都一样，不同时间的两次查询也应该得到不同的结果。如果在第一次查询时就缓存了，那第二次查询的时候直接使用第一次查询的结果就是错误的！

此外，既然是缓存，那就有它缓存失效的时间。MySQL的缓存系统会监测涉及到的每张表，只要该表的结构或者数据被修改，如对该表使用了insert、update、delete、truncate table、alter table、drop table或drop database语句，那使用该表的所有高速缓存查询都将变为无效并从查询缓存中删除。对于更新压力大的数据库来说，查询缓存的命中率会非常低！

总之，查询缓存往往弊大于利，查询缓存的失效非常频繁。

一般建议大家在静态表里使用查询缓存，什么叫静态表呢？就是一般我们极少更新的表。比如一个系统配置表、字典表，这张表上的查询才适合使用查询缓存。好在MySQL也提供了这种按需使用的方式。可以将my.cnf参数query_cache_type设置成DEMAND,代表当SQL语句中有SQL_CACHE关键词时才缓存。比如：

# 0:关闭查询缓存 OFF；1：开启ON；2 ：DEMAND
query_cache_type=2

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

select SQL_CACHE * from test where ID=5;

MySQL5.7下可以使用如下命令查看缓存开启状态（MySQL8下是查不到这个变量的）：

show variables like '%query_cache_type%'

查询缓存的相关监控：

show status like '%Qcache%';

# 本文mysql5.7结果
# 查询缓存中还有多少剩余的blocks
Qcache_free_blocks	1
#查询缓存的内存大小
Qcache_free_memory	1031872
#多少次命中缓存
Qcache_hits	0
#多少次未命中后插入
Qcache_inserts	0
# 记录有多少条查询因为内存不足而被移除出查询缓存
Qcache_lowmem_prunes	0
#  没有被缓存的查询数量
Qcache_not_cached	357486
# 当前缓存中缓存的查询数量
Qcache_queries_in_cache	0
# 当前缓存的block数量
Qcache_total_blocks	1

② 解析器

在解析器中对SQL语句进行语法分析、语义分析。

如果没有命中查询缓存，就要开始真正执行语句了。首先MySQL需要知道你要做什么，因此需要对SQL语句做解析。SQL语句的分析分为词法分析和语义分析。

分析器先做“词法分析”。你输入的是由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串分别是什么、代表什么。

接着要做“语法”分析。根据词法分析的结果，语法分析器(比如：Bison)会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。

如果你的语句不对，就会收到"You have an error in your SQL syntax"的错误提醒。

如果SQL语句正确，则会生成一个这样的语法树：

下图是SQL词法分析的过程步骤：

至此我们解析器的工作任务也基本圆满了，接下来进入到优化器。

③ 优化器

在优化器中会确定SQL语句的执行路径（执行计划），比如是根据全表检索还是根据索引检索等。

经过了解析器，MySQL就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

比如表里面有多个索引的时候，决定使用哪个索引。或者在一个语句有多表关联（join）的时候决定各个表的连接顺序，还有表达式简化、子查询转为连接、外连接转为内连接等。

在查询优化器中，可以分为逻辑查询优化阶段和物理查询优化阶段。

逻辑查询优化就是通过改变SQL语句的内容来使得SQL查询更高效，同时为物理查询优化提供更多的候选执行计划。通常采用的方式是对SQL语句进行等价变换，对查询进行重写，而查询重写的数学基础就是关系代数。对条件表达式进行等价谓词重写、条件简化，对视图进行重写，对子查询进行优化，对连接语义进行了外连接消除、嵌套连接消除等。

物理查询优化是基于关系代数进行的查询重写，而关系代数的每一步都对应着物理计算，这些物理计算往往存在多种算法，因此需要计算各种物理路径的代价，从中选择代价最小的作为执行计划。在这个阶段，对于单表和多表连接的操作，需要高效地使用索引，提升查询效率。

④ 执行器

截止到现在，还没有真正去读写真实的表，仅仅只是产生了一个执行计划。

在执行之前需要判断该用户是否具备权限。如果没有，就会返回权限错误。如果具备权限，就执行SQL查询并返回结果。在MySQL8.0以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，调用存储引擎API对表进行读写。存储引擎API只是抽象接口，下面还有个存储引擎层，具体实现还是要看表选择的存储引擎。

SQL语句在MySQL中的流程可归纳为：SQL语句--查询缓存--解析器--优化器--执行器 。

【2】SQL语法顺序

常见查询SQL实例如下：

# 123456789表示MySQL自己认为的次序
(7)     SELECT   
(8)     DISTINCT <select_list>  
(1)     FROM <left_table>  
(3)     <join_type> JOIN <right_table>  
(2)     ON <join_condition>  
(4)     WHERE <where_condition>  
(5)     GROUP BY <group_by_list>  
(6)     HAVING <having_condition>  
(9)     ORDER BY <order_by_condition>  
(10)    LIMIT <limit_number>  

总结如下图所示，由上到下由左到右。

【3】Oracle中的SQL执行流程

Oracle中采用了共享池来判断SQL语句是否存在缓存和执行计划，通过这一步骤我们可以知道应该采用硬解析还是软解析。

我们先来看下SQL再Oracle中的执行过程：

从上面这张图可以看出，SQL语句在Oracle中经历了以下几个步骤。

语法检查：检查SQL拼写是否正确，如果不正确就报语法错误。

语义检查：检查SQL中的访问对象是否存在，比如select语句中的列名错误。语法检查和语义检查的作用是保证SQL语句没有错误。

权限检查：看用户是否具备访问该数据的权限。

共享池检查：共享池（Shared Pool）是一块内存池，最主要的作用是缓存SQL语句和该语句的执行计划。

Oracle通过检查共享池是否存在SQL语句的执行计划，来判断进行软解析还是硬解析。

那什么是软解析和硬解析？

在共享池中，Oracle首先对SQL语句进行Hash运算，然后根据Hash值在库缓存(Library Cache)中查找，如果存在SQL语句的执行计划，就直接拿来执行，直接进入“执行器”的环节，这就是软解析。

如果没有找到SQL语句和执行计划，Oracle就需要创建解析树进行解析，生成执行计划，进入“优化器”这个步骤，这就是硬解析。

优化器 ：优化器中就是要进行硬解析，也就是决定怎么做。比如创建解析树，生成执行计划。

执行器：当有了解析树和执行计划之后，就知道了SQL该怎么被执行，这样就可以在执行器中执行语句了。

共享池是Oracle中的术语，包括了库缓存，数据字典缓冲区等。我们上面已经讲到了库缓存区，它主要缓存SQL语句和执行计划。而数据字典缓冲区存储的是Oracle中的对象定义，比如表、视图、索引等对象。当对SQL语句进行解析的时候，如果需要相关的数据，会从数据字典缓冲区中提取。

库缓存这一个步骤，决定了SQL语句是否需要进行硬解析。为了提升SQL的执行效率，我们应该尽量避免硬解析，因为在SQL的执行过程中，创建解析树，生成执行计划是很消耗资源的。

你可能会问，如何避免硬解析，尽量使用软解析呢？

在Oracle中，绑定变量是它的一大特色。绑定变量就是在SQL语句中使用变量，通过不同的变量取值来改变SQL的执行结果。这样做的好处是能提升软解析的可能性，不足之处在于可能会导致生成的执行计划不够优化，因此是否需要绑定变量还需要视情况而定。

举个例子，我们可以使用下面的查询语句：

select * from player where player_id=10001;

你也可以使用绑定变量，如：

select * from player where player_id= :player_id ;

这两个查询语句的效率在Oracle中是完全不同的。如果你在查询player_id=10001之后，还会查询10002,10003之类的数据，那么每一次查询都会创建一个新的查询解析。而第二种方式使用了绑定变量，那么在第一次查询之后，在共享池中就会存在这类查询的执行计划，也就是软解析。

因此我们可以通过使用绑定变量来减少硬解析，减少Oracle的解析工作量。但是这种方式也有缺点，使用动态SQL的方式，因为参数不同，会导致SQL的执行效率不同，同时SQL优化也会比较困难。

Oracle的架构图

基本组件概览