MySQL之查询性能优化

╰半橙微兮° 2022-05-13 14:12 416阅读 0赞

### 文章目录 ###

*  MySQL是否在扫描额外的记录
 *   *  响应时间
     *  扫描的行数的返回的行数
 *  扫描的行数和访问类型
 *  重构查询的方式
 *   *  一个复杂查询还是多个简单查询
     *  切分查询
     *  分解关联查询
 *  查询执行的基础
 *   *  MySQL客户端和服务器通信协议
     *  查询状态
     *  查询缓存
     *  查询的优化处理
     *   *  语法执行器和预处理
         *  查询优化器
 *  数据和索引统计信息
 *  MySQL如何执行关联查询
 *   *  执行计划
     *  关联查询优化器
     *  排序优化
     *  查询执行引擎
     *  返回结果给客户端
     *  结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送，再通过TCP协议进行传输，在TCP传输的过程中，可能对MySQL的封包进行缓存然后批量传输。

# MySQL是否在扫描额外的记录 #

*  在确定查询只返回需要的数据之后，接下看看看查询是否为了返回结果扫描了过多的数据。三个衡量查询开销的指标如下：

1.  响应时间
2.  扫描的行数
3.  返回的行数

*  这三个指标都会记录到MySQL的慢日志中，所以检查慢日志记录是找出扫描过多行数的查询的好办法。

## 响应时间 ##

*  响应时间只是一个表面上的值。
 *  响应时间是两部分之和：服务时间和排队时间。服务时间是指数据库处理这个查询真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间-可能是等待I/O操作完成，也可能是等待行锁等待。所以在不同类型压力下，响应时间并没有什么一致的规律和公式。诸如存储引擎的锁（表锁、行锁）、高并发资源竞争、硬件响应等诸多因素都会影响响应时间。
 *  当看到一个响应时间的时候，首先要问问自己这是不是一个合理的值。实际上可以使用“快速上限估计”来估算查询的响应时间。概况的说，了解这个查询需要那些索引以及它的执行计划是什么，然后大概需要多少个顺序和随机IO，再用其乘以具体硬件条件的一次IO的消耗时间。最后把这些消耗加起来，就可以获得一个大概参考值来判断当前响应时间是不是一个合理的值。

## 扫描的行数的返回的行数 ##

*  分析查询是，查看该查询扫描的行数是非常有帮助的。
 *  并不是所有的行的访问代价是相同的。较短的行的访问速度更快，内存中的行也比磁盘中的行的访问速度快的多。
 *  理想情况下，扫描的行数和返回的行数应该是相同的。但实际情况下，这种“美事”并不多。例如，关联查询时，服务器必须扫描多行才能生成一行。扫描的行数对返回的行数的比率通常很小，一般在1:1和10:1之间，不过有时候这个值也可能非常非常大。

# 扫描的行数和访问类型 #

*  在评估查询开销的时候，需要考虑一下从表中找到某一行的成本。MySQL有好几种方式可以查找并返回一行结果。有些访问方式需要扫描很多行才能返回一行结果，有些可能无需扫描就能返回结果。
 *  在EXPLAIN的type类反应了访问类型。访问类型包含全表扫描、索引扫描 、范围扫描、唯一索引查询、常识引用。它们的速度从慢到快。
 *  索引能让MySQL以最高效、扫描行数最少的方式找到需要的记录。
 *  一般MySQL使用如下三种方式引用WHERE条件，从好到坏依次为：

1.  在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。
2.  使用索引覆盖扫描（在Extra列中出现了Using index）来返回记录 ，直接从索引中过滤不需要的记录并返回命中的结构。在MySQL服务器层完成的。无需回表。
3.  从数据表中返回数据，然后过滤不需要的数据（在Extra列中出现Using where）。在MySQL服务器层完成，需要先读出数据在过滤。

*  好的索引可以让查询使用合适的访问类型，尽可能的只扫描需要的数据行。但也不是说增加索引就能让扫描的行数等于返回的行数。
 *  如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下面的技巧去优化它：

1.  使用索引覆盖扫描，把需要用到的列都放到索引中，这样存储引擎就无需回表就可以返回结果了。
2.  改变库结构。例如使用单独的汇总表。
3.  重新这个复杂的查询。让MySQL能以更优化的方式执行这个查询。

# 重构查询的方式 #

## 一个复杂查询还是多个简单查询 ##

