利用有序高效实施交并差集合运算

可阅读原文：http://c.raqsoft.com.cn/article/1545662863743?r=alice

 交并差是常见的集合运算，SQL 中对应的 intersect/union/minus 计算也很简单。不过当数据量较大时，这类集合运算性能往往偏低，尤其当参与计算的数据量超过内存容量时，性能表现会十分糟糕。

       本文专门针对这种情况下的高性能计算（HPC）需求，讨论如何使用集算器 SPL 语言通过有序计算思路显著提高大数据量下交并差三类集合运算的性能。下面讨论中使用了一个实际用户在数据库选型时的评测用例：数据基于数据库的 2 个表，共计 105 亿行数据，执行相关运算后，以输出第一批 500 条记录所用时间来衡量哪个数据库性能更优。

测试环境

类别	配置
机型	X86
CPU	E5-2680 v4 @ 2.40GHz
内存	512GB
数据硬盘存储	SAS 3TB
集群数量	4 台
网络环境	万兆
MPP 数据库资源配置（单节点）	硬盘：SSD 1.9T 内存：20GB(JVM) +   12GB(分片库)
集算器资源配置（单节点）	硬盘：SAS 1T 内存：120GB
操作系统	CentOS6.8（64 位）

 

数据描述

数据情况

表名称	数据量	数据结构	数据内容
A	103.68亿	52个字段，a1为timestamp。其他字段为字符型，长度=10	a1为1天的时间均匀分布数据，时间跨度为5天（每秒24000条），其他字段数据随机生成。
B	1728.048万	2个字符字段,长度=10	2个字段生成规律依照A表的a3和a7字段

 

索引

数据表	数据库索引
A表	a1、a3、a7字段建立btree索引，a4建立基于btree的varchar_pattern_ops索引。
B表	b1、b2字段建立btree索引

 

样例数据

a1-a52 列值：

2018-01-07 00:00:00,8888888888,MQJqxnXMLM,ccTTCC7755,aaa******8,ppppaaaavv,gggggttttt,MQJqxnXMLM,ccTTCC7755,aaa******8,ppppaaaavv,gggggttttt,MQJqxnXMLM,ccTTCC7755,aaa******8,ppppaaaavv,gggggttttt,MQJqxnXMLM,ccTTCC7755,aaa******8,ppppaaaavv,gggggttttt,MQJqxnXMLM,ccTTCC7755,aaa******8,ppppaaaavv,gggggttttt,MQJqxnXMLM,ccTTCC7755,aaa******8,ppppaaaavv,gggggttttt,MQJqxnXMLM,ccTTCC7755,aaa******8,ppppaaaavv,gggggttttt,MQJqxnXMLM,ccTTCC7755,aaa******8,ppppaaaavv,gggggttttt,MQJqxnXMLM,ccTTCC7755,aaa******8,ppppaaaavv,gggggttttt,MQJqxnXMLM,ccTTCC7755,aaa******8,ppppaaaavv,gggggttttt

2018-01-07 00:00:00,4444444444,dv@bi-lyMF,qqoovv22ww,)))777FFF4,jjjjIIIIVV,aaaaaRRRRR,dv@bi-lyMF,qqoovv22ww,)))777FFF4,jjjjIIIIVV,aaaaaRRRRR,dv@bi-lyMF,qqoovv22ww,)))777FFF4,jjjjIIIIVV,aaaaaRRRRR,dv@bi-lyMF,qqoovv22ww,)))777FFF4,jjjjIIIIVV,aaaaaRRRRR,dv@bi-lyMF,qqoovv22ww,)))777FFF4,jjjjIIIIVV,aaaaaRRRRR,dv@bi-lyMF,qqoovv22ww,)))777FFF4,jjjjIIIIVV,aaaaaRRRRR,dv@bi-lyMF,qqoovv22ww,)))777FFF4,jjjjIIIIVV,aaaaaRRRRR,dv@bi-lyMF,qqoovv22ww,)))777FFF4,jjjjIIIIVV,aaaaaRRRRR,dv@bi-lyMF,qqoovv22ww,)))777FFF4,jjjjIIIIVV,aaaaaRRRRR,dv@bi-lyMF,qqoovv22ww,)))777FFF4,jjjjIIIIVV,aaaaaRRRRR

