分区的概念

分区实质上是根据特定的规则，将表划分为若干个独立的物理存储单位。以MySQL为例，表会被拆分为多个物理文件。而在OceanBase​​​​​​​中，每个分区则表现为一个物理副本组，每个分区默认都拥有三个副本。

分区表的优势与劣势

在 MySQL 中，如果让我们在分区表和分库分表之间做选择，肯定很多人会毫不犹豫的选择分库分表，因为分区表虽然底层拆封出了多个物理文件，但是很多的操作其实还是表级，比如DDL，当表切换过程中，锁表影响的是所有分区。而分库分表只会影响部分表；第二就是分区表的负载其实还是集中在独立的实例上，并不能够做打散，而且当对应节点挂掉以后，所有分区都会收到影响，只能依赖后续的高可用。

而 OceanBase 因为本身是分布式数据库，所以它的分区其实更像 MySQL 的分库分表，因为底层通过将分区作为分片副本打散到不同的实例中，对于上层业务来说，并不需要关注底层分区的分布，而底层分区可以通过打散，来实现存储以及负载的均衡，并且某个实例（存在分区leader）宕机，也不会影响其他分区的读写。

所以分区表在分布式数据库中，有如下优势：

1、 负载均衡，可以将分区副本打散到多个节点，并且节点数量越多，越分散。

2、提高可用性，虽然本身集群有高可用机制，但是分区打散以后可以保证在故障恢复的阶段其他的分区请求不受影响。

3、便于数据管理，对于一些类似 TTL 的场景，可以通过 Truncate 分区来快速的清理数据。

4、提高性能，当正确使用分区时，可以使我们每次扫描/加载更少的数据，提高性能以及降低资源利用。

当然，分区表其实也是有隐患的，尤其是当分区使用不合理的时候，那么不仅有可能导致性能下降，甚至导致业务异常。比如 TP 的业务并且基本只会读取当天的数据，创建了天或者月级别的range分区，那么就会导致所有的请求集中到一个分区，出现热点问题。

所以创建合适的分区非常重要。

分区类别及使用

OceanBase 数据库的基本分区策略包括范围（Range/Range Columns）分区、列表（List/List Columns）分区、哈希（Hash）/Key 分区以及它们之间的组合。

RANGE 分区

Range 分区是按照某个连续的范围来划分数据区间，每个分区都包含分区表达式值位于给定范围内的行。常用于按年、月或日等时间维度进行分区。

特点：

  • 根据分区键值的范围把数据行存储到表的不同分区中
  • 多个分区的范围是连续的但不重叠。
  • 默认情况下使用VALUES LESS THAN属性，每个分区不包括指定的那个值

适用场景

  • 定期按分区范围清理历史数据
  • 并发不高并且请求范围集中
  • 范围查询

需要注意

1、如果业务的请求会集中在某几个范围内，比如只查当天的数据，并且请求量比较高，那么很容易产生热点问题。

2、如果范围是持续增加而不是固定的，一定不要设置 MAXVALUE分区。因为很可能导致大部分数据聚集在这个分区，并且无法拆分新的分区。

3、如果没有设置MAXVALUE分区，要插入分区表达式内不包含的值，那么一定要提前创建对应的分区，否则会报错。

4、RANGE 分区默认情况下只支持 int 类型

5、如果想要对非整型或者多列分区（比如时间范围），可以使用 Range Columns 分区。

常用的Range定义有下面几种：

直接根据字段范围：PARTITION BY RANGE(store_id)

根据时间年份：PARTITION BY RANGE ( YEAR(purchased))

根据时间戳：PARTITION BY RANGE(UNIX_TIMESTAMP(report_updated))

根据天：PARTITION BY RANGE(TO_DAYS(order_date))

根据时间字段范围：PARTITION BY RANGE COLUMNS(joined)

创建 Range 分区表

CREATE TABLE r (
    id INT NOT NULL,
    ctime DATE NOT NULL DEFAULT '2000-12-31'
)
PARTITION BY RANGE ( YEAR(ctime) ) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001)
);

创建 Range COLUMNS 分区表

CREATE TABLE rc(
    ctime DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(ctime) (
    PARTITION p0 VALUES LESS THAN ('1960-01-01'),
    PARTITION p1 VALUES LESS THAN ('1970-01-01'),
    PARTITION p2 VALUES LESS THAN ('1980-01-01'),
    PARTITION p3 VALUES LESS THAN ('1990-01-01')
);

现有表创建 Range 分区

ALTER TABLE r PARTITION BY RANGE ( YEAR(ctime) ) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001)
);

查询分区数据

select * from r partition(p0);

