并行查询使用示例_并行查询_PolarDB MySQL数据库_云数据库PolarDB_阿里云技术文档

本文以TPC-H为例，为您介绍并行查询使用示例。案例中所有示例，使用的数据量是TPC-H中SF=100GB ，使用POLARDB节点规格为88 核 710G的配置在主节点进行测试。

GROUP BY & ORDER BY支持
AGGREGATE函数支持（SUM/AVG/COUNT）
JOIN支持
BETWEEN函数 & IN函数支持
LIMIT支持
INTERVAL函数支持
CASE WHEN支持
LIKE支持

GROUP BY & ORDER BY支持

未开启并行查询，耗时1563.32秒，开启并行查询后，耗时只用49.65秒，提升31.48 倍。

原始SQL语句，如下所示：

SELECT   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 '1998-12-01' - INTERVAL '93' day 
GROUP BY l_returnflag, 
         l_linestatus 
ORDER BY l_returnflag, 
         l_linestatus ;

未开启并行查询，耗时1563.32秒。并行查询使用示例_并行查询_PolarDB MySQL数据库_云数据库PolarDB 阿里云技术文档第1张

开启并行查询后，耗时只用49.65秒，提升31.48 倍。并行查询使用示例_并行查询_PolarDB MySQL数据库_云数据库PolarDB 阿里云技术文档第2张

AGGREGATE函数支持（SUM/AVG/COUNT）

未开启并行查询，耗时1563.32秒，开启并行查询后，耗时只用49.65秒，提升31.48 倍。

原始SQL语句，如下所示：

SELECT   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 '1998-12-01' - INTERVAL '93' day 
GROUP BY l_returnflag, 
         l_linestatus 
ORDER BY l_returnflag, 
         l_linestatus ;

未开启并行查询，耗时1563.32秒。并行查询使用示例_并行查询_PolarDB MySQL数据库_云数据库PolarDB 阿里云技术文档第3张

开启并行查询后，耗时只用49.65秒，提升31.48 倍。并行查询使用示例_并行查询_PolarDB MySQL数据库_云数据库PolarDB 阿里云技术文档第4张

JOIN支持

未开启并行查询，耗时21.73秒，开启并行查询后，耗时1.37秒，提升15.86倍。

原始SQL语句，如下所示：

select sum(l_extendedprice* (1 - l_discount)) as revenue 
from   lineitem,   part 
where ( p_partkey = l_partkey and p_brand = 'Brand#12'
        and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') 
        and l_quantity >= 6 and l_quantity <= 6 + 10 
        and p_size between 1 and 5 
        and l_shipmode in ('AIR', 'AIR REG') 
        and l_shipinstruct = 'DELIVER IN PERSON' ) 
    or ( p_partkey = l_partkey and p_brand = 'Brand#13'
        and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') 
        and l_quantity >= 10 and l_quantity <= 10 + 10 
        and p_size between 1 and 10 
        and l_shipmode in ('AIR', 'AIR REG') 
        and l_shipinstruct = 'DELIVER IN PERSON' ) 
    or ( p_partkey = l_partkey and p_brand = 'Brand#24'
        and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') 
        and l_quantity >= 21 and l_quantity <= 21 + 10 
        and p_size between 1 and 15 
        and l_shipmode in ('AIR', 'AIR REG') 
        and l_shipinstruct = 'DELIVER IN PERSON' );

未开启并行查询，耗时21.73秒。并行查询使用示例_并行查询_PolarDB MySQL数据库_云数据库PolarDB 阿里云技术文档第5张

开启并行查询后，耗时1.37秒，提升15.86倍。并行查询使用示例_并行查询_PolarDB MySQL数据库_云数据库PolarDB 阿里云技术文档第6张

BETWEEN函数 & IN函数支持

未开启并行查询，耗时21.73秒，开启并行查询后，耗时1.37秒，提升15.86倍。

原始SQL语句，如下所示：

select sum(l_extendedprice* (1 - l_discount)) as revenue 
from   lineitem,   part 
where ( p_partkey = l_partkey and p_brand = 'Brand#12'
        and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') 
        and l_quantity >= 6 and l_quantity <= 6 + 10 
        and p_size between 1 and 5 
        and l_shipmode in ('AIR', 'AIR REG') 
        and l_shipinstruct = 'DELIVER IN PERSON' ) 
    or ( p_partkey = l_partkey and p_brand = 'Brand#13'
        and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') 
        and l_quantity >= 10 and l_quantity <= 10 + 10 
        and p_size between 1 and 10 
        and l_shipmode in ('AIR', 'AIR REG') 
        and l_shipinstruct = 'DELIVER IN PERSON' ) 
    or ( p_partkey = l_partkey and p_brand = 'Brand#24'
        and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') 
        and l_quantity >= 21 and l_quantity <= 21 + 10 
        and p_size between 1 and 15 
        and l_shipmode in ('AIR', 'AIR REG') 
        and l_shipinstruct = 'DELIVER IN PERSON' );

