MySQL 查询过程_mysql 存储引擎 查询过程

转自：https://www.jianshu.com/p/d7665192aaaf

很多的查询优化工作实际上就是遵循一些原则让 MySQL 的优化器能够按照预想的合理方式运行。

MySQL 整个查询执行过程

• 客户端向 MySQL 服务器发送一条查询请求（半双工）
• 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果，否则进入下一阶段。
• 服务器进行 SQL 解析（通过语法规则来验证和解析，生成解析树）、预处理（检查要查询的数据表和数据列是否存在）、再由优化器生成最优的执行计划。
• MySQL 根据执行计划，调用存储引擎的 API 来执行查询。
• 将结果返回给客户端，同时缓存查询结果。

客户端/服务端通信协议

MySQL 的客户端/服务端通信协议是“半双工”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。

一旦一端开始发送消息，另一端要接收完整个消息才能响应它，无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。

可以设置max_allowed_packet 参数，限制客户端发来的请求数据包大小。

尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用 SELECT * 以及加上 LIMIT 限制的原因之一。

查询缓存

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

如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。

MySQL 将缓存存放在一个引用表（可以认为是类似于 HashMap 的数据结构），通过一个哈希值索引。这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。

所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。

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

既然是缓存，就会失效，那查询缓存何时失效呢？
MySQL 的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。

开启缓存带来的系统消耗：

• 在任何的写操作时，MySQL 必须将对应表的所有缓存都设置为失效。
• 任何的查询语句在开始之前都必须经过检查，即使这条 SQL 语句永远不会命中缓存。
• 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗。

并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。

如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化，比如：

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

写密集型应用，不要打开缓存。如果你实在是忍不住，可以将 query_cache_type 设置为 DEMAND。这时只有加入 SQL_CACHE 的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。

语法解析和预处理

MySQL 通过关键字将 SQL 语句进行解析，并生成一棵对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如 SQL 中是否使用了错误的关键字或者关键字的顺序是否正确等等。

预处理则会根据 MySQL 规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。

查询优化

经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。

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

在 MySQL 可以通过查询当前会话的 last_query_cost 的值来得到其计算当前查询的成本。

MySQL 认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但 MySQL 值选择它认为成本小的，但成本小并不意味着执行时间短）等等。

MySQL 的查询优化器使用了非常多的优化策略来生成一个最优的执行计划：

• 重新定义表的关联顺序（多张表关联查询时，并不一定按照 SQL 中指定的顺序进行，但有一些技巧可以指定关联顺序）。
• 优化 MIN() 和 MAX() 函数（找某列的最小值，如果该列有索引，只需要查找 B+Tree 索引最左端，反之则可以找到最大值，具体原理见下文）。
• 提前终止查询（比如：使用 LIMIT 时，查找到满足数量的结果集后会立即终止查询）。
• 优化排序（mysql4.1 之前使用双路排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，mysql4.1 之后使用单路排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于 I/O 密集型应用，效率会高很多）。

查询执行引擎

在完成解析和优化阶段以后，MySQL 会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。

整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为 handler API。

查询过程中的每一张表由一个 handler 实例表示。实际上，MySQL 在查询优化阶段就为每一张表创建了一个 handler 实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。

存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。

返回结果给客户端

即使查询不到数据，MySQL仍会返回影响行数和执行时间等信息。

如果打开了缓存，MySQL会将结果放在缓存中。

结果集返回客户端是一个增量且逐步返回的过程。有可能 MySQL 在生成第一条结果时，就开始向客户端逐步返回结果集了。
结果集中的每一行都会以一个满足①中所描述的通信协议的数据包发送，再通过 TCP 协议进行传输，在传输过程中，可能对 MySQL 的数据包进行缓存然后批量发送。