GreenPlum分区表和非分区压测对比

背景

  之前有对Greenplum进行TPC-H测试，参考文章为[TPC-H测试](https://help.aliyun.com/document_detail/156160.html?spm=a2c4g.11186623.6.662.552a2dcaJdRHLG)，其中的表主要是列存表，没有对表进行分区处理，查询起来非常慢，其中的Q1 sql查询（查询表lineitem）时间达到600s，单表（lineitem）最大数据量为27亿。因此，本次在之前测试的基础上，对lineitem表进行分区处理，对比分区前后的结果。

机器配置

 1. 测试的Greenplum集群由18个计算节点，三台宿主机，每台宿主机有8个CPU核，32GB内存。
 2. 单独用一台服务器（centos7 8核16G）运行benchmarksql工具来生成TPC-C负载，向Greenplum实例发送请求。
 3. 表lineitem数据量为27亿

分区操作sql

按年分区sql：
create table lineitem_partition (    
    l_orderkey    bigint not null,        
    l_partkey     integer not null,    
    l_suppkey     integer not null,    
    l_linenumber  integer not null,    
    l_quantity    DECIMAL(15,2) not null,    
    l_extendedprice  DECIMAL(15,2) not null,    
    l_discount    DECIMAL(15,2) not null,    
    l_tax         DECIMAL(15,2) not null,    
    l_returnflag  char(1) not null,    
    l_linestatus  char(1) not null,    
    l_shipdate    date not null,    
    l_commitdate  date not null,    
    l_receiptdate date not null,    
    l_shipinstruct char(25) not null,   
    l_shipmode     char(10) not null,    
    l_comment      varchar(44) not null)
with (appendonly=true, orientation=column)
distributed by (l_orderkey) partition by range(l_shipdate)(
        PARTITION p1992 START ('1992-01-01'::date) inclusive END ('1993-01-01'::date) exclusive,
    PARTITION p1993 START ('1993-01-01'::date) inclusive END ('1994-01-01'::date) exclusive,
    PARTITION p1994 START ('1994-01-01'::date) inclusive END ('1995-01-01'::date) exclusive,
    PARTITION p1995 START ('1995-01-01'::date) inclusive END ('1996-01-01'::date) exclusive,
    PARTITION p1996 START ('1996-01-01'::date) inclusive END ('1997-01-01'::date) exclusive,
    PARTITION p1997 START ('1997-01-01'::date) inclusive END ('1998-01-01'::date) exclusive,
    PARTITION p1998 START ('1998-01-01'::date) inclusive END ('1999-01-01'::date) inclusive
); ```
--------------------------------------------------------------------------------------------------------
``` 按月分区sql：
create table lineitem_partition_month (        
        l_orderkey    bigint not null,            
        l_partkey     integer not null,       
        l_suppkey     integer not null,       
        l_linenumber  integer not null,        
        l_quantity    DECIMAL(15,2) not null,          
        l_extendedprice  DECIMAL(15,2) not null,        
        l_discount    DECIMAL(15,2) not null,        
        l_tax         DECIMAL(15,2) not null,        
        l_returnflag  char(1) not null,        
        l_linestatus  char(1) not null,        
        l_shipdate    date not null,        
        l_commitdate  date not null,        
        l_receiptdate date not null,        
        l_shipinstruct char(25) not null,       
        l_shipmode     char(10) not null,        
        l_comment      varchar(44) not null
)with (appendonly=true, orientation=column)
  distributed by (l_orderkey) 
  partition by range(l_shipdate)(    
        PARTITION p_ START ('1992-01-01'::date) inclusive END ('1993-01-01'::date) exclusive every ('1 month'::interval)));```
--------------------------------------------------------------------------------------------------------
```  测试sql1（时间范围为1年）：
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_partition_month
where
    l_shipdate <= date '1998-12-31' and l_shipdate >= date '1998-01-01'
group by
    l_returnflag,
    l_linestatus
order by
    l_returnflag,
    l_linestatus;

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_partition_month
where
    l_shipdate <= date '1998-12-31' and l_shipdate >= date '1998-12-01'
group by
    l_returnflag,
    l_linestatus
order by
    l_returnflag,
    l_linestatus;

测试结果

测试sql1

测试sql2

测试结论

1. Greenplum中分区表其目的也是用来避免扫描大表的全部内容，而分区表能够提升查询性能。分区是不会更改数据在segments之间的物理分布，而是对大表进行逻辑上的划分。
2. 需要注意的是，分区操作以及分区所依据的键需要结合业务进行判断，如例子中的业务是需要根据时间进行分组查询的，这也是大数据情况下很经典的查询方法，因此我们根据时间进行了分区操作。由上也可看到，分区粒度按月比按年的查询速度更快，按年分区也会比普通未分区表的查询速度快。但是数据量较小的情况并不需要进行分区，在数据量达到亿级别时，分区才能很好的发挥它的优势。
3. 虽然分区操作能加快大表的查询速度，但是我们注意查询语句的用法，比如当我们把where 条件语句改成 `where l_shipdate <= date '1998-10-31' - interval '30 day' ` ，同样是查询单月的数据，但是查询时间能达到30s以上，因此，在需要加快查询速度的情况下，sql语句的写法是否能够完全契合当前表的设置也很重要。