OceanBase 分区表操作示例 -- cnDBA.cn

1 分区表说明

OceanBase 数据库的 MySQL 模式中，单个表最多支持创建的分区个数为 8192 个。

MySQL模式目前支持的分区类型如下：http://www.cndba.cn/dave/article/131434

1.Range 分区
2.Range Columns 分区
3.List 分区
4.List Columns 分区
5.Hash 分区
6.Key 分区
7.组合分区

1.1 Range 分区

Range 分区的分区键必须是整数类型或 YEAR 类型，如果对其他类型的日期字段分区，则需要使用函数进行转换。Range 分区的分区键仅支持一列。

1.2 Range Columns 分区

Range Columns 分区与 Range 分区的作用基本类似，不同之处在于：Range Columns 分区的分区键的结果不要求是整型，可以是以下数据类型：

1.支持所有整数类型，包括 TINYINT、 SMALLINT、 MEDIUMINT、 INT ( INTEGER) 和 BIGINT。
2.支持时间类型 DATE 和 DATETIME。
3.支持字符串类型 CHAR、 VARCHAR、 BINARY 和 VARBINARY。

Range Columns 分区的分区键不能使用表达式。

Range Columns 分区的分区键可以写多个列（即列向量）。http://www.cndba.cn/dave/article/131434

1.3 List 分区

List 分区的分区键可以是列名，也可以是一个表达式，分区键必须是整数类型或 YEAR 类型。

1.4 List Columns 分区

List Columns 分区与 List 分区的作用基本相同，不同之处在于：

List Columns 分区的分区键不要求是整型，可以是以下数据类型：

1.支持所有整数类型，包括 TINYINT、 SMALLINT、 MEDIUMINT、 INT ( INTEGER) 和 BIGINT。
2.支持时间类型 DATE 和 DATETIME。
3.支持字符串类型 CHAR、 VARCHAR、 BINARY 和 VARBINARY。

不支持将 TEXT 或 BLOB 数据类型的列作为分区键。

List Columns 分区的分区键不能使用表达式。

List Columns 分区支持多个分区键，List 分区仅支持单分区键。

1.5 Hash 分区

Hash 分区通过对分区键上的 Hash 函数值来散列记录到不同分区中。

Hash 分区的分区键必须是整数类型或 YEAR 类型，并且可以使用表达式。

1.6 Key 分区

Key 分区与 Hash 分区类似，也是通过对分区个数取模的方式来确定数据属于哪个分区，不同的是系统会对 Key 分区键做一个内部默认的 Hash 函数后再取模。http://www.cndba.cn/dave/article/131434

Key 分区有如下特点：

1.Key 分区的分区键不要求为整型，可以为除 TEXT 和 BLOB 以外的其他数据类型。
2.Key 分区的分区键不能使用表达式。
3.Key 分区的分区键支持向量。

Key 分区的分区键中不指定任何列时，表示 Key 分区的分区键是主键。

示例如下：

obclient> CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 INT) 
       PARTITION BY KEY() 
       PARTITIONS 5;
Query OK, 0 rows affected

1.7 组合分区

在 MySQL 模式中，使用自增列作为分区键时，应注意以下事项：http://www.cndba.cn/dave/article/131434

1.在 OceanBase 数据库中，自增列的值全局唯一，但在分区内不保证递增。
2.与其他分区方式相比，对使用自增列作为分区键的分区表的插入操作由于无法有效路由，会产生跨机事务，性能会有一定的下降。

分区名字规则

1.对于 List 和 Range 分区，因为在创建表的过程中就指定了分区的名字，所以名字就是当时指定的名字。
2.对于 Hash/Key 分区，创建时如果没有指定分区的名字，分区的命名由系统根据命名规则完成。具体表现为：
(1)当 Hash/Key 分区为一级分区时，则每个分区分别命名为 p0、p1、…、pn。
(2)当 Hash/Key 分区为二级分区时，则每个分区分别命名为 sp0、sp1、 …、spn。
3.对于模板化二级分区表，定义二级分区后，系统根据二级分区的命名规则完成，其二级分区的命名规则为 ($part_name)s($subpart_name)。对于非模板化二级分区表，二级分区的分区名即为⾃定义的分区名。

2 操作示例

2.1 创建分区表

创建一个 Range 分区表 tbl1_log_r：

