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

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: MySQL中SQL生命周期与执行流程

【1】MySQL执行流程

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

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


查询缓存


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

ae6cb5ef286d4e2cbceca7aeabfc1822.pngMySQL拿到一个查询请求后,会先到查询缓存检测之前是不是执行过这条语句。之前执行过的语句及其结果可能会以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的架构图

基本组件概览

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
16天前
|
SQL 缓存 关系型数据库
MySQL技能完整学习列表6、查询优化——3、查询缓存——4、SQL优化技巧
MySQL技能完整学习列表6、查询优化——3、查询缓存——4、SQL优化技巧
28 0
|
19天前
|
SQL 关系型数据库 MySQL
MySQL - 一文解析 SQL 的执行顺序
MySQL - 一文解析 SQL 的执行顺序
|
16天前
|
SQL 关系型数据库 MySQL
MySQL技能完整学习列表3、SQL语言基础——1、SQL(Structured Query Language)简介——2、基本SQL语句:SELECT、INSERT、UPDATE、DELETE
MySQL技能完整学习列表3、SQL语言基础——1、SQL(Structured Query Language)简介——2、基本SQL语句:SELECT、INSERT、UPDATE、DELETE
33 0
|
2天前
|
SQL 缓存 关系型数据库
一文搞懂MySQL中一条SQL语句是如何执行的
一文搞懂MySQL中一条SQL语句是如何执行的
8 0
|
6天前
|
SQL 存储 关系型数据库
MySQL索引原理以及SQL优化
MySQL索引原理以及SQL优化
38 0
|
16天前
|
SQL 安全 关系型数据库
MySQL技能完整学习列表3、SQL语言基础——3、SQL运算符和函数
MySQL技能完整学习列表3、SQL语言基础——3、SQL运算符和函数
22 0
|
17天前
|
SQL 关系型数据库 MySQL
Mysql SQL的一些特殊用法记录
1、查询group by having 中having不起作用,及解决
11 0
|
18天前
|
SQL 存储 关系型数据库
【MySQL】七种SQL优化方式 你知道几条
【MySQL】七种SQL优化方式 你知道几条
28 0
|
19天前
|
SQL 存储 缓存
MySQL - 一条SQL在MySQL中是如何被执行的?
MySQL - 一条SQL在MySQL中是如何被执行的?
|
19天前
|
SQL 关系型数据库 MySQL
MySQL日期函数的SQL代码示例和使用场景
MySQL日期函数的SQL代码示例和使用场景
14 0