降本增效之应用优化 (二) 大报表
背景
在微服务或者单体业务系统中，都会有对应的管理后台系统，使用者通常为负责具体业务的产品和运营，这时候就会有一个经典的通用功能: “报表导出”。 为什么不直接在后台页面看数据，而是要导出到 Excel 呢？开发人员可能无法理解，但是站在产品和运营的角度，Excel 文件具有以下优势:
- 支持各种数据运算 (例如取某个字段的中位数和众数)，而不仅仅限于后台提供的分组和排序运算
- 支持各项数据组合使用，而不仅仅限于后台提供的固定格式和形式
- 可以直接根据数据生成报表，PPT 等演示文件
- 可以直接使用除了 SQL 功能之外的高阶函数 (例如 VLoopUp, 统计类函数等)
- 方便数据集成到其他系统中
- 方便数据和其他系统中的数据异构组合使用
- …
简单来说，Excel 可以为数据处理人员提效，并且大大降低开发人员的工作量，管理后台只需要提供最基础的数据筛选、列表、统计等功能即可， 剩下的由数据处理人员在 Excel 完成，从而实现双赢。
注意: 本文主要针对的是总量在几十万到十亿内的数据规模，不考虑数据量 10W 以下的场景，以及海量数据的场景，前者的规模无需优化，后者有专门的大数据处理框架集。
功能设计
问题
大多数情况下，大报表可以分为两种场景:
- 数据量大，从几千到几十万不等，例如订单数据、用户数据、日志数据
- 计算量大，最后结果数据可能就几十条，但是背后的计算量大，例如统计类数据、运营类数据
不论哪种场景，如果按照普通接口同步的方式请求直接导出报表文件，通常的前端请求超时 504，后端程序中的 SQL 语句把数据库的 CPU 打满 (CPU 100% 问题)，或者应用服务器的内存跑满 (OOM)， 最终的结果是导致其他请求阻塞，甚至导致数据库宕机。
优化设计
为了解决上面提到的问题，我们对整个报表文件的导出流程进行拆分:
- 数据查询/计算异步执行
- 单次执行查询/计算一部分数据 (例如总查询导出数据量为 20W, 那么单次可以查询 5000 条，总共查询 40 次)
- 查询到的数据以追加方式写入文件，缓解内存和磁盘 IO 的压力
核心设计思想是异步计算 + 数据分块 + IO 追加写入
按照上面的方法解决了性能问题之后，我们可以针对导出功能本身做一些体验上的优化，例如在用户页面上增加一些字段:
- 报表任务的提交时间
- 任务预计开始时间
- 任务实际开始时间
- 任务预计完成时间
- 任务实际完成时间
- 任务当前进度
- 任务完成总用时
- 任务发生错误信息 (如果导出报表中途发生了错误)
- 报表文件下载地址
- 可以随时取消任务
代码实现
现在，我们基于上面的设计思路，来实现功能的核心部分，限于篇幅，本文的代码主要以功能整体骨架设计为主，具体的方法细节不做实现，主要分为下面几个方面来说明:
- 设计任务数据结构
- 设计接口
- 创建任务
- 计算任务预计开始时间
- 计算任务预计完成时间
- 更新任务当前进度
- 计算 & 更新任务总用时
- 更新报表文件下载地址
1. 数据结构
Task 对象表示单个报表导出任务。
type Task struct {
ID int `json:"id"` // 任务 ID
Name string `json:"name"` // 任务名称
URL *url.URL `json:"url"` // 创建任务的 URL (也就是用于界面发起请求的 URL)
Created *time.Time `json:"created"` // 创建时间
Canceled *time.Time `json:"canceled"` // 取消时间
ExpectedRunTime *time.Time `json:"expectedRunTime"` // 预期开始时间
ExpectedFinishedTime *time.Time `json:"expectedFinishedTime"` // 预期完成时间
ActualRunTime *time.Time `json:"actualRunTime"` // 实际开始时间
ActualFinishedTime *time.Time `json:"actualFinishedTime"` // 实际完成时间
ProgressPercent float32 `json:"progressPercent"` // 任务当前进度
TimeCost float32 `json:"timeCost"` // 任务总用时
ErrorMsg string `json:"errorMsg"` // 任务错误信息
ResultFile string `json:"resultFile"` // 生成的文件下载地址
}
2. 接口
Exporter 表示报表导出接口，我们可以实现不同业务报表导出的具体功能 (例如订单服务中的每周/每月/年度 销售额报表，用户服务中的 日活/月活/新增 报表)， 限于篇幅，接口中只保留创建任务、取消任务、更新任务状态和数据三个抽象方法。
type Exporter interface {
Create() *Task
Cancel(*Task)
Update(*Task)
}
下面是两个报表导出的具体实现示例:
// OrderListExport 订单列表数据报表导出
// 限于篇幅，这里只输出代码结构作为演示
type OrderListExport struct {
...
}
func (ol *OrderListExport) Create() *Task {
...
}
// OrderStatisticExport 订单分析数据报表导出
// 限于篇幅，这里只输出代码结构作为演示
type OrderStatisticExport struct {
...
}
func (os *OrderStatisticExport) Create() *Task {
...
}
3. 创建任务
当用户在页面上发起请求时，直接调用具体功能的 Create 方法即可，例如 “订单列表数据报表导出” 请求直接调用 OrderListExport.Create 方法，
任务创建完成后返回给前端具体的响应代码，因为任务是异步执行的，所以符合 Restful 规范的 HTTP StatusCode 应该是 201，下面是示例代码。
# 示例: 导出 2023 年 8 月份所有订单记录
POST https://console.example.com/orders/export?start_time=2023-08-01&end_time=2023-08-31
201 Created
下面是示例请求发出之后，返回的对应数据:
{
"code": 0,
"msg":0,
"data": {
"location": {
"uri": "https://admin.example.com/tasks/exports/123456"
},
"task": {
"id": 123456,
"name": "OrderList Export Test",
"created": "2023-09-17T09:26:38.084577069+08:00",
"canceled": null,
"expectedRunTime": "2023-09-17T09:27:38.084577069+08:00",
"expectedFinishedTime": "2023-09-17T09:28:38.084577069+08:00",
"actualRunTime": null,
"actualFinishedTime": null,
"progressPercent": 0,
"errorMsg": "",
"resultFile": ""
}
}
}
除了 task 字段表示的对象之外，这里还增加了一个新的对象 location, 用来表示查看已创建资源状态的链接，前端可以根据该链接增加一些交互功能，
例如在用户创建导出任务之后，给出对应的弹窗提示并引导用户点击跳转到具体任务的详情页面。
4. 计算任务预计开始时间
任务执行主要分为两大类:
- 串行执行: 只需要获取任务队列中最后一个任务的 “预计结束时间”，然后加一个任务开始前的常数时间 (例如 5 秒)，就是任务的预计开始时间
- 并行执行：这个需要结合任务队列中已有的任务类型以及当前系统负载情况来考虑，因为其中涉及到一些排队论和系统负载均衡调度相关的主题，和本文主题关系不大，暂且跳过
在上面的示例代码中，我们假设用户创建的导出任务在创建 1 分钟之后自动开始。
5. 计算任务预计完成时间
对导出任务进行估时，也可以分为两种情况:
导出列表数据型任务
- 先根据筛选条件查询出总数据条数 (T)
- 指定单次导出最小数量 (N)
- 计算出导出 N 条数据 + 写入文件的用时 (S)
- 任务总用时 TN = T / N * S (这里假设数据库和应用服务器都处于稳定运行中)
- 在 TN 的基础之后，乘以一个百分比常数，作为整个任务导出流程中的缓冲时间
- 最后得到是就是一个比较宽裕的任务预计完成用时
举个例子来说明:
- 假设根据筛选条件查询出总的数据条数 T = 120000
- 指定单次导出最小数量 N = 5000
- 假设单次导出数据 + 写入文件用时 S = 2 秒
- 任务总用时 TN = 120000 / 5000 * 2 = 48 秒
- 指定缓冲百分比常数为 1.2, 任务预计完成用时 = 48 * 1.2 = 57.6 秒
导出统计数据型任务
例如现在要导出以下统计数据 (时间范围为 2023 年 8 月):
- 用户总增长
- 订单总量
- 订单总量增长百分比
- 订单总金额
- 订单总金额增长百分比
- 订单商品总数量
- 退货率
因为不同的业务维度数据计算方式差异非常大，所以 无法 根据下面这个简单的公式来计算出任务的预计执行时间。
任务预计总用时 = 单个业务维度数据用时 * 维度数量
针对这个问题，笔者目前也没有特别好的解决方案，所以目前在项目中使用的方案为:
- 从导出的业务维度指标中随机选取 3 个 (N)
- 将筛选条件中的时间范围分割为最小粒度的时间片 (T)
- 计算 N * T 得出 3 个指标数据在最小粒度的导出用时 (S)
- 根据上面的结果数值计算出任务预计总用时 (TN)
举个例子来说明:
- 假设业务维度指标数量为 12 个，随机选取 3 个
- 时间范围为 31 天，指定分割的最小粒度时间片 T = 1 天
- 假设 3 个指标计算 1 天的数据 (包括写入文件)，用时 S = 1 秒
- 任务预计总用时 TN = (12 / 3) * (31 / 1) * 1 秒 = 124 秒
最后的时间值可以像上面的 {导出列表数据} 任务一样，乘以一个百分比常数，作为整个任务导出流程中的缓冲时间，这里不再赘述。
6. 更新任务当前进度
将导出任务根据时间片进行拆分之后，计算出时间片的个数 (N), 那么单个子任务完成应该更新的进度值 (P) 可以使用如下公式计算得出。
P = floor (100 / N)
转换成对应的伪代码如下:
N := 20
P := math.Floor(float64(100 / N))
for _, subTask := range subTasks {
// 执行数据查询逻辑
...
// 执行追加写入文件逻辑
...
subTask.ProgressPercent += P
export.Update(subTask)
}
// 任务全部完成，更新进度为 100%
subTask.ProgressPercent = 100
export.Update(subTask)
7. 计算 & 更新任务总用时
任务完成时使用 {当前时间 - 任务实际开始时间} 得到任务总用时，更新 TimeCost 字段即可。
8. 更新报表文件下载地址
当任务完成后，将任务导出过程中写入本地的文件推送到存储中心 (例如阿里云的 OSS)，然后生成加密访问链接并更新 ResultFile 下载地址字段。
不足之处
本文主要从 功能流程设计、代码实现框架、导出流程+示例 三个方面介绍了如何优化 {大报表数据导出} 业务场景，到这里就全部介绍完了。 最后我们来总结下实际项目中还会遇到的其他问题:
- 数据库采用了分库分表
- 数据库负载并不是一直处于稳定状态，有峰值和低谷
- 应用服务器负载升高
- 数据库主从之间的数据同步延迟问题对报表数据的影响
- 多个报表中可能存在的数据组合、优化等问题
希望读者有了本文的框架基础之后，在遇到具体的问题时可以轻松快速地找到性能瓶颈，并给出实际可行的优化解决方案。
小结
应用层业务开发中，很多功能模块可能 “技术含量” 并不高，但是却能给使用者带来良好的用户体验，当功能的使用者是公司高管甚至业务大老板时， 功能体验本身会自带杠杆，开发人员应该尽可能抓住这些潜在的机会，同时在开发完功能之后要在组内甚至跨部门自我宣传，这种案例积累的越多， 开发人员在公司内部和外部的机会也越来越多。如果读者有过内部业务系统开发或者担任过技术管理工作，应该对上面的话深有体会。
