1. 前言

以下对MySQL执行查询语句的主要步骤进行分析，未涉及连接优化、排序优化等内容。

2. MySQL服务器架构

在了解MySQL如何执行查询语句前，首先需要了解MySQL服务器（MySQL Server）架构。

MySQL服务器架构示意图如下所示：

图片来源： https://www.oracle.com/technetwork/articles/javase/figure2-large-145676.jpg 。

mysql动态查询输出 mysql执行查询语句_数据库

图片来源： 《High Performance MySQL, 3rd Edition》 。

mysql动态查询输出 mysql执行查询语句_mysql动态查询输出_02

MySQL模块总览示意图如下所示：

图片来源： 《Understanding MySQL Internals》 。

mysql动态查询输出 mysql执行查询语句_mysql动态查询输出_03

以上示意图中均未显示查询执行引擎（Query Execution Engine），在后续内容中有对其说明。

2.1. MySQL服务器层与存储引擎关系

参考 https://dev.mysql.com/doc/refman/5.6/en/storage-engines.html 。

存储引擎（Storage engine）是用于处理不同表类型的SQL操作的MySQL组件。InnoDB是默认的也是最通用的存储引擎。

MySQL服务器使用可插拔的存储引擎架构，使存储引擎可以在运行的MySQL服务器加载或卸载。

参考 https://dev.mysql.com/doc/refman/5.6/en/pluggable-storage-common-layer.html 。

MySQL可插拔存储引擎是MySQL数据库服务器中的组件， 负责执行数据库的实际数据I/O操作 ，以及启用和实施针对特定应用程序需求的某些功能集。

参考 https://dev.mysql.com/doc/refman/5.6/en/mysqld.html 。

mysqld，也称为MySQL服务器，是在MySQL设施中完成大部分工作的主程序。MySQL服务器对包含数据库和表的MySQL数据目录的访问进行管理。数据目录也是保存其他信息的默认位置（例如日志文件和状态文件）。

当MySQL服务器启动时，会监听来自客户端程序的网络连接，并代表这些客户端管理对数据库的访问。

MySQL服务器还具有一组系统变量，这些变量会在MySQL服务器运行时影响其操作。MySQL服务器还具有一组状态变量，这些变量提供有关MySQL服务器操作的信息。

3. MySQL查询语句执行过程

以下内容参考 《High Performance MySQL, 3rd Edition》。

向MySQL（服务器）发送查询语句时，MySQL按照以下步骤执行：

  • （MySQL）客户端将SQL语句发送到（MySQL）服务器；
  • 服务器检查查询缓存。如果命中，从缓存中返回存储的结果；否则，将SQL语句传递到下一步；
  • 服务器对SQL进行解析、预处理，并优化SQL为查询执行计划；
  • 查询执行引擎通过调用存储引擎API来执行计划；
  • 服务器将结果发送给客户端。

查询语句的执行步骤如下图所示：

mysql动态查询输出 mysql执行查询语句_数据库_04

可以看到查询语句执行的步骤中，会涉及以下内容：

  • MySQL客户端/服务器协议
  • 查询缓存
  • 解析器与预处理器
  • 优化器
  • 查询执行引擎
  • 返回结果给客户端

3.1. MySQL客户端/服务器协议

MySQL客户端/服务器协议（The MySQL Client/Server Protocol）是半双工的，这意味着在任何指定的时间，MySQL服务器都可以发送或接收消息，但不能同时发送或接收消息。这也意味着没有办法缩短消息。

以上协议使MySQL通信变得简单而快速，但在某些方面也存在限制。这意味着没有流量控制；一旦一方发送了一条消息，另一方在进行响应之前必须获取整个消息。

3.2. 查询缓存

在解析查询之前，如果已启用查询缓存（Query Cache），MySQL会在查询缓存中检查查询。该操作是区分大小写的哈希查找。如果该查询与缓存中的类似查询不同，即使只相差一个字节，也不会匹配，查询处理将进入下一阶段。

参考 https://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/ 。

从MySQL 8.0开始，不再支持查询缓存。

尽管MySQL Query Cache旨在提高性能，但它具有严重的可扩展性问题，并且很容易成为严重的瓶颈。

从MySQL 5.6起，查询缓存已默认被禁用，因为众所周知它无法在多核计算机上随着高吞吐量工作负载进行扩展。

假设可扩展性能够被提高，那么查询缓存的限制因素在于，由于只有命中缓存的查询才会得到改善；它不太可能提高性能的可预测性。对于面向用户的系统，降低性能差异通常比提高吞吐量峰值更重要。

当缓存靠近客户端时，缓存可以带来最大的好处。

参考 https://dev.mysql.com/doc/refman/5.6/en/query-cache-in-select.html ，在查询语句中指定SQL_CACHE，当查询缓存可用时，该查询结果可以被缓存；在查询语句中指定SQL_NO_CACHE，MySQL服务器不会使用查询缓存。

参考 https://dev.mysql.com/doc/refman/5.6/en/query-cache-configuration.html 、
https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html 。

