《MySQL高级篇》二、逻辑架构分析（二）-阿里云开发者社区

《MySQL高级篇》二、逻辑架构分析（二）

2022-12-13 123

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS MySQL，集群版 2核4GB 100GB

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

RDS MySQL Serverless 高可用系列，价值2615元额度，1个月

简介： 《MySQL高级篇》二、逻辑架构分析

2.1.3 优化器

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

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

举例：如下语句是执行两个表的 join:

select * from test1 join test2 using(ID)
where test1.name='zhangwei' and test2.name='mysql高级课程';

方案1:可以先从表 test1 里面取出 name='zhangwei'的记录的 ID 值，再根据 ID 值关联到表 test2，再判断 test2 里面 name 的值是否等于 'mysql高级课程'。
方案2:可以先从表 test2 里面取出 name='mysql高级课程' 的记录的 ID 值，再根据 ID 值关联到 test1， 再判断 test1 里面 name的值是否等于 zhangwei。
这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。优化
器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。
如果你还有一些疑问，比如优化器是怎么选择索引的，有没有可能选择错等。后面讲到索引我们再谈。

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

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

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

2.1.4 执行器

截止到现在，还没有真正去读写真实的表，仅仅只是产出了一个执行计划。于是就进入了 执行器阶段 。

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

select * from test where id=1;

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

比如：表 test 中，ID 字段没有索引，那么执行器的执行流程是这样的：

调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是1，如果不是则跳过，如果是则将这行存在结果集中; 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。

执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

至此，这个语句就执行完成了。对于有索引的表，执行的逻辑也差不多。

SQL 语句在 MySQL 中的流程是：SQL 语句 → 查询缓存 → 解析器 → 优化器 → 执行器。

2.2 MySQL8中SQL执行原理

前面的结构图很复杂，我们需要抓取最核心的部分: SQL的执行原理。不同的DBMS的SQL的执行原理是相通的，只是在不同的软件中，各有各的实现路径。

既然一条SQL语句会经历不同的模块,那我们就来看下，在不同的模块中，SQL 执行所使用的资源(时间)是怎样的。如何在MySQL中对一条SQL语句的执行时间进行分析。

2.2.1. 确认profiling是否开启

了解查询语句底层执行的过程：select @@profiling; 或者show variables like '%profiling%' 查看是否开启计划。开启它可以让MySQL收集在SQL执行时所使用的资源情况，命令如下：

mysql> select @@profiling;
mysq1> show variables like 'profiling';

profiling = 0代表关闭，我们需要把profiling打开，即设置为1；

mysql> set profiling = 1;

Profiling功能由MySQL会话变量：profiling控制。默认是OFF（关闭状态）。

2.2.2.多次执行相同SQL查询

mysql> select * from employees;
mysql> select * from employees;

2.2.3.查看profiles

mysql> show profiles #查询所有sql语句的分析概览

2.2.4.查看profile

mysql> show profie;

 mysql> show profie for query Query_ID;# 查看某一次sql执行的分析过程

2.3 MySQL5.7中SQL执行原理

这里我们需要显示开启查询缓存模式。在MySQL5.7中如下设置：

2.3.1.配置文件中开启查询缓存

query_cache_type=1

2.3.2.重启Mysql服务

systemctl restart mysqld

2.3.3.开启查询计划

mysql> set profiling = 1;

2.3.4.执行语句两次

select * from departments;
select * from departments;

2.3.5.查看profiles

2.3.6.查看profile

结论不言而喻.执行编号10时,比执行编号9时少了很多信息,从截图中可以看出查询语句直接从缓存中获取数据 .

注意1：SQL必须时一致的,否则,不能命中缓存.

例如：

#虽然查询结果一致， 但并没有命中缓存。
select * from mydb . mytbl where id=2
select * from mydb . mytbl where id>1 and id<3

**注意2：**同样的开启缓存的配置信息如果在MySQL8中添加。重启服务时会报错:

[ root@atguigu01 ~]# vim /etc/my.cnf
[root@atguigu01 ~]# systemctl restart mysqld;
Job for mysqld.service failed because the control process exited with error code. See
"systemctl status mysqld. service" and "journalctl -xe" for details.

分别在MySQL5.7和MySQL8中执行如下命令：

mysql> show variables like '%query_ cache%';

MySQL5.7中显示：

MySQL8.0中显示：