Mysql 性能调优四：查询优化

在前面几篇中，我们介绍了如何设计最优的库表结构、如何建立最好的索引，这些对于提高性能来说是必不可少的。但这些还不够——还需要合理地设计查询。如果查询写得很糟糕，即使库表结构再合理、索引再合适，也无法实现高性能

为什么查询会慢

在尝试编写快速的查询之前，需要清楚一点，真正重要的是响应时间。如果把查询看作一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快。

通常来说，查询的生命周期大致可以按照如下顺序来看：从客户端到服务器，然后在服务器上进行语法解析，生成执行计划，执行，并给客户端返回结果。其中，“执行”可以被认为是整个生命周期中最重要的阶段，这其中包括大量为了检索数据对存储引擎的调用以及调用后的数据处理，包括排序、分组等。

在完成这些任务的时候，查询需要在不同的地方花费时间，包括网络、CPU计算、生成统计信息和执行计划、锁等待（互斥等待）等操作，尤其是向底层存储引擎检索数据的调用操作，这些调用需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间。根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用。

在每一个消耗大量时间的查询案例中，我们都能看到一些不必要的操作、某些操作被额外地重复了很多次、某些操作执行得太慢等。优化查询的目的就是减少和消除这些操作所花费的时间。

再次声明一点，对于一个查询的全部生命周期，上面列得并不完整。这里我们只是想说明：了解查询的生命周期和清楚查询的时间消耗情况对于优化查询有很大意义。有了这些概念，我们再一起来看看如何优化查询

慢查询基础：优化数据访问

某些查询可能不可避免地需要筛选大量数据，但这并不常见。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询，我们发现通过下面两个步骤来分析总是很有效：

1. 某些查询可能不可避免地需要筛选大量数据，但这并不常见。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询，我们发现通过下面两个步骤来分析总是很有效：
2. 确认MySQL服务器层是否在分析大量不需要的数据行

是否向数据库请求了不需要的数据

• 查询了不需要的记录（及时limit，不要查询所有）
• 多表连接时返回全部列（会产生额外的io、内存和cpu消耗）
• 重复查询相同的数据（可以使用缓存）

MySQL是否在扫描额外的记录

在确定查询只返回需要的数据以后，接下来应该看看查询为了返回结果是否扫描了过多的数据。对于Mysql，最简单的衡量查询开销的三个指标如下：

• 响应时间
• 扫描的行数
• 返回的行数

没有哪个指标能够完美的衡量查询的开销，但它们大致反映了Mysql在内部执行查询时需要访问多少数据，并可以大概推测出运行时间。这三个指标都会记录到Mysql的慢日志中，MySQL是否在扫描额外的记录。

响应时间

要记住，响应时间只是一个表面上的值。这样说可能看起来和前面关于响应时间的说法有矛盾，其实并不矛盾，响应时间仍然是最重要的指标，这有一点复杂，后面细细道来。

响应时间是两部分之和：服务时间和排队时间。服务时间是指数据库处理这个查询真正花了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间- 可能是等待I/O操作完成。也可能是等待行锁等等。遗憾的是，我们无法把响应时间细分到上面这些部分，除非有什么办法能够逐个测量这些消耗，这很难做到。最常见和重要的是I/O等待和锁等待，但是实际情况更加复杂。实际上，I/O等待和锁等待非常重要，因为它们对于性能有着至关重要的影响。

所以在不同类型的应用压力下，响应时间并没有一致的规律和公式。诸如存储引擎的锁（表锁、行锁）、高并发资源竞争、硬件响应等诸多因素都会影响响应时间。所以，响应时间既可能是一个问题也可以是另外一个问题的原因

扫描的行数和返回的行数

分析查询时，查看该查询扫描的行数是非常有帮助的。这在一定程度上能够说明该查询找到需要的数据的效率高不高。对于找出那些“糟糕”的查询，这个指标可能还不够完美，因为并不是所有行的访问代价都是相同的。较短的行的访问速度更快，内存中的行比磁盘中的行的访问速度要快得多

