GaussDB for DWS&TD分区表设计原理和使用对比-伙伴云

GaussDB for DWS&TD分区表设计原理和使用对比

网友投稿 1573 2022-05-29

第一章：GaussDB for DWS分区介绍

一、分区与分区表：

1. 分区表及分区概念：

分区表是把逻辑上的一张表根据某种方案分成几张物理块进行存储。这张逻辑上的表称之为分区表，物理块称之为分区。分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的，即是由普通表存储的，主要用于提升查询性能。

常见的分区方案有范围分区（Range Partitioning）、哈希分区（Hash Partitioning）、列表分区（List Partitioning）、数值分区（Value Partition）等。目前行存表、列存表仅支持范围分区。

范围分区是根据表的一列或者多列，将要插入表的记录分为若干个范围，这些范围在不同的分区里没有重叠。为每个范围创建一个分区，用来存储相应的数据。范围分区的分区策略是指记录插入分区的方式。目前范围分区仅支持范围分区策略。

范围分区策略：根据分区键值将记录映射到已创建的某个分区上，如果可以映射到已创建的某一分区上，则把记录插入到对应的分区上，否则给出报错和提示信息。这是常用的分区策略。

范围分区表：将数据基于范围映射到每一个分区，这个范围是由创建分区表时指定的分区键决定的。这种分区方式是为常用的，并且分区键经常采用日期，例如将销售数据按照月份进行分区。

2. GaussDB for DWS分区表：

GaussDB for DWS数据库支持的分区表为范围分区表。通过分区表的剪枝机制可以大幅减少数据的扫描量。有限地支持唯一约束和主键约束，即唯一约束和主键约束的约束键必须包含所有分区键。

分区表上不支持并行创建索引、不支持创建部分索引、不支持NULL FIRST特性。在分区表上创建唯一索引时，索引项中必须包含分布列和所有分区键。

在数据写入分区表时，GaussDB for DWS还提供了Exchange（交换分区）的技术来提升写入性能。

3. 分区及使用分区表的好处：

分区可以提供若干好处：

l 某些类型的查询性能可以得到极大提升。特别是表中访问率较高的行位于一个单独分区或少数几个分区上的情况下。分区可以减少数据的搜索空间，提高数据访问效率。

l 当查询或更新一个分区的大部分记录时，连续扫描那个分区而不是访问整个表可以获得巨大的性能提升。

l 如果需要大量加载或者删除的记录位于单独的分区上，则可以通过直接读取或删除那个分区以获得巨大的性能提升，同时还可以避免由于大量DELETE导致的 VACUUM超载（仅范围分区）。

分区表和普通表相比具有以下优点：

l 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索效率。

l 增强可用性：如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。

l 方便维护：如果分区表的某个分区出现故障，需要修复数据，只修复该分区即可。

l 均衡I/O：可以把不同的分区映射到不同的磁盘以平衡I/O，改善整个系统性能。

二、 GaussDB for DWS分区：

1. GaussDB for DWS分区表的技术指标：

l 创建列存分区表和HDFS分区表的数量建议不超过1000个。

l 分区表的分区个数最大值为32768

l 分区表的单个分区大小最大值为1PB

l 分区表的单个分区记录数最大值为255

2. 分区建议

当表中的数据量很大时，应当对表进行分区，一般需要遵循以下原则：

l 【建议】使用具有明显区间性的字段进行分区，比如日期、区域等字段上建立分区。

l 【建议】分区名称应当体现分区的数据特征。例如，关键字+区间特征。

l 【建议】将分区上边界的分区值定义为MAXVALUE，以防止可能出现的数据溢出。

普通表若要转成分区表，需要新建分区表，然后把普通表中的数据导入到新建的分区表中。因此在初始设计表时，请根据业务提前规划是否使用分区表。

3. 分区使用说明

CREATE TABLESPACE

Ø 只有系统管理员可以创建表空间。

