This article mainly analyzes the problem of memory consumption when querying MySQL in PHP and returning a large number of results from the principle, manual and source code. It also involves the use of MySQL C API

Yesterday, a colleague mentioned in the PHP discussion group that he was working on a project that returned too many results (up to 100000) from MySQL queries, resulting in insufficient PHP memory Therefore, he asked whether the data was already in memory before executing the following code to traverse the returned MySQL results-

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

 

Of course, there are many optimization methods for this problem However, in terms of this problem, I first thought that MySQL is a classic C/S (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). Another possibility is that the data is still in the server's send buffer and not transmitted to the client

Before checking the source code of PHP and MySQL, I noticed that there are two functions with similar functions in the PHP manual:

1. mysql_query() 
2.  
3. mysql_unbuffered_query() 

 

 

The literal meaning and description of the two functions confirm my idea. When the former function executes, it will read all the result sets from the server side to the buffer on the client side, while the latter does not, which means "unbuffered"

That is, if mysql_unbuffered_query() is used to execute an SQL statement that returns a large number of result sets, PHP's memory is not occupied by the result sets before traversing the results When mysql_query() is used to execute the same statement, PHP's memory usage will increase sharply when the function returns, and the memory will be consumed immediately

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

1. /* {{{ proto resource mysql_query(string query [, int link_identifier])  
2.    Sends an SQL query to MySQL */  
3. PHP_FUNCTION(mysql_query) 
4. { 
5.     php_mysql_do_query(INTERNAL_FUNCTION_PARAM_PASSTHRU, MYSQL_STORE_RESULT);  
6. } 
7. /* }}} */  
8.  
9. /* {{{ proto resource mysql_unbuffered_query(string query [, int link_identifier])  
10.    Sends an SQL query to MySQL, without fetching and buffering the result rows */  
11. PHP_FUNCTION(mysql_unbuffered_query) 
12. { 
13.     php_mysql_do_query(INTERNAL_FUNCTION_PARAM_PASSTHRU, MYSQL_USE_RESULT);  
14. } 
15. /* }}} */  

 

 

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():

1. if (use_store == MYSQL_USE_RESULT) { 
2.     mysql_result=mysql_use_result(&mysql->conn);  
3. }  else  { 
4.     mysql_result=mysql_store_result(&mysql->conn);  
5. } 

 

 

 

Mysql_use_result() and mysql_store_result() are MySQL's C API functions. The difference between the two C API functions is that the latter reads all the result sets from the MySQL server side to the client side, while the former only reads the meta information of the result set

Back to PHP, use mysql_unbuffered_query() to avoid immediate memory consumption If the results are not "cached in PHP" during the traversal process (such as being placed in an array), the entire execution process operates 100000 or million or more pieces of data, but the memory occupied by PHP is always very small

label: Beijing website production High end website construction