查询性能的优化

不信网恋

MySQL 客户端/服务端 协议
　　虽然你不必明白MySQL客户端/服务端的协议的细节，但是你必须明白在更高一层它的工作方式。协议是半双工的。意思就是在任意的时间内，MySQL服务端既可以发送或者接受消息，但是不能同时。也意味着没法减少消息。
　　
　　这种协议是MySQL通信简单而快速。但是也有一些限制。比如，没有流程控制。一旦一端发送一个消息，另一端在相应之前就必须获取整个信息。有点像投掷球的游戏：一个时刻每一方只能有一个球，并且你没有球就不能投掷。
　　
　　客户端发送一个语句到服务端作为一个单独的数据包。如果你有一个大的语句，max_packet_size就对你很重要。一旦一个客户端发送一个语句，如果没有相应，它只能等待结果。。
　　
　　相比较而言，从服务端的响应常常有许多数据包组成。当服务端相应的时候，客户端必须要接受整个结果集。它不能仅仅获取一些行并且之后再要求服务器没必要发剩下的数据。如果客户端仅仅需要不分行返回，它既然要等待服务器所有的数据包并且还要抛弃它们不需要的，或者以不好的方式断开连接。两者都是不好的方法，这就是为什么LIMIT条件如此的重要。
　　
　　关于这点，还有另一方面值得思考：当一个客户端从服务端获取一些行，这是个"pulling拉"的过程。但是事实上，MySQL服务端当生成数据的时候就pushing推送这些行。这个客户端仅仅是接收这些“推”的数据。没有方法告诉服务器停止发送数据。客户端就是 “drinking from the fire hose,”貌似是一直在汲取。。。可以这么说吧。。。
　　
　　大部分连接MySQL的库既可以获取整个结果集并且缓冲到内存中，或者获取你需要的每一行。默认的行为就是获取所有的结果并且把它们缓冲到内存中。这是非常重要的，因为在所有的行被获取之前，MySQL服务端不会释放语句所需要的锁和其他资源。这个语句的状态为“Sending Data”状态。（在以后的部分会说到语句状态的。）当客户端库获取到所有结果，它减少了服务端要工作的数目：服务端完成并且尽可能快的清楚这个语句。
　　
　　事实上，虽然你的结果集是从客户端的内存中的缓冲区中获得，但是大部分客户端可以让你把这个过程看做从服务端直接返回。大部分时间这种方式都可以正常工作，但是对于大结果就不是一个好方法，因为大结果集需要消耗许多时间和大量内存。如果你指定库不缓冲结果，你可以使用更少的内存，并且更快的对结果集进行操作。缺点就是当应用和库交互的时候，服务端的锁和其他资源还是打开状态。
　　
　　让我们看看PHP一个例子。首先，来看看经常使用的查询例子
　　
　　


<?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函数把结果缓存了。这个while仅仅是用来循环缓冲区的。相比较下，下面的代码才是不需要缓冲结果的，因为使用了mysql_
　　unbuffered_query( )替代了mysql_query( ):
　　
　　


<?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函数指定了使用结果而不是缓冲。你也可以在连接的时候指定，这样每个语句都不会被缓冲。
　　
　　


my $dbh = DBI->connect('DBI:mysql:;mysql_use_result=1', 'user', 'p4ssword');
 
查询语句代码
　　每个MySQL连接，或者线程，都有状态来表示某一时刻到底在做什么。有很多方法来查看这些状态，最简单的方法就是使用SHOW FULL PROCESSLIST命令。这个状态会出现在Command列。作为一个查询语句贯穿整个周期，它的状态也变化多次，并且有12个状态。MySQL手册会有详细说明，我们仅仅列出其中的几个，来看看它们的意思。
　　
　　Sleep
　　这个线程正在等待从客户端来的新语句。
　　
　　Query
　　这个线程可能执行了语句或者发送一个结果到客户端。
　　
　　Locked
　　这个线程正在等待在服务端被授权的表锁。锁是由存储引擎所实现的，比如InnoDB是行锁，就不会导致线程locked状态。
　　
　　Analyzing and statistics
　　这个线程在检查存储引擎的统计以及语句的优化。
　　
　　Copying to tmp table [on disk]
　　这个线程正运行一个查询并且把结果复制到临时表中。可能使用了GROUP BY或者UNION.如果结果以on disk结尾，那么已经把内存表转为在硬盘上的表。
　　
　　Sorting result
　　这个县城在排序结果
　　
　　Sending data
　　表达意思很多：线程可能在各个语句阶段发送数据，生成结果集或者返回结果集到客户端。
　　
　　这会帮助你了解基本的状态，因此你可以看到到底”谁拿到了球“。在一个非常忙的服务器，你可能看到不寻常的状态，比如，statistics。开始消耗大量重要的时间。常常意味着发生了一些错误。