原文链接:http://www.walu.cc/php/async-mysql-query.md
php5.3之后,mysqlnd驱动为php提供了异步请求mysql服务器的功能(mysql的驱动必须是mysqlnd才行)。
下面列出我的demo,以及异步查询和平常查询的时间消耗。我这只能说明使用方法,数据有点简单,欢迎大家使用线上数据做更详细的测试。
这个异步查询接口无法复用同一个链接,对于每一个query,需要使用一个完全独立的连接,对服务器 来说,这是一个资源消耗。
<?php $host = '127.0.0.1'; $user = 'root'; $password = ''; $database = 'test'; /** * 期望得到额结果 * array( * 1 => int, * 2 => int, * 3 => int * ) */ $result = array(1=>0, 2=>0, 3=>0); //异步方式[并发请求] $time_start = microtime(true); $links = array(); foreach ($result as $key=>$value) { $obj = new mysqli($host, $user, $password, $database); $links[spl_object_hash($obj)] = array('value'=>$key, 'link'=>$obj); } $done = 0; $total = count($links); foreach ($links as $value) { $value['link']->query("SELECT COUNT(*) AS `total` FROM `demo` WHERE `value`={$value['value']}", MYSQLI_ASYNC); } do { $tmp = array(); foreach ($links as $value) { $tmp[] = $value['link']; } $read = $errors = $reject = $tmp; $re = mysqli_poll($read, $errors, $reject, 1); if (false === $re) { die('mysqli_poll failed'); } elseif ($re < 1) { continue; } foreach ($read as $link) { $sql_result = $link->reap_async_query(); if (is_object($sql_result)) { $sql_result_array = $sql_result->fetch_array(MYSQLI_ASSOC);//只有一行 $sql_result->free(); $hash = spl_object_hash($link); $key_in_result = $links[$hash]['value']; $result[$key_in_result] = $sql_result_array['total']; } else { echo $link->error, "\n"; } $done++; } foreach ($errors as $link) { echo $link->error, "1\n"; $done++; } foreach ($reject as $link) { printf("server is busy, client was rejected.\n", $link->connect_error, $link->error); //这个地方别再$done++了。 } } while ($done<$total); var_dump($result); echo "ASYNC_QUERY_TIME:", microtime(true)-$time_start, "\n"; $link = end($links); $link = $link['link']; echo "\n"; //平常顺序执行的时间 $time_start = microtime(true); $result = array(1=>0, 2=>0, 3=>0); foreach ($result as $key=>$value) { $sql_result = $link->query("SELECT COUNT(*) AS `total` FROM `demo` WHERE `value`={$key}"); if (is_object($sql_result)) { $sql_result_array = $sql_result->fetch_array(MYSQLI_ASSOC); $sql_result->free(); $result[$key] = $sql_result_array['total']; } else { echo "error.\n"; } } var_dump($result); echo "COMMON_QUERY_TIME:", microtime(true)-$time_start, "\n";
结果数据
测试结果实在我的mac air上跑的,只能反应一个对比情况,并不代表线上服务器的实际性能。
数据量 异步查询 普通查询
82168条总数据 0.038721084594727 0.064269065856934
263865条总数据 0.11696815490723 0.19273400306702