集算器优化多数据集关联报表性能

发表: 2018-08-15 浏览: 1591

集算器多数据集性能优化

多数据集关联报表是很常见的报表形式，它允许开发者分别从不同的来源（表或数据库）分别准备数据形成不同的数据集，在报表端（模板）通过表达式描述数据集间的关系完成关联。这样可以避免在数据准备时写过于复杂的SQL/存储过程，降低维护难度。尤其当报表数据来源于多个数据库时，多数据集的优势更加明显。

凡事都有两面性，多数据集为开发带来方便的同时却对性能造成了极大的影响。在报表端进行多数据集关联时要计算关联表达式（举例：ds2.select(name,,id==A1)）时，报表引擎一般会采用顺序遍历的方式进行，先拿一个数据集的第一条记录去第二个数据集中遍历查找符合条件的记录，然后是第二条，第三条…。因此两个数据集关联的时间复杂度是O(n²)，数据量不大时感受还不明显，数据量稍大一些就会很慢，随着数据集数量的增多报表性能也会呈指数下降。

因此在实际报表业务中，当多数据集关联导致报表性能降低时可以考虑将多个数据集SQL合并成一句，利用数据库的关联计算能力提升性能。但这种方式又会导致SQL过于复杂，很难维护，而太复杂的SQL很可能被数据库搞错优化路径，结果性能仍不可控。并且合并SQL的方式有适用场景的限制（如无法完成跨异构库关联、文本关联等）。

下面介绍采用集算器的优化方法，写法简单且性能高，能够普遍适用于各种场景：

单数据库，多个数据集SQL比较复杂，很难写成一句
单数据库，多数据集中使用了存储过程，无法整合成一句SQL
单数据库，多数据集合并成一句SQL后性能仍不如人意
多数据库，多数据集来源多个数据库，无法通过一句SQL进行查询
涉及文件数据，多数据集中部分数据来自文件，无法使用SQL进行统一查询

不同于SQL（关系代数）采用笛卡尔积再过滤的方式看待JOIN，基于离散数据集模型的集算器将关联运算做了区分（只考虑等值JOIN）：多对一的主外键表采用外键属性化方式关联、一对一的同维表采用同维表等同化方式关联、一对多的主子表采用主子表一体化关联，针对不同的表间关系采用不同算法进行运算，可以获得更简单的写法和更高的性能以及更广泛的适用范围。

我们将通过一些示例来说明面向各种情况时，如何使用集算器获得最优的实现和效率。需要说明的是，为了描述方便我们使用抽象后最简单的情况说明各种关联运算，实际业务会复杂得多，每个数据集SQL也会复杂得多，但是不管怎样多数据集关联关系也逃不出多对一、一对一和一对多的情况，所以拿原子操作来说明问题，以期大家遇到问题时可以采用最合适的方式处理。

报表集成

这里假定读者已经了解集算器与报表的关系，集算器仅为报表提供数据准备，将原来的多数据集通过集算器完成关联计算，将计算以结果以单/多数据集的方式提供给报表进行呈现。

集算器脚本可以直接被润乾报表5.0及以上版本直接引用（集算器数据集）；如果是其他报表工具，集算器提供了标准JDBC和ODBC接口，可以采用类似调用存储过程的方式调用集算器脚本，详细可以参考教程《应用集成-被JAVA调用》章节，以及《集算器与BIRT集成》或《集算器与JasperReport集成》。

因此下面大部分例子将省略报表制作部分，主要说明集算器处理多数据集关联计算的过程。

外键表（多对一）

表A的某些字段与表B的主键关联。A表称为事实表，B表称为维表。A表中与B表主键关联的字段称为A指向B的外键，B也称为A的外键表。外键表是多对一的关系，且只有JOIN和LEFT JOIN，一般不会用到FULL JOIN。如：订单表和客户表

Orders表和Customer表的主键都是其中的id字段，Orders表的customerID字段是指向Customer表的外键。

这里说的主键是指逻辑上的主键（下同），也就是在表中取值唯一的字段（组），一个表上可能有多个字段（组）都取值唯一（并不常见），可以认为都是主键。不是一定是在物理表上建立的那个主键。

单外键举例

报表中有两个数据集，数据分别来自订单信息表（Orders）和客户表（Customer）（实际业务中可能是两条复杂SQL），订单表的客户ID指向客户表的主键客户ID，属于典型的主外键关系。

【计算目标】查询某时间段内订单和客户详单

