Asynchronous query implementation of MySQL database in PHP

Problem

Typically a Web application has a performance bottleneck in the database. Because, in general, the MySQL query in PHP is serial. That is, if you specify two SQL statements, the second SQL statement waits for the first SQL statement to execute. At this point, if you execute 2 SQL statements, each execution time is 50ms, it may take 100ms to complete the execution. Since, the main reason is that the serial execution of SQL is causing. Can we change the way we do it to improve performance? The answer is yes, yes. We can improve performance in a way that asynchronously executes.

Asynchronous

If executed asynchronously, there may be a significant improvement in performance. In the case of asynchronous, two SQL statements are executed concurrently and may take 60ms to complete.

Realize

Mysqli + MYSQLND. PHP's official implementation of the MYSQLND provides an asynchronous query method. respectively:
Mysqlnd_async_query sending a query request
Mysqlnd_reap_async_query getting query Results
This eliminates the need to block waiting query results every time a query request is sent.

The implementation code is as follows:

<!--? php $host = ' 127.0.0.1 '; $user = ' root '; $password = '; $database = ' Test ';   /** * Expected to get the result * Array (* 1 =--> int, * 2 = int, * 3 = int *) */$result = Array (1=>0, 2=>0, 3=>0);   asynchronous mode [concurrent request] $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 [' VA   Lue ']} ", 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);//Only one line $            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, $lin        K-&GT;ERROR);    Don't $done++ this place any more.   }} while ($done < $total); Var_dump ($result); echo "Async_query_time:", Microtime (True)-$time _start, "\ n"; $link = End ($links), $link = $link [' link '];echo ' \ n ';

Conclusion

For each query request, the MySQL database starts a single thread for processing. If the MySQL server has too many threads to boot, it will inevitably cause the thread switchover to cause excessive system load. Using asynchronous queries is a good choice if the MySQL database load is low.

