一、分区表的创建
(一)分区表的纬度
1.本文对分区表的类别的区分纬度
分区表又名分片表,其通过分区的方式、分区表位置和分区表名称设置三种构成:
-
分区表的方式
-
表达式分片
-
轮训法分片
-
列表分区
-
间隔自动分片
-
-
分区表位置
-
每个分片表一个数据空间
-
多个分片表的位置设置在一个数据空间
-
-
分区表名称
-
指定分区表名称
-
不指定分区表名称
-
注意事项
-
remainder分片没有需要的数据也会被扫描,所以尽量避免使用reaminder分片
(二)表达式分区
表达式分区片是根据某种分区规则将数据根据规则分成多份,每份存在一个分区表内。
表达式分区表有三种:
- 单数据空间指定分区表名称
- 多数据库空间指定分区表名称
- 多数据库空间不指定分区表名称
数据库空间说明:
- 本次测试使用的数据库空间为datadbs1,datadbs2,datadbs3,datadbs4
- sql语句后的in datadbs指的是当分区表的数据库空间
1.表达式分片单数据空间指定分区表名称
create table p2(id int,name varchar(100))
fragment by expression
partition p2_1 id <100 in datadbs1,
partition p2_2 remainder in datadbs1;
2.表达式分片多数据空间指定分区表名称
--指定分区表的名称
create table p1_cp(id int,name varchar(10))
fragment by expression
partition p1_cp1 id<100 in datadbs1,
partition p1_cp2 id>99 and id<200 in datadbs2,
partition p1_cp3 id>199 and id <300 and name='a' in datadbs3,
partition p1_cp4 remainder in datadbs4;
3.表达式分片多数据空间未指定分区表名称
--未指定分区表的表名
create table p1(id int,name varchar(10))
fragment by expression
id<100 in datadbs1,
id>99 and id<200 in datadbs2,
id>199 and id <300 and name='a' in datadbs3,
remainder in datadbs4;
(三)轮训分片
轮循分片没有相应的规则,他是将数据依次在指定的分区表中循环存储。
轮循分片的分区表有三种:
- 单数据空间指定分区表名称
- 多数据库空间指定分区表名称
- 多数据库空间不指定分区表名称
1.轮训分片单数据空间指定分区表名称
create table p1(id int,name varchar(10))
fragment by
round robin
partition p1_1 in datadbs1,
partition p1_2 in datadbs1;
2.轮训分片多数据空间指定分区表名称
--指定分区表的表名
create table p2cp(id int,name varchar(10))
fragment by round robin
partition p11 in datadbs1,
partition p12 in datadbs2;
3.轮训分片多数据空间未指定分区表名称
--未指定分区表的表名
create table p2(id int,name varchar(10))
fragment by
round robin in datadbs1,datadbs2,datadbs3,datadbs4;
(四)列表分片
1.列表分片单数据空间指定分区表名称
create table p3(name varchar(10),keben varchar(10),fen int)
fragment by list(name)
partition p3_a values('a') in datadbs1,
partition p3_b values('b') in datadbs1,
partition p3_re remainder in datadbs1;
2.列表分片多数据空间指定分区表名称
create table p3(name varchar(10),keben varchar(10),fen int)
fragment by list(name)
partition p3_a values('a') in datadbs1,
partition p3_b values('b') in datadbs2,
partition p3_re remainder in datadbs3;
(四)间隔自动分区
1.数值间隔分区
需要配置空值的分区和小于0的分区,需要两个数据空间,其他的分区表名称会自动创建
- interval(1000)表示数值没间隔1000就建立一个分区
create table p1(id int,name varchar(10))
fragment by range(id)
interval(1000) store in(datadbs1,datadbs2)
partition p0 values is null in datadbs1,
partition p1_1 values<0 in datadbs2;
2.时间间隔分区
需要确定现场环境的时间类型格式
- interval:1 units day间隔1天
- store in:将会存储的数据空间
- 需要建立第一个数据空间
create table t17(id int,name varchar(110),k timestamp)
fragment by range (k)
interval(1 units day)
store in(datadbs1,datadbs2)
partition p10 values<date("2020-1-1") in rootdbs;
二、分片索引
1.分区表索引的创建
- 表不分片也可以创建分片索引
--表达式索引
create index test1_idx on test1(id)
fragment by
expression (id <=100) in datadbs,
(id>100) in data2dbs;
三、分片表的DDL语句
(一)分区表操作的注意事项
1.注意事项
-
alter fragment是单个事务,所有的移动或者增加都会写事务,注意产生长事务,可以考虑关闭日志或者将操作分成多个小的alter fragment进行操作
-
alter fragment 执行时需要表级锁,面对是否记录日志将会有两种情况
-
记录日志:当数据移动时,原先的数据会被删除移动到新的片,但删除数据时,数据占用的extent仍是被使用状态,只有所有的数据移动后才可直接使用,需要有足够的剩余空间
-
不记录日志:原先的片会被保留,直到成功后会被释放
-
2.针对分区表无法获取表级锁的特殊操作
alter fragment语句需要表级锁,在摘除分片或者挂载分片时可能由于其他业务要求无法独占表,所以为了避免这种情况,将会停止其他访问这张表的事务暂时终止的措施
--通过force_ddl_exec指定整数值,数据库尝试在这段时间内把其他事务进行回滚,以便相关操作可以进行
set environment force_ddl_exec "60";
set lock mode to wait 10;
set environment force_ddl_exec "off";
-
set environment force_ddl_exec
-
在日志记录或者ansi的数据库中使用
-
只有dbsa用户可以使用
-
如果没有设置锁等待上限,语句不会生效
-
如果另外一个用户在同一张表上执行DDL语句,alter fragment将会失败
-
如果有其他的用户也在执行这个语句,,第二个用户将会返回错误
-
如果系统表被锁住,alter fragment将会返回错误
-
(二)表从分区表和普通表之间的相互转换
1.分区表初始为普通表
--将分区表p2初始化成普通表在数据空间datadbs1
alter fragment on table p2 init in datadbs1;
2.普通表初始化为分区表
alter fragment on table p2
init fragment by list(name)
partition p2_1 values('a') in datadbs1,
partition p2_2 values('b') in datadbs1,
partition p2_3 remainder in datadbs1;
(三)表将其中一个区(片)变为普通表
1.表达式分区
--表达式分区,将分区表p3的分片p3_2和p3_3分离出去,两种写法
alter fragment on table p3 detach p3_3 p3_p;
alter fragment on table p3 detach partition p3_2 p3_2p;
2.轮询分区
--轮询分区,将p4分片p4_3分离出去
alter fragment on table p4 detach p4_3 p4_3p;
3.列表分区
--列表分区
alter fragment on table p2 detach partition p2_4 p2_4p;
4.间隔分区
--间隔分区,将分区表p1的分片sys_p34剔除变为表p_34
alter fragment online on table p1 detach partition sys_p34 p_34;
(四)将分割出去的普通表重新加入分区表(轮询不可以)
1.表达式分区
--表达式分区,将分割出去的表p3_p重新加入分区并命名为p3_3
alter fragment on table p3
attach p3_p
as
partition p3_3 id>300 and id <400;
2.列表分区
--列表分区
alter fragment on table p2
attach p2_b
as
partition p2_2 values('b');
3.间隔分区
--间隔分区
alter fragment on table p1
attach p_34
as
partition sysp34 values<34000;
(五)分区规则的改动
1.增加一个分区规则(间隔分区不可以)
--表达式分区
alter fragment on table p3 add partition p3_3 id>300 and id <400 in datadbs1;
--轮询分区
alter fragment on table t6 add datadbs1;
alter fragment on table p4 add partition p4_4 in datadbs1;
--列表分区
alter fragment on table p2 add partition p2_4 values('c') in datadbs2;
2.更改分区表的整体分区规则
alter fragment on table p1
init fragment by range (id)
interval(100) store in(datadbs1,datadbs2)
partition p0 values is null in datadbs1,
partition p1 values <0 in datadbs2;
3.更改分区表单个分区规则
alter fragment on table k1
modify k1_2
to
id <400 and id >100 in datadbs1;
4.删除一个分区
--普通表达式分区
alter fragment on table p3 drop p3_3;
--轮询分区
alter fragment on table p4 drop partition p4_4;
--列表分区
alter fragment on table p2 drop partition p2_3;
--间隔分区
alter fragment on table p1 drop partition sysp34;