如何创建LIST DEFAULT HASH混合分区表 - 云原生数据库 PolarDB

本文介绍了创建和修改LIST DEFAULT HASH分区表的方法。

背景信息

PolarDB在同一级别支持两种分区类型：LIST和HASH。前面是普通的LIST分区，不符合LIST分区规则的数据会放在DEFAULT分区里，DEFAULT分区如果有多个分区则根据HASH规则计算。LIST DEFAULT HASH分区类型常用在LIST VALUES分布不均匀以及无法全部枚举的场景。图示如下：

使用限制

集群版本需满足如下条件之一，您可以通过查询版本号来确认集群版本：
- PolarDB MySQL版8.0.1版本且修订版本为8.0.1.1.34及以上。
- PolarDB MySQL版8.0.2版本且修订版本为8.0.2.2.1及以上。
一级DEFAULT PARTITION支持一个或多个DEFAULT分区。
支持二级分区LIST+DEFAULT，但每个PARTITION仅支持一个二级DEFAULT分区。
一级分区一个DEFAULT分区的情况下，支持所有类型的二级分区。
一级分区多个DEFAULT分区的情况下，仅支持HASH或KEY二级分区。

创建LIST DEFAULT HASH分区表

语法

PolarDB支持新的分区类型，前面是普通的LIST分区，不在LIST分区中的数据放在DEFAULT分区，如果DEFAULT分区太大可以根据HASH规则分成多个DEFAULT分区：

CREATE TABLE [ schema. ]table_name
 table_definition
   PARTITION BY LIST [COLUMNS] (expr)
   SUBPARTITION BY ...
   (list_partition_definition[, ..., list_partition_definition],
    default_partition_definition
   )

其中，default_partition_definition为：

PARTITION partition_name DEFAULT [PARTITIONS number]

每个分区的定义也可以包含二级分区，二级分区也支持使用LIST DEFAULT分区，定义如下：

SUBPARTITION subpartition_name DEFAULT

参数说明

参数名称	参数说明
table_name	要创建的表名称。
partition_name	只有一个DEFAULT分区时，表示分区名称。不可与其他分区表重复。当有多个DEFAULT分区时，表示分区名称前缀。“partition_name+序号”表示分区名称。
subpartition_name	子分区名称。在同一个表中不可重复，子分区最多只支持一个DEFAULT分区。
number	DEFAULT分区按照哈希规则分成number个分区，通过number指定分区个数。PARTITIONS number是可选项，不指定时，则默认为一个DEFAULT分区。

示例

创建单个DEFAULT分区示例如下：

CREATE TABLE list_default (
  a INT,
  b INT
)
PARTITION BY LIST (a)
(PARTITION p0 VALUES IN (1,2,3,4,5),
 PARTITION p1 VALUES IN (6,7,8,9,10),
 PARTITION pd DEFAULT);

创建多个DEFAULT分区示例如下：

CREATE TABLE list_default_hash (
  a INT,
  b INT
)
PARTITION BY LIST (a)
(PARTITION p0 VALUES IN (1,2,3,4,5),
 PARTITION p1 VALUES IN (6,7,8,9,10),
 PARTITION pd DEFAULT PARTITIONS 3);

分区键字段类型为VARCHAR时，使用LIST COLUMNS示例如下：

CREATE TABLE t_goods
(
  country   VARCHAR(30),
  year      VARCHAR(60),
  goods     TEXT
) PARTITION BY LIST COLUMNS(country)
(
  PARTITION p1 VALUES IN ('China'),
  PARTITION p2 VALUES IN ('USA'),
  PARTITION p3 VALUES IN ('Asia'),
  PARTITION p3 VALUES IN ('Singapore'),
  PARTITION p_deft DEFAULT PARTITIONS 5
);

通过explain查看分区：

EXPLAIN SELECT * FROM list_default_hash;

显示结果如下：

+----+-------------+-------------------+-------------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table             | partitions        | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------------------+-------------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | list_default_hash | p0,p1,pd0,pd1,pd2 | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------------------+-------------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.04 sec)

二级分区支持LIST DEFAULT类型，示例如下：

CREATE TABLE test (a int, b int)
PARTITION BY RANGE(a)
SUBPARTITION BY LIST(b) (
PARTITION part0 VALUES LESS THAN (10)
( SUBPARTITION sub0 VALUES IN (1,2,3,4,5),
  SUBPARTITION sub1 DEFAULT),
PARTITION part1 VALUES LESS THAN (20)
( SUBPARTITION sub2 VALUES IN (1,2,3,4,5),
  SUBPARTITION sub3 DEFAULT),
PARTITION part2 VALUES LESS THAN (30)
( SUBPARTITION sub4 VALUES IN (1,2,3,4,5),
  SUBPARTITION sub5 DEFAULT));

一级分区存在多个LIST DEFAULT HASH分区的情况下，仅支持HASH或KEY二级分区：

CREATE TABLE list_default_hash_sub (
  a INT,
  b INT
)
PARTITION BY LIST (a)
SUBPARTITION BY HASH (b) SUBPARTITIONS 20
(PARTITION p0 VALUES IN (1,2,3,4,5),
 PARTITION p1 VALUES IN (6,7,8,9,10),
 PARTITION pd DEFAULT PARTITIONS 3);

修改LIST DEFAULT HASH分区表

LIST DEFAULT HASH分区支持ALTER TABLE ADD PARTITION、ALTER TABLE DROP PARTITION、ALTER TABLE REORGANIZE PARTITION、ALTER TABLE TRUNCATE PARTITION、ALTER TABLE EXCHANGE PARTITION、ALTER TABLE OPTIMIZE PARTITIONALTER TABLE REBUILD PARTITION、ALTER TABLE REPAIR PARTITION、ALTER TABLE ANALYZE PARTITION、ALTER TABLE CHECK PARTITION操作。

