从 TPCH 测试学习性能优化技巧之 Q2

一、     查询要求

 

Q2语句查询获得最小代价的供货商。得到给定的区域内,对于指定的零件(某一类型和大小的零件),哪个供应商能以最低的价格供应它,就可以选择哪个供应商来订货。

Q2语句的特点是:带有排序、聚集操作、子查询并存的多表查询操作。查询语句没有从语法上限制返回多少条元组,TPC-H标准规定,查询结果只返回前100行即可(通常依赖于应用程序实现)。

 

 

二、     Oracle执行

 

Oracle编写的查询SQL语句如下:

select * from (

         select   /*+ parallel(n) */

                   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;

其中/*+ parallel(n) */ 是Oracle的并行查询语法,n是并行数。

脚本执行时间,单位:秒

并行数124812
Oracle5635231627

 

 

三、     SPL优化

 

仔细分析这句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   /*+ parallel(n) */

                   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不支持这种聚合运算,于是只能写成子查询的情况(即使转换成单表运算后)。

如果数据库优化引擎不好,严格按这个子查询描述的方法去遍历计算,就会导致N*N的复杂度(N是V的记录数);即使数据库优化引擎较好,也需要先对V按partkey分组求ps_supplycost的最小值后做形成中间结果集再做索引,然后再次遍历V,计算量也不少。

 

解决这个问题更好的办法就是支持这种返回记录本身的聚合计算,一次分组聚合即可完成运算。SPL有集合和引用数据类型,也支持聚合出最小值所在记录的聚合运算,可以实现这个想法,整体复杂度就会低很多。

SPL脚本如下:

 A
1=1
2>size=25
3>type="*COPPER"
4>name="ASIA"
5=now()
6=file(path+"region.ctx").create().cursor().select(R_NAME==name).fetch()
7=file(path+"nation.ctx").create().cursor().switch@i(N_REGIONKEY,   A6:R_REGIONKEY).fetch().keys@i(N_NATIONKEY)
8=file(path+"part.ctx").create().cursor@m(P_PARTKEY,P_MFGR;P_SIZE==size   && like(P_TYPE,type);A1).fetch().keys@i(P_PARTKEY)
9=file(path+"supplier.ctx").create().cursor@m(S_SUPPKEY,S_NAME,S_ADDRESS,S_NATIONKEY,S_PHONE,S_ACCTBAL,S_COMMENT;S_NATIONKEY:A7;A1)
10=A9.fetch().keys@i(S_SUPPKEY)
11=file(path+"partsupp.ctx").create().cursor@m(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;PS_PARTKEY:A8,PS_SUPPKEY:A10;A1)
12=A11.groups(PS_PARTKEY;top(1;PS_SUPPLYCOST):rs).conj(rs)
13=A12.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)
14=A13.sort(S_ACCTBAL:-1,N_NAME,S_NAME,P_PARTKEY).to(100)
15=now()
16=interval@s(A5,A15)

需要解释的是,SPL相对于SQL更底层一些,SPL不象SQL有元数据概念,也没有系统级的表概念,数据访问直接从文件开始读取数据,这时前面准备数据的代码就显得稍微冗长一点。实际应用中可以通过使用SPL预定义全程变量或虚表语法来简化,达到类似SQL直接使用数据表的效果。但这不是本篇的重点,而且为了让读者更方便地看出数据的原始流向,这里就采用了直接文件访问的语法。

 

代码中A6-A11用于定义上述视图V的游标,A12中用groups内的top函数实现分组的同时聚合出最小值所在记录(而不是最小值本身)。

A7中的switch@i函数将把外键不能匹配的记录过滤掉,同时将能匹配的关联字段转换成外键表记录的指针,这样在后面可以直接用.的形式访问外键表的字段。SPL看待JOIN运算的思路和SQL不一样,如果数据能事先加载进内存,SPL以利用预关联提高运算性能。不过,本系列例子均假定从外存取数计算,本问题中SPL和SQL在JOIN运算性能也没有算法上的区别,只是写法不同。详细解释可参考SPL教案中关于JOIN的部分。

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

 

脚本执行时间,单位:秒

并行数124812
Oracle5635231627
SPL组表2014854

这个问题的数据量不大,几次执行后,操作系统可以把数据都缓存进内存,列存在这里不是重点,带来的访问量优势可以忽略,性能优势主要是算法优化带来的。

从表中还能看出,这种分组聚合的并行效果也较好。

相关推荐
<p> 课程演示环境:Windows10  </p> <p> 需要学习<span>Ubuntus</span>系统<span>YOLOv4-tiny</span>的同学请前往《<span>YOLOv4-tiny</span>目标检测实战:训练自己的数据集》 <span></span> </p> <p> <span> </span> </p> <p> <span style="color:#E53333;">YOLOv4-tiny</span><span style="color:#E53333;">来了!速度大幅提升!</span><span></span> </p> <p> <span> </span> </p> <p> <span>YOLOv4-tiny</span>在<span>COCO</span>上的性能可达到:<span>40.2% AP50, 371 FPS (GTX 1080 Ti)</span>。相较于<span>YOLOv3-tiny</span>,<span>AP</span>和<span>FPS</span>的性能有巨大提升。并且,<span>YOLOv4-tiny</span>的权重文件只有<span>23MB</span>,适合在移动端、嵌入式设备、边缘计算设备上部署。<span></span> </p> <p> <span> </span> </p> <p> 本课程将手把手地教大家使用<span>labelImg</span>标注和使用<span>YOLOv4-tiny</span>训练自己的数据集。课程实战分为两个项目:单目标检测(足球目标检测)和多目标检测(足球和梅西同时检测)。<span></span> </p> <p> <span> </span> </p> <p> 本课程的<span>YOLOv4-tiny</span>使用<span>AlexAB/darknet</span>,在<span>Windows10</span>系统上做项目演示。包括:<span>YOLOv4-tiny</span>的网络结构、安装<span>YOLOv4-tiny</span>、标注自己的数据集、整理自己的数据集、修改配置文件、训练自己的数据集、测试训练出的网络模型、性能统计<span>(mAP</span>计算<span>)</span>和先验框聚类分析。 <span> </span> </p> <p> <span> </span> </p> <p> 除本课程《<span>Windows</span>版<span>YOLOv4-tiny</span>目标检测实战:训练自己的数据集》外,本人推出了有关<span>YOLOv4</span>目标检测的系列课程。请持续关注该系列的其它视频课程,包括:<span></span> </p> <p> 《<span>Windows</span>版<span>YOLOv4</span>目标检测实战:训练自己的数据集》<span></span> </p> <p> 《<span>Windows</span>版<span>YOLOv4</span>目标检测实战:人脸口罩佩戴识别》<span></span> </p> <p> 《<span>Windows</span>版<span>YOLOv4</span>目标检测实战:中国交通标志识别》<span></span> </p> <p> 《<span>Windows</span>版<span>YOLOv4</span>目标检测:原理与源码解析》<span></span> </p> <p> <span> <img alt="" src="https://img-bss.csdnimg.cn/202007061503586145.jpg" /></span> </p> <p> <span><img alt="" src="https://img-bss.csdnimg.cn/202007061504169339.jpg" /><br /> </span> </p>
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页