*  设计查询的时候一个需要考虑的重要的问题是，是否需要将一个复杂的查询分成多个简单的查询。
 *  在传统的实现中，总是强调需要数据库层完成尽可能多的工作，这样做的逻辑在于以前总是认为网络通信、查询解析和优化是一键代价很高的事情。
 *  但是这样的想法对MySQL并不适用，MySQL从设计上让连接和断开连接都很轻量级。在返回一个小的查询结果方面很高效。所以运行多个小查询现在已经不是大问题了。
 *  MySQL内部每秒能够扫描内存中上百万行数据，相比之下，MySQL响应数据给客户端就慢得多了。在其他条件都相同的时候，使用尽可能少的查询当然是更好的。但是有时候，将一个大查询分解为多个小查询是很有必要的。别害怕这样做，好好衡量一下这样做是不是会减少工作量。

## 切分查询 ##

*  有时候需要对一个大查询“分而治之”，将大查询切分为多个小查询，小查询的功能完全一样，每次只完成一小部分，只返回一小部分查询结果。
 *  删除旧数据就是一个很好的例子。定期清理大量数据时，如果用一个大语句一次性完成的话，可能需要一次锁住很多数据，占满整个事务日志，耗尽系统资源，阻塞很多小但重要的查询。我们可以分多次删除。
 *  一次性删除一万行数据一般来说是一个比较高效而且对服务器影响也最小的做法（如果是事务型引擎，很多时候小事务能够更高效）。同时需要注意的是，每次删除后，都暂停一会儿再做下一次删除，这样可以将服务器原本一次性的眼里分散到一个很长的时间段中，就可以大大降低对服务器的影响，还可以大大减少删除时锁的持有时间。

## 分解关联查询 ##

*  很多高性能的应用都会对关联查询进行分解。简单地，可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。
 *  用分解关联查询重构查询的优势如下：

1.  让缓存的效率更高。很多应用程序可以方便的缓存单表对应的结果对象。另外，对于MySQL的查询缓存来说，如果关联中的某个表发生了变化，查询缓存就无效了。而拆分后，如果某个表很少改变，就可以重复利用查询缓存结果了。
2.  将查询分解后，执行单个查询可以检索锁的竞争。
3.  在应用层做拆分，可以更容易对数据库进行拆分，更容易做到高性能和高扩展。
4.  查询本身的效率也有可能提升。使用IN()代替关联查询，可以让MySQL安装ID顺序进行查询，这可能比随机的关联要更高效。
5.  可以减少冗余记录的查询。在应用层做关联查询，意味着某个记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消耗。
6.  更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高很多。

*  在很多场景下，将关联放到应用程序中将会更加高效，比如：当应用能够很方便的缓存单个查询的时候、当可以将数据分布到不同的MySQL服务器上的时候，当能够使用IN()代替关联查询的时候、当查询中使用同一个数据表的时候。

# 查询执行的基础 #

*  当希望MySQL能够以更高的性能运行查询时，最好的办法是弄清除MySQL是如何优化和执行查询的。一旦理解这一点，很多查询优化工作实际上就是遵循一些原则让优化器能够按照预想的合理的方式运行。
 *  ![mysql的查询执行路径][mysql]
 *  MySQL的执行过程如下：

1.  客户端发送一个查询给服务器。
2.  服务器先检查查询缓存，如果命中了缓存，则直接返回缓存中的结构。否则进入下一个阶段。
3.  服务器端进行SQL解析、预处理，再有优化器生成对应的执行计划。
4.  MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
5.  将结果返回给客户端。

## MySQL客户端和服务器通信协议 ##

