Analysis on memory usage of MySQL massive data query using PHP _ PHP Tutorial

PHP queries the memory usage analysis of a large amount of MySQL data. This article mainly describes the memory usage when MySQL returns a large number of results in PHP based on principles, manuals, and source code analysis. it also involves the use of MySQLCAPI. yesterday, some colleagues in P wrote this article mainly about memory usage when querying MySQL returned a large number of results in PHP from principles, manuals and source code analysis, mySQL C APIs are also involved.

Yesterday, a colleague mentioned in the PHP discussion group that a project he created had too many results (up to 0.1 million results) returned by MySQL queries, resulting in insufficient PHP memory. therefore, he asked, before executing the following code to traverse the returned MySQL results, is the data already in the memory? -

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

Of course, there are many optimization methods for this problem. however, for this question, I first thought 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's buffer through the network (assuming TCP/IP is used, that is, the data is still in the sending buffer of the Server and is not sent to the Client.

Before viewing the source code of PHP and MySQL, I noticed that the PHP Manual has two functions similar to the following:

Mysql_query () Mysql_unbuffered_query ()

The literal meanings and descriptions of the two functions confirm my idea. when the previous function is executed, all the result sets will be read from the Server to the Client buffer, and the other functions will not, this is what "unbuffered (unbuffered)" means.

That is to say, if mysql_unbuffered_query () is used to execute an SQL statement that returns a large number of result sets, the PHP memory is not occupied by the result set before traversing the results. if you use mysql_query () to execute the same statement, the PHP memory usage increases dramatically when the function returns, consuming the memory immediately.

If you read the PHP code, you can see the similarities and differences between the two functions:

/* {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_FUNCTION_PARAM_PASSTHRU, MYSQL_USE_RESULT ); } /*}}}*/

Both functions call php_mysql_do_query (), but the difference is only 2nd. MYSQL_STORE_RESULT and MYSQL_USE_RESULT. then, let's 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 c api functions. The difference between these two c api functions is that the latter reads all the result sets from the MySQL Server to the Client, the former only reads the meta information of the result set.

Back to PHP, use mysql_unbuffered_query () to avoid immediate memory usage. if the result is not cached in PHP during the traversal process (such as in an array), although the entire execution process operates on 100,000 or millions of data records or more, however, the memory occupied by PHP is always very small.

Memory usage when MySQL returns a large number of results in PHP queries using manual and source code analysis. it also involves the use of MySQL C APIs. yesterday, a colleague at P...