【学习笔记】mysql查询执行的基础

本文属于读书笔记，大部分内容摘抄于《高性能MYSQL》，摘抄内容版权属于原作者。

mysql执行一个查询的过程，到底做了什么？

 

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

2. 服务器先检查查询缓存，如果命中缓存，则理科返回存储在缓存中的记过。否则进入下一阶段。

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

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

5. 将结果返回给客户端。

MYSQL客户端/服务器通信协议

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

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

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

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

换一种方式解释这种行为：当客户端从服务器取数据时，看起来是一个拉数据的过程，但实际上是mysql在向客户端推送数据的过程。客户端不断地接收从服务器推送的数据，客户端也没法让服务器停下来。客户端像是“从消防水管喝水”。

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

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

查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么mysql会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只是一个字节不同，那也不会匹配缓存结果，这种情况下查询就会进入下一阶段的处理。

如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前mysql会检查一次用户权限。这仍然是无需解析查询SQL语句的，因为在查询缓存中已经存放了当前查询需要访问的表信息。如果权限没有问题，mysql会跳过所有其他阶段，直接从缓存中拿到结果并返回给客户端。这种情况下，查询不会被解析，不用生成执行计划，不会被执行。

查询优化处理

查询的生命周期的下一步是将一个sql转换成一个执行计划，mysql再依照合格执行计划和存储引擎进行交互。这包括多个子阶段：解析SQL、预处理、优化SQL执行计划。这个过程中任何错误（例如语法错误）都可能终止查询。

语法解析器和预处理

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

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

下一步预处理器会验证权限，这通常很快，除非服务器上油很多的权限配置。

查询优化器

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

mysql使用基于成本的优化器，它将尝试预测一个查询使用某种查询计划时的成本，并选择其中成本最小的一个。最初，成本的最小单位是随机读取一个4K数据页的成本，后来（成本计算公式）变得更加复杂，并且引入了一些“因子”来估算某些操作的代价，如当执行一次where条件比较的成本。可以通过查询当前回话的Last_query_cost的值来得知mysql计算的当前查询的成本。

有很多原因会导致mysql优化器选择错误的执行计划，如下所示：

• 统计信息不准确。mysql以来存储引擎提供的统计信息来评估成本，但是有的存储引擎提供的信息是准确的，有的偏差可能会非常大。
  
• 执行计划中的成本估算不等同于实际执行得成本。所以即使统计信息精确，优化器给出的执行计划也可能不是最优的。
  
• mysql的最优可能和你想的最优不一样。你可能希望执行时间尽可能的端，但是mysql只是基于其成本模型选择最优的执行计划，而有些时候，这并不是最快的执行方式。所以，这里我们看到根据执行成本来选择执行计划并不是完美模型。
  
• mysql从不考虑其他并发执行得查询，可能会影响到当前查询的速度。
  
• mysql也并不是任何时候都是基于成本优化的。有时也会基于一些固定的规则，例如，如果存在全文搜索的match子句，则在存在全文索引的时候就使用全文索引。即使有时候使用别的索引和where条件可以远比这种方式要快，mysql仍然会使用对应的全文索引。
  
• mysql不会考虑不受其控制的操作成本，例如执行存储过程或者用户自定义函数的成本。
  
• 后面我们还会看到，优化器有时候无法去估算所有可能的执行计划，所以它可能错过实际上最优的执行计划。
  

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

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

在执行语句和存储过程的时候，动态优化和静态优化的区别非常重要。mysql对查询的静态优化只需要做一次，但是对插叙你的动态优化则在每次执行时都需要重新评估。有时候甚至在查询的执行过程中也会重新优化（例如，在关联操作中，范围检查的执行计划会针对每一行重新评估索引）。

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

• 重新定义关联表的顺序；
  
• 将外链接转化成内连接；
  
• 使用等价变换规则（如 5=5 AND a>5将被改写成a>5）；
  
• 优化count()、min()和max()；
  
• 预估并转化为常数定义式；
  
• 覆盖索引扫描；
  
• 子查询优化；
  
• 提前终止查询；
  
• 等值传播；
  
• 对象IN()的比较；
  