理想情况下扫描的行数和返回的行数应该是相同的，但实际中这种“美事”并不多。例如，在做一个联接查询时，服务器必须要扫描多行才能生成结果集中的一行。扫描的行数与返回的行数的比率通常很低，一般在1：1到10：1之间，不过有时候这个值也可能非常非常大。

扫描的行数和访问类型

在评估查询开销的时候，需要考虑从表中找到某一行数据的成本。MySQL有好几种访问方式可以查找并返回一行结果。有些访问方式可能需要扫描很多行才能返回一行结果，也有些访问方式可能无须扫描就能返回结果。

EXPLAIN语句中的type列反映了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里列出的这些，速度从慢到快，扫描的行数从多到少。你不需要记住这些访问类型，但需要明白扫描表、扫描索引、范围访问和单值访问的概念

如果你没办法找到一个合适的访问类型，那么最好的解决办法通常就是增加一个合适的索引，这也正是我们前一章讨论过的问题。现在应该明白为什么索引对于查询优化如此重要了吧。索引让MySQL以最高效、扫描行数最少的方式找到需要的记录。

例如，我们看一下示例数据库Sakila中的一个查询案例：

这个查询将返回10行数据，从EXPLAIN的结果可以看到，Mysql在索引idx_fk_film_id上使用了ref访问类型来执行查询：

EXPLAIN的结果还显示Mysql预估需要访问10行数据。换句话说，查询优化器认为这种类型可以高效的完成查询。如果没有合适的索引会怎么样那？Mysql就不得不使用一种糟糕的访问类型，下面来看看如果删除对应的索引再来运行这个查询会发生什么情况：

正如我们预测的，访问类型变成了一个全表扫描（ALL），现在Mysql预估需要扫描5462条记录来完成这个查询。这里的“Using Where”表示Mysql将通过Where条件来筛选存储引擎返回的记录

一般地，Mysql能够使用如下三种方式应用Where条件，从好到坏依次为：

• 在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的
• 使用覆盖索引扫描（在Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在Mysql服务层完成的，但无须再回表查询记录
• 从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using Where）。这是在Mysql服务层完成，Mysql需要先从数据表中读出记录然后过滤

举个例子：
假设有一个表 employees，包含字段 id、name 和 salary，并且 id 字段上有一个索引。如果执行如下查询：

SELECT * FROM employees WHERE id = 1234 AND salary > 50000;

在存储引擎层完成的部分：

1. 索引查找：
   • 存储引擎在id索引中查找值为1234的记录。
   • 存储引擎初步过滤出id为1234的记录。

在MySQL服务器层完成的部分：

1. 结果过滤：
   • MySQL服务器从存储引擎获取id为1234的记录。
   • 进一步应用salary > 50000的条件进行过滤。
2. 结果返回：
   • MySQL服务器将最终符合条件的记录返回给客户端。

上面的这个例子说明了好的索引多么重要。好的索引可以让查询使用合适的访问类型，尽可能地只扫描需要的数据行。

如果发现查询需要扫描大量的数据但只返回少数行，那么通常可以尝试下面的技巧去优化它：

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

重构查询的方式

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

• 用多个简单查询代替一个复杂查询
• 如果一个查询能执行完的，也不必拆成多个简单查询（比如一个查询取10条数据， 要改成10次取一条）

切分查询

有时候对于一个大查询，我们需要“分而治之”，将大查询切分成小查询，每个查询的功能完全一样，只完成一小部分，每次只返回一小部分查询结果。

删除旧的数据就是一个很好的例子。定期清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的DELETE语句切分成多个较小的查询可以尽可能小地影响MySQL的性能，同时还可以降低MySQL复制的延迟。例如，我们需要每个月运行一次下面的查询：

那么可以用类似下面的办法来完成同样的工作：

一次删除一万行数据一般来说是一个比较高效而且对服务器[3]影响最小的做法（如果是事务型引擎，很多时候小事务能够更高效）。同时，需要注意的是，如果每次删除数据后，都暂停一会儿再做下一次删除，也可以将服务器上原本一次性的压力分散到一个很长的时间段中，可以大大降低对服务器的影响，还可以大大减少删除时锁的持有时间。

