OB MySQL 分区表支持用自增列作为分区键示例

OceanBase 早期版本里,MySQL 的自增列是不能作为分区表的分区键。原因是分区表的自增列在各个分区里是分段生成的,而要插入的值要落入到哪个分区则先看分区列的值。这是一个矛盾。OceanBase 从3.1 版本改进了这个功能,分区表的分区键如果是自增列时,还会生成分布式INSERT 执行计划,提前生成写入的值,从而破除了之前的功能限制。

示例:

CREATE TABLE `t1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `c1` varchar(50) DEFAULT NULL, `c2` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `t1_ind1` (`c2`) BLOCK_SIZE 16384 GLOBAL, KEY `t1_ind2` (`c1`, `c2`) BLOCK_SIZE 16384 LOCAL ) AUTO_INCREMENT = 4000004 DEFAULT CHARSET = utf8mb4 ROW_FORMAT = COMPACT COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 partition by hash(id) partitions 9; insert into t1 values(); insert into t1 values(); insert into t1 values(); … insert into t1 values(); select * from t1; MySQL [test]> select * from t1; MySQL [test]> select * from t1 order by c2; ±--------±-----±--------------------+ | id | c1 | c2 | ±--------±-----±--------------------+ | 2000020 | NULL | 2021-11-01 19:03:01 | | 2000021 | NULL | 2021-11-01 19:03:02 | | 2000022 | NULL | 2021-11-01 19:03:03 | | 2000023 | NULL | 2021-11-01 19:03:39 | | 2000025 | NULL | 2021-11-01 19:03:40 | | 2000024 | NULL | 2021-11-01 19:03:40 | ±--------±-----±--------------------+ 6 rows in set (0.104 sec)

退出重新登录。 继续插入。

MySQL [test]> insert into t1 values(); Query OK, 1 row affected (0.272 sec) MySQL [test]> insert into t1 values(); Query OK, 1 row affected (0.051 sec) MySQL [test]> select * from t1 order by c2; ±--------±-----±--------------------+ | id | c1 | c2 | ±--------±-----±--------------------+ | 2000020 | NULL | 2021-11-01 19:03:01 | | 2000021 | NULL | 2021-11-01 19:03:02 | | 2000022 | NULL | 2021-11-01 19:03:03 | | 2000023 | NULL | 2021-11-01 19:03:39 | | 2000025 | NULL | 2021-11-01 19:03:40 | | 2000024 | NULL | 2021-11-01 19:03:40 | | 1000090 | NULL | 2021-11-01 19:04:13 | | 1000091 | NULL | 2021-11-01 19:04:14 | ±--------±-----±--------------------+ 8 rows in set (0.087 sec) MySQL [test]> select * from t1 partition (p2) order by c2; ±--------±-----±--------------------+ | id | c1 | c2 | ±--------±-----±--------------------+ | 1000091 | NULL | 2021-11-01 19:04:14 | | 2000027 | NULL | 2021-11-01 19:06:26 | ±--------±-----±--------------------+ 2 rows in set (0.041 sec) MySQL [test]> select * from t1 partition (p3) order by c2; ±--------±-----±--------------------+ | id | c1 | c2 | ±--------±-----±--------------------+ | 2000028 | NULL | 2021-11-01 19:06:26 | | 1000092 | NULL | 2021-11-01 19:07:51 | ±--------±-----±--------------------+ 2 rows in set (0.002 sec) MySQL [test]> select * from t1 partition (p4) order by c2; ±--------±-----±--------------------+ | id | c1 | c2 | ±--------±-----±--------------------+ | 2000020 | NULL | 2021-11-01 19:03:01 | | 2000029 | NULL | 2021-11-01 19:06:26 | ±--------±-----±--------------------+ 2 rows in set (0.006 sec) MySQL [test]> select * from t1 partition (p5) order by c2; ±--------±-----±--------------------+ | id | c1 | c2 | ±--------±-----±--------------------+ | 2000021 | NULL | 2021-11-01 19:03:02 | | 2000030 | NULL | 2021-11-01 19:06:26 | ±--------±-----±--------------------+ 2 rows in set (0.068 sec)

会发现分区表的自增列这个值只保证全局唯一,但不保证单调递增。即使是在一个分区内部也不一定单调递增。这算是一个实现方法的妥协吧。

欢迎留言讨论 OB 的小功能。