这些并不是mysql优化器的全部，mysql还会做大量其他的优化，但是这些例子足以让大家明白优化器的复杂性和只能行了。当然虽然优化器已经很只能了，但是有时候也无法给出最优的结果。有时候你可能会比优化器更了解数据，例如，由于应用逻辑使得某些条件总是成立；还有时，优化器缺少某种功能特性，如哈希索引；再如前面提到的，从优化器的执行成本角度评估出来的最优执行计划，实际运行中可能比其他的执行计划慢。

如果能够确认优化器给出的不是最优解，并清除别后的逻辑，那么也可以帮助优化器做进一步优化。

数据和索引的统计信息

mysql架构由多个层次组成，在服务器层有查询优化器，却没有保存数据和索引的统计信息。统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息（也可以按照不同的格式存储统计信息）。某些引擎，例如archive引擎，则根本没有存储任何统计信息。

因为服务器层没有任何统计信息，所以mysql查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息。存储引擎则=提供给优化器对应的统计信息，包括：每个表或者索引有多少个页面，每个表每个索引的技术是多少、数据行和索引长度、索引的分布信息等。优化器根据这些信息来选择一个最优的执行计划。

mysql如何执行关联查询

mysql中的“关联”一次锁包涵的意义比一般意义上理解的要更广泛。总的来说，mysql认为任何一个查询都是一个“关联”——并不仅仅是一个查询需要到两个表匹配才叫关联，所以在mysql中，每一个查询，每一个片段（包括子查询，甚至基于单表的select）都有可能是关联。

我们根据union查询的例子来理解关联查询。对于union查询，mysql先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表来完成union查询。在mysql的概念中，每个查询都是一次关联，所以读取结果临时表也是一次关联。

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

按照这样的方式查找第一个表的记录，再嵌套查询下一个关联表，然后回溯到上一个表，在mysql中时通过嵌套循环的方式实现的。

执行计划

和很多其他关系数据库不同，mysql并不会生成查询字节码来执行查询。mysql生成查询的一棵指令书，然后通过存储引擎执行完成这棵指令书并返回结果。最终的执行计划包含了重构查询的全部信息。对于某个查询执行EXPLAIN EXTENDED后，再执行SHOW WARNINGS，就可以看到冲过出的查询。

任何多表查询都可以使用一棵树来表示如下图：

 在就氨基科学中，这被称为一棵平衡树。但是，这并不是mysql执行查询的方式。正如我们之前介绍的，mysql总是从一个表开始一直嵌套循环、回溯完成所有表的关联。所以，mysql的执行计划总是如下图所示，是一棵左侧深度优先树：

 

关联查询优化器

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

关联优化器会尝试在所有的关联顺序中选择一个成本最小的来生成执行计划树。如果可能，优化器会店里每一个表然后逐个做嵌套循环计算每一棵可能的执行计划树的成本，最后返回一个最优执行计划。

不过糟糕的是，如果有超过N个表的关联，那么需要检查n的阶乘种关联顺序。我们称之为所有可能的执行计划的“搜索空间”，搜索空间增长速度非常快。当搜索空间非常大的时候，优化器不可能逐一评估每一种关联顺序的成本。这时，优化器选择使用“贪婪”搜索的方式查找“最优”的关联顺序。实际上，当需要关联的表超过optimizer_search_depth的限制的时候，就会选择“贪婪”搜索模式了。

在mysql这些年的发展过程中，优化器积累了很多“启发式”的优化策略来加速执行计划的生成。绝大多数情况下这些都是有效的，但因为不会去计算每一种关联顺序的成本，所以偶尔也会选择一个不是最优的执行计划。

有时，各个查询的顺序并不能随意安排，这时关联优化器可以根据这些规则大大减少搜索空间，例如，左连接，相关子查询（后面我将继续讨论子查询）。这时因为，后面的表的查询需要依赖于前面表的查询结果。这种依赖关系通常可以帮助优化器大大减少需要得执行计划数量。

排序优化

无论如何排序都是一个成本很高的操作，所以从性能的角度考虑，应该尽可能避免排序或者尽可能避免对大量数据进行排序。

