更新时间:2024-09-06 GMT+08:00

INTERVAL RANGE

INTERVAL RANGE分区表是RANGE分区表的扩展,向RANGE分区表插入数据时,如果插入的数据超出当前已存在分区的范围,将无法插入并且会返回错误;

而对于INTERVAL RANGE分区表,当新插入的数据超过现有分区的范围时,允许数据库根据INTERVAL子句提前指定的规则来添加新分区。

前提条件

  • GaussDB(for MySQL)的内核版本大于等于2.0.54.240600。
  • 设置特性开关rds_interval_range_enabled的值为ON。

使用限制

  • INTERVAL RANGE表只支持HASH/KEY二级分区。
  • 采取RANGE COLUMNS(column_list) INTERVAL([type], value)形式描述INTERVAL RANGE规则时:
    • column_list(分区键)中列个数只能为1,并且只能是是整数类型或者DATE/TIME/DATETIME类型。
    • 如果分区键是整型,INTERVAL的间隔类型type不需要填写。
    • 如果分区键为DATE类型,INTERVAL的间隔类型type只能取YEAR、QUARTER、MONTH、WEEK、DAY。
    • 如果分区键为TIME类型,INTERVAL的间隔类型type只能取HOUR、MINUTE、SECOND。
    • 如果分区间为DATETIME类型,INTERVAL的间隔类型type可以取YEAR、QUARTER、MONTH、WEEK、DAY、HOUR、MINUTE、SECOND。
    • 间隔值value只能为正整数。
    • 如果INTERVAL的间隔类型是SECOND,间隔不能小于60。
  • 采取RANGE(expr) INTERVAL(value)形式描述INTERVAL RANGE规则时,expr表达式的结果应为整数,间隔值value只能为正整数。
  • 不支持使用INSERT ...SELECT语句、INSERT ...ON DUPLICATE KEY UPDATE语句、UPDATE语句触发分区新增。
  • LOAD DATA时不会触发分区新增(如果分区覆盖了所有插入数据的范围,能使用load data导入数据,如果分区没有覆盖插入数据的范围,load data无法触发自增分区,导入数据失败)。
  • 事务中如果触发分区自增,一旦新分区创建成功,不支持回滚。
  • 自增的分区使用'_p'作为分区名的前缀,因此客户设置的以此开头的分区名可能导致分区自增失败。
  • SET INTERVAL([type], value)子句只适用于INTERVAL RANGE表和RANGE表,如果这两种表有二级分区,只支持二级分区为HASH或KEY类型。
  • SET INTERVAL([type], value)子句的type和value取值要受原表的分区表达式expr或分区键column_list的约束。

参数说明

表1 参数说明

参数名称

级别

参数说明

rds_interval_range_enabled

Global

INTERVAL RANGE功能控制开关。

取值范围如下:

  • ON:启用INTERVAL RANGE功能。
  • OFF:关闭INTERVAL RANGE功能。

创建INTERVAL RANGE分区表

INTERVAL RANGE分区表定义格式类似于RANGE分区表,但多了INTERVAL子句。

语法:

CREATE TABLE [IF NOT EXISTS] [schema.]table_name
table_definition
partition_options;

其中,partition_options为:

PARTITION BY
    RANGE {(expr) | COLUMNS(column_list)}
    {INTERVAL(value) | INTERVAL(type, expr)}
    (partition_definition [, partition_definition] ...)

partition_definition为:

PARTITION partition_name
    [VALUES LESS THAN {expr | MAXVALUE}]
    [[STORAGE] ENGINE [=] engine_name]
    [COMMENT [=] 'string' ]
    [DATA DIRECTORY [=] 'data_dir']
    [INDEX DIRECTORY [=] 'index_dir']
    [MAX_ROWS [=] max_number_of_rows]
    [MIN_ROWS [=] min_number_of_rows]
    [TABLESPACE [=] tablespace_name]

其中,INTERVAL子句仅支持设置间隔数值(value)和间隔类型(type)。

INTERVAL子句关联参数说明:

表2 参数说明

参数名称

参数说明

INTERVAL(value)

使用RANGE(expr) 或者 RANGE COLUMNS(column_list)且column是整型字段时,INTERVAL子句的格式,其中value代表间隔数值,必须是正整数。

expr

RANGE(expr)中的分区表达式,目前只支持整数类型。

column_list

RANGE COLUMNS(column_list)的分区字段列表,在INTERVAL RANGE分区表中,column_list只能是单列。

INTERVAL(type, value)

使用RANGE COLUMNS(column_list)且column_list是DATE/TIME/DATETIME类型时,INTERVAL子句的格式,其中type代表间隔类型,value代表间隔数值。 type目前支持8种时间类型(YEAR、QUARTER、MONTH、WEEK、DAY、HOUR、MINUTE、SECOND)。 value代表间隔数值,必须是正整数;当type为SECOND类型时,间隔不能小于60。