分解联接查询

很多高性能的应用都会对联接查询进行分解。简单地说，可以对每一个表进行一次单表查询，然后将结果在应用程序中进行联接。例如，下面这个查询：

可以分解成下面这些查询来代替：

到底为什么要这样做？乍一看，这样做并没有什么好处，原本一条查询，这里却变成多条查询，返回的结果又是一模一样的。事实上，用分解联接查询的方式重构查询有如下优势：

• 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。例如，上面查询中的tag mysql已经被缓存了，那么应用就可以跳过第一个查询。再例如，应用中已经缓存了ID为123、567、9098的内容，那么第三个查询的IN()中就可以少几个ID。
• 将查询分解后，执行单个查询可以减少锁的竞争。
• 在应用层做联接，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
• 查询本身的效率也可能会有所提升。在这个例子中，使用IN()代替联接查询，可以让MySQL按照ID顺序进行查询，这可能比随机的联接要更高效。
• 可以减少对冗余记录的访问。在应用层做联接查询，意味着对于某条记录应用只需要查询一次，而在数据库中做联接查询，则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消耗

在有些场景下，在应用程序中执行联接操作会更加有效。比如，当可以缓存和重用之前查询结果中的数据时、当在多台服务器上分发数据时、当能够使用IN()列表替代联接查询大型表时、当一次联接查询中多次引用同一张表时。

查询执行的基础

Mysql执行了一个查询的过程。根据下图可以看到，当向Mysql发送一个请求的时候，Mysql到底做了哪些：

1. 客户端给服务器发送一条SQL查询语句。
2. 服务器端进行SQL语句解析、预处理，再由优化器生成对应的执行计划。
3. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
4. 将结果返回给客户端。

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

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

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

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

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

查询状态

对于一个MySQL连接，或者一个线程，任何时刻都有一个状态，该状态表示了MySQL当前正在做什么。有很多种方式能查看当前的状态，最简单的是使用SHOW FULL PROCESSLIST命令（该命令返回结果中的Command列，其就表示当前的状态）。在一个查询的生命周期中，状态会变化很多次。MySQL官方手册中对这些状态值的含义有最权威的解释，下面将这些状态列出来，并做一个简单的解释

• Sleep：线程正在等待客户端发送新的请求
• Query：线程正在执行查询或者正在将结果发送给客户端
• Locked：在Mysql服务层，该线程正在等待表锁。在存储引擎级别实现的锁，例如Innodb 的行锁，并不会体现在线程状态中
• Analyzing and statistics：线程正在检查存储引擎的统计信息，并优化查询
• Copying to tmp table [on disk]：线程正在执行查询，并且将结果集复制到一个临时表中，这种状态一般要么是在过GROUP BY操作，要么是在进行文件排序操作，或者是在进行UNION操作。如果这个状态后面还有“on disk”标记，那表示Mysql正在将一个内存临时表放到磁盘上
• Sorting result：线程正在对结果集进行排序
  

查询优化处理

查询的生命周期的下一步是将一个SQL查询转换成一个执行计划，MySQL再依照这个执行计划和存储引擎进行交互。这包括多个子阶段：解析SQL、预处理、优化SQL执行计划。这个过程中产生的任何错误（例如，语法错误）都可能终止查询。这里不打算详细介绍MySQL的内部实现，而只是选择性地介绍其中几个独立的部分，在实际执行中，这几部分可能一起执行也可能单独执行。我们的目的是帮助大家理解MySQL如何执行查询，以便写出更优秀的查询

语法解析器和预处理

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

然后，预处理器检查生成的解析树，以查找解析器无法解析的其他语义，例如，这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。

查询优化器

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

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

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

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

