选择表存储模型

选择表存储模型

Greenplum数据库支持多种存储模型和一种混合存储模型。当用户创建一个表时，用户会选择如何存储它的数据。这个主题解释了表存储的选项以及如何为用户的负载选择最好的存储模型。

堆存储
追加优化存储
选择面向行或者面向列的存储
使用压缩（只用于追加优化表）
检查一个追加优化表的压缩和分布
修改一个表
删除一个表

注意：为了简化数据库表的创建，用户可以使用Greenplum数据库的服务器配置参数 gp_default_storage_options为一些表存储选项指定默认值。

更多有关该参数的信息，请见Greenplum数据库参考指南中的“服务器配置参数”部分。

堆存储

默认情况下，Greenplum数据库使用和PostgreSQL相同的堆存储模型。堆表存储在OLTP类型负载下表现最好，这种环境中数据会在初始载入后被频繁地修改。UPDATE和DELETE操作要求存储行级版本信息来确保可靠的数据库事务处理。堆表最适合于较小的表，例如维度表，它们在初始载入数据后会经常被更新。

追加优化存储

追加优化表存储在数据仓库环境中的规范化事实表表现最好。规范化事实表通常是系统中最大的表。事实表通常成批地被载入并且被只读查询访问。将大型的事实表改为追加优化存储模型可以消除每行中的更新可见性信息负担，这可以为每一行节约大概20字节。这可以得到一种更加简洁并且更容易优化的页面结构。追加优化表的存储模型是为批量数据装载优化的，因此不推荐单行的INSERT语句。

要创建一个堆表

面向行的堆表是默认的存储类型。

=> CREATE TABLE foo (a int, b text) DISTRIBUTED BY (a);

使用CREATE TABLE命令的WITH子句可以声明表的存储选项。默认是将表创建为面向行的堆存储表。例如，要创建一个不压缩的追加优化表：

=> CREATE TABLE bar (a int, b text) 
    WITH (appendonly=true)
    DISTRIBUTED BY (a);

在一个可序列化事务中的追加优化表上不允许UPDATE和DELETE，它们将导致该事务中止。追加优化表上不支持CLUSTER、DECLARE…FOR UPDATE和触发器。

选择面向行或者面向列的存储

Greenplum provides a choice of storage orientation models: row, column, or a combination of both. This topic provides general guidelines for choosing the optimum storage orientation for a table. Evaluate performance using your own data and query workloads.

面向行的存储：good for OLTP types of workloads with many iterative transactions and many columns of a single row needed all at once, so retrieving is efficient.
面向列的存储：适合于在少量列上计算数据聚集的数据仓库负载，或者是用于需要对单列定期更新但不修改其他列数据的情况。

对于大部分常用目的或者混合负载，面向行的存储提供了灵活性和性能的最佳组合。不过，也有场景中面向列的存储模型提供了更高效的I/O和存储。在为一个表决定存储方向模型时，请考虑下列需求：

表数据的更新。如果用户会频繁地装载和更新表数据，请选择一个面向行的堆表。面向列的表存储只能用于追加优化表。

更多信息请见堆存储。
频繁的INSERT。如果频繁地向表中插入行，请考虑面向行的模型。列存表并未对写操作优化，因为一行的列值必须被写到磁盘上的不同位置。
查询中要求的列数。 如果在查询的SELECT列表或者WHERE子句中常常要求所有或者大部分列，请考虑面向行的模型。面向列的表最适合的情况是，查询会聚集一个单一列中的很多值且WHERE或者HAVING谓词也在该聚集列上。例如：
```
SELECT SUM(salary)...
```
```
SELECT AVG(salary)... WHERE salary > 10000
```
。另一种适合面向列的情况是WHERE谓词在一个单一列上并且返回相对较少的行。例如：
```
SELECT salary, dept ... WHERE state='CA'
```
表中的列数。 在同时要求很多列或者表的行尺寸相对较小时，面向行的存储会更有效。对于具有很多列的表且查询中访问这些列的一个小子集时，面向列的表能够提供更好的查询性能。
压缩。 列数据具有相同的数据类型，因此在列存数据上支持存储尺寸优化，但在行存数据上则不支持。例如，很多压缩方案使用临近数据的相似性来进行压缩。不过，临近压缩做得越好，随机访问就会越困难，因为必须解压数据才能读取它们。

要创建一个面向列的表

CREATE TABLE命令的WITH子句指定表的存储选项。默认是面向行的堆表。使用面向列的存储的表必须是追加优化表。例如，要创建一个列存表：

