文档首页> 数据仓库服务 GaussDB(DWS)> 8.2.0> 定义数据库对象> 创建和管理表
更新时间：2023-04-19 GMT+08:00
查看PDF

创建和管理表

创建表

CREATE TABLE命令创建一个表，创建表时可以定义以下内容：

示例：CREATE TABLE创建了一个表web_returns_p1，使用wr_item_sk作为分布键，并基于wr_returned_date_sk设置了range分布功能。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
CREATE TABLE web_returns_p1
(
    wr_returned_date_sk       integer,
    wr_returned_time_sk       integer,
    wr_item_sk                integer NOT NULL,
    wr_refunded_customer_sk   integer
)
WITH (orientation = column)
DISTRIBUTE BY HASH (wr_item_sk)
PARTITION BY RANGE(wr_returned_date_sk)
(
    PARTITION p2019 START(20191231) END(20221231) EVERY(10000),
    PARTITION p0 END(maxvalue)
);

表约束的定义

可以在列和表上定义约束来限制表中的数据，但是有以下一些限制：

  1. 表中的主键约束和唯一约束必须包含分布列。
  2. 列存表支持PARTIAL CLUSTER KEY、主键和唯一表级约束，不支持外键表级约束。
  3. 列存表的字段约束只支持NULL、NOT NULL和DEFAULT常量值。
  • 检查约束

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

    1
2
3
4
5
6
    		 
    CREATE TABLE products
(
   product_no integer, 
   name text, 
   price numeric CHECK (price > 0)
);
  • 非空约束

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

    1
2
3
4
5
6
    		 
    CREATE TABLE products 
( 
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric 
);
  • 唯一约束

    唯一约束确保一列或者一组列中包含的数据对于表中所有的行都是唯一的。 如果没有声明DISTRIBUTE BY REPLICATION，则唯一约束的列集合中必须包含分布列。

    1
2
3
4
5
6
    		 
    CREATE TABLE products 
(
    product_no integer UNIQUE, 
    name text, 
    price numeric
)DISTRIBUTE BY HASH(product_no);
  • 主键

    主键约束是一个UNIQUE约束和一个NOT NULL约束的组合。 如果没有声明DISTRIBUTE BY REPLICATION，则主键约束的列集合中必须包含分布列。如果一个表具有主键，这个列（或者这一组列）会被默认选中为该表的分布键。

    例如：

    1
2
3
4
5
6
    		 
    CREATE TABLE products 
( 
    product_no integer PRIMARY KEY, 
    name text, 
    price numeric
)DISTRIBUTE BY HASH(product_no);
  • 局部聚簇
    局部聚簇通过min/max稀疏索引较快的实现基表扫描的filter过滤。Partial Cluster Key可以指定多列，但是一般不建议超过2列。例如： 
    1
2
3
4
5
6
7
    		 
    CREATE TABLE products 
(
    product_no integer, 
    name text, 
    price numeric,
    PARTIAL CLUSTER KEY(product_no)
) WITH (ORIENTATION = COLUMN);

