MySQL-SQL执行流程

在《MySQL-逻辑架构》这篇文章中说到了MySQL的逻辑架构，那么MySQL的SQL执行流程是怎么样的呢？

从MySQL的逻辑架构可以看出MySQL中的SQL执行流程可以是这样的：

MySQL查询的执行流程

1.查询缓存

因为查询缓存的效率并不高，所以在后面的版本（MySQL8.0）之后就抛弃了这个功能。

MySQL收到一个查询请求之后，会先查询缓存看看之前是否执行过这条语句。相当于将之前执行的SQL以及结果以key-value的形式缓存在内存中，key就是查询SQL，value就是查询的结果。

如果在缓存中查询到了这个SQL，那么缓存中的结果就会直接返回给客户端。
如果在缓存中没有查询到这个SQL，那么就会继续后面的执行，执行完成后，本次查询也会被保存在缓存中去。

为什么MySQL8.0之后要抛弃缓存呢？

在MySQL中缓存的不是查询计划，而是将查询的结果缓存。也就是说只有完全相同的查询操作才会命中缓存。比如两个查询SQL在任何字符上的不同（空格、注释、大小写等），都会导致缓存不能命中。所以MySQL中的缓存命中率并不高。比如下面两个SQL就是不同的，第二个SQL比第一个多出了一个空格：
```
SELECT id FROM t_user;
SELECT id FROM  t_user;
```
如果查询SQL中包含某些系统函数、用户自定义变量以及函数、系统表（比如mysql、information_schema、performance_schema库中的表），那这个SQL是不会被缓存的。

比如系统函数NOW()，每次调用都是获取最新的当前时间，想象一下，如果缓存了这个结果，那么第二次命中的时候，那应该返回什么呢？
既然是缓存，那缓存都是由失效时间的。MySQL的缓存系统会检测涉及到的每一张表，只要该表的结构或者数据被修改，比如使用了INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或者DROP DATABASE等语句，那么涉及到该表的所有缓存都将变为无效并从缓存中删除。对于更新修改较多的数据库来说，这样的缓存命中率将会非常低。

什么时候使用缓存比较好？

一般建议在静态表使用缓存，静态表一般指极少更新的表，比如系统的配置表、字典表。MySQL提供了选择按需使用缓存的方式，可以将my.cnf（配置文件）中的配置query_cache_type设置为DEMAND，表示当SQL语句中有SQL_CACHE关键词的时候才会缓存。配置如下：

# 0：关闭查询缓存
# 1：表示开启查询缓存
# 2：表示按需使用即上面说到的DEMAND
query_cache_type=2

在SQL中则这样使用：

SELECT SQL_CACHE * FROM dict WHERE id = 5

2.解析器

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

词法分析：首先MySQL需要识别出来SQL语句中的字符串分别是什么，代表什么。比如MySQL从SELECT这个关键字识别出来，这是一个查询SQL。
语法分析：根据词法分析的结果，语法解析器（比如Bison）会根据语法规则，判断这个SQL语句是否满足MySQL的语法。
1. 如果语句有问题，那么就会收到You have an error in your SQL syntax错误，比如下面这样：
```
mysql> SELECT * FRO test;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FRO test' at line 1
```
2. 如果语句是没有问题的，则会生成这样的一个语法树：

下面则是解析器分析SQL的过程和步骤：

3.优化器

在优化器中会确定SQL语句的执行路径，比如是根据全表检索还是根据索引检索等等。经过了上面的解析器，MySQL就已经知道这个SQL是做什么的了。在开始执行之前，还需要经过优化器的处理。

一条SQL可以有很多种执行方式，最后都会返回相同的结果。优化器的作用就是找到这些执行方式中最好的一个。比如：

优化器在表里面有多个索引的时候，决定使用哪个索引。
在一个SQL有多表关联（JOIN）的时候决定每个表的连接顺序。
还有表达式简化、子查询转为连接、外连接转为内连接等等。

比如下面的这个SQL中有两个表的JOIN：

SELECT * FROM t_user JOIN t_order ON t_user.id = t_order.id
WHERE t_user.username='张三' and t_order.order_no='2022013000001';

方案1：可以先从t_user表里面查询出username=='张三'的记录的ID，然后再根据ID关联到表t_order，再判断t_order中的order_no是否等于2022013000001。

