对比PHP对MySQL的缓冲查询和无缓冲查询

关于缓冲查询和无缓冲查询
MySQL的客户端有两种类型的查询：
缓冲查询：将接收查询的结果并把他们存储在客户端的缓存中，而且接下来获取行记录的请求仅仅从本地内获取。
(1)优点：可以在结果集中自由地移动“当前行”的指针，这样很容易找到，因为结果是存在客户端的。
(2)缺点：需要额外的内存来存储这些结果集，而且需要大量的内存，另外，php中用来运行查询的函数会一直到所有的结果都接收才会返回值。

无缓冲查询：会限制你通过严格的顺序访问查询结果。但他不需要额外的内存来存储整个结果集。你可以在MySQL服务器开始返回值的时候就开始获取而处理或显示数据行。当使用无缓冲结果集时，必须使用mysql_fetch_row函数获取所以的数据行，或者在给服务器发送其他任何命令前用mysql_free_result函数关闭结果集。

哪种类型的查询好？最好的根据是具体情况而定，无缓冲查询在结果集巨大的时为你节省大量的临时内存，而且查询不需要排序时，php在MySQL数据库实际上还在处理时就可以获得第一个数据行。
缓存查询便于寻找，它可以提供一个全面的寻找加速。因为每一个单独的查询的都会快读结束，mysql快速的获取结果集并存放在内存中，而不是在处理PHP代码时保持查询为可用的。
另外一个无缓冲查询的限制是在所有的数据行都被读取或者结果集用mysqli_free_result释放之前，你将无法向服务器发送任何命令。

PHP+MySQL缓冲查询和无缓冲查询
PHP MySQL查询(mysqli,pdo_mysql)默认使用缓冲模式. 
也就是说查询结果将一次性从MySQL传输到PHP进程内存中, 
这时可以统计结果集的行数,以及移动结果集指针. 
缓冲模式下,如果结果集很大,那么PHP进程也会占用大量的内存, 
直到结果集被unset或者free.

store_result用于缓冲模式,所有结果一次性存储到PHP进程中: 

mysqli::query MYSQLI_STORE_RESULT

mysqli::store_result

mysqli_stmt::store_result

use_result表示无缓冲查询: 如果PHP的MySQL数据库驱动底层用的是libmysqlclient,那么memory_limit不能统计到结果集占用的内存, 
除非结果集已经赋值给PHP变量,如果底层使用mysqlnd作为驱动时则可以统计到(PHP从5.4开始底层默认使用mysqlnd). 
无缓冲模式下执行的查询将会返回一个resource资源引用,位于MySQL的查询结果等待PHP获取. 
无缓冲模式下,PHP进程占用的内存很少,但会增大MySQL服务器的负载. 
在PHP取回所有结果前,在当前数据库连接下不能发送其他的查询请求.

mysqli::query MYSQLI_USE_RESULT

mysqli::use_result

 

总结: 

非缓冲查询方法一: mysqli
<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$uresult = $mysqli->query("SELECT Name FROM City", MYSQLI_USE_RESULT);
 
if ($uresult) {
  while ($row = $uresult->fetch_assoc()) {
    echo $row['Name'] . PHP_EOL;
  }
}
$uresult->close();
?>


非缓冲查询方法二: pdo_mysql
<?php
$pdo = new PDO("mysql:host=localhost;dbname=world", 'my_user', 'my_pass');
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
 
$uresult = $pdo->query("SELECT Name FROM City");
if ($uresult) {
  while ($row = $uresult->fetch(PDO::FETCH_ASSOC)) {
    echo $row['Name'] . PHP_EOL;
  }
}
?>


非缓冲查询方法三: mysql
<?php
$conn = mysql_connect("localhost", "my_user", "my_pass");
$db  = mysql_select_db("world");
 
$uresult = mysql_unbuffered_query("SELECT Name FROM City");
if ($uresult) {
  while ($row = mysql_fetch_assoc($uresult)) {
    echo $row['Name'] . PHP_EOL;
  }
}
?>

当结果集不大时,或者需要在读取所有行前获取结果集行数时,使用缓冲查询(默认). 
当结果集很大时,使用无缓冲查询,避免PHP进程占用大量的内存. 

$rs = $mysqli->query("SELECT * FROM City", MYSQLI_USE_RESULT);

$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

$rs = $pdo->query("SELECT * FROM City");

默认情况下,mysqli_stmt的SELECT查询结果将留在MySQL服务器上,等待fetch方法把记录逐条取回到PHP程序中,这样做会降低性能,但能节省内存. 
如果需要对所有记录进行处理,可以调用mysqli_stmt::store_result,把所有结果一次性全部传回到PHP程序中, 
这样做更高效,能减轻MySQL服务器的负担,虽然内存占用会多一些. 
如果获取SELECT语句查找到了多少条记录,可以用 mysqli_stmt::$num_rows 获取. 
这个属性只有在提前执行过 mysqli_stmt::store_result 方法,将全部查询结果传回到PHP程序中的情况下才可以使用. 
对比 mysqli_result::$num_rows 则不没有这个限制. 
用 mysqli_stmt::free_result 关闭 mysqli_stmt::store_result:
 

$stmt->store_result();

echo $stmt->num_rows;

$stmt->free_result();

mysqli_stmt::store_result能让mysqli_stmt::fetch更高效,但也需要用mysqli_stmt::free_result显式关闭. 
可以用mysqli_stmt::get_result拿到结果集对象$result,然后mysqli_result::fetch_all拿到查询数组$results:
 

$result = $stmt->get_result();

$results = $result->fetch_all(MYSQLI_ASSOC);

整理一下相关参数：

mysqli::query                //执行SQL,成功返回mysqli_result(SELECT,SHOW,DESCRIBE操作)对象或TRUE(其他操作),失败返回FALSE.用mysqli::close关闭.

mysqli::prepare              //预处理SQL,成功返回statement对象,失败返回FALSE.

mysqli_stmt::execute         //执行SQL.用mysqli_stmt::close关闭.

mysqli_stmt::store_result    //取回全部查询结果(SELECT,SHOW,DESCRIBE,EXPLAIN)到PHP,可选.用mysqli_stmt::free_result关闭.

mysqli_stmt::bind_result     //把prepare和execute产生的结构绑定结果到变量,然后在mysqli_stmt::fetch中把这些变量输出或赋值.

mysqli_stmt::fetch           //每次返回结果集的一条,赋值给mysqli_stmt::bind_result绑定的变量.

mysqli_stmt::get_result      //获得结果对象,然后调用mysqli_result::fetch_all就能返回结果集数组.mysqlnd下可用.

mysqli_result::fetch_all      //返回一个结果集数组(MYSQLI_NUM(默认),MYSQLI_ASSOC,MYSQLI_BOTH),用mysqli_result::close关闭.mysqlnd下可用.

mysqli_result::fetch_array    //每次返回结果集的一条,包含一个一维的数字数组和关联数组.

mysqli_result::fetch_assoc    //每次返回结果集的一条,即一个一维的关联数组.

mysqli_result::fetch_row      //每次返回结果集的一条,即一个一维的数字数组.