增加分区

ALTER TABLE r ADD PARTITION (PARTITION p3 VALUES LESS THAN(2006));

# 新增 MAXVALUE 分区
alter table r ADD PARTITION(PARTITION p4 VALUES less than (MAXVALUE));

清空分区

alter table r truncate partition p0;

删除分区

alter table r drop partition p0;

List 分区

故名思义，List分区是根据给定的值列表将表进行分区，每个分区对应一个列表中的值。它跟range分区有些类似，每个分区都必须显式定义。

特点：

跟range分区有些类似，各分区的列表值不能重复，但是 List 分区数据不需要连续。

适用场景：

  • 定期清理分区内的历史数据
  • 并发不高并且请求范围集中

注意：

1、同range分区，如果业务的请求会集中在某几个值/列表内，并且并发量比较高，那么很容易产生热点问题。

2、如果没有定义的值比较多，一定不要设置 DEFAULT分区。因为很可能导致大部分数据聚集在这个分区，并且无法拆分新的分区。

3、如果没有设置DEFAULT分区，要插入分区表达式内不包含的值，那么一定要提前创建对应的分区，否则会报错，所以一定要提前规划好。

4、如果分区使用表达式，那么结果必须是整型，并且只能引用一列。

5、如果想要对非整型或者多列分区，可以使用 List Columns 分区。

创建 List 分区表

CREATE TABLE l (
    id INT NOT NULL,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION p0 VALUES IN (3,5),
    PARTITION p1 VALUES IN (1,2),
    PARTITION p2 VALUES IN (4,6)
);

创建 List Columns 分区表

CREATE TABLE lc (
    id INT NOT NULL,
    store_id varchar(10)
)
PARTITION BY LIST COLUMNS(store_id) (
    PARTITION p0 VALUES IN ("3","5"),
    PARTITION p1 VALUES IN ("1","2"),
    PARTITION p2 VALUES IN ("4","6")
);

现有表创建 List 分区

alter table l PARTITION BY LIST(store_id) (
    PARTITION p0 VALUES IN (3,5),
    PARTITION p1 VALUES IN (1,2),
    PARTITION p2 VALUES IN (4,6)
);

新增分区

alter table l ADD PARTITION(PARTITION p3 VALUES IN (7,8));

# 新增 DEFAULT 分区
alter table l ADD PARTITION(PARTITION p3 VALUES IN (DEFAULT));

其他操作等同。

Hash 分区

Hash 分区是数据库根据用户指定的分区键的哈希算法将行映射到分区，它跟 Range、List 不同，不再需要指定列值存储在哪个分区，这种方式一般情况下会将数据打散的更加均衡。

常规的 HASH 分区非常的简便，通过取模（N = MOD(expr, num)）的方式可以让数据更加平均的分布每一个分区。比如4个分区，101会落在P1分区，因为 MOD( 101 , 4 ) = 1。

特点：

HASH 分区通常能消除热点查询，可以充分利用每台机器的资源。

适用场景：

1、没有明显可以分区的特征字段，但数据又非常庞大的表。

2、业务请求是点查或者少量的查询数据。

注意：

1、如果业务有大量的范围查询，那么可能会造成大量的分区扫描，此时分区只会起到反效果。

2、HASH分区的键值必须是一个INT类型的值，或是通过函数可以转为INT类型

创建 Hash 分区表

