划线反馈

浅析创建分区时常见的几个问题

更新时间：2025-05-15 06:36

适用版本：V4.0.x、V4.1.x、V4.2.x内容类型：FAQ

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

若有一张订单流水表，数据很大，想考虑按年份对数据进行分区。现在只有 ID 列是主键，无法按日期进行分区，必须要把日期做成和 ID 的联合主键才可以分区，主键必须包含所有分区键。因为主键的唯一性检查是在各个分区内部进行的，如果主键不包含全部分区键，这个检查就会失效，所以 MySQL 及其他数据库，也一样会有这个要求。

obclient [mysql]> create table t1(c1 int,
            c2 int,
            c3 int,
            primary key (c1,c2))
            partition by range (c2)
            (partition p1 values less than(3),
            partition p2 values less than(6));

示例如下。

创建测试表 test99。

obclient [mysql]> create table test99(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.114 sec)

插入测试数据。

obclient [mysql]> insert into test99 values(1, 2, 3);
Query OK, 1 row affected (0.002 sec)

obclient [mysql]> insert into test99 values(1, 5, 3);
Query OK, 1 row affected (0.001 sec)

提交插入数据事务。

obclient [mysql]> commit;
Query OK, 0 rows affected (0.000 sec)

查询表 test99 内容。

obclient [mysql]> select * from test99;

输出结果如下：

+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|    1 |    2 |    3 |
|    1 |    5 |    3 |
+------+------+------+
2 rows in set (0.196 sec)

查询 p0 分区数据。

obclient [mysql]> select * from test99 PARTITION(p0);

输出结果如下：

+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|    1 |    2 |    3 |
+------+------+------+
1 row in set (0.005 sec)

查询 p1 分区数据。

obclient [mysql]> select * from test99 PARTITION(p1);

输出结果如下：

+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|    1 |    5 |    3 |
+------+------+------+
1 row in set (0.006 sec)

综上可知创建的表 test99 主键是 c1 和 c2，分区键是 c2，小于 3 的值在 p0 分区，大于等于 3 且小于 6 的值在 p1 分区。然后插入了两个行，查询数据在分区中的分布，第一行在 p0 分区，第二行在 p1 分区。

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

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

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

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

obclient [mysql]> explain select * from test99 where c2 = 1;

输出结果如下：

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

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

obclient [mysql]> explain select * from test99 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    |test99  |1       |5           |                              |
| =============================================================                              |
| Outputs & filters:                                                                         |
| -------------------------------------                                                      |
|   0 - output([INTERNAL_FUNCTION(test99.c1, test99.c2, test99.c3)]), filter(nil), rowset=16 |
|   1 - output([INTERNAL_FUNCTION(test99.c1, test99.c2, test99.c3)]), filter(nil), rowset=16 |
|       dop=1                                                                                |
|   2 - output([test99.c1], [test99.c2], [test99.c3]), filter(nil), rowset=16                |
|       force partition granule                                                              |
|   3 - output([test99.c1], [test99.c2], [test99.c3]), filter([test99.c3 = 1]), rowset=16    |
|       access([test99.c1], [test99.c2], [test99.c3]), partitions(p[0-1])                    |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],          |
|       range_key([test99.c1], [test99.c2]), range(MIN,MIN ; MAX,MAX)always true             |
+--------------------------------------------------------------------------------------------+
19 rows in set (0.180 sec)

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

示例一：

obclient [mysql]> 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

在 OceanBase 数据库的 MySQL 模式中，为了兼容 MySQL 行为，会和 MySQL 对 random expressions 进行一些限制。

示例二：

obclient [mysql]> 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

为了兼容 MySQL 行为，OB 对生成列的使用也进行了限制，生成列里也不允许出现 UNIX_TIMESTAMP 这个特殊的表达式，所以用生成列绕过，并没什么用。

说明

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

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

obclient [mysql]> select UNIX_TIMESTAMP();

输出结果如下：

+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1727246500 |
+------------------+
1 row in set (0.001 sec)

obclient [mysql]> select UNIX_TIMESTAMP();

输出结果如下：

+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1727246512 |
+------------------+
1 row in set (0.000 sec)

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

详细内容参见：分区类型其中有一种分区方式叫 Range Columns，和 Range 分区十分类似，优点是相比 Range 分区可以支持更多的数据类型，例如用户需要的 datetime 类型，缺点是分区定义不支持表达式。因为 Range 不支持 UNIX_TIMESTAMP 这类特殊的非 deterministic 表达式，所以个人理解这里可以通过 Range Columns 解决用户的问题，示例如下。

obclient [mysql]> 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.071 sec)

Range 分区和 Range Columns 分区的区别

有关于 RANGE COLUMNS partitioning 的介绍参见 MySQL 的官网文档 RANGE COLUMNS partitioning，网页界面如下。

通过利用 to_days 函数代替 UNIX_TIMESTAMP 函数的方式解决第三个问题，这样就不需要更改 Range 分区为 Range Columns 分区了。

示例如下。

创建 Range 分区表。

###-- 分区字段是 start_time，类型 datetime。
obclient [mysql]> 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'))
      );
Query OK, 0 rows affected (0.065 sec)

适用版本

OceanBase 数据库 V4.x 版本。

创建表报错 -5199, Row size too large 的原因和解决方法

OceanBase 数据库 V2.x，V3.x 版本中进行表级恢复卡在 MODIFY_SCHEMA 阶段的原因和解决方法

OceanBase 数据库