如何建立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