开源SPL提速资产负债表60倍

问题描述

X 公司资产负债表，访问人员众多，访问频次很高，明细数据约 6000 万，业务人员要等待 60 秒以上才能看到结果，响应速度严重影响业务，急需优化。

报表表样如下：

解决过程

1、 理解业务和计算特征

这是一个典型的中国式复杂报表格式，其复杂并不在于布局，而在于其中“期末余额”的每个单元格都是一个需要独立计算的指标，互相之间几乎没有关系，事实上就是一个各种指标的汇总清单，而这些指标往往会有上百个之多。

在源数据表结构中，有一个字段称为科目，其长度总是固定的 10 位，如：1234567890，如下图：

科目字段的值实际上是一个分层的代码，而前面表里上百个指标就是根据需求对不同层次科目数据的统计结果，具体的做法是通过截取科目的前几位来确定层次，然后按需求 ** 自由组合，** 作为条件进行过滤，最后对金额字段进行累计汇总。

报表运算的 SQL 大体如下：

SELECT SUM(CASE
WHEN LEFT(科目, 4) = ‘1001’
OR LEFT(科目, 4) = ‘1002’ THEN 金额
ELSE 0
END) AS 指标 A,
SUM(CASE
WHEN (LEFT( 科目, 4) = ‘2702’
OR LEFT(科目, 6) = ‘153102’
OR LEFT(科目, 8) = ‘12310105’) THEN 金额
ELSE 0
END) AS 指标 B,
…
FROM T1
WHERE CONCAT(年, 月) <= ?

即每个单元格对应一个计算表达式，完整写出来的 SQL 会比以上片段的长度多几十倍，不但维护难度大，随着数据源数据量不断增大，基于明细数据，多次使用字符串截取函数再比较也会使得性能很差。

2、 确定优化方案

1、预汇总。

如果能够把数据事先按科目汇总，那么我们就可以不必重复累加科目相等的记录了，而且存储量也会变少，IO 也会更快。汇总结果的数据结构应当是：科目、年、月、本科目下当月的金额汇总值。

2、有序找。

如果能利用数据有序直接进行有序查找，将能够获得更好的查询效率。利用有序查找时，单主键比多主键查找更快。在预汇总时，我们还可以将年、月、科目号合并为一个主键。这样就将问题变为了单键值下的批量有序查找。

3、按位加。

每个格子需要查找的科目号不同，比如 [1207,1214] 或者[1001, 1207, 153102,…]，多个格子可能涉及相同的汇总项。常规方法需要对一次性批量有序查找出的结果按每个科目号再遍历后再去求和，即使用二分法在有序的 key 里找也需要计算量，如果可以直接按数据对应的位置找到累计金额汇总值再求和就会更快。

解释：指标 A 和指标 B 的所有科目号合并，然后统一排序生成序号，通过序号在有序结果集中找到对应的金额，再利用位置序号把金额倒回到每个指标中，每个指标下对多个科目号的金额汇总，即指标汇总值。

3、 确定技术选型和实施方案

关系型数据库理论上不支持有序存储，只能建立索引来快速取数，但数据库的索引在批量取多个索引值时失去效果（这里要取出数百个值，写到 IN 条件中时也很难利用索引），分别取会导致多次访问数据库，性能也很差。而且第三步的按位加也很难用 SQL 实现。所以只能放弃关系数据库。

这里涉及都是不再改变的历史数据，可以将数据外置到文件来自行处理，不仅方便实现上述算法，还有更好的 IO 性能。不过，使用 Java 或 C++ 等高级语言虽然可以实现上述优化方案，但编码量过大，实现周期过长，容易出现代码错误隐患，也很难调试和维护。

开源集算器的 SPL 语言可以为上述优化方案提供全面的算法支持，包括高压缩比的二进制文件、批量有序查找、序号对位计算等机制，能够让我们用较少的代码量快速实现这种个性化的计算。

4、 实现优化方案

第一步，在源数据上，用“年”和“月”两列字段动态计算一个变量值，可以称为“月号”，按照科目、月号分组，统计本科目下月号的累计金额。

月号的计算规则：假设原始数据是从 2014 年开始的，所谓 "月号" 就是每条记录的时间是从初始年份 1 月开始的第几个月。公式：月号 =(当前年 - 初始年)*12+ 当前月，举例：当前一条数据记录中年是 2017，月是 3 的话，那么根据这个公式的结果：月号 =(2017-2014)*12+3，也就是 2014 年 1 月开始的第 39 个月。