2018-01-07 00:00:00,9999999999,bxk3J/2YDd,ppvv**--88,uuuNNNBBBA,BBBBhhhhjj,_____PPPPP,bxk3J/2YDd,ppvv**--88,uuuNNNBBBA,BBBBhhhhjj,_____PPPPP,bxk3J/2YDd,ppvv**--88,uuuNNNBBBA,BBBBhhhhjj,_____PPPPP,bxk3J/2YDd,ppvv**--88,uuuNNNBBBA,BBBBhhhhjj,_____PPPPP,bxk3J/2YDd,ppvv**--88,uuuNNNBBBA,BBBBhhhhjj,_____PPPPP,bxk3J/2YDd,ppvv**--88,uuuNNNBBBA,BBBBhhhhjj,_____PPPPP,bxk3J/2YDd,ppvv**--88,uuuNNNBBBA,BBBBhhhhjj,_____PPPPP,bxk3J/2YDd,ppvv**--88,uuuNNNBBBA,BBBBhhhhjj,_____PPPPP,bxk3J/2YDd,ppvv**--88,uuuNNNBBBA,BBBBhhhhjj,_____PPPPP,bxk3J/2YDd,ppvv**--88,uuuNNNBBBA,BBBBhhhhjj,_____PPPPP

测试用例

l  交集（intersect）

select * from A, B where a1>'2018-01-07 02:14:43' and a1 < '2018-01-07 04:14:43' and a3=b1 or a7 = b2

intersect

select * from A, B where a1>'2018-01-07 12:14:43' and a1 < '2018-01-07 14:14:43' and a3=b1 or a7=b2

 

l  并集（union）

select * from A, B where a1>'2018-01-07 02:14:43' and a1 < '2018-01-07 04:14:43' and a3=b1 or a7 = b2

union

select * from A, B where a1>'2018-01-07 12:14:43' and a1 < '2018-01-07 14:14:43' and a3=b1 or a7=b2

 

l  差集（minus）

select * from A, B where a1>'2018-01-07 02:14:43' and a1 < '2018-01-07 04:14:43' and a3=b1 or a7 = b2

minus

select * from A, B where a1>'2018-01-07 12:14:43' and a1 < '2018-01-07 14:14:43' and a3=b1 or a7=b2

 

用例分析

       分析上述 SQL 可以发现，此计算场景为大数据量的多对多集合运算。查询条件的前半段（a1>'2018-01-07 02:14:43' and a1 < '2018-01-07 04:14:43' and a3=b1）是 A 表 2 个小时内的数据与 B 表进行多对多关联；而后半段（or a7 = b2）则是 A 表全量数据和 B 表进行多对多关联。因此，这个用例主要考察的是大表 A 和小表 B 多对多关联后的集合运算性能。

      

       实测时，该 SQL 使用 MPP 数据库得不到查询结果（运行时间超过 1 小时），因为数据量很大，内存无法容纳全部数据，从而造成数据库在运算时频繁进行磁盘交互，导致整体性能极低。

 

       按照我们一贯的思路，要实施高性能计算必须设计符合数据特征和计算特征的算法，而不是简单地使用通用的算法。这里，为了避免过多的磁盘交互（这也是大数据规模计算的首要考虑目标），最好只遍历一次 A 表就能完成计算。观察数据可以发现，A 表包含时间字段（a1），而且在时间字段（a1）和关联字段（a3、a7）上均建有索引，同样 B 表的两个字段（b1、b2）也建有索引，这样，我们就可以设计出这样的算法：

1)       根据 A 表数据生成的特点，逐秒读取 A 表数据（每秒 24000 条）；

2)       针对每秒的数据循环处理，根据过滤条件逐条与 B 表关联，返回关联后结果；

3)       对两部分数据，即用于交并差的两个集合进行集合运算。

通过以上三步就可以完成全部计算，而整个过程中对 A 表只遍历了 2 次（分别得到用于交并差的两个集合）。当然，整个过程中由于数据量太大，集算器将通过延迟游标的方式进行归并，游标归并时数据需要事先排序，所以在 1)和 2) 步之间还需要对每秒的 24000 条数据按照关联字段和其他字段排序，会产生一些额外的开销。下面是具体的集算器 SPL 脚本。

实现思路：http://c.raqsoft.com.cn/article/1545662863743?r=alice