Split PolarDB-X KEY Table Hotspot Partitions for Even Load Distribution - PolarDB-X

In a KEY-partitioned table, data is distributed based on the hash of the first partition key. When a specific key value — for example, the ID of a top seller — concentrates a disproportionate share of rows in one partition, that partition becomes a hotspot and degrades overall query performance.

PolarDB-X lets you resolve this by splitting a hotspot partition: rows matching a specified key value are redistributed into a set of new partitions using the next available partition key for secondary hashing. All other rows remain in their existing partitions, unaffected.

Prerequisites

Before splitting a hotspot partition, confirm the following:

The PolarDB-X instance version is 5.4.14-16539836 or later for hotspot partition splits
The PolarDB-X instance version is 5.4.17-16952556 or later for hotspot subpartition splits
The target table uses KEY partitioning — the table must contain KEY partitions or KEY subpartitions

How it works

PolarDB-X KEY partitioning routes rows to partitions using consistent hashing. When a table has two or more partition keys, only the first key determines hash space allocation by default. The remaining keys are reserved as MAXVALUE, forming a multi-dimensional vector space for future redistribution.

When a specific value in the first partition key concentrates data in one partition, you can trigger secondary hashing using the second partition key to redistribute those rows across new partitions. If the first two keys together still produce a hotspot, secondary hashing can extend to the third key, and so on.

Only the rows matching the specified hot value move into the new partitions. The partition count and naming prefix you specify determine how many new partitions are created and how they are named.

Syntax

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)

ALTER TABLEGROUP BY TABLE tbl_name automatically resolves the table group from the table name. It produces the same result as ALTER TABLEGROUP tg_name.

Parameters

Parameter	Description
`tbl_name`	The table to split
`tg_name`	The table group to split
`part_name_prefix`	Name prefix for the new partitions. Use a meaningful prefix (for example, `hot88_`) to identify which partitions were split from a hotspot
`partition_count`	Number of new partitions to create for the hot value
`hot_value`	The specific partition key value identifying the hotspot
`subpart_name_prefix`	Name prefix for new subpartitions
`subpartition_count`	Number of new subpartitions to create for the hot value
`part_name`	The specific partition containing the hotspot subpartition (non-templated subpartitions only)

Scenario 1: Split a KEY partition without subpartitions

Split a table partition

The orders table is partitioned by (seller_id, id) with 5 partitions:

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 is a major seller whose data is concentrated in one partition. To redistribute that seller's data across 10 new partitions with names prefixed hot88_:

ALTER TABLE orders SPLIT INTO hot88_ PARTITIONS 10 BY HOT VALUE(88);

After running this statement, rows where seller_id = 88 are distributed across the 10 new hot88_* partitions. All other sellers' data remains in the original partitions.

Add a second partition key (single-key tables only)

If the table was originally defined with only one partition key, secondary hashing is not possible. Add a second key before splitting:

-- Original single-key table
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;

-- Add a second partition key to enable secondary hashing
ALTER TABLE orders PARTITION BY KEY(seller_id, id) PARTITIONS 5;

-- Now split the hotspot
ALTER TABLE orders SPLIT INTO hot88_ PARTITIONS 10 BY HOT VALUE(88);

Split partitions in a table group

Splitting a partition in a table group splits the corresponding partitions in all tables within the table group.

Replace ALTER TABLE tbl_name with ALTER TABLEGROUP tg_name or ALTER TABLEGROUP BY TABLE tbl_name:

ALTER TABLEGROUP tg_name SPLIT INTO hot88_ PARTITIONS 10 BY HOT VALUE(88);
-- or
ALTER TABLEGROUP BY TABLE orders SPLIT INTO hot88_ PARTITIONS 10 BY HOT VALUE(88);

Scenario 2: Split a partition that contains subpartitions

After splitting, the new partitions inherit the subpartition definitions of the original hotspot partition.

Split a table partition

The t1 table uses KEY partitioning on (a, b) with non-templated RANGE COLUMNS subpartitions on (c, d):

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)
  )
);

To redistribute rows where a = 66 across 5 new partitions:

ALTER TABLE t1 SPLIT INTO hot66_ PARTITIONS 5 BY HOT VALUE(66);

To verify the result, run SHOW CREATE TABLE t1. The output shows the new hot66_1 through hot66_5 partitions, each carrying the same subpartition structure as the original hotspot partition:

SHOW 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))

The five new hot66_* partitions each carry two subpartitions with the same RANGE COLUMNS boundaries as the original hotspot partition's subpartitions. Rows where a = 66 are evenly distributed across these five partitions; rows with other values of a remain in their original partitions.

Split partitions in a table group

Splitting a partition in a table group splits the corresponding partitions in all tables within the table group.

Replace ALTER TABLE tbl_name with ALTER TABLEGROUP tg_name or ALTER TABLEGROUP BY TABLE tbl_name.

Scenario 3: Split a templated subpartition

Split a table subpartition

The tb1 table uses KEY partitioning on (c, d) with templated KEY subpartitions on (a, b):

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 (a major seller) causes a subpartition hotspot. To redistribute those rows across 5 new subpartitions under each partition:

ALTER TABLE tb1 SPLIT INTO hot55_ SUBPARTITIONS 5 BY HOT VALUE(55);

After this statement, rows where a = 55 under all partitions are distributed across 5 new hot55_* subpartitions. Rows with other values of a remain in their original subpartitions.

Split subpartitions in a table group

Splitting a subpartition in a table group splits the corresponding subpartitions in all tables within the table group.

Replace ALTER TABLE tbl_name with ALTER TABLEGROUP tg_name or ALTER TABLEGROUP BY TABLE tbl_name.

Scenario 4: Split a non-templated subpartition

Split a table subpartition

The tb1 table uses KEY partitioning on (c, d) with non-templated KEY subpartitions:

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
);

a = 55 causes a hotspot in p1. To split those rows into 5 new subpartitions under p1:

ALTER TABLE tb1 MODIFY PARTITION p1 SPLIT INTO hot55_ SUBPARTITIONS 5 BY HOT VALUE(55);

After this statement, rows where a = 55 under partition p1 are distributed across the 5 new hot55_* subpartitions. Rows with other values of a in p1 remain in the original subpartitions.

Split subpartitions in a table group

Splitting a subpartition in a table group splits the corresponding subpartitions in all tables within the table group.

Replace ALTER TABLE tbl_name with ALTER TABLEGROUP tg_name or ALTER TABLEGROUP BY TABLE tbl_name.

Key concepts

Term	Definition
Table group	A collection of logical tables or global index tables that share identical partition key columns
Global index	An index on a partitioned table built on non-partition key columns; supports unique constraints
KEY partitioning	A partitioning method that routes rows to partitions based on hash values computed by the consistent hashing algorithm
Hotspot partition	A partition that receives a disproportionately large share of rows due to a concentrated value in the first partition key