PHP query MySQL Large amount of data memory

This article is mainly from the principles, manuals and source analysis in PHP query MySQL return a large number of results, memory footprint problem, while the use of the MySQL C API also involved.

Yesterday, a colleague in the PHP discussion group mentioned that he did a project because the MySQL query returned too many results (up to 100,000), resulting in insufficient PHP memory. So, he asks, is the data already in memory before executing the following code traversing the MySQL results returned? -

while ($row = Mysql_fetch_assoc ($result)) {

...

}

Of course, there are many ways to optimize this problem. However, on this issue, I first think that MySQL is a classic C/s (client/server, client/server) model, before traversing the result set, the underlying implementation may have all the data through the network (assuming the use of TCP/IP) read the Client's buffer, Another possibility is that the data is still in the server-side send buffer and not passed to the client.

Before looking at the PHP and MySQL source, I noticed that there are two functions in the PHP manual that are similar:

mysql_query ()

Mysql_unbuffered_query ()

The literal meaning and description of the two functions confirms my idea that when the previous function executes, it reads all the result sets from the server side to the client side of the buffer, and the second one does not, which means "unbuffered (not buffered)".

That is, if you execute a SQL statement with Mysql_unbuffered_query () that returns a large number of result sets, PHP memory is not occupied by the result set until the result is traversed. And with mysql_query () to execute the same statement, the function returned, PHP memory footprint will increase dramatically, immediately consume memory.

If you read the code in PHP, you can see the similarities and differences in the implementation of these two functions:

/* {{{Proto resource mysql_query (string query [, int link_identifier])

Sends a 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 a SQL query to MySQL, without fetching and buffering the result rows * *

Php_function (Mysql_unbuffered_query)

{

Php_mysql_do_query (Internal_function_param_passthru, Mysql_use_result);

}

/* }}} */

All two functions call Php_mysql_do_query (), except for the difference of the 2nd parameter, Mysql_store_result and Mysql_use_result. Then look at the implementation of Php_mysql_do_query ():

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 () are MySQL's C API functions, and the difference between these two C API functions is that the latter reads the result set from the MySQL server side to the client side, The former only reads the meta information of the result set.

Back to PHP, using Mysql_unbuffered_query (), you can avoid the immediate memory footprint. If the traversal process does not "PHP cache" The results (such as in an array), the entire execution process operates with 100,000 or millions or more data, but the memory used by PHP is always very small.