表管理—存储模型

表管理—存储模型

SeaboxMPP数据库支持多种存储模型和一种混合存储模型。 当用户创建一个表时，用户会选择如何存储它的数据。

这个主题解释了表存储的选项以及如何为用户的负载选择最好的存储模型。

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

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

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

行存储（堆存储）

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

创建一个行存表

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

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

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

SeaboxMPP提供面向存储的模型选择：行，列或两者的组合。 本主题提供了为表选择最佳存储的一般准则。

使用实际数据和查询工作负载评估性能。

• 面向行的存储：适用于具有许多迭代事务的OLTP类型的工作负载以及一次需要多列的单行，因此检索是高效的。

• 面向列的存储：适合于在少量列上计算数据聚集的数据仓库负载，或者是用于需要对单列定期更新但不修改其他列数据的情况。

对于大部分常用目的或者混合负载，面向行的存储提供了灵活性和性能的最佳组合。 不过，也有场景中面向列的存储模型提供了更高效的I/O和存储。

在为一个表决定存储方向模型时，请考虑下列需求：

• 表数据的更新 如果用户会频繁地导入和更新表数据，请选择一个面向行的堆表。面向列的表存储只能用于列存表。

• 频繁的插入 如果频繁地向表中插入行，请考虑面向行的模型。列存表并未对写操作优化，因为一行的列值必须被写到磁盘上的不同位置。

• 查询中要求的列数如果在查询的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 (orientation=column)
    DISTRIBUTED BY (a);

使用压缩（只适用列存表）

对于列存表，在SeaboxMPP数据库中有两种类型的库内压缩可用：

• 应用于一整个表的表级压缩。

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

下面的表总结了可用的压缩算法。

表. 用于列存表的压缩算法

表方向	可用的压缩类型	支持的算法
列	列和表	RLE, LZ4, ZSTD, Gorilla, T64, Multiple 和 AUTO1

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

• CPU使用。用户的executor系统必须具有可用的CPU能力来压缩和解压数据。

• 压缩率/磁盘尺寸。最小化磁盘尺寸是一个因素，但也要考虑压缩和扫描数据所需的时间和CPU计算能力。要找到能高效压缩数据但不导致过长压缩时间或者过慢扫描率的最优设置。

• 压缩的速度。

算法	说明
LZ4	压缩算法，优势在于压缩、解压速度都很快
RLE	压缩算法，对重复数据有较高的压缩效果
ZSTD	压缩算法，由Facebook开源，具有良好的压缩比且支持压缩等级
Gorilla	压缩算法，偏好时序数据
T64	压缩算法，针对有序数字类型的压缩算法
Multiple	T64+ZSTD的压缩算法组合，针对时序数据有天然的高压缩比优势

• 解压速度/扫描率。压缩的列存表的性能取决于硬件、查询调优设置和其他因素。请执行对比测试来判断在用户的环境中的真实性能。

  注意: 不要在使用压缩的文件系统上创建压缩的列存表。如果用户的executor数据目录所在的文件系统是一个压缩的文件系统，用户的列存表不能使用压缩。

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

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

创建压缩表示例：

CREATE TABLE命令的WITH子句声明表的存储选项。 使用压缩技术的表必须是列存表。

例如，要创建一个使用zstd压缩的列存表：

=> CREATE TABLE foo (a int, b text) 
   WITH (orientation=column, compresstype=zstd);

检查列存表的分布

SeaboxMPP提供了内建函数来检查一个列存表分布情况。这些函数要求对象ID或者表名作为参数。用户可以用schema名来限定表名。

• get_ao_distribution()

返回类型

(dbid, tuplecount)行的集合

描述

展示一个列存表的行在阵列中的分布情况。 返回一个行集合，其中每一个行包括了一个节点dbid以及存储在其中的元组数。

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

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

RLE支持

SeaboxMPP数据库对列级压缩支持游程编码（RLE）。 RLE数据压缩把重复的数据存储为一个单一的数据值和一个计数。

例如，在有两个列date和description的表中，它包含200,000个含有值date1的项以及400,000个含有值date2的项，对这个date域的RLE压缩会类似于date1 200000 date2 400000。

RLE对于没有大量重复数据集合的文件来说用处不大，因为它会大幅度增加这种文件的尺寸。

SeaboxMPP数据库支持面向列存表的RLE压缩。

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

增加列级压缩

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

• 压缩类型

• 压缩级别

• 列的块尺寸

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

下文详细介绍了列级压缩存储指令的类型以及每一个指令可能的值。

compresstype:压缩的类型

zstd:

ZStandard算法

lz4:

lz4算法

Gorilla:

Gorilla时间序列压缩算法

rle:

游程编码

T64:

高速(Turbo)64算法

Multiple:

T64+zstd

none:

无压缩

值不区分大小写。

compresslevel:压缩级别

zstd压缩

1-19.1是最快的方法但压缩率最低。1是默认值。19是最慢的方法但压缩率最高。

Multiple压缩

同zstd，其压缩率设置也是设置的Multiple中zstd算法的压缩率

lz4压缩

1 – 默认值，使用压缩。

Gorilla压缩

1 – 默认值，使用压缩。

rle压缩

1 – 默认值，使用压缩。

