SPL组表进一步优化 JOIN 性能

【摘要】

介绍了如何使用集算器的组表进一步优化 JOIN 运算的方法。SPL 组表进一步优化 JOIN 性能

上一篇《优化 Join 运算的系列方法》介绍了如何使用集算器优化JOIN性能,其中数据存储使用的是集文件。如果想进一步提升性能,可以使用组表代替集文件。

1 组表特点

相对于集文件,组表有不少新特点:列存、分段、有序。充分利用好这些特点可以有效提升查询性能、节省存储空间。

 

2 组表使用

       建立组表的操作可以参考相关的教程文档,下面看几个组表访问的例子。

2.1 列式访问

组表通过游标来查询数据,下面以订单表和用户表为例,查询每一笔订单的用户名称、用户级别和下单时间。

订单表

用户表

订单编号

用户编号

用户编号

用户名

卖家编号

手机号

下单时间

注册时间

订单状态

VIP级别

 


A

1

=file("订单表.ctx").create()

2

=file("用户表.ctx").create()

3

=A1.cursor(订单编号, 用户编号,下单时间)

4

=A2.cursor(用户编号,用户名,VIP级别).fetch()

5

=A3.switch(用户编号,A4:用户编号)

6

=A5.new(订单编号, 用户编号.用户名:用户名, 用户编号.VIP级别:用户级别, 下单时间)

A1创建订单表组表对象;

A2,创建用户表组表对象;

A3,创建订单表游标,只取出订单编号、用户编号、下单时间字段;

A4,得到用户表数据,只取出用户编号、用户名、VIP级别字段;

A5,关联,在A3订单表的用户编号字段上建立指向用户信息表记录的指针;

A6,外键指针化之后,将外键表字段用户名、VIP级别作为属性用户名、用户级别使用。

这里的计算过程就是“外键指针化”,这与使用集文件时无异,但是取出的数据并非整个表的数据,而是按需取出。取出的数据少了,性能自然就提升了,这里就利用到了组表列式存储的特点。经过测试,使用组表执行这个计算所使用的时间,能够比使用集文件少1/4

2.2 条件过滤

组表游标还可以使用查询条件,例如在上面的例子中增加一个查询条件,把查询结果限定为2015年以来的订单记录:


A

1

=file("订单表.ctx").create()

2

=file("用户表.ctx").create()

3

=A1.cursor(订单编号, 用户编号,下单时间;下单时间>=date("2015-01-01"))

4

=A2.cursor(用户编号,用户名,VIP级别).fetch()

5

=A3.switch(用户编号,A4:用户编号)

6

=A5.new(订单编号, 用户编号.用户名:用户名, 用户编号.VIP级别:用户级别, 下单时间)

在上面代码的基础上,为A3加一个查询条件就实现了。组表里的数据是对维字段有序的,可以做到快速查询。不论是按照范围查询,还是等值查询,组表都做了最大程度优化。虽然集文件的游标也有条件查询的功能,但是在性能上比组表差很多。条件查询的结果越少,这个差距也就越明显,甚至可能会相差上百倍。

需要注意的是,这里可以优化的前提是条件字段必须是维字段。对于订单表,因为订单编号唯一,而且订单编号和下单时间都是递增的,所以可以把这两个字段设置为维字段。

 

2.3 有序归并

如果两个组表对于关联键都已经是有序的了,那么可以直接使用归并算法来处理关联。来看一个例子:

订单表

订单明细表

订单编号

订单编号

用户编号

商品编号

卖家编号

数量

下单时间

金额

订单状态


把两个组表按照订单编号字段进行关联,从而汇总每个卖家的销售额,当数据量不大的时候可以这样查询:


A

1

=file("订单表.ctx").create().cursor().fetch()

2

=file("订单明细表.ctx").create().cursor().fetch()

3

=join@m(A1:订单,订单编号;A2:明细,订单编号)

4

=A3.groups(订单.卖家编号 :卖家编号; sum(明细.金额):总销售额 )

当数据很大无法导入内存时,则可以使用游标方式实现:


A

1

=file("订单表.ctx").create().cursor()

2

=file("订单明细表.ctx").create().cursor()

3

=joinx(A1:订单,订单编号;A2:明细,订单编号)

