达梦分区表

温情Lucky

已于 2023-03-01 09:59:13 修改

阅读量2.9k

点赞数 4

CC 4.0 BY-SA版权

文章标签：数据库 database dba

于 2022-12-20 17:34:21 首次发布

本文链接：https://blog.csdn.net/cn_lyt/article/details/128343145

1. 达梦分区类型

达梦支持对表进行水平分区。分区表作为主表，每个分区以一个子表实体存在，即每个分区都是一个完整的表，因此每个子表都可指定存放的表空间。一般命名为主表明_分区名。对于水平分区，子表跟主表有着相同的逻辑结构。在达梦分区表中，主表本身不存储数据，所有数据只存储在子表中，从而实现不同分区的完全独立性。水平分区子表删除后，会将子表上的数据一起删除。达梦水平分区表提供以下分区方式：

1. 范围水平分区：对表中的某些列上的值的范围进行分区，根据某个值的范围，决定将该数据存储在那个分区。

2. 哈希水平分区：通过指定分区编号来均匀分布数据的一种分区类型，通过在I/O设备上进行散列分区，使得这些分区大小基本一致。

3. 列表水平分区：通过指定表中的某个列的离散值集，来确定应当存储在一起的数据。例如，可以对表上的status列的值在('A', 'H', '0')放在一个分区，值在('B', 'I', ;P')放在另一个分区，以此类推。

4. 多级分区表：按上述三种分区方法进行任意组合，将表进行多次分区，称为多级分区表。

2. 达梦水平分区表定义

2.1 范围水平分区

CREATE TABLE RANGE_TEST
(
	ID INT,
	NAME VARCHAR(20),
	CONTENT VARCHAR(20),
	STARTTIME DATETIME,
    NOT CLUSTER PRAMARY KEY(ID,STARTTIME)
)
PARTITION BY RANGE(STARTTIME)
(
	PARTITION P1 VALUES LESS THAN(DATETIME'2008-01-01 00:00:00'),
	PARTITION P2 VALUES LESS THAN(DATETIME'2009-01-01 00:00:00'),
	PARTITION PN VALUES EQU OR LESS THAN(MAXVALUE)
);

进行范围分区时，首先应指定分区列（可多列），格式参考VALUES LESS THAN（无上限）或者VALUES EQU OR LESS THAN（包含上限）。

范围分区的适用场景为可为数据做出明显的范围划分。如时间、自然数等。

2.2 列表水平分区

CREATE TABLE LIST_TEST
(
	ID INT,
	NAME VARCHAR(20),
	CONTENT VARCHAR(20),
	STARTTIME DATETIME,
    NOT CLUSTER PRAMARY KEY(ID,NAME)
)
PARTITION BY LIST(NAME)
(
	PARTITION P1 VALUES('北京', '上海', '广州','深圳'),
	PARTITION P2 VALUES('成都','绵阳','广元','遂宁'),
	PARTITION P3 VALUES('')
);

列表分区的适用场景为，当无法确定数据范围时，并且分区列上的值是相对固定的一些值时，可以考虑使用列表分区。如城市名字，书籍类型等。

2.3 哈希水平分区

CREATE TABLE HASH_TEST
(
	ID INT PRIMARY KEY,
	NAME VARCHAR(20),
	CONTENT VARCHAR(20),
	STARTTIME DATETIME
)
PARTITION BY HASH(ID)
(
	PARTITION P1,
	PARTITION P2,
	PARTITION P3
);

哈希水平分区的适用场景为，既无法确定数据范围，又不是固定的一些值时，可以考虑使用哈希水平分区。如某些加密值，人名等。

3. 水平分区表维护

3.1 增加分区

列表分区/哈希分区/不含maxvalues的范围分区表增加分区如下：

--range
ALTER TABLE SYSDBA.RANGE_TEST ADD PARTITION P3 VALUES LESS THAN(DATETIME'2010-01-01 00:00:00');
--list
ALTER TABLE SYSDBA.LIST_TEST ADD PARTITION P4 VALUES('西安');
--hash
ALTER TABLE SYSDBA.HASH_TEST ADD PARTITION P4;

注意事项：

1). 对于范围分区，如果包含maxvalue分区，则会提示报错：范围分区值非递增。这种情况下可通过以下两种方式新增分区

2). 对于列表分区，增加分区包含的值不能存在于某个现有分区中

3). 对于哈希分区的新增，存储选项中的HASHPARTMAP值需为1。（默认为1）

3.1.1 分区拆分

将maxvalue分区拆分添加的新分区+新的maxvalue分区。语句如下：

--method 1
ALTER TABLE SYSDBA.RANGE_TEST SPLIT PARTITION P4 AT(DATETIME'2010-01-01 00:00:00') INTO(PARTITION P3,PARTITION P5);


--OR LIKE THIS
ALTER TABLE T1 SPLIT PARTITION P3 INTO(PARTITION p7 VALUES LESS 
THAN(25,25),PARTITION p8 VALUES LESS THAN (28,28),PARTITION p9);


