今天来总结一下解决了下载16万条数据到csv文件的问题。主要是消费报表，用于财务对账，需要下载成excel文件，我们是下载成.csv文件，遇到数据量大的商家，一个月的消费数据超过15万条，经常就下载进度到99%卡住 ，其实后台已经报错500或者504。
?
今天彻底解决了下载15万8千多条数据不成功的问题，当然之前还出现过内存溢出的问题，后来找到瓶颈，优化了代码，解决了内存溢出的问题。
以前的代码是将数据库取的2万条数据没按分页取，直接一下子取出来，然后将数据拼装成csv文件所需要的整块的data，也就是不是逐行写入，第一次浪费内存是在将数据存放在array中，第二次浪费内存是将数据拼装成array再往csv文件中写，所以内存溢出。优化方案是扩大php内存，然后优化代码，php默认的内存128M太小，相对于下载20w条数据的业务来说，所以内存扩大到1G。代码优化方案：将取数据改成分页取，每10000条取一次，然后foreach这一万条数据，一边拼装一边往csv文件中写，fputcsv($fp, $row); 这样优化以后， 下载8万条数据不会报内存溢出。
?
将下载这个特殊的action的内存扩大，
class="php" name="code"> ini_set('memory_limit', '1G');//将内存最大限制改为1G
?
之后就不再出现内存溢出了，但是数据量在11万条以上时，仍然下载不成功，查日志发现是PHP Fatal error:? Maximum execution time of 30 seconds exceeded in /xx/xx 目录，所以解决办法是在这个下载的action里前面加上一句：
set_time_limit(0);//不设置超时时间
?然后再执行下载，发现php里不再报错，但是页面端返回504超时。在页面的response里能看到提示的是nginx报错，去nginx日志里查，果然有Fatal的错误：
2017/10/31 15:25:03 [error] 15639#0: *696311 upstream timed out (110: Connection timed out) while reading response header from upstream, client: 61.148.196.162, server: manage.dev.acewill.net, request: "POST /statisticTrade/exportTrade?start=2017-09-30&end=2017-10-31&cardno=&phoneNo=&sid=&type=0&tctype=&pay=&grid=all&source=&startTime=0&endTime=11&tcId= HTTP/1.1", upstream: "fastcgi://127.0.0.1:9001", host: "manage.dev.acewill.net", referrer: "http://manage.dev.acewill.net/statisticTrade/trade?start=2017-09-30&startTime=0&end=2017-10-31&endTime=11&serialnumber=&cardno=&type=&tctype=0&pay=&grid=all&shopselector_shops=&shopselector_isEntire=true&sid=all&search=&entire_chk=on&lv=on&lv=on&chk=on&chk=on&chk=on&chk=on&lv=on&lv=on&chk=on&chk=on&chk=on&chk=on&chk=on&chk=on&chk=on&chk=on&chk=on&lv=on&lv=on&chk=on&chk=on&chk=on&chk=on&lv=on&lv=on&chk=on&lv=on&lv=on&chk=on&lv=on&lv=on&chk=on&lv=on&lv=on&chk=on&lv=on&lv=on&chk=on&lv=on&lv=on&chk=on&chk=on&chk=on&lv=on&chk=on&chk=on&source=all"
?
然后找到运维同事，帮忙修改一下服务器nginx的配置，反向代理超时，下载15万8千多条数据成功。
修改配置就是：
NGINX反向代理的超时报错，解决方法： server { listen *:80; server_name manage.dev.acewill.net; index index.php; access_log logs/manage.dev.access.log main; error_log logs/manage.dev.error.log notice; root /data/dev/www/WeLife/manage_www; charset utf-8; location / { if (!-e $request_filename) { rewrite ^/(.*) /index.php last; } location ~ .*\.(php|php5)?$ { fastcgi_pass 127.0.0.1:9001; fastcgi_index index.php; fastcgi_read_timeout 200; fastcgi_param SCRIPT_FILENAME /scripts$fastcgi_script_name; include fastcgi.conf; } location ~ .*\.(gif|jpg|jpeg|png|bmp|swf)$ { expires 30d; } }
?
?生效的主要就是那个fastcgi_read_timeout时间。改成了200，测试下载158500条数据没问题。
?
这里贴一下关于写csv文件的重要代码，
$header = array('流水号','卡号/手机号','交易类型','消费总金额（元）','实收金额（元）','使用储值（元）','使用储值实收金额（元）','使用储值奖励金额（元）','券抵扣（元）','使用代金券','使用礼品券','积分抵现（元）','奖励代金券','奖励礼品券','奖励积分','累计次数返券活动','支付方式','会员等级','门店','交易时间','来源','操作人','备注');
if ($listType == self::LIST_TYPE_COUPON) {
$header = array('流水号','卡号/手机号','消费总金额（元）','使用储值（元）','积分抵现（元）','实收金额（元）','支付方式','购买详情','交易类型','门店','交易时间','操作人');
}
$path = '/tmp/statistic/';
$fileName = md5($this->_bid.$starttime.$endtime) . '.csv';
$exportName = "消费流水".$starttime.'-'.$endtime.'.csv';
if ($listType == self::LIST_TYPE_COUPON) {
$exportName = "商品售卖流水".$starttime.'-'.$endtime.'.csv';
}
$fpath = '/tmp/statistic/'.$fileName;
$appName = basename(Bootstrap_Env::get('app_path'));
$dir = Config::get("$appName");
$dir = rtrim($dir, '/') . '/' . ltrim($fpath, '/');
// 自动创建不存在的目录
$directory = dirname($dir);
if (!is_dir($directory)) {
mkdir($directory, 0755, true);
}
chmod($directory, 0755);
if (file_exists($dir)) {
chmod($dir, 0644);
}
$fp = fopen($dir, 'w');
if (!$fp) {
throw new Exception('文件无法打开');
}
// 输出Excel列名信息
$head = $header;
foreach ($head as $i => $v) {
// CSV的Excel支持GBK编码，一定要转换，否则乱码
$head[$i] = iconv("utf-8", "gbk//TRANSLIT", $v);
}
// 将数据通过fputcsv写到文件句柄
fputcsv($fp, $head);
$staModel = new WeLife_models_Statistic();
//线上当天数据不用分页处理
$pageOptions = array();
$pageOptions1 = array();
$order = array('tcCreated' => 'DESC');
if($starttime < date('Ymd', time())){
$sh = 0;
}else{
$sh = $startHour;
}
if($endtime >= date('Ymd', time())) {
while (true) {
$pageOptions = array('currentPage' => $pageOptions['currentPage'] + 1, 'perPage' => 10000);
$todayConsumers = $staModel->listOnLineConsumeDetail($this->_bid, $sid, $grid, $memberType, $resource, $paytype, $consumetype, $cardNo, $phoneNo, $storedtype, $order, Date('Y-m-d', time()), Date('Y-m-d', time()), $sh, $endHour, $pageOptions,array(),'',$tcid,$tableType);
if ($todayConsumers['data']) {
//活动名称的集合
$actResArr = array();
$aids = array();
foreach ($todayConsumers['data'] as $cons) {
if ($cons['aid']) {
$aids[] = $cons['aid'];
}
}
$aids = array_unique($aids);//aid去除重复
if ($aids) {
$activitys = WeLife_src_Rpc_YarClient_Activity::get($this->_bid, $aids);
if ($activitys) {
foreach ($activitys as $key => $act) {
$actResArr[$key] = $act['aName'];
}
}
}
foreach ($todayConsumers['data'] as $key => $data) {
if ($listType == self::LIST_TYPE_COUPON) {
$v = $this->_coupondealExportData($data, $tcCouponType, $payTypes, $sources, $actResArr, $gradeRules);
} else {
$v = $this->_dealExportData($data, $tcType, $payTypes, $sources, $actResArr, $gradeRules);//这里单独的方法，处理数据，将数据按文件需要的列的顺序组成一个array
}
foreach ($v as $i => $va) {
$row[$i] = iconv("utf-8", "gbk//TRANSLIT", $va);
}
fputcsv($fp, $row);//注意这里就是将每一行数据逐行写入到文件中。
}
}
$pageOptions = $todayConsumers['pageOptions'];
// 没有更多页。
if ($pageOptions['currentPage'] * $pageOptions['perPage'] >= $pageOptions['totalItems']) {
break;
}
}
}
?
其中将数据库查的数据拼装成下载的文件所需要的列，且按列的顺序，代码如下：
private function _dealExportData($data,$tcType,$payTypes,$sources,$actResArr,$gradeRules){ $cancelType = Consts::get('Trade', 'Trade_src_Entity_TradeConsume', 'TYPE_CANCEL_CONSUME'); if($data['tctype'] == $cancelType){ $status = "-"; }else{ $status = ""; } $tcIdstr = $data['tcid'] ."\t" ; //加上"\t"避免科学计数法 if($tcType[$data['tctype']] == '撤销消费'){ $tcIdstr = $tcIdstr . '（原流水号：'.$data['tcrelateid'].'）'; } if($data['tcThirdId']){ $tcIdstr = $tcIdstr . '(微信商户单号：'.$data['tcThirdId'].')'; } if(in_array($data['tcsourcetype'], array(Consts::get('Trade', 'Trade_src_Entity_TradeConsume', 'SOURCE_TYPE_YPOS'), Consts::get('Trade', 'Trade_src_Entity_TradeConsume', 'SOURCE_TYPE_WANGPOS'), Consts::get('Trade', 'Trade_src_Entity_TradeConsume', 'SOURCE_TYPE_SUNMIPOS')))){ if($data['tcpaytype'] == Consts::get('Trade', 'Trade_src_Entity_TradeConsume', 'PAY_TYPE_WEIXIN') || $data['tcpaytype'] == Consts::get('Trade', 'Trade_src_Entity_TradeConsume', 'PAY_TYPE_WEIXIN_OFFLINE')) { $tcIdstr = $tcIdstr . '(微信商户单号：'.$data['tctransnumber'].')'; }else if($data['tcpaytype'] == Consts::get('Trade', 'Trade_src_Entity_TradeConsume', 'PAY_TYPE_ALIPAY')) { $tcIdstr = $tcIdstr . '(支付宝交易号：'.$data['tctransnumber'].')'; } } $trades[] = $tcIdstr; if($data['uphone'] != 0){ $trades[] = $data['uphone'] .'（卡号：'.$data['uno'].'）'; } else { $trades[] = $data['uno']."\t"; } $trades[] = $tcType[$data['tctype']]; $trades[] = $tcType[$data['tctype']] == '储值过期'?number_format($data['overdue']/100,2):$data['tctotalfee'] == 0?'-': number_format($data['tctotalfee']/100,2); $trades[] = $data['tcfee'] == 0?'-': number_format($data['tcfee']/100,2); $trades[] = $tcType[$data['tctype']] == '储值过期'?number_format($data['overdue']/100, 2):($data['tcstoredpay'] == 0?'-':number_format($data['tcstoredpay']/100,2)); $useStoredPay = 0; $usePresentPay = 0; if($tcType[$data['tctype']] == '储值过期'){ $useStoredPay = $data['overduetclprinciple'] == null ?'-':number_format($data['overduetclprinciple']/100,2) ; }else{ $useStoredPay = $data['tclprinciple'] == null?'-':number_format($data['tclprinciple']/100,2) ; } if($tcType[$data['tctype']] == '储值过期'){ $usePresentPay = $data['overduetclprofit'] == null?'-':number_format($data['overduetclprofit']/100,2); }else{ $usePresentPay = $data['tclprofit'] == null?'-':number_format($data['tclprofit']/100,2) ; } $trades[] = $useStoredPay; //使用储值实收金额（元） $trades[] = $usePresentPay; //使用储值奖励金额（元） $couPayMoney = $data['tccouponpay'] + $data['tcgiftcouponpay']; $trades[] = $couPayMoney == 0 ? '-':number_format($couPayMoney/100, 2); //使用代金券coupon if($tcType[$data['tctype']] == '撤销消费'){ $coup = $data['cancelcoupon'] ; }else { $coup = $data['usecoupon']; } $trades[] = $this->_dealCoupon($status,$coup); //礼品券 if($tcType[$data['tctype']] == '撤销消费'){ $giftcoup = $data['cancelgiftcoupon'] ; }else{ $giftcoup = $data['usegiftcoupon']; } if($giftcoup){ $giftStr = $this->_dealCoupon($status,$giftcoup); if($giftStr != '-'){ $giftStr = $giftStr .'抵扣'.number_format($data['tcgiftcouponpay']/100,2) .'元'; } }else{ $giftStr = '-'; } $trades[] = $giftStr; $trades[] = $data['pointpay'] == 0?'-':number_format($data['pointpay']/100,2);//积分抵现金额 $trades[] = $this->_dealCoupon($status,$data['sendcoupon']); //奖励代金券 $trades[] = $this->_dealCoupon($status,$data['sendgiftcoupon']); //奖励礼品券 $trades[] = $data['sendpoint'] == 0?'-':$data['sendpoint'];//奖励的积分 $trades[] = $tcType[$data['tctype']] == '储值过期'?'-':$data['tclactivityaddup'] == '' || $data['tclactivityaddup'] == 0 ? '-':$actResArr[$data['aid']] == '' ? '-' : $actResArr[$data['aid']].' ' . $status . $data['tclactivityaddup'] .'次'; $trades[] = ($tcType[$data['tctype']] == '储值过期' || $data['tctotalfee'] == 0)?'-':($payTypes[$data['tcpaytype']]); $trades[] = $data['grid'] == 0 ?'普通会员': $gradeRules[$data['grid']]; $trades[] = $this->_shopNamesArray[$data['sid']] == ''?'-':$this->_shopNamesArray[$data['sid']]; $trades[] = date('Y-m-d H:i:s', strtotime($data['tclcreated'])); $tcResourceType = $sources[$data['tcsourcetype']]; if($data['tcsourcetype'] == Consts::get('Trade', 'Trade_src_Entity_TradeConsume', 'SOURCE_TYPE_OPENAPI') && $data['tcbizid']){ $tcResourceType .= '-' .$data['tcbizid']; } $trades[] = $tcResourceType ; $trades[] = $tcType[$data['tctype']] == '储值过期'?'-':$data['mname']; $trades[] = $tcType[$data['tctype']] == '储值过期'?'-':$data['remark']; return $trades; }
?
?
相关文章