间隔数值(value)和间隔类型(type)的进一步说明:

  • 间隔数值(expr)

    连续的1000个数字进入同一个分区。

    示例如下:

    INTERVAL(1000)
  • 时间类型
    • 年(YEAR)

      以年为单位设置自动分区的间隔,每一年的数据进入同一个分区。

      示例如下:

      INTERVAL(YEAR, 1)
    • 季度(QUARTER)

      以季度为单位设置自动分区的间隔,每一季度的数据进入同一个分区。

      示例如下:
      INTERVAL(QUARTER, 1)
    • 月(MONTH)

      以月为单位设置自动分区的间隔,每一月的数据进入同一个分区。

      示例如下:

      INTERVAL(MONTH, 1)
    • 周(WEEK)

      以周为单位设置自动分区的间隔,每一周的数据进入同一个分区,示例如下:

      INTERVAL(WEEK, 1)
    • 日(DAY)

      以日为单位设置自动分区的间隔,每一日的数据进入同一个分区。

      示例如下:

      INTERVAL(DAY, 1)
    • 时(HOUR)以小时为单位设置自动分区的间隔,每一小时的数据进入同一个分区。
      示例如下:
      INTERVAL(HOUR, 1)
    • 分(MINUTE)以分钟为单位设置自动分区的间隔,每一分钟的数据进入同一个分区。
      示例如下:
      INTERVAL(MINUTE, 1)
    • 秒(SECOND)以秒为单位设置自动分区的间隔,每60秒的数据进入同一个分区。
      示例如下:
      INTERVAL(SECOND, 60)

以下示例将order_time作为分区键,按间隔划分sales表。

在数据库中创建一个新的INTERVAL RANGE分区表,并向表中插入数据,示例如下:

CREATE TABLE sales
(
  id BIGINT,
  uid BIGINT,
  order_time DATETIME
)
PARTITION BY RANGE COLUMNS(order_time) INTERVAL(MONTH, 1)
(
  PARTITION p0 VALUES LESS THAN('2021-9-1')
);

向INTERVAL RANGE分区表中插入数据,示例如下:

INSERT INTO sales VALUES(1, 1010101010, '2021-11-11');

插入数据后,SHOW CREATE TABLE查询sales表定义。新的表定义如下:

CREATE TABLE `sales` (
  `id` bigint DEFAULT NULL,
  `uid` bigint DEFAULT NULL,
  `order_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800220201 INTERVAL(MONTH, 1) */
/*!50500 (PARTITION p0 VALUES LESS THAN ('2021-9-1') ENGINE = InnoDB,
 PARTITION _p20211001000000 VALUES LESS THAN ('2021-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20211101000000 VALUES LESS THAN ('2021-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20211201000000 VALUES LESS THAN ('2021-12-01 00:00:00') ENGINE = InnoDB) */

通过上述示例发现INTERVAL RANGE分区自动新增加了_p20211001000000、_p20211101000000、_p20211201000000三个分区,这里要注意‘_p’作为前缀的分区名将会保留为系统命名规则,手动管理分区(创建新分区或者重命名分区的操作)时,不建议使用‘_p’作为前缀的分区名

INTERVAL RANGE表支持HASH或KEY类型类型的二级分区,例如:

CREATE TABLE sales_ir_key
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY RANGE(month(date)) INTERVAL(1)
SUBPARTITION BY KEY(date) SUBPARTITIONS 2
(
  PARTITION q1_2012 VALUES LESS THAN(4)
    (SUBPARTITION sp_001,
     SUBPARTITION sp_002),
  PARTITION q2_2012 VALUES LESS THAN(7)
    (SUBPARTITION sp_003,
     SUBPARTITION sp_004)
);
​
CREATE TABLE sales_ir_hash
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY RANGE COLUMNS(date) INTERVAL(YEAR, 1)
SUBPARTITION BY HASH(TO_DAYS(date)) SUBPARTITIONS 2
(
  PARTITION q1_2012 VALUES LESS THAN('2021-01-01')
    (SUBPARTITION sp_001,
     SUBPARTITION sp_002),
  PARTITION q2_2012 VALUES LESS THAN('2022-01-01')
    (SUBPARTITION sp_003,
     SUBPARTITION sp_004)
);

INTERVAL RANGE分区表与任意类型表的相互转换

语法:

其他类型表转化为INTERVAL RANGE分区表。

ALTER TABLE table_name table_definition
partition_options;
​
partition_options:
    PARTITION BY
    { RANGE{(expr) | COLUMNS(column_list)} }
    { INTERVAL(type, value) | INTERVAL(value) }
    [(partition_definition [, partition_definition] ...)]
​
partition_definition:
    PARTITION partition_name
        [VALUES LESS THAN {expr | MAXVALUE}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]

INTERVAL子句关联参数说明:

表3 参数说明

参数名称

参数说明

INTERVAL(value)

使用RANGE(expr) 或者 RANGE COLUMNS(column_list)且column是整型字段时,INTERVAL子句的格式,其中value代表间隔数值,必须是正整数。

expr

RANGE(expr)中的分区表达式,目前只支持整数类型。

column_list

RANGE COLUMNS(column_list)的分区字段列表,在INTERVAL RANGE分区表中,column_list只能是单列。

INTERVAL(type, value)

使用RANGE COLUMNS(column_list)且column_list是DATE/TIME/DATETIME类型时,INTERVAL子句的格式,其中type代表间隔类型,value代表间隔数值。 type目前支持8种时间类型(YEAR、QUARTER、MONTH、WEEK、DAY、HOUR、MINUTE、SECOND)。 value代表间隔数值,必须是正整数;当type为SECOND类型时,间隔不能小于60。

INTERVAL RANGE分区表转化为其他任意类型的表,这里partition_options是可选的。

ALTER TABLE table_name table_definition
[partition_options];

示例:

将其他类型表转为INTERVAL RANGE表:

CREATE TABLE orders(
  orderkey BIGINT NOT NULL,
  custkey BIGINT NOT NULL,
  orderdate DATE NOT NULL
);
​
ALTER TABLE orders
PARTITION BY RANGE COLUMNS(orderdate) INTERVAL(MONTH, 1) (
  PARTITION p0 VALUES LESS THAN('2021-10-01')
);

将INTERVAL RANGE表转化为其他类型表:

CREATE TABLE orders (a INT, b DATETIME)
PARTITION BY RANGE (a) INTERVAL(10)
(
    PARTITION p0 VALUES LESS THAN(10),
    PARTITION p2 VALUES LESS THAN(20)
);
​
ALTER TABLE orders PARTITION BY LIST COLUMNS (a)
(
    PARTITION p0 VALUES IN (1, 11, 25)
);

修改INTERVAL RANGE表的INTERVAL子句信息:

CREATE TABLE orders (a INT, b DATETIME)
PARTITION BY RANGE (a) INTERVAL(10)
(
    PARTITION p0 VALUES LESS THAN(10),
    PARTITION p2 VALUES LESS THAN(20)
);
​
ALTER TABLE orders PARTITION BY RANGE (a) INTERVAL(20)
(
    PARTITION p0 VALUES LESS THAN(10),
    PARTITION p2 VALUES LESS THAN(20)
);
​
# 消除INTERVAL子句
ALTER TABLE orders PARTITION BY RANGE (a)
(
    PARTITION p0 VALUES LESS THAN(10),
    PARTITION p2 VALUES LESS THAN(20)
);
​
# 添加INTERVAL子句
ALTER TABLE orders PARTITION BY RANGE (a) INTERVAL(100)
(
    PARTITION p0 VALUES LESS THAN(10),
    PARTITION p2 VALUES LESS THAN(20)
);

SET INTERVAL子句支持

支持使用SET INTERVAL子句修改INTERVAL RANGE表定义的INTERVAL子句间隔类型和间隔值,也可实现消除或添加INTERVAL子句。

语法:

ALTER TABLE table_name SET INTERVAL {() | (type, value) | (value)};
表4 参数说明

参数名称

参数说明

type

目前支持8种时间类型(YEAR、QUARTER、MONTH、WEEK、DAY、HOUR、MINUTE、SECOND),不显式指定默认是数字类型的间隔。

value

指定间隔的数值大小。当type为SECOND类型时,间隔不能小于60。

示例:

修改INTERVAL RANGE表的INTERVAL类型和值。

CREATE TABLE orders(
  orderkey BIGINT NOT NULL,
  custkey BIGINT NOT NULL,
  orderdate DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(orderdate) INTERVAL(MONTH, 1) (
  PARTITION p0 VALUES LESS THAN('2021-10-01')
);
​
ALTER TABLE orders SET INTERVAL(YEAR, 1);

实现RANGE表和INTERVAL RANGE表之间的转化。

CREATE TABLE orders(
  orderkey BIGINT NOT NULL,
  custkey BIGINT NOT NULL,
  orderdate DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(orderdate) INTERVAL(MONTH, 1) (
  PARTITION p0 VALUES LESS THAN('2021-10-01')
);
​
# 删除INTERVAL子句
ALTER TABLE sales SET INTERVAL();
​
# 添加INTERVAL子句
ALTER TABLE sales SET INTERVAL(DAY, 60);

即使当前功能开关rds_interval_range_enabled关闭,"ALTER TABLE table_name SET INTERVAL()"语句也可使用(用于消除INTERVAL RANGE表的INTERVAL子句定义信息,转化为普通RANGE表)。