T64压缩

1 – 默认值，使用压缩。

blockrownum：表中每一块的最大行数

16 – 261120

默认值32768。

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

[ 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=lz4, blockrownum=65536) 

COLUMN C1 ENCODING (compresstype=Gorilla, compresslevel=6, blockrownum=65536)

DEFAULT COLUMN ENCODING (compresstype=lz4)

默认压缩值

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

压缩设置的优先级

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

• 表级定义的列压缩设置会覆盖该类型的任何压缩设置。

• 表级指定的列压缩设置会覆盖整个表的任何压缩设置。

• 为分区指定的列压缩设置会覆盖列级或表级别的任何压缩设置。

• 为子分区指定的列压缩设置会覆盖分区，列或表级别的任何压缩设置。

• 当ENCODING子句与WITH子句冲突时，ENCODING子句的优先级高于WITH子句。

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

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

列压缩设置的最佳位置

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

存储指令示例

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

例 1

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

CREATE TABLE T1 (c1 int ENCODING (compresstype=zstd),
                  c2 char ENCODING (compresstype=lz4, blockrownum=65536),
                  c3 char)    WITH (orientation=column);

例 2

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

CREATE TABLE T2 (c1 int ENCODING (compresstype=Gorilla),
                  c2 char ENCODING (compresstype=lz4, blockrownum=65536),
                  c3 char,
                  COLUMN c3 ENCODING (compresstype=rle)
                  )
    WITH (orientation=column);

例 3

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

注意，列c3在分区中使用Gorilla（而不是rle），因为在分区子句中的列存储比表的列定义中的存储指令优先级高。

CREATE TABLE T3 (c1 int ENCODING (compresstype=Gorilla),
                  c2 char ENCODING (compresstype=lz4, blockrownum=65536),
                  c3 text, COLUMN c3 ENCODING (compresstype=rle) )
    WITH (orientation=column)
    PARTITION BY RANGE (c3) (START ('1900-01-01'::DATE)          
                             END ('2100-12-31'::DATE),
                             COLUMN c3 ENCODING (compresstype=Gorilla));

例 4

在这个例子中，CREATE TABLE把Gorilla压缩类型存储指令分配给c1。 列c2没有存储指令并且从DEFAULT COLUMN ENCODING子句继承了压缩类型（lz4）和块尺寸（65536）。列c3的ENCODING子句定义其压缩类型rle。 为特定列定义的ENCODING子句会覆盖DEFAULT ENCODING子句，因此列c3使用默认块大小32768。列c4的压缩类型为none，并使用默认块大小。

CREATE TABLE T4 (c1 int ENCODING (compresstype=Gorilla),
                  c2 char,
                  c3 text,
                  c4 smallint ENCODING (compresstype=none),
                  DEFAULT COLUMN ENCODING (compresstype=lz4, blockrownum=65536),
                  COLUMN c3 ENCODING (compresstype=rle)
                  ) 
   WITH (orientation=column);

例 5

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

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

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

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

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

选择块尺寸

blockrownum是一个表中每一块最大行数。最大行数必须介于16和261120之间。默认是32768。

指定大的块行数可能会消耗大量的内存。 块大小决定了存储层中的缓存。 在面向列的表中，SeaboxMPP维护为每个分区每个列维护了一个缓存。具有许多分区或列的表占用大量内存。

修改表的分布

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

更改分布策略

对于已分区的表，对于分布策略的更改会递归地应用到子分区上。 这种操作会保留拥有关系和该表的所有其他属性。

例如，下列命令使用customer_id列作为分布键在所有executor之间重新分布表sales：

ALTER TABLE sales SET DISTRIBUTED BY (customer_id); 

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

ALTER TABLE sales SET DISTRIBUTED RANDOMLY;

将表的分布策略更改为DISTRIBUTED REPLICATED或从DISTRIBUTED REPLICATED修改为其他分布，会自动重新分配表数据。

重新分布表的数据

要用一种随机分布策略（或者当哈希分布策略没有被更改时）对表重新分布数据，可使用REORGANIZE=TRUE。重新组织数据对于更正一个数据倾斜问题是必要的，当系统中增加了executor资源后也需要重新组织数据。

例如，下面的命令会使用当前的分布策略（包括随机分布）在所有executor上重新分布数据。

ALTER TABLE sales SET WITH (REORGANIZE=TRUE);

将表的分布策略更改为DISTRIBUTED REPLICATED或从DISTRIBUTED REPLICATED改为其它总是重新分配表数据，即使REORGANIZE=FALSE也是如此。

修改表的存储模型

表存储、压缩和行列类型只能在创建时声明。要改变存储模型，用户必须用正确的存储选项创建一个表，再把原始表的数据载入到新表中，接着删除原始表并且把新表重命名为原始表的名称。

用户还必须重新授权原始表上有的权限。例如：

CREATE TABLE sales2 (LIKE sales) 
WITH (compresstype=lz4, 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命令增加的列。

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

ALTER TABLE T1
      ADD COLUMN c4 int DEFAULT 0
      ENCODING (compresstype=Gorilla);

删除表

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

DROP TABLE mytable;

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

DELETE FROM mytable;

TRUNCATE mytable;

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