"一、 SQL 及分析查询SQL语句如下： select * from ( select s_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address, .."

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

用 TPCH 练习性能优化 Q2

计算＆AI

一、 SQL及分析

查询SQL语句如下：

select * from (
    select
        s_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_comment
    from part,supplier,partsupp,nation,region
    where
        p_partkey = ps_partkey
        and s_suppkey = ps_suppkey
        and p_size = 25
        and p_type like '%COPPER'
        and s_nationkey = n_nationkey
        and n_regionkey = r_regionkey
        and r_name = 'ASIA'
        and ps_supplycost = (
            select
                min(ps_supplycost)
            from
                partsupp,
                supplier,
                nation,
                region
            where
                p_partkey = ps_partkey
                and s_suppkey = ps_suppkey
                and s_nationkey = n_nationkey
                and n_regionkey = r_regionkey
                and r_name = 'ASIA'
        )
    order by
        s_acctbal desc,n_name,s_name,p_partkey
)
where rownum <= 100;

仔细分析这句SQL，如果把子查询

select
    *
from
    part,partsupp,supplier,nation,region
where
    p_partkey = ps_partkey
    and s_suppkey = ps_suppkey
    and s_nationkey = n_nationkey
    and n_regionkey = r_regionkey
    and r_name = 'ASIA'
    and p_size = 25
    and p_type like '%COPPER'

看成是某个视图V，原来查询主体可以改写成：

select
    s_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_comment
from V
where
    ps_supplycost = (
        select
            min(ps_supplycost)
        from
            V V1
        where
            V.p_partkey = V1.p_partkey
    )

这样将原查询变成一个单表查询，相当于找出V中这样一些记录，使得这些记录的ps_supplycost值在所有与该记录的partkey值相同的记录中取值最小。这个运算的本质是对V按partkey分组后对每组聚合，计算出每组中ps_supplycost最小的那条记录。但是，SQL不支持这种聚合运算，于是只能写成子查询的情况。

二、 SPL实现

SPL有集合和引用数据类型，可以直接聚合计算出最小值所在记录。

	A
1	=now()
2	>size=25
3	>type="COPPER"
4	>name="ASIA"
5	=file("region.btx").import@b().select@1(R_NAME==name).R_REGIONKEY
6	=file("nation.btx").import@b().select(N_REGIONKEY==A5).derive@o().keys(N_NATIONKEY)
7	=file("part.ctx").open().cursor@m(P_PARTKEY,P_MFGR;P_SIZE==size && pos@t(P_TYPE,type)).fetch().keys@im(P_PARTKEY)
8	=file("supplier.ctx").open().cursor@m(S_SUPPKEY,S_NAME,S_ADDRESS,S_NATIONKEY,S_PHONE,S_ACCTBAL,S_COMMENT;S_NATIONKEY:A6).fetch().keys@im(S_SUPPKEY)
9	=file("partsupp.ctx").open().cursor@m(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;PS_PARTKEY:A7,PS_SUPPKEY:A8)
10	=A9.group(PS_PARTKEY).conj(~.minp@a(PS_SUPPLYCOST))
11	=A10.new(PS_SUPPKEY.S_ACCTBAL,PS_SUPPKEY.S_NAME,PS_SUPPKEY.S_NATIONKEY.N_NAME,PS_PARTKEY.P_PARTKEY,PS_PARTKEY.P_MFGR,PS_SUPPKEY.S_ADDRESS,PS_SUPPKEY.S_PHONE,PS_SUPPKEY.S_COMMENT)
12	=A11.total(top(100;-S_ACCTBAL,N_NAME,S_NAME,P_PARTKEY))
13	=interval@ms(A1,now())

代码中A5-A9用于定义上述视图V的游标。

A10中partsupp表PS_PARTKEY有序，可以使用有序分组group函数计算出游标形式的结果集，这样能占用更小的内存并避免Hash计算及比对。minp函数在分组内找出最小值所在记录（而不是最小值本身）。

另外，在A7中也使用了Q1中提到的在游标建立时使用过滤条件的技巧。A8和A9中将这个技巧与switch@i方法结合起来（第2组参数），在游标建立时做外键匹配，不能匹配者直接过滤掉，不再读取其它字段且不再生成该记录，能匹配时则将关联字段转换成指针。

三、进一步优化

1. 数据转换

1.1 维表预加载

