Implementation of asynchronous query of mysql database in php

Problem

Usually the performance bottleneck of a web application is the database. Because, usually mysql queries in php are serial. That is to say, if two SQL statements are specified, the second SQL statement will wait until the first SQL statement is executed before executing it. At this time, if 2 SQL statements are executed, each execution time is 50ms, and it may take 100ms to complete the execution. Since, the main reason is caused by the serial execution of sql. So can we change the execution method to improve performance? The answer is, yes. We can improve performance through asynchronous execution.

Asynchronous

If executed in an asynchronous manner, the performance may be greatly improved. If the asynchronous method is used, the two SQL statements will be executed concurrently, and it may take 60ms to complete the execution.

implementation

mysqli + mysqlnd. The asynchronous query method is provided in mysqlnd officially implemented by PHP. They are:
mysqlnd_async_query sends a query request
mysqlnd_reap_async_query gets the query results
This way you don’t have to block and wait for the query results every time after sending the query request.

The implementation code is as follows:

<!--?php
   
$host       = &#39;127.0.0.1&#39;;
$user       = &#39;root&#39;;
$password   = &#39;&#39;;
$database   = &#39;test&#39;;
   
/**
 * 期望得到额结果
 * 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(&#39;value&#39;=>$key, &#39;link&#39;=>$obj);
}
$done = 0;
$total = count($links);
   
foreach ($links as $value) {
    $value[&#39;link&#39;]->query("SELECT COUNT(*) AS `total` FROM `demo` WHERE `value`={$value[&#39;value&#39;]}", MYSQLI_ASYNC);
}
   
do {
   
    $tmp = array();
    foreach ($links as $value) {
        $tmp[] = $value[&#39;link&#39;];
    }
   
    $read = $errors = $reject = $tmp;
    $re = mysqli_poll($read, $errors, $reject, 1);
    if (false === $re) {
        die(&#39;mysqli_poll failed&#39;);
    } 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][&#39;value&#39;];
            $result[$key_in_result] = $sql_result_array[&#39;total&#39;];
        } 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[&#39;link&#39;];
echo "\n";

Conclusion

MySQL database starts a separate thread for processing each query request. If the MySQL server starts too many threads, thread switching will inevitably cause high system load. If the MySQL database load is not high, using asynchronous query is still a good choice.

Reference documentation

http://www.walu.cc/php/async-mysql-query.md