ALTER TABLE SYSDBA.RANGE_TEST SPLIT PARTITION P4 INTO(PARTITION P4 VALUES LESS THAN(DATETIME'2011-01-01 00:00:00'),PARTITION P5);

两种方式的区别在于，第一种无法定义拆分出来的分区边界，而第二种可以。当数据量及业务空闲时，可使用分区拆分的方式将带maxvalue的分区表进行拆分，从而增加水平分区。当数据量过大及业务繁忙时不应使用此方式，此方式涉及数据重组及分区索引重建，耗费较长时间。

3.1.2 分区重构

针对场景为包含maxvalue分区且maxvalue内包含大量数据（如20亿）的分区表增加分区。实现思路如下：

1). 新建同分区表同结构的普通临时表，用于存储置换maxvalue分区数据。

2). 将maxvalue分区同普通临时表进行置换

3). 删除maxvalue分区

4). 使用3.1中范围分区增加分区sql语句增加分区

5). 根据分区列手动整理临时表中数据，针对每个新建分区创建对应临时普通表存放相关数据

6). 将整理好的临时表同新建分区进行数据置换

7). 增加maxvalue分区

3.1.3 分区重构实战

本次样例源自生产环境分区表改造。一张包含35亿数据，maxvalue分区3亿5数据的分区表增加子分区表。

测试表搭建,表内无数据。原表如上所述，总数据量35一，maxvalue3亿5，maxvalue中数据均为2022年数据，:

CREATE TABLE TEST_PART(
	C1_TIME	DATETIME(6) NOT NULL,
	C2_CODE	NUMBER(5,0) NOT NULL,
	C3_ID	NUMBER(10,0) NOT NULL,
	C4_DATA	NUMBER(9,2),
	NOT CLUSTER PRIMARY KEY(C1_TIME,C3_ID,C2_CODE)
)
PARTITION BY RANGE(C1_TIME)
(
	PARTITION P1 VALUES LESS THAN(DATETIME'2012-01-01 00:00:00'),
	PARTITION P2 VALUES LESS THAN(DATETIME'2013-01-01 00:00:00'),
	PARTITION P3 VALUES LESS THAN(DATETIME'2014-01-01 00:00:00'),
	PARTITION P4 VALUES LESS THAN(DATETIME'2015-01-01 00:00:00'),
	PARTITION P5 VALUES LESS THAN(DATETIME'2016-01-01 00:00:00'),
	PARTITION P6 VALUES LESS THAN(DATETIME'2017-01-01 00:00:00'),
	PARTITION P7 VALUES LESS THAN(DATETIME'2018-01-01 00:00:00'),
	PARTITION P8 VALUES LESS THAN(DATETIME'2019-01-01 00:00:00'),
	PARTITION P9 VALUES LESS THAN(DATETIME'2020-01-01 00:00:00'),
	PARTITION P10 VALUES LESS THAN(DATETIME'2021-01-01 00:00:00'),
    PARTITION P11 VALUES LESS THAN(DATETIME'2022-01-01 00:00:00'),
	PARTITION P12 VALUES EQU OR LESS THAN(MAXVALUE)
);

分区重构：

1）数据备份

根据历史备份时间，在执行操作时间点往前推对应时间进行数据全备用于容灾。

BACKUP DATABASE FULL BACKUPSET 'DB_FULL_BACK_20221228' COMPRESSED LEVEL 2 PARALLEL 2;

1）暂停相关业务

方案中因涉及DROP maxvalue分区，因此需关闭相关实时业务，预估耗时5分钟，因业务有缓存，短时内停止不影响数据完整性。

2）创建临时表TEST_PARTTEMP用于交换maxvalue分区数据

该临时表表结构需和原分区表保持高度一致。

CREATE TABLE TEST_PARTTEMP(
	C1_TIME	DATETIME(6) NOT NULL,
	C2_CODE	NUMBER(5,0) NOT NULL,
	C3_ID	NUMBER(10,0) NOT NULL,
	C4_DATA	NUMBER(9,2),
	NOT CLUSTER PRIMARY KEY(C1_TIME,C3_ID,C2_CODE)
);

3）分区置换

将maxvalue子表同临时表进行数据交换，为DROPmaxvalue分区做准备。

ALTER TABLE TEST_PART EXCHANGE PARTITION P12 WITH TABLE SYSDBA.TEST_PARTTEMP;

4）删除maxvalue分区

ALTER TABLE SYSDBA.TEST_PART DROP PARTITION P12;

5）增加2022-2030子分区