方案2：可以先从t_order表里面查询出order_no='2022013000001'的记录的ID，然后再根据ID关联到表t_user，再判断t_user中的username是否等于张三。

上面两个执行方法的逻辑结果都是一样的，但是执行的效率会不同，而优化器的作用就是决定使用哪一种方案。优化器阶段完成后，这个SQL的执行方案也就确定下来了，最后进入执行器的阶段。

在优化器中又可以分为逻辑查询优化阶段以及物理查询优化阶段：

逻辑查询优化：就是通过改变SQL语句的内容来是SQL查询更加高效，同时为物理查询优化提供更多的候选执行计划。通常采用的方式则是对SQL语句进行等价变换，对查询进行重写，而查询重写的数学基础就是关系代数。对条件表达式进行等价谓词重写、条件简化，对视图进行重写，对子查询进行优化，对连接语义进行了外连接消除、嵌套连接消除等等。
物理查询优化：是基于关系代数进行的查询重写，而关系代数的每一步都对应着物理计算，这些物理计算往往存在多种算法，因此需要计算各种物理路径的代价，从中选择代价最小的最为执行计划。在这个阶段里面，对于单表和多表连接的操作，需要高效地使用索引来提升查询效率。

4.执行器

截止到这里，还没实际地读写真实的表，仅仅只是产出了一个执行计划。所以这里就进入了执行器的阶段。

在实际执行之前需要判断用户是否具备相应的权限。如果没有权限则返回错误，如果有权限则执行并返回结果（在MySQL8.0之前如果设置开启了缓存，这里还会将结果缓存起来）。

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

比如在表t_user中，id字段没有索引，那么执行器的执行流程是：

调用存储引擎的API获取这个表的第一行，判断id是不是1，如果不是则跳过，如果是则保存这行到结果集中。
调用存储引擎的API获取下一行，重复和第1步相同的判断逻辑，直到获取到最后一行。
执行器将上面所有满足条件的行组成结果集返回给客户端。

执行流程简单总结

根据上面可以得出，SQL语句在MySQL中的流程可以简单的表示为SQL语句->查询缓存->解析器->优化器->执行器：

SQL执行原理

上面的结构图很复杂，我们只需要获取最核心的部分：SQL的执行原理。不同的DBMS的SQL执行原理是相通的，只是各有各的实现路径。下面将分别从MySQL8.0以及MySQL5.7来说明MySQL的SQL执行原理。

MySQL8.0

既然一条SQL语句在MySQL服务中会经过不同的模块，那就让我们来看看在不同的模块中，SQL执行所使用的资源（时间）是怎么样的？如何在MySQL中对一条SQL语句的执行时间进行分析？

1.确认profiling是否开启

使用SELECT @@profiling;或者SHOW variables LIKE 'profiling';查看是否开启计划。开启计划可以使MySQL收集在SQL执行时所使用的资源情况，比如下面这样可以查看：

mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW variables LIKE 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

可以看到默认profiling是等于0的，也就是关闭的，如果需要打开，那么修改为1即可：

mysql> SET profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW variables LIKE 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

2.多次执行相同SQL查询

然后在这里任意执行一个SQL查询：

SELECT * FROM user

3.查看profiles

查看当前会话所产生的所有profiles：

mysql> SHOW profiles;
+----------+------------+-----------------------+
| Query_ID | Duration   | Query                 |
+----------+------------+-----------------------+
|        1 | 0.00017025 | SELECT @@profiling    |
|        2 | 0.00077750 | SHOW databases        |
|        3 | 0.00014850 | SELECT DATABASE()     |
|        4 | 0.00173225 | SHOW tables           |
|        5 | 0.00050050 | SELECT * FROM user    |
|        6 | 0.00034925 | SELECT * FROM user    |
+----------+------------+-----------------------+

4.查看profile

显示执行计划，查看程序的执行步骤，使用SHOW profile查看：