obclient> CREATE TABLE tbl1_r (log_id BIGINT NOT NULL,log_value VARCHAR(50),log_date TIMESTAMP NOT NULL)
       PARTITION BY RANGE(UNIX_TIMESTAMP(log_date)) 
        (PARTITION M202001 VALUES LESS THAN(UNIX_TIMESTAMP('2020/02/01'))
       , PARTITION M202002 VALUES LESS THAN(UNIX_TIMESTAMP('2020/03/01'))
       , PARTITION M202003 VALUES LESS THAN(UNIX_TIMESTAMP('2020/04/01'))
       , PARTITION M202004 VALUES LESS THAN(UNIX_TIMESTAMP('2020/05/01'))
        );
Query OK, 0 rows affected

创建一个 Range Columns 分区表 tbl1_log_rc：

obclient> CREATE TABLE tbl1_log_rc (log_id BIGINT NOT NULL,log_value VARCHAR(50),log_date DATE NOT NULL)
       PARTITION BY RANGE COLUMNS(log_date) 
        (PARTITION M202001 VALUES LESS THAN('2020/02/01')
       , PARTITION M202002 VALUES LESS THAN('2020/03/01')
       , PARTITION M202003 VALUES LESS THAN('2020/04/01')
       , PARTITION M202004 VALUES LESS THAN('2020/05/01')
       , PARTITION MMAX VALUES LESS THAN MAXVALUE
        );
Query OK, 0 rows affected

创建一个 List 分区表 tbl1_l：

obclient> CREATE TABLE tbl1_l (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50))
       PARTITION BY LIST(col1) 
        (PARTITION p0 VALUES IN (1, 2, 3),
         PARTITION p1 VALUES IN (5, 6),
         PARTITION p2 VALUES IN (DEFAULT)
        );
Query OK, 0 rows affected

创建一个 List Columns 分区表：

obclient> CREATE TABLE tbl1_lc (id INT,partition_id VARCHAR(2))
       PARTITION BY LIST COLUMNS(partition_id)
        (PARTITION p0 VALUES IN ('00','01'),
         PARTITION p1 VALUES IN ('02','03'),
         PARTITION p2 VALUES IN (DEFAULT)
        );
Query OK, 0 rows affected

创建一个 Hash 分区表 tbl1_h：

obclient> CREATE TABLE tbl1_h(col1 INT,col2 VARCHAR(50))
       PARTITION BY HASH(col1) PARTITIONS 60;
Query OK, 0 rows affected

创建一个 Key 分区表 tbl1_k：

obclient> CREATE TABLE tbl1_k(id INT,gmt_create DATETIME,info VARCHAR(20))
       PARTITION BY KEY(id,gmt_create) PARTITIONS 10;
Query OK, 0 rows affected

创建 Range Columns + List Columns 分区的模板化二级分区表：

obclient> CREATE TABLE t_m_rclc(col1 INT,col2 INT) 
       PARTITION BY RANGE COLUMNS(col1)
       SUBPARTITION BY LIST COLUMNS(col2)
       SUBPARTITION TEMPLATE 
        (SUBPARTITION mp0 VALUES IN(1,3),
         SUBPARTITION mp1 VALUES IN(4,6),
         SUBPARTITION mp2 VALUES IN(7)
        )
        (PARTITION p0 VALUES LESS THAN(100),
         PARTITION p1 VALUES LESS THAN(200),
         PARTITION p2 VALUES LESS THAN(300)
        ); 
Query OK, 0 rows affected

创建 List + Range 分区的模板化二级分区表：http://www.cndba.cn/dave/article/131434

obclient> CREATE TABLE t2_m_lr(col1 INT,col2 INT) 
       PARTITION BY LIST (col1)
       SUBPARTITION BY RANGE(col2)
       SUBPARTITION TEMPLATE 
        (SUBPARTITION mp0 VALUES LESS THAN(100),
         SUBPARTITION mp1 VALUES LESS THAN(200),
         SUBPARTITION mp2 VALUES LESS THAN(300)
        )
        (PARTITION p0 VALUES IN(1,3),
         PARTITION p1 VALUES IN(4,6),
         PARTITION p2 VALUES IN(7,9)
        );
Query OK, 0 rows affected

创建 Range + List Columns 分区的非模板化二级分区表：

