实战教程第三章3.6：如何使用 OceanBase 分区表进行水平拆分

分区技术（Partitioning）是 OceanBase 非常重要的分布式能力之一，它能解决大表的容量问题和高并发访问时的性能问题，主要思想就是将大表拆分为更多更小的结构相同的独立对象，即分区。普通的表只有一个分区，可以看作分区表的特例。每个分区只能存在于一个节点内部，分区表的不同分区可以分散在不同节点上。

分区路由

OceanBase 的分区表是内建功能，您只需要在建表的时候指定分区策略和分区数即可。分区表的查询 SQL 跟普通表是一样的，OceanBase 的 OBProxy 或 OBServer 会自动将用户 SQL 路由到相应节点内，因此，分区表的分区细节对业务是透明的。

如果知道要读取的数据所在的分区号，可以通过 SQL 直接访问分区表的某个分区。简单语法格式如下：

part_table partition ( p[0,1,…][sp[0,1,...]] )

默认情况下，除非表定义了分区名，分区名都是按一定规则编号，例如：

一级分区名为：p0 , p1 , p2 , … 二级分区名为：p0sp0 , p0sp1 , p0sp2 , … ; p1sp0 , p1sp1 , p1sp2 , … 示例：访问分区表的具体分区。

select * from t1 partition (p0) ;

select * from t1 partition (p5sp0) ;

分区策略

OceanBase 支持多种分区策略：

• 范围（RANGE）分区
• RANGE COLUMNS 分区
• 列表（LIST）分区
• LIST COLUMNS 分区
• 哈希（HASH）分区
• 组合分区

范围（RANGE）分区

RANGE 分区根据分区表定义时为每个分区建立的分区键值范围，将数据映射到相应的分区中。它是常见的分区类型，经常跟日期类型一起使用。比如说，可以将业务日志表按日/周/月分区。RANGE 分区简单的语法格式如下：

CREATE TABLE table_name (
    column_name1        column_type
    [, column_nameN     column_type]
) PARTITION BY RANGE ( expr(column_name1) )
(
    PARTITION   p0      VALUES LESS THAN ( expr )
    [, PARTITION pN     VALUES LESS THAN (expr ) ]
 [, PARTITION pX    VALUES LESS THAN (maxvalue) ]
);

当使用 RANGE 分区时，需要遵守如下几个规则：

• PARTITION BY RANGE ( expr )
•  里的 expr 表达式的结果必须为整形。
• 每个分区都有一个 VALUES LESS THAN 子句，它为分区指定一个非包含的上限值。分区键的任何值等于或大于这个值时将被映射到下一个分区中。
• 除第一个分区外，所有分区都隐含一个下限值，即上一个分区的上限值。
• 允许且只允许最后一个分区上限定义为 MAXVALUE ，这个值没有具体的数值，比其他所有分区的上限都要大，也包含空值。

注意：RANGE 分区可以新增、删除分区。如果最后一个 RANGE 分区指定了 MAXVALUE ，则不能新增分区。所以建议不要使用 MAXVALUE 定义最后一个分区。

RANGE 分区要求表拆分键表达式的结果必须为整型，如果要按时间类型列做 RANGE 分区，则必须使用 timestamp 类型，并且使用函数 UNIX_TIMESTAMP 将时间类型转换为数值。这个需求也可以使用 RANGE COLUMNS 分区实现，就没有整型这个要求。

示例如下：

