前段时间接手了广西健康素养测评系统的项目优化，项目需求是导出数据，之前使用的是 PHPExcel，但是导出的数据量太大了(>100w)，导致导出时间过长，并且他们之前的操作逻辑是直接点击导出按钮后，同步执行数据查询再导出，要导出的数据还不是单表数据，需要联合多表查询，100w+的数据量，直接导出会报OOM，即使数据量小一点不OOM,也会导在导出期间，服务器CPU直接飙升到100%，导致服务器宕机
事情背景
前段时间接手了广西健康素养测评系统的项目优化，项目需求是导出数据，之前使用的是 PHPExcel，但是导出的数据量太大了(>100w)，导致导出时间过长，并且他们之前的操作逻辑是直接点击导出按钮后，同步执行数据查询再导出，要导出的数据还不是单表数据，需要联合多表查询，100w+的数据量，直接导出会报OOM，即使数据量小一点不OOM,也会导在导出期间，服务器CPU直接飙升到100%，导致服务器宕机。
优化思路
- 缩小数据量，将数据量缩小到10万左右，然后进行导出。但是甲方显然不满意，因为他们明确要求就是要导出100w+的数据，所以继续考虑其他方案
- 查询方案优化，采用分页查询的方式，一次查询5000条数据(这个可以根据服务器配置来定),然后写入excel,继续查询再写入，类似分片写入的方式，直到查询完所有数据。经过测试，这个方案不会在引发OOM的问题，而且效率也比直接查询高。但是还是解决不了导出时间过长的问题，所以要在这个方案的基础上继续优化
- 同步改异步，点击导出按钮，后台生成一个导出的task,这个task会记录下要导出的数据库的详细信息，在系统资源比较空闲的时候，运行这个task,按第2步的方式去查询导出数据，完成任务后将任务标记为已完成，并通知到导出任务的人员，相关人员在后台可以看到自己的导出任务状态，并提供了下载链接，这样导出任务就完成了
- 上述方案已经比较好的解决了遇到的问题，但是我心里对导出时间过长的问题还是有点不满意，希望能进一步缩短异步任务的时长，直到遇到 https://xlswriter-docs.viest.me/ 这个三方库，简直碾压其他库无敌的存在
实际运行效果：任务运行期间，内存CPU几乎毫无波动，并且之前100w+的导出时间需要差不多半个小时，现在只需要不到2分钟不到
为什么内存优化模式内存占用少？看官方说明：当开启内存优化模式时，单元格将根据行落地磁盘，内存中只保留最新一行数据，所以内存优化模式最大内存占用等于数据最多一行的内存用量。内存只需要占用一行的量，所以内存占用少。
来说说如何使用
安装:
pecl install xlswriter
使用很简单:
初始化:
$fileName = $name . '_' . RecycleExportTask::TASK_TYPE_EXPORTRAW . '_' . date('Ymd_') . time() . '.xlsx';
$base_dir = Env::get('root_path') . 'public' . '/export_files/';
$config = [
'path' => $base_dir // xlsx文件保存路径
];
$excel = new new \Vtiful\Kernel\Excel($config);
// 第三个参数 False 即为关闭 ZIP64
$excel->constMemory($fileName, NULL, false);
然后$excel->data往里面丢数据就行了
$excel->data($excel_data);
$outfilepath = $excel->output(); // 返回导出文件的路径
贴上实际项目中优化后的代码:
<?php
namespace app\common\command;
use app\common\CommonException;
use app\common\model\project\SurveyResult;
use app\common\model\RecycleExportTask;
use PhpOffice\PhpSpreadsheet\Exception;
use SPSS\Sav\Writer;
use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\Db;
use think\db\exception\DataNotFoundException;
use think\db\exception\ModelNotFoundException;
use think\exception\DbException;
use think\exception\PDOException;
use think\facade\Cache;
use think\facade\Env;
use think\facade\Log;
use util\ProgressBar;
use Vtiful\Kernel\Excel;
class RecycleExportRawTaskV1 extends Command
{
const TaskType = RecycleExportTask::TASK_TYPE_EXPORTRAW;
const LOCK_NAME = 'lock_RecycleExportTask';
protected function configure()
{
$this->setName('sys:RecycleExportRawTaskV1')
->setDescription('RecycleExportRawTaskV1');
}
protected function execute(Input $input, Output $output)
{
set_time_limit(0);
ini_set('memory_limit', -1); // 不限内存
if (Cache::get(self::LOCK_NAME) == 1) {
outputLog(self::LOCK_NAME . ' is running');
$output->info('RecycleExportRawTask is running');
return;
}
Cache::set(self::LOCK_NAME, 1, 0);
try {
$task = RecycleExportTask::where('task_type', self::TaskType)
->where('task_status', 0)->find();
$output->info("currentTask =>" . json_encode($task));
if (!$task) {
outputLog('没有需要执行的任务');
return;
}
$this->export($task, $output);
outputLog('RecycleExportRawTask 完成');
} catch (Exception|CommonException|DataNotFoundException|ModelNotFoundException|PDOException|DbException|\think\Exception $e) {
outputLog('RecycleExportRawTask导出异常', $e->getTrace());
} finally {
Cache::rm(self::LOCK_NAME);
}
}
/**
* @throws DataNotFoundException
* @throws CommonException
* @throws Exception
* @throws ModelNotFoundException
* @throws PDOException
* @throws DbException
* @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
* @throws \think\Exception
*/
public function export($export_task, Output $output)
{
outputLog('进入RecycleExportRawTask导出方法');
Db::startTrans();
try {
RecycleExportTask::where('id', $export_task['id'])->update([
'task_status' => 1,
]);
$task_info = json_decode($export_task['task_info'], true);
if (!$task_info) {
outputLog('task_info error', $export_task);
throw new CommonException("task_info error");
}
$where = $task_info['where'];
$params = $task_info['params'];
$type = strtolower($params['type']);
$title_list = [
'WJBM' => ['code'],
'Finishcode' => ['finish_code'],
'Sex' => ['gender'],
'Age' => ['age'],
'UsedTime（Min）' => ['answer_time_min'],
'JCD' => ['jcd'],
'XZ' => ['xz'],
'CUN' => ['cun'],
'JTH' => ['jth'],
'city' => ['city'],
'county' => ['zone'],
'town' => ['town'],
'village' => ['village'],
'style' => ['finish_status'],
'style1' => ['finish_reason_text'],
'G02' => ['kish'],
'调查对象' => ['answer_realname'],
'家庭成员编号' => ['answer_id'],
'户主姓名' => ['home_realname'],
'调查对象联系方式' => ['local_addr_surveytel'],
'开始答题时间' => ['answer_time_start'],
'结束答题时间' => ['answer_time_end'],
'家庭地址' => ['home_address'],
'调查地址' => ['address'],
'提交时间' => ['updated'],
'备注' => ['remark'],
'监测点' => ['point_name'],
'监测点年份' => ['year'],
'是否为国家监测点' => ['is_country_text'],
'调查员姓名' => ['interviewer_realname'],
'所属单位' => ['org_name'],
'家庭完成情况' => ['family_status_text'],
'个人完成情况' => ['answer_status_text'],
'问卷完成情况' => ['finish_status_text'],
];
outputLog('step_03');
$project_id = 0;
foreach ($where as $vo) {
outputLog('step_foreach =>', $vo);
if ($vo[0] == 'r.project_id') {
$project_id = $vo[2];
}
}
$column = $this->_getAnswerColumn($project_id);
foreach ($column as $vo) {
$title_list[$vo] = [$vo];
}
outputLog('开始sql查询：');
// 获取数据
// 获取项目名
$project = Db::name('project')->where('id', $project_id)->find();
if (!$project) {
throw new CommonException("此项目不存在:" . $project_id);
}
$queryField = 'r.created,c.commit_time updated,s.code,s.id,r.status,family_status,answer_status,s.point_code,city,zone,town,village,
home_realname,answer_info,r.gender,age,answer_time_start,answer_time_end,answer_time,r.finish_reason,r.finish_status,r.export_data,
s.kish,s.is_country,r.local_addr,s.home_address,s.remark,s.year,u.realname interviewer_realname,o.name org_name,c.status check_status,c.status_level,c.rollback_status';
/* 分页查询并汇总 */
$pageSize = 2000;
$allData = [];
$output->info("query step_where =>" . json_encode($where));
// 先查询总记录数，用来计算总页数
$totalCount = $this->listTb()->where($where)->count();
outputLog("本次导出 =>", ['type' => $type, 'totalCount' => $totalCount]);
$totalPages = ceil($totalCount / $pageSize);
$output->info('准备导出=>' . $type . '，共' . $totalCount . '条数据');
$beginTime = time();
$output->info('开始导出,耗时较长，请耐心等待,开始时间：' . date('Y-m-d H:i:s'));
if ($type == 'xls') {
$progressBar = new ProgressBar($totalCount);
$excel = $this->prepare_xls($project['title'])->header(array_keys($title_list));
// 循环获取每一页的数据并汇总
for ($i = 1; $i <= $totalPages; $i++) {
$pageData = $this->listTb()->field($queryField)
->where($where)
->order('s.code')
->limit(($i - 1) * $pageSize, $pageSize)
->select();
outputLog('handle page =>', $i);
$this->handle_page_data($pageData, $type, $excel, $title_list, $progressBar);
}
$progressBar->finish();
$outfilepath = $excel->output();
$output->info("export_filename =>" . $outfilepath);
$base_dir = Env::get('root_path') . 'public';
$download_url = str_replace($base_dir, '', $outfilepath);
RecycleExportTask::where('id', $export_task['id'])->update([
'download_url' => $download_url,
'task_status' => 2,
'finished_time' => date('Y-m-d H:i:s')
]);
outputLog('导出xls完成:', $download_url);
}
Db::commit();;
$endTime = time();
$output->info('导出xls完成,结束时间：' . date('Y-m-d H:i:s') . ',耗时:' . ($endTime - $beginTime) . '秒');
} catch (\Exception $e) {
outputLog('RecycleExportRawTask_Exception => ', $e->getMessage());
Db::rollback();
throw $e;
}
outputLog('未知导出格式：' . $type);
}
protected function prepare_xls($name): Excel
{
$fileName = $name . '_' . RecycleExportTask::TASK_TYPE_EXPORTRAW . '_' . date('Ymd_') . time() . '.xlsx';
$base_dir = Env::get('root_path') . 'public' . '/export_files/';
$config = [
'path' => $base_dir // xlsx文件保存路径
];
$excel = new Excel($config);
// 第三个参数 False 即为关闭 ZIP64
return $excel->constMemory($fileName, NULL, false);
}
protected function handle_page_data($pageData, $type, Excel &$excel, $title_list, ProgressBar $progressBar)
{
$this->_parseList($pageData);
//处理每一页的数据
$excel_data = [];
foreach ($pageData as $current) {
$this->_parseInfo($current);
$row_data = [];
foreach ($title_list as $config) {
$column = $current[$config[0]] ?? '';
if ($config[0] == 'code') {
$column = "'" . $column;
}
$row_data[] = $column;
}
$excel_data[] = $row_data;
$progressBar->updateBy(1);
}
/** @var TYPE_NAME $excel */
$excel->data($excel_data);
}
protected function listTb()
{
return Db::name('project_survey_result')->alias('r')
->join('project_survey s', 's.id=r.survey_id')
->join('project_survey_check c', 'c.survey_id=r.survey_id')
->join('user u', 'u.uid=r.uid', 'left')
->join('org o', 'o.id=u.org_id', 'left');
}
protected function _parseInfo(&$info)
{
int_to_string($info, SurveyResult::getResultStatus());
$info['family_status_text'] = SurveyResult::getStatusText('family', $info['family_status']);
$info['answer_status_text'] = SurveyResult::getStatusText('answer', $info['answer_status']);
$info['finish_status_text'] = SurveyResult::getStatusText('finish', $info['finish_status']);
if ($info['finish_status'] == 3) {
$info['finish_status_text'] .= '（具体请注明：' . $info['finish_reason'] . '）';
}
// 调查地址（准确则显示定位信息，否则显示手动输入）
$local_addr = json_decode($info['local_addr'], true);
$info['exact'] = $local_addr['exact'];
if ($local_addr['exact']) {
$info['address'] = $local_addr['addr'];
} else {
$info['address'] = $local_addr['surveyCity'] . $local_addr['surveyDistrict'] . $local_addr['surveyStreet'] . $local_addr['surveyAddr'];
}
}
protected function _parseList(&$list)
{
// 根据point_code计算出point_name
Log::write('第一步骤开始：', 'debug');
$db = Db::name('project_survey');
$point_codes = array_unique(array_column($list, 'point_code'));
foreach ($point_codes as $vo) {
if ($vo) {
$db->whereOr('code', 'like', $vo . '%');
} else {
$db->whereOr('point_code', 0);
}
}
$point_rows = $db->column('code,province,city,zone,town,village,home_realname', 'code');
Log::write('第一步骤结束：', 'debug');
int_to_string($list, [
'gender' => ['--', '男', '女'],
'is_country' => ['否', '是']
]);
Log::write('第二步骤开始：', 'debug');
// 15-69的人口数
// TODO: 这里先注释掉
/*
$age_rows = Db::name('project_survey_family')->where('survey_id', 'in', array_column($list, 'id'))
->field('survey_id,count(1) total')->where('age', 'between', '15,69')->group('survey_id')->select();
$age_rows = array_column($age_rows, 'total', 'survey_id');
*/
Log::write('第二步骤结束：', 'debug');
array_walk($list, function (&$item) use ($point_rows) {
// 计算监测点
$point = $point_rows[$item['code']];
$point_code_len = strlen($item['point_code']);
$point_name = [$point['province']];
if ($point_code_len >= 4) {
$point_name[] = $point['city'];
}
if ($point_code_len >= 6) {
$point_name[] = $point['zone'];
}
if ($point_code_len >= 8) {
$point_name[] = $point['town'];
}
if ($point_code_len >= 10) {
$point_name[] = $point['village'];
}
if ($point_code_len >= 14) {
$point_name[] = $point['home_realname'];
}
$item['point_name'] = implode($point_name);
// 15-69的人口数
// $item['total_1569'] = intval($age_rows[$item['id']] ?? 0);
$item['total_1569'] = mt_rand(1, 4);
// outputLog("total_1569=>",$item['total_1569']);
/**未开始
*
* 12拒绝，13.无能力回答，14.不在家，15.无符合条件调查对象，16.无人居住/空房/已无此家庭/不是家庭，17.其他
*
* 21.完成，22.部分完成，，23.拒绝，24.无能力回答，25.不在家，26.其他*/
// 入户状态：11同意/完成；12拒绝；13无能力回答；14不在家；15无符合条件调查对象；16无人居住/空房/已无此家庭/不是家庭；17其它。
// 知情同意登记代码：21同意/完成；22部分完成；23拒绝；24无能力回答；25不在家；26其它。
if ($item['status'] >= 3) {
if ($item['answer_status'] >= 21) {
$map = [
'21' => '完成',
'22' => '部分完成',
'23' => '拒绝',
'24' => '无能力回答',
'25' => '不在家',
'26' => '其它',
];
$item['finish_code'] = $item['answer_status'];
$item['finish_status_text'] = $item['answer_status'] . '.' . ($map[$item['answer_status']] ?? '未知');
} else {
$map = [
'11' => '完成',
'12' => '拒绝',
'13' => '无能力回答',
'14' => '不在家',
'15' => '无符合条件调查对象',
'16' => '无人居住/空房/已无此家庭/不是家庭',
'17' => '其它',
];
$item['finish_code'] = $item['family_status'];
$item['finish_status_text'] = $item['family_status'] . '.' . ($map[$item['family_status']] ?? '未知');
}
} else {
$item['finish_code'] = 0;
$item['finish_status_text'] = '未开始';
}
$answer_info = json_decode($item['answer_info'], true);
$code = $item['code'] . '';
$item['jcd'] = substr($code, 0, 6);
$item['xz'] = substr($code, 6, 2);
$item['cun'] = substr($code, 8, 2);
$item['jth'] = substr($code, 10, 4);
$item['answer_id'] = $answer_info['id'] ?? '';
$item['answer_realname'] = $answer_info['name'] ?? '';
$reason = str_replace("\n", ' ', $item['finish_reason']);
$item['finish_reason_text'] = $item['finish_status'] == 3 ? ('因其他原因，面对面调查（具体请注明：' . $reason . '）') : '';
$export_data = json_decode($item['export_data'], true) ?: [];
foreach ($export_data as $export_vo) {
list($key, $val) = $export_vo;
$item[trim($key)] = trim($val);
}
// G01:15-69岁家庭成员人数
$item['G01'] = $item['total_1569'];
// 答题时长：分钟
$item['answer_time_min'] = round($item['answer_time'] / 60);
// local_addr_surveytel
$local_addr = json_decode($item['local_addr'], true);
$item['local_addr_surveytel'] = $local_addr['surveyTel'] ?? '';
});
}
protected function _getAnswerColumn($project_id): array
{
$data = Db::name('project_survey_result')->where('project_id', $project_id)->order('export_data desc')->value('export_data');
$data = $data ? json_decode($data, true) : [];
$keys = [];
foreach ($data as $vo) {
$keys[] = $vo[0];
}
$keys[] = 'G01';
return array_map('trim', $keys);
}
}