大主子表关联的性能优化方法

【摘要】

主子表是数据库最常见的关联关系之一，最典型的包括合同和合同条款、订单和订单明细、保险保单和保单明细、银行账户和账户流水、电商用户和订单、电信账户和计费清单或流量详单。当主子表的数据量较大时，关联计算的性能将急剧降低，在增加服务器负载的同时严重影响用户体验。作为面向过程的结构化数据计算语言，集算器 SPL 可通过有序归并的方法，显著提升大主子表关联计算的性能。 下面就来乾学院一探究竟：大主子表关联的性能优化方法。

一、        原理解释

所谓主子表关联计算，就是针对主表的每条记录，按关联字段找到子表中对应的一批记录。以订单（主表）和订单明细（子表）为例，两者以订单ID为关联字段。下图显示了关联计算过程中对主表中一条记录的处理情况，红色箭头代表没找到对应记录（不可关联），绿色箭头代表找到了对应记录（可关联）：

                                             

假设订单（主表）有m条记录，订单明细（子表）有n条记录，在不考虑优化算法时，主表中每一条记录的关联都需要遍历子表，相应的时间复杂度为O(n)。而主表一共有m条记录，所以整个计算的复杂度就是O(m*n)，显然过高。虽然数据库一般会采用hash方案来优化，但在数据量较大或较多表关联时，仍然会面临时难以并行、使用外存缓存数据的问题，性能依旧会急剧下降。

而对于集算器来说，针对大主子表关联算法，可以通过两步来实现显著优化：数据有序化、归并关联。

l   数据有序化

对主表和子表，首先分别按照关联字段排序，形成有序数据。

l   归并关联

首先在主表和子表上分别用指针指向第一条记录，然后开始比对，对于主表的第一条记录，如果子表遇到匹配的记录，则表示可以关联，记录后子表指针前移；如果遇到不匹配的记录，表示主表第一条记录的关联计算完成，此时子表指针不动，主表指针下移一位，指向第二条记录。以此类推……

优化后，单条记录的关联计算可用下图示意：

可以看到，经过优化，主表中单条记录的关联只需比对部分数据，不再需要遍历子表。事实上，对主表所有记录的关联，才会遍历一次子表，也就是复杂度为O(n)。再加上主表本身会遍历一次，因此整个计算的复杂度就是O(m+n)。

这样，经过集算器优化后，算法的时间复杂度变为线性，而且不再需要生成落地的中间数据，性能自然得到大幅提升。

当然，需要注意的是，有序化本身也会耗费时间，因此这种优化方法不适合只做一次的关联算法。但在实际业务中，关联算法通常会反复执行，这时有序化的开销就是一次性的，完全可以忽略不计。

二、        具体实现

下面还是以订单和订单明细为例，说明集算器优化大主子表关联的方法。

首先进行数据有序化（注意，这是一次性动作）。集算器脚本“数据有序化.dfx”如下：

	A	B
1	=connect("orcl")
2	=A1.cursor("select 订单ID,客户ID,订购日期 from 订单 order by 订单ID")	=A1.cursor("select 订单ID, 产品ID,单价,数量 from 订单明细 order by 订单ID,产品ID")
3	=file("订单.ctx").create(#订单ID,客户ID,订购日期)	=file("订单明细.ctx").create(#订单ID,#产品ID,单价,数量 )
4	=A3.append(A2)	=B3.append(B2)
5	=A1.close()

A1连接Oracle数据源，A5关闭数据源。集算器可连接大部分常用数据源，包括数据库、Excel、阿里云、SAP等等。

A2、B2：用SQL语句分别取订单和订单明细，并按关联字段排序。由于数据量较大，无法一次性读入内存，因此这里用到了游标函数cursor。

A3、B3：分别创建组表文件“订单.ctx”和“订单明细.ctx”，用于存储有序化之后的数据。这里需要指定字段名，其中带#号的字段是主键，。数据将按主键排序，且主键的值不可重复。

A4-B4：将游标追加写入组表文件。

 

 

其次，对于通常会反复执行的关联算法，可以用集算器脚本“归并关联.dfx”实现如下：

	A	B
1	=file("订单.ctx").create().cursor(订单ID)	=file("订单明细.ctx").create().cursor(订单ID,数量)
2	=joinx(A1:主表,订单ID; B1:子表,订单ID)
3	=A2.groups(;sum(子表.数量))

A1、B1：读入组表文件“订单.ctx”和“订单明细.ctx”。注意组表默认为列式存储，因此只需读入后续计算需要的字段，从而大幅降低I/O。

A2：对有序游标A1、B1进行归并关联，其中“主表”、“子表”是别名，方便后续引用，如果省略别名，后续可以通过默认别名_1、_2引用。注意，函数joinx默认进行内关联，可用选项@1指定左关联，或者@f指定全关联。如果有多个游标都要与A1关联，可用分号依次隔开。

A3：对关联结果进行后续计算，例如汇总产品数量。事实上后续计算可以支持任意算法，也不是本文的讨论范围了。

 

上面介绍了集算器SPL脚本的写法，而在实际执行时，还需要部署集算器的运行环境。有两种部署方式可供选择：内嵌部署和独立部署。

l   内嵌部署

内嵌部署时，集算器的用法类似内嵌数据库，应用系统使用集算器驱动（JDBC）执行同一个JVM下的集算器脚本。

下面是Java调用“