创建和管理表

创建表¶

SeaboxMPP数据库的表与任何一种关系型数据库中的表类似，不过其表中的行被分布在系统中的不同executor上。

当用户创建一个表时，用户会指定该表的分布策略。

CREATE TABLE命令创建一个表并且定义它的结果。当用户创建一个表时，用户需要定义：

该表的列以及它们的数据类型。参见选择列的数据类型。
任何用于限制列或者表中能包含的数据的表或者列约束。参见设置表和列约束。
表的分布策略，这决定了SeaboxMPP数据库如何在executor之间划分数据。
表存储在磁盘上的方式。参见选择表存储模型。
大型表的表分区策略。参见创建和管理数据库。

选择列的数据类型¶

一个列的数据类型决定了该列能包含的数据的类型。选择的数据类型应使用最少的空间，但仍能容纳用户的数据并且能最好地约束数据。

例如，对字符串使用character数据类型，对于日期使用date或者timestamp数据类型，而对数字使用numeric数据类型。

对于包含文本数据的表列，应指定数据类型为VARCHAR或者TEXT。不推荐指定数据类型为CHAR。

在SeaboxMPP数据库中数据类型VARCHAR或者TEXT会把加在数据后面的边距（在最后一个非空白字符后面增加的空白字符）处理为有效字符，而数据类型CHAR不会这样做。

关于character数据类型的信息，请见SeaboxMPP数据库参考指南中的CREATE TABLE命令。

使用能容纳用户的数字型数据的且允许未来扩张的最小数字数据类型。例如，为适合INT或SMALLINT的数据使用BIGINT会浪费存储空间。

如果用户预期用户的数据值将会随着时间扩张，应该考虑到在导入大量数据后从较小的数据类型更改成较大的数据类型需要很大的代价。例如，如果用户当期的数据值适合SMALLINT，但是很可能值会扩张，这样INT就是更好的长期选择。

对用户计划要用在交叉表连接中的列使用相同的数据类型。交叉表连接通常使用一个表中的主键和其他表中的外键。当数据类型不同时，数据库必须转换其中之一以便数据值能被正确地比较，这会增加不必要的开销。

SeaboxMPP数据库为用户提供了丰富的本地数据类型集合。有关内建数据类型的信息请见SeaboxMPP数据库参考指南。

设置表和列约束¶

用户可以在列和表上定义约束来限制表中的数据。 SeaboxMPP数据库支持和SeaboxSQL相同的约束，但是有一些限制，包括：

CHECK约束只能引用它所在的表。
UNIQUE和PRIMARY KEY约束必须和它们所在表的分布键和分区键（如果有）兼容。
允许FOREIGN KEY约束，但不会被强制。
用户在分区表上定义的约束将作为整体应用到分区表上。用户不能在该表的单独的部分上定义约束。

检查约束¶

检查约束允许用户指定一个特定列中的值必须满足一个布尔（真值）表达式。例如，要求正的产品价格：

=> CREATE TABLE products 
            ( product_no integer, 
              name text, 
              price numeric CHECK (price > 0) );

非空约束¶

非空约束指定一个列不能有空值。非空约束总是被写作为列约束。例如：

=> CREATE TABLE products 
       ( product_no integer NOT NULL,
         name text NOT NULL,
         price numeric );

唯一约束¶

唯一约束确保一列或者一组列中包含的数据对于表中所有的行都是唯一的。该表必须是哈希分布或复制表（不可以是DISTRIBUTED RANDOMLY）。如果表是哈希分布的，约束列必须是该表的分布键列（或者是一个超集）。例如：

=> CREATE TABLE products 
       ( product_no integer UNIQUE, 
         name text, 
         price numeric)
      DISTRIBUTED BY (product_no);

主键¶

主键约束是一个UNIQUE约束和一个NOT NULL约束的组合。该表必须是哈希分布（非DISTRIBUTED RANDOMLY）的，并且约束列必须是该表的分布键列（或者是一个超集）。