obclient> CREATE TABLE t2_f_rlc (col1 INT NOT NULL,col2 varchar(50),col3 INT NOT NULL) 
PARTITION BY RANGE(col1)
SUBPARTITION BY LIST COLUMNS(col3)
(PARTITION p0 VALUES LESS THAN(100)
  (SUBPARTITION sp0 VALUES IN(1,3),
   SUBPARTITION sp1 VALUES IN(4,6),
   SUBPARTITION sp2 VALUES IN(7,9)),
 PARTITION p1 VALUES LESS THAN(200)
  (SUBPARTITION sp3 VALUES IN(1,3),
   SUBPARTITION sp4 VALUES IN(4,6),
   SUBPARTITION sp5 VALUES IN(7,9))
); 
Query OK, 0 rows affected

创建 Range Columns + List Columns 分区的非模板化二级分区表：

obclient> CREATE TABLE t2_f_rclc (col1 INT,col2 INT) 
       PARTITION BY RANGE COLUMNS(col1)
       SUBPARTITION BY LIST COLUMNS(col2)
       (PARTITION p0 VALUES LESS THAN(100)
         (SUBPARTITION sp0 VALUES IN(1,3),
          SUBPARTITION sp1 VALUES IN(4,6),
          SUBPARTITION sp2 VALUES IN(7,9)),
        PARTITION p1 VALUES LESS THAN(200)
         (SUBPARTITION sp3 VALUES IN(1,3),
          SUBPARTITION sp4 VALUES IN(4,6),
          SUBPARTITION sp5 VALUES IN(7,9))
       ); 
Query OK, 0 rows affected

查看分区表：

obclient [dave]> select  owner,TABLE_NAME ,PARTITIONING_TYPE ,SUBPARTITIONING_TYPE u from oceanbase.dba_part_tables;
+-------+-------------+-------------------+--------------+
| owner | TABLE_NAME  | PARTITIONING_TYPE | u            |
+-------+-------------+-------------------+--------------+
| dave  | tbl1_r      | RANGE             | NONE         |
| dave  | tbl1_log_rc | RANGE COLUMNS     | NONE         |
| dave  | tbl1_l      | LIST              | NONE         |
| dave  | tbl1_lc     | LIST COLUMNS      | NONE         |
| dave  | tbl1_h      | HASH              | NONE         |
| dave  | tbl1_k      | KEY               | NONE         |
| dave  | t_m_rclc    | RANGE COLUMNS     | LIST COLUMNS |
| dave  | t2_m_lr     | LIST              | RANGE        |
| dave  | t2_f_rlc    | RANGE             | LIST COLUMNS |
| dave  | t2_f_rclc   | RANGE COLUMNS     | LIST COLUMNS |
+-------+-------------+-------------------+--------------+
10 rows in set (0.037 sec)

单表直接转成分区表：

obclient [dave]> CREATE TABLE tbl1(c1 int, c2 datetime);
Query OK, 0 rows affected (0.150 sec)

obclient [dave]> ALTER TABLE tbl1 PARTITION BY HASH(c1) partitions 4;
Query OK, 0 rows affected (1.610 sec)

2.2 添加 / 删除分区

查看分区信息：

obclient [dave]> select dpt.owner,dpt.TABLE_NAME ,dpt.PARTITIONING_TYPE ,dpt.SUBPARTITIONING_TYPE u, dtp.PARTITION_NAME  from oceanbase.DBA_PART_TABLES dpt , oceanbase.DBA_TAB_PARTITIONS dtp where dpt.TABLE_NAME = dtp.TABLE_NAME  and dpt.TABLE_NAME = 'tbl1_r';
+-------+------------+-------------------+------+----------------+
| owner | TABLE_NAME | PARTITIONING_TYPE | u    | PARTITION_NAME |
+-------+------------+-------------------+------+----------------+
| dave  | tbl1_r     | RANGE             | NONE | M202001        |
| dave  | tbl1_r     | RANGE             | NONE | M202002        |
| dave  | tbl1_r     | RANGE             | NONE | M202003        |
| dave  | tbl1_r     | RANGE             | NONE | M202004        |
+-------+------------+-------------------+------+----------------+
4 rows in set (0.003 sec)


obclient [dave]> ALTER TABLE tbl1_r ADD PARTITION (PARTITION M202106 VALUES LESS THAN(UNIX_TIMESTAMP('2021/07/01')));
Query OK, 0 rows affected (0.167 sec)