=> CREATE TABLE bar (a int, b text) 
    WITH (appendonly=true, orientation=column)
    DISTRIBUTED BY (a);

使用压缩（只适用于追加优化表）

对于追加优化表，在Greenplum数据库中有两种类型的库内压缩可用：

应用于一整个表的表级压缩。
应用到一个指定列的列级压缩。用户可以为不同的列应用不同的列级压缩算法。

下面的表摘要了可用的压缩算法。

表 1. 用于追加优化表的压缩算法
表方向	可用的压缩类型	支持的算法
行	表	`ZLIB`以及 `QUICKLZ`¹
列	列和表	`RLE_TYPE`、`ZLIB`以及 `QUICKLZ`¹

注意： 1QuickLZ压缩在Greenplum数据库的开源版本中不可用。

在为追加优化表选择一种压缩类型和级别时，要考虑这些因素：

CPU使用。用户的Segment系统必须具有可用的CPU能力来压缩和解压数据。
压缩率/磁盘尺寸。最小化磁盘尺寸是一个因素，但也要考虑压缩和扫描数据所需的时间和CPU计算能力。要找到能高效压缩数据但不导致过长压缩时间或者过慢扫描率的最优设置。
压缩的速度。与zlib比较，QuickLZ压缩通常使用较少的CPU计算能力、能更快地压缩数据且得到较低的压缩率。zlib提供更高的压缩率，但是速度较慢。

例如，在压缩级别1上（compresslevel=1），QuickLZ和zlib具有差不多的压缩率，但是压缩速度不同。使用compresslevel=6的zlib能比QuickLZ更显著地提升压缩率，但是压缩速度更慢。
解压速度/扫描率。压缩的追加优化表的性能取决于硬件、查询调优设置和其他因素。请执行对比测试来判断在用户的环境中的真实性能。

注意：不要在使用压缩的文件系统上创建压缩的追加优化表。如果用户的Segment数据目录所在的文件系统是一个压缩的文件系统，用户的追加优化表不能使用压缩。

压缩的追加优化表的性能取决于硬件、查询调优设置和其他因素。请执行对比测试来判断在用户的环境中的真实性能。

注意： QuickLZ的压缩级别只能被设置为级别1，没有其他的选项可用。zlib的压缩级别可以被设置为1-9的值。RLE的压缩级别可以被设置为1-4的值。

ENCODING子句指定个别列的压缩类型和级别。当ENCODING子句与WITH子句冲突时，ENCODING子句的优先级高于WITH子句。

要创建一个压缩表

CREATE TABLE命令的WITH子句声明表的存储选项。使用压缩技术的表必须是追加优化表。例如，要创建一个使用zlib压缩且压缩级别为5的追加优化表：

=> CREATE TABLE foo (a int, b text) 
   WITH (appendonly=true, compresstype=zlib, compresslevel=5);

检查追加优化表的压缩和分布

Greenplum提供了内建函数来检查一个追加优化表的压缩率和分布情况。这些函数要求对象ID或者表名作为参数。用户可以用方案名来限定表名。

表 2. 用于压缩追加优化表元数据的函数
函数	返回类型	描述
get_ao_distribution(name) get_ao_distribution(oid)	(dbid, tuplecount)行的集合	展示一个追加优化表的行在阵列中的分布情况。返回一个行集合，其中每一个行包括了一个Segment dbid以及存储在其中的元组数。
get_ao_compression_ratio(name) get_ao_compression_ratio(oid)	float8	为一个压缩的追加优化表计算压缩率。如果该信息不可用，这个函数会返回-1。

压缩率被返回为一个公比。例如，返回值 3.19或者3.19:1表示解压后的表比压缩表的尺寸的3倍略大。

表的分布被返回为一个行集合，它们反映了每个Segment上存储了多少元组。例如，在一个具有四个主要Segment（dbid值从0-3）的系统中，该函数返回与以下类似的四个行：

=# SELECT get_ao_distribution('lineitem_comp');
 get_ao_distribution
---------------------
(0,7500721)
(1,7501365)
(2,7499978)
(3,7497731)
(4 rows)

游程编码支持

Greenplum数据库对列级压缩支持游程编码（RLE）。RLE数据压缩把重复的数据存储为一个单一的数据值和一个计数。例如，在有两个列date和description的表中，它包含200,000个含有值date1的项以及400,000个含有值 date2的项，对这个date域的RLE压缩会类似于 date1 200000 date2 400000。对于没有大量重复数据集合的文件来说用处不大，因为它会大幅度增加这种文件的尺寸。

有四种级别的RLE压缩可用。这些级别逐步增加了压缩率，但是同时也会降低压缩速度。

