最近在开发一个PHP程序时遇到了下面的错误:
PHP Fatal error: Allowed memory size of 268 435 456 bytes exhausted
错误信息显示允许的最大内存已经耗尽。遇到这样的错误起初让我很诧异,但转眼一想,也不奇怪,因为我正在开发的这个程序是要用一个foreach
循环语句在一个有4万条记录的表里全表搜索具有特定特征的数据,也就是说,一次要把4万条数据取出,然后逐条检查每天数据。可想而知,4万条数据全部加载到内存中,*阿克苏地区内存不爆才怪。
毕竟编程这么多年,我隐约记得PHP里提供有非一次全部加载数据的API,是像处理流媒体那样,随用随取随丢、数据并不会积累在内存的查询方法。经过简单的搜索,果然在官方网站上找到的正确的用法。
这个问题在PHP的官方网站上叫缓冲查询和非缓冲查询(Buffered and Unbuffered queries)。 PHP的查询缺省模式是缓冲模式。也就是说,查询数据结果会一次全部提取到内存里供PHP程序处理。这样给了PHP程序额外的功能,比如说,计算行数,将 指针指向某一行等。更重要的是程序可以对数据集反复进行二次查询和过滤等操作。但这种缓冲查询模式的缺陷就是消耗内存,也就是用空间换速度。
相对的,另外一种PHP查询模式是非缓冲查询,数据库服务器会一条一条的返回数据,阿克苏市而不是一次全部返回,这样的结果就是PHP程序消耗较少的内存,但却增加了数据库服务器的压力,因为数据库会一直等待PHP来取数据,一直到数据全部取完。
很显然,缓冲查询模式适用于小数据量查询,而非缓冲查询适应于大数据量查询。
对于PHP的缓冲模式查询大家都知道,下面列举的例子是如何执行非缓冲查询API。
非缓冲查询方法一: mysqli
<?php $mysqli = new mysqli("localhost", "my_user", "my_password", "world"); $uresult = $mysqli->query("SELECT Name FROM City", MYSQLI_USE_RESULT); if ($uresult) { while ($row = $uresult->fetch_assoc()) { echo $row['Name'] . PHP_EOL; } } $uresult->close(); ?>
非缓冲查询方法二: pdo_mysql
<?php $pdo = new PDO("mysql:host=localhost;dbname=world", 'my_user', 'my_pass'); $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false); $uresult = $pdo->query("SELECT Name FROM City"); if ($uresult) { while ($row = $uresult->fetch(PDO::FETCH_ASSOC)) { echo $row['Name'] . PHP_EOL; } } ?>
非缓冲查询方法三: mysql
<?php $conn = mysql_connect("localhost", "my_user", "my_pass"); $db = mysql_select_db("world"); $uresult = mysql_unbuffered_query("SELECT Name FROM City"); if ($uresult) { while ($row = mysql_fetch_assoc($uresult)) { echo $row['Name'] . PHP_EOL; } } ?>
http://www.cere.cc/editor/attached/file/20200520/20200520225629_7073.html
http://www.cere.cc/editor/attached/file/20200520/20200520225623_6136.html
http://www.cere.cc/editor/attached/file/20200520/20200520225909_2542.html
http://www.cere.cc/editor/attached/file/20200520/20200520230101_2542.html
http://www.cere.cc/editor/attached/file/20200520/20200520225701_2230.html
http://www.cere.cc/editor/attached/file/20200520/20200520225805_2542.html
http://www.cere.cc/editor/attached/file/20200520/20200520225615_4886.html
http://www.cere.cc/editor/attached/file/20200520/20200520225925_2386.html
http://www.cere.cc/editor/attached/file/20200520/20200520225549_3636.html
http://www.cere.cc/editor/attached/file/20200520/20200520225725_2230.html
http://www.cere.cc/editor/attached/file/20200520/20200520225957_2855.html
http://www.cere.cc/editor/attached/file/20200520/20200520230029_2542.html
http://www.cere.cc/editor/attached/file/20200520/20200520225941_2386.html
http://www.cere.cc/editor/attached/file/20200520/20200520225709_3323.html
http://www.cere.cc/editor/attached/file/20200520/20200520230005_2542.html
http://www.cere.cc/editor/attached/file/20200520/20200520225605_2698.html
http://www.cere.cc/editor/attached/file/20200520/20200520230021_2698.html
http://www.cere.cc/editor/attached/file/20200520/20200520230037_2542.html
http://www.cere.cc/editor/attached/file/20200520/20200520225829_2386.html
http://www.cere.cc/editor/attached/file/20200520/20200520225933_3792.html
http://www.cere.cc/editor/attached/file/20200520/20200520225646_2386.html
http://www.cere.cc/editor/attached/file/20200520/20200520225821_2386.html
http://www.cere.cc/editor/attached/file/20200520/20200520225653_3167.html
http://www.cere.cc/editor/attached/file/20200520/20200520225749_2386.html
http://www.cere.cc/editor/attached/file/20200520/20200520230013_2542.html
http://www.cere.cc/editor/attached/file/20200520/20200520225853_2386.html
http://www.cere.cc/editor/attached/file/20200520/20200520225949_2386.html
http://www.cere.cc/editor/attached/file/20200520/20200520225813_2386.html
http://www.cere.cc/editor/attached/file/20200520/20200520225541_4261.html
http://www.cere.cc/editor/attached/file/20200520/20200520230109_2542.html
http://www.cere.cc/editor/attached/file/20200520/20200520225733_2386.html
http://www.cere.cc/editor/attached/file/20200520/20200520230053_2386.html
http://www.cere.cc/editor/attached/file/20200520/20200520230045_3480.html
http://www.cere.cc/editor/attached/file/20200520/20200520225845_2386.html
http://www.cere.cc/editor/attached/file/20200520/20200520225741_2386.html
http://www.cere.cc/editor/attached/file/20200520/20200520225837_2542.html
http://www.cere.cc/editor/attached/file/20200520/20200520225557_2386.html
http://www.cere.cc/editor/attached/file/20200520/20200520225901_2386.html
http://www.cere.cc/editor/attached/file/20200520/20200520225637_2386.html
http://www.cere.cc/editor/attached/file/20200520/20200520225917_2386.html
http://www.cere.cc/editor/attached/file/20200520/20200520225757_3480.html
http://www.cere.cc/editor/attached/file/20200520/20200520225717_2386.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225554_3577.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520230001_3249.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520230113_3088.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225602_2483.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225627_6075.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225642_2324.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225737_2008.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225809_2006.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520230017_3248.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225833_2786.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225634_7793.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225825_2317.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225621_5763.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520230010_2311.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520230105_3245.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225921_2002.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225722_2165.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225914_2158.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225849_7160.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520230041_3402.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225755_2163.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225931_2782.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225658_2167.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225841_2785.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225538_4360.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225729_1696.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520230026_2310.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520230033_3091.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225650_2792.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225954_2156.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225857_4034.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225706_2167.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225905_2002.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225745_2007.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225612_3264.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225714_2009.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225801_3569.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225946_2156.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520230057_3089.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520230049_3246.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225817_1693.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225936_4188.html
http://www.ht-blue.com/kindeditor/attached/file/20200520/20200520225547_2015.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225782878287.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520230193359335.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225859065906.html
http://www.lited.com/kindeditor/attached/file/20200520/2020052022550199199.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520230013641364.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520230073047304.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225882568256.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225921442144.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225771607160.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520230068686868.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520230068366836.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225741874187.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520230076137613.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225812051205.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225794879487.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520230068486848.html
http://www.lited.com/kindeditor/attached/file/20200520/2020052022590737737.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225816741674.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225937113711.html
http://www.lited.com/kindeditor/attached/file/20200520/2020052022570288288.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520230031793179.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225759495949.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225637053705.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225564196419.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225674957495.html
http://www.lited.com/kindeditor/attached/file/20200520/2020052022570264264.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225646784678.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225932533253.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225635573557.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520230079607960.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225865236523.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225617811781.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225830113011.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225913661366.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225631473147.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225569126912.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225874617461.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225977647764.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225614371437.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225835853585.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225954435443.html
http://www.lited.com/kindeditor/attached/file/20200520/20200520225614001400.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520110041_3737.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520110033_3610.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105913_4438.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105601_4069.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105754_4366.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105728_3882.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105621_6253.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105816_3886.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105801_4234.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520110049_3684.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105546_3535.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105849_3375.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105736_4024.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520110017_6563.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105904_3973.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105841_3352.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105744_4274.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105538_8550.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105857_3186.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105945_4449.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105641_4251.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105553_5204.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105657_4277.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105833_3492.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105611_4228.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105824_4419.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105808_4393.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105936_4661.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105920_4303.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105650_3511.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520110025_3626.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520110057_3663.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105713_4542.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520110001_3949.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520110113_3475.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105930_3911.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105721_4269.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520110105_3861.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520110009_3431.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105705_3277.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105634_8100.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105626_8096.html
http://www.qdc.com/kindeditor/attached/file/20200520/20200520105953_3450.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520230021352135.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225943854385.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225726662666.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225757355735.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225826112611.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225621282128.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520230036953695.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225994789478.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520230019831983.html
http://www.scjkc.cn/uploadfile/file/20200520/2020052023000728728.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225682258225.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225780798079.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520230171327132.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225670977097.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225741314131.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225830743074.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225838523852.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225521152115.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225629802980.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225911941194.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225650045004.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520230032273227.html
http://www.scjkc.cn/uploadfile/file/20200520/2020052022570636636.html
http://www.scjkc.cn/uploadfile/file/20200520/2020052022590421421.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520230016661666.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225524292429.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225896409640.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225715151515.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225775997599.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225940714071.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225890149014.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225846354635.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225512611261.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225692299229.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225677917791.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225754205420.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225637513751.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520230077637763.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225929382938.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520230151035103.html
http://www.scjkc.cn/uploadfile/file/20200520/20200520225994849484.html