obclient [dave]> select dpt.owner,dpt.TABLE_NAME ,dpt.PARTITIONING_TYPE ,dpt.SUBPARTITIONING_TYPE u, dtp.PARTITION_NAME  from oceanbase.DBA_PART_TABLES dpt , oceanbase.DBA_TAB_PARTITIONS dtp where dpt.TABLE_NAME = dtp.TABLE_NAME  and dpt.TABLE_NAME = 'tbl1_r';
+-------+------------+-------------------+------+----------------+
| owner | TABLE_NAME | PARTITIONING_TYPE | u    | PARTITION_NAME |
+-------+------------+-------------------+------+----------------+
| dave  | tbl1_r     | RANGE             | NONE | M202001        |
| dave  | tbl1_r     | RANGE             | NONE | M202002        |
| dave  | tbl1_r     | RANGE             | NONE | M202003        |
| dave  | tbl1_r     | RANGE             | NONE | M202004        |
| dave  | tbl1_r     | RANGE             | NONE | M202106        |
+-------+------------+-------------------+------+----------------+
5 rows in set (0.003 sec)

如果表上有默认的分区，比如 DEFAULT，或者 MAX，那么则需要先删除这些分区，在添加：

obclient [dave]> select dpt.owner,dpt.TABLE_NAME ,dpt.PARTITIONING_TYPE ,dpt.SUBPARTITIONING_TYPE u, dtp.PARTITION_NAME,dtp.HIGH_VALUE  from oceanbase.DBA_PART_TABLES dpt , oceanbase.DBA_TAB_PARTITIONS dtp where dpt.TABLE_NAME = dtp.TABLE_NAME  and dpt.TABLE_NAME = 'tbl1_log_rc';
+-------+-------------+-------------------+------+----------------+--------------+
| owner | TABLE_NAME  | PARTITIONING_TYPE | u    | PARTITION_NAME | HIGH_VALUE   |
+-------+-------------+-------------------+------+----------------+--------------+
| dave  | tbl1_log_rc | RANGE COLUMNS     | NONE | M202001        | '2020-02-01' |
| dave  | tbl1_log_rc | RANGE COLUMNS     | NONE | M202002        | '2020-03-01' |
| dave  | tbl1_log_rc | RANGE COLUMNS     | NONE | M202003        | '2020-04-01' |
| dave  | tbl1_log_rc | RANGE COLUMNS     | NONE | M202004        | '2020-05-01' |
| dave  | tbl1_log_rc | RANGE COLUMNS     | NONE | MMAX           | MAXVALUE     |
+-------+-------------+-------------------+------+----------------+--------------+
5 rows in set (0.070 sec)


obclient [dave]> ALTER TABLE tbl1_log_rc DROP PARTITION MMAX;
Query OK, 0 rows affected (0.425 sec)

obclient [dave]> select dpt.owner,dpt.TABLE_NAME ,dpt.PARTITIONING_TYPE ,dpt.SUBPARTITIONING_TYPE u, dtp.PARTITION_NAME,dtp.HIGH_VALUE  from oceanbase.DBA_PART_TABLES dpt , oceanbase.DBA_TAB_PARTITIONS dtp where dpt.TABLE_NAME = dtp.TABLE_NAME  and dpt.TABLE_NAME = 'tbl1_log_rc';
+-------+-------------+-------------------+------+----------------+--------------+
| owner | TABLE_NAME  | PARTITIONING_TYPE | u    | PARTITION_NAME | HIGH_VALUE   |
+-------+-------------+-------------------+------+----------------+--------------+
| dave  | tbl1_log_rc | RANGE COLUMNS     | NONE | M202001        | '2020-02-01' |
| dave  | tbl1_log_rc | RANGE COLUMNS     | NONE | M202002        | '2020-03-01' |
| dave  | tbl1_log_rc | RANGE COLUMNS     | NONE | M202003        | '2020-04-01' |
| dave  | tbl1_log_rc | RANGE COLUMNS     | NONE | M202004        | '2020-05-01' |
+-------+-------------+-------------------+------+----------------+--------------+
4 rows in set (0.003 sec)

