Mysql database asynchronous query implementation in php

Mysql database asynchronous query implementation in php

Problem

Generally, the performance bottleneck of a web application lies in the database. Because mysql queries in php are serialized. That is to say, if two SQL statements are specified, the second SQL statement will be executed after the first SQL statement is executed. At this time, if two SQL statements are executed and each execution time is 50 ms, it may take 100 ms to complete all execution. The main cause is SQL serial execution. Can we change the execution method to improve performance? The answer is yes. We can improve the performance through asynchronous execution.

Asynchronous

If it is executed in asynchronous mode, the performance may be greatly improved. In asynchronous mode, the two SQL statements are executed concurrently, which may take 60 ms to complete.

Implementation

Mysqli + mysqlnd. Mysqlnd officially implemented by php provides the asynchronous query method. They are:
Mysqlnd_async_query sends a query request
Mysqlnd_reap_async_query
In this way, you do not have to wait until the query results are blocked after each query request is sent.

The implementation code is as follows:

 Int, * 2 => int, * 3 => int *) */$ result = array (1 => 0, 2 => 0, 3 => 0 ); // asynchronous [concurrent requests] $ 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); // only one row $ 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); // do not add $ done ++ here.} While ($ done <$ total); var_dump ($ result); echo "ASYNC_QUERY_TIME:", microtime (true)-$ time_start, "\ n "; $ link = end ($ links); $ link = $ link ['link']; echo "\ n ";

Conclusion

The mysql database starts a separate thread for processing each query request. If the mysql server has too many threads to start, thread switching will inevitably cause excessive system load. If the load on the mysql database is low, it is a good choice to use Asynchronous queries.