北京
乾学院 32 号会员
1 回帖  •  262 浏览  • 
     ...

用 TPCH 练习性能优化 Q2

 
tpch(25) 性能优化(158)

一、 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("part.ctx").open().cursor().fetch()

2

=A1.id(P_TYPE).sort()

3

=file("p_type.txt").export(A2)

4

=A1.run(P_PARTKEY=#, P_TYPE=A2.pos@b(P_TYPE))

5

=file("part_2.ctx").create(#P_PARTKEY, P_NAME,P_MFGR, P_BRAND, P_TYPE, P_SIZE, P_CONTAINER, P_RETAILPRICE, P_COMMENT)

6

>A5.append(A4.cursor())

A2 用id函数得到P_TYPE的不同取值列表，再排序

A3 将A2中排序后的列表保存到p_type.txt文件

A4 将主键P_PARTKEY设为行号，将P_TYPE改为它在取值列表中的序号

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())

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

计算代码：

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，将维表读成列式序表。

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

计算代码：

A

1

=now()

2

>size=25

3

>type="COPPER"

4

>name="ASIA"

5

=nation.(region(N_REGIONKEY).R_NAME==name)

6

=part.@m(P_SIZE==size && pos@t(p_type(P_TYPE),type))

7

=supplier.(A5(S_NATIONKEY))

8

=file("partsupp_2.ctx").open().cursor@mv(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;A6(PS_PARTKEY),A7(PS_SUPPKEY))

9

=A8.group(PS_PARTKEY).conj(~.minp@a(PS_SUPPLYCOST))

10

=A9.new(supplier(PS_SUPPKEY):s,part(PS_PARTKEY):p).new(s.S_ACCTBAL,s.S_NAME,nation(s.S_NATIONKEY).N_NAME, p.P_PARTKEY,p_mfgr(p.P_MFGR):P_MFGR,s.S_ADDRESS, s.S_PHONE, s.S_COMMENT)

11

=A10.total(top(100;-S_ACCTBAL,N_NAME,S_NAME,P_PARTKEY))

12

=interval@ms(A1,now())

A6中加选项@m，A7中没有@m，为啥？在列式序表中，计算T.@m(x)表达式时，会将T复制多份(与并行路数相同)，如果表达式x很简单，用多路计算的效率可能还不如单路。这里A6的表达式比较复杂，而A7中比较简单。

在列式计算中，要尽量避免使用switch操作。A8中使用A6(PS_PARTKEY)仅仅过滤数据，而不是象原始数据时使用PS_PARTKEY:A6:#关联与过滤同时进行，因为这里PS_PARTKEY是序号化的，可以直接在part表中通过行号获取记录而无需关联，可以更好地使用列式计算的优点。

A10中先用PS_SUPPKEY在supplier表中找到对应的记录生成s列、用PS_PARTKEY在part表中找到对应记录生成p列，然后再次用new函数通过s、p列找到要显示的目标列数据。

五、 测试结果

单位：秒

常规

列式

优化前

2.2

1.4

优化后

1.7

1.0


以下是广告时间

对润乾产品感兴趣的小伙伴，一定要知道软件还能这样卖哟
润乾软件年买断模式
性价比还不过瘾？ 欢迎加入好多乾计划。
这里可以低价购买软件产品，让已经亲民的价格更加便宜！
这里可以销售产品获取佣金，赚满钱包成为土豪不再是梦！
这里还可以推荐分享抢红包，每次都是好几块钱的巨款哟！
来吧，现在就加入，拿起手机扫码，开始乾包之旅



嗯，还不太了解好多乾？
猛戳这里
玩转好多乾
tpch(25) 性能优化(158)