开源SPL优化银行预计算固定查询成实时灵活查询

问题描述

W银行指标查询系统用来计算、展现各项汇总指标，是银行经营指标体系的重要支撑工具。由于明细数据量过大，指标查询系统一直采用预计算方式。但是，随着指标体系的不断发展，预计算方式无法满足指标数量急剧增长的需要，逐步成为制约经营指标体系的瓶颈。

预计算方式，是应对明细数据量巨大的无奈之举。W银行的客户有几千万，每天产生几千万条存款明细，几百万条贷款明细。这些明细数据保存在数据库中，业务人员提出指标计算需求，由软件工程师编写SQL语句完成计算。SQL很多很复杂，要计算的明细数据量又很大，如果白天提交给数据库计算，会造成数据库负担过重，甚至无法响应正常业务请求。因此，指标查询系统是把写好的SQL语句保存下来，每天晚上批量提交给数据库执行。执行的结果存入指标结果表，第二天上班的时候，业务人员就可以快速查询了。

随着指标越来越多，预计算方式已经无法满足需要。以贷款余额为例，按照五级分类维度，会衍生出五个指标：“一级贷款余额、二级贷款余额…”。再加上四类担保，又会衍生出二十个指标：“一级、A类担保余额、一级B类担保余额…三级C类担保余额…”。再加上十种客户、四种放款方式，会衍生出800个指标。再加上币种、分支机构、日期、客户年龄段、学历等等，衍生出来的指标数量特别巨大。这还只是贷款，还要考虑存款、客户等等。如果这些指标都要预汇总的话，算出来的指标结果，根本无法全部保存！

有没有什么办法能够直接从明细数据实时计算出需要的各种指标呢？

基于明细数据实时计算，面临的最大的难点是性能问题。指标计算涉及到大表关联、条件过滤、分组汇总、去重计数等多种混合运算。以“某日存款去重客户数”为例，客户表有两千万条记录，当日存款明细表有三千万条记录。两个表通过客户号关联之后，过滤选出符合两个固定条件，多个动态条件的记录，再按照客户号去重计数。固定条件是“多个分支机构和币种”，动态条件是存款类型、客户年龄段、性别、学历、产品等等。仅仅是这一个指标给数据库去算，就需要较长时间。

实际上，用户通过指标查询系统灵活定义的一个页面，就可能包含几十到几百个这样的指标，如下图：

以图中下方的“近12个月变化趋势”这个“指标块”为例，每个月月末一天（加上当日）都要计算“逾期90天贷款、逾期贷款、不良贷款和非应计贷款”四种指标，每种都要计算余额、笔数、去重用户数三个指标，合计就是13×4×3=156个指标。上面的四个“指标块”也有4×2×4=32个指标，整个页面共有188个指标需要计算。考虑到多个用户并发查询这个页面的情况，十个用户查询就会有近两千个指标需要同时计算出来。

几千个指标同时由数据库去算，意味着对几千万条记录的大表要关联、过滤、去重、汇总计算几千次。数据库会不堪重负、业务人员要长时间等待查询结果，这是银行方面无法接受的。

W银行的服务器是主流配置的虚拟机， 64核CPU、256G内存、1T硬盘。单节点数据库显然无法满足几千个指标的同时计算。那么数据库集群是否可行呢？假设有20个用户并发访问，就要同时计算4000个指标，而且要秒级响应。如果每个数据库节点分担100个指标计算的话，总共也需要40台服务器。这样规模的集群仅仅为这么一个指标查询业务服务，建设和维护成本是W银行无法接受的。

分析解决

面对性能问题，需要仔细分析数据和计算的特征，定位性能关键点，通过改变数据的存储方式和计算方法逐步优化。

性能优化一，经过估算和验证，一天的数据装入内存就需要30G以上空间，所以全内存的方案不可行。要采用存贷款明细、客户等大表外存，机构、员工等小维表内存的存储方案。

以存款为例，数据有几十列，计算一个指标时，只用到其中几列。由于查询不会涉及全部列，考虑采用列式有序存储，如下图：

采用列存可以避免整个文件的读取，只要读取需要的列即可。列存使得客户号字段的各个值相邻，有序又会有很多重复，有利于提高压缩比。高压缩比可以减少数据占用的磁盘大小，减少读取文件所用的时间。

性能优化二，客户和存款通过客户号关联，我们看作是主子关系，可以通过有序归并的方式提速，如下图：

主子表都按照客户号有序存储，按顺序归并，一次遍历即可实现连接，复杂度M+N。而关系数据库是采用哈希法连接，复杂度SUM(Ni*Mi)。相比之下，有序归并性能要好很多。