第二步，对科目前 N 位分别汇总金额（比如科目是 1234567890，新增科目号 1234、123456、12345678 对应的汇总金额；其中科目 1234 会把所有 1234 开头的科目的金额值进行累计汇总，依次类推）；再利用上一步生成的月号和科目合并成唯一主键 Key。

新主键 Key 的计算规则：月号计算出来是 2 位（假设数据记录跨度不超过 99 个月），科目为固定的 10 位，这样为了保证合并成主键后的唯一性，需要定义新主键的总长度为 12 位。公式：Key(12 位)= 月号 (月号为 2 位)*10000000000+ 总账科目 (最长为 10 位)。需要说明一下：这里设定 Key 的长度为 12 位，可以存放在一个 long 类型中，如果更长（与需求有关），就要用字符串了，虽然会相对慢一点，但也影响不大。完整的数据预处理的思路，如下图所示：

经过上面两步的预处理后，可以把需要计算的 100 个指标的科目号都整理好，然后执行一次遍历查询，就能把所有指标汇总结果都查找出来。具体思路如下：

1、根据查询参数年、月、初始年，构造月号；接着与科目号构造唯一 key

2、把查询指标的所有科目号合并，然后统一排序生成序号

3、通过序号在有序结果集中找到对应的金额

4、再利用位置序号把金额倒回到每个指标中，每个指标下对多个科目号的金额汇总

为了清楚地描述序号定位与查找的过程，这里以指标参数 A 和指标参数 B 为例来说明查询的流程，如下图所示：

实际效果

在客户提供的生产环境中进行实测。不改变原有报表工具时，原来需要 60 多秒才能呈现的资产负债表，现在提高到 1 秒左右。

在编程难度方面，SPL 做了大量封装，提供了丰富的函数，内置了上述优化方案需要的基本算法和存储方案。实际编写的代码很短，开发效率很高。

比如数据预处理的第一步：用年和月两列字段动态计算 "月号"，按照科目、月号分组，统计本科目下月号的累计金额。只有 6 行代码：

比如数据预处理的第二步：分别对科目前 N 位汇总金额；同时利用”月号”和科目合并成唯一主键 key，排序后进行存储。只有 9 行代码：

比如查询 100 多个指标的代码只有 27 行（其中大部分都是为了存放参数指标，真实计算的代码仅 6 行）：

对于报表的制作过程来说，并不需要做什么改变，只需要把数据源由原来的数据库切换到 SPL 即可。而且，SPL 脚本可以与报表模板一起管理，也能有效降低应用管理的复杂度。

后记

解决性能优化难题，最重要的是设计出高性能的计算方案，有效降低计算复杂度，最终把速度提上去。因此，一方面要充分理解计算和数据的特征，另一方面也要熟知常见的高性能算法，才能因地制宜地设计出合理的优化方案。本次工作中用到的基本高性能算法，都可以从下面这门课程中找到：识别下面二维码学习性能优化课程，有兴趣的同学可以参考。

性能优化课程

很遗憾的是，当前业界主流大数据体系仍以关系数据库为基础，无论是传统的 MPP 还是 HADOOP 体系以及新的一些技术，都在努力将编程接口向 SQL 靠拢。兼容 SQL 确实能让用户更容易上手，但受制于理论限制的 SQL 却无法实现大多数高性能算法，眼睁睁地看着硬件资源被浪费，还没有办法改进。SQL 不应是大数据计算的未来。

有了优化方案后，还要用好的程序语言来高效地实现这个算法。虽然常见的高级语言能够实现大多数优化算法，但代码过于冗长，开发效率过低，会严重影响程序的可维护性。SPL 是个很好的选择，它有足够的算法底层支持，代码能做到很简洁，还提供了友好的可视化调试机制，能有效提高开发效率，以及降低维护成本。

重磅！开源SPL交流群成立了

简单好用的SPL开源啦！

为了给感兴趣的小伙伴们提供一个相互交流的平台，

特地开通了交流群（群完全免费，不广告不卖课）

需要进群的朋友，可长按扫描下方二维码

本文感兴趣的朋友，请转到阅读原文去收藏 ^_^