性能优化案例课程 TPCH-Q1
select
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from
lineitem
where
l_shipdate <= date '1995-12-01' - interval '90' day(3)
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus;
计算不涉及表间关联，针对 LINEITEM 表做条件过滤、分组汇总和结果排序。
1. 集文件
LINEITEM 是大表，需要外存，先尝试存成行式的集文件。数据转换代码：
A |
|
1 |
=file("lineitem.tbl").cursor(;,"|").new(_1:L_ORDERKEY,_4:L_LINENUMBER,_2:L_PARTKEY,_3:L_SUPPKEY,_5:L_QUANTITY,_6:L_EXTENDEDPRICE,_7:L_DISCOUNT,_8:L_TAX,_9:L_RETURNFLAG,_10:L_LINESTATUS,_11:L_SHIPDATE,_12:L_COMMITDATE,_13:L_RECEIPTDATE,_14:L_SHIPINSTRUCT, _15:L_SHIPMODE, _16:L_COMMENT) |
2 |
=file("lineitem.btx").export@b(A1) |
字段 L_RETURNFLAG 和 L_LINESTATUS 都是长度为 1 的字符串，用来记录标志和状态，实际取值分别是 2 个和 3 个。
这是一个小结果集分组，用 groups 计算。计算代码：
A |
|
1 |
=now() |
2 |
1995-12-01 |
3 |
=A2-90 |
4 |
=file("lineitem.btx").cursor@b(L_SHIPDATE,L_QUANTITY, L_EXTENDEDPRICE,L_DISCOUNT,L_TAX,L_RETURNFLAG,L_LINESTATUS).select(L_SHIPDATE<=A3) |
5 |
=A4.groups(L_RETURNFLAG,L_LINESTATUS;sum(L_QUANTITY):sum_qty,sum(L_EXTENDEDPRICE):sum_base_price, sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):sum_disc_price, sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)):sum_charge,avg(L_QUANTITY):avg_qty,avg(L_EXTENDEDPRICE):avg_price,avg(L_DISCOUNT):avg_disc, count(1):count_order) |
6 |
=interval@s(A1,now()) |
测试结果：
测试项目 |
执行时间（秒） |
集文件 |
108 |
这里的测试目的是，对比不同手段的提速效果。具体的执行时间不重要，主要是观察应用不同的性能优化手段后，执行时间是否提升，以及提升的幅度。
2. 并行计算
并行计算用多线程同时遍历，提高性能：
A |
|
1 |
=now() |
2 |
1995-12-01 |
3 |
=A2-90 |
4 |
=file("lineitem.btx").cursor@mb(L_SHIPDATE,L_QUANTITY, L_EXTENDEDPRICE,L_DISCOUNT,L_TAX,L_RETURNFLAG,L_LINESTATUS).select(L_SHIPDATE<=A3) |
5 |
=A4.groups(L_RETURNFLAG, L_LINESTATUS; sum(L_QUANTITY):sum_qty, sum(L_EXTENDEDPRICE):sum_base_price, sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):sum_disc_price, sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)):sum_charge, avg(L_QUANTITY):avg_qty, avg(L_EXTENDEDPRICE):avg_price, avg(L_DISCOUNT):avg_disc, count(1):count_order) |
6 |
=interval@s(A1,now()) |
A4 中 cursor 增加了 m 选项，表示并行。
测试用的机器是 4 核虚拟机，集算器设置了并行线程数是 4。
测试结果：
测试项目 |
执行时间（秒） |
集文件 |
108 |
集文件并行 |
37 |
3. 列存组表
对于这种遍历计算，用组表列存方式，性能应好于集文件的行存方式。数据转换代码：
A |
|
1 |
=file("lineitem.btx").cursor@b() |
2 |
=file("lineitem3.ctx").create(L_ORDERKEY,L_LINENUMBER,L_PARTKEY, L_SUPPKEY, L_QUANTITY, L_EXTENDEDPRICE,L_DISCOUNT, L_TAX, L_RETURNFLAG, L_LINESTATUS, L_SHIPDATE, L_COMMITDATE, L_RECEIPTDATE,L_SHIPINSTRUCT, L_SHIPMODE, L_COMMENT) |
3 |
>A2.append(A1) |
组表文件命名为 lineitem3，表示是在第 3 节生成的，主要是为了区分后续生成的组表文件。
计算代码：
A |
|
1 |
=now() |
2 |
1995-12-01 |
3 |
=A2-90 |
4 |
=file("lineitem3.ctx").open().cursor@m(L_SHIPDATE,L_QUANTITY, L_EXTENDEDPRICE,L_DISCOUNT,L_TAX,L_RETURNFLAG,L_LINESTATUS;L_SHIPDATE<=A3) |
5 |
=A4.groups(L_RETURNFLAG, L_LINESTATUS; sum(L_QUANTITY):sum_qty, sum(L_EXTENDEDPRICE):sum_base_price, sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):sum_disc_price, sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)):sum_charge, avg(L_QUANTITY):avg_qty, avg(L_EXTENDEDPRICE):avg_price, avg(L_DISCOUNT):avg_disc, count(1):count_order) |
6 |
=interval@s(A1,now()) |
A4 把过滤条件写在组表游标中，可以避免不满足条件的记录被生成，减少硬盘读入内存的数据量。
测试结果：
测试项目 |
执行时间（秒） |
集文件 |
108 |
集文件并行 |
37 |
组表列存 |
15 |
4. 枚举型字符串字段数字化
字符串的运算相对于整数来说更费时，如果数据表行数很多，某字符串字段的不同取值较少，可以把这些不同取值构成一个列表，把字段值替换成串值在列表中对应的序号，从而变成整数型字段以提高运算的效率。
L_RETURNFLAG 和 L_LINESTATUS 是字符串字段，它们的取值个数分别为 3 个和 2 个，可以转换成整数字段。在 Q1 中，两个字段恰好是分组字段，实际上，不是分组字段的枚举型字符串字段也可以数字化。
数据转换代码：
A |
|
1 |
=file("lineitem3.ctx").open() |
2 |
=A1.cursor(L_RETURNFLAG, L_LINESTATUS).id(L_RETURNFLAG, L_LINESTATUS).(~.sort()) |
3 |
=file("l_returnflag.txt").export(A2(1)) |
4 |
=file("l_linestatus.txt").export(A2(2)) |
5 |
=A1.cursor().run(L_RETURNFLAG =A2(1).pos@b(L_RETURNFLAG), L_LINESTATUS =A2(2).pos@b(L_LINESTATUS)) |
6 |
=file("lineitem4.ctx").create(L_ORDERKEY,L_LINENUMBER,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,L_TAX,L_RETURNFLAG,L_LINESTATUS,L_SHIPDATE,L_COMMITDATE,L_RECEIPTDATE,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT) |
7 |
>A6.append(A5) |
A2 计算两个字符串字段的取值列表并排序。
A5 用 pos@b 查找相应字符串在列表中的序号。
计算代码：
A |
|
1 |
>l_linestatus=file("l_linestatus.txt").import@si() |
2 |
>l_returnflag=file("l_returnflag.txt").import@si() |
3 |
=now() |
4 |
1995-12-01 |
5 |
=A4-90 |
6 |
=file("lineitem4.ctx").open().cursor@m(L_QUANTITY, L_EXTENDEDPRICE,L_DISCOUNT,L_TAX,L_RETURNFLAG,L_LINESTATUS;L_SHIPDATE<=A5) |
7 |
=A6.groups(L_RETURNFLAG, L_LINESTATUS; sum(L_QUANTITY):sum_qty, sum(L_EXTENDEDPRICE):sum_base_price, sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):sum_disc_price, sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)):sum_charge, avg(L_QUANTITY):avg_qty, avg(L_EXTENDEDPRICE):avg_price, avg(L_DISCOUNT):avg_disc, count(1):count_order) |
8 |
=A7.run(L_RETURNFLAG=l_returnflag(L_RETURNFLAG),L_LINESTATUS=l_linestatus(L_LINESTATUS)) |
9 |
=interval@s(A3,now()) |
A8 把枚举型字符串字段重新转换回它们在列表中对应的字符串值。
测试结果：
测试项目 |
执行时间（秒） |
集文件 |
108 |
集文件并行 |
37 |
组表列存 |
15 |
枚举型字符串字段数字化 |
12 |
5. 分组字段合并
分组过程中，分组字段的计算与对比是重要的计算量。多一个分组字段，在计算和比对时都会复杂很多，导致性能下降。也就是说，分组字段个数越少性能会越好。
我们发现，分组字段 L_ RETURNFLAG、L_LINESTATUS 的取值范围很小，可以合并计算出一个新的分组字段 gk，在 groups 函数中就可以把按 2 个字段分组变成按一个字段 gk 分组，可以加快分组计算的速度。
计算代码：
A |
|
1 |
>l_linestatus=file("l_linestatus.txt").import@si() |
2 |
>l_returnflag=file("l_returnflag.txt").import@si() |
3 |
=now() |
4 |
=l_linestatus.len()+1 |
5 |
1995-12-01 |
6 |
=A5-90 |
7 |
=file("lineitem4.ctx").open().cursor@m(L_QUANTITY, L_EXTENDEDPRICE,L_DISCOUNT,L_TAX,L_RETURNFLAG,L_LINESTATUS;L_SHIPDATE<=A6) |
8 |
=A7.groups(L_RETURNFLAG*A4+L_LINESTATUS:gk ; sum(L_QUANTITY):sum_qty, sum(L_EXTENDEDPRICE):sum_base_price, sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):sum_disc_price,sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)):sum_charge, avg(L_QUANTITY):avg_qty, avg(L_EXTENDEDPRICE):avg_price, avg(L_DISCOUNT):avg_disc, count(1):count_order) |
9 |
=A8.run(L_RETURNFLAG =l_returnflag(gk\A4), L_LINESTATUS =l_linestatus(gk%A4)) |
10 |
=interval@s(A3,now()) |
A9 用 gk 反过来算出 L_ RETURNFLAG、L_LINESTATUS 的值，并换成它们在列表中对应的字符串值。
实际测试结果是这样的：
测试结果：
测试项目 |
执行时间（秒） |
集文件 |
108 |
集文件并行 |
37 |
组表列存 |
15 |
枚举型字符串字段数字化 |
12 |
分组字段合并 |
11 |
6. 日期字段数字化
日期型数据的计算和比较也较为费时，把日期用 days@o 函数转换成小整数以提高性能。
L_SHIPDATE 字段是日期型数据，将它转换成一个小整数进行优化。
A |
|
1 |
=file("lineitem4.ctx").open().cursor().run(L_SHIPDATE=days@o(L_SHIPDATE)) |
2 |
=file("lineitem6.ctx").create(L_ORDERKEY,L_LINENUMBER,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,L_TAX,L_RETURNFLAG,L_LINESTATUS,L_SHIPDATE,L_COMMITDATE,L_RECEIPTDATE,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT) |
3 |
>A2.append(A1) |
A1 把 L_SHIPDATE 字段的值用 days@o 变换为小整数。
计算代码：
A |
|
1 |
>l_linestatus=file("l_linestatus.txt").import@si() |
2 |
>l_returnflag=file("l_returnflag.txt").import@si() |
3 |
=now() |
4 |
=l_linestatus.len()+1 |
5 |
1995-12-01 |
6 |
=days@o(A5-90) |
7 |
=file("lineitem6.ctx").open().cursor@m(L_QUANTITY, L_EXTENDEDPRICE,L_DISCOUNT,L_TAX,L_RETURNFLAG,L_LINESTATUS;L_SHIPDATE<=A6) |
8 |
=A7.groups(L_RETURNFLAG*A4+L_LINESTATUS:gk ; sum(L_QUANTITY):sum_qty, sum(L_EXTENDEDPRICE):sum_base_price, sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):sum_disc_price,sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)):sum_charge, avg(L_QUANTITY):avg_qty, avg(L_EXTENDEDPRICE):avg_price, avg(L_DISCOUNT):avg_disc, count(1):count_order) |
9 |
=A8.run(L_RETURNFLAG =l_returnflag(gk\A4), L_LINESTATUS =l_linestatus(gk%A4)) |
10 |
=interval@s(A3,now()) |
A6 用 days@o 把日期过滤条件转换为数字。
测试结果：
测试项目 |
执行时间（秒） |
集文件 |
108 |
集文件并行 |
37 |
组表列存 |
15 |
枚举型字符串字段数字化 |
12 |
分组字段合并 |
11 |
日期数字化 |
11 |
日期数字化效果不明显，是因为这里只是日期比较，没有计算年月日这些动作。日期型和小整数的计算差距相对于其它计算来讲，耗时占比太小了，体现不出来效果。
7. 避免重复计算
打折价格 L_EXTENDEDPRICE*(1-L_DISCOUNT) 算了两次，可以先算出来赋给变量 dp，避免重复计算。
avg_qty、avg_price、avg_disc 这些平均值不要在 groups 中计算，而是在其后再计算，可以减少分组统计中的计算量。
修改后代码：
A |
|
1 |
>l_linestatus=file("l_linestatus.txt").import@si() |
2 |
>l_returnflag=file("l_returnflag.txt").import@si() |
3 |
=now() |
4 |
=l_linestatus.len()+1 |
5 |
1995-12-01 |
6 |
=days@o(A5-90) |
7 |
=file("lineitem6.ctx").open().cursor@m(L_QUANTITY, L_EXTENDEDPRICE,L_DISCOUNT,L_TAX,L_RETURNFLAG,L_LINESTATUS;L_SHIPDATE<=A6) |
8 |
=A7.groups(L_RETURNFLAG*A4+L_LINESTATUS:gk; sum(L_QUANTITY):sum_qty, sum(L_EXTENDEDPRICE):sum_base_price, sum(dp=L_EXTENDEDPRICE*(1-L_DISCOUNT)):sum_disc_price, sum(dp*L_TAX):sum_charge,sum(L_DISCOUNT):sum_disc, count(1):count_order) |
9 |
=A8.new(l_returnflag(gk\A4):L_RETURNFLAG,l_linestatus(gk%A4):L_LINESTATUS,sum_qty,sum_base_price,sum_disc_price,sum_charge+sum_disc_price:sum_charge,sum_qty/count_order:avg_qty,sum_base_price/count_order:avg_price,sum_disc/count_order:avg_disc,count_order) |
10 |
=interval@ms(A3,now()) |
A10 用 sum 和 count 的结果计算平均值，可以减少分组汇总计算量。
而且，分组完成后再把价税加起来 sum_charge+sum_disc_price:sum_charge，减少加法次数（分组后的记录数要远少于原表记录数）。
测试结果：
测试项目 |
执行时间（秒） |
集文件 |
108 |
集文件并行 |
37 |
组表列存 |
15 |
枚举型字符串字段数字化 |
12 |
分组字段合并 |
11 |
日期数字化 |
11 |
避免重复计算 |
10 |
8. 列式计算
列式计算可以进一步提高性能，但是有些注意事项，在后面的代码中将会说明。
列式计算不需要做特殊的数据转换，继续使用 lineitem5.ctx。
计算代码：
A |
|
1 |
>l_linestatus=file("l_linestatus.txt").import@si() |
2 |
>l_returnflag=file("l_returnflag.txt").import@si() |
3 |
=now() |
4 |
=l_linestatus.len()+1 |
5 |
1995-12-01 |
6 |
=days@o(A5-90) |
7 |
=file("lineitem5.ctx").open().cursor@mv(L_QUANTITY, L_EXTENDEDPRICE,L_DISCOUNT,L_TAX,L_RETURNFLAG,L_LINESTATUS;L_SHIPDATE<=A6) |
8 |
=A7.derive@o(L_EXTENDEDPRICE*(1-L_DISCOUNT):dp,L_RETURNFLAG*A4+L_LINESTATUS:gk) |
9 |
=A8.groups(gk;sum(L_QUANTITY):sum_qty, sum(L_EXTENDEDPRICE):sum_base_price,sum(dp):sum_disc_price,sum(dp*L_TAX):sum_charge, sum(L_DISCOUNT):sum_disc, count(1):count_order) |
10 |
=A9.new(l_returnflag(gk\A4):L_RETURNFLAG,l_linestatus(gk%A4):L_LINESTATUS,sum_qty,sum_base_price,sum_disc_price,sum_disc_price+sum_charge:sum_charge,sum_qty/count_order:avg_qty,sum_base_price/count_order:avg_price,sum_disc/count_order:avg_disc,count_order) |
11 |
=interval@s(A3,now()) |
A7 中选项 @v 表示使用列式游标。
列式游标要避免在计算过程中产生新的变量，要使用 derive 方法将新变量变成新的列，如 A8 中创建 dp 列，选项 @o 表示新列增加到原序表上，不产生新序表。
测试结果：
测试项目 |
执行时间（秒） |
集文件 |
108 |
集文件并行 |
37 |
组表列存 |
15 |
枚举型字符串字段数字化 |
12 |
分组字段合并 |
11 |
日期数字化 |
11 |
避免重复计算 |
10 |
列式计算 |
4 |
9. 日期有序存储
过滤条件是日期范围，只考虑这一题的话，按照日期有序最有利于提高过滤计算性能。
数据转换代码：
A |
|
1 |
=file("lineitem6.ctx").open().cursor(L_SHIPDATE,L_ORDERKEY,L_LINENUMBER,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,L_TAX,L_RETURNFLAG,L_LINESTATUS,L_COMMITDATE,L_RECEIPTDATE,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT).sortx(L_SHIPDATE;1000000) |
2 |
=file("lineitem9.ctx").create(#L_SHIPDATE,L_ORDERKEY,L_LINENUMBER,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,L_TAX,L_RETURNFLAG,L_LINESTATUS,L_COMMITDATE,L_RECEIPTDATE,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT) |
3 |
>A2.append(A1) |
A2 定义组表的时候，第一个字段 L_SHIPDATE 前加 #号，表示这是有序的维字段。
计算时继续使用上一章的代码，把其中的组表名字改为 lineitem9.ctx 就可以了，其他不用修改。
SPL 引擎会自动利用组表的有序字段提高性能。
测试结果：
测试项目 |
执行时间（秒） |
集文件 |
108 |
集文件并行 |
37 |
组表列存 |
15 |
枚举型字符串字段数字化 |
12 |
分组字段合并 |
11 |
日期数字化 |
11 |
避免重复计算 |
10 |
列式计算 |
4 |
日期有序存储 |
2 |
英文版