• 统计信息不准确。Mysql服务器依赖存储引擎提供的统计信息来评估成本，但是有的存储引擎提供的信息是准确的，有的偏差可能非常大。例如，InnoDB因为其MVCC的架构，并不能维护一个数据表的行数的精确统计信息。
• 成本指标并不完全等同于运行查询的实际成本，因为即使统计数据是准确的，查询的成本也可能超过或者低于Mysql估算的近似值。例如，有时候某个执行计划虽然需要读取更多的页面，但是它的成本却更低。因为如果这些页面都是顺序读或者这些页面都已经在内存中，那么它的访问将很低。Mysql并不知道哪些页面是在内存中。哪些在磁盘中，所以查询在实际执行过程中到底需要多少次物理I/O是无法得知的
• Mysql的最优可能和你想的的最优是不一样的。你可能希望执行时候尽可能短，但是Mysql只是基于成本模型选择最优的执行计划，而某些时候这并不是最快的执行方式。所以，这里我们看到的根据执行计划并不是完美的模型

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

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

在执行绑定变量和存储过程的时候，动态优化和静态优化的区别非常重要。MySQL对查询的静态优化只需要做一次，但对查询的动态优化则在每次执行时都需要重新评估。有时候甚至在查询的执行过程中也会重新优化

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

• 重新定义联接表的顺序

数据表的联接并不总是按照在查询中指定的顺序进行。决定联接的顺序是优化器很重要的一个功能

• 将外连接转换为内连接

数据表的联接并不总是按照在查询中指定的顺序进行。决定联接的顺序是优化器很重要的一个功能

• 使用代数等价变换规则

MySQL可以使用一些代数等价变换规则来简化并规范表达式。它可以合并和减少一些比较，还可以移除一些恒成立和一些恒不成立的判断。例如，（5=5 AND a>5）将被改写为a>5。类似地，如果有（a5 AND b=c AND a=5

• 优化COUNT()、MIN()和MAX()

索引和列是否可为空通常可以帮助MySQL优化这类表达式。例如，要找到某一列的最小值，只需要查询对应B-tree索引最左端的记录，MySQL可以直接获取索引的第一行记录。在优化器生成执行计划的时候就可以利用这一点，在B-tree索引中，优化器会将这个表达式作为一个常数对待。类似地，如果要查找一个最大值，也只需读取B-tree索引的最后一条记录。如果MySQL使用了这种类型的优化，那么在EXPLAIN中就可以看到“Select tables optimized away”。从字面意思可以看出，它表示优化器已经从执行计划中移除了该表，并以一个常数代替

• 预估并转化为常数表达式

当MySQL检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理

• 覆盖索引扫描

当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需要的数据，而无须查询对应的数据行

• 子查询优化

MySQL在某些情况下可以将子查询转换为一种效率更高的形式，从而减少多个查询多次对数据进行访问。

• 提取终止查询

在发现已经满足查询需求的时候，MySQL总是能够立刻终止查询。一个典型的例子就是当使用了LIMIT子句的时候。除此之外，MySQL在其他几类情况下也会提前终止查询，例如发现了一个不成立的条件，这时MySQL可以立刻返回一个空结果。从下面的例子中可以看到这一点：

从这个例子可以看到，询在优化阶段就已经终止。除此之外，MySQL在执行过程中，如果发现某些特殊的条件，则会提前终止查询

• 等值传播

如果两列的值可以通过等式联接，那么MySQL能够把其中一列的WHERE条件传递到另一列上。例如，我们看下面的查询：

因为这里使用了film_id字段进行等值连接，MySQL知道这里的WHERE子句不仅适用于film表，而且对于film_actor表同样适用。如果使用的是其他的数据库管理系统，可能还需要手动通过一些条件来告知优化器这个WHERE条件适用于两个表，那么写法就会如下：

在Mysql中这是不必要的，这样写反而会让查询更难维护

• 列表IN的比较

在很多数据库服务器中，IN完全等同于多个OR条件的子句，因为这两者完全是等价的。在MySQL中这点是不成立的，MySQL将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个O（logn）复杂度的操作，等价地转换成OR查询的复杂度为O（n），对于IN()列表中有大量取值的时候，MySQL的处理速度将会更快

