记一次To B开发普通的性能优化历程......报表优化

你的名字 2023-09-27 12:31 132阅读 0赞

![81ba09180ce71edc678e61b47d052ecc.png][]

### 前言： ###

To B开发的道路上，永远不缺报表的需求~

### 需求背景： ###

这次是接到一个财务报表的需求，其实就是一个在线版Excel+财务数据定制化，在这个报表里，不仅要满足Excel表格的所有功能，还要支持给每个单元格配置额外的取数规则，因为在线版Excel，市面上早已经有了成熟的解决方案，所以....花点钱能解决的就不算问题，于是这次的重点就来到了这个定制化的取数上...

### 后端需求 ###

这次的重点就是定制化的取数规则，比如某一个单元格，它的取数逻辑是where 维度1=A and 维度2=B and 维度3=C and... 如是下去，目前可以支持20个维度同时选择条件，当然如果，某个维度没有选择条件，则取该维度内所有“我”有权限的维值，比如维度4我没选，则 维度4 in (所有有权限的值)。

如果上面这个看起来还好，那再复杂一点点，就是现在一个单元格，不只是对应一套取数规则，而是对应多套上述这样的取数规则，每套取数规则都可以单独取出一个数据，然后两套取数规则取出的数据之间可以支持加减乘除的方式，计算出最终单元格的取数（目前只支持加减乘除）。

ok，需求确定了，接下来就是我这次的开发历程...

### 第一版：统一取数再回填 ###

简单来说，就是将所有单元格的取数规则聚合到一起，取出一批结果集，然后再按单元格的取数规则回填到每个单元格。

**举例：**

单元格A1-取数规则：

select sum(贷款余额) as m0
    where 维度1=A and 维度2=B and 维度3=C

单元格A2-取数规则：

select sum(贷款余额) as m0
    where 维度1=A' and 维度2=B' and 维度3=C'

则聚合后的查询sql：

