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：导入班级数据，并且设置主键为majorId和classId；
A3：join() 函数进行双字段的主键关联，将班主任信息添加到学生信息中。
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() |
A1：2018年11月的订单明细仍然无法装入内存，使用游标访问。
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更快。主子表是1：N的关系，当N越大时，读取速度的提升就越大；当主表的主键是多字段时，读取速度提升的越明显。
当订单是1亿条，每条对应大约10条订单明细时，本案例实际测试结果：
| 耗时（秒） | ||
| 2 个组表 joinx | 组表合一 | 组表合一并行（4 线程） |
| 781 | 602 | 368 |
使用主子表合一后，不仅有速度上的优化，在空间上也更小了，合一后的组表比两个组表要节省20%左右的硬盘空间。
另外有需要云服务器可以了解下创新互联scvps.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
分享题目：SPL关联优化技巧-创新互联
URL地址：http://cdiso.cn/article/edoho.html