Ø 不允许在一个事务块内部执行CREATE TABLESPACE。

Ø 执行CREATE TABLESPACE失败，如果内部创建目录（文件）操作成功了就会产生残留的目录（文件），重新创建时需要用户手动清理表空间指定的目录下残留的内容。如果在创建过程中涉及到数据目录下的表空间软连接残留，需要先将软连接的残留文件删除，再重新执行OM相关操作。

Ø CREATE TABLESPACE不支持两阶段事务，如果部分节点执行失败，不支持回滚。

语法：

CREATE TABLESPACE tablespace_name [ OWNER user_name ] RELATIVE LOCATION 'directory' [ MAXSIZE 'space_size' ] [with_option_clause];

其中普通表空间的with_option_clause为：

WITH ( {filesystem= { 'general'| "general" | general} | random_page_cost = { 'value' | value } | seq_page_cost = { 'value' | value }}[,...])

其中HDFS表空间的with_option_clause为：

WITH ( filesystem= { 'systemtype '| "systemtype" | systemtype } [ { , address = 'ip:port [ {, ip:port }]' ], cfgpath = 'path' , storepath = 'rootpath' [{, random_page_cost = { 'value'| value }}] [{,seq_page_cost = { 'value'| value }}])

CREATE TABLE PARTITION

语法：

CREATE TABLE [ IF NOT EXISTS ] partition_table_name ( [ { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ] | table_constraint | LIKE source_table [ like_option [...] ] }[, ... ] ] ) [ WITH ( {storage_parameter = value} [, ... ] ) ] [ COMPRESS | NOCOMPRESS ] [ TABLESPACE tablespace_name ] [ DISTRIBUTE BY { REPLICATION | { [ HASH ] ( column_name ) } } ] [ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ] PARTITION BY { {VALUES (partition_key)} | {RANGE (partition_key) ( partition_less_than_item [, ... ] )} | {RANGE (partition_key) ( partition_start_end_item [, ... ] )} } [ { ENABLE | DISABLE } ROW MOVEMENT ];

列约束column_constraint：

[ CONSTRAINT constraint_name ] { NOT NULL | NULL | CHECK ( expression ) | DEFAULT default_expr | UNIQUE index_parameters | PRIMARY KEY index_parameters } [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

表约束table_constraint：

[ CONSTRAINT constraint_name ] { CHECK ( expression ) | UNIQUE ( column_name [, ... ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] ) index_parameters} [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

like选项like_option：

{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | RELOPTIONS | DISTRIBUTION | ALL }

索引存储参数index_parameters：

[ WITH ( {storage_parameter = value} [, ... ] ) ] [USING INDEX TABLESPACE tablespace_name ]

partition_less_than_item：

PARTITION partition_name VALUES LESS THAN ( { partition_value | MAXVALUE } ) [TABLESPACE tablespace_name]

partition_start_end_item：

PARTITION partition_name { {START(partition_value) END (partition_value) EVERY (interval_value)} | {START(partition_value) END ({partition_value | MAXVALUE})} | {START(partition_value)} | {END({partition_value | MAXVALUE})} } [TABLESPACE tablespace_name]

在创建分区表若第一个分区定义含START值，则范围（MINVALUE，START）将自动作为实际的第一个分区。在创建分区表时START END与LESS THAN语法不可混合使用。即使创建分区表时使用START END语法，备份（gs_dump）出的SQL语句也是 VALUES LESS THAN语法格式。

ALTER TABLE PARTITION

修改表分区，包括增删分区、切割分区、合成分区，以及修改分区属性等。

注意事项

Ø 添加分区的表空间不能是PG_GLOBAL。

Ø 添加分区的名字不能与该分区表已有分区的名字相同。

Ø 添加分区的分区键值要和分区表的分区键的类型一致，且要大于分区表中后一个范围分区的上边界。

Ø 如果目标分区表中已有分区数达到了大值（32767），则不能继续添加分区。

Ø 当分区表只有一个分区时，不能删除该分区。

Ø 选择分区使用PARTITION FOR()，括号里指定值个数应该与定义分区时使用的列个数相同，并且一一对应。

Ø Value分区表不支持相应的Alter Partition操作。

Ø 列存分区表不支持切割分区。

Ø 不能修改分区表的tablespace，但可以修改分区的tablespace。

● 修改表分区主语法：

ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name )} action [, ... ];

