站長(zhǎng)資訊網(wǎng)在平時(shí)生活或其他時(shí)候，我們可能會(huì)需要大批量導(dǎo)出excel數(shù)據(jù)，所以將這次的優(yōu)化過程發(fā)布出來，希望對(duì)有需要的同學(xué)啟到一定的幫助。
項(xiàng)目后臺(tái)有導(dǎo)出幾 w 條數(shù)據(jù)生成 excel 的功能，剛好前同事的方法直接報(bào)內(nèi)存溢出錯(cuò)誤，
所以將這次的優(yōu)化過程發(fā)布出來，希望對(duì)有需要的同學(xué)啟到一定的幫助
先看優(yōu)化后效果：
異步生成數(shù)據(jù)且真實(shí)進(jìn)度條反饋進(jìn)度
2.進(jìn)度完成，前端 js 跳轉(zhuǎn)到下載地址
3.生成 csv 文件代替 excel ， 3.5w 條數(shù)據(jù)文件大小 8M
原代碼報(bào)錯(cuò)信息
[2020-09-27 09:21:13] local.ERROR: Allowed memory size of 536870912 bytes exhausted (tried to allocate 8192 bytes) {"userId":1,"exception":"[object] (Symfony\Component\Debug\Exception\FatalErrorException(code: 1): Allowed memory size of 536870912 bytes exhausted (tried to allocate 8192 bytes) at /Users/****/WebRoot/ValeSite/****/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Concerns/HasAttributes.php:879)
原代碼邏輯
$list = Good::with(['good_standard', 'good_standard.default_picture', 'good_standard.brand']) ...... ->selectRaw('goods.*')~~~~ ->get(); #內(nèi)存溢出點(diǎn) 1, 該 orm 返回?cái)?shù)據(jù)量為 3.5w 行數(shù)據(jù) ...... ~~~~ $list = $this->goodsRepository->batchGetFullGoodsScope($list); foreach ($list as $item) { $cell = []; ..... //沒條數(shù)組共 30 個(gè)元素 $cellData[] = $cell; } # 內(nèi)存溢出點(diǎn) 2 ，生成需要的數(shù)據(jù)，3w + 條數(shù)據(jù)時(shí)，內(nèi)存消耗大概在 110M + ..... Excel::create(...) #內(nèi)存溢出點(diǎn) 3 ， Maatwebsite/Laravel-Excel庫大批量生成也會(huì)內(nèi)存溢出 # 和直觀的代碼處理流，該代碼在小數(shù)據(jù)量時(shí)無問題
解決思路分析
-
orm 取數(shù)據(jù)優(yōu)化 (mysql)
-
對(duì)已獲取的 orm 數(shù)據(jù)二次處理后 , 數(shù)據(jù)存儲(chǔ)優(yōu)化
-
導(dǎo)出 excel 時(shí)， 導(dǎo)出優(yōu)化
后續(xù)所有的代碼功能都是圍繞該 3 個(gè)方向來處理
方案 1 （異步生成數(shù)據(jù) ）
思路分析：
前端 ajax 發(fā)送 excel 導(dǎo)出請(qǐng)求 ->后端結(jié)束請(qǐng)求且計(jì)算數(shù)據(jù)總條數(shù)， 按一定倍數(shù)拆分成多次 job 生成數(shù)據(jù) ->后端多個(gè)進(jìn)程異步執(zhí)行 job 隊(duì)列任務(wù)，按批次生成數(shù)據(jù) （每次執(zhí)行計(jì)數(shù)一次，數(shù)據(jù)寫入 redis） ->前端 ajax 輪詢獲取總次數(shù)和當(dāng)前已執(zhí)行次數(shù) （計(jì)算出進(jìn)度條 ） ->前端獲 ajax 輪詢結(jié)果總次數(shù) = 已執(zhí)行次數(shù) ~~~~（進(jìn)度100%），跳轉(zhuǎn)到下載地址 ->后端 redis 取數(shù)據(jù)，渲染生成 csv 文件（下載完成）
代碼實(shí)現(xiàn)：
前端代碼： jquery + Bootstrap
# 進(jìn)度條樣式，可在 bootstrap 找到 <section class="panel" id="export_loading_box"> <p class="panel-body m-b-10" style="display: none"> <p class="text-muted"> 數(shù)據(jù)導(dǎo)出中 ..... </p> <p class="progress progress-striped active"> <p class="progress-bar progress-bar-info" role="progressbar" aria-valuemin="0" aria-valuemax="100" style="width: 0%"> 0% </p> </p> </p> </section>
$(function () { $('.down-list').click(function () { let formName = $(this).attr('data-form') let data = $('#' + formName).serialize(); OE.params.url = $(this).attr('data-url'); OE.handle(data); }); }) //商品導(dǎo)出 JS 控件 let OE = window.OE || {} OE = { params: { ifRun: 0, url: '', cachePre: '', }, # 1. 前端 ajax 發(fā)送 excel導(dǎo)出請(qǐng)求 handle: function (formData) { if (OE.params.ifRun) { return false; } OE.params.ifRun = 1; OE.rateShow(100, 0); $('#export_loading_box').find('.panel-body').show(); $.getJSON(OE.params.url, formData + '&run=1', function (data) { if (200 == data.status) { OE.params.cachePre = data.cachePre; //請(qǐng)求成功， 渲染進(jìn)度條 OE.init(); } else { OE.showAlert(false, data.msg); } }) }, # 4. ajax 輪詢渲染進(jìn)度條 init: function () { let t = setInterval(function () { $.getJSON(OE.params.url, "get_run=1&cache_pre="+OE.params.cachePre, function (data) { OE.rateShow(data.total, data.run); if (200 == data.status && data.total == data.run) { clearInterval(t); OE.params.ifRun = 0; OE.showAlert(true); //跳轉(zhuǎn)下載 excel window.location.href = OE.params.url+'?cache_pre='+OE.params.cachePre; return; } }); }, 2000); }, showAlert: function (success, msg) { if (success) { html = '<p class="alert alert-success fade in">' + ' <button data-dismiss="alert" class="close close-sm" type="button">' + ' <i class="fa fa-times"></i>' + ' </button>' + ' <strong>導(dǎo)出成功</strong> 數(shù)據(jù)下載中' + ' </p>'; } $('#export_loading_box').append(html); $('#export_loading_box').find('.panel-body').hide(); }, # 進(jìn)度條計(jì)算 rateShow: function (total, run) { let width = ((run / total) * 100).toFixed(0); $('#export_loading_box').find('.progress-bar').css('width', width + '%'); $('#export_loading_box').find('.progress-bar').text(width + '% '); } }
后端代碼 ：
2.后端總?cè)肟?/p>
// 前端第一次請(qǐng)求觸發(fā)代碼實(shí)現(xiàn) $listOrm = self::getGoodOrm(); //求總，初始化 job 數(shù)據(jù) $total = $listOrm->count(); for ($page = 0; $page <= ($totalPage - 1); $page++) { //創(chuàng)建子隊(duì)列 CreateExportGoodData::dispatch($requestData, $page, $authAdmin->id, $cachePre) ->onQueue(self::JOB_QUEUE); }
3.后端異步子隊(duì)列執(zhí)行任務(wù) （和前端無關(guān)）
self::$requestData = $requestData; $listOrm = self::getGoodOrm(); $list = $listOrm->offset($page * self::PAGE_NUM) ->limit(self::PAGE_NUM) ->orderByDesc('goods.id') ->get(); //數(shù)據(jù)清洗過程 ...... //執(zhí)行次數(shù)遞增 1 Redis::incr(self::GE_RUN_KEY . $cachePre . ':' . $adminId); //清洗后的數(shù)據(jù)壓入 redis 列表 Redis::lpush(self::GE_DATA_KEY . $cachePre . ':' . $adminId, serialize($data));
4.后端實(shí)現(xiàn)前端 ajax 輪詢執(zhí)行進(jìn)度反饋代碼實(shí)現(xiàn)
$total = Redis::get(GoodsExportRepository::GE_TOTAL_KEY. $cachePre. ':'. $authAdmin->id); $run = Redis::get(GoodsExportRepository::GE_RUN_KEY. $cachePre. ':'. $authAdmin->id); if ($request->input('get_run')) { //前端 ajax 輪詢獲取同步已運(yùn)行隊(duì)列數(shù) return ['status' => 200, 'total' => $total, 'run' => $run]; }
6.后端實(shí)現(xiàn)前端 excel 下載代碼實(shí)現(xiàn)
$fileName = "商品導(dǎo)出" . date('Y-m-d'); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="' . $fileName . '.csv"'); header('Cache-Control: max-age=0'); //開啟預(yù)輸出流 $fp = fopen('php://output', 'a'); //輸出商品列表數(shù)據(jù) while (true) { //核心1 從 redis 列表里依次取數(shù)據(jù) $data = Redis::rpop(self::GE_DATA_KEY . $cachePre . ':' . $adminId); if (!$data) { // redis 列表數(shù)據(jù)為空，結(jié)束 while 循環(huán) break; } //核心2 ob_flush(); //取出 $fb 輸出流 存入 buffer 內(nèi)數(shù)據(jù) flush(); //直接渲染至 http 數(shù)據(jù)流至瀏覽器 $data = unserialize($data); foreach ($data as $row) { foreach ($row as $key => $value) { if (is_string($value)) { $row[$key] = iconv('utf-8', 'gbk//IGNORE', $value); } } fputcsv($fp, $row); } } fclose($fp); //必須 exit 阻止框架繼續(xù)輸出 exit();
至此，異步導(dǎo)出 excel 已完成
總結(jié)：
-
后端隊(duì)列任務(wù)生產(chǎn)數(shù)據(jù)錄入 redis list
-
前端 ajax 輪詢獲取執(zhí)行情況
-
前端 獲取后端已將所有 隊(duì)列任務(wù)執(zhí)行， 跳轉(zhuǎn)至下載地址
-
下載地址取 redis 內(nèi)數(shù)據(jù)，渲染成 csv 文件
優(yōu)點(diǎn)：
-
異步多隊(duì)列進(jìn)程執(zhí)行，效率高
-
前端可實(shí)時(shí)獲取執(zhí)行進(jìn)度， 用戶體驗(yàn)好
缺點(diǎn)：
-
ajax 輪詢占用正常用戶請(qǐng)求資源，該方案只適合后臺(tái)實(shí)現(xiàn)
-
代碼復(fù)雜， 施工人員需一定的 laravel 隊(duì)列知識(shí)和 前端知識(shí)儲(chǔ)備；對(duì)自己把握不足的同學(xué)可直接看第二種解決方案
方案 2 （同步生成數(shù)據(jù) ）
思路分析：
-
設(shè)置 php 腳本時(shí)間 set_time_limit(0);
-
orm 依次獲取數(shù)據(jù)，對(duì)獲取的數(shù)據(jù)直接清洗后直接寫入 輸出流， 輸出至瀏覽器
代碼實(shí)現(xiàn)：
set_time_limit(0) //直接輸出頭部聲明 $fileName = "商品導(dǎo)出" . date('Y-m-d'); header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="' . $fileName . '.csv"'); header('Cache-Control: max-age=0'); //開啟輸出流 $fp = fopen('php://output', 'a'); // while 循環(huán)取數(shù)據(jù) $page = 0; while (true) { $listOrm = self::getGoodOrm(); $list = $listOrm->offset($page * self::PAGE_NUM) ->limit(self::PAGE_NUM) ->orderByDesc('goods.id') ->get(); if ($list->isEmpty()) { //無數(shù)據(jù)時(shí)退出 while 循環(huán) break; } //數(shù)據(jù)清洗 $data = ..... //直接將清洗后的 $data 數(shù)據(jù)寫入輸出流 foreach ($data as $row) { foreach ($row as $key => $value) { if (is_string($value)) { $row[$key] = iconv('utf-8', 'gbk//IGNORE', $value); } } fputcsv($fp, $row); } //輸出至瀏覽器 ob_flush(); flush(); } fclose($fp); exit();
總結(jié)
-
優(yōu)點(diǎn)： 代碼流程簡(jiǎn)單，開發(fā)難度低
-
缺點(diǎn)： 前端體驗(yàn)差， （ 數(shù)據(jù)單進(jìn)程獲取，效率低） 下載等待耗時(shí)長(zhǎng) ）
不管是異步還是同步， 實(shí)際思路都是分頁獲取數(shù)據(jù)， 對(duì)分頁獲取的數(shù)據(jù)進(jìn)行處理； 都有用到核心方法：
fopen('php://output', 'a') , ob_flush(), flush();
推薦學(xué)習(xí)：php視頻教程
