关于导出优化

[yangweijie]

导出性能优化

导出的标准流程

1. 查询数据
2. 生成excel
3. 浏览器下载

导出遇到的问题

1. 时间过长 （未用索引、查询数据过大、查询次数过多）
2. 内存不够 （测试环境 和 生产环境差异）
3. 本地环境无扩展

解决思路

1. 不断优化sql，让查询次数尽量少
2. 优化代码逻辑，减少循环中套循环
3. mock excel生成

核心思想

让计算交给最快的处理者（DB、内存）来做

让计算只算一次

案例

门店提货订单导出

优化点:

• 去除无用的商品图片关联
• 商品分类和供应商 从查询所有订单后询换订单商品再一条条查询改为了关联商品预加载（N+1=>1）
• 定义了远程关联
• 导出不走扩展 前端xlsx 生成execl

8000多个订单（1万多商品）全部导出由当初60s 降至 5.72s

数字福利平台大牌卡券导出

优化点

• 写了一个基类提供 orderList 方法来获取数据 动态传入表名 order_type 且支持回调来跟改获取的字段
• Extend/DB 实现了 子查询 先debug 后获取 $db->last() 原始sql， 后 selectInSubQuery 查询时做替换
• 查询卡号时 不关联大表 app_merge_pay_order 来查询 先查询放入where 条件里 in 数组查询
• 将一些循环里的计算 放入 sql Db:raw 里 原生函数查询 如 'total_fee_mch' => DB::raw("CASE WHEN a.order_status=5 AND a.refund_status=1 THEN '0.00' WHEN d.edition = 1 THEN '0.00' WHEN a.total_fee_mch > 0 THEN a.total_fee_mch ELSE a.pay_money END"),
• 循环里的数据初始化值也放入sql 查询别名字段 'balance'=>DB::raw("'0'"),
• 去除循环判断 来取数据 改为先将 merge_pay_order 的数据按照 order_sn=> 订单集合 固化为键值 数组，循环整个列表时直接获取
• 表加查询用字段如mch_id、 order_type、 order_sn等
  31000个订单数据导出 优化前 257s 优化后 1.5s
• thinkphp6 以后默认模型返回结果集、Db返回数组，数据量大的查询用Db 12000行1.7s 立马变0.2秒 （7.4）

内存方面

单次查询能解决 不需要多次查询组装的 用游标，比chunk 快（chunk将一次查询拆多次分页了）

需要多次查询组装的， 可以考虑多进程分文件写入 最后合并

思考

PHP 处理大数组 给某个键 进行字符串格式化 快 还是 数据库快

如 有30000的数据 每个数据有时间戳create_time 循环30000次 date('Y-m-d H:i:s', $v['create_time'])
还是 查询结果集时 'create_time' => DB::raw('FROM_UNIXTIME(a.create_time)'),字段计算快?

前端生成excel 快还是 后端扩展快？

json 格式比较大 是否需要将数据压缩 gzip 或改为 ProtocolBuffer 传输更快

xlsx 单sheet 有上限

如何应对同条件导出多次？

缓存、 异步