解析在OceanBase创建分区的常见问题｜OceanBase 用户问题精粹

在《分区策略和管理分区计划的实践方案》这篇文章中，我们介绍了在ODC中制定分区策略及有效管理分区计划的经验。有不少用户在该帖下提出了使用中的问题，其中一个关于创建分区的限制条件的问题，也是很多用户遭遇的老问题。因此本文以其为切入，将创建分区的几个问题进行解析，与大家共同探讨分享。

为什么主键必须包含全部分区键？

用户问：“有一张订单流水表，数据很大，想考虑按年份对数据进行分区。现在只有 ID 列是主键。尝试了一下好像无法按日期进行分区。是必须要把日期做成和 ID 的联合主键才可以分区么？”

答案是对的，主键必须包含所有分区键。因为主键的唯一性检查是在各个分区内部进行的，如果主键不包含全部分区键，这个检查就会失效，所以 MySQL 及其他数据库，也一样会有这个要求。

-- 如果主键不包含全部分区键，建表就会失败报错，报错信息也挺明确的。
create table t1(c1 int, c2 int,c3 int,primary key (c1))
partition by range (c2) (partition p1 values less than(3),partition p1 values less than(6));ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

下面举个例子：

create table t1(c1 int, c2 int,c3 int,primary key (c1, c2))
partition by range (c2) (partition p0 values less than(3),partition p1 values less than(6));
Query OK, 0 rows affected (0.146 sec)obclient [test]> insert into t1 values(1, 2, 3);
Query OK, 1 row affected (0.032 sec)obclient [test]> insert into t1 values(1, 5, 3);
Query OK, 1 row affected (0.032 sec)obclient [test]> select * from t1;
+----+----+------+
| c1 | c2 | c3   |
+----+----+------+
|  1 |  2 |    3 |
|  1 |  5 |    3 |
+----+----+------+
2 rows in set (0.032 sec)

我们创建了一张表，主键是 c1 和 c2，分区键是 c2，小于 3 的值在 p0 分区，大于等于 3 且小于 6 的值在 p1 分区。然后插入了两个行，第一行在 p0 分区，第二行在 p1 分区。

obclient [test]> select * from t1 PARTITION(p0);
+----+----+------+
| c1 | c2 | c3   |
+----+----+------+
|  1 |  2 |    3 |
+----+----+------+
1 row in set (0.033 sec)obclient [test]> select * from t1 PARTITION(p1);
+----+----+------+
| c1 | c2 | c3   |
+----+----+------+
|  1 |  5 |    3 |
+----+----+------+
1 row in set (0.034 sec)

如果主键只有 c1 而没有 c2，那么在 p0 和 p1 分区内对 c1 列的唯一性检测都会成功，因为在各个分区内 c1 列的值都不重复，然后就会判定插入的数据符合主键约束。但实际上在分区间会有重复值，数据并不符合主键约束，所以所有数据库在分区时，都要求主键包含全部分区键。

为什么分区能让查询变快？

用户另外一个问题：“按日期分区是否能达到让查询变快的目的？”

个人理解，分区除了可以让一张超级大表的数据比较被均衡地被负载在不同的数据库节点上，另外一个目的就是加速查询。因为查询时会利用过滤条件里面的分区键进行分区裁剪。例如下面这两个例子：

如果过滤条件里有分区键，计划中可以看到 partitions(p0)，说明只扫描了 p0 这一个分区的数据。


obclient [test]> explain select * from t1 where c2 = 1;
+------------------------------------------------------------------------------------+
| Query Plan                                                                         |
+------------------------------------------------------------------------------------+
| ===============================================                                    |
| |ID|OPERATOR       |NAME|EST.ROWS|EST.TIME(us)|                                    |
| -----------------------------------------------                                    |
| |0 |TABLE FULL SCAN|t1  |1       |3           |                                    |
| ===============================================                                    |
| Outputs & filters:                                                                 |
| -------------------------------------                                              |
|   0 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c2 = 1]), rowset=16            |
|       access([t1.c1], [t1.c2], [t1.c3]), partitions(p0)                            |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],  |
|       range_key([t1.c1], [t1.c2]), range(MIN,MIN ; MAX,MAX)always true             |
+------------------------------------------------------------------------------------+
11 rows in set (0.034 sec)

如果过滤条件里没有分区键，计划中可以看到 partitions(p[0-1])，说明扫描了 p0 和 p1 全部所有分区的数据。其中 PX PARTITION ITERATOR 算子就是用来循环扫描所有分区的迭代器。

obclient [test]> explain select * from t1 where c3 = 1;
+------------------------------------------------------------------------------------+
| Query Plan                                                                         |
+------------------------------------------------------------------------------------+
| =============================================================                      |
| |ID|OPERATOR                 |NAME    |EST.ROWS|EST.TIME(us)|                      |
| -------------------------------------------------------------                      |
| |0 |PX COORDINATOR           |        |1       |6           |                      |
| |1 |└─EXCHANGE OUT DISTR     |:EX10000|1       |6           |                      |
| |2 |  └─PX PARTITION ITERATOR|        |1       |5           |                      |
| |3 |    └─TABLE FULL SCAN    |t1      |1       |5           |                      |
| =============================================================                      |
| Outputs & filters:                                                                 |
| -------------------------------------                                              |
|   0 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3)]), filter(nil), rowset=16     |
|   1 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3)]), filter(nil), rowset=16     |
|       dop=1                                                                        |
|   2 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset=16                    |
|       force partition granule                                                      |
|   3 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c3 = 1]), rowset=16            |
|       access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1])                        |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],  |
|       range_key([t1.c1], [t1.c2]), range(MIN,MIN ; MAX,MAX)always true             |
+------------------------------------------------------------------------------------+
19 rows in set (0.038 sec)

