MySQL之查询性能优化(四)

查询性能优化

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

一般来说，不需要去理解MySQL通信协议的内部实现细节，只需要大致理解通信协议是如何工作的。MySQL客户端和服务器之间的通信协议是"半双工"的，这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。所以，我们也无法也无须将一个消息切成小块独立来发送。这种协议让MySQL通信简单快速，但是也从很多地方限制了MySQL.一个明显的限制是，这意味着没法进行流量控制。一旦一端开始发送消息，另一端要接收完整个消息才能响应它。这就像来回抛球的游戏:在任何时刻，只有一个人能控制球，而且只有控制球的人才能将球抛回去(发送消息)。一旦客户端发送了请求，它能做的事情就只是等待结果了。相反的，一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整接收整个返回结果，而不能简单地只取前面几条结果，然后让服务器停止发送数据。这种情况下，客户端若接收完整的结果，然后取前面几条需要的结果，或者接收完几条结果后就"粗暴"地断开连接，都不是好注意。这也是在必要的时候一定要在查询中加上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官方手册中对这些状态值的含义有权威的解释，下面将这些状态列出来，并做一个简单的解释.

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

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

查询缓存(这里是指Query Cache)

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果(Percona版本的MySQL中提供了一个新的特性，可以在计算查询语句哈希值时，先将注释移除再计算哈希值，这对于不同注释的相同查询可以命中相同的查询缓存结果)。这种情况下查询就会进入下一个阶段的处理。如果当前的查询恰好命中了查询缓存，那么再返回查询结果之前MySQL会检查一次用户权限。这仍然时无须解析查询SQL语句的，因为在查询缓存中已经存放了当前查询需要访问的表信息。如果权限没有问题，MySQL会跳过所有其他阶段，直接从缓存中拿到结果并返回给客户端。这种情况下，查询不会被解析，不用生成执行计划，不会被执行

查询优化处理

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

语法解析器和预处理

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

查询优化器

现在语法书被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。最初，成本的最小单位是随机读取一个4K数据页的成本，后来(成本计算公式)变得更加复杂，并且引入了一些"因子"来估算某些操作的代价，如当执行一次WHERE条件比较的成本.可以通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本;

mysql> SELECT SQL_NO_CACHE COUNT(*) FROM film_actor;
+----------+
| COUNT(*) |
+----------+
|     5462 |
+----------+
1 row in set (0.07 sec)
mysql> SHOW STATUS LIKE 'Last_query_cost';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 1104.399000 |
+-----------------+-------------+
1 row in set (0.07 sec)

这个结果表示MySQL的优化器认为大概需要做1040个数据页的随机查找才能完成上面的查询。这是根据一系列的统计信息计算得来的:每个表或者索引的页面个数、索引的基数(索引中不同值得数量)、索引和数据行得长度、索引分布情况。优化器再评估成本的时候并不考虑任何层面的缓存，它假设读取任何数据都需要一次磁盘IO.有很多种原因会导致MySQL优化器选择错误的执行计划，如下所示:

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