上面列举的远不是Mysql优化器的全部，Mysql还会做大量其他的优化，即使本章全部用来描述这一点也会篇幅不足，但上面的这些例子已经足以让大家明白优化器的复杂性和智能性。

表和索引的统计信息

MySQL架构由多个层次组成。在服务器层有查询优化器，却没有保存数据和索引的统计信息。统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息（也可以按照不同的方式存储统计信息）。
因为服务器没有存储任何统计信息，所以MySQL查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息。存储引擎则给优化器提供对应的统计信息，包括：每个表或者索引有多少个页面、每个表的每个索引的基数是多少、数据行和索引的长度是多少、索引的分布信息等。优化器根据这些信息来选择一个最优的执行计划。

Mysql是如何执行联接查询

Mysql中使用的术语“联接”的范围可能比你熟悉的更广泛。总的来说，Mysql认为每一个查询都是联接-不仅是匹配两张表中对应行的查询，而是每一个查询、每一个片段（包括子查询、甚至基于单表的SELECT）都是联接。因为，理解MYSQL如何执行联接查询都是非常重要的。

所以，理解Mysql如何执行UNION查询至关重要。我们先来看一个UNION查询的例子。**对于UNION查询，Mysql先将一系列的单个查询结果放到一个临时表中，然后再读出临时表中的数据来完成UNION查询。**在Mysql的概念中，每个查询都是一次联接，所以读取临时表的结果也是一次联接。

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

执行计划

和很多关系型数据不同，Mysql并不会生成查询字节码来执行查询。Mysql生成查询的一颗指令树，然后根据查询执行引擎完成这颗指令树并返回结果。最终的执行计划包含了重构查询的全部信息。如果你对某个查询执行EXPLAIN EXTENDED后，再执行SHOW WARNINGS，就可以看到重构出的查询

任何多表查询都可以使用一颗树来表示，例如，可以按照如图执行一个四表的联接操作

多表联接的一种方式
在计算科学中，这被称为一颗平衡数。但是，这并不是Mysql执行查询的方式。MySQL总是从一个表开始，一直嵌套循环、回溯完成所有表联接。所以，MySQL的执行计划总是如图所示，是一棵左侧深度优先的树

排序优化

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

查询执行引擎

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

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

存储引擎接口有着非常丰富的功能，但是底层接口却只有几十个，这些接口像“搭积木”一样能够完成查询的大部分操作。例如，有一个查询某个索引的第一行的接口，其还有查询某个索引条目的下一个条目的功能，有了这两个功能就可以完成全索引扫描的操作了。这种简单的接口模式，让MySQL的存储引擎的插件式架构成为可能，但是正如前面的讨论，这也给优化器带来了一定的限制。

并不是所有的操作都由handler完成。例如，当MySQL需要进行表锁的时候。handler可能会实现自己的级别的、更细粒度的锁，如InnoDB就实现了自己的行级基本锁，但这并不能代替服务器层的表锁。正如我们在第1章所介绍的，如果是所有存储引擎共有的特性则由服务器层实现，比如时间和日期函数、视图、触发器等。

将结果返回给客户端

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

MySQL将结果集返回客户端是一个增量且逐步返回的过程。例如，我们回头看看前面的联接操作，一旦服务器处理完最后一个联接表，开始生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了。这样处理有两个好处：服务器端无须存储太多的结果，也就不会因为要返回太多结果而消耗太多内存。另外，这样的处理也可让MySQL客户端第一时间获得返回的结果。[12]结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送，再通过TCP协议进行传输，在TCP传输的过程中，可能对MySQL的封包进行缓存，然后批量传输。

Mysql查询优化器的局限性

MySQL所实现的查询执行方式并不是对每种查询都是最优的。不过还好，MySQL查询优化器只对少部分查询不适用，而且我们往往可以通过改写查询让MySQL高效地完成工作。

UNION的限制

有时候，Mysql无法将限制条件从UNION的外层“下推”到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。

如果希望UNION的各个子句能够根据LIMIT只取部分结果集，或者希望能够先排好序再合并结果集的话，就需要在UNION的各个子句中分别使用这些子句。例如，想将两个子查询结果联合起来，然后再取前20条记录，那么MySQL会将两个表存放到同一个临时表中，然后再取出前20行记录：

