一条SQL查询语句是如何执行的

最新推荐文章于 2022-04-29 23:43:57 发布

ゞ浪人与酒丶0

最新推荐文章于 2022-04-29 23:43:57 发布

阅读量1.4k

点赞数 2

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

本文链接：https://blog.csdn.net/qq_41977838/article/details/123954967

版权

mysql 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

一条SQL查询语句是如何执行的

当希望 MySQL 能够以更高的性能查询时，最好的办法就是弄清楚 MySQL 是如何优化和执行查询的

mysql> select * from test where id = 10；

如上，MySQL 执行了一条查询的过程，MySQL到底做了什么

在这里插入图片描述

客户端发送一条查询给服务器
服务器先检查缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
服务器端进行 SQL 解析、预处理、再由优化器生成对应的执行计划
MySQL 根据优化区生成执行计划，调用存储引擎的 API 来执行查询
将结果返回给客户端

MySQL 客户端、服务器通信协议

一般来说，不需要去理解 MySQL通信协议的内部实现细节，只需要大致理解通信协议是如何工作的。MySQL 客户端和服务器之间的通信协议是 半双工 的，这意味着，在任何时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。所以，我们无法也无需将一个消息切成小块来独立发送。

这种协议让 MySQL 通信简单快速，但是也从很多地方限制了 MySQL ,一个明显的限制是没法进行流量控制。一旦一端开始发送消息，另一端要接收完整个消息才能响应它。

客户端用一个单独的数据包将查询传给服务器。一旦客户端发送了请求，他能做的事情就只是等待结果了

相反的，一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，或者接收完几条结果后就粗暴地断开连接。这也是在必要的时候一定要在查询中加上limit限制的原因。

多数连接mysql 的库函数都可以获得全部结果集并缓存到内存中，还可以逐行获取需要的数据，默认一般是获取全部结果集并缓存到内存中。mysql通常要等待所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通常可以减少服务器的压力，让查询能够早点结束，早点释放相应的资源。

当使用多数连接mysql的库函数从mysql获取数据时，其结果看起来都像是从mysql服务器获取数据，而实际上都是从这个库函数的缓存获取数据。多数情况下这没什么问题，但是如果需要返回一个很大的结果集的时候，这样做并不好，因为库函数会花很多时间和内存来存储所有的结果集。如果能够尽早开始处理这些结果集，就能大大减少内存的消耗，这种情况下可以不使用缓存来记录结果而是直接处理。这样做的缺点是，对于服务器来说，需要查询完成后才能释放资源，所以在和客户端交互的整个过程中，服务器的资源都是被这个查询所占用的。

查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么 MySQL 会优先检查者查询是否命中查询缓存中的数据，这个检查是通过一个对大小写敏感的哈希查找实现的, 之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。你可以看到，如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

解析器

如果没有命中缓存那就进行下一步将一个 SQL 转换成一个执行计划，MySQL 再依照这个执行计划和存储引擎进行交互。

首先，MySQL 通过关键字将 SQL 语句进行解析，并生成一颗对应的 解析树。 MySQL 解析器将使用 MySQL 语法规则和解析查询。例如，它将验证是否使用错误的关键字，或者使用关键字的顺序是否正确等，再或者它还会验证引号是否能前后正确匹配

mysql> elect * from test 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 'elect * from test where id=1' at line 1

一般语法错误会提示第一个出现错误的位置

预处理器

预处理器则根据一些 MySQL 规则进一步检查解析树是否合法，例如，这里将检查数据表和数据列是否存在，还会解析别名，看看它们是否有歧义。

mysql> select * from test1 where id=1;
1146 - Table 'hr.test1' doesn't exist

查询优化器

现在语法树被认为是合法的了，并且由优化器将其装换成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果，优化器的作用就是找到这其中最好的执行计划

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

mysql> select count(*) from test where c1=1;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.04 sec)

mysql> show status like 'Last_query_cost';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| Last_query_cost | 0.349000 |
+-----------------+----------+
1 row in set (0.04 sec)

MySQL 查询优化器是一个非常复杂的部件，它使用了很多优化策略来生成一个最优的执行计划。优化策略可以简单地分为2种，一种是静态优化，一种是动态优化。静态优化可以直接对解析树进行解析，完成优化，例如，优化器可以通过一些简单的代数将 where 条件转换成另一种等价形式。静态优化不依赖于特别的数值，如 where 条件中带入的一些常数等。静态优化在第一次完成后就一直有效，即使使用不同的参数重复只查询也不会发生变化。可以认为这是一种编译时优化。

相反，动态优化则和查询上下文有关，也有可能和其他因素有关，例如 where 条件取值、索引条目对应的行数等，这需要在每次查询的时候都重新评估。可以认为这是运行时优化

在执行语句和存储过程的时候，动态优化和静态优化的区别非常重要。MySQL对查询的静态优化只做一次，但对查询的动态优化则在每次执行时都需要重新评估

下面是 MySQL 能够处理的优化类型：

重新定义关联表的顺序
将外连接转化成内连接
使用等价变换规则
优化 COUNT()、MIN() 和MAX()
预估并转换为常数表达式
覆盖索引扫描
子查询优化
提前终止查询
等值传播
列表 IN() 的比较

当然，虽然优化器已经很智能了，但是有时候也无法给出最优的结果，如果能确认优化器给出的不是最佳选择，并且清楚背后的原理，那么也可以帮助优化器做进一步优化。例如，可以在查询中添加 hint 提示，也可以重写查询，调优表结构，添加合适索引

查询执行引擎

在解析和优化阶段，MySQL 将生成查询对应的执行计划，MySQL 的查询致献殷勤则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构，而不是和很多其他的关系型数据库那样会生成对应的字节码。

MySQL 根据执行计划给出的指令逐步执行，在执行过程中，有大量的操作需要通过调用存储引擎实现的接口完成，这些接口也就是我们称为 handler API 接口。查询中每一个表由一个 handler 实例表示，MySQL 在优化阶段就为每个表创建了一个 handler 实例，优化其根据这些实例的接口可以获取表的相关信息，包括列名、索引统计等