Greenplum 最佳实践 - 行存与列存的选择以及转换方法

2 minute read

背景

数据在数据库中的存储形式多种多样,比较常见的如

1. PostgreSQL的堆表,以行的形式存储,(当变成字段压缩后的长度超过数据块的四分之一时,会以TOAST的形式存储到TOAST表)。

2. MySQL innodb则是以b+tree形式存储的。

3. 在数据仓库产品中,如Greenplum,支持行存,也支持列存。

还有很多存储格式,本文将讨论行存和列存应该如何选择呢?

行存储优劣分析

Greenplum行存储(堆表)的优势在哪里?

数据顺序写入BLOCK中,持续写入的情况下,一条记录命中在一个块中,IO开销相对比较小,速度较快。

查询多个字段时,因为记录在一个块中命中,速度较快。

Greenplum行存储(堆表)的劣势在哪里?

查询少量字段时,也要访问整条记录,造成一定的IO浪费。

行存储的压缩比有限。

行存储适合什么应用场景

行存储适合非常典型的OLTP应用场景。

列存储优劣分析

Greenplum列存储的优势在哪里?

数据按列存储,压缩比可以做到很高。

当查询少量字段时,扫描的块更少,可以节约IO还能提升效率。

Greenplum列存储的劣势在哪里?

因为是按列存储的,当需要查询大量字段时,或者查询的记录数偏少时,会造成离散IO较多。

例如查询1条记录的20个列,行存储可能只需要扫描1个块,而列存储至少需要扫描20个块。

由于IO的放大,列存储不适合OLTP的场景,如有大量的更新,查询操作。

列存储适合什么应用场景

列存储适合非常典型的OLAP应用场景,按列做较大范围的聚合分析,或者JOIN分析。

如何设置表的存储格式

建表时,在with(storage parameter)中指定

screenshot

screenshot

或者在分区的with(storage parameter)中指定

screenshot

或者在子分区的with(storage parameter)中指定

screenshot

screenshot

因此Greenplum的存储格式支持到了子分区这个级别,一张表(指父表)可以混合使用行存储与列存储。

如何转换表的存储格式

screenshot

screenshot

行列混合存储应用场景

例如用户如果有一张按时间分区的表,最近1个月的查询类似OLTP的请求,需要查询较多字段,而一个月以前的表则OLAP的需求更旺盛。

这种情况下,我们的需求是将老的分区转换为列存储,怎么做呢?

例子

创建分区表,选择行存储

create table t_digoal (id int, info text, crt_time timestamp) distributed by (id) partition by range(crt_time) (start (date '2016-08-01') inclusive end (date '2016-12-01') exclusive every (interval '1 day'));  

查看分区定义

postgres=> select * from pg_partitions;  
-[ RECORD 1 ]------------+----------------------------------------------------------------------------------------------------------------------------------------------  
schemaname               | public  
tablename                | t_digoal  
partitionschemaname      | public  
partitiontablename       | t_digoal_1_prt_1  
partitionname            |   
parentpartitiontablename |   
parentpartitionname      |   
partitiontype            | range  
partitionlevel           | 0  
partitionrank            | 1  
partitionposition        | 1  
partitionlistvalues      |   
partitionrangestart      | '2016-08-01 00:00:00'::timestamp without time zone  
partitionstartinclusive  | t  
partitionrangeend        | '2016-08-02 00:00:00'::timestamp without time zone  
partitionendinclusive    | f  
partitioneveryclause     | '1 day'::interval  
partitionisdefault       | f  
partitionboundary        | START ('2016-08-01 00:00:00'::timestamp without time zone) END ('2016-08-02 00:00:00'::timestamp without time zone) EVERY ('1 day'::interval)  
parenttablespace         | pg_default  
partitiontablespace      | pg_default  
-[ RECORD 2 ]------------+----------------------------------------------------------------------------------------------------------------------------------------------  
schemaname               | public  
tablename                | t_digoal  
partitionschemaname      | public  
partitiontablename       | t_digoal_1_prt_2  
partitionname            |   
parentpartitiontablename |   
parentpartitionname      |   
partitiontype            | range  
partitionlevel           | 0  
partitionrank            | 2  
partitionposition        | 2  
partitionlistvalues      |   
partitionrangestart      | '2016-08-02 00:00:00'::timestamp without time zone  
partitionstartinclusive  | t  
partitionrangeend        | '2016-08-03 00:00:00'::timestamp without time zone  
partitionendinclusive    | f  
partitioneveryclause     | '1 day'::interval  
partitionisdefault       | f  
partitionboundary        | START ('2016-08-02 00:00:00'::timestamp without time zone) END ('2016-08-03 00:00:00'::timestamp without time zone) EVERY ('1 day'::interval)  
parenttablespace         | pg_default  
partitiontablespace      | pg_default  

创建列存单表,用于交换分区

postgres=> create table t_digoal_col(id int, info text, crt_time timestamp) with (appendonly=true, ORIENTATION=column) distributed by (id);  
CREATE TABLE  

将历史分区数据插入列存储的交换分区

insert into t_digoal_col select * from t_digoal_1_prt_1;  

指定对应的rank,交换分区

alter table t_digoal exchange partition for (rank(1)) with table t_digoal_col with validation;  

可以使用 without validation 加快速度。

如何查看表的存储结构

pg_class.relstorage表示这个对象是什么存储:

postgres=# select distinct relstorage from pg_class ;    
 relstorage     
------------    
 a  -- 行存储AO表    
 h  -- heap堆表、索引    
 x  -- 外部表(external table)    
 v  -- 视图    
 c  -- 列存储AO表    
(5 rows)    

查询当前数据库有哪些AO表:

postgres=# select t2.nspname, t1.relname from pg_class t1, pg_namespace t2 where t1.relnamespace=t2.oid and relstorage in ('c', 'a');    
 nspname  |      relname          
----------+-------------------    
 postgres | tbl_tag    
 postgres | tbl_pos_1_prt_p1    
 postgres | tbl_pos_1_prt_p2    
 postgres | tbl_pos_1_prt_p3    
 postgres | tbl_pos_1_prt_p4    
 postgres | tbl_pos_1_prt_p5    
 postgres | tbl_pos_1_prt_p6    
 postgres | tbl_pos_1_prt_p7    
 postgres | tbl_pos_1_prt_p8    
 postgres | tbl_pos_1_prt_p9    
 postgres | tbl_pos_1_prt_p10    
 postgres | tbl_pos    
 postgres | xx_czrk_qm_col    
 postgres | ao1    
(14 rows)    

查询当前数据库有哪些堆表:

select t2.nspname, t1.relname from pg_class t1, pg_namespace t2 where t1.relnamespace=t2.oid and relstorage in ('h') and relkind='r';    

祝大家玩得开心,欢迎随时来 阿里云促膝长谈业务需求 ,恭候光临

阿里云的小伙伴们加油,努力 做好内核与服务,打造最贴地气的云数据库

Flag Counter

digoal’s 大量PostgreSQL文章入口