背景
公司的管理后台的列表导出 excel 功能,一次性导出上万条数据就会崩溃报错,而列表查询一天的数据行可能就有十几万条,无法满足需求,所以需要优化。
报错分析
运行环境:LNMP(ThinkPHP5框架)
代码:
public function exportExcel()
{
//set_time_limit(0); //调试步骤1
//ini_set('memory_limit', '-1'); //调试步骤2
$data = $this->index(true); //mysql数据库返回的数据
//var_dump(1);exit(); //调试步骤3
$rows = [];
foreach ($data as $val) {
//导出前对数据做一些处理
}
$excel = new Excel(); //调用 excel 插件
$excel->setColumnMean([])->setData($rows)->download('用户列表-'.date('YmdHis'));
}
1.直接导出接近 100 万条数据
超过了设置的内存限制值
2.如上代码,打开调试步骤3,看是否在查询数据库这一步报错的
报错结果同上,说明在查询数据库这一步超过了设置的内存限制值
3.代码恢复,打开调试步骤2,不限内存,再导出
报 404,应该是执行超时了
4.代码恢复,打开调试步骤2 和 3,再导出
还是报 404,说明在查询数据库这一步已经执行超时了
5.代码恢复,打开调试步骤1 和 2,不限执行时间不限内存,再导出
报 404
6.代码恢复,打开调试步骤1 和 2 和 3,再导出
报 404
问题总结:
mysql 一次性查询大批量数据,加载到 php 内存,导致内存溢出并且执行容易超时。
解决办法
关键词:非缓冲查询
public function exportExcel()
{
$head = []; //excel 内容表头字段
$sql = $this->index(true); //返回待执行的 sql 语句
$this->putCsv($sql, '用户列表'.date('YmdHis'), $head); //导出 csv 文件
}
private function putCsv($sql, $mark, $head)
{
set_time_limit(0); //不限执行时长,避免超时
try {
$filePath = ROOT_PATH . DS . 'runtime' . DS . 'temp' . DS . $mark . '.csv';
$fp = fopen($filePath, 'w'); //生成临时文件
fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));//转码,防止乱码
fputcsv($fp, $head);
$dbConfig = config('db_data'); //数据库具体配置
$dsn = "mysql:dbname={
$dbConfig['database']};host={
$dbConfig['hostname']};port={
$dbConfig['hostport']}";
$pdo = new \PDO($dsn, $dbConfig['username'], $dbConfig['password']); //直接使用PDO
$pdo-