4

=A3.groups(订单.卖家编号:卖家编号; sum(明细.金额):总销售额 )

当关联字段不是有序时,就需要事先进行排序,这个处理跟上一篇对集文件的数据准备是一样的。

对比测试的结果,使用组表执行这个计算所使用的时间,仅仅是使用集文件所用时间的1/3

2.4 并行计算

组表对数据分段存储的特点,能够使并行计算非常方便。来看一个例子:计算卖家销售总额:


A

1

=file("订单表.ctx").create().cursor@m(;;8)

2

=file("订单明细表.ctx").create().cursor(;;A1)

3

=joinx(A1:订单,订单编号;A2:明细 ,订单编号)

4

=A3.groups(订单.卖家编号 :卖家编号; sum(明细.金额):总销售额 )

A1得到订单表的多路游标,这里的路数是8,是根据实际情况选定的;

A2得到订单明细表的多路游标,这个游标是根据A1同步分段的;

A3使用joinx进行归并,得到游标;

A4对多路游标进行分组汇总。

两个组表进行同步分段很方便,而集文件的同步分段就要麻烦很多。

 

3 综合案例

上一篇使用过一个综合案例,外键表、同维表、主子表这几种关联类型一起出现,接下来看看如何使用组表实现这个计算。

3.1 表结构和查询目标

某电商平台中和订单编号这个字段相关的有6个表,主要表结构如下:

订单表

订单明细表

订单优惠表

订单发货表

订单支付表

订单评价表

订单编号

订单编号

订单编号

订单编号

订单编号

订单编号

用户编号

商品编号

优惠类型

快递编码

支付渠道

评分

卖家编号

数量

优惠金额


支付时间

评论时间

下单时间

金额



是否分期

评价

订单状态







1:N

1:N

1:1

1:1

1:1

他们都靠订单编号字段进行关联,下面是订单表和另外5个表的对应关系:

用户信息表

用户地址信息表

用户编号

用户编号

用户名

手机

注册时间

区县

VIP级别

地址


1:1

用户表和用户地址表,这两个表是按照用户编号字段11的关系,这是同维表情况。

商品信息表

类别信息表

商品编号

类别编号

名称

大类名称

类别编号

二级名称

厂商编号



1:1

商品信息表和类别信息表是通过类别编码进行关联,这是外键表的情况。

卖家信息表

卖家编号

开户行

联系

名称

信用级

最后还有一个卖家信息表。这里一共有11个表,假设要做这样一个查询:现在想知道江浙沪三省的VIP用户在2018年内从5星级卖家那里购买的所有电脑类商品的详情,并且要求只统计那些优惠总金额大于100元、用户评分4分以上的使用邮政配送的订单,而且这些订单不能是分期付款的。

3.2 分析关联类型

计算的步骤跟上一篇几乎一样,首先对这个查询进行拆分,得到以下几个子步骤:

P1=对用户表、用户地址表关联,得到江浙沪三省VIP用户的用户编号,这是同维表情况;

P2=卖家信息表取游标,条件是信用级别=5,得到卖家编码;

P3=对订单优惠表按照订单编号分组,按条件(优惠总金额>=100元)过滤;

P4=订单发货表取游标,条件是快递编码=1(邮政快递编码);

P5=订单支付表取游标,条件是是否分期=false

P6=订单评价表取游标,条件是评分>=4

P7=商品信息表和类别信息表用类别编码做外键关联,用条件(大类=电脑)过滤;

P8=订单明细表通过商品编号字段对P7做外键关联;

P9=订单表依次对P1P2做外键关联;

这时P3P4P5P6P8P9这几个子查询都是同维或者主子表的关系,对它们通过订单编码字段做有序归并,就得到了需要查询的结果。

3.3 查询实现

用户信息表很大,但查询目标是VIP级别的用户,符合VIP这个条件的用户并不多,进行过滤后就可以装入内存,所以P1子查询可以全部装入内存;同样,用户地址信息表作为用户信息表的同维表也很大,但属于江浙沪三省的用户并不多,经过过滤后可以全部导入内存。把这两个同维表关联后,然后再完成订单表的关联计算,来看看这个子查询的写法:


A

1

=file("用户信息表.ctx ").create().cursor(用户编号;VIP级别>=1)

