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

一、 查询要求

Q1语句查询lineItem的一个定价总结报告。在单个表lineitem上查询某个时间段内，对已经付款的、已经运送的等各类商品进行统计，包括业务量的计费、发货、折扣、税、平均价格等信息。

Q1语句的特点是：带有分组、排序、聚集操作并存的单表查询操作。这个查询会导致表上的数据有95%到97%行被读取到。

二、 Oracle执行

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

select  /*+ parallel(n) */

         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;

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

脚本执行时间，单位：秒

三、 SPL优化

这是一个常规的分组查询，结果集也不大，没有特殊的优化技术，使用多路游标充分利用并行即可。

编写Q1查询的SPL脚本如下：

其中A1格为设置的并行数量，后续其它例子均这样约定。

这段代码较为常规，A6定义多路游标利用并行，因结果集不大，在A7使用groups做小分组。

脚本执行时间，单位：秒

可以看出，SPL的并行效果很好，接近线性提速。

单线程时SPL的性能也更好，这主要是因为组表采用了压缩列式存储。

本查询涉及数据量较大，需要从外存读入数据，硬盘访问时间是不可忽略的因素。当计算未涉及全部数据列时，使用列式存储能减少读取量。而且，列存方式更容易压缩，从而进一步减少硬盘访问时间。

事实上，集算器SPL目前采用Java开发，如果单纯对比CPU的计算性能，应当会弱于C++开发的Oracle。但是，因为压缩列式存储减少了硬盘访问时间，这导致了慢速的Java也能跑过快速的C++。

不过，列存并非总是有效，如果采用机械硬盘，列式存储会导致更多的寻道时间，虽然读取量变少，但由于寻道导致的时间消耗很可能更多。而这次测试采用了SSD硬盘，没有寻道时间的问题。

还需要值得注意的是，我们把过滤条件写进了A6，也就是游标建立的语句中。这样，SPL在读取数据时，如果发现条件不成立，将直接放弃读取相关列，进一步减少硬盘访问和记录生成的时间。

四、 进一步优化

这个SQL是单表分组统计，没有关联，有过滤。lineitem这张表数据量很大，读数耗时很长，如果能在过滤环节大幅度减少读数的消耗，可以进一步提升性能。在业务许可时，如果设计组表时改用过滤字段l_shipdate作为维字段，则可以快速选出目标数据，提升查询速度。

重新生成组表(此组表仅用于Q1查询)的SPL脚本如下：

用此组表测试，查询时间对比如下：

脚本执行时间，单位：秒

创建组表时不一定总按主键排序，如果事先知道查询条件或者这个查询条件很常用，则可以用查询字段排序来提高性能。在使用中可以采取主键排序的组表和查询字段排序的组表共存的策略，编写某个查询任务时可根据需要灵活选用某个组表。