php mysql 溢出_php导致内存溢出

在执行一个导出csv脚步时，当要导出的数据超过3w多条时，就会报错，如下：

Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes)

php存储3w多条数据这个数组占用内存超过20M

解决方法：分批取数据，分批处理数据

问题点：一次取多少数据最合适

一次取1w条，减少数据库IO操作次数，但php数组就好较大

一次取1k条，增加了数据库IO操作次数，php数组很小

数据一共41000条

因为是循环查询数据，只能返回空，所以会有一次无效的IO查询

每次请求次数

响应时间

数组库IO次数

5000

11.34

10

1000

8.02

6

2000

7.07

4

3000

5.12

3

4000

4.93

3

综合考虑，觉得使用每次请求1w条数据

代码简单记录下

$filename = '测试';

$head = array('申请时间', '省', '市', '超市品牌');

$field = array('created_time', 'pro_name', 'city_name', 'brand_name');

$csv = ExportCsvService::getInstance(implode(",", $head), $field, $filename);

$param['limit'] = C('SQL_LIMIT');

$i = 0;

while (true) {

$param['current'] = $i + 1;

$info = $rainbowservice->get_list($param)['data']['list'];

if (empty($info)) {

$csv->end();

break;

} else {

$csv->sendDate($info);

}

unset($info);

$i++;

}

csv操作类

class ExportCsvService

{

private $content;

private $filename;

private $field;

private static $instance;

private function __construct($head,$field,$filename)

{

$this->field = $field;

$this->filename = $filename;

$this->content = iconv('UTF-8', 'GBK',$head) . PHP_EOL;

}

/**

* 实例化服务类

* @param $head

* @param $field

* @param $filename

* @return ExportCsvService

*/

public static function getInstance($head,$field,$filename) {

if (!self::$instance instanceof self) {

self::$instance = new self($head,$field,$filename);

}

return self::$instance;

}

/**

* 拼接要导出的数据

* @param $data

*/

public function sendDate($data) {

foreach ($data as $key => $value) {

$temp = [];

foreach ($this->field as $k) {

$temp[] = iconv('UTF-8', 'GBK', $value[$k]);

}

$this->content .= implode(",", $temp) . PHP_EOL; //用英文逗号分开

unset($temp);

}

}

/**

* 输出数据

*/

public function end() {

header("Content-type:text/csv;charset=GBK");

header("Content-Disposition:attachment;filename=" . $this->filename);

header('Cache-Control:must-revalidate,post-check=0,pre-check=0');

header('Expires:0');

header('Pragma:public');

echo $this->content;

}

}