数据量不大的维表，可以预先读入内存中供复杂查询使用。

1.2 字符串整数化

优化原理参看Q1，本例中的P_TYPE字段也做字符串整数化处理。

1.3 维表主键序号化

如果维表的主键是从1开始的自然数(也就是记录所在行号)，那么就可以用键值直接按行号定位维表记录，而不再需要计算和比对HASH值了，从而加快与维表关联的速度。而且，直接用序号定位，还不需要建立索引，占用内存也会小很多。

2. 数据转换代码

2.1 region转换

	A
1	=file("region.btx").import@b()
2	=A1.run(R_REGIONKEY=#)
3	=file("region_2.btx").export@b(A2)

A2 将主键R_REGIONKEY的值修改为行号

A3 将转换后的数据保存为region_2.btx

2.2 nation转换

	A
1	=file("nation.btx").import@b()
2	=file("region.btx").import@b()
3	=A1.run(N_NATIONKEY=#, N_REGIONKEY=A2.pselect(R_REGIONKEY==N_REGIONKEY))
4	=file("nation_2.btx").export@b(A3)

A3 将主键N_NATIONKEY设为行号，将N_REGIONKEY设为它在region表的行号

2.3 part转换

	A
1	=file(path+"part.ctx").open().cursor().fetch()
2	=A1.id(P_MFGR,P_BRAND,P_TYPE,P_CONTAINER).(~.sort())
3	=file(destinate+"p_mfgr.txt").export(A2(1))
4	=file(destinate+"p_brand.txt").export(A2(2))
5	=file(destinate+"p_type.txt").export(A2(3))
6	=file(destinate+"p_container.txt").export(A2(4))
7	=A1.run(P_PARTKEY=#, P_MFGR=A2(1).pos@b(P_MFGR), P_BRAND=A2(2).pos@b(P_BRAND), P_TYPE=A2(3).pos@b(P_TYPE), P_CONTAINER=A2(4).pos@b(P_CONTAINER))
8	=file(destinate+"part.ctx").create(#P_PARTKEY, P_NAME,P_MFGR, P_BRAND, P_TYPE, P_SIZE, P_CONTAINER, P_RETAILPRICE, P_COMMENT)
9	>A8.append(A7.cursor())

A2 用id函数得到P_MFGR,P_BRAND,P_TYPE,P_CONTAINER等的不同取值列表，再排序

A3-A6 将A2中排序后的列表分别保存到对应的文件

A7 将主键P_PARTKEY设为行号，将P_MFGR,P_BRAND,P_TYPE,P_CONTAINER等改为它在取值列表中的序号

2.4 supplier转换

	A
1	=file("supplier.ctx").open().cursor().fetch()
2	=file("nation.btx").import@b()
3	=A1.run(S_SUPPKEY=#,S_NATIONKEY=A2.pselect(N_NATIONKEY==S_NATIONKEY))
4	=file("supplier_2.ctx").create(#S_SUPPKEY, S_NAME, S_ADDRESS, S_NATIONKEY, S_PHONE, S_ACCTBAL, S_COMMENT)
5	>A4.append(A3.cursor())

2.5 partsupp转换

	A
1	=file("partsupp.ctx").open().cursor()
2	=file("part.ctx").open().import(P_PARTKEY).keys@im(P_PARTKEY)
3	=file("supplier.ctx").open().import(S_SUPPKEY).keys@im(S_SUPPKEY)
4	=A1.run(PS_PARTKEY=A2.pfind(PS_PARTKEY), PS_SUPPKEY=A3.pfind(PS_SUPPKEY))
5	=file("partsupp_2.ctx”).create@p(#PS_PARTKEY, #PS_SUPPKEY,PS_AVAILQTY, PS_SUPPLYCOST,PS_COMMENT)
6	>A5.append(A4)

A1 partsupp表数据较大，使用游标

A2 读取part表中P_PARTKEY列设为主键并建立索引

A4 在A2中查找主键值为PS_PARTKEY的记录所在行号，将它赋给PS_PARTKEY，PS_SUPPKEY类似处理

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

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

	A
1	>env(region, file(“region_2.btx”).import@b())
2	>env(nation, file("nation_2.btx").import@b())
3	>env(part, file("part_2.ctx").open().import())
4	>env(supplier, file("supplier_2.ctx").open().import())
5	>env(p_type,file("p_type.txt").import@si())
6	>env(p_brand,file("p_brand.txt").import@si())
7	>env(p_container,file("p_container.txt").import@si())
8	>env(p_mfgr,file("p_mfgr.txt").import@si())

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

计算代码：

	A
1	=now()
2	>size=25
3	>type="COPPER"
4	>name="ASIA"
5	=nation.(region(N_REGIONKEY).R_NAME==name)
6	=part.@m(if(P_SIZE==size && pos@t(p_type(P_TYPE),type),~,null))
7	=supplier.@m(if(A5(S_NATIONKEY),~,null))
8	=file("partsupp_2.ctx").open().cursor@m(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;PS_PARTKEY:A6:#, PS_SUPPKEY:A7:#)
9	=A8.group(PS_PARTKEY).conj(~.minp@a(PS_SUPPLYCOST))
10	=A9.new@m(PS_SUPPKEY.S_ACCTBAL,PS_SUPPKEY.S_NAME,nation(PS_SUPPKEY.S_NATIONKEY).N_NAME, PS_PARTKEY.P_PARTKEY,p_mfgr(PS_PARTKEY.P_MFGR):P_MFGR,PS_SUPPKEY.S_ADDRESS, PS_SUPPKEY.S_PHONE, PS_SUPPKEY.S_COMMENT)
11	=A10.total(top(100;-S_ACCTBAL,N_NAME,S_NAME,P_PARTKEY))
12	=interval@ms(A1,now())

A5 用nation.(region(N_REGIONKEY).R_NAME==name)算出一个与记录数等长的、值为true或false的序列，称为对位序列；supplier表中的S_NATIONKEY已经序号化处理过，它的值对应于nation的记录行号，所以在A7就可以用A5(S_NATIONKEY)来判断supplier中此行数据是否满足过滤条件。A6、A7也是对位序列，它的成员是当满足过滤条件时为原记录，不满足时为null。

A6/A7 @m是为了使用多路并行计算

A8 PS_PARTKEY:A6:#表示用partsupp中当前行PS_PARTKEY的值与A6的行号直接关联到此行记录，避免Hash计算和比对，如果A6中此行为null，则不读取当前行，否则将PS_PARTKEY转化为指向A6中关联行的指针。PS_SUPPKEY:A7:#与此类似。

四、使用企业版列式计算

1. 原始数据

	A
1	=now()
2	>size=25
3	>type="COPPER"
4	>name="ASIA"
5	=file("region.btx").import@b().select@1(R_NAME==name).R_REGIONKEY
6	=file("nation.btx").import@b().select(N_REGIONKEY==A5).derive@o().keys(N_NATIONKEY)
7	=file("part.ctx").open().cursor@mv(P_PARTKEY,P_MFGR;P_SIZE==size && pos@t(P_TYPE,type)).fetch().keys@im(P_PARTKEY)
8	=file("supplier.ctx").open().cursor@mv(S_SUPPKEY,S_NAME,S_ADDRESS,S_NATIONKEY,S_PHONE,S_ACCTBAL,S_COMMENT;S_NATIONKEY:A6).fetch().keys@im(S_SUPPKEY)
9	=file("partsupp.ctx").open().cursor@mv(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;PS_PARTKEY:A7,PS_SUPPKEY:A8)
10	=A9.group(PS_PARTKEY).conj(~.minp@a(PS_SUPPLYCOST))
11	=A10.new(PS_SUPPKEY.S_ACCTBAL,PS_SUPPKEY.S_NAME,PS_SUPPKEY.S_NATIONKEY.N_NAME,PS_PARTKEY.P_PARTKEY,PS_PARTKEY.P_MFGR,PS_SUPPKEY.S_ADDRESS,PS_SUPPKEY.S_PHONE,PS_SUPPKEY.S_COMMENT)
12	=A11.total(top(100;-S_ACCTBAL,N_NAME,S_NAME,P_PARTKEY))
13	=interval@ms(A1,now())

A7、A8、A9中cursor函数都加选项@v，表示使用列式游标读数

2. 优化后数据

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

	A
1	>env(region, file(“region_2.btx”).import@bv())
2	>env(nation, file("nation_2.btx").import@bv())
3	>env(part, file("part_2.ctx").open().import@v())
4	>env(supplier, file("supplier_2.ctx").open().import@v())
5	>env(p_type,file("p_type.txt").import@si())

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