Greenplum数据库4.2.1及其后的版本支持面向列的RLE压缩。如果要备份一个用了RLE压缩的表用来恢复到一个早期版本的Greenplum数据库中，应在开始备份操作前修改该表为不采用压缩或者采用早期版本中支持的压缩类型（ZLIB或者QUICKLZ）。

Greenplum数据库为BIGINT、INTEGER、DATE、 TIME或者TIMESTAMP列中的数据的RLE压缩结合了增量压缩。该增量压缩算法基于连续列值之间的变化来进行压缩，它的设计目的是在以排序顺序装载数据时或者对已排序数据应用压缩时改进压缩性能。

增加列级压缩

用户可以为列存追加优化表的列增加下列存储指令：

压缩类型
压缩级别
列的块尺寸

使用CREATE TABLE、ALTER TABLE以及CREATE TYPE命令增加存储指令。

下面的表格详细介绍了存储指令的类型以及每一个指令可能的值。

表 3. 列级压缩的存储指令
名称	定义	值	注释
`COMPRESSTYPE`	压缩的类型。	`zlib:` 缩小算法 `quicklz`: 快速压缩 `RLE_TYPE`: 游程编码 `none`: 无压缩	值不区分大小写。
`COMPRESSLEVEL`	压缩级别。	`zlib` 压缩： `1`-`9`	`1` 是最快的方法但压缩率最低。 `1`是默认值。 `9`是最慢的方法但压缩率最高。
		`QuickLZ`压缩： `1` – 使用压缩	`1`是默认值。
		`RLE_TYPE`压缩：`1` – `4` `1` - 只应用RLE `2` - 应用RLE然后应用zlib压缩级别1 `3` - 应用RLE然后应用zlib压缩级别5 `4` - 应用RLE然后应用zlib压缩级别9	`1`是最快的方法但压缩率最低。 `4`是最慢的方法但压缩率最高。`1`是默认值。
`BLOCKSIZE`	表中每一块的以字节计的尺寸	`8192 – 2097152`	该值必须是8192的倍数。

下面是增加存储指令的格式。

[ ENCODING ( storage_directive [,…] ) ]

其中单词ENCODING是必需的并且存储指令有三个部分：

指令的名称
一个等号
参数

多个存储指令用逗号分隔。如下面的CREATE TABLE子句所示，可以把一个存储指令应用到单一列或者把它作为所有列的默认指令。

一般用法：

column_name data_type ENCODING ( storage_directive [, … ] ), …

COLUMN column_name ENCODING ( storage_directive [, … ] ), …

DEFAULT COLUMN ENCODING ( storage_directive [, … ] )

示例：

C1 char ENCODING (compresstype=quicklz, blocksize=65536)

COLUMN C1 ENCODING (compresstype=zlib, compresslevel=6, blocksize=65536)

DEFAULT COLUMN ENCODING (compresstype=quicklz)

默认压缩值

如果没有定义压缩类型、压缩级别和块尺寸，默认是无压缩并且块尺寸被设置为服务器配置参数 block_size。

压缩设置的优先级

列压缩设置从表级继承到分区级，再到子分区级。最低级别上的设置优先。

在表级别指定的列压缩设置覆盖整个表的任何压缩设置。
为分区指定的列压缩设置覆盖在列或者表级指定的任何压缩设置。
为子分区指定的列压缩设置覆盖在分区、列或者表级别指定的任何压缩设置。
当一个ENCODING子句与一个 WITH子句冲突时，ENCODING子句比WITH子句具有更高的优先级。

注意：在一个含有存储指令或者列引用存储指令的表中不允许INHERITS子句。

使用LIKE子句创建的表忽略存储指令以及列引用存储指令。

列压缩设置的最佳位置、

最佳做法是在数据所在的层次上设置列压缩设置。请参考例 5，它展示了一个分区深度为2的表。RLE_TYPE压缩在子分区层次上被增加到一个列。

存储指令示例

下面的例子展示了在CREATE TABLE语句中存储指令的使用。

例 1

在这个例子中，列c1被使用zlib压缩并且使用系统中定义的块尺寸。列c2用quicklz压缩并且使用的块尺寸为65536。列c3没有被压缩并且使用系统中定义的块尺寸。

