Memory usage analysis when PHP queries MySQL large amounts of data_PHP tutorial

Yesterday, a colleague mentioned in the PHP discussion group that a project he was working on returned too many results (up to 100,000) from the MySQL query, resulting in insufficient PHP memory. Therefore, he asked, after executing the following code traversal Is the data already in memory before the MySQL result is returned? -

Copy code The code is as follows:

　while ($ row = mysql_fetch_assoc($result)) {
　　// ...
　}

Of course, there are many optimization methods for this problem. However, as far as this problem is concerned, I The first thing that comes to mind is that MySQL is a classic C/S (Client/Server, client/server) model. Before traversing the result set, the underlying implementation may have read all the data to the Client through the network (assuming TCP/IP is used) Buffer, there is another possibility, that is, the data is still in the sending buffer on the server side and has not been passed to the client.
Before looking at the source code of PHP and MySQL, I noticed that there are two similar functions in the PHP manual Function:

Copy code The code is as follows:

mysql_query()
mysql_unbuffered_query()

The literal meaning and description of the two functions confirmed my idea. When the former function is executed, all the result sets will be read from the server to the buffer of the client, but the latter one will not. This is "unbuffered" "Buffering)" means.
That is to say, if you use mysql_unbuffered_query() to execute a SQL statement that returns a large number of result sets, PHP's memory is not occupied by the result set before traversing the results. And use mysql_query() To execute the same statement, when the function returns, PHP's memory usage will increase sharply and the memory will be consumed immediately.
If you read the relevant code of PHP, you can see the similarities and differences in the implementation of these two functions:

Copy code The code is as follows:

　/* {{{ proto resource mysql_query(string query [, int link_identifier])
Sends an SQL query to MySQL */
　PHP_FUNCTION(mysql_query)
　{
　php_mysql_do_query(INTERNAL_FUNCTION_PARAM_PASSTHRU, MYSQL_STORE_RESULT);
　}
　/* }}} */
　/* {{ { proto resource mysql_unbuffered_query(string query [, int link_identifier])
　Sends an SQL query to MySQL, without fetching and buffering the result rows */
　PHP_FUNCTION(mysql_unbuffered_query)
　{
　php_mysql_do_query(INTERNAL_FU NCTION_PARAM_PASSTHRU, MYSQL_USE_RESULT);
　}
　/* }}} */

Both functions call php_mysql_do_query(), only the second parameter is different, MYSQL_STORE_RESULT and MYSQL_USE_RESULT. Let’s look at the implementation of php_mysql_do_query():

Copy code The code is as follows:

　if(use_store == MYSQL_USE_RESULT) {
Mysql_result=mysql_use_result(&mysql->conn);
} else {
mysql_result=mysql_store_result(&mysql->conn);
}

mysql_use_result() and mysql_store_result () is the C API function of MySQL. The difference between the two C API functions is that the latter reads the entire result set from the MySQL Server to the Client, while the former only reads the meta-information of the result set.
Return to In PHP, using mysql_unbuffered_query() can avoid the immediate occupation of memory. If the results are not "PHP cached" during the traversal process (such as placing them in an array), the entire execution process will operate on hundreds of thousands or millions of items or More data, but the memory footprint of PHP is always very small.

http://www.bkjia.com/PHPjc/323922.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/323922.htmlTechArticleYesterday, a colleague mentioned in the PHP discussion group that the results returned by a MySQL query on a project he was working on were too large. (up to 100,000), resulting in insufficient PHP memory. So, he asked, after executing the following...