obclient [dave]> ALTER TABLE tbl1_log_rc ADD PARTITION ( PARTITION M202101 VALUES LESS THAN('2021/01/01'), PARTITION MMAX VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected (0.164 sec)

obclient [dave]> select dpt.owner,dpt.TABLE_NAME ,dpt.PARTITIONING_TYPE ,dpt.SUBPARTITIONING_TYPE u, dtp.PARTITION_NAME,dtp.HIGH_VALUE  from oceanbase.DBA_PART_TABLES dpt , oceanbase.DBA_TAB_PARTITIONS dtp where dpt.TABLE_NAME = dtp.TABLE_NAME  and dpt.TABLE_NAME = 'tbl1_log_rc';
+-------+-------------+-------------------+------+----------------+--------------+
| owner | TABLE_NAME  | PARTITIONING_TYPE | u    | PARTITION_NAME | HIGH_VALUE   |
+-------+-------------+-------------------+------+----------------+--------------+
| dave  | tbl1_log_rc | RANGE COLUMNS     | NONE | M202001        | '2020-02-01' |
| dave  | tbl1_log_rc | RANGE COLUMNS     | NONE | M202002        | '2020-03-01' |
| dave  | tbl1_log_rc | RANGE COLUMNS     | NONE | M202003        | '2020-04-01' |
| dave  | tbl1_log_rc | RANGE COLUMNS     | NONE | M202004        | '2020-05-01' |
| dave  | tbl1_log_rc | RANGE COLUMNS     | NONE | M202101        | '2021-01-01' |
| dave  | tbl1_log_rc | RANGE COLUMNS     | NONE | MMAX           | MAXVALUE     |
+-------+-------------+-------------------+------+----------------+--------------+
6 rows in set (0.003 sec)

2.3 truncate 分区

Truncate 一级分区时，可以将一个或多个分区中的数据清空。

在 Truncate 一级分区时，请尽量保证该分区上不存在活动的事务或查询，否则可能会导致 SQL 语句报错，或者出现一些异常情况。

在 sys 租户下，通过视图 oceanbase.GV$OB_TRANSACTION_PARTICIPANTS 查询当前还未结束的事务上下文状态。

ALTER TABLE table_name TRUNCATE PARTITION partition_name[, partition_name ...];


obclient [dave]> alter table tbl1_log_rc truncate partition M202101;
Query OK, 0 rows affected (0.330 sec)

2.4 创建索引

全局索引相比局部索引有更高的维护代价，应当充分评估主表的分区规则，合理的选择分区键，尽量使更多的查询条件能够覆盖主表的分区键，从而尽可能的避免使用全局索引。

由于 OceanBase 数据库的表是索引组织表（ IOT ），对于分区表而言，为了保证指定主键的查询能很快定位到表所在的分区，所以分区键必须是主键的子集。

如果需要在分区表上创建局部分区唯一索引（ Local Partitioned Unique Index ），则该索引键需要包含主表的分区键，而对于全局分区唯一索引（ Global Partitioned Unique Index ）并没有这个限制。

全局索引的创建规则是在索引属性中指定 GLOBAL 关键字。与局部索引相比，全局索引最大的特点是全局索引的分区规则跟表分区是相互独立的，全局索引允许指定自己的分区规则和分区个数，不一定需要跟表分区规则保持一致。

本地索引：

obclient> CREATE TABLE test(pk int,c2 int ,c3 int, PRIMARY KEY(pk)) PARTITION BY hash(pk) partitions 5;
Query OK, 0 rows affected
obclient> CREATE UNIQUE INDEX idx ON test(c2) LOCAL;
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

obclient> CREATE UNIQUE INDEX idx ON test(c2, pk) LOCAL;
Query OK, 0 rows affected

obclient> DROP INDEX idx ON test;
Query OK, 0 rows affected

obclient> CREATE UNIQUE INDEX idx ON test(c2) GLOBAL;
Query OK, 0 rows affected

全局索引：

obclient> CREATE TABLE tbl1_h(col1 INT PRIMARY KEY,col2 INT)
       PARTITION BY HASH(col1) PARTITIONS 5;
Query OK, 0 rows affected

obclient> CREATE INDEX tbl1_h_idx1 ON tbl1_h(col2) GLOBAL
       PARTITION BY RANGE(col2)
       (PARTITION p0 VALUES LESS THAN(100), 
        PARTITION p1 VALUES LESS THAN(200), 
        PARTITION p2 VALUES LESS THAN(300)
       );
Query OK, 0 rows affected

签到成功

CNDBA社区