MySQL – 查询执行的基础

本是古典何须时尚 2022-06-01 06:16 224阅读 0赞

MySQL – 查询执行的基础

我们在平时工作中，会需要对所使用的sql语句进行优化，这就需要我们搞清楚MySQL是如何优化和执行查询的。

当向MySQL发送一个请求的时候，MySQL到底做了些什么：

![SouthEast][]

1.      客户端发送一条查询给服务器。

2.      服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。

3.      服务器端进行SQL解析，预处理，再由优化器生成对应的执行计划。

4.      MySQL根据优化器生成的执行计划，调用存储引擎的API执行查询。

5.      返回结果给客户端。

**1.MySQL客户端/服务器通信协议**

** **

MySQL客户端和服务器之间的通信协议是“半双工”的，在任何一个时刻，要么是由服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时执行。所以，我们无法也无须将也消息切成小块独立来发送。

这种协议让MySQL通信简单快速，但是也从很多地方限制了MySQL。一个明显的限制是：无法进行流程控制。一旦一端开始发送消息，另一端要接受完整个消息才能响应它。这就像来回抛球的游戏：在任何时刻，只有一个人能控制球，而且只有控球的人才能将球发回去（发送消息）。

客户端用一个单独的数据包将查询传给服务器。这也是为什么当查询的语句很长的时候，参数max\_allowed\_packet就特别重要了。一旦客户端发送可请求，它能做的就只有等待结果了。

相反的，一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整地接受整个返回结果，而不能简单地只取前面几条结果，然后让服务器停止发送。这种情况下，客户端若接收完整的结果，然后取前面几条需要的结果，或者接受完几条结果后就断开连接，都不是好主意。这就是为什么在必要的时候一定要在查询中加上LIMIT限制的原因。

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

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

**查询状态**

对于一个MySQL连接，或者说一个线程，任何时刻都有一个状态，该状态表示了MySQL当前正在做什么。有很多种方式能查看当前的状态，最简单的是使用SHOW FULL PROCESSLIST命令（该命令返回结果中的Command列就表示当前的状态）。在一个查询的生命周期中，状态会变化很多次。MySQL官方手册对这些状态值的含义最权威的解释:

Sleep:线程正在等待客户端发送新的请求。

Query:线程正在执行查询或者正在将结果发送给客户端。

Locked:在MySQL服务器层，该线程在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会出现在线程状态中。

Analyzing and statistics:线程正在收集存储引擎统计信息，并生成查询的执行计划。

Copying to tmp table \[on disk\]:线程正在执行查询，并且将其结果集都复制到一个临时表中，这种状态一般要么是在做GROUP BY操作，要么是文件排序操作，或者是UNION操作。如果这个状态后面还有“on disk”标记，那表示MySQL正在将一个内存临时表放到磁盘上。

Sorting result:线程正在对结果集进行排序。

Sending data:这表示多种情况：线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。

了解这些状态的基本含义非常有用，这可以让你很快地了解当前“谁正在持球”。

**2.查询缓存**

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。

![SouthEast 1][]

**3.查询优化处理**

查询的生命周期的下一步是将一个SQL转换成一个执行计划，MySQL再依照这个执行计划和存储引擎进行交互。这包括多个子阶段：

![SouthEast 2][]

***语法解析器和预处理***

首先，MySQL通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”

***查询优化器***

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

MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。

有很多原因会导致MySQL优化器会选择错误执行计划。

1.      统计信息不准确。

2.      执行计划中的成本估算不等同于实际执行的成本。

3.      MySQL的最优可能和你想的不一样。

4.      MySQL从不考虑其他并发执行的查询。

5.      MySQL也并不是任何时候都是基于成本的优化。

6.      MySQL不会考虑不受其控制的操作的成本。

7.      优化器有时候无法去估算所有可能的执行计划，所以它可能错过实际上最优的执行计划。

优化策略可以简单的分成两种：静态优化和动态优化

静态优化：直接对解析树进行分析，并完成优化。静态优化不依赖于特别的数值。静态优化在第一次完成后就一直有效，即使使用不同的参数重复执行查询也不会发送变化。可以认为这是一种“编译时优化”。

动态优化：和查询的上下文有关，也可能和其他很多因素有关。这需要在每次查询的时候都重新评估，可以认为这是“运行时优化”。

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

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

1.      重新定义关联表的顺序：数据表的关联并不总是按照在查询中指定的顺序进行。

2.      将外连接转换成内连接：并不是所有的OUTER JOIN语句都必须以外连接的方式执行。

