How PHP queries MySQL concurrently

Recently in the study of PHP, like, encounter php concurrency query MySQL problem, this article mainly introduces PHP concurrency query MySQL instance code, small series think very good, and now share to everyone, also for everyone to do a reference. Follow the small series together to see it, hope to help everyone.

Synchronizing queries

This is our most common invocation pattern, the client calls the query[function], initiates the query command, waits for the result to return, reads the result, then sends the second query command, waits for the result to return, reads the result. The total time spent will be the sum of the two queries. Simplify the process, for example:

Example diagram, from 1.1 to 1.3 for a query[function] call, two queries, will be serial through 1.1, 1.2, 1.3, 2.1, 2.2, 2.3, especially in 1.2 and 2.2 will block the wait, the process can not do other things.

The advantage of synchronous invocation is that it conforms to our intuitive thinking, invocation and processing are simple. The disadvantage is that the process is blocked waiting for the result to return, adding extra uptime.
If there is more than one query request, or if the process has other things to deal with, then it is possible to make reasonable use of the waiting time and improve the processing capacity of the process.

Split

Now, we break the query[function], the client returns immediately after 1.1, the client skips 1.2, and then reads the data after 1.3 has reached the data. This process is liberated in the original 1.2 stage, can do more things, for example ... Launch a SQL query [2.1] and see if the prototype of the concurrency query is seen.

Concurrent queries

The next query that is relative to the synchronization query is initiated after the previous one completes, and the next query request can be initiated immediately after the last query request is initiated. Simplify the process:

Example diagram, after 1.1.1 successfully sent the request, immediately return to [1.1.2], the final query results returned in the distant 1.2. However, in the middle of the 1.1.1 to 1.2, also launched another query request, this time period, the simultaneous launch of two query requests, 2.2 before 1.2 arrived, then the total time of two queries, only the first query.

The advantage of concurrent queries is that you can increase the utilization of processes, avoid blocking waiting for the server to process queries, and shorten the time-consuming of multiple queries. But the disadvantage is also obvious, the launch of N concurrent query, you need to establish an N database link, for the application of database connection pool can avoid this situation.

Degradation

Ideally, we want the concurrency of n queries to be equal to a query with the longest query time. But it is also possible that the concurrency query will [degenerate] to [synchronous query]. What? In the example diagram, if 1.2 is returned before 2.1.1, then the concurrency query is [degraded] to [synchronous query], but the cost is higher than the synchronous query.

Multiplexing

• Initiating Query1

• Initiating Query2

• Initiating Query3

• .........

• Waiting for Query1, Query2, Query3

• Read Query2 results

• Read Query1 results

• Read Query3 results

So, how to wait to know when the query results returned, and which query results returned it?

Call read for each query IO? If you encounter blocking Io, it will block on an IO, and other IO results are returned and cannot be processed. Then, if the non-blocking IO, then do not have to be afraid of blocking in one of the IO, it is, but it will cause continuous polling judgment, wasting CPU resources.

For this scenario, you can use multiplexing to poll multiple IO.

PHP implementation concurrency query MySQL

PHP's mysqli (MYSQLND driver) provides multiplexed polling Io (mysqli_poll) and asynchronous Queries (Mysqli_async, mysqli_reap_async_query) that implement concurrent queries using these two features, sample code:

