MySQL accounted for high memory and CPU reason analysis

Some colleagues 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? -

The code is as follows	Copy Code
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:

The code is as follows	Copy Code
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:

The code is as follows

Copy Code

/* {{{Proto resource mysql_query (string query [, int link_identifier]) sends a SQL query to MySQL/php_function (m Ysql_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, Withou T fetching and buffering the result rows */php_function (mysql_unbuffered_query) {php_mysql_do_query (internal_func  Tion_param_passthru, Mysql_use_result); }  /* }}} */

All two functions call Php_mysql_do_query (), except for the difference of the 2nd argument, Mysql_store_result and

The code is as follows	Copy Code
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 a "PHP cache" (such as an array) is not performed on the result during the traversal process, the whole