本文仅介绍ALTER TABLE ADD PARTITION、ALTER TABLE DROP PARTITION、ALTER TABLE REORGANIZE PARTITION的使用方法，修改LIST DEFAULT HASH分区表的其它操作请参见修改分区表。

ALTER TABLE ADD PARTITION

ADD DEFAULT PARTITION

语法

对于没有DEFAULT分区的普通LIST分区表，通过ADD PARTITION增加DEFAULT分区，使之变成LIST DEFAULT HASH分区表。

ALTER TABLE table_name ADD PARTITION(default_partition_definition)

示例

增加一个DEFAULT分区示例如下：

CREATE TABLE list_tab (
  a INT,
  b INT
)
PARTITION BY LIST (a)
(PARTITION p0 VALUES IN (1,2,3,4,5),
 PARTITION p1 VALUES IN (6,7,8,9,10)
);
ALTER TABLE list_tab ADD PARTITION(PARTITION pd DEFAULT);

增加两个DEFAULT分区示例如下：

CREATE TABLE list_tab (
  a INT,
  b INT
)
PARTITION BY LIST (a)
(PARTITION p0 VALUES IN (1,2,3,4,5),
 PARTITION p1 VALUES IN (6,7,8,9,10)
);
ALTER TABLE list_tab ADD PARTITION(PARTITION pd DEFAULT PARTITIONS 2);

ADD LIST PARTITION
PolarDB MySQL版8.0.2.2.11及以上版本，LIST DEFAULT HASH分区表ALTER TABLE ADD PARTITION语法支持使用WITHOUT VALIDATION选项添加LIST分区。
您需要确保新增分区的LIST VALUES是新增的数据，即在DEFAULT分区中没有符合新增LIST规则的数据。否则，请使用ALTER TABLE REORGANIZE PARTITION语法，从DEFAULT分区中分离部分数据，建立新的LIST分区。
语法
```
ALTER TABLE table_name ADD PARTITION(
  list_partition_definition[, ..., list_partition_definition])
WITHOUT VALIDATION
```
示例
新增一个LIST分区。
```
CREATE TABLE list_default_hash (
  a INT,
  b INT
)
PARTITION BY LIST (a)
(PARTITION p0 VALUES IN (1,2,3,4,5),
 PARTITION p1 VALUES IN (6,7,8,9,10),
 PARTITION pd DEFAULT PARTITIONS 3);

ALTER TABLE list_default_hash ADD PARTITION(
  PARTITION p2 VALUES IN (11,12,13)
)WITHOUT VALIDATION;
```
执行后，list_default_hash表会增加一个LIST分区p2，p2中没有数据。
说明
您需要确保DEFAULT分区中参数a的值不为11、12、13，否则，增加LIST分区后在DEFAULT分区里的这些数据可能会查不到。

ALTER TABLE DROP PARTITION

关于DROP PARTITION的语法介绍请参见DROP PARTITION。

示例

DROP PARTITION操作时，只能一次性删除全部DEFAULT分区，不支持只删除部分DEFAULT分区。

执行DROP PARTITION操作，删除所有分区。

ALTER TABLE list_default_hash DROP PARTITION pd0,pd1,pd2;
Query OK, 0 rows affected (0.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

报错

单独删除部分DEFAULT分区时会报错。

ALTER TABLE list_default_hash DROP PARTITION pd0;

报错信息如下：

ERROR 8078 (HY000): DROP PARTITION cannot be used on default partitions of LIST DEFAULT, except once dropping all default partitions

ALTER TABLE REORGANIZE PARTITION

关于REORGANIZE PARTITION的语法介绍请参见REORGANIZE PARTITION。

示例

REORGANIZE PARTITION操作时，只能一次性修改全部DEFAULT分区，不支持只修改部分DEFAULT分区。

使用REORGANIZE PARTITION操作可以改变DEFAULT分区的个数：
```
ALTER TABLE list_default_hash
REORGANIZE PARTITION
  pd0,pd1
INTO(
  PARTITION pd DEFAULT PARTITIONS 3);
```
执行后，DEFAULT分区的个数会由2个变成3个。
使用REORGANIZE PARTITION可以从DEFAULT分区中分离出一个LIST分区：
```
ALTER TABLE list_default_hash
REORGANIZE PARTITION
  pd0,pd1
INTO (
 PARTITION p2 VALUES IN (20,21),
 PARTITION pd DEFAULT PARTITIONS 2);
```
执行后，list_default_hash分区表会增加一个LIST分区p2，p2中会有从DEFAULT分区中分离出来的符合VALUES IN (20,21)的数据。
使用REORGANIZE PARTITION可以合并一个LIST分区到DEFAULT分区：
```
ALTER TABLE list_default_hash
REORGANIZE PARTITION
  p2, pd0, pd1
INTO (
 PARTITION pd DEFAULT PARTITIONS 2);
```
执行后，LIST分区p2会合并到DEFAULT分区里。
使用REORGANIZE PARTITION可以从DEFAULT分区中分离部分values放到LIST分区：
```
ALTER TABLE list_default
REORGANIZE partition
  p2, pd0, pd1
INTO (
  PARTITION p2 VALUES IN (20,21,22,23,24),
  PARTITION pd DEFAULT PARTITIONS 4);
```
执行后，p2的定义由PARTITION p2 VALUES IN (20,21)变为PARTITION p2 VALUES IN (20,21,22,23,24)，相应的数据也会从DEFAULT分区挪到p2。

云原生数据库 PolarDB：LIST DEFAULT HASH