这条查询将会把actor表中的200条记录和customer表中的599条记录存放在一个临时表中，然后再从临时表中取出前20条。

可以通过在UNION的两个子查询中分别加上一个LIMIT 20来减少临时表中的数据：

现在临时表只包含40条记录了，除了考虑性能之外，在这里还需要注意一点：从临时表中取出数据的顺序并不是一定的，所以如果想获得正确的顺序，还需要在最后的LIMIT操作前加上一个全局的ORDER BY操作

等值传递

某些时候，等值传递会带来一些意想不到的额外消耗。例如，考虑一列上的巨大IN()列表，优化器知道它将等于其他表中的一些列，这是由于WHERE、ON或USING子句使列彼此相等。

优化器通过将列表复制到所有相关表中的相应列来“共享”列表。通常，因为各个表新增了过滤条件，所以优化器可以更高效地从存储引擎过滤记录。但是如果这个列表非常大，则会导致优化和执行都会变慢。

举例：
假设我们有两个表 orders 和 customers：

• orders 表：包含订单信息。

CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  order_date DATE,
  amount DECIMAL(10, 2)
);

• customers 表：包含客户信息。

CREATE TABLE customers (
  customer_id INT,
  customer_name VARCHAR(100),
  region VARCHAR(50)
);

我们需要从这些表中查找特定客户的订单信息，并且我们有一个巨大的 IN() 列表指定客户ID。假设 customer_id 列上有一个索引。

SELECT orders.order_id, orders.amount, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
WHERE orders.customer_id IN (1, 2, 3, ..., 10000);

1. 等值传递：
   • 优化器会看到 orders.customer_id 等于 customers.customer_id。
   • 优化器会将 IN (1, 2, 3, ..., 10000) 列表复制到 customers.customer_id 列，以便在 customers 表上也应用这个过滤条件。
2. 优化器处理：
   • 优化器需要处理这个巨大的列表，并将其应用到 customers 表和 orders 表。
   • 这会导致优化阶段的计算量大大增加，因为优化器必须考虑这个列表对每个表的影响。
3. 执行阶段：
   • 在执行阶段，MySQL必须在 orders 和 customers 表上都应用这个巨大的 IN() 列表。
   • 每个表的查询执行器都需要评估每条记录是否在这个巨大的列表中，这会导致执行时间增加。

并行执行

MySQL无法利用多核特性来并行执行查询。很多其他的关系数据库能够提供这个特性，但是MySQL做不到。这里特别指出是想告诉读者不要花时间去尝试寻找并行执行查询的方法。

在同一个表中查询和更新

Mysql不允许对同一张表同时进行查询和更新。这其实并不是优化器的限制，如果你清楚MySQL是如何执行查询的，就可以避免这种情况。
下面是一段无法运行的SQL语句，尽管这是一段符合标准的SQL语句。这个查询会将表中每一行的c字段值更新为和该行的type字段值相同的行数量：

可以通过生成表的形式来绕过上面的限制，因为Mysql只会把这个表当作一个临时表来处理。实际上，这执行了两个查询：一个是子查询中的SELECT语句，另外一个是多表UPDATE查询，其中包含原表和子查询的联接结果。子查询会在UPDATE语句打开表之前就完成，所以下面的查询将会正常执行：

优化特定类型的查询

优化Count查询

count的作用

COUNT()是一个特殊的函数，有两种非常不同的作用：它可以统计某列的值的数量，在统计列值时要求列值是非空的（不统计NULL）。如果在COUNT()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数

COUNT()的另一个作用是统计结果集的行数。当MySQL确认括号内的表达式值不可能为空时，实际上就是在统计行数。最简单的就是当我们使用COUNT（）的时候，这种情况下通配符并不会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数。

简单优化

