SQL语句执行流程

最新推荐文章于 2024-09-05 10:23:45 发布

朱襄夏

最新推荐文章于 2024-09-05 10:23:45 发布

阅读量1.4k

点赞数 28

分类专栏：数据库文章标签： sql 数据库

本文链接：https://blog.csdn.net/qq_43185851/article/details/135110744

版权

数据库专栏收录该内容

10 篇文章 0 订阅

订阅专栏

参考文章：https://cloud.tencent.com/developer/article/1981543

SQL语句执行流程

SQL执行步骤：请求、缓存、SQL解析、优化SQL查询、调用引擎执行，返回结果

连接：客户端向 MySQL 服务器发送一条查询请求，与connectors交互：连接池认证相关处理。
缓存：服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果，否则进入下一阶段
解析：服务器进行SQL解析(词法语法)、预处理。
优化：再由优化器生成对应的执行计划。
执行：MySQL 根据执行计划，调用存储引擎的 API来执行查询。
结果：将结果返回给客户端，同时缓存查询结果。

第1步：Connectors ：客户端/服务端通信协议

MySQL客户端/服务端通信协议是 “半双工” 的，在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置 max_allowed_packet参数，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用 SELECT * 以及加上 LIMIT 限制的原因之一
https://blog.csdn.net/gao_yu_long/article/details/74905490

具体建立连接说明：
建立与 MySQL 的连接，这就是由连接器Connectors来完成的。连接器Connectors负责跟客户端建立连接、获取权限、维持和管理连接。连接命令为： mysql -hlocalhost -P3306 -uuser−ppasswd
验证通过后，连接器会到权限表里面查出你拥有的权限，之后这个连接里面的权限判断逻辑，都将依赖于此时读到的权限，一个用户成功建立连接后，即使管理员对这个用户的权限做了修改，也不会影响已经存在连接的权限，修改完后，只有再新建的连接才会使用新的权限设置。
连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它。

系统的连接：

客户端如果太长时间没动静，连接器就会自动将它断开；这个时间是由参数 wait_timeout 控制的，默认值是8小时。如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒：Lost connection to MySQL server during query。
长连接和短连接

数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。
短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

建立连接的过程通常是比较复杂的，建议在使用中要尽量减少建立连接的动作，尽量使用长连接。但是全部使用长连接后，有时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。
两种解决方案：

定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
MySQL 5.7 以上版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection() 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

第2步：查询缓存

在解析一个查询语句前，如果查询缓存是打开的，那么 MySQL 会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。MySQL将缓存存放在一个引用表 (不要理解成table，可以认为是类似于 HashMap 的数据结构)，通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同 (例如 : 空格、注释)，都会导致缓存不会命中

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL库中的系统表，其查询结果都不会被缓存。比如函数 NOW() 或者 CURRENT_DATE() 会因为不同的查询时间，返回不同的查询结果，再比如包含 CURRENT_USER 或者 CONNECION_ID() 的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义

MySQL 查询缓存系统会跟踪查询中涉及的每个表，如果这些表 (数据或结构) 发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿，而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外 :

任何的查询语句在开始之前都必须经过检查，即使这条 SQL语句永远不会命中缓存
如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗
两个SQL语句，只要相差哪怕是一个字符（例如大小写不一样：多一个空格等），那么两个SQL将使用不同的cache。

基于此，并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，特别是写密集型应用，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。可以尝试打开查询缓存，并在数据库设计上做一些优化 :

用多个小表代替一个大表，注意不要过度设计
批量插入代替循环单条插入
合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适
可以通过 SQL_CACHE 和 SQL_NO_CACHE 来控制某个查询语句是否需要进行缓存

注 : SQL_NO_CACHE 是禁止缓存查询结果，但并不意味着 cache 不作为结果返回给 query，之前的缓存结果之后也可以查询到

mysql> SELECT SQL_CACHE COUNT(*) FROM a;
+----------+
| COUNT(*) |
+----------+
|    98304 |
+----------+
1 row in set, 1 warning (0.01 sec)mysql> SELECT SQL_NO_CACHE COUNT(*) FROM a;
+----------+
| COUNT(*) |
+----------+
|    98304 |
+----------+
1 row in set, 1 warning (0.02 sec)

