我们怎样把保险公司团保明细单查询提速 2000 倍

时间：2021-12-08 17:13:17

关键字：数据库 SQL 代码性能优化

手机看文章

扫描二维码
随时随地手机看文章

[导读]问题描述C保险公司业务系统中，团体保险明细查询速度很慢。查询时输入保单号，要返回团体保单包含的所有被保险人的信息。较小的保单，包含1万个被保险人，返回页面需要等待7.5分钟。较大的保单，包含100万被保险人，返回页面等待了4个小时没有出来。团体保险明细比较大，分两个数据库保存。每...

问题描述

C保险公司业务系统中，团体保险明细查询速度很慢。查询时输入保单号，要返回团体保单包含的所有被保险人的信息。较小的保单，包含1万个被保险人，返回页面需要等待7.5分钟。较大的保单，包含100万被保险人，返回页面等待了4个小时没有出来。团体保险明细比较大，分两个数据库保存。每个团体保单的数据，在两个库中都有可能出现。数据库是Oracle，SQL语句共163行，如下图：分析解决

面对性能问题，需要仔细分析数据和计算的特征，定位性能关键点，通过改变数据的存储方式和计算方法逐步优化。第一步，确认需求前提。团体保险明细查询是应用系统中的一个功能，需要查询最新数据。如果采用ETL定时将数据导出计算的方式，不能满足这个要求。因此，还是要想办法从数据库取数、库外计算，来优化性能。第二步，了解业务需求特征。团体保险明细数据存放在两个数据库db1、db2，每个数据库都有两个表m1、m2。这四个表在查询时要合并查询结果，我们统一称为团体保险明细表。四个团体保险明细表有所不同，但是都可以查询出主要字段：保单号、保险成员号、批改次数、业务编号1、业务编号2、业务标志，还有姓名、性别年龄等个人信息“批改”是针对保险合同的调整，系统将调整后的最新保险明细也保存在团体保险明细表中，不会修改原保险明细，保留轨迹。在数据中通过“批改次数”字段体现。查询时，要查询批改次数最大的一次，也就是最新的数据。明细数据中还有一部分是无效数据。要看业务编号1和业务标志连接成的字符串是否在无效集合中。无效集合是指：同一个保单号的数据中，批改次数小于9，并且业务标志为D或者U时，业务编号2和字母A连接成的字符串形成的集合。如果业务编号1和业务标志连接成的字符串出现在无效集合中，这条记录就是无效的记录，要舍弃掉。第三步，梳理研究计算过程。SQL虽然比较长，但是可以分成几个部分。第一部分是两个数据库的4个团体保险明细表，各自按照保单号查询需要的数据，再用union合并在一起。第二部分是条件过滤，包括去掉无效数据和另外几个简单的条件。第三部分是用窗口函数row_number() OVER(PARTITION BY 保险成员号 ORDER BY 批改次数 desc)，查找批改次数最大的明细记录。第一部分单独执行时，返回的结果数据量是几万到几百万，全部返回的时间比较长。如果用数据库JDBC游标的话，很快就能返回部分数据，比如几秒就可以返回几千条。第二部分，单独从数据库中取得无效集合只需要几秒，而且返回结果数据量不大，可以全内存。但是，第一部分和第二部分合并执行的时候，速度就变得很慢，即使是游标方式分批返回，也还是很慢。如果再加上第三部分，就更慢了。第四步，设计呈现方案。根据SQL分段执行的情况，确定采用流式大报表的方式实现提速，原理如下图：从数据库取数和呈现采用两个异步线程，取数线程发出 SQL 后不断取出数据经过复杂计算后，缓存到本地。再由呈现线程从本地缓存中获取数据进行显示。这样，已经取出并缓存的数据就能快速呈现，不再有等待感。第五步，设计计算过程优化方案。我们考虑将取数和计算分三段实现。第一段，上面说的第一部分SQL加上按照保险成员号和批改次数降序排序之后，用数据库JDBC游标依然能够快速分批取出部分数据。加上排序，可以在分批取出数据时，保证一个保险成员的数据相邻取出，在后续第三段中，就能够快速找到批改次数最大的最新数据。第二段，我们将这个保单的无效集合一次性取出到内存中，对第一段分批取出的数据进行过滤，计算出符合条件的有效明细。无效数据并不多，不会过滤掉太多的明细数据。第三段，根据被保险人号是否改变，判断是不是一个被保险人的第一条数据。因为明细数据按照被保险人和批改次数有序，所以当被保险人号改变的时候，第一条数据就是当前被保险人批改次数的最大值。这样就起到了，和上面说到的窗口函数一样的作用。由于每个保险成员的数据量都不大，一般是最多十几条数据（对应几次到十几次批改），而且无效数据并不多。所以第一部分分批取出的数据量不需要很多，就可以向前端批量返回数据了。这是流式大报表能够快速展现的必要条件。第六步，设计代码实现方案。使用延迟游标的方法实现上述三个分段。延迟游标的原理是，先依次定义三个分段的游标计算，定义的时候并不真的执行计算，而是在三个分段都定义好之后再执行。延迟计算的好处是可以一次遍历完成三个分段计算，不必生成中间结果占用空间，可以把查询结果分批提交给前端去展现。第三段游标计算比较复杂，需要用程序游标来实现。原理如下图：程序游标要做到被调用的时候，边计算边返回结果，这样才能达到流式大报表的要求。实际效果

