Description of MatrixOne DDL statement partition support

1. Partition types supported by MatrixOne

Currently, the 6 partition types supported by MatrixOne’s DDL statements are basically the same as the MySQL official website, as follows:

  • KEY partitioning

  • HASH partitioning

  • RANGE partitioning

  • RANGE COLUMNS partitioning

  • LIST partitioning

  • LIST COLUMNS partitioning

2. Description of partition keys

The relationship between Partition Keys, Primary Keys and Unique Keys

The relationship rules of Partition Keys, Primary Keys and Unique Keys can be summarized as:

All columns used in the partition expression of a partition table must be part of each unique key that the table may have.

Note

The only keys include PrimaryKey and Unique KEY.

​ That is, each unique key on the table must use each column in the table’s partition expression. The unique key also includes the primary key of the table, because by definition, the primary key of the table is also a unique key.

Example Description

For example, each of the following table creation statements is invalid:

> CREATE TABLE t1 (
      col1 INT NOT NULL,
      col2 DATE NOT NULL,
      col3 INT NOT NULL,
      col4 INT NOT NULL,
      UNIQUE KEY (col1, col2)
  )
  PARTITION BY HASH(col3)
  PARTITIONS 4;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

> CREATE TABLE t2 (
     col1 INT NOT NULL,
     col2 DATE NOT NULL,
     col3 INT NOT NULL,
     col4 INT NOT NULL,
     UNIQUE KEY (col1),
     UNIQUE KEY (col3)
 )
  PARTITION BY HASH(col1 + col3)
  PARTITIONS 4;

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

About KEY Partition Key is NULL

  1. KEY accepts only lists of zero or more column names. In the case where the table has a primary key, any column used as a partition key must contain part or all of the table primary key.

    If no column name is specified as partition key, the primary key of the table is used (if any). For example, the following CREATE TABLE statement is valid in MySQL.

  2. If there is no primary key, but UNIQUE KEY, then UNIQUE KEY is used for partition keys.

For example, in the following table creation statement, the KEY partition partition key is NULL, and no primary key is defined, but it contains a unique key. When building a partition expression, a unique key is used as the partition key:

CREATE TABLE t1 (
	col1 INT NOT NULL,
	col2 DATE NOT NULL,
	col3 INT NOT NULL,
	col4 INT NOT NULL,
	UNIQUE KEY (col1, col2)
)
PARTITION BY KEY()
PARTITIONS 4;

Note

Other partition rules are basically consistent with MySQL.

3. Description of MatrixOne partition expressions

When a DDL statement builds a partition table, a partition expression is generated for each partition definition, which can be used to calculate the partition to which the data belongs.

During the planning construction phase, the partition information data structure in the DDL statement is plan.PartitionInfo:

type PartitionInfo struct {
	Type PartitionType
	Expr *Expr
	PartitionExpression *Expr
	Columns []*Expr
	PartitionColumns []string
	PartitionNum uint64
	Partitions []*PartitionItem
	Algorithm int64
	IsSubPartition bool
	PartitionMsg string
}

Where PartitionExpression is a partition expression. The partition expression is MatrixOne. The method of converting the partition clause into an expression for processing. The construction method of each partition expression is as follows:

KEY Partitioning

The KEY partition will construct a partition expression based on the partition key and the number of partitions. The calculation result of the partition expression is an integer greater than or equal to 0, representing the partition number, and incrementing from zero.

SQL examples are as follows:

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY (col1, col2)
)
PARTITION BY KEY(col1)
PARTITIONS 4;

HASH Partitioning

Similar to KEY partition, the HASH partition will build a partition expression based on the partition function and the number of partitions. The calculation result of the partition expression is an integer greater than or equal to 0, representing the partition number, and incrementing from zero.

SQL examples are as follows:

CREATE TABLE t1 (
    col1 INT,
    col2 CHAR(5),
    col3 DATE
)
PARTITION BY LINEAR HASH( YEAR(col3))
PARTITIONS 6;

RANGE Partitioning

​RANGE partitioning is a method of dividing table data into different partitions based on the range of column values. This partition type is very suitable for data that can be segmented based on the value range of a particular column.

SQL examples are as follows:

CREATE TABLE employees (
	id INT NOT NULL,
	fname VARCHAR(30),
	lname VARCHAR(30),
	hired DATE NOT NULL DEFAULT '1970-01-01',
	separate DATE NOT NULL DEFAULT '9999-12-31',
	job_code INT NOT NULL,
	store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
	PARTITION p0 VALUES LESS THAN (6),
	PARTITION p1 VALUES LESS THAN (11),
	PARTITION p2 VALUES LESS THAN (16),
	PARTITION p3 VALUES LESS THAN MAXVALUE
);

RANGE COLUMNS partitioning

RANGE COLUMNS partition allows the use of a combination of one or more columns as partition keys, each partition defines a range of values ​​corresponding to the combined values ​​of the partition key column. When data is inserted, the partition to which the row belongs is determined based on the values ​​of these columns.

SQL examples are as follows:

CREATE TABLE rc (
	a INT NOT NULL,
	b INT NOT NULL
)
PARTITION BY RANGE COLUMNS(a,b) (
	PARTITION p0 VALUES LESS THAN (10,5) COMMENT = 'Data for LESS THAN (10,5)',
	PARTITION p1 VALUES LESS THAN (20,10) COMMENT = 'Data for LESS THAN (20,10)',
	PARTITION p2 VALUES LESS THAN (50,MAXVALUE) COMMENT = 'Data for LESS THAN (50,MAXVALUE)',
	PARTITION p3 VALUES LESS THAN (65,MAXVALUE) COMMENT = 'Data for LESS THAN (65,MAXVALUE)',
	PARTITION p4 VALUES LESS THAN (MAXVALUE,MAXVALUE) COMMENT = 'Data for LESS THAN (MAXVALUE,MAXVALUE)'
);

LIST Partitioning

LIST partitions divide data based on discrete values ​​of a single column. Each partition contains a specific list of column values. When data is inserted, rows are assigned to the corresponding partition according to the value of the column.

SQL examples are as follows:

CREATE TABLE client_firms (
	id INT,
	name VARCHAR(35)
)
PARTITION BY LIST (id) (
	PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21),
	PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22),
	PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23),
	PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24)
);

LIST COLUMNS partitioning

​LIST COLUMNS partitions are similar to LIST partitions, but allow the use of a combination of one or more columns as partition keys. Partitions are specific lists based on the combination of column values. When inserting rows, their partitions are determined based on the combined values ​​of these columns.

SQL examples are as follows:

CREATE TABLE lc (
	a INT NULL,
	b INT NULL
)
PARTITION BY LIST COLUMNS(a,b) (
	PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ),
	PARTITION p1 VALUES IN( (0,1), (0,2) ),
	PARTITION p2 VALUES IN( (1,0), (2,0) )
);