ALTER TABLE SYSDBA.TEST_PART ADD PARTITION P12 VALUES LESS THAN(DATETIME'2023-01-01 00:00:00');
ALTER TABLE SYSDBA.TEST_PART ADD PARTITION P13 VALUES LESS THAN(DATETIME'2024-01-01 00:00:00');
ALTER TABLE SYSDBA.TEST_PART ADD PARTITION P14 VALUES LESS THAN(DATETIME'2025-01-01 00:00:00');
ALTER TABLE SYSDBA.TEST_PART ADD PARTITION P15 VALUES LESS THAN(DATETIME'2026-01-01 00:00:00');
ALTER TABLE SYSDBA.TEST_PART ADD PARTITION P16 VALUES LESS THAN(DATETIME'2027-01-01 00:00:00');
ALTER TABLE SYSDBA.TEST_PART ADD PARTITION P17 VALUES LESS THAN(DATETIME'2028-01-01 00:00:00');
ALTER TABLE SYSDBA.TEST_PART ADD PARTITION P18 VALUES LESS THAN(DATETIME'2029-01-01 00:00:00');
ALTER TABLE SYSDBA.TEST_PART ADD PARTITION P19 VALUES LESS THAN(DATETIME'2030-01-01 00:00:00');

6）增加maxvalue子分区

ALTER TABLE SYSDBA.TEST_PART ADD PARTITION P20 VALUES EQU OR LESS THAN(MAXVALUE);

7）临时表数据置换

因原maxvalue子分区数据只包含2022年数据，因此直接同2022年度子分区进行交换即可。

ALTER TABLE SYSDBA.TEST_PART EXCHANGE PARTITION P12 WITH TABLE SYSDBA.TEST_PARTTEMP;

3.1.4 分区重构实战总结

1. 就实际可操作性而言，针对包含maxvalue的小表增加少量分区，使用拆分分区的方式可行且高效的。但是针对包含maxvalue的大表以及增加大量子分区，使用拆分分区的方式在生产环境应该是不被允许的。之前测试时使用过20亿数据的分区表进行测试。交换分区的方式增加子分区耗时为毫秒级

2. 对于超大的分区表如包含35亿的大分区表，不能使用分区分裂。因为拆分会导致数据重组及重建。而这个表太大了，耗费的时间太久了而且会将相关业务直接卡死。现场应用开始使用方法为拆分分区。执行半小时后发现相关业务直接卡死，撤销后执行分离的manager因为回滚直接卡住。

3. 对于实时性较强的分区表，使用分区重构的方式由于需要DROP maxvalue分区，因此应先暂停相关业务防止发生数据丢失破坏数据完整性。

3.2 删除分区

范围分区/列表分区删除分区如下：

--range
ALTER TABLE SYSDBA.RANGE_TEST DROP PARTITION PN;
--list
ALTER TABLE SYSDBA.LIST_TEST DROP PARTITION P4;

注意事项：

哈希分区不支持删除分区。

3.3 交换分区

范围分区/列表分区交换分区如下：

--TEMP TABLE
CREATE TABLE RANGE_TEST_TEMP
(
	ID INT,
	NAME VARCHAR(20),
	CONTENT VARCHAR(20),
	STARTTIME DATETIME,
	NOT CLUSTER PRIMARY KEY(ID,STARTTIME)
)
--EXCHANGE
ALTER TABLE SYSDBA.RANGE_TEST EXCHANGE PARTITION PN WITH TABLE SYSDBA.RANGE_TEST_TEMP;

注意事项：

1). 仅范围分区和列表分区支持交换分区，哈希分区不支持。并且交换表同分区表需具备完全一样的结构

2). 交换分区不会校验数据，不管是否为该分区范围内数据均会写入该分区

3). 如果包含加密列，对应的加密列要求加密信息完全一致

3.4 合并分区

范围分区/列表分区合并分区如下：

ALTER TABLE SYSDBA.RANGE_TEST MERGE PARTITIONS P1,P2 INTO PARTITION P1_2;

注意事项：

1). 仅范围分区和列表分区支持交换分区，哈希分区不支持。且对于范围分区。合并的两个分区需为相邻分区

2). 仅支持一级子表类型为range/list

3). 合并多级分区表中的一级子表时，该一级子表下的二级子表及以上层次子表按照级别分别由系统自动合并为一个子表，子表名称为系统内部设置。range类型给你范围值为maxvalue，list类型范围值为default。

4). 不允许自定义二级及以上层次子表

5). 不允许直接合并二级及以上层次子表

6). 合并分区会导致数据重组和分区索引重建，因此对于大分区合并比较耗时

3.5 拆分分区

范围分区/列表分区拆分分区如下：

--method 1
ALTER TABLE SYSDBA.RANGE_TEST SPLIT PARTITION P4 AT(DATETIME'2010-01-01 00:00:00') INTO(PARTITION P3,PARTITION P5);


ALTER TABLE T1 SPLIT PARTITION P3 INTO(PARTITION p7 VALUES LESS 
THAN(25,25),PARTITION p8 VALUES LESS THAN (28,28),PARTITION p9);

--OR LIKE THIS
ALTER TABLE SYSDBA.RANGE_TEST SPLIT PARTITION P4 INTO(PARTITION P4 VALUES LESS THAN(DATETIME'2011-01-01 00:00:00'),PARTITION P5);

注意事项：

1). 仅支持一级子表类型为range/list

2). 支持拆分为2个及以上个子表

3). 不允许自定义二级及以上层次子表

4). 拆分会导致数据重组及重建，因此拆分可能比较耗时