如果一个表具有主键，这个列（或者这一组列）会被默认选中为该表的分布键。例如：

=> CREATE TABLE products 
       ( product_no integer PRIMARY KEY, 
         name text, 
         price numeric)
      DISTRIBUTED BY (product_no);

外键¶

不支持外键。用户可以声明它们，但是参照完整性不会被实施。

外键约束指定一列或者一组列中的值必须匹配出现在另一个表的某行中的值，以此来维护两个相关表之间的参照完整性。参照完整性检查不能在一个SeaboxMPP数据库的分布表段之间实施。

选择表分布策略¶

所有的SeaboxMPP数据库表都会被分布。当用户创建或者修改一个表时，用户可以有选择地指定DISTRIBUTED BY（哈希分布），DISTRIBUTED RANDOMLY（随机分布），或DISTRIBUTED REPLICATED（全分布）来决定该表的行分布。

注意: 如果创建表时没有指定DISTRIBUTED BY，SeaboxMPP数据库服务器配置参数sc_create_table_random_default_distribution控制表的分布策略。

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

在决定表分布策略时，请考虑以下几点：

均匀数据分布 为了最好的性能，所有的executor应该包含等量的数据。如果数据不平衡或者倾斜，具有更多数据的executor就必须做更多工作来执行它那一部分的查询处理。请选择对于每一个记录都唯一的分布键，例如主键。
本地和分布式操作 — 本地操作比分布式操作更快。在executor层面上，如果与连接、排序或者聚集操作相关的工作在本地完成，查询处理是最快的。在系统层面完成的工作要求在executor之间分布元组，其效率会低些。当表共享一个共同的分布键时，在它们共享的分布键列上的连接或者排序工作会在本地完成。对于随机分布策略来说，本地连接操作就行不通了。
均匀查询处理 — 为了最好的性能，所有的executor应该处理等量的查询负载。如果一个表的数据分布策略与查询谓词匹配不好，查询负载可能会倾斜。例如，假定一个销售事务表按照客户ID列（分布键）分布。如果查询中的谓词引用了一个单一的客户ID，该查询处理工作会被集中在一个executor上。

复制表分布策略(DISTRIBUTED REPLICATED)应该在小表上使用。将大表数据复制到每个节点上无论在存储还是维护上都是有代价的。

复制表最基本的用例是：

删除用户定义的函数可以对节点执行的操作的限制
频繁使用的表不需要广播到所有节点可以提高查询性能。

注意: 隐藏的系统列（rowid，ctid，cmin，cmax，xmin，xmax和sc_dbindex_id）无法在复制表的用户查询中引用，因为它们没有单一的，明确的值。SeaboxMPP数据库为查询返回一个不存在的列错误。

声明分布键¶

CREATE TABLE命令的可选子句DISTRIBUTED BY, DISTRIBUTED RANDOMLY和DISTRIBUTED REPLICATED决定了表的分布策略。默认的哈希分布策略使用PRIMARY KEY（如果有的话）或表的第一列作为分布键。几何信息列或用户自定义数据类型的列是不能作为SeaboxMPP数据库分布列的。如果找不到合适的哈希分布的列，SeaboxMPP数据库就选择随机分布策略。

复制表没有分布列，因为每行都分布在SeaboxMPP数据库所有节点上。

为了保证哈希分布数据的均匀分布，最好选一个唯一键作为分布列。如果找不到，则选择DISTRIBUTED RANDOMLY。例如：

=> CREATE TABLE products
                        (name varchar(40),
                         prod_id integer,
                         supplier_id integer)
             DISTRIBUTED BY (prod_id);

=> CREATE TABLE random_stuff
                        (things text,
                         doodads text,
                         etc text)
             DISTRIBUTED RANDOMLY;