select sum(贷款余额) as m0, 维度1，维度2，维度3
    where 维度1 in (A,A') and 维度2 in (B,B') and 维度3 in (C,C')
    group by 维度1，维度2，维度3

查出来一批结果集，然后再遍历每个单元格，根据单元格的取数规则，从结果集中匹配出对应的结果。

**优化：**

这里做了一点优化，就是每次聚合一列单元格的取数规则，去开启一个线程执行查询语句，多列会开启多线程并发的执行查询。

**问题：**

问题一：数据发散。

还是以上述聚合后的sql为例：

select sum(贷款余额) as m0, 维度1，维度2，维度3
    where 维度1 in (A,A') and 维度2 in (B,B') and 维度3 in (C,C')
    group by 维度1，维度2，维度3

这个sql查出来的结果集中其实会包含大量交叉的不需要的结果，比如会查出：

这个结果集中，只有第一条是我们需要的，而其他三条数据都是额外返回的。

问题二：查询结果有误！！

如果说上面这个问题还能接受，那查询结果不对，就是毁灭性的打击了。

是这样的，在维度的基础表中，一个维值A，会存在父子关系，它可能有一个父亲维值A'， 也可能有一个子维值a， 当然好在一点，这个维值A，只可能作为父亲维值，而有子维值，或者只可能作为子维值，而有父亲维值；

另外，作为父亲维值时，它有多个子维值，而作为子维值时，它也可能有多个父亲维值。

所以**加入了维度的父子的网状关系**，我们在做单元格取数时，再来看当一个单元格 where 维度1=A时，如果A是一个子维值，则继续取财务数据中维度1=A的那些数据，但是**当A是一个父维值时，就要在财务数据中找所有A的子维值的数据**，（财务数据中只会记录子维值的明细数据）

举例： 假设A是一个父亲维值，它有a,a'两个子维值，则该单元格的查询sql变为：

select sum(贷款余额) as m0
    where 维度1 in (a,a') and 维度2=B' and 维度3=C'

聚合后的sql为：

select sum(贷款余额) as m0, 维度1，维度2，维度3
    where 维度1 in (a,a',A') and 维度2 in (B,B') and 维度3 in (C,C')
    group by 维度1，维度2，维度3

因此我们查出的结果集就变为了：

**但问题是我们单元格配置的是A，这样在不知道A和a，a'的关系的情况下，我们根据A就拿不到a,a'的那两个结果！！**

当然我们可以join一下维度基础关系表，找出每个维度维值的父亲维值，但是这样如果有20个维度，无疑就得join20次，而且关键是最后的结果，我们group by 的时候还得再增加20次父亲维度。

![4ef5689c652b594a36e53985ede4bcf5.png][]

### 第二版：放弃聚合，一个取数规则一查 ###

针对上述问题，于是决定化繁为简，就爽快点儿，一个取数规则一查，一个报表，咱们搞个**多线程，每个取数规则去并发取数**。

这样虽然不会有回填数据的问题，每个单元格都一定能取到正确的数据，但是...

当我配置了一个10x10的报表时，**100个数据库连接请求打到数据库，数据库就hold不住了！**

> PS:我们这次还是通过第三方的数据平台，托管我们的sql，通过接口调用的方式去执行查询，所以，不光财务那边的数据库受不了，被调用的第三方数据平台也压力巨大。

因此，我们不得不pass掉这种方式，还是回归到第一版统一取数再回填的思路上，继续研究~

### 第三版：改造统一取数的sql ###

上面我们说过，统一取数有数据发散和当维度条件是父亲维值时数据不正确这两个问题，但是综合考虑后，数据发散的问题，暂时不那么致命，所以目前**主要是看有什么办法可以解决数据不正确的问题**；

首先，我们目前的现状是，传进来的一批维度维值，有可能是一个明细（子）维值，也有可能是一个汇总（父亲）维值，而当它是汇总维值的时候，我们需要根据它的明细维值去财务表里找到数据，然后再根据汇总维值group by。

所以，**汇总维值的匹配条件和group by的字段不是同一个值，而明细字段的匹配条件和group by的字段是同一个值**，那...

有没有可能，我去造这么一个通吃的字段...

于是我改出了这样一个中间表：

select code, code as p_code
    from 有权限的维度
    where code in (A, b)
    union 
    select code, p_code 
    from 有权限的维度
    join 维度网状关系表
    where p_code in (A, b)

（传进来的大写的A是一个汇总维值，小写的b是一个明细维值）

其实上面的思路就是在汇总维值和明细维值一起传进来的时候，我在不知道他们谁是汇总谁是明细的情况下，做了两种假设：

union前的内容：假设传进来的是明细维值，因此我们查出来的结果为：

union后的内容：假设传进来的是汇总维值，p\_code in (A,b)中，就只有A能查到结果，所以查出来是：

然后，我们再去查财务数据表的时候：

> join 上面这个中间结果 on 中间结果.code = 财务数据.维度1

于是我们得到的结果集就变成了这样：

> 然后我们group by 中间结果.p\_code，因此我们明细数据a,a'的数据都被汇总到了A的下面，而明细数据b被分到了b的下面，这样我们最终回填时，就能根据传进来的A，b分别去匹配到对应的结果。

至此，我们完美解决了汇总维度、明细维度统一取数，且正确回填的问题！

### 第四版：存储过程建临时表 ###

上面虽然已经能正确取数了，但是回到我们的性能问题，由于上面的查询过程中，我们不光增加了很多维度维值中间结果，而且由于权限控制，我们本身的维度数据，财务数据都需要严格做权限过滤，所以也诞生了很多的财务数据中间结果，尤其是当查询的数据期间拉长的时候，财务**数据量变激增，直接导致了数据库内存崩掉，查询报错**。（这次使用的是Yellowbrick数据库，没有索引，连接数有限等...）

> 因为之前没有用过这次的这个数据库，所以专门请教了一下之前有经验的同事，给出的建议是做存储过程，在存储过程中，将这些中间结果集创建为临时表。

比如其中一个临时表：

drop table if exists ads_ptc_auth_claim_tmp;
    execute 'create temp table ads_ptc_auth_claim_tmp as '||
            'select distinct a.segment_type, a.code as segment_code,a.ledger_id,b.management_caliber_codes '||
            'from ifin_ods.ods_mdm_mdm_flex_value a, '||
            'ifin_dwd.dwd_auth_claim_t b '||
            'where a.segment_type= b.segment_type '||
            'and b.um_no = upper('''|| um ||''') '||
            'and b.url like '''|| url ||'%'' '||
            'and a.ledger_id = b.ledger_code::int '||
            'and (case when b.segment_code = ''ALL'' then ''ALL'' else a.code end) = (case when b.segment_code = ''ALL'' then ''ALL'' else b.segment_code end) '||
            'and b.ledger_code in ('|| ledgers_id ||')';

因为业务逻辑比较复杂，都是通过拼接sql的方式去调execute命令执行sql。

然后复习一下存储过程中几个知识点：

**1. 如果存储过程返回结果集，则先要创建结果集表（主要是结构）**：

returns setof ifin_ads_temp.ads_ptc_accounting_balance（结果集表）

**2. 返回结果集这样写**：

FOR rec IN EXECUTE(ls_sql)
       loop
          raise info '%',rec;
       return next rec;
       end loop;

> 注意，返回的结果和结果集中的字段顺序和类型要一一对应，而跟字段名字没有半毛钱关系，这是百思不得其解半天的收获~

**3. 存储过程中打印日志**

raise info '%',rec;

### 第五版：content download耗时优化 ###

在上述改造完后，我们在页面上点下查询，同样的数据量下，查询速度由原来的50秒，缩短到5秒，这个时候我们已经成功了，但是...

我发现有些查询，后台实际的接口耗时只有2秒多，而接口中除了witing的2秒，剩下的全部花在content download上，**那这个content loaddown究竟是个什么东西呢？**

百度一下，由于接口返回的数据量很大，于是content download也就是前端**下载这批数据的耗时便需要很长时间**，于是我专门把返回的数据复制出来放到了一个TXT文档里，果然，大小超过了1M。

**缩减结果数据**

于是再百度有什么优化建议，多数建议都是对症下药，缩减这个返回结果，比如创建一个VO对象，对象中去掉那些为null的不需要的字段，那我就创建一个新的VO，去掉那些.....

最终接口调用耗时：2.56s，完成~

### 重构 ###

另外，因为这次的报表是从公司一个成熟的报表工具做二次开发而来，所以在兼容新旧报表的基础上，我自己对原来的报表做了重构。

首先是旧代码的方法提取和分离，也是运用《代码整洁之道》的思想，将代码中可变部分和不可变部分分离成不同的接口。

原先的代码几乎没有做公共方法的提取，比如一个Excel单元格遍历的代码，在查看报表，保存报表，编辑报表里都完全复制了几乎一模一样的代码，所以我先对这部分代码做了统一的封装；

然后考虑到新旧报表的变化点，就是处理每个单元格的取数上，因此该部分通过策略模式，使用一个策略上下文实例进行调用，不同的情境下，通过策略上下文，调用不同的服务。

### 结尾： ###

*  To B的性能优化和TO C最大的不同，就是用户访问量级不同，TO B用户量小，更多的优化功底在一个单线程的查询上，所以传统的sql优化，或者单线程改多线程成为了比较有效的方式，但是实际操作过程中，还有很多的细节往往是决定调优的关键！
 *  而且带入到具体调优的过程中，我们往往还可以根据业务特性进行针对性的调优，比如我虽然写完了博客，但是我刚刚拿到了业务的一份具体的报表，发现他们一张报表中，往往很多的维度维值都是一样的，虽然我们当然要支持每个单元格完全不同的取数规则，但是，是否可以针对大多数相同的情况，而再做一些针对性的优化呢？

[81ba09180ce71edc678e61b47d052ecc.png]: https://img-blog.csdnimg.cn/img_convert/81ba09180ce71edc678e61b47d052ecc.png
[4ef5689c652b594a36e53985ede4bcf5.png]: https://img-blog.csdnimg.cn/img_convert/4ef5689c652b594a36e53985ede4bcf5.png