"一、 SQL 及分析查询SQL语句如下： select * from ( select l_orderkey, sum(l_extendedprice * (1 - l_discount)) .."

sjr 北京
乾学院 32 号会员
1 回帖 • 877 浏览 • 2 年前

用 TPCH 练习性能优化 Q3

计算＆AI

一、 SQL及分析

查询SQL语句如下：

select * from (
    select
        l_orderkey,
        sum(l_extendedprice * (1 - l_discount)) as revenue,
        o_orderdate,
        o_shippriority
    from
        customer,
        orders,
        lineitem
    where
        c_mktsegment = 'BUILDING'
        and c_custkey = o_custkey
        and l_orderkey = o_orderkey
        and o_orderdate < date '1995-03-15'
        and l_shipdate > date '1995-03-15'
    group by
        l_orderkey,
        o_orderdate,
        o_shippriority
    order by
        revenue desc,
        o_orderdate
) where rownum<=10;

这是典型的主子表关联，关联后进行分组统计。

二、 SPL实现

主子表的主键经过排序后，可以采用有序归并的算法实现关联，比较次数要少很多，而且很容易并行。

另外，本例GROUP BY子句中有三个字段：L_ORDERKEY、O_ORDERDATE、O_SHIPPRIORITY，原因是结果集中要输出这三个字段。事实上，后两个字段可以被第一个字段决定，只要针对第一个字段进行分组，另两个字段可根据第一个字段计算出来，不需要一起参与对比。

	A
1	=now()
2	1995-3-15
3	>mktsegment="BUILDING"
4	=file("customer.ctx").open().cursor@m(C_CUSTKEY;C_MKTSEGMENT==mktsegment).fetch().keys@im(C_CUSTKEY)
5	=file("orders.ctx").open().cursor@m(O_ORDERKEY,O_ORDERDATE,O_SHIPPRIORITY;O_ORDERDATE<A2 && A4.find(O_CUSTKEY))
6	=file("lineitem.ctx").open().news@r(A5,O_ORDERKEY, sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue,O_ORDERDATE,O_SHIPPRIORITY;L_SHIPDATE>A2)
7	=A6.total(top(10;-revenue,O_ORDERDATE))
8	=interval@ms(A1,now())

A6也使用了游标建立时过滤的技巧。在A5中也有这种技巧，其中的A4.find表示只需要把外键不匹配的记录过滤掉，而不再转换成指针。

A6中对主子表做有序归并时，因为主表order已被过滤，而主子表的主键同序，这时可以用news方法读取子表时跳过主表中已经过滤掉的键值，从而减少子表的读取量。选项@r表示对子表记录按主表主键分组做聚合运算。

需要强调的是，lineitem必须按orders匹配的方法分段存储，即开始准备数据时lineitem创建组表用了@p，这样才能保证并行分段时不会错位关联。

三、进一步优化

1. 优化方法

本例中要使用Q2中介绍的维表预加载(加载customer表)、维表主键序号化(customer中的C_CUSTKEY和orders中的O_CUSTKEY),还要使用Q1中介绍的字符串整数化(customer中的C_MKTSEGMENT)、日期整数化(orders中的O_ORDERDATE)等优化方法。lineitem表中的L_SHIPDATE已在Q1中转换过了。

2. 数据转换代码

2.1 customer转换

	A