可以在 SELECT 语句中指定查询缓存的选项，对于那些肯定要实时的从表中获取数据的查询，或者对于那些一天只执行一次的查询，都可以指定不进行查询缓存，使用 SQL_NO_CACHE 选项。对于那些变化不频繁的表，查询操作很固定，可以将该查询操作缓存起来，这样每次执行的时候不实际访问表和执行查询，只是从缓存获得结果，可以有效地改善查询的性能，使用 SQL_CACHE 选项

查看开启缓存的情况，可以知道query_cache_size的设置是否合理

查询服务器关于query_cache的配置

query_cache_limit：超出此大小的查询将不被缓存
query_cache_min_res_unit：缓存块的最小大小，
query_cache_min_res_unit的配置是一柄双刃剑，默认是 4KB ，设置值大对大数据查询有好处，但是如果你查询的都是小数据查询，就容易造成内存碎片和浪费。
query_cache_size：查询缓存大小（注：QC存储的单位最小是1024byte，所以如果你设定的一个不是1024的倍数的值。这个值会被四舍五入到最接近当前值的等于1024的倍数的值。）
query_cache_type：缓存类型，决定缓存什么样子的查询，注意这个值不能随便设置必须设置为数字，可选值以及说明如下：
- 0：OFF 相当于禁用了
- 1：ON 将缓存所有结果，除非你的select语句使用了SQL_NO_CACHE禁用了查询缓存
- 2：DENAND 则只缓存select语句中通过SQL_CACHE指定需要缓存的查询。
query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成在读表获取结果。

对于查询缓存的一些操作 FLUSH QUERY CACHE : 清理查询缓存内存碎片 RESET QUERY CACHE : 从查询缓存中移出所有查询 FLUSH TABLES : 关闭所有打开的表，同时该操作将会清空查询缓存中的内容。

如果查询缓存碎片率超过20%，可以用flush query cache整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。

查询缓存利用率：（query_cache_size-Qcache_free_memory)/query_cache_size*100% 查询缓存利用率在25%以下的话说明query_cache_size设置过大，可以适当减小：查询缓存利用率在80%以上而且Qcache_lowmem_prunes>50的话说明query_cache_size可能有点小，要不就是碎片太多

查询缓存命中率：Qcache_hits/(Qcache_hits+Qcache_inserts)*100%

Query Cache的限制

a）所有子查询中的外部查询SQL 不能被Cache：
b）在procedure，function以及trigger中的Query不能被Cache
c）包含其他很多每次执行可能得到不一样的结果的函数的Query不能被Cache

第3步：Analyzer分析器

如果查询缓存未命中，就要开始执行语句了。首先，MySQL 需要对 SQL 语句进行解析。

1.词法分析：
SQL语句是由多个字符串和空格组成的，MySQL 需要识别出里面的字符串分别是什么，代表什么。 MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“user_info”识别成“表名 user_info”，把字符串“id ”识别成“列 id ”

2.语法分析：
根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这SQL语句是否满足 MySQL 语法。
如果你 SQL 语句不对，就会收到 You have an error in your SQL syntax 的错误提醒，比如下面这个语句 from 写成了 form。

mysql> select * form user_info where id = 1; 
1064 - 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 'form user_info where id = 1' at line 1

一般语法错误会提示第一个出现错误的位置，所以要关注的是紧接 use near 的内容。

第4步：Optimizer优化器：查询优化

经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果,优化器的作用就是找到这其中最好的执行计划.

MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在 MySQL 可以通过查询当前会话的 last_query_cost 的值来得到其计算当前查询的成本

SELECT * FROM p_product_fee WHERE total_price BETWEEN 580000 AND 680000; 
SHOW STATUS LIKE 'last_query_cost';

显示要做多少页的随机查询才能得到最后一查询结果，这个结果是根据一些列的统计信息计算得来的，这些统计信息包括 : 每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等有非常多的原因会导致 MySQL 选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本(用户自定义函数、存储过程)、MySQL认为的最优跟我们想的不一样 (我们希望执行时间尽可能短，但 MySQL 值选择它认为成本小的，但成本小并不意味着执行时间短) 等等

