PHP uses mysql_query to query the solution to a very large result set and excessive memory, phpmysql_query

PHP uses mysql_query to query the solution to a very large result set and excessive memory, phpmysql_query_PHP tutorial

WBOY

Release： 2016-07-12 08:57:24

Original

704 people have browsed it

php uses mysql_query to query a very large result set that exceeds memory. phpmysql_query

When using mysql_query to query a very large result set, a fatal error will occur that exceeds the memory limit. This is because mysql_query uses the method of querying all results and then caching the entire result set into memory.

Mysql query also provides another query method. The function name is mysql_unbuffered_query. This function uses the result set immediately after finding the result, and does not cache the result set into the memory, thus avoiding exceeding the limit. A memory situation occurs. However, the cost of using this method is that you cannot use methods such as getting the head office when querying, because this method returns results while querying. At the same time, you cannot perform other operations on the same database link when using this method. If you want to perform other operations, you must first terminate the current operation, release all result rows generated by uncached SQL queries, or re-instantiate a database. Connect and use the new link for other actions.

The following is a comparison between using cache and not using cache (there are more than 10 million rows of data in the queried table):

<span>function</span><span> selecttest()
    {
        </span><span>try</span><span> {
            </span><span>$pdo</span> = <span>new</span> PDO("mysql:host=localhost;dbname=test", 'root', '123456'<span>);
</span><span>//</span><span>            不使用缓存结果集方式
//            $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);</span>
            <span>$sth</span> = <span>$pdo</span>->prepare('select * from test'<span>);
            </span><span>$sth</span>-><span>execute();
            </span><span>echo</span> '最初占用内存大小：' . memory_get_usage() . "\n"<span>;
            </span><span>$i</span> = 0<span>;
            </span><span>while</span> (<span>$result</span> = <span>$sth</span>->fetch(PDO::<span>FETCH_ASSOC)) {
                </span><span>$i</span> += 1<span>;
                </span><span>if</span> (<span>$i</span> > 10<span>) {
                    </span><span>break</span><span>;
                }
                </span><span>sleep</span>(1<span>);
                </span><span>print_r</span>(<span>$result</span><span>);
                </span><span>echo</span> '占用内存大小：' . memory_get_usage() . "\n"<span>;
            }
        } </span><span>catch</span> (<span>Exception</span> <span>$e</span><span>) {
            </span><span>echo</span> <span>$e</span>-><span>getMessage();
        }
    }</span>

Copy after login

The method used above is to cache all result sets. When running this function, an out-of-memory error will be reported, as shown below:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 204800000 bytes) in E:\ProgramDevelopment\RuntimeEnvironment\xampp\htdocs\test\test.php on line 57<span>

Call Stack</span>:
    0.0005     135392   1. {main}() E:\ProgramDevelopment\RuntimeEnvironment\xampp\htdocs\test\test.php:0
    0.0005     135568   2. test->selecttest() E:\ProgramDevelopment\RuntimeEnvironment\xampp\htdocs\test\test.php:86
    0.0055     142528   3. PDOStatement->execute() E:\ProgramDevelopment\RuntimeEnvironment\xampp\htdocs\test\test.php:57

Copy after login

Memory limit exceeded while executing $sth->execute();;

Remove the comment of // $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false); and the result set will not be cached. Running this function will output the following content:

最初占用内存大小：144808
<span>Array</span><span>
(
    [id] </span>=> 1<span>
    [a] </span>=><span> v
    [b] </span>=><span> w
    [c] </span>=><span> i
)
占用内存大小：</span>145544
<span>Array</span><span>
(
    [id] </span>=> 2<span>
    [a] </span>=><span> b
    [b] </span>=><span> l
    [c] </span>=><span> q
)
占用内存大小：</span>145544
<span>Array</span><span>
(
    [id] </span>=> 3<span>
    [a] </span>=><span> m
    [b] </span>=><span> p
    [c] </span>=><span> h
)
占用内存大小：</span>145536
<span>Array</span><span>
(
    [id] </span>=> 4<span>
    [a] </span>=><span> j
    [b] </span>=><span> i
    [c] </span>=><span> b
)
占用内存大小：</span>145536
<span>Array</span><span>
(
    [id] </span>=> 5<span>
    [a] </span>=><span> q
    [b] </span>=><span> g
    [c] </span>=><span> g
)
占用内存大小：</span>145536

Copy after login

As you can see, the memory occupied by obtaining a row of results without caching the result set is very small. This solves the problem of exceeding the memory limit.