Create LIST-HASH Partitioned Tables for Efficient Data Distribution - PolarDB for MySQL

LIST-HASH partitioning splits a table into LIST partitions, then further divides each partition into HASH or KEY subpartitions. For example, a table with 3 LIST partitions and 2 HASH subpartitions per partition results in 3 × 2 = 6 physical partitions total—combining value-based routing with even data distribution.

Syntax

CREATE TABLE [schema.]table_name
  table_definition
    PARTITION BY LIST {(expr) | COLUMNS (column_list)}
    SUBPARTITION BY {
      [LINEAR] HASH (expr) [SUBPARTITIONS number]
      | [LINEAR] KEY [ALGORITHM={1|2}] (column_list)
    }
    (partition_definition [, partition_definition] ...);

Where partition_definition is:

PARTITION partition_name
  VALUES IN (value_list)
  (subpartition_definition [, subpartition_definition] ...)

And subpartition_definition is:

SUBPARTITION subpartition_name

Parameters

Parameter	Description
`expr`	The partition expression. Must be `INT` type; string types are not supported.
`number`	The number of subpartitions per partition.
`column_list`	The partition key columns for `LIST COLUMNS` or `KEY` subpartitioning. Expressions are not supported; column names only.
`value_list`	The boundary values that define each LIST partition.
`partition_name`	The partition name. Must be unique within the table.
`subpartition_name`	The subpartition name. Must be unique within the table.

Examples

Create a list-hash partitioned table

The following example partitions sales_list_hash by amount (LIST), then subpartitions each partition into 2 HASH buckets based on dept_no.

CREATE TABLE sales_list_hash
(
  dept_no   INT,
  part_no   INT,
  country   VARCHAR(20),
  date      DATE,
  amount    INT
)
PARTITION BY LIST(amount)
SUBPARTITION BY HASH(dept_no) SUBPARTITIONS 2
(
  PARTITION p0 VALUES IN (1, 2),
  PARTITION p1 VALUES IN (3, 4),
  PARTITION p2 VALUES IN (5, 6)
);

This creates 3 × 2 = 6 physical partitions in total.

Create a list columns-hash partitioned table

Use LIST COLUMNS to partition by a non-integer column such as country. The following example creates 3 LIST COLUMNS partitions, each split into 2 HASH subpartitions.

CREATE TABLE sales_list_columns_hash
(
  dept_no   INT,
  part_no   INT,
  country   VARCHAR(20),
  date      DATE,
  amount    INT
)
PARTITION BY LIST COLUMNS(country)
SUBPARTITION BY HASH(dept_no) SUBPARTITIONS 2
(
  PARTITION europe  VALUES IN ('FRANCE', 'ITALY'),
  PARTITION asia    VALUES IN ('INDIA', 'PAKISTAN'),
  PARTITION americas VALUES IN ('US', 'CANADA')
);

This creates 3 × 2 = 6 physical partitions in total.