MySQL 查询执行的过程

查询的生命周期大致可以按照顺序来看：从客户端到服务端，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中 “执行” 可以认为是整个生命周期中最重要的阶段，其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序分组等。当希望 MySQL 能够以高性能的方式运行查询时，最好的办法就是弄清楚 MySQL 是如何优化和执行查询的。MySQL 执行一个查询的过程，如下：

【1】首先我们会连接到这个数据库上，这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。然后客户端发送一条查询给服务器；

开始执行这条sql时，首先会校验你的用户名和密码是否正确，若是不正确会返回错误信息：Access denied for user；

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

注意：Mysql中的缓存比较适合于那些静态的表，更新不频繁的表，因为只要当前表有数据更新，关于该表的缓存就会失效，若是表更新频繁缓存频繁的失效，这样维护缓存的消耗的性能远大于使用缓存带来的性能优化，这样就会得不偿失，严重影响 Mysql的性能，所以在 Mysql 8版本中的时候把缓存这一块给砍掉了。个人感觉，没必要砍掉，可以设置成默认关闭缓存，需要的时候再设置开启，并且可以通过配置参数指定特定的表使用缓存，那些表不使用缓存，这样或许使用缓存更有效。

【3】服务器进行SQL解析、预处理、再由优化器生成对应的执行计划（这个过程中任何语法等错误都可能终止查询）。分析器主要有两步：（1）词法分析（2）语法分析；

语法解析器和预处理：MySQL 通过关键字将 SQL 语句进行解析，并生成一颗对应的 “解析树”。MySQL 解析器使用 MySQL 语法规则验证和解析查询。例如验证是否使用错误的关键字，或者使用关键字的顺序是否正确等，还会验证引号前后是否正确等。预处理器则根据一些 MySQL 规则进一步检查解析树是否合法（数据或数据列是否存在等）。预处理器会验证权限，通常很快。

【4】MySQL 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询。优化器是在表里面有多个索引的时候，决定使用哪个索引。或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序；

优化器：将语法树转化成执行计划。一条查询可以有多种执行方式，最终都返回相同的结果。优化器的作用就是找到其中最好的执行计划。MySQL 基于成本的优化器，它尝试预测一个查询使用某种执行计划的成本，并选择成本最小的一个。可以通过 SHOW STATUS LIKE ‘Last_query_cost’ 值得知 MySQL 计算的当前查询的成本。

【5】MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有权限的错误。如果有权限，将结果返回给客户端，也会将结果存放到查询缓存中；

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

【1】MySQL 客户端和服务端之间的通信协议是 “半双工”的，意味着，在任何一个时刻，要么是服务器向客户端发送数据，要么是由客户端向服务端发送数据，这两个动作不能同时发生。我们无法也无需将一个消息切成小块独立来发送。一旦一端开始发送消息，另一端要接收完整个消息才能响应它。

【2】客户端用一个单独的数据包将查询传给服务器，这也是为什么查询的语句很长的时候，参数 max_allowed_packet 就特别重要了。一旦客户端发送了请求，它能做的事情就只能等待结果了。相反的，一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果，而不能简单地只取前面几条结果，然后让服务器停止发送数据。

在 MySQL 中，max_allowed_packet 参数用于控制服务器和客户端之间通信的最大数据包大小。默认情况下，这个值可能会有所不同，具体取决于 MySQL 版本和配置。对于 MySQL 5.7 及更高版本，默认的 max_allowed_packet 值通常是 4MB（4194304 字节）。

可以通过以下命令来检查当前 MySQL 服务器的 max_allowed_packet 值：

SHOW VARIABLES LIKE 'max_allowed_packet';

如果你需要更改这个值，可以在 MySQL 配置文件（通常是 my.cnf 或 my.ini）中进行设置。例如：

[mysqld]
max_allowed_packet=64M

然后重启 MySQL 服务以使更改生效。请注意，适当调整 max_allowed_packet 的值可以帮助避免一些大数据传输时可能遇到的问题，但设置过大的值可能会增加内存使用和潜在的安全风险，因此应根据实际需求进行调整。

【3】当客户端从服务器取数据时，看起来是一个拉数据的过程，但实际上是 MySQL 在向客户端推送数据的过程。客户端不断地接收从服务器推送的数据，客户端也没法让服务器停下来。

二、查询缓存

在分析一个查询语句之前，如果查询缓存是打开的，那么 MySQL 会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。当命中缓存之后，MySQL 会检查一次用户权限。如果权限没问题，MySQL 会跳过所有其他阶段，直接从缓存中拿到结果并返回。

三、语法解析器和预处理

MySQL 通过关键字将 SQL 语句进行解析，并生成一棵对应的 “解析树”。MySQL 解析器将使用 MySQL 语法规则校验和解析查询。例如，它验证是否使用错误的关键字，或者使用关键字的顺序是否正确等，再或者它还会验证引号是否能前后正确匹配。预处理则根据一些 MySQL 规则进一步检查解析树是否合法，例如，这里检查数据表和数据列是否存在，还会解析名字和别名，看看他们是否有歧义。下一步预编译器会验证权限。这通常很快，除非服务器上有非常多的权限配置。

四、查询优化器

当语法树被认为合法时，优化器会将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同结果。优化器的作用就是找到这其中最好的执行计划。MySQL 使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。可以通过查询当前会话的 Last_query_cost 的值来得知 MySQL 计算的当前查询的成本。

SHOW STATUS LIKE 'Last_query_cost'
+-----------------+------------+
|  Variable_name  | Value      |
+-----------------+------------+
| Last_query_cost | 1030.47800 |
+-----------------+------------+