根据计算特征拟定了优化方案后，需要选择合适的工具来实现计算和展现的性能优化。直接使用Java当然可以实现，但编码量过大，实现周期过长，容易出现代码错误隐患，也很难调试和维护。而开源的集算器SPL语言提供上述所有的算法支持，包括延迟游标、游标有序分段取出、程序游标等机制，能够让我们用较少的代码量快速实现这种个性化的计算。前端呈现需要支持流式大报表机制的报表工具，我们选择了润乾报表来实现。仅仅经过1天时间的编程、调试和测试，就完成了性能优化的验证，而且查询的响应速度非常快。较小的保单，包含1万个被保险人，原来返回页面需要等待7.5分钟，优化后的报表首页只需要3秒即可展现出来。较大的保单，包含100万被保险人，原来返回页面等待了4个小时没有出来，优化后的报表首页仅7秒即可展现出来，响应速度提高了2000倍还多。在编程难度方面，SPL做了大量封装，提供了丰富的函数，内置了上述优化方案需要的基本算法和存储机制。实际编写的代码很短，开发效率很高。上述取数的三段代码只有这么几行：后记

解决性能优化难题，最重要的是设计出高性能的计算方案，有效降低计算复杂度，最终把速度提上去。因此，一方面要充分理解计算和数据的特征，另一方面也要熟知常见的高性能算法，才能因地制宜地设计出合理的优化方案。本次工作中用到的基本高性能算法，都可以从下面这门课程中找到：点击这里学习性能优化课程（底部原文中可点击链接），有兴趣的同学可以参考。很遗憾的是，当前业界主流大数据体系仍以关系数据库为基础，无论是传统的MPP还是HADOOP体系以及新的一些技术，都在努力将编程接口向SQL靠拢。兼容SQL确实能让用户更容易上手，但受制于理论限制的SQL却无法实现大多数高性能算法，眼睁睁地看着硬件资源被浪费，还没有办法改进。SQL不应是大数据计算的未来。有了优化方案后，还要用好的程序语言来高效地实现这个算法。虽然常见的高级语言能够实现大多数优化算法，但代码过于冗长，开发效率过低，会严重影响程序的可维护性。开源SPL是个很好的选择，它有足够的算法底层支持，代码能做到很简洁，还提供了友好的可视化调试机制，能有效提高开发效率，以及降低维护成本。
对于本例中的报表呈现，还需要有能支持流式呈现的报表工具，这方面润乾报表有独特的优势，不需要全部取出数据就可以开始呈现，也不依赖于数据库分页机制（这种方法可能造成数据不一致）就可以支持高速前后翻页。这样才能获得业务用户的良好体验。正在为 SQL 性能优化头疼的同学们，可以和我们一起探讨：《慢得受不了的查询跑批》