*  MySQL客户端和服务器的通信协议是“半双工”的，这意味着，在任何一个时刻，要么是有服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作无法同时发生。所以，我们无法也无须将一个消息切成小块独立来发送。
 *  这种协议让MySQL通信简单快速，但是也从很多地方限制了MySQL。一个明显的限制是，无法进行流量控制。一旦一端开始发送消息，另一端要接受完整个消息才能响应它。
 *  客户端用一个单独的数据包将查询传给服务器。一旦客户端发送了请求，它能做的事情就只能是等待结果了。当查询语句很长的时候，参数max\_allowed\_packet特别重要（如果查询太多，服务器会拒绝接受更多的数据并抛出相应的错误）。
 *  服务器发送给客户端的数据通常很多，由多个数据包组成。当服务器开始相应客户端的时候，客户端必须完整地接受整个返回结果，而不是简单的取前面几条数据，让服务器停止发送数据。这种情况下，客户端若接收完整的结果，然后取前面几条需要的结果，或者接收完几条结果后就“粗暴”地断开连接，都不是好主意。这也是在必要的时候一定要在查询中加上LIMIT 限制的原因。
 *  换一种方式解释这种行为：当客户端从服务器取数据时，看起来是一个拉数据的过程，但实际上是MySQL在向客户端推送数据的过程。客户端不断地接收从服务器推送的数据，客户端也没法让服务器停下来。客户端像是“从消防水管喝水”。
 *  多数连接MySQL的库函数都可以获得全部结果集并缓存到内存里，还可以逐行获取需要的数据。默认一般是获得全部结果集并缓存到内存中。MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通常可以减少服务器的压力，让查询能够早点结束、早点释放相应的资源。
 *  当使用多数连接MySQL的库函数从MySQL获取数据时，其结果看起来都像是从MySQL服务器获取数据，而实际上都是从这个库函数的缓存获取数据。多数情况下这没什么问题，但是如果需要返回一个很大的结果集的时候，这样做并不好，因为库函数会花很多时间和内存来存储所有的结果集。如果能够尽早开始处理这些结果集，就能大大减少内存的消耗，这种情况下可以不使用缓存来记录结果而是直接处理。这样做的缺点是，对于服务器来说，需要查询完成后才能释放资源，所以在和客户端交互的整个过程中，服务器的资源都是被这个查询所占用的。

## 查询状态 ##

对应一个MySQL连接，或者说一个线程，任何时刻都有一个状态，表示MySQL当前正在做什么。有很多方式可以查看MySQL当前的状态，如`SHOW FULL PROCESSLIST`（该命令返回的Command列就表示当前的状态）。在一个查询的生命周期中，状态会变化多次。

*  Sleep  
    线程正在等待客户端发送新的请求。
 *  Query  
    线程正在执行查询或者正在将结果发送给客户端。  
    Locked  
    在MySQL服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态，但在其他没有行锁的引擎中也经常会出现。
 *  Analyzing and statistics  
    线程正在收集存储引擎的统计信息，并生成查询的执行计划。
 *  Copying to tmp table \[on disk\]  
    线程正在执行查询，并且将其结果集都复制到一个临时表中，这种状态一般要么是在做GROUP BY 操作，要么是文件排序操作，或者是UNION操作。如果这个状态后面还有“on disk”标记，那表示MySQL正在将一个内存临时表放到磁盘上。
 *  The thread is  
    线程正在对结果集进行排序。
 *  Sending data  
    这表示多种情况：线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。

## 查询缓存 ##

*  在解析一个查询语句前，如果查询缓存是开启的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找来实现的。查询和缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果。
 *  如果查询恰好命中了缓存，那么在返回查询之前会检查一次用户权限。这仍然是无须解析查询SQL语句的，因为在查询缓存中已经存放了当前查询需要访问的表信息。如果权限没有问题，MySQL会跳过所有其他阶段，直接从缓存中拿到结果并返回给客户端。这种情况下，查询不会被解析，不用生成执行计划，不会被执行。

## 查询的优化处理 ##

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

### 语法执行器和预处理 ###

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

### 查询优化器 ###

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

mysql> SELECT SQL_NO_CACHE COUNT(*) FROM film_actor;
    mysql> SHOW STATUS LIKE ‘Last_query_cost’;

它的值会返回1040.599000。这个结果表明MySQL的优化器任务大概需要做1040个数据页的随机查找才能完成上面的查询。 这是根据一系列的统计信息计算得来的：每个表或者索引的页面个数、索引的基数（索引中不同值的数量）、索引和数据行的长度、索引分布情况。优化器在评估成本的时候并不考虑任何层面的缓存，它假设读取任何数据都需要一次磁盘I/O。

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

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