当不能使用索引生成排序结果的时候，mysql需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，不过mysql将这个过程同一称为文件潘旭，即使完全是内存排序不需要任何磁盘文件时也是如此。

如果需要排序的数据量小于“排序缓冲区”，mysql使用内存进行“快速排序”操作。如果内存不够排序，那么mysql会先将数据分块，对每个独立的块使用“快速排序”进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并，自后返回排序结果。

mysql有如下两种排序法：

两次传输排序（旧版本使用）：读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。这需要进行两次数据传输，即需要从数据表中独具两次数据，第二次读取数据的时候，因为是读取排序列进行排序后的所有记录，这回产生大量的随机I/O，所以两次数据传输的成本非常高。

单次传输排序（新版本使用）：先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。因为不再需要从数据表中读取两次数据，对I/O密集型应用，这样做的效率高了很多。另外相比两次传输排序，这个算法只需要一次顺序I/O读取所有数据，而无需任何随机I/O。缺点是，如果需要返回的列非常多、非常大，会额外占用大量的空间，而这些列对排序操作本身来说是没有任何作用的。

查询执行引擎

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

相对于查询优化阶段，查询执行阶段不是那么复杂：mysql只是简单的根据执行计划给出的指令逐步执行。在根据执行计划逐步执行得过程中，有大量操作需要通过调用存储引擎实现的接口来完成，这些接口也就是我们称为“handler API”的接口。查询中的每一个表由一个handler的实例表示。前面我们有意忽略了这点，实际上，mysql在优化阶段就为每个表创建了一个handler实例，优化器根据这些实例的接口可以获取表的相关信息，包括表的所有列名、索引统计信息，等等。

存储引擎接口有着非常丰富的功能，但是底层接口却只有几十个，这些接口像“搭积木”一样能够完成查询的大部分操作。

为了执行查询，mysql只需要重复执行计划中的各个操作，知道完成所有的数据查询。

返回结果给客户端

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

如果查询可以被缓存，那么mysql在这个极端也会将结果存放在查询缓存中。

mysql将结果集返回给客户端是一个增量、逐步返回的过程。例如，我们回头看看前面的关联操作，一旦服务器处理完最后一个关联表，开始生成第一条结果时，mysql就可以开始向客户端逐步返回结果集了。

这样处理有两个好处：服务器端无需存储田铎结果，也就不会因为要返回太多结果而消耗太多内存。另外，这样的处理也让mysql客户端第一时间获得返回的结果。

结果集中的每一行都会以一个满足mysql客户端/服务器通信协议的封包发送，再通过TCP协议进行传输，在TCP传输的过程中，可能对mysql的封包进行缓存然后批量传输。

查询执行引擎

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

相对于查询优化阶段，查询执行阶段不是那么复杂：mysql只是简单的根据执行计划给出的指令逐步执行。在根据执行计划逐步执行得过程中，有大量操作需要通过调用存储引擎实现的接口来完成，这些接口也就是我们称为“handler API”的接口。查询中的每一个表由一个handler的实例表示。前面我们有意忽略了这点，实际上，mysql在优化阶段就为每个表创建了一个handler实例，优化器根据这些实例的接口可以获取表的相关信息，包括表的所有列名、索引统计信息，等等。

存储引擎接口有着非常丰富的功能，但是底层接口却只有几十个，这些接口像“搭积木”一样能够完成查询的大部分操作。

为了执行查询，mysql只需要重复执行计划中的各个操作，知道完成所有的数据查询。

返回结果给客户端

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

如果查询可以被缓存，那么mysql在这个极端也会将结果存放在查询缓存中。

mysql将结果集返回给客户端是一个增量、逐步返回的过程。例如，我们回头看看前面的关联操作，一旦服务器处理完最后一个关联表，开始生成第一条结果时，mysql就可以开始向客户端逐步返回结果集了。

这样处理有两个好处：服务器端无需存储田铎结果，也就不会因为要返回太多结果而消耗太多内存。另外，这样的处理也让mysql客户端第一时间获得返回的结果。

结果集中的每一行都会以一个满足mysql客户端/服务器通信协议的封包发送，再通过TCP协议进行传输，在TCP传输的过程中，可能对mysql的封包进行缓存然后批量传输。