查询性能优化(二)

查询执行的基础

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

MySQL 执行一个查询的过程。根据图 6-1，我们可以看到当向 MySQL 发送一个请求的时候，MySQL 到底做了些什么:

图6_1查询执行路径图 6-1: 查询执行路径

客户端发送一条查询给服务器
服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
服务器端进行 SQL 解析、预处理，再由优化器生成对应的执行计划。
MySQL 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询
将结果返回给客户端

上面的每一步都比想象的复杂。

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

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

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

客户端用一个单独的数据包将查询传给服务器。这也是为什么当查询的语句很长的时候，参数 max_allowed_packet 就特别重要了(如果查询太大，服务端会拒绝接收更多的数据并抛出相应错误)。
一旦客户端发送了请求，它能做的事情就只是等待结果了。

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

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

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

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

我们看看当使用 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 的时候指定这个属性，这会让整个连接都使用不缓存的方式处理结果集:

1	`my $dbh = DBI->connect('DBI:mysql:;mysql_use_result=1', 'user', 'p4ssword');`

查询状态:

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

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

了解这些状态的基本含义非常有用，这可以让你很快地了解当前 "谁正在持球"。
在一个繁忙的服务器上，可能会看到大量的不正常的状态，例如 statistics 正占用大量的时间。

查询优化处理

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

语法解析器和预处理

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

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

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

查询优化器

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

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

last_query_cost使用1

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