3.      使用等价变换规则：可以合并和减少一些比较，移除一些恒成立和恒不成立的判断

4.      利用索引优化COUNT(),MIN和MAX()

5.      预估并转换为常数表达式：当检测到一个表达式可以转换为常熟的时候，会一直把该表达式作为常数优化处理。

6.      覆盖索引扫描：当索引中的列包含所有查询中需要使用的列的时候，可以使用索引返回需要的数据，而无需查询对应的数据行。

7.      子查询优化：某些情况下，可以将子查询转换成一种效率更高的形式，从而减少多个查询多次对数据进行访问。

8.      提前终止查询：当发现已经满足查询需求的时候，总是能够立刻终止查询。例如使用LIMIT子句。还有几种情况也会提前终止查询，例如发现一个不成立的条件等。

9.      等值传播：如果两个列的值通过等式关联，那么MySQL能够把其中一个列的WHERE条件传递到另一列上。

10.  列表in()的比较：将IN（）列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件。

***执行关联查询***

MySQL认为任何一个查询都是一次“关联”。所以，理解MySQL如何执行关联查询至关重要。

以UNION查询为例：MySQL先将一系列的单个查询结果放到一个临时表中，然后再重新独处临时表数据来完成UNION查询。在MySQL的概念中，每个查询都是一次关联，所以读取结果临时表也是一次关联。

MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中查找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。MySQL会尝试在最后一个关联表中找到所有匹配的行，如果最后一个关联表无法找到更多的行以后，MySQL返回到上一层次关联表，看是否能够找到更多的匹配记录，依次类推迭代执行。

我们来看一个例子：select tbl1.col1, tbl2.col2 from tbl1 inner join tbl2 using(col3)where tbl1.col1 in (5,6);

如下图的泳道图：根据优化器执行的路径绘制可视化查询计划

![SouthEast 3][]

从本质上说，MySQL对所有的类型的查询都以同样的方式运行。例如，MySQL在FROM子句中遇到子查询时，先执行子查询并将其结果放到一个临时表中，然后将这个临时表当做一个普通表对待。MySQL在执行UNION查询时也使用类似的临时表，在遇到右外连接时，将其改写成等价的左外连接。

***执行计划***

MySQL生成查询的一颗指令树，然后通过存储引擎执行完成这可指令树并返回结果。

如果对某个查询执行EXPLAN EXTENDED后，再执行SHOW WARNINGS，就可以看到重构出的查询。任何多表查询都可以使用一棵树表示：

![SouthEast 4][]

这种被称为一颗平衡树，但是，这并不是MySQL执行查询的方式。MySQL总是从一个表开始一直嵌套循环，回溯完成所有表关联。所以MySQL总是如下所示，是一颗左测深度优先的树：

![SouthEast 5][]

***关联查询优化器***

MySQL优化器最重要的一部分就是关联查询优化，它决定了多个表关联时的顺序。通过多表关联的时候，可以有多种不同的关联顺序来获得相同的执行结果。关联查询优化器通过评估不同顺序时的成本来选择一个代价最小的关联顺序。

***排序优化***

无论如何排序都是一个成本很高的操作，所以，应尽可能避免排序或者尽可能避免对大量数据进行排序。当不能使用索引生成排序结果的时候，MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘。

**4.查询执行引擎**

在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构。

这个过程并不是那么复杂，MySQL只是简单地根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成，这些接口也就是我们称为“handler API”的接口。查询中的每一个表由一个handler的实例表示。

**5.返回结果给客户端**

查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果集给客户端，MySQL仍然会返回这个查询的一些信息，如该查询影响到的行数。

如果查询可以被缓存，那么MySQL在这个阶段也会将结果存放到查询缓存中。

MySQL将结果集返回客户端是一个增量，逐步返回的过程。结果集中的每一行都会以一个满足通信协议的封包发送，再通过TCP协议进行传输，在传输过程中，可能对MySQL的风暴进行缓存然后批量传输。

[SouthEast]: /images/20220601/327d021946ae4fba991e9474469cdd19.png
[SouthEast 1]: /images/20220601/ce5f33095e3b485587804b9e23feabe9.png
[SouthEast 2]: /images/20220601/60ee301364f148c8bfa21c9b96b5df26.png
[SouthEast 3]: /images/20220601/17185d1074994fdd8793ff44ec642a7a.png
[SouthEast 4]: /images/20220601/99ee3440310044a189a7365ef5e85ed4.png
[SouthEast 5]: /images/20220601/2c6090fceea64b97a65048a08a3baa54.png