SPL 关联优化技巧

1.          小维表

SPL进行关联,当维表不大时可以读入内存。

1.1           单字段主键

如果维表是单字段主键,可以使用switch做连接。例如有订单、客户、雇员三个表存储在集文件中,表结构如下:

Order

Customer

Employee

orderID

customerID

employeeID

customerID

name

name

employeeID

City

title

……

……

country



……




 

现在把订单表和客户表、雇员表进行关联:


A

1

=file("order.btx").cursor@b()

2

=file("customer.btx").import@b()

3

=file("employee.btx").import@b()

4

=A1.switch(customerID,A2:   customerID;employeeID,A3: employeeID)

5

=A4.new(orderID,   customerID.name, employeeID.name:name)

A1:订单表数据很多,所以用游标。

A2:客户表数据少,全部装入内存,并且建立索引。

A3:同理雇员表也做维表内存化。

A4:用switch做关联,根据客户ID字段关联订单表和客户表,以及根据employeeID字段关联订单表和雇员表。

A4可以看到,switch可以一次处理多个关联计算。

 

当维表的主键是序号时还可以用序号定位。


A

1

=file("order.btx").cursor@b()

2

=file("customer.btx").import@b().index()

3

=file("employee.btx").import@b()

4

=A1.switch(customerID,A2:   customerID; employeeID D,A3:#)

5

=A4.new(orderID,   customerID.name, employeeID.name:name)

A5:雇员表的employeeID字段是从1开始的自然数,所以可以做外键序号化。

 

如果维表的主键不是序号值,就无法直接使用外键序号化进行性能优化。比如customerID字段的值就是由字母构成的。这时,可以把维表的主键转换成序号后再使用外键序号化。

首先把客户表的客户ID转换为序号:


A

1

=file("customer.btx").import@b()

2

=A1.derive(#:newCustomerID)

3

=file("newAndOldCustomerID.btx").export@b(A2,   newCustomerID, customerID)

4

=file("newCustomer.btx").export@b(A2,   newCustomerID: customerID, name,city)

序号化后的客户保存到了集文件newCustomer.btx中。其中newAndOldCustomerID.btx里保存的是新旧客户ID的对应关系。

 

然后再把订单表的customerID进行序号化:


A

1

=file("newAndOldCustomerID.btx").import@b()

2

=file("order.btx").cursor@b()

3

=A2.switch(customerID,A1:   customerID)

4

=A3.run(customerID.   newCustomerID: customerID)

5

=file("newOrder.btx").export@ba(A4)

序号化后的订单保存到了集文件订单.btx中。

 

这时对于customerID字段,也可以通过序号化进行连接了。


A

1

=connect("demo")

2

=file("newOrder.btx").cursor@b()

3

=file("newCustomer.btx").import@b()

4

=file("employee.btx").import@b()

5

=A2.switch(customerID,A3:#;   employeeID,A4:#)

6

=A5.new(orderID,   customerID.name,employeeID.name:name)

 

1.2           多字段主键

当维表的主键是多个字段的时候,要使用join做连接。例如有学生表(Students)和班级表(Classes),学生表的专业号和班级号为外键字段,分别指向班级表的联合主键(专业号,班级号),表结构如下:

 

Student

Class

studentId

majorId

name

classId

majorId

teacher

classId


 

现在要查询学生的学号、姓名、专业、班级和班主任:


A

1

=file("student.btx").import@b()

2

=file("class.btx").import@b().keys(majorId,classId)

3

=A1.join(majorId:classId,A2,teacher)

A2:导入班级数据,并且设置主键为majorIdclassId

A3join() 函数进行双字段的主键关联,将班主任信息添加到学生信息中。

2.          大维表

如果维表无法装入内存,而事实表可以装入内存,则可以使用joinx函数进行关联。此时维表要按主键有序存储,可分段集文件或组表均可,后者效率更高。

例如有退货表、产品表两个表存储在集文件中,表结构如下:

Returns

Product

orderID

productID

productID

name

price

price

quantity

category

date

……

……


这里退货表对关联字段producID是无序的,产品表是按照producID字段有序的。计算目标是获得每一笔退货记录的产品类别,需要把退货表和产品表做关联:


A

1

=file("returns.btx").import@b()

2

=file("product.btx")

3

=A1.joinx@q(productID,A2:productID,categroy:   categroy;)

4

=A3.fetch()

A1:把退货表装入内存;

A2:给出产品表的文件对象;

A3:使用joinx函数进行关联。

 

如果事实表对关联字段也是有序的,就可以加上@c,进一步提速。例如计算每一笔退货记录的客户ID,就要把退货表和订单表做关联:


A

1

=file("returns.btx").import@b()

2

=file("order.btx")

3

=A1.joinx@qc(orderID,A2:orderID,customerID:   customerID;)

4

=A3.fetch()

 

实际上,上面两个例子可以一次完成:


A

1

=file("returns.btx").import@b()

2

=file("order.btx")

3

=file("product.btx")

4

=A1.joinx@qc(orderID,A2:orderID,customerID:customerID;   productID,A3:productID,category: category;)

5

=A4.fetch()

A4:做了两次关联,退货表表先跟订单表关联,得到的结果再跟产品表做关联。这里退货表对关联字段orderID是有序的,所以可以加@c,但要写在最前面。

 

对于事实表无法装入内存的情况,可以使用游标。例如有订单明细的表结构如下:

orderDetails

orderID

productID

price

quantity

date

……

订单明细保存在组表里,现在要计算某个月销售的产品的种类情况,需要把订单明细表的单月数据和产品表进行关联,:


A

1

=file("orderDetails.ctx").create().cursor(;year(date)==2018&&   month(date)==11)

2

=file("product.btx")

3

=A1.joinx@q(productID,A2:productID,categroy:   categroy;)

4

=A3.fetch()

A1201811月的订单明细仍然无法装入内存,使用游标访问。

A2:给出产品表的文件对象;

A3:使用joinx函数进行关联。

 

joinx支持事实表是游标的情况,但不宜太大,否则效率就会比较低了。

3.          同维主子表

对于按主键有序存储的主子表可以使用joinx实现有序归并连接。订单表、订单明细表已经是对订单ID字段有序的,计算每个客户的总消费额:


A

1

=file("order.btx").cursor@b()

2

=file("orderDetail.btx").cursor@b()

3

=joinx(A1:order,orderID;A2: detail,orderID)

4

=A3.groups(order.customerID:customerID;sum(detail.price*detail.quantity):amount   )

 

有序归并还可以和游标外键一起使用,例如计算消费总金额大于1000的客户名:


A

1

=file("order.btx").cursor@b()

2

=file("orderDetail.btx").cursor@b()

3

=file("customer.btx").import@b()

4

=A1.switch@i(customerID, A3: customerID)

5

=joinx(A4:order,orderID;A2:detail,orderID)

6

=A5.groups(order.customerID.name:customer;

sum(detail.price*detail.quantity):amount   ).select(amount>100000)

 

如果主子表不是按住键有序时则要事先排序才能使用这种方法。

4.          并行归并

对于同维的主子组表,可以并行进行关联。例如订单和订单明细这两个组表,分段字段都是订单ID。计算消费总金额大于1000的客户名,仍然使用joinx进行关联:


A

1

=file("order.ctx").create().cursor@m(;;4)

2

=file("orderDetail.ctx").create().cursor(;;A1)

3

=joinx(A1:order,orderID;A2:detail ,orderID)

4

=A3.groups(order.customerID:customer;   sum(detail.price*detail.quantity):amount).select(amount>100000)

A1:得到订单的多路游标,这里使用的游标路数是4

A2:根据多路游标A1的值,得到订单明细的多路游标,游标路数也是4

实际测试的结果表明,使用4线程并行后速度快了大约2.5倍。

 

需要注意的是,分段字段要在产生组表时就指定:


A

1

=file("order.ctx").create(#orderID,customerID,employeeID;orderID)

2

=file("orderDetail.ctx").create(#orderID,productID,price,quantity,date;orderID)

这样追加的数据会按orderID字段分段,不会把orderID同值的记录分到两段中。

5.          主子表合一

组表支持主子表保存在同一文件中,合一后取出关联数据的性能会更高。例如可以把订单作为主表保存到组表文件order.ctx,再在主表上创建一个附表,命名为detail,把订单明细保存到这个附表上,这时再计算消费总金额大于1000的客户名,就是这样:


A

1

=file("order.ctx").create().attach(detail)

2

=A1.cursor(orderID,customerID,price,quantity)

3

=A2.groups(customerID:customer; sum(price*quantity):amount).select(amount>100000)

A1:打开附表订单明细;

A2:建立附表游标;

A3:进行分组,并计算消费总额。

 

这种方式也支持并行,只要把A2稍微修改就可以:


A

1

=file("order.ctx").create().attach(detail)

2

=A1.cursor@m(orderID,customerID,price,quantity;;4)

3

=A2.groups(customerID:customer; sum(price*quantity):amount).select(amount>100000)

A2:建立附表的多路游标,路数为4

 

根据实际测试的结果,使用附表比使用joinx更快。主子表是1N的关系,当N越大时,读取速度的提升就越大;当主表的主键是多字段时,读取速度提升的越明显。

当订单是1亿条,每条对应大约10条订单明细时,本案例实际测试结果:

耗时(秒)

 2 个组表 joinx

组表合一

组表合一并行(4 线程)

781

602

368

 

使用主子表合一后,不仅有速度上的优化,在空间上也更小了,合一后的组表比两个组表要节省20%左右的硬盘空间。


以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