其中action统指如下分区维护子语法。当存在多个分区维护子句时，保证了分区的连续性，无论这些子句的排序如何，GaussDB A总会先执行DROP PARTITION再执行ADD PARTITION操作，后顺序执行其它分区维护操作。

– move_clause子语法用于移动分区到新的表空间。

MOVE PARTITION { partion_name | FOR ( partition_value [, ...] ) } TABLESPACE tablespacename

– exchange_clause子语法用于把普通表的数据迁移到指定的分区。

EXCHANGE PARTITION { ( partition_name ) | FOR ( partition_value [, ...] ) } WITH TABLE {[ ONLY ] ordinary_table_name | ordinary_table_name * | ONLY ( ordinary_table_name )} [ { WITH | WITHOUT } VALIDATION ] [ VERBOSE ]

进行交换的普通表和分区必须满足如下条件：

▪ 普通表和分区的列数目相同，对应列的信息严格一致，包括：列名、列的数据类型、列约束、列的Collation信息、列的存储参数、列的压缩信息等。

▪ 普通表和分区的表压缩信息严格一致。

▪ 普通表和分区的分布列信息严格一致。

▪ 普通表和分区的索引个数相同，且对应索引的信息严格一致。

▪ 普通表和分区的表约束个数相同，且对应表约束的信息严格一致。

▪ 普通表不可以是临时表。

完成交换后，普通表和分区的数据被置换，同时普通表和分区的表空间信息被置换。此时，普通表和分区的统计信息变得不可靠，需要对普通表和分区重新执行analyze。

– row_clause子语法用于设置分区表的行迁移开关。

{ ENABLE | DISABLE } ROW MOVEMENT

– merge_clause子语法用于把多个分区合并成一个分区。

MERGE PARTITIONS { partition_name } [, ...] INTO PARTITION partition_name [ TABLESPACE tablespacename ]

– modify_clause子语法用于设置分区索引是否可用。

MODIFY PARTITION partition_name { UNUSABLE LOCAL INDEXES | REBUILD UNUSABLE LOCAL INDEXES }

– split_clause子语法用于把一个分区切割成多个分区。

SPLIT PARTITION { partition_name | FOR ( partition_value [, ...] ) } { split_point_clause | no_split_point_clause }

▪ 指定切割点split_point_clause的语法为:

AT ( partition_value ) INTO ( PARTITION partition_name [ TABLESPACE tablespacename ] , PARTITION partition_name [ TABLESPACE tablespacename ] )

u 列存分区表不支持切割分区。

u 切割点的大小要位于正在被切割的分区的分区键范围内，指定切割点的方式只能把一个分区切割成两个新分区。

▪ 不指定切割点no_split_point_clause的语法为。

INTO { ( partition_less_than_item [, ...] ) | ( partition_start_end_item [, ...] ) }

u 不指定切割点的方式，partition_less_than_item指定的第一个新分区的分区键要大于正在被切割的分区的前一个分区（如果存在的话）的分区键，partition_less_than_item指定的后一个分区的分区键要等于正在被切割的分区的分区键大小。

u 不指定切割点的方式，partition_start_end_item指定的第一个新分区的起始点（如果存在的话）必须等于正在被切割的分区的前一个分区（如果存在的话）的分区键，partition_start_end_item指定的后一个分区的终止点（如果存在的话）必须等于正在被切割的分区的分区键。

u partition_less_than_item支持的分区键个数多为4，而partition_start_end_item仅支持1个分区键。

