性能优化案例课程 TPCH-Q13

select
    c_count,
    count(*) as custdist
from (
    select
        c_custkey,
        count(o_orderkey) c_count
    from
        customer left outer join orders on
            c_custkey = o_custkey
            and o_comment not like '%special%accounts%'
    group by
        c_custkey
) c_orders
group by
    c_count
order by
    custdist desc,
c_count desc;

这个查询简单看是对 orders 做两轮常规分组,第一轮按 custkey 分组计算出每个顾客的下单数,第二轮再按下单数分组计算出每种下单数各有多少顾客。

1. 数据存储

Orders 和 customer 表没有什么特殊要求,按照主键有序存储。

继续使用题目 Q3 中的 orders.ctx、customer.ctx。

将这些表复制到本题的主目录中。

2. 一般实现

注意到原 SQL 中有个左连接(也就是左外连接),需要将没有下单过的顾客(下单数为 0)也统计在内,而基于 orders 针对 O_CUSTKEY 做分组汇总时会遗漏掉这些数据,需要事后再统计出来再补一条记录,即下单数为 0 的顾客数量。


A

1

=now()

2

>filter="*special*accounts*"

3

=file("orders.ctx").open().cursor@m(O_CUSTKEY;!like(O_COMMENT,filter))

4

=A3.groups@u(O_CUSTKEY;count(1):c_count)

5

=A4.len()

6

=A4.groups@um(c_count;count(1):custdist)

7

=file("customer.ctx").open().cursor@m().skip()

8

=A6.insert(0,0,A7-A5)

9

=A6.sort@z(custdist,c_count)

10

=interval@ms(A1,now())

代码中用到了前面讲到过的游标前过滤、多线程并行等优化方法。

A4 做第一轮分组,A6 做第二轮。A7 计算所有客户数,A8 中用 A7 减去已下单的客户数 A5,得到没下单的客户数,补充到 A6 上再一起排序。

A4 中用 groups@u 分组,@u 表示分组后不用按分组字段 O_CUSTKEY 排序。

测试结果:

测试项目

执行时间(秒)

一般实现

22

3. 序号分组

上节的 groups 函数在分组时,需要 hash 计算。如果分组字段是序号,那么可以用 @n 选项直接定位,避免 hash 计算。

这里使用前面介绍的维表主键序号化方法,customer 中的 C_CUSTKEY 和 orders 中的 O_CUSTKEY 都已在前面的例子中转换过了。

可以直接使用以前的题目 Q3 转换的 orders_5.ctx。customer 使用 Q5 中的 customer_3.ctx。

将这些表复制到本题的主目录中。

计算代码:


A

1

=now()

2

>filter="*special*accounts*"

3

=file("customer_3.ctx").open().cursor().skip()

4

=file("orders_5.ctx").open().cursor@m(O_CUSTKEY;!like(O_COMMENT,filter))

5

=A4.groups@n0(O_CUSTKEY;count(1):c_count;A3)

6

=A5.len()

7

=A5.groups@um(c_count;count(1):custdist)

8

=A7.insert(0,0,A3-A6)

9

=A7.sort@z(custdist,c_count)

10

=interval@ms(A1,now())

A5 中 groups@n0 表示直接按 O_CUSTKEY 的值定位到第几组,@0 表示去掉空组。

测试结果:

测试项目

执行时间(秒)

一般实现

22

序号分组

17

4. 序号化后用序列计算

上一节序号化分组的结果集是序表,分组计算的时候要向序表聚合,占用内存比较大。可以在序号化后直接使用序列来进行分组统计,减少内存占用,提高性能。

计算代码:


A

B

1

=now()


2

>filter="*special*accounts*"


3

=file("customer_3.ctx").open().cursor().skip()

4

=file("orders_5.ctx").open().cursor@m(O_CUSTKEY;!like(O_COMMENT,filter))

5

fork A4

=A3.(0)

6


=A5.run(B5(O_CUSTKEY)+=1).skip()

7


return B5

8

=transpose(A5).(~.sum()).groups@m(~:c_count;count(1):custdist)

9

=A8.sort@z(custdist,c_count)


10

=interval@ms(A1,now())


A3 求出 customer 表的记录数 (为便于下文描述,记为 n)。

A5 对 A4 游标进行多线程分段计算,线程数 m 取决于 A4 的 cursor@m,这里是 SPL 设置的多路游标缺省路数。

每个线程在 B5 中定义一个有 n 个成员的序列,赋初值为 0。在 B6 中循环当前线程对应的 orders 游标分段,让序列的第 O_CUSTKEY 个成员值加 1。多线程运行完后,会在 A5 得到 m 个长度为 n 的序列,分别表示各分段中 O_CUSTKEY 的下单数。

A8 对 A5 进行行列转置,结果是 n 个长度为 m 的序列,每个序列对应一个客户。将每行的 m 个成员加起来,就得到每个客户的下单总数。然后用 groups 计算第二轮分组,得到每种下单数对应的顾客数。

这个过程中都是对序列计算,相比序表节省内存,性能更高。

测试结果:

测试项目

执行时间(秒)

一般实现

22

序号分组

17

序号化后用序列

11

5. 列式计算


A

1

=now()

2

>filter="*special*accounts*"

3

=file("orders_5.ctx").open().cursor@mv(O_CUSTKEY;!like(O_COMMENT,filter))

4

=A3.groups@uz(O_CUSTKEY;count(1):c_count)

5

=A4.len()

6

=A4.groups@um(c_count;count(1):custdist).o()

7

=file("customer_3.ctx").open().cursor().skip()

8

=A6.insert(0,0,A7-A5)

9

=A6.sort@z(custdist,c_count)

10

=interval@ms(A1,now())

A4 中 groups 加选项 @z,表示多路并行分组时各路共享结果集。选项 @z 在分组结果集很大时 (一般百万组以上) 使用。

列式序表不能插入数据,A6 中的 o() 函数将列式序表转换成常规序表,以便在 A8 中调用 insert 方法。

列式计算还未提供 groups@n,不能使用序号分组方法。

测试结果:

测试项目

执行时间(秒)

一般实现

22

序号分组

17

序号化后用序列

11

列式计算

5