集算器数据准备

单库情况

当两个数据集来源于单个数据库，数据集SQL比较复杂不易合并时，通过集算器实现多对一关联计算，脚本如下：

脚本解析：

1、前3行连接数据库后分别取订单和客户数据作为两个独立数据集（事实上A2和A3的SQL可以任意复杂，取数阶段无需将两条SQL合并，分别查询即可）；这里为了说明指针与记录，将两个表所有字段都选出，实际业务中应该用哪些字段取哪些。

2、A2中使用了脚本参数begin和end来接收起止时间范围

设置集算器脚本参数

3、注意A3的query函数使用了@x选项，代表查询后关闭连接，使用完数据库连接一定要及时关闭（也可以通过Aclose()显示关闭数据库连接）

4、A4中通过switch函数在A2订单表的客户ID字段上建立指向客户表记录的指针实现关联

关联后A2格结果集（客户ID指向客户表对应记录）

5、A5利用建立关联关系通过“外键字段.维表字段”的方式进行引用，如“客户ID:客户名称”，将维表记录看做外键的的属性，这便是外键属性化的由来；

6、A5为报表返回关联后结果集

关于switch函数

在SQL的概念体系中并不区分外键表和主子表，多对一和一对多从SQL的观点看来只是关联方向不同，本质上是一回事。比如，订单也可以理解成订单明细的外键表。但是，集算器把它们区分开，在简化语法和性能优化时使用不同的手段。

switch是集算器中实现多对一关联的函数，通过建立事实表和维表之间的外键指针实现连接。其原理是通过HASH算法在外键字段上建立指向维表记录的指针，这样在建立关联的时间与数据库中最快的关联方式HASH JOIN一样，但接下来使用连接结果时就不需要再查找HASH TABLE，直接通过指针定位到内存中的维表记录。

建立外键指针后外键字段的原值不再存储，而被转化为指向维表记录的指针，所有维表字段都可以通过“外键字段.维表字段”方式引用，因此switch函数只适合做单外键的关联（原外键字段值变了），多外键关联时需要使用A.join函数（后面会说明多外键情况）。

指针式连接的意义在于一次建立多次使用，重复使用时由于无需再建立连接性能高效得多。如上述例子中，除了获取订单和客户详单，还想针对客户所在区域汇总订单数量，那么可以写成这样（B5格）：

B5的计算继续使用了在A2客户ID字段上建立的指针，而无需重新建立关联。实际应用中，指针式关联建立后，重复使用次数越多性能优势越明显。

在报表中复用连接，计算不同的结果集多用于分片报表，分片报表在报表业务中并非很常见，但也不算罕见，不过对应业务都比较复杂，不大合适举例，这里就不细说了。当遇到报表分片且有相同关联情况时可以考虑使用集算器进行连接复用。

外键指针化关联示意图

多库情况

前面提到多库尤其是异构多库情况下无法利用SQL做关联计算，在报表中计算性能又低，这时非常适合使用集算器来做。下面假设订单和客户表分别来源两个不同数据库db1和db2，计算目标仍然是：查询某时间段内订单和客户详单，来看集算器的具体写法。

注意到和单库情况的区别了吗？

多库情况只需要在脚本中建立多库的连接（A1和B1）分别执行SQL查询（A2和A3），剩下的运算和单库完全一致，轻松实现基于多库的关联计算。

事实上，集算器（脚本）还非常利于应用移植和数据库扩展，当底层数据库发生变化或者由单库拆分成多库时，只需更改数据库连接，主要的计算逻辑完全不用改。更进一步，如果连接信息也维护在配置中，则可以写出更加通用的脚本做到系统扩展时脚本无缝移植。

涉及文本

集算器作为开放计算引擎提供了多数据源支持，除了关系数据库外，本地文件（Excel、TXT、CSV、JSON/XML）、NoSQL、Hadoop等也可以直接作为数据源参与运算。因此如果报表中有数据来源于文本、Excel等文件，可以通过集算器直接处理（SQL就无能为力了）。

沿用上面的例子，假设客户信息来源于TXT，计算目标仍然是：查询某时间段内订单和客户详单。来看集算器的写法。

涉及到文本有什么变化吗？只将A3改为读取文件数据即可，核心计算逻辑仍然没有变化。

上面我们通过多对一的两个表对单库、多库和文件三种情况进行说明，报表遇到相应问题可以使用集算器处理。实际业务中还可能涉及多层外键情况，即多表外键关联。