*  MySQL的查询优化器是一个非常复杂的部件，它使用了很多优化策略来生成一个最优的执行计划。优化策略可以简单分为两种：静态优化和动态优化。
 *  静态优化可以直接对解析树进行分析，并完成优化。例如，优化器可以通过一些简单的代数变换将WHERE 条件转换成另一种等价形式。静态优化不依赖于特别的数值，如WHERE 条件中带入的一些常数等。静态优化在第一次完成后就一直有效，即使使用不同的参数重复执行查询也不会发生变化。可以认为这是一种“编译时优化”。
 *  动态优化则和查询的上下文有关。也可能和很多其他因素有关，例如WHERE 条件中的取值、索引中条目对应的数据行数等。这需要在每次查询的时候都重新评估，可以认为这是“运行时优化”。
 *  在执行语句和存储过程的时候，动态优化和静态优化的区别非常重要。MySQL对查询的静态优化只需要做一次，但对查询的动态优化则在每次查询执行时都需要重新评估。有时候甚至在查询的执行过程中也会重新优化。例如，在关联操作中，范围检查的执行计划会针对每一行重新评估索引。可以通过EXPLAIN执行计划中的Extra列看是否有"range checked for each record"来确认这一点。
 *  下面是一下MySQL能够处理的优化类型：

1.  重新定义关联表的顺序  
    数据表的关联并总是按照查询中指定的顺序执行。决定关联的顺序是优化器很重要的一部分功能。
2.  将外连接转换为内连接  
    并不是所有的OUTER JOIN都必须以外连接的方式执行。诸多因素，例如WHERE条件，库表结构都可能会让外连接等价于一个内连接。MySQL能够识别这点并重新查询，让其可以调整关联顺序。
3.  使用等价变化规则  
    MySQL能够使用一些等级变化来简化并规范表达式。它可以合并和减少一些比较，还可以移除一些恒成立和一些恒不成立的判断。例如，（5=5 AND a>5）则被改写为a>5。
4.  优化COUNT()、MIN()、MAX()  
    索引和列是否为空通常可以帮助MySQL优化这类表达式。例如，要找到某一列的最小值，只需要查询对应B-Tree索引最左边的记录，MySQL可以直接获取索引的第一行记录。在优化器生成执行计划的时候就可以利用这一点，在B-Tree索引中，优化器会将这个表达式作为一个常数对待。如果MySQL使用了这种类型的优化，那么在EXPLAIN中就可以看到`Select tables optimized away`。它表示优化器已经从执行计划中移除了该表，并以一个常数取而代之。  
    类似的，没有任何WHERE 条件的COUNT（\*） 查询通常也可以使用存储引擎提供的一些优化（例如，MyISAM维护了一个变量来存放数据表的行数）。
5.  预估并转换为常数表达式  
    当My例如，一个用户自定义变量在查询中没有发生变化时就可以转换为一个常数。数学表达式则是另一种典型的例子。SQL检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理。  
    让人惊讶的是，在优化阶段，有时候甚至一个查询也能够转化为一个常数。一个例子是在索引列上执行MIN() 函数。甚至是主键或者唯一键查找语句也可以转换为常数表达式。如果WHERE 子句中使用了该类索引的常数条件，MySQL可以在查询开始阶段就先查找到这些值，这样优化器就能够知道并转换为常数表达式。  
    另一种会看到常数条件的情况是通过等式将常数值从一个表传到另一个表，这可以通过WHERE、USING 或者ON 语句来限制某列取值为常数。
6.  覆盖索引扫描  
    当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需要的数据，而无须查询对应的数据行。
7.  子查询优化  
    MySQL在某些情况下可以将子查询转换为一种效率更高的形式，从而减少多个查询多次对数据进行访问。
8.  提前终止查询  
    在发现已经满足查询需要的时候，MySQL总是能够立即终止查询。如，使用LIMIT的时候，发现了一个不成立的条件，这是MySQL可以立刻返回一个空结果。  
    除此之外，MySQL发现某些特殊的条件，则会提前终止查询。如，当存储引擎需要检索“不同值”和判断存在性的时候。  
    类似这种“不同值/不存在”的优化一般可用于DISTINCT 、NOT EXIST() 或者LEFT JOIN 类型的查询。
9.  等值传播  
    如果两个列通过等式关联，那么MySQL能将其中一个列的WHERE条件传递到另外一个列上。
10. 列表IN()的比较  
    在很多数据库系统中，IN()完全等同于多个OR条件的子句，因为这两者是完全等价的。在MySQL中这点是不成立的，MySQL将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件。 这是一个O（log n ）复杂度的操作，等价地转换成OR 查询的复杂度为O（n ），对于IN() 列表中有大量取值的时候，MySQL的处理速度将会更快。