1	=file("customer.ctx").open().import()
2	=A1.id(C_MKTSEGMENT).sort()
3	=file("c_mktsegment.txt").export(A2)
4	=A1.run(C_CUSTKEY=#, C_MKTSEGMENT=A2.pos@b(C_MKTSEGMENT))
5	=file("customer_3.ctx").create(#C_CUSTKEY, C_NAME, C_ADDRESS, C_NATIONKEY, C_PHONE, C_ACCTBAL, C_MKTSEGMENT, C_COMMENT)
6	>A5.append(A4.cursor())

A2/A3 对C_MKTSEGMENT做字符串整数化

A4 设主键C_CUSTKEY为行号，设C_MKTSEGMENT为它在取值列表中的序号

2.2 orders转换

	A
1	=file("customer.ctx").open().import(C_CUSTKEY).keys@im(C_CUSTKEY)
2	=file("orders.ctx").open().cursor()
3	=A2.run(O_CUSTKEY=A1.pfind(O_CUSTKEY),O_ORDERDATE=days@o(O_ORDERDATE))
4	=file("orders_3.ctx").create(#O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_TOTALPRICE, O_ORDERDATE,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY,O_COMMENT)
5	>A4.append(A3)

A1 读取customer表中C_CUSTKEY列为主键并创建索引

A3 在A1中查找主键值为O_CUSTKEY的记录所在行号，将它给O_CUSTKEY。将O_ORDERDATE转换为小整数

2.3 lineitem转换

使用Q1中转换过的lineitem_1.ctx，重命名为lineitem_3.ctx。

3. 数据转换后的计算代码

维表和字符串字段取值列表需要预加载，加载代码如下：

	A
1	>env(customer, file("customer_3.ctx").open().import())
2	>env(c_mktsegment,file("c_mktsegment.txt").import@si())

在查询计算之前，需要先运行预加载代码，将小维表读入内存。

计算代码：

	A
1	=now()
2	=days@o(date("1995-03-15"))
3	>mktsegment=c_mktsegment.pos@b("BUILDING")
4	=customer.@m(C_MKTSEGMENT==mktsegment)
5	=file("orders_3.ctx").open().cursor@m(O_ORDERKEY,O_ORDERDATE,O_SHIPPRIORITY;O_ORDERDATE<A2 && A4(O_CUSTKEY))
6	=file("lineitem_3.ctx").open().news@r(A5,O_ORDERKEYsum(,L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue,O_ORDERDATE,O_SHIPPRIORITY;L_SHIPDATE>A2)
7	=A6.total(top(10;-revenue,O_ORDERDATE))
8	=A7.run(O_ORDERDATE=date@o(O_ORDERDATE))
9	return interval@ms(A1,now())

A4 主键序号化后使用对位序列，优化原理参看Q2。

A8 将整数化后的日期用date@o再还原成原始日期。

四、使用企业版列式计算

1. 原始数据

	A
1	=now()
2	1995-3-15
3	>mktsegment="BUILDING"
4	=file("customer.ctx").open().cursor@mv(C_CUSTKEY;C_MKTSEGMENT==mktsegment).fetch().keys@im(C_CUSTKEY)
5	=file("orders.ctx").open().cursor@mv(O_ORDERKEY,O_ORDERDATE,O_SHIPPRIORITY;A4.find(O_CUSTKEY) && O_ORDERDATE<A2)
6	=file("lineitem.ctx").open().news@r(A5,O_ORDERKEY,sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue,O_ORDERDATE,O_SHIPPRIORITY;L_SHIPDATE>A2)
7	=A6.total(top(10;-revenue,O_ORDERDATE))
8	=interval@ms(A1,now())

A4、A5中cursor函数都加选项@v，表示使用列式游标读数。A6中使用news函数与A5创建同步游标，它也与同步的A5一样是列式游标。

2. 优化后数据

维表和字符串字段取值列表需要预加载，加载代码如下：

	A
1	>env(customer, file("customer_3.ctx").open().import@v())
2	>env(c_mktsegment,file("c_mktsegment.txt").import@si())

加载维表数据时都加上选项@v，将维表读成列式序表。

在查询计算之前，需要先运行预加载代码，将小维表读入内存。

计算代码：

	A
1	=now()
2	=days@o(date("1995-03-15"))
3	>mktsegment=c_mktsegment.pos@b("BUILDING")
4	=customer.(C_MKTSEGMENT==mktsegment)
5	=file("orders_3.ctx").open().cursor@mv(O_ORDERKEY,O_ORDERDATE,O_SHIPPRIORITY;A4(O_CUSTKEY) && O_ORDERDATE<A2)
6	=file("lineitem_3.ctx").open().news@r(A5,O_ORDERKEY,sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue,O_ORDERDATE,O_SHIPPRIORITY;L_SHIPDATE>A2)
7	=A6.total(top(10;-revenue,O_ORDERDATE))
8	=A7.new(O_ORDERKEY,revenue,date@o(O_ORDERDATE):O_ORDERDATE,O_SHIPPRIORITY)
9	=interval@ms(A1,now())

A8中要将整数化的日期再转回日期，常规的做法是用run函数重设O_ORDERDATE的值，但列式中不能改变列的数据类型，所以这里要用new。

五、测试结果

单位：秒

	常规	列式
优化前	12.6	8.4
优化后	8.9	5.8

tpch(25) 性能优化(158)