u 在同一语句中partition_less_than_item和partition_start_end_item两者不可同时使用；不同split语句之间没有限制。

▪ 分区项partition_less_than_item的语法为:

PARTITION partition_name VALUES LESS THAN ( { partition_value | MAXVALUE } [, ...] ) [ TABLESPACE tablespacename ]

▪ 分区项partition_start_end_item的语法为:

– add_clause子语法用于为指定的分区表添加一个或多个分区。

ADD {partition_less_than_item | partition_start_end_item}

– drop_clause子语法用于删除分区表中的指定分区。

DROP PARTITION { partition_name | FOR ( partition_value [, ...] ) }

● 修改表分区名字的语法:

ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name )} RENAME PARTITION { partion_name | FOR ( partition_value [, ...] ) } TO partition_new_name;

语法:

SELECT * FROM [scheme]. table_name PARTITION { ( partition_name ) | FOR ( partition_value [, ...] ) };

DROP TABLE

删除指定的分区表。DROP TABLE会强制删除指定的表，删除表后，依赖该表的索引会被删除，而使用到该表的函数和存储过程将无法执行。删除分区表，会同时删除分区表中的所有分区。删除分区表与删除普通表的语法一致，都是通过DROP TABLE语法进行删除。

语法:

DROP TABLE [ IF EXISTS ] { [schema.]table_name } [, ...] [ CASCADE | RESTRICT ];

DROP TABLESPACE

Ø 只有表空间所有者有权限执行DROP TABLESPACE命令，系统管理员默认拥有此权限。

Ø 在删除一个表空间之前，表空间里面不能有任何数据库对象，否则会报错。

Ø DROP TABLESPACE不支持回滚，因此，不能出现在事务块内部。

Ø 执行DROP TABLESPACE操作时，如果有另外的会话执行\db查询操作，可能会由于tablespace事务的原因导致查询失败，请重新执行\db查询操作。

Ø 如果执行DROP TABLESPACE失败，需要再次执行一次DROP TABLESPACE IF EXISTS。

语法:

DROP TABLESPACE [ IF EXISTS ] tablespace_name;

第二章：Teradata分区介绍

一、 Teradata分区表的设计原理：

1.1 创建分区表的时候会在每行记录上加一个分区值Partition #。

Ø Row ID = Partition # + Row Hash + Uniqueness Value

Ø 在新版本里面会自适应表分区的个数，小于65,535时使用2-byte做分区值，如果大于会用8-byte分区值，最大支持：9.223 E18个分区

1.2 数据存放的三级机制：

Ø 通过Row的PI字段的hash值确定放那个AMP上（类比我们的逻辑DN）

Ø 在AMP级别，Row会按其分区值排序存放。

Ø 在分区内，数据行通过Row-ID序列逻辑存储。

图例：

1.3 分区表数据访问机制：

二、 Teradata分区表的优缺说明：

2.1. 优点：

Ø 提升按partition字段查询的效率，避免做全表扫描，节约IO资源。

2.2. 缺点：

Ø 每行多申请2-byte空间（8-byte），会需要额外perm空间。

Ø PI的访问可能会慢，因为要扫每个partition。

Ø PI的join可能会比较慢，以前等值直接join，现在要每个分区都需要做sub-join。

Ø 不能定义UPI，因为这样会导致insert-select会非常慢，需要判断每个partition里面的合法性。

三、 Teradata分区表的使用说明

3.1. 创建分区表语法：

CREATE TABLE …[UNIQUE] PRIMARY INDEX (col1, col2, …)PARTITION BY

Ø 选项包括:

² 范围分区

² 条件分区, 模数分区, 和表达式分区.

² 分区列不一定是PI中的字段，如果不是的话，那么PI不能加唯一约束。

² 分区表达式中包括的列称为“分区列”。

² TD提供了RANGE_N和CASE_N两个函数来方便创建常见分区表。

