【赵渝强老师】OceanBase的分区表

OceanBase当前支持的类型包括Range分区、Range COLUMNS分区、List分区、List COLUMNS分区、Hash分区和Key分区等，其中：

• Range分区、Range COLUMNS分区、List分区和List COLUMNS分区可以用于解决业务中大量删除带来的性能问题，支持快速删除分区。
• Hash分区和Key分区可以用于大量写入场景下的数据打散。与Hash分区相比，Key分区支持多列打散和非整数类型字段的打散。

点击这里查看视频讲解：【赵渝强老师】OceanBase的分区表

下面分别介绍这几种分区表。

一、 Range分区

一个表按Range分区是指，对于表的每个分区中包含的所有行，按分区表达式计算的值都落在给定的范围内。Range必须是连续的，并且不能有重叠，通过使用VALUES LESS THAN进行定义。下面通过一个示例来进行演示。
（1）创建一张新的员工表，按照薪水建立Ranger分区。

ob> create table emp_range 
  (empno int,
    ename varchar(10),
    job varchar(10),
    mgr int,
    hiredate varchar(10),
    sal int,
    comm int,
    deptno int
  )partition by range (sal) (
      partition p0 values less than (1000),
      partition p1 values less than (3000),
      partition p2 values less than maxvalue
  );

（2）往表中插入数据。

ob> insert into emp_range select * from emp;

（3）查询薪水在2000~2500之间的员工，并查看执行计划。

ob> explain select * from emp_range where sal between 2000 and 2500;

# 提示：从执行计划可以看出，本次查询只扫描分区p1。

二、 Range COLUMNS分区

Range COLUMNS分区是Range分区的一种变体。可以使用一个或者多个列作为分区键，分区列的数据类型可以是整数(integer)、字符串（CHAR/VARCHAR），DATE和DATETIME。不支持使用任何表达式。和Range分区一样，Range COLUMNS分区同样需要分区的范围是严格递增的。下面通过一个示例来进行演示。
（1）创建一张新的员工表，按照职位和薪水建立Range COLUMNS分区。

ob> create table emp_range_column
  (empno int,
    ename varchar(10),
    job varchar(10),
    mgr int,
    hiredate varchar(10),
    sal int,
    comm int,
    deptno int
  )partition by range columns (job,sal) (
      partition p0 values less than ('CLERK',1000),
      partition p1 values less than ('CLERK',3000),
  	   partition p2 values less than ('CLERK',maxvalue),
      partition p3 values less than ('SALESMAN',1000),
      partition p4 values less than ('SALESMAN',3000),
  	partition p5 values less than ('SALESMAN',maxvalue)
  );

（2）往表中插入数据。

ob> insert into emp_range_column select * from emp;

（3）查询职位是CLERK，且薪水小于2500之间的员工，并查看执行计划。

ob> explain select * from emp_range where job='CLERK' and sal < 2500;

# 提示：从执行计划可以看出，本次查询只扫描分区p0和p1。

三、 List分区

List分区和Range分区有很多相似的地方。不同之处主要在于List分区中，对于表的每个分区中包含的所有行，按分区表达式计算的值属于给定的数据集合。每个分区定义的数据集合有任意个值，但不能有重复的值。下面通过一个示例来进行演示。
（1）创建一张新的员工表，按照部门号建立List分区。

ob> create table emp_list
  (empno int,
    ename varchar(10),
    job varchar(10),
    mgr int,
    hiredate varchar(10),
    sal int,
    comm int,
    deptno int)
  partition by list(deptno)(
    PARTITION p0 VALUES IN (10,20),
    PARTITION p1 VALUES IN (30),
    PARTITION p2 VALUES IN (default)
  ); 

（2）往表中插入数据。

ob> insert into emp_list select * from emp;

（3）查询40号部门的员工，并查看执行计划。

ob> explain select * from emp_list where deptno=30;

# 提示：从执行计划可以看出，本次查询只扫描分区p1。

四、 List COLUMNS分区

List COLUMNS分区是List分区的一种变体，可以将多个列用作分区键，并且可以将整数类型以外的数据类型的列用作分区列。该分区还可以使用字符串类型、DATE和DATETIME类型的列。下面通过一个示例来进行演示。
（1）创建一张新的员工表，按照薪水建立Range INTERVAL分区。

ob> create table emp_list_columns
  (empno int,
    ename varchar(10),
    job varchar(10),
    mgr int,
    hiredate varchar(10),
    sal int,
    comm int,
    deptno int)
  partition by list columns(job)(
    PARTITION p0 VALUES IN ('MANAGER'),
    PARTITION p1 VALUES IN ('CLERK'),
    PARTITION p2 VALUES IN ('SALESMAN'),
    PARTITION p3 VALUES IN ('ANALYST'),
    PARTITION p4 VALUES IN ('PRESIDENT')
  ); 

（2）查看表上的分区信息。

ob> show create table emp_list_columns \G;

# 输出的信息如下：
*************************** 1. row ***************************
       Table: emp_list_columns
Create Table: CREATE TABLE `emp_list_columns` (
  `empno` int(11) DEFAULT NULL,
  `ename` varchar(10) DEFAULT NULL,
  `job` varchar(10) DEFAULT NULL,
  `mgr` int(11) DEFAULT NULL,
  `hiredate` varchar(10) DEFAULT NULL,
  `sal` int(11) DEFAULT NULL,
  `comm` int(11) DEFAULT NULL,
  `deptno` int(11) DEFAULT NULL
) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by list columns(job)
(partition `p3` values in ('ANALYST'),
partition `p1` values in ('CLERK'),
partition `p0` values in ('MANAGER'),
partition `p4` values in ('PRESIDENT'),
partition `p2` values in ('SALESMAN'))
1 row in set (0.035 sec)

五、 Hash分区

Hash分区会根据数据的Hash值建立分区，如果数据的Hash值一样，那么数据将会放入同一个分区中。Hash分区主要用于保证数据均匀地分散到一定数量的分区里面。下图解释了Hash分区的原理。

下面的语句将创建一张新的员工表，按照经理号（mgr）建立4个Hash分区。

ob> create table emp_hash_by_mgr
      (empno int,
      ename varchar(10),
      job varchar(10),
      mgr int,
      hiredate varchar(10),
      sal int,
      comm int,
      deptno int)
	  partition by hash(mgr) partitions 4;

# 提示：如果不指定partitions 4，默认的分区数量为 1。

六、 Key分区

Key分区与Hash分区都可以保证将数据均匀地分散到一定数量的分区里面，区别是Hash分区只能根据一个指定的整数表达式或字段进行分区，而Key分区可以根据字段列表进行分区，且Key分区的分区字段不局限于整数类型。创建Key分区表时，你需要在CREATE TABLE后面添加PARTITION BY KEY(columnList)，其中columnList是字段列表，可以包含一个或多个字段。每个字段的类型可以是除BLOB、JSON、GEOMETRY之外的任意类型（请注意TiDB不支持GEOMETRY类型）。此外，你很可能还需要加上PARTITIONS num，其中num是一个正整数，表示将表划分多少个分区。下面的语句将创建一个Key分区表，按员工的职位job分成4个分区：

ob> create table emp_hash_by_job
      (empno int,
      ename varchar(10),
      job varchar(10),
      mgr int,
      hiredate varchar(10),
      sal int,
      comm int,
      deptno int)
	  partition by key(job) partitions 4;