2

=file("用户地址信息表.ctx").create().cursor(用户编号;=="江苏"||=="浙江"||=="上海")

3

=joinx(A1:用户信息,用户编号;A2,用户编号).new(用户信息.用户编号:用户编号).fetch()

4

=file("订单表.ctx").create().cursor(订单编号,用户编号,卖家编号;year(下单时间)==2018)

5

=A4.switch@i(用户编号,A3:用户编号)

6

=A5.new(订单编号.订单编号:订单编号, 订单编号.卖家编号:卖家编号,用户编号.用户编号:用户编号).fetch()

7

return A6

A1,得到用户信息表的游标,并按条件过滤;

A2,得到用户地址信息表的游标,并按条件过滤;

A3,对A1A2按照用户编号字段进行有序归并,返回的结果只取用户编号;

A4,得到订单表的游标,并按条件过滤;

A5,把A4A3做外键关联;

A6,返回结果只取订单编号、卖家编号和用户编号字段;

A7,返回执行结果;

把这个脚本保存为P9.dfx

 

接下来实现商品信息表和类别信息表的关联。类别信息表是商品信息表的外键,这个表很大无法装入内存。但是大类是电脑的类别信息就不多了,所以用大类等于电脑这个条件先过滤一下后就可以装入内存。下面是个子查询,把大类是电脑的所有商品的编码全部导入内存:


A

1

=file("类别信息表.ctx ").create().cursor(类别编号;大类名称=="电脑").fetch()

2

=file("商品信息表.ctx ").create().cursor(商品编号,类别编号)

3

=A2.switch@i(商品编号,A1:商品编号)

4

=A3.new(商品编号).fetch()

5

return A4

A1,得到类别信息表的数据,并按条件过滤后取出;

A2,得到商品信息表游标,并按条件过滤;

A3,把A2的商品编号字段替换为A1的对应记录;

A4,结果只取商品编号字段;

A5,返回执行结果;

这个脚本保存为P7.dfx

 

上面是两个子查询的处理,整个查询的实现是这样:


A

1

=file("订单优惠表.ctx").create().cursor(订单编号,优惠金额).group(订单编号;sum(优惠金额):优惠总额).select(优惠总额>=100)

2

=file("订单发货表.ctx").create().cursor(订单编号;快递编码==1)

3

=file("订单支付表.ctx").create().cursor(订单编号;是否分期==false)

4

=file("订单评价表.ctx").create().cursor(订单编号;评分>=4)

5

=file("卖家信息表.ctx").create().cursor().fetch()

6

=call("P9.dfx")

7

=A6.switch@i(卖家编号,A5:#).select(卖家编号.信用级==5).sort(订单编号).cursor()

8

=call("P7.dfx")

9

=file("订单明细表.ctx").create().cursor()

10

=A9.switch@i(商品编号,A8:商品编号)

11

=joinx(A7:订单,订单编号; A10:明细,订单编号; A1,订单编号;A2,订单编号;A3,订单编号;A4,订单编号)

A1,得到订单优惠表游标;

A2,得到订单发货表游标;

A3,得到订单支付表游标;

A4,得到订单评价表游标;

A5,得到卖家信息表数据(这里认为卖家信息表的数据可以导入内存);

A6,调用P9.dfx

A7,把A6的结果的卖家编号替换成卖家信息表里的对应记录,按条件(信用级=5)进行过滤,并排序;

A8,调用P7.dfx,得到大类是电脑的所有商品的编码;

A9,得到订单明细表游标;

A10,把订单明细表的商品编号替换成A8结果里的对应记录;

A11,对A7A10A1A2A3A4进行有序归并;

3.4 查询技巧

技巧一:使用组表游标时只取出用到的字段,用不到的一律不取出。这样的做的好处是可以充分利用组表的列式存储特点,减少从硬盘读取的数据量。

技巧二:对于维字段的查询条件,尽量使用组表游标来完成条件过滤。组表对维字段的过滤效率很高,这一点对性能的提升往往会起到关键作用。

 

总结

组表对性能的提升,很多时候都是无形的,即使是一个最简单的查询,使用组表也要比集文件要快很多,而且不仅提高了性能,使用起来也很方便。