3.2. RANGE_N的使用说明

Ø test_expression

一个表达式或者字段，数据类型为BYTEINT, SMALLINT, INTEGER, DATE, CHAR, VARCHAR, GRAPHIC or VARGRAPHIC。

Ø start_expression

定义范围起始边界的字符串或字符串表达式。

start_expression的数据类型必须与test_expression的数据类型相同，或者必须隐式转换为与test_expression相同的数据类型。

如果未指定结束边界，则范围由其开始边界定义（并且该开始边界包括在该范围内），直到但不包括下一个范围的开始边界。使用星号（*）作为列表中第一个范围的开始边界，以指示可能的最小值（所有值和NULL均大于指定为星号的起始边界）。星号与任何数据类型兼容。

Ø end_expression

定义范围结束边界的字符串或字符串表达式。

end_expression的数据类型必须与test_expression的数据类型相同，或者必须隐式转换为与test_expression相同的数据类型。

列表中的最后一个范围必须指定结束边界。对于所有其他范围，如果没有指定结束边界，则范围由其开始边界定义（并且该开始边界包括在该范围内），直到但不包括下一个范围的开始边界。星号（*）表示列表中最后一个范围的结束边界，以表示可能的最大值（所有值和NULL均小于指定为星号的结束边界）。

Ø EACH range_size

值大于零的字符或字符表达式。

指定一个EACH短语的范围等效于一系列范围，其中系列中的第一个范围从start_expression开始，随后的范围从start_expression +（range_size * n）开始，当start_expression +（range_size * n）小于或等于end_expression，或者小于范围列表中的下一个start_expression时，n从1开始递增1。

对于DATE类型，在后续范围内的有效日期的计算使用ADD_MONTHS而不是加号（+）算术运算符。

range_size的数据类型必须兼容才能添加到test_expression中。

Ø Note:

如果test_expression的数据类型是字符类型（CHAR，VARCHAR，GRAPHIC或

VARGRAPHIC），则无法指定EACH短语。

Ø NO RANGE

用于处理未映射到任何指定范围的test_expression的范围。

Ø OR UNKNOWN

与NO RANGE一起使用。

NO RANGE或UNKNOWN处理不会映射到任何指定范围的test_expression，或者当RANGE_N未指定BETWEEN * AND *范围时，test_expression评估为NULL。

Ø UNKNOWN

当RANGE_N未指定范围BETWEEN * AND *时，处理一个求值为NULL的test_expression。

Ø 例如：

例1:

CREATE TABLE orders

(storeid INTEGER NOT NULL

,productid INTEGER NOT NULL

,orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL

,totalorders INTEGER)

PRIMARY INDEX (storeid, productid)

PARTITION BY RANGE_N(totalorders BETWEEN *, 100, 1000 AND *,

UNKNOWN);

分区号

范围

totalorders >100

100<=totalorders<1000

totalorders >=1000

totalorders =NULL,UNKNOWN.

例2:

CREATE TABLE orders

(storeid INTEGER NOT NULL

,productid INTEGER NOT NULL

,orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL

,totalorders INTEGER NOT NULL)

PRIMARY INDEX (storeid, productid)

PARTITION BY (RANGE_N(totalorders BETWEEN *, 100, 1000 AND *)

,RANGE_N(orderdate BETWEEN *, '2005-12-31' AND *) );

Level 1 Partition Number

Level 2 Partition Number

范围

totalorders >100 and orderdate<'2005-12-31'

totalorders >100 and orderdate>='2005-12-31'

(100<=totalorders<1000) and orderdate<'2005-12-31'

(100<=totalorders<1000) and orderdate>='2005-12-31'

totalorders >=1000 and orderdate<'2005-12-31'

totalorders >=1000 and orderdate>='2005-12-31'

例3.

CREATE TABLE Orders