表分布的定义

  • GaussDB(DWS)支持的分布方式：复制表（Replication）、哈希表（Hash）和轮询表（Roundrobin）。

    轮询表（Roundrobin）分布方式仅8.1.2及以上集群版支持。

    策略

    描述

    适用场景

    优势与劣势

    复制表（Replication）

    集群中每一个DN实例上都有一份全量表数据。

    小表、维度表。
    • Replication优点是每个DN上都有此表的全量数据，在join操作中可以避免数据重分布操作，从而减小网络开销，同时减少了plan segment(每个plan segment都会起对应的线程)
    • Replication缺点是每个DN都保留了表的完整数据，造成数据的冗余。一般情况下只有较小的维度表才会定义为Replication表。

    哈希表（Hash）

    表数据通过hash方式散列到集群中的所有DN实例上。

    数据量较大的事实表。
    • 在读/写数据时可以利用各个节点的IO资源，大大提升表的读/写速度。
    • 一般情况下大表（1000000条记录以上）定义为Hash表。

    轮询表（Roundrobin）

    表的每一行被轮番地发送给各个DN，数据会被均匀地分布在各个DN中。

    数据量较大的事实表，且使用Hash分布时找不到合适的分布列。
    • Roundrobin优点是保证了数据不会发生倾斜，从而提高了集群的空间利用率。
    • Roundrobin缺点是无法像Hash表一样进行DN本地化优化，查询性能通常不如Hash表。
    • 一般在大表无法找到合适的分布列时，定义为Roundrobin表，若大表能够找到合适的分布列，优先选择性能更好的Hash分布。
  • 选择分布列

    采用Hash分布方式，需要为用户表指定一个分布列（distribute key）。当插入一条记录时，系统会根据分布列的值进行hash运算后，将数据存储在对应的DN中。

    所以Hash分布列选取至关重要，需要满足以下原则：

    1. 列值应比较离散，以便数据能够均匀分布到各个DN。例如，考虑选择表的主键为分布列，如在人员信息表中选择身份证号码为分布列。
    2. 在满足第一条原则的情况下尽量不要选取存在常量filter的列。例如，表dwcjk相关的部分查询中出现dwcjk的列zqdh存在常量的约束(例如zqdh=’000001’)，那么就应当尽量不用zqdh做分布列。
    3. 在满足前两条原则的情况，考虑选择查询中的连接条件为分布列，以便Join任务能够下推到DN中执行，且减少DN之间的通信数据量。

      对于Hash分表策略，如果分布列选择不当，可能导致数据倾斜，查询时出现部分DN的I/O短板，从而影响整体查询性能。因此在采用Hash分表策略之后需对表的数据进行数据倾斜性检查，以确保数据在各个DN上是均匀分布的。可以使用以下SQL检查数据倾斜性

      1
2
3
4
5
      		 
      select 
xc_node_id, count(1) 
from tablename 
group by xc_node_id 
order by xc_node_id desc;

      其中xc_node_id对应DN，一般来说，不同DN的数据量相差5%以上即可视为倾斜，如果相差10%以上就必须要调整分布列。

    4. 一般不建议用户新增一列专门用作分布列，尤其不建议用户新增一列，然后用SEQUENCE的值来填充做为分布列。因为SEQUENCE可能会带来性能瓶颈和不必要的维护成本。

查看表数据

  • 使用系统表pg_tables查询数据库所有表的信息。 
    1
    		 
    SELECT * FROM pg_tables;
  • 使用gsql的\d+命令查询表的属性。 
    1
    		 
    \d+ customer_t1;
  • 执行如下命令查询表customer_t1的数据量。 
    1
    		 
    SELECT count(*) FROM customer_t1;
  • 执行如下命令查询表customer_t1的所有数据。 
    1
    		 
    SELECT * FROM customer_t1;
  • 执行如下命令只查询字段c_customer_sk的数据。 
    1
    		 
    SELECT c_customer_sk FROM customer_t1;
  • 执行如下命令过滤字段c_customer_sk的重复数据。 
    1
    		 
    SELECT DISTINCT( c_customer_sk ) FROM customer_t1;
  • 执行如下命令查询字段c_customer_sk为3869的所有数据。 
    1
    		 
    SELECT * FROM customer_t1 WHERE c_customer_sk = 3869;
  • 执行如下命令按照字段c_customer_sk进行排序。 
    1
    		 
    SELECT * FROM customer_t1 ORDER BY c_customer_sk;

删除表数据

在使用表的过程中，可能会需要删除已过期的数据，删除数据必须从表中整行的删除。

SQL不能直接访问独立的行，只能通过声明被删除行匹配的条件进行。如果表中有一个主键，用户可以指定准确的行。用户可以删除匹配条件的一组行或者一次删除表中的所有行。

  • 使用DELETE命令删除行，如果删除表customer_t1中所有c_customer_sk为3869的记录： 
    1
    		 
    DELETE FROM customer_t1 WHERE c_customer_sk = 3869;
  • 如果执行如下命令，会删除表中所有的行。 
    1
    		 
    DELETE FROM customer_t1;

    1
    		 
    TRUNCATE TABLE customer_t1;

全表删除的场景下，建议使用truncate，不建议使用delete。

  • 删除创建的表。 
    1
    		 
    DROP TABLE customer_t1;
父主题： 定义数据库对象