查询缓存是否开启由系统变量query_cache_size与query_cache_type决定。当query_cache_size为0，或query_cache_type为关闭时，查询缓存会关闭。

query_cache_size默认值为1M，query_cache_type默认为关闭。查询缓存默认关闭。

参考 https://mariadb.com/kb/en/server-system-variables 。

在MariaDB中，大于等于10.1.7版本的query_cache_size默认值为1M，query_cache_type默认为关闭；小于等于10.1.6版本的query_cache_size默认值为0，query_cache_type默认为启用。即查询缓存默认关闭。

3.3. 查询优化过程

在查询步骤的下一步，会将SQL查询转换为查询执行引擎（query execution engine）处理的执行计划（execution plan）。包含几个子步骤：解析，预处理和优化。

3.3.1. 解析器与预处理器

首先，MySQL的解析器（parser ）将查询分解，并构建“解析树”（parse tree）。解析器使用MySQL的SQL语法来解释和验证查询。

然后，预处理器（preprocessor ）会在结果解析树中检查解析器无法解析的其他语义。

接下来，预处理器会检查权限。这通常非常快，除非服务器存在大量的权限。

3.3.2. 优化器

解析树目前是有效的，可供优化器（optimizer ）将其转换为查询执行计划。查询通常可以通过多种不同的方式执行，并产生相同的结果。优化器的工作是找到最佳的选项。

MySQL使用基于成本的优化器，这意味着它会尝试预测各种执行计划的成本，并选择成本最低的执行计划。

MySQL查询优化器是一个非常复杂的软件，它使用许多优化将查询转换为执行计划。

参考 https://dev.mysql.com/doc/internals/en/optimizer-definitions.html ，优化器的狭义定义是：优化器是一组例行程序，决定DMBS执行查询时使用什么执行路径。

参考《Understanding MySQL Internals》，MySQL的优化器有以下几个重要的任务：

  • 确定哪些索引键可用于从表中检索记录，并为每个表选择最佳索引键；
  • 对于每一个表，确定全表扫描是否比通过索引键读取更好。 如果有很多与键值匹配的记录，通过索引键读取的优点会降低，并且全表扫描会变得更快；
  • 当查询中存在多个表时，确定对表进行连接时的顺序；
  • 改写（rewrite）WHERE子句以消除无效代码，减少不必要的计算，并在可能的情况下更改约束，以使得索引键可被使用；
  • 从连接中删除未使用的表；
  • 确定索引键是否可用于ORDER BY和GROUP BY；
  • 尝试用内连接替换外连接；
  • 尝试简化子查询，并确定可以将其结果缓存到什么程度；
  • 合并视图（将视图引用扩展为宏）。
3.3.2.1. 表与索引数据

包含查询优化器的MySQL服务器层，不存储有关数据和索引的统计信息。这是存储引擎的工作，因为每个存储引擎可能会保留不同种类的统计信息（或以不同的方式进行保存）。

由于MySQL服务器不存储统计信息，因此MySQL查询优化器必须从存储引擎获取查询中的表的统计信息。存储引擎为优化器提供统计信息，例如每个表或每个索引的页数，表和索引的基数，行和索引键的长度，以及索引键分布信息。优化器可以使用以上信息来帮助确定最佳执行计划。

3.3.2.2. 执行计划

很多其他数据库产品会生成字节码来执行查询，MySQL与它们不同，MySQL不会生成字节码来执行查询。相反，查询执行计划实际上是指令树，查询执行引擎会根据查询执行计划指令树产生查询结果。最终的计划包含足够的信息来重建原始查询。

3.4. 查询执行引擎

解析和优化阶段输出了查询执行计划，MySQL的查询执行引擎会使用查询执行计划来处理查询。查询执行计划是一个数据结构，不是可执行的字节码。

与优化阶段相比，执行阶段通常并不那么复杂：MySQL仅遵循查询执行计划中给出的指令。查询执行计划中的许多操作调用存储引擎接口实现的方法，也称为handler API。

在执行查询时，MySQL服务器只需重复执行指令，直到没有更多行用于检查。

参考MySQL文档中关于MySQL服务器访问存储引擎的示意图，可知中间会通过handler API，图片地址为 https://dev.mysql.com/doc/refman/5.6/en/images/innodb_memcached2.jpg ，如下所示：

mysql动态查询输出 mysql执行查询语句_mysql动态查询输出_05

3.5. 返回结果给客户端

执行查询的最后步骤是回复客户端。即使查询没有返回结果集，仍会使用有关查询的信息（例如受影响的行数），对客户端连接进行回复。

如果查询是可缓存的，MySQL在此阶段还会将结果放入查询缓存。

MySQL服务器逐渐生成并发送结果。当MySQL处理完最后一个表并成功生成一行时，它就可以并且应当将该行发送给客户端。

以上有两个好处：使服务器避免将行保留在内存中；这意味着客户端将尽快开始获取结果。