CREATE TABLE test_range(id INT, gmt_create TIMESTAMP, info VARCHAR(20), PRIMARY KEY (gmt_create))
PARTITION BY RANGE(UNIX_TIMESTAMP(gmt_create))
(PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00')),
PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2016-01-01 00:00:00')),
PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2017-01-01 00:00:00')));

哈希（HASH）分区

HASH 分区适合于对不能用 RANGE 分区、LIST 分区方法的场景，它的实现方法简单，通过对分区键上的 HASH 函数值来散列记录到不同分区中。如果您的数据符合下列特点，使用 HASH 分区是个很好的选择：

• 不能指定数据的分区键的列表特征。
• 不同范围内的数据大小相差非常大，并且很难手动调整均衡。
• 使用 RANGE 分区后数据聚集严重。
• 并行 DML、分区剪枝和分区连接等性能非常重要。

示例：创建一个 HASH 分区表

CREATE TABLE ware2(
    w_id int
    , w_ytd number(12,2)
    , w_tax number(4,4)
    , w_name varchar(10)
    , w_street_1 varchar(20)
    , w_street_2 varchar(20)
    , w_city varchar(20)
    , w_state char(2)
    , w_zip char(9)
    , primary key(w_id)
) PARTITION by hash(w_id) partitions 60;

HASH 分区不能做新增或删除分区操作。

组合分区（二级分区）

组合分区通常是先使用一种分区策略，然后在子分区再使用另外一种分区策略，适合于业务表的数据量非常大时。使用组合分区能发挥多种分区策略的优点。

在指定二级分区分区策略细节时，可以使用 SUBPARTITION TEMPLATE 子句。

示例：创建组合分区表。

CREATE TABLE t_ordr_part_by_hash_range (o_w_id int
, o_d_id int
, o_id int
, o_c_id int
, o_carrier_id int
, o_ol_cnt int
, o_all_local int
, o_entry_d TIMESTAMP NOT NULL
, index idx_ordr(o_w_id, o_d_id, o_c_id, o_id) LOCAL 
, primary key ( o_w_id, o_d_id, o_id, o_entry_d )
)  
PARTITION BY hash(o_w_id) 
SUBPARTITION BY RANGE(UNIX_TIMESTAMP(o_entry_d))
SUBPARTITION template
(
    SUBPARTITION M202001 VALUES LESS THAN(UNIX_TIMESTAMP('2020/02/01'))
    , SUBPARTITION M202002 VALUES LESS THAN(UNIX_TIMESTAMP('2020/03/01'))
    , SUBPARTITION M202003 VALUES LESS THAN(UNIX_TIMESTAMP('2020/04/01'))
    , SUBPARTITION M202004 VALUES LESS THAN(UNIX_TIMESTAMP('2020/05/01'))
    , SUBPARTITION M202005 VALUES LESS THAN(UNIX_TIMESTAMP('2020/06/01'))
    , SUBPARTITION M202006 VALUES LESS THAN(UNIX_TIMESTAMP('2020/07/01'))
    , SUBPARTITION M202007 VALUES LESS THAN(UNIX_TIMESTAMP('2020/08/01'))
    , SUBPARTITION M202008 VALUES LESS THAN(UNIX_TIMESTAMP('2020/09/01'))
    , SUBPARTITION M202009 VALUES LESS THAN(UNIX_TIMESTAMP('2020/10/01'))
    , SUBPARTITION M202010 VALUES LESS THAN(UNIX_TIMESTAMP('2020/11/01'))
    , SUBPARTITION M202011 VALUES LESS THAN(UNIX_TIMESTAMP('2020/12/01'))
    , SUBPARTITION M202012 VALUES LESS THAN(UNIX_TIMESTAMP('2021/01/01'))
)
partitions 16;

CREATE TABLE t_log_part_by_range_hash (
    log_id      int NOT NULL 
    , log_value varchar(50)
    , log_date  TIMESTAMP NOT NULL 
    , PRIMARY key(log_id, log_date)
) PARTITION BY RANGE(UNIX_TIMESTAMP(log_date))
SUBPARTITION BY HASH(log_id) SUBPARTITIONS 16
(
    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'))
    , PARTITION M202005 VALUES LESS THAN(UNIX_TIMESTAMP('2020/06/01'))
    , PARTITION M202006 VALUES LESS THAN(UNIX_TIMESTAMP('2020/07/01'))
    , PARTITION M202007 VALUES LESS THAN(UNIX_TIMESTAMP('2020/08/01'))
    , PARTITION M202008 VALUES LESS THAN(UNIX_TIMESTAMP('2020/09/01'))
    , PARTITION M202009 VALUES LESS THAN(UNIX_TIMESTAMP('2020/10/01'))
    , PARTITION M202010 VALUES LESS THAN(UNIX_TIMESTAMP('2020/11/01'))
    , PARTITION M202011 VALUES LESS THAN(UNIX_TIMESTAMP('2020/12/01'))
    , PARTITION M202012 VALUES LESS THAN(UNIX_TIMESTAMP('2021/01/01'))
);

尽管 OceanBase 在组合分区上支持 RANGE + HASH 和 HASH + RANGE 两种组合，对于一个流水大表，为了维护方便（新增和删除分区），建议使用 RANGE + HASH 组合方式。

分区表的索引

分区表的查询性能跟 SQL 中条件有关。当 SQL 中带上拆分键时，OceanBase 会根据条件做分区剪枝，只用搜索特定的分区即可；如果没有拆分键，则要扫描所有分区。

分区表也可以通过创建索引来提升性能。跟分区表一样，分区表的索引也可以分区或者不分区。

• 如果分区表的索引不分区，就是一个全局索引（ 
• GLOBAL
•  ），是一个独立的分区，索引数据覆盖整个分区表。
• 如果分区表的索引分区了，根据分区策略又可以分为两类。一是跟分区表保持一致的分区策略，则每个索引分区的索引数据覆盖相应的分区表的分区，这个索引又叫本地索引（ 
• LOCAL
•  ）。

注意：通常创建索引时默认都是全局索引，本地索引需要在后面增加关键字 LOCAL 。 建议尽可能的使用本地索引，只有在有必要的时候才使用全局索引。其原因是全局索引会降低 DML 的性能，DML 可能会因此产生分布式事务。

示例：创建分区表的本地索引和全局索引。

CREATE INDEX idx_log_date ON t_log_part_by_range_hash(log_date) LOCAL;

CREATE INDEX idx_log_date2 ON t_log_part_by_range_hash(log_value, log_date) GLOBAL;

注意：OceanBase 的分区表主键和唯一键，不需要单独建索引。OceanBase 分区表的一个功能限制是如果分区表有主键，主键必须包含分区键。

分区表的实践

通常当表的数据量非常大，以致于可能使数据库空间紧张，或者由于表非常大导致相关 SQL 查询性能变慢时，可以考虑使用分区表。

使用分区表时要选择合适的拆分键以及拆分策略。如果是日志类型的大表，根据时间类型的列做 RANGE 分区是最合适的。如果是并发访问非常高的表，结合业务特点选择能满足绝大部分核心业务查询的列作为拆分键是最合适的。无论选哪个列做为分区键，都不大可能满足所有的查询性能。

分区表中的全局唯一性需求可以通过主键约束和唯一约束实现。OceanBase 数据库的分区表的主键约束必须包含拆分键。唯一约束是一个全局索引。全局唯一的需求也可以通过本地唯一索引实现（在唯一索引里包含拆分键 ），也可以通过全局索引实现。

CREATE TABLE account(
         id bigint NOT NULL PRIMARY KEY
         , name varchar(50) NOT NULL UNIQUE
         , value number NOT NULL
         , gmt_create timestamp DEFAULT current_timestamp NOT NULL
         , gmt_modified timestamp DEFAULT current_timestamp NOT NULL
     ) PARTITION BY HASH(id) PARTITIONS 16;

CREATE TABLE account2(
         id bigint NOT NULL PRIMARY KEY
         , name varchar(50) NOT NULL
         , value number NOT NULL
         , gmt_create timestamp DEFAULT current_timestamp NOT NULL
         , gmt_modified timestamp DEFAULT current_timestamp NOT NULL
     ) PARTITION BY HASH(id) PARTITIONS 16;


CREATE UNIQUE INDEX account2_uk ON account2(name, id) LOCAL ;

CREATE UNIQUE INDEX t_log_part_by_range_hash_uk2 on t_log_part_by_range_hash(log_value);


obclient> show indexes from account;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| account |          0 | PRIMARY  |            1 | id          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
| account |          0 | name     |            1 | name        | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
2 rows in set (0.002 sec)

obclient> show indexes from account2;
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| Table    | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| account2 |          0 | PRIMARY     |            1 | id          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
| account2 |          0 | account2_uk |            1 | name        | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
| account2 |          0 | account2_uk |            2 | id          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
3 rows in set (0.002 sec)

obclient> SELECT * FROM information_schema.`TABLE_CONSTRAINTS` WHERE table_schema='TEST' AND table_name LIKE 'ACCOUNT%';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| def                | test              | PRIMARY         | test         | account    | PRIMARY KEY     |
| def                | test              | name            | test         | account    | UNIQUE          |
| def                | test              | PRIMARY         | test         | account2   | PRIMARY KEY     |
| def                | test              | account2_uk     | test         | account2   | UNIQUE          |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
4 rows in set (0.001 sec)

附录：

• 3.1 查看 OceanBase 集群资源的使用情况
• 3.2 如何创建和连接 MySQL 租户
• 3.3 如何连接租户
• 3.4 如何对租户参数（或变量）进行设置
• 3.5 如何使用 MySQL 租户做常见数据库开发
• 3.6 如何使用 OceanBase 分区表进行水平拆分
• 3.7 （高级）如何使用 OceanBase 表分组
• 3.8（高级）如何使用 OceanBase 复制表
• 3.9 常见问题

结束语

加入教程直播群方式一：钉钉群号3255 4020

加入教程直播群方式二：扫码下方二维码加入