未开启并行查询，耗时21.73秒。并行查询使用示例_并行查询_PolarDB MySQL数据库_云数据库PolarDB 阿里云技术文档第7张

开启并行查询后，耗时1.37秒，提升15.86倍。并行查询使用示例_并行查询_PolarDB MySQL数据库_云数据库PolarDB 阿里云技术文档第8张

LIMIT支持

未开启并行查询，耗时339.22 秒，开启并行查询后，耗时29.31秒，提升11.57倍。

原始SQL语句，如下所示：

select l_shipmode, sum(case when o_orderpriority = '1-URGENT' or o_orderpriority = '2-HIGH' then 1 
    else 0 
end) as high_line_count, sum(case when o_orderpriority <> '1-URGENT' and o_orderpriority <> '2-HIGH' then 1 
else 0 
end) as low_line_count 
from   orders,   lineitem 
where o_orderkey = l_orderkey 
and l_shipmode in ('MAIL', 'TRUCK') 
and l_commitdate < l_receiptdate 
and l_shipdate < l_commitdate 
and l_receiptdate >= date '1996-01-01' 
and l_receiptdate < date '1996-01-01' + interval '1' year 
group by l_shipmode 
order by l_shipmode limit 10;

未开启并行查询，耗时339.22 秒。并行查询使用示例_并行查询_PolarDB MySQL数据库_云数据库PolarDB 阿里云技术文档第9张

开启并行查询后，耗时29.31秒，提升11.57倍。并行查询使用示例_并行查询_PolarDB MySQL数据库_云数据库PolarDB 阿里云技术文档第10张

INTERVAL函数支持

未开启并行查询，耗时220.87秒，开启并行查询后，耗时7.75秒，提升28.5倍。

原始SQL语句，如下所示：

select 
    100.00 * sum(case when p_type like 'PROMO%' then l_extendedprice * (1 - l_discount) 
    else 0 
end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue 
from   lineitem,   part 
where l_partkey = p_partkey
and l_shipdate >= date '1996-01-01' 
and l_shipdate < date '1996-01-01' + interval '1' month limit 10;

未开启并行查询，耗时220.87秒。并行查询使用示例_并行查询_PolarDB MySQL数据库_云数据库PolarDB 阿里云技术文档第11张

开启并行查询后，耗时7.75秒，提升28.5倍。并行查询使用示例_并行查询_PolarDB MySQL数据库_云数据库PolarDB 阿里云技术文档第12张

CASE WHEN支持

未开启并行查询，耗时220.87秒，开启并行查询后，耗时7.75秒，提升28.5倍。

原始SQL语句，如下所示：

select 
    100.00 * sum(case when p_type like 'PROMO%' then l_extendedprice * (1 - l_discount) 
    else 0 
end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue 
from   lineitem,   part 
where l_partkey = p_partkey
and l_shipdate >= date '1996-01-01' 
and l_shipdate < date '1996-01-01' + interval '1' month limit 10;

未开启并行查询，耗时220.87秒。并行查询使用示例_并行查询_PolarDB MySQL数据库_云数据库PolarDB 阿里云技术文档第13张

开启并行查询后，耗时7.75秒，提升28.5倍。并行查询使用示例_并行查询_PolarDB MySQL数据库_云数据库PolarDB 阿里云技术文档第14张

LIKE支持

未开启并行查询，耗时427.46秒，开启并行查询后，耗时33.72秒，提升12.68倍。

原始SQL语句，如下所示：

select s_name, s_address from
 supplier,  nation where
s_suppkey in 
    ( select ps_suppkey from  partsupp where
             ps_partkey in ( select p_partkey from  part where p_name like 'dark%')
            and ps_availqty>(select 0.0005 * sum(l_quantity) as col1
     from   lineitem,   partsupp
     where l_partkey = ps_partkey and l_suppkey = ps_suppkey
     and l_shipdate >= date '1993-01-01' and l_shipdate < date '1993-01-01' + interval '1' year)
    )
and s_nationkey = n_nationkey and n_name = 'JORDAN'
order by s_name limit 10;

未开启并行查询，耗时427.46秒。并行查询使用示例_并行查询_PolarDB MySQL数据库_云数据库PolarDB 阿里云技术文档第15张

开启并行查询后，耗时33.72秒，提升12.68倍。并行查询使用示例_并行查询_PolarDB MySQL数据库_云数据库PolarDB 阿里云技术文档第16张

并行查询使用示例_并行查询_PolarDB MySQL数据库_云数据库PolarDB

并行查询使用示例

GROUP BY & ORDER BY支持

AGGREGATE函数支持（SUM/AVG/COUNT）

JOIN支持

BETWEEN函数 & IN函数支持

LIMIT支持

INTERVAL函数支持

CASE WHEN支持

LIKE支持

版权声明

评论