热点分区分裂

注意事项

• 本文要求PolarDB-X实例版本必须为5.4.14-16539836及以上；

• 若要对二级分区进行分区分裂操作，要求PolarDB-X实例版本必须为5.4.17-16952556及以上。

• 对于热点分裂，只适用于包含key分区策略（一级分区或者二级分区是key分区皆可）的数据表。

名词解释

• 表组：分区列完全相同的一组逻辑表或全局索引表的集合。

• 全局索引：使用另一个维度进行水平分区的数据与主表始终保持强一致的分区表。

语法

在PolarDB-X的key分区策略中，采用了一致性哈希算法作为路由机制。当创建分区表时，系统会按照表的分区定义，自动将一段连续的哈希空间平均地分配给各个分区。对于有两个或更多分区键的表，只有首个分区键默认参与哈希空间的初步分配。其他分区键则以最大值（maxvalue）的方式预留，构成了一个多维向量空间，以备将来之需。

当第一个分区键的特定值累积了大量的数据行（例如订单表的大客户），形成所谓的热点数据时，PolarDB-X可通过基于第二个分区键的哈希值进行二次散列，有效地打散热点，并重新平衡数据的分布。此外，如果第一个与第二个分区键的值组合同样导致热点问题，系统可以进一步根据第三个分区键进行散列，以此类推，确保数据的均匀分布，优化整体性能。这种多级散列策略可以动态调整分区，消除热点带来的影响。

语法如下：

ALTER { TABLE tbl_name | TABLEGROUP tg_name | TABLEGROUP BY TABLE tbl_name } 
		split_partition_by_hot_value_definition
  | split_template_subpartition_by_hot_value_definition
  | split_non_template_subpartition_by_hot_value_definition

  
split_partition_by_hot_value_definition:
SPLIT INTO [part_name_prefix] PARTITIONS partition_count BY HOT VALUE(hot_value)

split_template_subpartition_by_hot_value_definition:
SPLIT INTO [subpart_name_prefix] SUBPARTITIONS subpartition_count BY HOT VALUE(hot_value)

split_non_template_subpartition_by_hot_value_definition:
MODIFY PARTITION part_name SPLIT INTO [subpart_name_prefix] SUBPARTITIONS subpartition_count BY HOT VALUE(hot_value)

场景1：目标分区是不包含二级分区的一级key分区

表级用法

假设订单表orders的定义为：

create table orders ( id int(11) not null auto_increment, 
 seller_id int(11) default null, 
 primary key (id) )
 partition by key(seller_id, id) partitions 5

以seller_id=88大卖家的订单数据为例，可以通过以下语法将该大卖家的订单数据打散到10个新分区中，指定分区名称都以hot88_为前缀，带上此前缀是为了标识哪些分区是热点散列分裂出来的：

alter table orders split into hot88_ partitions 10 by hot value(88)

特别地，对于拆分键只有一列的分区表，例如：

create table orders ( id int(11) not null auto_increment, 
 seller_id int(11) default null, 
 primary key (id) )
 partition by key(seller_id) partitions 5

在需要将热点卖家数据seller_id=88打散之前，需为表添加第二个分区键，这样热点散列才能根据第二个分区键的哈希空间做二次散列，添加拆分键（以ID列为例）的语法如下：

alter table orders partition by key(seller_id,id) partitions 5;

表组级用法

和表级类似，仅需将语法规则alter table #tb改成alter tablegroup #tgname或alter tablegroup by #tb。

场景2：目标分区是包含有二级分区的一级分区

表级用法

对于以下分区表：

create table t1 (
a bigint unsigned not null,
b bigint unsigned not null,
c datetime NOT NULL,
d varchar(16) NOT NULL,
e varchar(16) NOT NULL
)
partition by key (a,b) partitions 4
subpartition by range columns (c,d)
(
partition p1
(
subpartition p1sp1 values less than ( '2020-01-01', 'abc' ),
subpartition p1sp2 values less than ( maxvalue, maxvalue )
),
partition p2
(
subpartition p2sp1 values less than ( '2020-01-01', 'abc' ),
subpartition p2sp2 values less than ( '2021-01-01', 'abc' ),
subpartition p2sp3 values less than ( '2022-01-01', 'abc' ),
subpartition p2sp4 values less than ( maxvalue, maxvalue )
),
partition p3
(
subpartition p3sp1 values less than ( '2020-01-01', 'abc' ),
subpartition p3sp2 values less than ( maxvalue, maxvalue )
),
partition p4
(
subpartition p4sp1 values less than ( '2020-01-01', 'abc' ),
subpartition p4sp2 values less than ( maxvalue, maxvalue )
)
);

如果对一级分区的分区键热点值做热点散列，例如将a=66的热点数据散列成5份，语法与场景1类似：

alter table t1 split into hot66_ partitions 5 by hot value(66);

热点散列后，执行show create table t1可以观察到表结构应该类似（分区名字会略有差异），分裂后新的热点分区的子分区定义和原热点数据所在的分区是一致的，并没有发生变化。

show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
	`a` bigint(20) UNSIGNED NOT NULL,
	`b` bigint(20) UNSIGNED NOT NULL,
	`c` datetime NOT NULL,
	`d` varchar(16) NOT NULL,
	`e` varchar(16) NOT NULL,
	KEY `auto_shard_key_a_b` USING BTREE (`a`, `b`),
	KEY `auto_shard_key_c_d` USING BTREE (`c`, `d`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`a`,`b`)