多层外键关联举例

报表中有三个数据集，数据分别来自订单信息表（Orders）、客户表（Customer）和地区表（Area），订单表的客户ID指向客户表的主键客户ID，客户表的所在区域指向区域表的主键区域ID。

【计算目标】查询某时间段内订单及其客户与所在区域详细信息

集算器数据准备

脚本解析：

1、A2-B4分别查询订单、客户和地区数据

2、A4中通过switch函数在A3所在区域上建立指向地区记录的指针实现关联

3、同理，B4在A2订单表的客户ID字段上建立指向客户表记录的指针实现关联，这里得到了一个三层结果的集合

4、A5通过外键属性化的方式引用区域和客户信息，可以看到无论有多少层外键都可以通过[点]（.）的方式作为外键属性引用

在实际业务中很常见的星型结构还会涉及到同一个事实表和多个维表进行关联，不同于传统的HASH分段JOIN方案，集算器无需两两消除、多次遍历，通过遍历一次事实表即可完成与多个维表的关联，非常高效，适合多数据库表关联性能低下需要改善的场景。

下面以一个事实表与两个维表关联说明多维表情况下集算器处理方式。

关联多个维表举例

订单信息表（Orders）与客户表（Customer）、雇员表（Employee），订单表的客户ID指向客户表的主键客户ID；销售ID指向雇员表的员工ID

【计算目标】按客户所在区域和销售人员汇总订单金额

集算器数据准备

集算器实现脚本：

在A5中通过switch将订单信息同时与客户表和雇员表关联，客户ID和雇员ID分别指向对应维表的记录

这里可以看到，通过遍历一次订单表就关联了客户和雇员，当外键关联较多时使用switch更加简单高效。相反，在写SQL关联多个表时，偶尔会出现漏写join条件导致数据库被跑死的情况，而集算器则完全避免了这种情况。

多外键情况举例

单外键下无论是数据来源数据库或是文件均可使用switch进行处理，实际业务中还可能存在多外键的情况。报表中有两个数据集分别来自学生表（Students）和班级表（Classes），学生表的专业号和班级号为外键字段，分别指向班级表的联合主键（专业号，班级号）。

【计算目标】查询所有学生的学号，姓名，专业，班级，班主任

集算器数据准备

脚本解析：

1、A3查询班级数据，并通过keys设置班级的主键为专业号和班级号；

2、A4使用A.join()函数进行双主键关联，将班主任信息添加到学生信息中，形成目标结果集

与switch处理单外键关联不同，当出现多外键的情况下需要使用A.join完成关联。

同维表（一对一）

表A的主键与表B的主键关联，A和B互称为同维表。同维表是一对一的关系，JOIN、LEFT JOIN和FULL JOIN的情况都会有，如：员工表和经理表。

两个表的主键都是id，经理也是员工，两表共用同样的员工编号，经理会比普通员多一些属性，另用一个经理表来保存。

单主键举例

报表中有三个数据集，分别来自回款表（OrderPayment）、客户表（Customer）和订单表（Orders），回款表的客户ID指向客户表主键客户ID，订单表的客户ID指向客户表主键客户ID。

【计算目标】按客户（所有）查看某时间段订单总额和回款总额

这是很常见的一类报表，按照某个维度（如地区、日期、人员）汇总多个指标（如订单额、回款额），但我们发现报表的三个数据集之间并不是像销售表和员工表（主键都是人员ID）那样互为同维表，不过结合计算目标分析一下，由于一个客户会有多笔订单和回款记录，因此需要对两个表分别按照客户ID分组后（结果集以客户ID为主键）向客户表主键客户ID对齐，显然三个集合是一组以客户ID为主键的同维表。

集算器数据准备

脚本解析：

1、A2和A3针对订单和回款数据分别按照客户ID进行分组汇总；

2、A5按照客户表左关联（@1选项代表左连接）订单和回款数据

3、A6获得关联结果返回报表数据集

这里关注一下join函数（上述例子A5=join@1(A4:客户,客户ID;A2:订单,客户ID;A3:回款,客户ID)），可以看到join的各个表之间看起来似乎是无关的，在集算器中关联时无需关注表间关系，只需要同时向某一个维度（如客户维度）对齐即可，这样在关联表增多或减少时修改非常方便。如果是SQL的写法必须指定两个表的关联条件，关联的表数量太多时就容易漏写一两个条件导致出现叉乘算错的情况，如果漏写条件的表比较大，还容易把数据库跑死；集算器的join则避免了这种情况。