*  不要自以为比优化器更聪明，最终你可能会占点便宜，但是更有可能会使查询变得更加复杂而难以维护，而最终的收益却为零。让优化器按照它的方式工作就可以了。
 *  如果能够确认优化器给出的不是最佳选择，并且清楚背后的原理，那么也可以帮助优化器做进一步的优化。例如，可以在查询中添加hint提示，也可以重写查询，或者重新设计更优的库表结构，或者添加更合适的索引。

# 数据和索引统计信息 #

*  MySQL架构由多个层次中组成。在服务器层由查询优化器，却没有保存数据和索引的统计信息。统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息（也可以按不同的格式存储统计信息）。
 *  MySQL查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息。存储引擎提供给优化器的统计信息，包括：每个表或索引有多少个页面、每个表的每个索引的基数是多少、数据行和索引的长度、索引的分布信息等。

# MySQL如何执行关联查询 #

*  MySQL中`关联`(join)一词所包含的意义更加广泛。总的来说，MySQL认为任何一次查询都是关联-并不仅仅是一个查询需要到两个表匹配才叫关联，所以在MySQL中，每一个查询，每一个片段（包括子查询，甚至基于单表的SELECT）都可以是关联。
 *  对应UNION查询，MySQL先将一系统的单个查询结果放到一个临时表中，然后在重新读出临时表数据来完成UNION查询。在MySQL的概念中，每个查询都是一次关联，所以读取结果临时表也是一次关联。
 *  当前MySQL关联执行的策略很简单：MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。MySQL会尝试在最后一个关联表中找到所有匹配的行，如果最后一个联表无法找到更多的行以后，MySQL返回到上一层次关联表，看是否能够找到更多的匹配记录，依此类推迭代执行。
 *  按照这样的方式查找第一个表记录，再嵌套查询下一个关联表，然后回溯到上一个表，在MySQL中是通过嵌套循环的方式实现——正如其名“嵌套循环关联”。
 *  从本质上说，MySQL对所有的类型的查询都以同样的方式运行。例如，MySQL在FROM子句中遇到子查询时，先执行子查询并将其结果放到一个临时表中（临时表是没有索引的），然后将这个临时表当作一个普通中对待（派生表）。MySQL在执行UNION查询时也使用类似的临时表，在遇到右外连接的时候，MySQL将其改写成等价的左外连接。简而言之，当前版本的MySQL会将所有的查询类型都转换成类似的执行计划。
 *  不过，不是所有的查询都可以转换成上面的形式。例如，全外连接就无法通过嵌套循环和回溯的方式完成，这时当发现关联表中没有找到任何匹配行的时候，则可能是因为关联是恰好从一个没有任何匹配的表开始。这大概也是MySQL并不支持全外连接的原因。还有些场景，虽然可以转换成嵌套循环的方式，但是效率却非常差。

## 执行计划 ##

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

## 关联查询优化器 ##

*  MySQL优化器最重要的一部分就是关联查询优化，它决定了多个表关联时的顺序。  
    [STRAIGHT\_JOIN][STRAIGHT_JOIN]
 *  严格来说，MySQL并不是根据读取的记录数来选择最优的执行计划。实际上，MySQL通过预估需要读取的数据页来选择，读取的数据源越少越好。不过读取的记录数通常能很好的反映一个查询的成本。
 *  重新定义关联的顺序是优化器非常重要的一部分功能。不过有的时候，优化器给出的并不是最优的关联顺序。这时可以使用`STRAIGHT_JOIN`关键字重写查询，让优化器按照你认为的最优的关联顺序执行——不过老实说，人的判断很难那么精准。绝大多数时候，优化器做出的选择都比普通人的判断要更准确。
 *  关联优化器会尝试在所有的关联顺序中选择一个成本最小的来生成执行计划树。如果可能，优化器会遍历每一个表然后逐个做嵌套循环计算每一棵可能的执行计划树的成本，最后返回一个最优的执行计划。
 *  不过，糟糕的是，如果有超过n个表的关联，那么需要检查n的阶乘种关联顺序。我们称之为所有可能的执行计划的“搜索空间”，搜索空间的增长速度非常块——例如，若是10个表的关联，那么共有3628800种不同的关联顺序！当搜索空间非常大的时候，优化器不可能逐一评估每一种关联顺序的成本。这时，优化器选择使用“贪婪”搜索的方式查找“最优”的关联顺序。实际上，当需要关联的表超过optimizer\_search\_depth 的限制的时候，就会选择“贪婪”搜索模式了（optimizer\_search\_depth 参数可以根据需要指定大小）。
 *  有时，各个查询的顺序并不能随意安排，这时关联优化器可以根据这些规则大大减少搜索空间，例如，左连接、相关子查询。这是因为，后面的表的查询需要依赖于前面表的查询结果。这种依赖关系通常可以帮助优化器大大减少需要扫描的执行计划数量。