上述结果表示 MySQL 的优化器认为大概需要 1030个数据页的随机查找才能完成上述的查询。很多原因会导致 MySQL 优化器选择错误的执行计划，如下：
【1】统计信息不准确：MySQL 依赖存储引擎提供的统计信息来评估成本，但有的偏差可能非常大。例如，InnoDB 因为其 MVCC 的架构，并不能维护一个数据表的行数的精确统计信息。
【2】执行计划中的成本估算不等同实际执行的成本：所以即使统计信息精准，优化器给出的执行计划也可能不是最优的。例如某个执行计划虽然需要读取更多的页面，但是它的成本却更小。因为如果这些页面都是顺序读或者页面都已经在内存中的话，那么它的访问成本将很小。MySQL 层面并不知道哪些页面在内存中、哪些在磁盘上，所以查询实际执行过程中到底需要多少次物理 I/O 是无法得知的。
【3】MySQL 的最优可能和我们想的最优不一样：我们希望执行时间尽可能短，但是MySQL 只是基于其成本模型选择最优的执行计划，有时候并不是最快的执行方式。所以，我们根据执行成本选择执行计划并不是完美的模型。
【4】MySQL 从不考虑其他并发执行的查询：可能会影响到当前查询的速度。
【5】MySQL 也并不是任何时候都是基于成本的优化：有时也基于一些固定的规则。
【6】MySQL 不会考虑不受其控制的操作的成本：例如执行存储过程或者用户自定义函数的成本。
【7】优化器有时候无法估算所有可能的执行计划，所以它可能错过实际上最优的执行计划。

MySQL 的查询优化器使用了很多优化策略来生成一个最优的执行计划。优化策略可以简单分为两种：静态优化和动态优化。静态优化可以直接对解析树进行分析，并完成优化。动态优化则和查询的上下文有关，也可能和很多其它因素有关，例如WHERE 条件中的取值、索引中条目对应的数据行数等。这需要在每次查询的时候都重新评估，可以认为这是 “运行时优化”。下面是一些 MySQL 能够处理的优化类型：
【1】重新定义关联表的顺序：数据表的关联并不总是按照在查询中指定的顺序进行。决定关联的顺序是优化器很重要的一部分功能。
【2】使用等价变换规则：MySQL 可以使用一些等价变换来简化并规范表达式。它可以合并和减少一些比较，还可以移除一些恒成立和一些恒不成立的判断。例如：（5=5 AND a>5）将被改写为 a>5；
【3】将外连接转化成内连接：并不是所有的 OUTER JOIN 语句都必须以外连接的方式执行。
【4】优化 COUNT()、MIN() 和 MAX()：要找到某一列的最小值，只需要查询对应 B-Tree 索引最左端的记录，MySQL 可以直接获取索引的第一行记录。在优化器生成执行计划的时候就可以利用这一点，在 B-Tree 索引中，优化器会将这个表达式作为一个常数对待。
【5】预估并转化为常数表达式：当 MySQL 检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理。
【6】覆盖索引扫描：当索引中的列包含所有查询的列时，MySQL 就可以使用索引返回需要的数据，而无须查询对应的数据行。
【7】子查询优化：MySQL 在某些情况下可以将子查询转换一种效率更高的形式，从而减少多个查询多次对数据进行访问。
【8】提前终止查询：如果发现已经满足查询需求，MySQL 总是能够立刻终止查询。典型的例子就是 LIMIT 子句。
【9】列表IN() 的比较：很多数据库系统中，IN()完全等同于多个 OR 条件子句，因为这两个是完全等价的。在MySQL中不成立，IN()列表中的数据先排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个 O(logn)复杂度的操作，等价地转化成 OR 查询的复杂度为 O(n)，对于 IN() 列表有大量取值的时候，MySQL 的处理速度将会更快。

五、执行计划

和很多其他关系数据库不同，MySQL 并不会生成查询字节码来执行查询。MySQL 生成查询的一颗指令树，然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。如果对某个查询执行 EXPLAIN EXTENDED 后，再执行 SHOW WARNINGS，就可以看到重构出的查询。MySQL 总是从一个表开始一直嵌套循环、回溯完成所有表关联。所以，MySQL 的执行计划总是如下图所示：是一颗左侧深度优先的树。

explain 的 extended 扩展能够在原本 explain的基础上额外的提供一些查询优化的信息，这些信息可以通过 mysql 的 show warnings 命令得到。

六、查询执行引擎

在解析和优化阶段，MySQL 将生成查询对应的执行计划，MySQL 的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构，而不是字节码。MySQL 根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成，这些接口也就是我们称为 “handler API” 的接口。查询中的每一个表由一个 handler 的实例表示。实际上，MySQL 在优化阶段就为每个表创建了 handler 实例，优化器根据这些实例的接口可以获取表的相关信息，包括表的所有列名，索引统计信息等等。

七、返回结果给客户端

最后一个阶段是将结果返回给客户端，即使查询不需要返回结果集给客户端，MySQL 仍然会返回这个查询的一些信息，如该查询影响到的行数。如果查询可以被缓存，那么 MySQL 在这个阶段也会将结果存放到查询缓存中。MySQL 将结果集返回客户端是一个增量、逐步返回的过程。例如：关联查询操作，一旦服务器处理完最后一个关联表，开始生成第一条结果时，MySQL 就可以开始向客户端逐步返回结果集了。这样处理的好处：服务端无须存储太多的结果，也就不会因为要返回太多结果而消耗太多内存。另外，这样处理也让 MySQL客户端第一时间获得返回的结果。
结果集中的每一行都会以一个满足 MySQL 客户端/服务端通信协议的封包发送，再通过 TCP 协议进行传输，在 TCP 传输的过程中，可能对 MySQL 的封包进行缓存然后批量传输。