range 分区不支持 datetime 类型咋办？

用户的另另外一个问题：“range 分区不支持 datetime 类型咋办？”。

CREATE TABLE ff01 (a datetime , b timestamp)
PARTITION BY RANGE(UNIX_TIMESTAMP(a))(PARTITION p0 VALUES less than (UNIX_TIMESTAMP('2000-2-3 00:00:00')),PARTITION p1 VALUES less than (UNIX_TIMESTAMP('2001-2-3 00:00:00')),PARTITION pn VALUES less than MAXVALUE);ERROR 1486 (HY000): Constant or random or timezone-dependent expressions in (sub)partitioning function are not allowed

试了下，OB 的 MySQL 模式，为了兼容 MySQL 行为，会和 MySQL 对 random expressions 进行一些限制。我第一时间想到的是用生成列绕过，不过很快发现，为了兼容 MySQL 行为，OB 对生成列的使用也进行了限制，生成列里也不允许出现 UNIX_TIMESTAMP 这个特殊的表达式，所以并没什么卵用：

CREATE TABLE ff01 (a datetime , b timestamp as (UNIX_TIMESTAMP(a)))
PARTITION BY RANGE(b)(PARTITION p0 VALUES less than (UNIX_TIMESTAMP('2000-2-3 00:00:00')),PARTITION p1 VALUES less than (UNIX_TIMESTAMP('2001-2-3 00:00:00')),PARTITION pn VALUES less than MAXVALUE);ERROR 3102 (HY000): Expression of generated column contains a disallowed function

至于为啥 UNIX_TIMESTAMP 在生成列里属于 disallowed function，猜测大概率是因为它是个非 deterministic 的系统函数。非 deterministic 简单来说就是这个 UNIX_TIMESTAMP() 函数在前一秒执行，和在后一秒执行，可能会返回不同的结果。像分区表达式、生成列表达式、check 约束里面的表达式，都不允许出现这种非确定性的函数。

下面举个简单的例子，解释一下上面 ERROR 1486 这个报错里 random 一词，以及非 deterministic 的含义：

obclient [test]> select UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1725008180 |
+------------------+
1 row in set (0.042 sec)obclient [test]> select UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1725008419 |
+------------------+
1 row in set (0.041 sec)-- 是不是一下子就明白，为啥 UNIX_TIMESTAMP 这么特殊，在哪里都不受待见了吧？

不过不得不说，OB 的 MySQL 兼容性做的还挺好的，不仅是兼容了 MySQL 各种使用上的限制，甚至是一些 MySQL 的 bug 都给兼容了，虽然给使用带来了一些不便，不过迁移 MySQL 大概会变得比较轻松。

扯远了，回归正题，后面查了下 OB 官网，发现有一种分区方式叫 Range Columns，和 Range 分区十分类似，优点是相比 Range 分区可以支持更多的数据类型，例如用户需要的 datetime 类型，缺点是分区定义不支持表达式。

因为 Range 不支持 UNIX_TIMESTAMP 这类特殊的非 deterministic 表达式，所以个人理解这里可以通过 Range Columns 解决用户的问题。例如：

CREATE TABLE ff01 (a datetime , b timestamp)
PARTITION BY RANGE COLUMNS(a)(PARTITION p0 VALUES less than ('2023-01-01'),PARTITION p1 VALUES less than ('2023-01-02'),PARTITION pn VALUES less than MAXVALUE);Query OK, 0 rows affected (0.101 sec)

说来惭愧，我之前也一直没注意过 Range 分区和 Range Columns 分区的区别，一直是把他们等价的，今天也算是学习到了，哈哈~

最后附上一个 MySQL 的官网文档链接，感觉它对 RANGE COLUMNS partitioning 的介绍比 OB 的官网要更清楚些，在这里推荐给对分区方式感兴趣的朋友阅读~

1725007840

What else？

有同学提出还可以通过利用 to_days 函数代替 UNIX_TIMESTAMP 函数的方式解决第三个问题，这样就不需要更改 range 分区为 range columns 分区了。例如：

##创建range分区表
-- 分区字段是start_time，类型datetime
CREATE TABLE dba_test_range_1 (id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',`name` varchar(50) NOT NULL COMMENT 'name',start_time datetime NOT NULL COMMENT '开始时间',
PRIMARY KEY (id,start_time)
)AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COMMENT = 'test range' 
PARTITION BY RANGE(to_days(start_time))(PARTITION M202301 VALUES LESS THAN(to_days('2023-02-01')),PARTITION M202302 VALUES LESS THAN(to_days('2023-03-01')),PARTITION M202303 VALUES LESS THAN(to_days('2023-04-01')));