结果集的每一行均以MySQL客户端/服务器协议单独的包发送，协议包在TCP协议层可进行缓冲和合并发送。

4. WHERE子句的执行方式

通常，MySQL可以通过以下三种方式处理WHERE子句，以下分别为从最佳到最差的处理方式：

  • 将条件应用于索引查找操作，以（从查询结果）消除不匹配的行。以上发生在存储引擎层；
  • 使用覆盖索引（covering index）避免对行进行访问，并在从索引中获取每个结果后滤除不匹配的行。以上发生在服务器层，但不需要从表中读取行；
  • 从表中检索行，然后过滤不匹配的行。这发生在服务器层，需要服务器从表中读取行才能对其进行过滤。

5. MySQL语句执行过程分析方法

5.1. 获取MySQL最近执行的语句

5.1.1. 使用SHOW PROFILES获取执行的语句

参考 https://dev.mysql.com/doc/refman/5.6/en/show-profiles.html 、 https://dev.mysql.com/doc/refman/5.6/en/show-profile.html 。

SHOW PROFILES、SHOW PROFILE语句显示分析（profiling）信息，表明当前会话过程中执行语句的资源使用情况。

SHOW PROFILES显示最近发送到服务器的语句列表。列表的大小由会话变量profiling_history_size控制，其默认值为15。最大值为100。将其值设置为0会禁用分析。

所有的语句都会被分析，除了SHOW PROFILE与SHOW PROFILES，因此在分析结果列表中不会出现上述语句。格式错误的语句会出现在分析结果中

会话变量profiling可用于对分析进行控制，其默认值为0（OFF）， 将profiling变量值设置为1或ON可以开启分析 ：

SET profiling = 1;

分析针对每个会话启用。当会话结束时，其分析信息会丢失。

SHOW PROFILES输出示例如下：

Query_ID | Duration   | Query
---------+------------+------------------------------------------------------
       1 | 0.00026232 | select now()
       2 | 0.00249026 | select * from test_table
       4 | 3.32405767 | select * from test_table_log where other2=2 limit 1

5.2. 获取MySQL最近执行语句的资源使用情况

5.2.1. 使用SHOW PROFILE获取语句资源使用情况

参考 https://dev.mysql.com/doc/refman/5.6/en/show-profile.html 。

5.2.1.1. SHOW PROFILE作用与启用

SHOW PROFILE显示关于单个语句的详细信息。

启用SHOW PROFILE时，需要将会话变量profiling设置为1或ON。

5.2.1.2. SHOW PROFILE语法

SHOW PROFILE的语法如下：

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type值是可选的，可用于指定需要显示的额外类型的信息，可选值包括ALL、BLOCK IO、CONTEXT SWITCHES、CPU、IPC、MEMORY、PAGE FAULTS、SOURCE、SWAPS。

如果没有FOR QUERY n子句，则输出最近执行的语句。如果包括FOR QUERY n子句，则显示第n条语句的信息。n的值对应于SHOW PROFILES显示的Query_ID值。

默认情况下，SHOW PROFILE显示Status与Duration列。Status列的值与SHOW PROCESSLIST展示的State值类似。

5.2.1.3. SHOW PROFILE输出格式

SHOW PROFILE输出中的Status字段值，可参考 https://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html 。

对于查询语句执行SHOW PROFILE，输出结果中的Status列常见值如下所示：

starting
checking permissions
Opening tables
After opening tables
System lock
Table lock
After opening tables
init
optimizing
statistics
preparing
executing
Sending data
end
query end
closing tables
freeing items
updating status
logging slow query
cleaning up

Sending data表示线程正在为查询语句读取并处理行，并将数据发送给客户端。因为在此状态下的操作通常需要执行大量的磁盘访问（读取），因此该状态通常是给定的查询生命周期中运行时间最长的状态。

其他状态的含义与显示的名称接近，与MySQL执行语句时的执行步骤对应。

SHOW PROFILE输出中的Duration列，代表语句在指定的状态执行的时间，单位为秒。

SHOW PROFILE输出中的剩余列的含义可参考INFORMATION_SCHEMA.PROFILING表的说明。

5.2.2. 从数据库表获取语句资源使用情况

参考 https://dev.mysql.com/doc/refman/5.6/en/profiling-table.html 。

INFORMATION_SCHEMA.PROFILING表提供语句的分析信息，其内容与SHOW PROFILE和SHOW PROFILES语句产生的信息对应。

会话变量profiling需要设置为1，否则上述表是空的。

INFORMATION_SCHEMA.PROFILING表包含以下关注的列：

  • QUERY_ID

语句的数字形式标识符；

  • SEQ

序列号，表明具有相同QUERY_ID值的行的显示顺序；

  • STATE

同SHOW PROFILE输出结果中的Status列；

  • DURATION

同SHOW PROFILE输出结果中的Duration列；

  • SOURCE_FUNCTION, SOURCE_FILE, SOURCE_LINE

表明被分析的状态执行的源代码位置的信息。