如何在一个查询中统计同一列的不同值的数量，以减少查询的语句量。例如，假设可能需要通过一个查询返回各种不同颜色的商品数量，此时不能使用OR语句（比如，SELECT COUNT（color='blue’OR color=‘red’）FROM items；），因为这样做无法区分不同颜色的商品数量；也不能在WHERE条件中指定颜色（比如，SELECT COUNT（*）FROM items WHERE color='blue’AND color=‘RED’；），因为颜色的条件是互斥的。

下面的查询可以在一定程度上解决这个问题：

也可以使用COUNT()而不是SUM()实现同样的目的，只需要将满足条件设置为真，不满足条件设置为NULL即可：

使用近似值

有时候，某些业务场景并不要求完全精确的统计值，此时可以用近似值来代替。EXPLAIN出来的优化器估算的行数就是一个不错的近似值，执行EXPLAIN并不需要真正地去执行查询，所以成本很低。

很多时候，计算精确值非常复杂，而计算近似值则非常简单。曾经有一个客户希望我们统计他的网站的当前活跃用户数是多少，这个活跃用户数保存在缓存中，过期时间为30分钟，所以每隔30分钟需要重新计算并放入缓存。这个活跃用户数本身就不是精确值，所以使用近似值代替是可以接受的。另外，如果要精确统计在线人数，使用WHERE条件会很复杂，一方面需要剔除当前非活跃用户，另一方面还要剔除系统中某些特定ID的“默认”用户，去掉这些约束条件对总数的影响很小，但却可能提升该查询的性能。更进一步的优化则可以尝试删除DISTINCT这样的约束来避免文件排序。这样重写过的查询比原来精确统计的查询快很多，而返回的结果则几乎相同

更复杂的优化

通常来说，COUNT()查询需要扫描大量的行（意味着要访问大量数据）才能获得精确的结果，因此是很难优化的。除了前面提到的方法，在MySQL层面还能做的就只有索引覆盖扫描了。

如果这还不够，那就需要考虑修改应用的架构，可以增加类似Memcached这样的外部缓存系统。不过，可能很快你就会陷入一个熟悉的困境：“快速、精确和实现简单”。三者永远只能满足其二，必须舍掉一个。

优化联接查询

1. 确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到联接的顺序。当表A和表B用列c联接的时候，如果优化器的联接顺序是B、A，那么就不需要在B表的对应列上建索引。没有用到的索引只会带来额外的负担。一般来说，除非有其他理由，否则只需在联接顺序中的第二个表的相应列上创建索引
2. 确保任何GROUP BY和ORDER BY中的表达式只涉及一个表中的列，这样MySQL才有可能使用索引来优化这个过程
3. 当升级MySQL的时候需要注意：联接语法、运算符优先级等其他可能会发生变化的地方。因为以前是普通联接的地方可能会变成笛卡儿积，不同类型的联接可能会生成不同的结果，甚至会产生语法错误。

使用WITH ROLLUP优化GROUP BY

分组查询的一个变种就是要求MySQL对返回的分组结果再做一次超级聚合。可以使用WITH ROLLUP子句来实现这种逻辑，但可能优化得不够。可以通过EXPLAIN来观察其执行计划，特别要注意分组是否是通过文件排序或者临时表实现的。然后再去掉WITH ROLLUP子句来看执行计划是否相同

很多时候，如果可以，在应用程序中做超级聚合是更好的，虽然这需要给客户端返回更多的结果。也可以在FROM子句中嵌套使用子查询，或者是通过一个临时表存放中间数据，然后和临时表执行UNION来得到最终结果

最好的办法是尽可能地将WITH ROLLUP功能转移到应用程序中处理

优化LIMIT和OFFSET子句

在系统中需要进行分页操作的时候，我们通常会使用LIMIT加上偏移量的办法实现，同时加上合适的ORDER BY子句。如果有对应的索引，通常效率会不错，否则，MySQL需要做大量的文件排序操作

一个非常常见又令人头疼的问题是，在偏移量非常大的时候，例如，可能是LIMIT 1000，20这样的查询，这时MySQL需要查询10020条记录然后只返回最后20条，前面10000条记录都将被抛弃，这样的代价非常高。如果所有的页面被访问的频率都相同，那么这样的查询平均需要访问半个表的数据。要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能