<?php $sqls = Array (' SELECT * from ' mz_table_1 ' limit 1000,10 ', ' select * from ' mz_table_1 ' limit 1010,10 ', ' select '  * from ' mz_table_1 ' limit 1020,10 ', ' select * from ' mz_table_1 ' limit 10000,10 ', ' select * from ' mz_table_2 ' limit 1 ', ' SELECT * from ' mz_table_2 ' LIMIT 5,1 '); $links = []; $tvs = Microtime (); $TV = Explode (' ', $tvs); $start = $TV [1] * + (int) ($TV [0] * 1000);  Link the database and initiate the asynchronous query foreach ($sqls as $sql) {$link = Mysqli_connect (' 127.0.0.1 ', ' root ', ' root ', ' dbname ', ' 3306 '); $link->query ($sql, Mysqli_async); Initiates an asynchronous query, immediately returns $links [$link->thread_id] = $link; } $llen = count ($links); $process = 0;  do {$r _array = $e _array = $reject = $links; multiplexed Polling IO if (!) (  $ret = Mysqli_poll ($r _array, $e _array, $reject, 2))) {continue; }//Read the query with the result returned, processing the result foreach ($r _array as $link) {if ($result = $link->reap_async_query ()) {Print_r ($result-&G    T;fetch_row ());   if (Is_object ($result)) Mysqli_free_result ($result); } else {}//After the operation is finished, the current dataLink removes unset from the collection to be polled ($links [$link->thread_id]);   $link->close ();  $process + +;  } foreach ($e _array as $link) {die;  } foreach ($reject as $link) {die; }}while ($process < $llen); $tvs = Microtime (); $TV = Explode (' ', $tvs); $end = $TV [1] * + (int) ($TV [0] * 1000); Echo $end-$start, Php_eol;

Mysqli_poll Source:

#ifndef php_win32#define Php_select (M, R, W, E, T) select (M, R, W, E, T) #else # include "Win32/select.h" #endif/* {{MYSQLND _poll */phpapi Enum_func_statusmysqlnd_poll (mysqlnd **r_array, Mysqlnd **e_array, Mysqlnd ***dont_poll, long sec, long us EC, int * desc_num) {struct timeval TV; struct Timeval *tv_p = NULL; Fd_set RfDs, Wfds, Efds; php_socket_t max_fd = 0; I NT retval, sets = 0; int Set_count, max_set_count = 0; Dbg_enter ("_mysqlnd_poll");  if (sec < 0 | | usec < 0) {php_error_docref (NULL, e_warning, "negative values passed for SEC and/or usec"); Dbg_return (FAIL); } fd_zero (&rfds); Fd_zero (&wfds); Fd_zero (&efds);  Gets the socket-link descriptor from all mysqli links if (R_array! = NULL) {*dont_poll = Mysqlnd_stream_array_check_for_readiness (R_array);  Set_count = Mysqlnd_stream_array_to_fd_set (R_array, &rfds, &AMP;MAX_FD);  if (Set_count > Max_set_count) {max_set_count = Set_count; } sets + = Set_count; }//Gets the socket-link descriptor from all mysqli links if (E_array! = NULL) {Set_count = Mysqlnd_stream_array_to_fd_set (E_array, &efds, &AMP;MAX_FD);  if (Set_count > Max_set_count) {max_set_count = Set_count; } sets + = Set_count; } if (!sets) {php_error_docref (NULL, e_warning, *dont_poll?).  "All arrays passed is clear": "No stream arrays were passed"); DBG_ERR_FMT (*dont_poll?  "All arrays passed is clear": "No stream arrays were passed"); Dbg_return (FAIL); } php_safe_max_fd (MAX_FD, Max_set_count);  Select Poll blocking time if (USec > 999999) {tv.tv_sec = sec + (usec/1000000); tv.tv_usec = usec% 1000000;  } else {tv.tv_sec = sec; Tv.tv_usec = USEC; } tv_p = &tv; Polling, waiting for multiple IO readable, php_select is a macro definition of select retval = Php_select (max_fd + 1, &rfds, &wfds, &efds, tv_p); if (retval = =-1) {Php_error_docref (NULL, e_warning, "Unable to select [%d]:%s (max_fd=%d)", errno, Strerror (errno  ), MAX_FD); Dbg_return (FAIL); if (R_array! = null) {Mysqlnd_stream_array_from_fd_set (R_array, &rfds);} if (E_array! = null) {Mysqlnd_stream_a Rray_from_fd_Set (E_array, &efds); }//Returns the number of operable io *desc_num = retval; Dbg_return (PASS);}

Concurrent query Operation results

To see the effect more visually, I looked for a 130 million data volume and no optimized table to work with.

Results of concurrent Queries:

Results of the synchronization query:

From the results, the total time spent in a synchronous query is the sum of the times of all queries, while the total duration of the concurrency query is actually the longest query time (Fourth of the synchronous query, time consuming is 10 seconds, meeting the total time of the concurrent query), and the order of the query and the result of the concurrent query is different.

More time-consuming and shorter query comparisons

Compare the SQL with a short query time with more than one

Test 1 results for concurrent queries (database link time also counts):

Results of the synchronization query (database link time also counted):

Test 2 results for concurrent queries (do not count database link time):

From the results, concurrent query Test 1 did not benefit. From the synchronous query, each query time is about 3-4ms. But if the database link time is not counted in (synchronous query only one database link), the advantages of concurrent queries can be reflected.

Conclusion

Here discusses the PHP implementation concurrency query MySQL, from the experimental results intuitively understand the pros and cons of concurrent queries. The time to establish a database connection is still very large on an optimized SQL query. #没有连接池, what do you want with