CREATE TABLE h (
    id INT NOT NULL,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

已有表创建 Hash 分区

alter table h PARTITION BY HASH(store_id) PARTITIONS 4;

Key 分区

KEY分区其实跟HASH分区差不多，不同点如下：

  • KEY分区允许多列，而HASH分区只允许一列。
  • 如果在有主键或者唯一键的情况下，KEY分区的分区列可不指定，默认为主键或者唯一键，如果没有，则必须显性指定列。
  • KEY分区对象必须为列，而不能是基于列的表达式。
  • KEY分区和HASH分区的算法不一样，PARTITION BY HASH (expr)，MOD取值的对象是expr返回的值，而PARTITION BY KEY (column_list)，基于的是列的MD5值。

创建 Key 分区

默认不指定列，以主键或者唯一键自动分区



CREATE TABLE k (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;

指定列创建

CREATE TABLE k2 (
    id INT NOT NULL,
    store_id varchar(10)
)
PARTITION BY KEY(`id`,`store_id`)
PARTITIONS 2;

二级分区

二级分区是指在分区表中每个一级分区的基础上，再做一层分区。二级分区和一级分区可以是同一个列，也可以是不同的列。可以实现在一级分区的基础上二次打散的效果。

对于模板化二级分区表来说，定义二级分区后，每个二级分区的命名规则为 ($part_name)s($subpart_name)。例如：p0sp1。

创建二级分区表

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );

查询二级分区数据（二级分区创建表达式是 to_days）

obclient [test]> select * from ts partition(p0);
+------+------------+
| id   | purchased  |
+------+------------+
|    1 | 1980-01-20 |
|    3 | 1980-01-22 |
|    2 | 1980-01-21 |
+------+------------+
3 rows in set (0.025 sec)

obclient [test]> select * from ts partition(p0sp0);
+------+------------+
| id   | purchased  |
+------+------------+
|    2 | 1980-01-21 |
+------+------------+
1 row in set (0.014 sec)

obclient [test]> select * from ts partition(p0sp1);
+------+------------+
| id   | purchased  |
+------+------------+
|    1 | 1980-01-20 |
|    3 | 1980-01-22 |
+------+------------+
2 rows in set (0.006 sec)

查询分区明细

SELECT table_name,partition_name,subpartition_name FROM information_schema.partitions;

分区的限制以及常见问题

限制

  • 如果表中存在主键或者唯一键，那么分区键必须是主键或者唯一键或者其中的部分列，主键或者唯一键必须包含分区键。
  • 单表限制 8192 个
  • 集群分区数量限制跟租户内存成正比，大概1G内存能建6000个分区
  • 单分区大小建议不超过 100G

常见问题

A PRIMARY KEY must include all columns in the table's partitioning function：分区键必须是主键或者唯一键或者其中的部分列，主键或者唯一键必须包含分区键，否则会创建失败。

比如下面两个例子都会失败

# 案例 1
CREATE TABLE t1 (s1 CHAR(32) PRIMARY KEY, s2 CHAR(32) ) PARTITION BY KEY(s2) PARTITIONS 4;

# 案例 2
CREATE TABLE t1 (s1 CHAR(32) PRIMARY KEY,  s2 CHAR(32) ) PARTITION BY KEY(s2,s1) PARTITIONS 4;

当主键为 （s1,s2）这样的组合主键时，上面的两个sql可以执行成功。

VALUES LESS THAN value must be strictly increasing for each partition：RANGE分区如果指定MAXVALUE分区，增加分区会失败。

cannot add partition when DEFAULT partition exists：List 分区如果指定 DEFAULT 分区，增加分区会失败。

Table has no partition for value ：分区的范围没有包含要插入的值，那么将插入失败。

为什么分区键必须是主键/唯一键的一部分？

简单来说，这个是索引组织表的限制。之所以对索引组织表有这样的限制，个人认为，还是基于性能考虑。

假设分区键和主键是两个不同的列或者分区键不包含在主键中，在进行插入操作时，虽然也指定了分区键，但还是需要扫描所有分区才能判断插入的主键值是否违反了唯一性约束。这样的话，效率会比较低下，违背了分区表的初衷。

分区建议

上面的分区类别中提到了各类分区的使用场景，其实分区怎么用，还是要看业务逻辑。

下面有一些分区使用的建议：

  • 如果是请求量比较高的 TP 业务，不建议使用 range 或者 List 分区，因为很容易产生热点问题。通常建议使用 hash/key 分区，来将请求打散。
  • 如果本身业务逻辑需要根据范围过滤一部分数据（比如时间），那么建议在一级 Range 分区的基础上，再做一层 hash/key 的二级分区。二级分区的数量建议是租户所占用节点数量的倍数。
  • 对分区表进行查询的时候，一定要指定分区键，否则的话没办法用到分区裁剪，会造成分区扫描，这样的话分区不但没有性能提升，反而起到了反效果。
  • hash/key 分区不合适范围扫描，如果这类业务请求比较多，不建议使用 hash/key分区，或者一级分区用 range，二级分区再做 hash/key 分区。
  • 表的数据量不大的话，可以不分区。
  • MAXVALUE 分区和 DEFAULT 分区定义要谨慎，因为后续将无法再扩展新的分区。
# oceanbase # 性能优化
Logo
加入 OceanBase 分布式数据库社区

了解最新的技术洞察和前沿趋势，参与 OceanBase 定期举办的线下活动，与行业开发者互动交流

更多推荐

cover

从MySQL+MyCAT架构升级为分布式数据库，百丽应用OceanBase 4.2的感受分享

avatar OceanBase数据库社区
cover

OceanBase ODC功能解析：SQL 检查规范，降低数据库高危变更风险

avatar OceanBase数据库社区
cover

Oracle索引组织表与大对象平滑迁移至OceanBase的实施方案

avatar OceanBase数据库社区