用 TPCH 练习性能优化 Q1
一、 SQL及分析
查询SQL语句如下：
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;
这是个小结果集的常规分组汇总。
二、 SPL实现
小结果集的常规分组汇总直接用groups：
A |
|
1 |
=now() |
2 |
1995-12-01 |
3 |
=A2-90 |
4 |
=file("lineitem.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(dp=L_EXTENDEDPRICE*(1-L_DISCOUNT)):sum_disc_price, sum(dp*L_TAX):sum_charge, avg(L_QUANTITY):avg_qty, avg(L_EXTENDEDPRICE):avg_price, avg(L_DISCOUNT):avg_disc, count(1):count_order) |
6 |
=A5.run(sum_charge+=sum_disc_price) |
7 |
=interval@ms(A1,now()) |
A4中用@m表示生成多路游标并行计算，将过滤条件写在游标中能避免不满足条件的记录被生成。
A5中计算时，打折价格被两次使用，先计算出来赋给变量dp避免重复计算。
A6中分组完成后再做run把价税加起来，减少加法次数（分组后的记录数要远少于原表记录数）。
三、 进一步优化
1. 优化方法
1.1 日期整数化
日期型数据通常是long类型，其计算和比较都较为费时，将日期用days@o函数转换成小整数，可以提高性能。本例中的L_SHIPDATE字段是日期型数据，将它转换成一个小整数进行优化。
1.2 字符串整数化
字符串的运算相对于整数来说也更费时，如果数据表行数很多，某字符串字段的不同取值较少，可以把这些不同取值构成一个列表，把字段值替换成串值在列表中对应的序号，从而变成整数型字段以提高运算的效率。本例中的分组字段L_RETURNFLAG和L_LINESTATUS是字符串字段，它们的取值个数分别为3个和2个，可以转换成整数字段。
2. 数据转换代码
A |
|
1 |
=file("lineitem.ctx").open().cursor(L_RETURNFLAG, L_LINESTATUS) |
2 |
=A1.id(L_RETURNFLAG, L_LINESTATUS).(~.sort()) |
3 |
=file("l_returnflag.txt").export(A2(1)) |
4 |
=file("l_linestatus.txt").export(A2(2)) |
5 |
=file("lineitem.ctx").open().cursor().run(L_RETURNFLAG =A2(1).pos@b(L_RETURNFLAG), L_LINESTATUS =A2(2).pos@b(L_LINESTATUS), L_SHIPDATE=days@o(L_SHIPDATE)) |
6 |
=file("lineitem_1.ctx").create@p(#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查找相应字符串在列表中的序号，同时将L_SHIPDATE字段的值用days@o变换为小整数。
3. 数据转换后的计算代码
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("lineitem_1.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()) |
A1/A2 读出L_LINESTATUS、L_ RETURNFLAG字段对应的串值列表
A6 将过滤条件的日期也变换成小整数
A8 用L_ RETURNFLAG、L_LINESTATUS的值计算出一个新的分组键值gk，在groups函数中就把按2个字段分组变成按一个字段gk分组，可以加快分组的速度。分组统计值中不计算avg_qty、avg_price、avg_disc，而是在A9中通过相应的sum值和count值计算出来，可以减少分组统计中的计算量。
A9 用gk返算出L_ RETURNFLAG、L_LINESTATUS的值，并换成它们在列表中对应的串值
四、 使用企业版列式计算
1. 原始数据
A |
|
1 |
=now() |
2 |
1995-12-01 |
3 |
=A2-90 |
4 |
=file("lineitem.ctx").open().cursor@mv(L_QUANTITY, L_EXTENDEDPRICE,L_DISCOUNT,L_TAX,L_RETURNFLAG,L_LINESTATUS;L_SHIPDATE<=A3) |
5 |
=A4.derive@o(L_EXTENDEDPRICE*(1-L_DISCOUNT):dp) |
6 |
=A5.groups(L_RETURNFLAG, L_LINESTATUS; sum(L_QUANTITY):sum_qty, sum(L_EXTENDEDPRICE):sum_base_price,sum(dp):sum_disc_price, sum(dp*L_TAX):sum_charge, avg(L_QUANTITY):avg_qty, avg(L_EXTENDEDPRICE):avg_price, avg(L_DISCOUNT):avg_disc, count(1):count_order) |
7 |
=A6.new(L_RETURNFLAG,L_LINESTATUS,sum_qty,sum_base_price,sum_disc_price,sum_disc_price+sum_charge:sum_charge,avg_qty,avg_price,avg_disc,count_order) |
8 |
=interval@ms(A1,now()) |
A4中选项@v表示使用列式游标
列式游标中要避免在计算过程中产生新的变量，推荐使用derive方法将新变量变成新的列，如A5中所示，选项@o表示新列增加到原序表上，不产生新的。
A7中没有使用run函数，run适合用于行式序表，列式中尽量避免使用。
2. 优化后数据
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("lineitem_1.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@ms(A3,now()) |
A8中同时创建dp和gk两个新列
五、 测试结果
单位：秒
常规 |
列式 |
|
优化前 |
22.2 |
11.1 |
优化后 |
18.6 |
7.3 |
英文版