mysql> SHOW profile;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000075 |
| Executing hook on transaction  | 0.000005 |
| starting                       | 0.000007 |
| checking permissions           | 0.000005 |	//权限检查
| Opening tables                 | 0.000051 |	//打开表
| init                           | 0.000005 |	//初始化
| System lock                    | 0.000008 |	//锁系统
| optimizing                     | 0.000004 |	//优化查询
| statistics                     | 0.000015 |	//统计
| preparing                      | 0.000018 |	//准备
| executing                      | 0.000096 |	//执行
| end                            | 0.000004 |
| query end                      | 0.000003 |
| waiting for handler commit     | 0.000009 |
| closing tables                 | 0.000009 |
| freeing items                  | 0.000027 |
| cleaning up                    | 0.000010 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

也可以查询指定的Query ID，比如：

mysql> SHOW profile for QUERY 6;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000075 |
| Executing hook on transaction  | 0.000005 |
| starting                       | 0.000007 |
| checking permissions           | 0.000005 |
| Opening tables                 | 0.000051 |
| init                           | 0.000005 |
| System lock                    | 0.000008 |
| optimizing                     | 0.000004 |
| statistics                     | 0.000015 |
| preparing                      | 0.000018 |
| executing                      | 0.000096 |
| end                            | 0.000004 |
| query end                      | 0.000003 |
| waiting for handler commit     | 0.000009 |
| closing tables                 | 0.000009 |
| freeing items                  | 0.000027 |
| cleaning up                    | 0.000010 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

查询 SQL 的执行时间结果和上面是一样的。此外，还可以查询更丰富的内容：

mysql> SHOW profile cpu,block io for QUERY 6;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000075 | 0.000050 |   0.000024 |            0 |             0 |
| Executing hook on transaction  | 0.000005 | 0.000004 |   0.000000 |            0 |             0 |
| starting                       | 0.000007 | 0.000001 |   0.000006 |            0 |             0 |
| checking permissions           | 0.000005 | 0.000005 |   0.000000 |            0 |             0 |
| Opening tables                 | 0.000051 | 0.000033 |   0.000018 |            0 |             0 |
| init                           | 0.000005 | 0.000000 |   0.000005 |            0 |             0 |
| System lock                    | 0.000008 | 0.000007 |   0.000001 |            0 |             0 |
| optimizing                     | 0.000004 | 0.000004 |   0.000000 |            0 |             0 |
| statistics                     | 0.000015 | 0.000009 |   0.000006 |            0 |             0 |
| preparing                      | 0.000018 | 0.000012 |   0.000006 |            0 |             0 |
| executing                      | 0.000096 | 0.000060 |   0.000036 |            0 |             0 |
| end                            | 0.000004 | 0.000004 |   0.000000 |            0 |             0 |
| query end                      | 0.000003 | 0.000000 |   0.000003 |            0 |             0 |
| waiting for handler commit     | 0.000009 | 0.000006 |   0.000003 |            0 |             0 |
| closing tables                 | 0.000009 | 0.000003 |   0.000006 |            0 |             0 |
| freeing items                  | 0.000027 | 0.000020 |   0.000006 |            0 |             0 |
| cleaning up                    | 0.000010 | 0.000004 |   0.000006 |            0 |             0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
17 rows in set, 1 warning (0.00 sec)

MySQL5.7

在Mysql5.7中重复上面的操作，发现前后两次相同的sql语句，其执行的查询过程仍然是相同的。并没有使用到之前说的缓存，这是怎么回事呢？

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

修改配置文件，在my.cnf中新增一行：

# 0：关闭查询缓存
# 1：表示开启查询缓存
# 2：表示按需使用即上面说到的DEMAND
query_cache_type=1

这里我们直接设置全部开启即可。

重启mysql服务。

然后我们重复上面8.0的操作，分别查看两个完全相同的SQL的profile，即可看见使用到了缓存。

如果觉得文章对你有用，请随意赞赏

MySQL 学习笔记

MySQL-SQL执行流程

https://zygzyg.com/archives/993fa080c9f4496997b84956337c3d63

作者

Error

发布于

2022-08-13

更新于

2023-04-18

许可协议

CC BY 4.0

MySQL-SQL执行流程

MySQL-SQL执行流程

MySQL查询的执行流程

1.查询缓存

为什么MySQL8.0之后要抛弃缓存呢？

什么时候使用缓存比较好？

2.解析器

3.优化器

4.执行器

执行流程简单总结

SQL执行原理

MySQL8.0

1.确认profiling是否开启

2.多次执行相同SQL查询

3.查看profiles

4.查看profile

MySQL5.7

作者

发布于

更新于

许可协议

评论