上一篇：《第8章-2 查询执行的基础》，接着对查询过程进一步学习
MySQL的客户端/服务器通信协议
一般来说，不需要去理解MySQL通信协议的内部实现细节，只需要大致理解通信协议是如何工作的。MySQL的客户端和服务器之间的通信协议是“半双工”的，这意味着，在任何时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。所以，我们无法也无须将一个消息切成小块来独立发送。
这种协议让MySQL通信变得简单快速，但是也从很多地方限制了MySQL。一个明显的限制是，这意味着没法进行流量控制。一旦一端开始发送消息，另一端要接收完整个消息才能响应它。这就像来回抛球的游戏：在任何时刻，只有一个人能控制球，而且只有控制球的人才能将球抛回去（发送消息）。
客户端用一个单独的数据包将查询传给服务器。这也是为什么当查询的语句很长的时候，参数max_allowed_packet就特别重要了。 [4] 一旦客户端发送了请求，它能做的事情就只是等待结果了。
然而，一般的服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果，而不能简单地只取前面几条结果，然后让服务器停止发送数据。在这种情况下，客户端若接收完整的结果，然后取前面几条需要的结果，或者接收完几条结果后就“粗暴”地断开连接，都不是好主意。这也是在必要的时候一定要在查询中加上LIMIT限制的原因。
换一种方式解释这种行为：当客户端从服务器取数据时，看起来是一个拉数据的过程，但实际上是MySQL在向客户端推送数据的过程。客户端不断地接收从服务器推送的数据，客户端也没法让服务器停下来。客户端像是“从消防水管喝水”（这是一个术语）。
多数连接MySQL的库函数都可以获得全部结果集并将结果缓存到内存里，还可以逐行获取需要的数据。默认一般是获得全部结果集并将它们缓存到内存中。MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通常可以减少服务器的压力，让查询能够早点结束、早点释放相应的资源。
当使用多数连接MySQL的库函数从MySQL获取数据时，其结果看起来都像是从MySQL服务器获取数据，而实际上都是从这个库函数的缓存获取数据。多数情况下这没什么问题，但是在需要返回一个很大的结果集的时候，这样做并不好，因为库函数会花很多时间和内存来存储所有的结果集。如果能够尽早开始处理这些结果集，就能大大减少内存的消耗，在这种情况下可以不使用缓存来记录结果而是直接处理。这样做的缺点是，对于服务器来说，需要查询完成后才能释放资源，所以在和客户端交互的整个过程中，服务器的资源都是被这个查询所占用的（可以使用SQL_BUFFER_RESULT，后面会介绍这方面的知识）。
我们看看当使用PHP的时候是什么情况。下面是我们连接MySQL的通常写法：
<?php
$link = mysql_connect('localhost', 'user', 'p4ssword');
$result = mysql_query('SELECT * FROM HUGE_TABLE', $link);
while ( $row = mysql_fetch_array($result) ) {
// Do something with result
}
?>这段代码看起来像是只有当你需要的时候，才通过循环从服务器端取出数据。而实际上，在上面的代码中，在调用mysql_query()的时候，PHP就已经将整个结果集缓存到内存中了。while循环只是从这个缓存中逐行取出数据。然而，如果使用下面的查询，用mysql_unbuffered_query()代替mysql_query()，PHP则不会缓存结果：
<?php
$link = mysql_connect('localhost', 'user', 'p4ssword');
$result = mysql_unbuffered_query('SELECT * FROM HUGE_TABLE', $link);
while ( $row = mysql_fetch_array($result) ) {
// Do something with result
}
?>不同的编程语言处理缓存的方式不同。例如，在Perl的DBD：mysql驱动中需要指定C客户端库的mysql_use_result属性（默认是mysql_buffer_result）。下面是一个例子：
#!/usr/bin/perl
use DBI;
my $dbh = DBI->connect('DBI:mysql:;host=localhost', 'user', 'p4ssword');
my $sth = $dbh->prepare('SELECT * FROM HUGE_TABLE',
{ mysql_use_result => 1 });
$sth->execute();
while ( my $row = $sth->fetchrow_array() ) {
# Do something with result
}注意，上面的prepare()调用指定了mysql_use_result属性为1，所以应用将直接使用返回的结果集而不会将其缓存。也可以在连接MySQL的时候指定这个属性，这会让整个连接都使用不缓存的方式处理结果集：
my $dbh = DBI->connect('DBI:mysql:;
mysql_use_result=1', 'user', 'p4ssword');查询状态
对于一个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
线程正在对结果集进行排序。
了解这些状态的基本含义非常有用，这可以让你很快地了解当前“谁正在持球”。在一个繁忙的服务器上，可能会看到大量的不正常的状态，例如，statistics正占用大量的时间。这通常表示，某个地方有异常了。
查询优化处理
查询的生命周期的下一步是将一个SQL查询转换成一个执行计划，MySQL再依照这个执行计划和存储引擎进行交互。这包括多个子阶段：解析SQL、预处理、优化SQL执行计划。这个过程中产生的任何错误（例如，语法错误）都可能终止查询。这里不打算详细介绍MySQL的内部实现，而只是选择性地介绍其中几个独立的部分，在实际执行中，这几部分可能一起执行也可能单独执行。我们的目的是帮助大家理解MySQL如何执行查询，以便写出更优秀的查询。
语法解析器和预处理
首先，MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询。例如，它将验证是否使用了错误的关键字，使用关键字的顺序是否正确，或者它还会验证引号是否能前后正确匹配。
然后，预处理器检查生成的解析树，以查找解析器无法解析的其他语义，例如，这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。下一步预处理器会验证权限。这通常很快，除非服务器上有非常多的权限配置。
查询优化器
现在解析树被认为是合法的了，并且由优化器将其转化成查询执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。最初，成本的最小单位是随机读取一个4KB数据页的成本，后来成本计算公式变得更加复杂，并且引入了一些“因子”来估算某些操作的代价，如执行一次WHERE条件比较的成本。可以通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本：
SELECT SQL_NO_CACHE COUNT(*) FROM film_actor;
SHOW STATUS LIKE 'Last_query_cost';
这个结果表示，MySQL的优化器认为大概需要做1040个数据页的随机查找才能完成上面的查询。这是根据一系列的统计信息计算得来的：每个表或者索引的页面个数、索引的基数（索引中不同值的数量）、索引和数据行的长度、索引分布情况。优化器在评估成本的时候并不考虑任何层面的缓存带来的影响，它假设读取任何数据都需要一次磁盘I/O。
有很多种原因会导致MySQL优化器选择错误的执行计划，如下所示：
● 统计信息不准确。MySQL服务器依赖存储引擎提供的统计信息来评估成本，但是有的存储引擎提供的信息是准确的，有的偏差可能非常大。例如，InnoDB因为其MVCC的架构，并不能维护一个数据表的行数的精确统计信息。
● 成本指标并不完全等同于运行查询的实际成本，因此即使统计数据是准确的，查询的成本也可能超过或者低于MySQL估算的近似值。例如，有时候某个执行计划虽然需要读取更多的页面，但是它的成本却更低。因为如果这些页面都是顺序读或者这些页面都已经在内存中的话，那么它的访问成本将很低。MySQL并不知道哪些页面在内存中、哪些在磁盘中，所以查询在实际执行过程中到底需要多少次物理I/O是无法得知的。
● MySQL的最优可能和你想的最优不一样。你可能希望执行时间尽可能短，但是MySQL只是基于其成本模型选择最优的执行计划，而有些时候这并不是最快的执行方式。所以，这里我们看到的根据执行成本来选择执行计划并不是完美的模型。
● MySQL从不考虑其他并发执行的查询，这可能会影响到当前查询的速度。
● MySQL也并不是任何时候都是基于成本的优化。它有时也会基于一些固定的规则，例如，如果存在全文搜索的MATCH()子句，则在存在FULLTEXT索引的时候就使用全文索引。即使有时候使用其他索引和WHERE条件可以远比这种方式要快，MySQL也仍然会使用对应的全文索引。
● MySQL不会考虑不受其控制的操作的成本，例如，执行存储函数或者用户自定义函数的成本。
● 后面我们还会看到，优化器有时候无法估算所有可能的执行计划，所以它可能错过实际上最优的执行计划。
MySQL的查询优化器是一个非常复杂的软件，它使用了很多优化策略来生成一个最优的执行计划。优化策略可以简单地分为两种，一种是静态优化，一种是动态优化。静态优化可以直接对解析树进行分析，并完成优化。例如，优化器可以通过一些简单的代数变换将WHERE条件转换成另一种等价形式。静态优化不依赖于特别的数值，如WHERE条件中带入的一些常数等。静态优化在第一次完成后就一直有效，即使使用不同的参数重复执行查询也不会发生变化，可以认为这是一种“编译时优化”。
然而，动态优化则和查询的上下文有关，也可能和很多其他因素有关，例如WHERE条件中的取值、索引中条目对应的数据行数等。这需要在每次查询的时候都重新评估，可以认为这是“运行时优化”。
在执行绑定变量和存储过程的时候，动态优化和静态优化的区别非常重要。MySQL对查询的静态优化只需要做一次，但对查询的动态优化则在每次执行时都需要重新评估。有时候甚至在查询的执行过程中也会重新优化。 （例如，在联接操作中，范围检查的执行计划会针对每一行重新评估进行索引。可以通过EXPLAIN执行计划中的Extra列是否有“range checked for each record”来确认这一点。该执行计划还会增加select_full_range_join这个服务器变量的值）
下面是一些MySQL能够处理的优化类型。
重新定义联接表的顺序
数据表的联接并不总是按照在查询中指定的顺序进行。决定联接的顺序是优化器很重要的一个功能，本章后面将深入介绍这一点。
将外联接转化成内联接
并不是所有的OUTER JOIN语句都必须以外联接的方式执行。诸多因素，例如WHERE条件、库表结构都可能会让外联接等价于一个内联接。MySQL能够识别这一点并重写查询，让其可以调整联接顺序。
使用代数等价变换规则
MySQL可以使用一些代数等价变换规则来简化并规范表达式。它可以合并和减少一些比较，还可以移除一些恒成立和一些恒不成立的判断。例如，（5=5 AND a>5）将被改写为a>5。类似地，如果有（a<b AND b=c）AND a=5则会改写为b>5 AND b=c AND a=5。这些规则对于编写条件语句很有用，我们将在本章后面的内容中继续讨论。
优化COUNT()、MIN()和MAX()
索引和列是否可为空通常可以帮助MySQL优化这类表达式。例如，要找到某一列的最小值，只需要查询对应B-tree索引最左端的记录，MySQL可以直接获取索引的第一行记录。在优化器生成执行计划的时候就可以利用这一点，在B-tree索引中，优化器会将这个表达式作为一个常数对待。类似地，如果要查找一个最大值，也只需读取B-tree索引的最后一条记录。如果MySQL使用了这种类型的优化，那么在EXPLAIN中就可以看到“Select tables optimized away”。从字面意思可以看出，它表示优化器已经从执行计划中移除了该表，并以一个常数代替。
预估并转化为常数表达式当MySQL检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数
进行优化处理。例如，一个用户自定义变量在查询中没有发生变化时就可以将其转换为一个常数。数学表达式则是另一种典型的例子。
让人惊讶的是，在优化阶段，有时候一个查询也能够转化为一个常数。一个例子是在索引列上执行MIN()函数。甚至是主键或者唯一键查找语句也可以被转换为常数表达式。如果WHERE子句中使用了该类索引的常数条件，MySQL可以在查询开始阶段就先查找到这些值，这样优化器就能够知道并将其转换为常数表达式。下面是一个例子：
EXPLAIN SELECT film.film_id, film_actor.actor_id
FROM film
INNER JOIN film_actor USING(film_id)
WHERE film.film_id = 1;
MySQL分两步来执行这个查询，也就是上面执行计划的两行输出。第一步先从film表找到需要的行。因为在film_id列上有主键索引，所以MySQL优化器知道这只会返回一行数据，优化器在生成执行计划的时候，就已经通过索引信息知道将返回多少行数据了。因为查询优化器已经明确知道有多少个值（WHERE条件中的值）需要做索引查询，所以这里的表访问类型是const。
在执行计划的第二步时，MySQL将第一步中返回的film_id列当作一个已知取值的列来处理。因为优化器清楚在第一步执行完成后，该值就会是明确的了。注意，正如在第一步中一样，使用film_actor字段对表的访问类型也是const。
另一种会看到常数条件的情况是通过等式将常数值从一个表传到另一个表，这可以通过
WHERE、USING或者ON语句来限制某列取值为常数。在上面的例子中，因为使用了USING子句，所以优化器知道这会限制film_id在整个查询过程中始终都是一个常量——因为它必须等于WHERE子句中的那个取值。
覆盖索引扫描
当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需要的数据，而无须查询对应的数据行，这在前面的章节中已经讨论过了。
子查询优化
MySQL在某些情况下可以将子查询转换为一种效率更高的形式，从而减少多个查询多次对数据进行访问。
提前终止查询
在发现已经满足查询需求的时候，MySQL总是能够立刻终止查询。一个典型的例子就是当使用了LIMIT子句的时候。除此之外，MySQL在其他几类情况下也会提前终止查询，例如发现了一个不成立的条件，这时MySQL可以立刻返回一个空结果。从下面的例子中可以看到这一点：
EXPLAIN SELECT film.film_id FROM film WHERE film_id = -1;
从这个例子中可看到，查询在优化阶段就已经终止。除此之外，MySQL在执行过程中，如果发现某些特殊的条件，则会提前终止查询。当查询执行引擎需要检索“不同取值”或者判断存在性的时候，MySQL都可以使用这类优化。例如，我们现在需要找到没有演员的所有电影（一部电没有演员，是有点儿奇怪。不过在示例数据库Sakila中，影片Slacker Liaisons中就没有任何演员，它的描述是“鲨鱼和见过中国古代鳄鱼的学生的简短传说）：
SELECT film.film_id
FROM film
LEFT OUTER JOIN film_actor USING(film_id)
WHERE film_actor.film_id IS NULL;这个查询将会过滤掉所有有演员的电影。每一部电影可能都会有很多的演员，但是上面的查询一旦找到任何一个演员，就会停止并立刻判断下一部电影，因为只要有一名演员，那么WHERE条件就会过滤掉这部电影。类似这种“不同值/不存在”的优化一般可用于DISTINCT、NOT EXIST()或者LEFT JOIN类型的查询。
等值传播
如果两列的值可通过等式联接，那么MySQL能够把其中一列的WHERE条件传递到另一列上。例如，我们看下面的查询：
SELECT film.film_id
FROM film
INNER JOIN film_actor USING(film_id)
WHERE film.film_id > 500;因为这里使用了film_id字段进行等值联接，MySQL知道这里的WHERE子句不仅适用于film表，而且对于film_actor表同样适用。如果使用的是其他的数据库管理系统，可能还需要手动通过一些条件来告知优化器这个WHERE条件适用于两个表，那么写法就会如下：
SELECT film.film_id
FROM film
INNER JOIN film_actor USING(film_id)
WHERE film.film_id > 500
AND film_actor.film_id > 500;在MySQL中这是不必要的，这样写反而会让查询更难维护。
列表IN()的比较
在很多数据库服务器中，IN()完全等同于多个OR条件的子句，因为这两者是完全等价的。在MySQL中这点是不成立的，MySQL将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个O（logn）复杂度的操作，等价地转换成OR查询的复杂度为O（n），对于IN()列表中有大量取值的时候，MySQL的处理速度将会更快。
上面列举的远不是MySQL优化器的全部，MySQL还会做大量其他的优化，即使本章全部用来描述这一点也会篇幅不足，但上面的这些例子已经足以让大家明白优化器的复杂性和智能性了。如果说从上面这段讨论中我们应该学到什么，那就是不要自以为比优化器更聪明。最终你可能会占一些便宜，但是有可能会使查询变得更加复杂而难以维护，而最终的收益为零。让优化器按照它的方式工作就可以了。
当然，虽然优化器已经很智能了，但是有时候也无法给出最优的结果。有时候你可能比优化器更了解数据，例如，由于应用逻辑使得某些条件总是成立；还有时候，优化器缺少某种功能特性，如哈希索引；再如前面提到的，从优化器的执行成本角度评估出来的最优执行计划，在实际运行中可能比其他的执行计划更慢。
如果能够确认优化器给出的不是最佳选择，并且清楚背后的原理，那么也可以帮助优化器做进一步的优化。例如，可以在查询中添加hint提示 [8] ，也可以重写查询，或者重新设计更优的库表结构，或者添加更合适的索引。
表和索引的统计信息
重新回忆一下图1-1，MySQL架构由多个层次组成。在服务器层有查询优化器，却没有保存数据和索引的统计信息。统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息（也可以按照不同的方式存储统计信息）。
因为服务器没有存储任何统计信息，所以MySQL查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息。存储引擎则给优化器提供对应的统计信息，包括：每个表或者索引有多少个页面、每个表的每个索引的基数是多少、数据行和索引的长度是多少、索引的分布信息等。优化器根据这些信息来选择一个最优的执行计划。在后面的小节中我们将看到统计信息是如何影响优化器的，帮助你理解MySQL在访问表和索引时查询和索引的关系。
优化通常需要三管齐下：不做、少做、快速地做。
上一篇：《第8章-2 查询执行的基础》
下一篇：