我们怎样把 W 银行预计算固定条件查询优化成实时灵活条件查询


【摘要】
每个指标计算都涉及到千万数据量大表的关联、条件过滤、分组汇总、去重计数等多种混合运算,而 3 秒钟要计算 2000 个指标!究竟如何做到?点击了解我们怎样把 W 银行预计算固定条件查询优化成实时灵活条件查询

问题描述

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

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

..

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

..

 

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

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

..

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

..

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

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

W银行的服务器是主流配置的虚拟机, 64CPU256G内存、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倍。

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

 

..

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

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

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

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

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

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

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

..

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

实际效果

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

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

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

..

 

后记

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

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

 


以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