查询性能的优化 - 语句执行的基础 - MySQL 客户端/服务端 协议

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。开始消耗大量重要的时间。常常意味着发生了一些错误。