查看原文
其他

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

乾学院 2022-11-07

问题描述

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开源啦!

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

特地开通了交流群(群完全免费,不广告不卖课)

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

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

您可能也对以下帖子感兴趣

文章有问题?点此查看未经处理的缓存