PHP analyzes and queries memory usage 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. at the same time, the usage of MySQLCAPI also involves yesterday, some colleagues in the PHP discussion group (276167802 verification: csl. if you are interested, you can join in to discuss this article mainly from the principle, manual and source code analysis when MySQL returns a large number of results in PHP queries, the memory usage problem is also involved in the use of MySQL c api. yesterday, some colleagues mentioned in the PHP discussion group (276167802 verification: csl, if you are interested, you can join the discussion) that, in a project he created, the MySQL Query returned too many results (up to 0.1 million results), 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? -[Php] 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 similar functions: [php] 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 "Unbuffered (unbuffered. 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: [php]/* {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 (rows) {php_mysql_do_query (rows, MYSQL_USE_RESULT);}/*} */both functions call php_mysql_do_query (), the difference is only 2nd. MYSQL_STORE_RESULT and MYSQL_USE_RESULT. let's look at php_mysql_do_query. () Implementation: [php] 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 the two c api functions is that the latter only reads the meta information of the result set from the MySQL Server to the Client. 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. I hope this article will be helpful to the majority of php developers. thank you for reading this article.