(o_orderkey INTEGER NOT NULL,

o_custkey INTEGER,

o_orderstatus CHAR(1) CASESPECIFIC,

o_totalprice DECIMAL(13,2) NOT NULL,

o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL,

o_orderpriority CHAR(21),

o_comment VARCHAR(79))

PRIMARY INDEX (o_orderkey)

PARTITION BY RANGE_N(

o_orderdate BETWEEN DATE '2002-01-01' AND DATE '2008-12-31'

EACH INTERVAL '1' MONTH)

分区为：2002-01-01到2008-12-31日期范围内的数据，每个月的数据作为一个分区。

目前在银行数据仓库中，第三种分区方式比较常见。

3.3. CASE_N的使用说明

Ø conditional_expression

条件表达式,计算结果必须为TRUE，FALSE或UNKNOWN。

Ø NO CASE

如果所有的conditional_expression计算结果为FALSE,则取值为TRUE。

Ø OR UNKNOWN

和 NO CASE一起使用.

如果列表中的conditional_expression结果为FALSE、或者UNKNOWN，则NO CASE 或者 UNKNOWN结果为TRUE。

Ø UNKNOWN

如果conditional_expression评估为UNKNOWN且列表中的所有先前条件均评估为TRUE

评估为FALSE。

Ø 例如：

例1.

CREATE TABLE orders

(storeid INTEGER NOT NULL

,productid INTEGER NOT NULL

,orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL

,totalorders INTEGER)

PRIMARY INDEX (storeid, productid)

PARTITION BY CASE_N(totalorders < 100, totalorders < 1000,

NO CASE, UNKNOWN);

分区号

范围

GaussDB for DWS&TD分区表设计原理和使用对比

totalorders >100

100<=totalorders<1000

totalorders >=1000

totalorders =NULL,UNKNOWN.

例2.

CREATE TABLE orders

(storeid INTEGER NOT NULL

,productid INTEGER NOT NULL

,orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL

,totalorders INTEGER NOT NULL)

PRIMARY INDEX (storeid, productid)

PARTITION BY (CASE_N(totalorders < 100, totalorders < 1000,

NO CASE)

,CASE_N(orderdate <= '2005-12-31', NO CASE) );

Level 1 Partition Number

Level 2 Partition Number

范围

totalorders >100 and orderdate<'2005-12-31'

totalorders >100 and orderdate>='2005-12-31'

(100<=totalorders<1000) and orderdate<'2005-12-31'

(100<=totalorders<1000) and orderdate>='2005-12-31'

totalorders >=1000 and orderdate<'2005-12-31'

totalorders >=1000 and orderdate>='2005-12-31'

数据结构数据仓库服务 GaussDB(DWS)

标签：Gauss

GaussDB重要通信参数汇总（gaussdb协议分析）

1573 2022-05-29

GaussDB for DWS&TD分区表设计原理和使用对比

GaussDB(DWS) GDS导入容错详解（gaussdbdws8.1客户端安装步骤）

GaussDB for DWS 数据融合系列第一期：云端数据接入（CDM）（gaussdb数据库）

GaussDB重要通信参数汇总（gaussdb协议分析）

推荐文章

企业生产管理是什么，企业生产管理软件

进盘点进销存软件排行榜前十名

进销存系统哪个简单好用？进销存系统优点

工厂生产管理（工厂生产管理流程及制度）

生产管理软件，机械制造业生产管理，制造业生产过程管理软件

进销存软件和ERP有什么区别？进销存与erp软件理解

进销存如何进行库存管理

如何利用excel制作销售订单管理系统？

数据库订单管理系统有哪些功能？数据库订单管理系统怎么设计？

什么是数据库管理系统？

最近发表

热评文章

零代码开发是什么？2022低代码平台排行榜

智能进销存库存管理系统（智慧进销存）

在线文档哪家强？8款在线文档编辑软件推荐

WPS2016怎么绘制简单的价格表?

什么是在线文档？怎么发在线文档

简单进销存库存管理盘点

友情链接