MySQL的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划 :

在表里面有多个索引的时候，决定使用哪个索引；
重新定义表的关联顺序 (多张表关联查询时，并不一定按照 SQL 中指定的顺序进行，但有一些技巧可以指定关联顺序)
优化 MIN() 和 MAX()函数 (找某列的最小值，如果该列有索引，只需要查找 B+Tree索引最左端，反之则可以找到最大值)
提前终止查询 (比如 : 使用 Limit 时，查找到满足数量的结果集后会立即终止查询)
优化排序 (在老版本 MySQL 会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于I/O密集型应用，效率会高很多)

比如你执行下面这样的语句，这个语句是执行两个表的 join：

SELECT * FROM order_master JOIN order_detail USING (order_id) WHERE order_master.pay_status = 0 AND order_detail.detail_id = 1558963262141624521;

既可以先从表 order_master 里面取出 pay_status = 0 的记录的 order_id 值，再根据 order_id 值关联到表 order_detail，再判断 order_detail 里面 detail_id 的值是否等于 1558963262141624521。
也可以先从表 order_detail 里面取出 detail_id = 1558963262141624521 的记录的 order_id 值，再根据 order_id 值关联到 order_master，再判断 order_master 里面 pay_status 的值是否等于 0。
这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

第5步：查询执行引擎Actuator

在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为 handler API。查询过程中的每一张表由一个 handler 实例表示。实际上，MySQL在查询优化阶段就为每一张表创建了一个 handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作

开始执行SQL语句: select * from user_info where id = 1;

1）、判断是否有查询权限有就继续执行没有就返回权限错误。

例如判断当前连接对这个表 user_info 有没有执行查询的权限，如果没有，就会返回没有权限的错误。错误如下（如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限)。

ERROR 1142 (42000): SELECT command denied to user 'appusser'@'localhost' for table 'user_info'

2）、执行器根据表的引擎定义去调用引擎接口

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

**对于没有有索引的表使用全表扫描API：**比如我们这个例子中的表 user_info 中，id 字段没有索引，那么执行器的执行流程是这样的：

调用 InnoDB 引擎接口取这个表的第一行，判断 id 值是不是 1，如果不是则跳过，如果是则将这行存在结果集中；
调用引擎接口取下一行，重复相同的判断逻辑，直到取到这个表的最后一行。
执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

全表扫描接口：

初始化全表扫描 virtual int rnd_init (bool scan);

从表中读取下一行 virtual int rnd_next (byte* buf);

对于有索引的表，使用索引相关接口：

第一次调用读取索引第一条内容接口（ha_index_first）
之后循环取满足索引条件的下一行接口（ha_index_next）

通过索引访问table内容：

使用索引前调用该方法 int ha_foo::index_init(uint keynr, bool sorted)
使用索引后调用该方法 int ha_foo::index_end(uint keynr, bool sorted)
读取索引第一条内容 int ha_index_first(uchar * buf);
读取索引下一条内容 int ha_index_next(uchar * buf);
读取索引前一条内容 int ha_index_prev(uchar * buf);
读取索引最后一条内容 int ha_index_last(uchar * buf);
给定一个key基于索引读取内容 int index_read(uchar * buf, const uchar * key, uint key_len, enum ha_rkey_function find_flag)

数据库的慢查询日志中有 rows_examined 字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。

第6步返回结果给客户端

查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL 仍然会返回这个查询的相关信息，比如该查询影响到的行数以及执行时间等。如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。结果集返回客户端是一个增量且逐步返回的过程。有可能 MySQL 在生成第一条结果时，就开始向客户端逐步返回结果集。这样服务端就无须存储太多结果而消耗过多内存，也可以让客户端第一时间获得返回结果。需要注意的是，结果集中的每一行都会以一个满足客户端/服务器通信协议的数据包发送，再通过 TCP协议进行传输，在传输过程中，可能对 MySQL 的数据包进行缓存然后批量发送。

MySQL记录了相关实际执行数据，可以使用以下命令查询