CREATE TABLE T1 (c1 int ENCODING (compresstype=zlib),
                  c2 char ENCODING (compresstype=quicklz, blocksize=65536),
                  c3 char     WITH (appendonly=true, orientation=column);

例 2

在这个例子中，列c1使用zlib压缩并且使用系统中定义的块尺寸。列c2使用quicklz压缩，并且使用的块尺寸为65536。列c3使用RLE_TYPE压缩并且使用系统中定义的块尺寸。

CREATE TABLE T2 (c1 int ENCODING (compresstype=zlib),
                  c2 char ENCODING (compresstype=quicklz, blocksize=65536),
                  c3 char,
                  COLUMN c3 ENCODING (compresstype=RLE_TYPE)
                  )
    WITH (appendonly=true, orientation=column)

例 3

在这个例子中，列c1使用zlib压缩并且使用系统中定义的块尺寸。列c2使用quicklz压缩，并且使用的块尺寸为65536。列c3使用zlib压缩并且使用系统中定义的块尺寸。注意，列c3在分区中使用zlib（而不是RLE_TYPE），因为在分区子句中的列存储比表的列定义中的存储指令优先级高。

CREATE TABLE T3 (c1 int ENCODING (compresstype=zlib),
                  c2 char ENCODING (compresstype=quicklz, blocksize=65536),
                  c3 char, COLUMN c3 ENCODING (compresstype=RLE_TYPE) )
    WITH (appendonly=true, orientation=column)
    PARTITION BY RANGE (c3) (START ('1900-01-01'::DATE)          
                             END ('2100-12-31'::DATE),
                             COLUMN c3 ENCODING (zlib));

例 4

在这个例子中，CREATE TABLE把zlib压缩类型存储指令分配给c1。列c2没有存储指令并且从DEFAULT COLUMN ENCODING子句继承了压缩类型（quicklz）和块尺寸（65536）。

Column c3的ENCODING子句定义了它的压缩类型RLE_TYPE。DEFAULT COLUMN ENCODING子句定义了c3的块尺寸为65536。

为一个特定列定义的ENCODING会覆盖DEFAULT ENCODING子句，因此列c4的压缩类型是none并且使用默认的块尺寸。

CREATE TABLE T4 (c1 int ENCODING (compresstype=zlib),
                  c2 char,
                  c4 smallint ENCODING (compresstype=none),
                  DEFAULT COLUMN ENCODING (compresstype=quicklz,
                                             blocksize=65536),
                  COLUMN c3 ENCODING (compresstype=RLE_TYPE)
                  ) 
   WITH (appendonly=true, orientation=column);

例 5

这个例子创建一个追加优化的列存表T5。T5有两个分区p1和p2，每一个都有子分区。每一个子分区都有ENCODING子句：

分区p1的子分区sp1的ENCODING子句定义了列 i的压缩类型是zlib并且块尺寸是65536。

分区p2的子分区sp1的ENCODING子句定义了列 i的压缩类型是rle_type并且块尺寸为默认值。列k使用默认压缩并且其块尺寸为8192。

CREATE TABLE T5(i int, j int, k int, l int) 
    WITH (appendonly=true, orientation=column)
    PARTITION BY range(i) SUBPARTITION BY range(j)
    (
       p1 start(1) end(2)
       ( subpartition sp1 start(1) end(2) 
         column i encoding(compresstype=zlib, blocksize=65536)
       ), 
       partition p2 start(2) end(3)
       ( subpartition sp1 start(1) end(2)
           column i encoding(compresstype=rle_type)
           column k encoding(blocksize=8192)
       )
    );

用ALTER TABLE命令把一个压缩列增加到现有表的例子，请见为表增加一个压缩列。

在TYPE命令中增加压缩

用户可以定义一种压缩类型来简化列压缩语句。例如，下面的CREATE TYPE命令定义了一种压缩类型comptype，它指定quicklz压缩。

其中comptype被定义为：

CREATE TYPE comptype (
   internallength = 4,
   input = comptype_in,
   output = comptype_out,
   alignment = int4,
   default = 123,
   passedbyvalue,
   compresstype="quicklz",
   blocksize=65536,
   compresslevel=1
   );

然后用户可以在一个CREATE TABLE命令中使用comptype为一个列指定quicklz压缩：

CREATE TABLE t2 (c1 comptype)
    WITH (APPENDONLY=true, ORIENTATION=column);

关于创建类型和对类型增加压缩参数的信息，请见CREATE TYPE。关于在类型中更改压缩说明的信息，请见ALTER TYPE。

选择块尺寸

blocksize是一个表中每一块的尺寸，以字节计。块尺寸必须介于8192字节和2097152字节之间，并且必须是8192的倍数。默认是32768。

指定大的块尺寸可能会消耗大量的内存。快尺寸决定着存储层中的缓冲。Greenplum为每个分区以及列存表中的每个列维护一个缓冲区。具有很多分区或者列的表会消耗大量的内存。

修改一个表

ALTER TABLE 命令改变一个表的定义。使用ALTER TABLE可以更改表的属性，例如列定义、分布策略、存储模型以及分区结构（另见维护分区表）。例如，为一个表列增加一个非空约束：

=> ALTER TABLE address ALTER COLUMN street SET NOT NULL;

修改表的分布

ALTER TABLE提供了选项来改变一个表的分布策略。当表分布选项改变时，表数据会被在磁盘上重新分布，这可能会造成资源紧张。用户也可以使用现有的分布策略重新分布表数据。

更改分布策略

对于已分区的表，对于分布策略的更改会递归地应用到子分区上。这种操作会保留拥有关系和该表的所有其他属性。例如，下列命令使用customer_id列作为分布键在所有Segment之间重新分布表sales：

ALTER TABLE sales SET DISTRIBUTED BY (customer_id);

在用户改变一个表的哈希分布时，表数据会被自动重新分布。把分布策略改成随机分布不会导致数据被重新分布。例如，下面的ALTER TABLE命令不会立刻产生效果：

ALTER TABLE sales SET DISTRIBUTED RANDOMLY;

重新分布表的数据

要用一种随机分布策略（或者当哈希分布策略没有被更改时）对表重新分布数据，可使用REORGANIZE=TRUE。重新组织数据对于更正一个数据倾斜问题是必要的，当系统中增加了Segment资源后也需要重新组织数据。例如，下面的命令会使用当前的分布策略（包括随机分布）在所有Segment上重新分布数据。

ALTER TABLE sales SET WITH (REORGANIZE=TRUE);

修改表的存储模型

表存储、压缩和存储方向只能在创建时声明。要改变存储模型，用户必须用正确的存储选项创建一个表，再把原始表的数据载入到新表中，接着删除原始表并且把新表重命名为原始表的名称。用户还必须重新授权原始表上有的权限。例如：

CREATE TABLE sales2 (LIKE sales) 
WITH (appendonly=true, compresstype=quicklz, 
      compresslevel=1, orientation=column);
INSERT INTO sales2 SELECT * FROM sales;
DROP TABLE sales;
ALTER TABLE sales2 RENAME TO sales;
GRANT ALL PRIVILEGES ON sales TO admin;
GRANT SELECT ON sales TO guest;

可参考分裂一个分区来学习如何该表一个已分区表的存储模型。

为表增加一个压缩列

使用ALTER TABLE命令为一个表增加一个压缩列。所有在增加列级压缩中描述的用于压缩列的选项和约束都适用于用ALTER TABLE命令增加的列。

下面的例子展示了如何向一个表中增加一个使用zlib压缩的列T1。

ALTER TABLE T1
      ADD COLUMN c4 int DEFAULT 0
      ENCODING (COMPRESSTYPE=zlib);

压缩设置的继承

如果一个表有子分区且子分区带有压缩设置，则向该表增加的一个分区会从子分区继承那些压缩设置。下面的例子展示了如何创建一个有子分区编码的表，然后修改它来增加一个分区。

CREATE TABLE ccddl (i int, j int, k int, l int)
  WITH
    (APPENDONLY = TRUE, ORIENTATION=COLUMN)
  PARTITION BY range(j)
  SUBPARTITION BY list (k)
  SUBPARTITION template(
    SUBPARTITION sp1 values(1, 2, 3, 4, 5),
    COLUMN i ENCODING(COMPRESSTYPE=ZLIB),
    COLUMN j ENCODING(COMPRESSTYPE=QUICKLZ),
    COLUMN k ENCODING(COMPRESSTYPE=ZLIB),
    COLUMN l ENCODING(COMPRESSTYPE=ZLIB))
  (PARTITION p1 START(1) END(10),
   PARTITION p2 START(10) END(20))
;
ALTER TABLE ccddl
  ADD PARTITION p3 START(20) END(30)
;

运行ALTER TABLE命令会创建表ccddl的名为ccddl_1_prt_p3和 ccddl_1_prt_p3_2_prt_sp1的分区。分区ccddl_1_prt_p3继承了子分区sp1的不同的压缩编码。

删除一个表

DROP TABLE 命令把表从数据库中移除。例如：

DROP TABLE mytable;

要清空一个表的行但不移除该表的定义，可使用DELETE 或者TRUNCATE。例如：

DELETE FROM mytable;
TRUNCATE mytable;

DROP TABLE 总是会移除目标表上存在的任何索引、规则、触发器和约束。删除一个被视图引用的表应指定CASCADE 。CASCADE会移除依赖表的视图。