优化此类分页查询的一个最简单的办法就是尽可能地使用**索引覆盖（可以是主键索引，也可以是其他）**扫描，而不是查询所有的行。然后根据需要做一次联接操作再返回所需的列。在偏移量很大的时候，这样做的效率会有非常大的提升。考虑下面的查询：

如果这个表非常大，那么这个查询最好改成下面的样子：

这种“延迟联接”之所以有效，是因为它允许服务器在不访问行的情况下检查索引中尽可能少的数据，然后，一旦找到所需的行，就将它们与整个表联接，以从该行中检索其他列。类似的技术也适用于带有LIMIT子句的联接。

LIMIT和OFFSET的问题，其实是OFFSET的问题，它会导致MySQL扫描大量不需要的行然后再抛弃掉。如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET。例如，若需要按照租借记录做翻页，那么可以根据最新一条租借记录向回追溯，这种做法可行是因为租借记录的主键是单调增长的。首先使用下面的查询获得第一组结果：

假设上面的查询返回的是主键为16，049到16，030的租借记录，那么下一页查询就可以从16，030这个点开始：

该技术的好处是无论翻页到多么靠后，其性能都会很好

其他优化办法还包括使用预先计算的汇总表，或者联接到一个冗余表，冗余表只包含主键列和需要做排序的数据列。

优化SQL CALC FOUND ROWS

分页的时候，另一个常用的技巧是在LIMIT语句中加上SQL_CALC_FOUND_ROWS提示（hint），这样就可以获得去掉LIMIT以后满足条件的行数，因此可以作为分页的总数。看起来，MySQL做了一些非常“高深”的优化，像是通过某种方法预测了总行数。但实际上，MySQL只有在扫描了所有满足条件的行以后，才会知道行数，所以加上这个提示以后，不管是否需要，MySQL都会扫描所有满足条件的行，然后再抛弃掉不需要的行，而不是在满足LIMIT的行数后就终止扫描。所以该提示的代价可能非常高。

一个更好的设计是将具体的页数换成“下一页”按钮，假设每页显示20条记录，那么我们每次查询时都是用LIMIT返回21条记录并只显示20条，如果第21条存在，那么就显示“下一页”按钮，否则就说明没有更多的数据，也就无须显示“下一页”按钮了

另一种做法是先获取并缓存较多的数据——例如，缓存1000条——然后每次分页都从这个缓存中获取。这样做可以让应用程序根据结果集的大小采取不同的策略，如果结果集小于1000，就可以在页面上显示所有的分页链接，因为数据都在缓存中，所以这样做不会对性能造成影响。如果结果集大于1000，则可以在页面上设计一个额外的“找到的结果多于1000条”之类的按钮。这两种策略都比每次生成全部结果集再抛弃不需要的数据的效率高很多。

另一种做法是先获取并缓存较多的数据——例如，缓存1000条——然后每次分页都从这个缓存中获取。这样做可以让应用程序根据结果集的大小采取不同的策略，如果结果集小于1000，就可以在页面上显示所有的分页链接，因为数据都在缓存中，所以这样做不会对性能造成影响。如果结果集大于1000，则可以在页面上设计一个额外的“找到的结果多于1000条”之类的按钮。这两种策略都比每次生成全部结果集再抛弃不需要的数据的效率高很多。

优化UNION查询

MySQL总是通过创建并填充临时表的方式来执行UNION查询，因此很多优化策略在UNION查询中都没法很好地被使用。经常需要手工地将WHERE、LIMIT、ORDER BY等子句“下推”到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化（例如，直接将这些子句冗余地写一份到各个子查询）。

除非你确实需要服务器消除重复的行，否则一定要使用UNION ALL，这一点很重要。如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。即使有ALL关键字，MySQL仍然会使用临时表存储结果。事实上，MySQL总是将结果放入临时表，然后再读出，再返回给客户端，虽然很多时候这样做是没有必要的（例如，MySQL可以直接把这些结果返回给客户端）。