网站顶部广告图片,南宁网站建设产品介绍,佛山做app网站,网站怎么做谷歌推广在《分区策略和管理分区计划的实践方案》这篇文章中#xff0c;我们介绍了在ODC中制定分区策略及有效管理分区计划的经验。有不少用户在该帖下提出了使用中的问题#xff0c;其中一个关于创建分区的限制条件的问题#xff0c;也是很多用户遭遇的老问题。因此本文以其为切入我们介绍了在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 tables 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]), rowset16 |
| access([t1.c1], [t1.c2], [t1.c3]), partitions(p0) |
| is_index_backfalse, is_global_indexfalse, 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), rowset16 |
| 1 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3)]), filter(nil), rowset16 |
| dop1 |
| 2 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), rowset16 |
| force partition granule |
| 3 - output([t1.c1], [t1.c2], [t1.c3]), filter([t1.c3 1]), rowset16 |
| access([t1.c1], [t1.c2], [t1.c3]), partitions(p[0-1]) |
| is_index_backfalse, is_global_indexfalse, 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 的官网要更清楚些在这里推荐给对分区方式感兴趣的朋友阅读~ 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)));