重要: 如果存在主键，则它是表的默认分布键。如果不存在主键，但是存在唯一键，则这是表的默认分布键。

自定义分布键哈希函数¶

用于哈希分布策略的哈希函数由列的数据类型的哈希运算符类定义。由于默认的SeaboxMPP数据库使用数据类型的默认哈希运算符类，因此用于哈希连接和哈希聚合的运算符类相同，适用于大多数用例。但是，您可以在DISTRIBUTED BY子句中声明非默认的哈希运算符类。

使用自定义哈希运算符类可以用于支持与默认相等运算符（=）不同的运算符上的共存连接。

自定义哈希操作符类示例

此示例为整数数据类型创建自定义哈希运算符类，该类用于提高查询性能。运算符类比较整数的绝对值。

创建一个函数和一个等于运算符，如果两个整数的绝对值相等，则返回true。

CREATE FUNCTION abseq(int, int) RETURNS BOOL AS
$$
  begin return abs($1) = abs($2); end;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

CREATE OPERATOR |=| (
  PROCEDURE = abseq,
  LEFTARG = int,
  RIGHTARG = int,
  COMMUTATOR = |=|,
  hashes, merges);

现在，创建一个使用运算符的哈希函数和运算符类。

CREATE FUNCTION abshashfunc(int) RETURNS int AS
$$
  begin return hashint4(abs($1)); end;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

CREATE OPERATOR CLASS abs_int_hash_ops FOR TYPE int4
  USING hash AS
  OPERATOR 1 |=|,
  FUNCTION 1 abshashfunc(int);

并且，为它们创建小于和大于运算符和B树运算符类。我们的查询不需要它们，但是SeaboxMPP数据库的Seabox查询优化器必须依赖它们做连接的co-location。

CREATE FUNCTION abslt(int, int) RETURNS BOOL AS
$$
  begin return abs($1) < abs($2); end;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

CREATE OPERATOR |<| (
  PROCEDURE = abslt,
  LEFTARG = int,
  RIGHTARG = int);

CREATE FUNCTION absgt(int, int) RETURNS BOOL AS
$$
  begin return abs($1) > abs($2); end;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

CREATE OPERATOR |>| (
  PROCEDURE = absgt,
  LEFTARG = int,
  RIGHTARG = int);

CREATE FUNCTION abscmp(int, int) RETURNS int AS
$$
  begin return btint4cmp(abs($1),abs($2)); end;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

CREATE OPERATOR CLASS abs_int_btree_ops FOR TYPE int4
  USING btree AS
  OPERATOR 1 |<|,
  OPERATOR 3 |=|,
  OPERATOR 5 |>|,
  FUNCTION 1 abscmp(int, int);

现在，您可以在表中使用自定义哈希运算符类。

CREATE TABLE atab (a int) DISTRIBUTED BY (a abs_int_hash_ops);
CREATE TABLE btab (b int) DISTRIBUTED BY (b abs_int_hash_ops);

INSERT INTO atab VALUES (-1), (0), (1);
INSERT INTO btab VALUES (-1), (0), (1), (2);

执行使用自定义相等运算符|=|的连接的查询可以利用co-location。使用默认的整数opclass，此查询将需要Redistribute Motion节点，但使用自定义opclass，可以实现更高效的计划。

EXPLAIN (COSTS OFF) SELECT a, b FROM atab, btab WHERE a = b;
                            QUERY PLAN                            
------------------------------------------------------------------
 Gather Motion 3:1  (slice3; executors: 3)
   ->  Hash Join
         Hash Cond: (btab.b = atab.a)
         ->  Redistribute Motion 3:3  (slice1; executors: 3)
               Hash Key: btab.b
               ->  Seq Scan on btab
         ->  Hash
               ->  Redistribute Motion 3:3  (slice2; executors: 3)
                     Hash Key: atab.a
                     ->  Seq Scan on atab
 Optimizer: Seabox query optimizer
(11 rows)