人大金仓分析型数据库使用之创建和管理表

目录

前言

一、创建表

1、选择列的数据类型

2、设置表和列约束

3、选择表分布策略

---

前言

        数据库的表与任何一种关系型数据库中的表类似，不过其表中的行被分布在系统中的不同实例上。 当用户创建一个表时，用户会指定该表的分布策略。        

---

一、创建表

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

• 该表的列以及它们的数据类型
• 大型表的表分区策略
• 表存储在磁盘上的方式
• 表的分布策略，这决定了 数据库如何在 实例 之间划分数据
• 任何用于限制列或者表中能包含的数据的表或者列约束

1、选择列的数据类型

        一个列的数据类型决定了该列能包含的数据的类型。 选择的数据类型应使用最少的空间，但仍能容纳用户的数据并且能最好地约束数据。 例如，对字符串使用character数据类型，对于日期使用date或者timestamp数据类型，而对数字使用numeric数据类型。

        对于包含文本数据的表列，应指定数据类型为VARCHAR或者TEXT。 不推荐指定数据类型为CHAR。 在数据库中数据类型VARCHAR或者TEXT会把加在数据后面的边距（在最后一个非空白字符后面增加的空白字符）处理为有效字符，而数据类型CHAR不会这样做。

        使用能容纳用户的数字型数据的且允许未来扩张的最小数字数据类型。 例如，为适合INT或SMALLINT的数据使用BIGINT会浪费存储空间。 如果用户预期用户的数据值将会随着时间扩张，应该考虑到在装载大量数据后从较小的数据类型更改成较大的数据类型需要很大的代价。 例如，如果用户当期的数据值适合SMALLINT，但是很可能值会扩张，这样INT就是更好的长期选择。

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

2、设置表和列约束

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

• 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);

3、选择表分布策略

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

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

• 均匀数据分布均 — 为了最好的性能，所有的 实例 应该包含等量的数据。如果数据不平衡或者倾斜，具有更多数据的实例 就必须做更多工作来执行它那一部分的查询处理。 请选择对于每一个记录都唯一的分布键，例如主键
• 均匀查询处理  — 为了最好的性能，所有的 实例 应该处理等量的查询负载。 如果一个表的数据分布策略与查询谓词匹配不好，查询负载可能会倾斜。 例如，假定一个销售事务表按照客户ID 列（分布键）分布。 如果查询中的谓词引用了一个单一的客户ID ，该查询处理工作会被集中在一个 实例上

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

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

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

         为了保证哈希分布数据的均匀分布，最好选一个唯一键作为分布列。 如果找不 到，则选择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;

        如果没有主键，但是有唯一索引存在，则选择它为分布键。