有序归并也可以放在每天晚上跑批时完成，将客户数据冗余到明细数据中，生成归并好的数据文件。查询时省去有序归并的时间，性能更好。

性能优化三，存款和客户有序归并的结果，还是对客户号有序的，采用有序去重的方式快速计数，如下图：

对客户去重计数，只要比较相邻的客户号即可，一次遍历就可以完成计算。

性能优化四，一个机构会包括n个分支机构（营业网点），统计一个机构的指标需要对明细数据的机构代码做IN条件判断。如果用一般的方法，逐条记录做IN计算，n较大时，性能较差。我们要预先将n个分支机构计算为布尔维序列，如下图：

查询时分段遍历存款文件。第1条记录，用dept字段的值3，找到布尔维序列中第3个成员，值为true，因此第1条客户记录满足条件。如果值为false，则不满足条件。其他记录以此类推。

布尔维序列将值比较转换为序号引用，有效的减少了计算时间。IN计算时间和IN枚举值的多少n无关，不会随着枚举值的增加而增加。

性能优化五，由于服务器有多核CPU，我们用多线程计算单指标，如下图：

用4个线程同时计算一个指标，速度会提高2-3倍。

性能优化六，币种、机构号等数据量不是很大，采用整形存储，比字符串方式存储量少，计算速度也更快。我们可以预先在内存中生成所有的小整数对象集，从0到65535，如下图：

币种、机构号等都小于65536，从硬盘上读入的时候，不再对象化，而是引用内存中预先生成的小整数对象，可减少对象化的时间。同时也减少内存的占用。

性能优化七，去重客户数指标需要有客户号、明细号字段，余额和笔数指标则不需要这两个字段。而客户号和明细号太多是计算量巨大的主要原因。

我们采用部分预汇总的方式，预先将客户和存款表关联，用客户号、明细号之外的其他字段分组汇总，生成新的数据文件，用来统计余额和笔数指标。新文件比原明细文件小很多，计算速度也快很多。而且，还可以使用本文第四步到第九步的优化方法。

需要注意的是，新数据文件要增加一个笔数字段，记录预汇总的笔数之和。后续的笔数统计就不能用计数，而要用求和了。

性能优化八，统计全行指标时，需要统计所有营业网点的明细数据，也就是说，所有的明细数据都是满足机构条件的。所以，不需要再计算机构号的IN判断条件。在代码中加一个判断，如果是全行指标，就去掉机构号的过滤条件，可以减少大量的计算，提高性能。

性能优化九，应用服务器和数据计算服务器之间是通过网络通信的。用户界面上有几十到几百个指标需要计算，都需要建立网络连接调用。多用户并发时，大量的网络请求、连接就会成为瓶颈。

所以，我们将应用服务器以指标为单位建立连接，调整为以“指标块”为单位建立连接，如下图：

可以看到，这个页面本来要建立188个连接。调整之后只需要建立5个连接了。应用服务器是多线程并行建立的5个连接，数据计算服务器在每个连接中又是多线程并行计算多个指标的，所以调整后整个页面的指标依然是并行计算的。

实际效果

根据计算特征拟定了优化方案后，需要选择一个合适的计算工具。通过上面的分析，关系数据库是不可行的，SQL过于粗线条，无法描述如此细致的优化算法。选择Java等常规高级语言没有问题，但需要编写大量代码，实现周期过长，容易出现代码错误隐患，也很难调试和维护。我们采用了开源的集算器SPL语言来实现，SPL提供有丰富底层算法支持，能让我们快速实现这种个性化的高性能计算。

经过几天时间的编程、调试和测试，直接从明细数据实时计算指标的效果非常好。单台服务器测试，以存款余额、笔数、去重客户数等为例，单指标计算时间100-200毫秒，包括前端页面的请求和统计图表展现。并发执行2000个指标只要3秒。如果需要支持更大的并发数，只需多机集群部署即可。集算器的集群能力很强，使用便捷，不用很复杂的配置就可以搭建起来，管理也很方便。

在编程难度方面，SPL做了大量封装，提供了丰富的函数，内置了上述优化方案需要的基本算法和存储方案。实际编写的代码很短，开发效率很高。比如单指标查询代码只有这么几行：

后记

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

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

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

正在为 SQL 性能优化头疼的同学们，可以和我们一起探讨：

《慢得受不了的查询跑批》

识别二维码打开该页面

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

简单好用的SPL开源啦！

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

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

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

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