另外，从上述例子来看当涉及多个事实表同时向维表对齐汇总时，一定要先group再join，如果先join再group就会算错，写成SQL应该是维表和有两个group by的子查询join。

多主键情况举例

与多外键情况类似，当同维表采用联合主键时就会存在多主键同维表关联的情况。报表中有两个数据集，分别来自回款表（OrderPayment）和订单表（Orders），两个表没有关联关系。

【计算目标】按客户和年份汇总回款金额和订单金额

这两个表直接并没有关联关系，但经过同样两个维度分组汇总后，就形成了两个以客户和日期为主键的同维表

按照计算目标，要同时获得回款金额和订单金额，需要将两个表进行关联计算。

集算器数据准备

脚本解析：

1、A2和A3分别查询订单和回款数据，并按客户和年份汇总订单额和回款额；

2、A4通过全连接对齐带有两个主键（客户ID,年份）的结果集

3、A5根据关联结果返回报表数据集

同维表与外键表混合

在实际业务中还经常能见到同维表和外键表混合使用的情况，集算器处理起来仍然简单高效。

举例

沿用上述单主键同维表的例子，现在还有一张地区表（Area），客户表外键字段所在区域指向区域表主键区域ID。

【计算目标】按客户所在区域和客户查看某时间段订单总额和回款总额

分析后仍然得到下面的同维表，只不过客户表外键字段所在区域又指向了地区表，出现了同维表和外键表混合的情况。

集算器数据准备

脚本解析：

1、A6在A4客户信息中建立外键关联

2、A7关联后结果可以看到集算器的结果集可以是任意多层结构

同维表和外键表混合情况下关联结果

3、A8通过外键属性化方式引用区域名称，为报表返回结果集

主子表（一对多）

表A的主键与表B的部分主键关联，A称为主表，B称为子表。主子表是一对多的关系，只有JOIN和LEFT JOIN，不会有FULL JOIN，如：订单和订单明细。

Orders表的主键是id，OrderDetail表中的主键是(id,no)，前者的主键是后者的一部分，订单表是主表，订单明细表子表。从子表去看主表，与前述提到的外键表非常类似，只是外键表不要求外键字段是主键，因此从子表角度观察表间关系可以将主子表看做外键表的特殊情况，所以有时也可以采用外键表处理关联的方法（switch）。

主子表关联计算在报表中并不常见，即使有，多数情况下可以转换成将主表作为外键表关联，或者子表group后变成同维表处理。当主表作为外键表处理时，除了可以用到外键表（多对一）switch的处理方式，还可以通过join实现。除了join可以关联多外键情况，当关联的两个结果集按照关联字段有序时还可以使用归并算法，性能比switch更高（数据量不大时优势并不明显）。

子表关联主表将主表作为外键表可参考前述外键表中<单外键举例>，子表group后变成同维表关联的例子可参考前述同维表中<单主键举例>。

下面介绍一种有序归并实施关联计算的方法，读者在对主子表（包括同维表）进行关联计算时也可选用，以获得更高性能。

主子表有序归并举例

报表有两个数据集，分别来自订单表（Orders）和订单明细表（OrderDetails）

订单表主键与订单明细表部分主键关联，订单表是主表，订单明细表是子表。现在两个表都按照订单ID有序。

【计算目标】查询某时间段内客户订单明细

集算器数据准备

脚本解析：

1、A2-A3分别查询订单和订单明细数据，结果集按订单ID有序

2、A4通过有序归并算法（@m选项）对两个集合按照订单ID关联

3、A5获得关联结果为报表返回结果集

有序归并可以极大提高关联效率，下面简单解释一下。

设两个关联表的规模（记录数）分别是N和M，则HASH分段技术的计算复杂度（关联字段的比较次数）大概是SUM(Ni*Mi)，其中Ni和Mi分别是HASH值为i的两表记录数，满足N=SUM(Ni)和M=SUM(Mi)，这大概率会比完全遍历时的复杂度N*M要小很多（运气较好的时候会小K倍，K是HASH值的取值范围）。

如果这两个表针对关联键都有序，那么我们就可以使用归并算法来处理关联，这时的复杂度是N+M；在N和M都较大的时候（一般都会远大于K），这个数会远小于SUM(Ni*Mi)，这就是有序归并的好处。