SUBPARTITION BY RANGE COLUMNS(`c`,`d`)
(PARTITION `p1`
 (SUBPARTITION `p1sp1` VALUES LESS THAN ('2020-01-01 00:00:00','abc') ENGINE = InnoDB,
 SUBPARTITION `p1sp2` VALUES LESS THAN (MAXVALUE,MAXVALUE) ENGINE = InnoDB),
 PARTITION `p2`
 (SUBPARTITION `p2sp1` VALUES LESS THAN ('2020-01-01 00:00:00','abc') ENGINE = InnoDB,
 SUBPARTITION `p2sp2` VALUES LESS THAN ('2021-01-01 00:00:00','abc') ENGINE = InnoDB,
 SUBPARTITION `p2sp3` VALUES LESS THAN ('2022-01-01 00:00:00','abc') ENGINE = InnoDB,
 SUBPARTITION `p2sp4` VALUES LESS THAN (MAXVALUE,MAXVALUE) ENGINE = InnoDB),
 PARTITION `p3`
 (SUBPARTITION `p3sp1` VALUES LESS THAN ('2020-01-01 00:00:00','abc') ENGINE = InnoDB,
 SUBPARTITION `p3sp2` VALUES LESS THAN (MAXVALUE,MAXVALUE) ENGINE = InnoDB),
 PARTITION `p5`
 (SUBPARTITION `sp7` VALUES LESS THAN ('2020-01-01 00:00:00','abc') ENGINE = InnoDB,
 SUBPARTITION `sp8` VALUES LESS THAN (MAXVALUE,MAXVALUE) ENGINE = InnoDB),
 PARTITION `hot66_1`
 (SUBPARTITION `sp9` VALUES LESS THAN ('2020-01-01 00:00:00','abc') ENGINE = InnoDB,
 SUBPARTITION `sp10` VALUES LESS THAN (MAXVALUE,MAXVALUE) ENGINE = InnoDB),
 PARTITION `hot66_2`
 (SUBPARTITION `sp11` VALUES LESS THAN ('2020-01-01 00:00:00','abc') ENGINE = InnoDB,
 SUBPARTITION `sp12` VALUES LESS THAN (MAXVALUE,MAXVALUE) ENGINE = InnoDB),
 PARTITION `hot66_3`
 (SUBPARTITION `sp13` VALUES LESS THAN ('2020-01-01 00:00:00','abc') ENGINE = InnoDB,
 SUBPARTITION `sp14` VALUES LESS THAN (MAXVALUE,MAXVALUE) ENGINE = InnoDB),
 PARTITION `hot66_4`
 (SUBPARTITION `sp15` VALUES LESS THAN ('2020-01-01 00:00:00','abc') ENGINE = InnoDB,
 SUBPARTITION `sp16` VALUES LESS THAN (MAXVALUE,MAXVALUE) ENGINE = InnoDB),
 PARTITION `hot66_5`
 (SUBPARTITION `sp17` VALUES LESS THAN ('2020-01-01 00:00:00','abc') ENGINE = InnoDB,
 SUBPARTITION `sp18` VALUES LESS THAN (MAXVALUE,MAXVALUE) ENGINE = InnoDB),
 PARTITION `p6`
 (SUBPARTITION `sp19` VALUES LESS THAN ('2020-01-01 00:00:00','abc') ENGINE = InnoDB,
 SUBPARTITION `sp20` VALUES LESS THAN (MAXVALUE,MAXVALUE) ENGINE = InnoDB))

表组级用法

和表级类似，仅需将语法规则alter table #tb改成alter tablegroup #tgname或alter tablegroup by #tb。

场景3：目标分区是模板化的二级分区

表级用法

对于以下带模板二级分区的表tb1：

create table tb1 (
a bigint unsigned not null,
b bigint unsigned not null,
c datetime NOT NULL,
d varchar(16) NOT NULL,
e varchar(16) NOT NULL
)
partition by key (c,d) partitions 3
subpartition by key (a,b) subpartitions 3;

如果二级分区出现热点数据，例如a=55是个大卖家用户数据，可以通过以下语法将其做热点数据散列到5个新分区：

alter table tb1 split into hot55_ subpartitions 5 by hot value(55);

执行该命令后，所有一级分区下的子分区a=55的数据都会被散列。

表组级用法

和表级类似，仅需将语法规则alter table #tb改成alter tablegroup #tgname或alter tablegroup by #tb。

场景4：目标分区是非模板化的二级分区

表级用法

对于以下带非模版二级分区的表tb1：

create table tb1 (
a bigint unsigned not null,
b bigint unsigned not null,
c datetime NOT NULL,
d varchar(16) NOT NULL,
e varchar(16) NOT NULL
)
partition by key (c,d) partitions 2
subpartition by key (a,b)
(
partition p1 subpartitions 2,
partition p2 subpartitions 4
);

如果p1下的二级分区出现热点数据，例如a=55是个大卖家用户数据，可以通过以下语法将其做热点数据散列到5个新分区：

alter table tb1 modify partition p1 split into hot55_ subpartitions 5 by hot value(55);

执行该命令后p1分区下的子分区a=55的数据都会被散列。

表组级用法

和表级类似，仅需将语法规则alter table #tb改成alter tablegroup #tgname或alter tablegroup by #tb即可。