## 排序优化 ##

*  无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。
 *  当不能使用索引生成排序结果的时候，MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，不过MySQL将这个过程统一称为文件排序（filesort ），即使完全是内存排序不需要任何磁盘文件时也是如此。
 *  如果需要排序的数据量小于“排序缓冲区”，MySQL使用内存进行“快速排序”操作。如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用“快速排序”进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并（merge），最后返回排序结果。
 *  MySQL有如下两种排序算法：

1.  两次传输排序（旧版本使用）  
    读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。  
    这需要进行两次数据传输，即需要从数据表中读取两次数据，第二次读取数据的时候，因为是读取排序列进行排序后的所有记录，这会产生大量的随机I/O，所以两次数据传输的成本非常高。当使用的是MyISAM表的时候，成本可能会更高，因为MyISAM使用系统调用进行数据的读取（MyISAM非常依赖操作系统对数据的缓存）。不过这样做的优点是，在排序的时候存储尽可能少的数据，这就让“排序缓冲区” 中可能容纳尽可能多的行数进行排序。
2.  单次传输排序（新版本使用）  
    先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。这个算法只在MySQL 4.1和后续更新的版本才引入。因为不再需要从数据表中读取两次数据，对于I/O密集型的应用，这样做的效率高了很多。另外，相比两次传输排序，这个算法只需要一次顺序I/O读取所有的数据，而无须任何的随机I/O。缺点是，如果需要返回的列非常多、非常大，会额外占用大量的空间，而这些列对排序操作本身来说是没有任何作用的。因为单条排序记录很大，所以可能会有更多的排序块需要合并。

*  很难说哪个算法效率更高，两种算法都有各自最好和最糟的场景。当查询需要所有列的总长度不超过参数max\_length\_for\_sort\_data 时，MySQL使用“单次传输排序”，可以通过调整这个参数来影响MySQL排序算法的选择。
 *  MySQL在进行文件排序的时候需要使用的临时存储空间可能会比想象的要大得多。原因在于MySQL在排序时，对每一个排序记录都会分配一个足够长的定长空间来存放。
 *  在关联查询的时候如果需要排序，MySQL会分两种情况来处理这样的文件排序。如果ORDER BY 子句中的所有列都来自关联的第一个表，那么MySQL在关联处理第一个表的时候就进行文件排序。如果是这样，那么在MySQL的EXPLAIN 结果中可以看到Extra 字段会有“Using filesort”。除此之外的所有情况，MySQL都会先将关联的结果存放到一个临时表中，然后在所有的关联都结束后，再进行文件排序。这种情况下，在MySQL的EXPLAIN 结果的Extra 字段可以看到“Using temporary;Using filesort”。如果查询中有LIMIT 的话，LIMIT 也会在排序之后应用，所以即使需要返回较少的数据，临时表和需要排序的数据量仍然会非常大。
 *  MySQL 5.6在这里做了很多重要的改进。当只需要返回部分排序结果的时候，例如使用了LIMIT 子句，MySQL不再对所有的结果进行排序，而是根据实际情况，选择抛弃不满足条件的结果，然后再进行排序。

## 查询执行引擎 ##

*  在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构，而不是和很多其他的关系型数据库那样会生成对应的字节码。
 *  相对于查询优化阶段，查询执行阶段不是那么复杂：MySQL只是简单地根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成，这些接口也就是我们称为“handler API ”的接口。
 *  MySQL需要进行表锁的时候。handler可能会实现自己的级别的、更细粒度的锁，如InnoDB就实现了自己的行基本锁，但这并不能代替服务器层的表锁。如果是所有存储引擎共有的特性则由服务器层实现，比如时间和日期函数、视图、触发器等。

## 返回结果给客户端 ##

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

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

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

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

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

**参考《高性能MySQL》**

[mysql]: /images/20220504/3d7443751bc94270ab3444d3cf6b4d8d.png
[MySQL]: /images/20220504/cc34d96506a948f3b72a7bd80f101050.png
[STRAIGHT_JOIN]: https://blog.csdn.net/aeolus_pu/article/details/7789543