为什么要分区
一个表中的数据量太大,对数据的操作会变慢,不便于存储
注:当过滤条件为分区的字段时才会自动寻找分区,否则还是全表扫描
水平分区的几种类型
- range分区
就是按照范围进行分区,只支持整型
drop table if exists `range_table`;
create table `range_table`(
`id` int,
`name` varchar(10)
)
partition by range(id)(
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than maxvalue
);
# 上面以id为分区字段,根据id大小划分为[-∞, 10),[10, 20),[20, +∞]三个区间,注意包前不包后,在数据插入时会自动根据id插入到各自分区
2.list分区
list就是枚举的意思,list分区就是在创建各分区时具体指定哪些值属于这些分区,只能是int型。
以部门作为分区依据,每个部门做一分区。
drop table if exists `list_table`;
create table `list_table`(
`id` int,
`name` varchar(10)
)
partition by list(id)(
partition p1 values in (1),
partition p2 values in (10,15,50)
);
id为1的保存到了p1分区,id为10,15,20的保存到了p2分区,
注意如果插入数据的 id 在各个分区所对应着的列表里面都没找到,则会报错。
- hash分区
在分区字段上基于分区个数的取模运算,根据余数分区
常规hash
drop table if exists `hash_table`;
create table `hash_table`(
`id` int,
`name` varchar(10)
)
partition by hash(id)
partitions 3;
hash分区不能指定分区名,会默认创建名为pn的分区,n从0开始自增。上面这段代码会创建p0,p1,p2三个分区
线性hash
线性hash在建表时只是比常规hash多了个linear字段:
drop table if exists `hash_linear_table`;
create table `hash_linear_table`(
`id` int,
`name` varchar(10)
)
partition by linear hash(id)
partitions 3;
4.key分区
key分区类似于hash分区,只不过分区列不再强制为整型,可以为除text和BLOB两种类型外的其它类型。key分区也有两种,常规key和线性key,常规key对分区字段采用的是MD5算法,线性key对分区字段采用的是二次方算法
当表中只有主键primary key或只有唯一键unique key时,分区列必须包含主键或唯一键中的部分或全部字段,不允许出现主键或唯一键中字段以外的其它字段
当表中主键和唯一键同时存在时,分区列为主键和唯一键公共字段的部分或全部
当表中主键唯一键都没有时:任意指定除text和BLOB类型外的其它字段,可以为1个或多个
drop table if exists `key_table`;
create table `key_table`(
`id` int,
`name` varchar(10) not null,
unique `uk_name` (name)
)
partition by key()
partitions 3;
-- 线性key分区
drop table if exists `key_table`;
create table `key_table`(
`id` int,
`name` varchar(10) not null,
unique `uk_name` (name)
)
partition by linear key()
partitions 3;
- 子分区(复合分区)
对采用range分区或者list分区的表,进行二次分区,二次分区只能为hash分区或者key分区。这种分区方式有两种建表写法,一种是指定子分区名,一种是不指定子分区名由系统默认。
drop table if exists `subpart_table`;
create table `subpart_table`(
dt date
)
partition by range(year(dt))
subpartition by hash(month(dt))
subpartitions 2 (
partition p1 values less than (1990),
partition p2 values less than (2000),
partition p3 values less than maxvalue
);
6.columns分区
查看分区
select
partition_name part,
partition_expression expr,
table_rows,
partition_description descr
from information_schema.partitions where
table_schema = schema()
and table_name='basic_ocid';
MySQL分区处理NULL值的方式
如果分区键所在列没有notnull约束。
如果是range分区表,那么null行将被保存在范围最小的分区。
如果是list分区表,那么null行将被保存到list为0的分区。
在按HASH和KEY分区的情况下,任何产生NULL值的表达式mysql都视同它的返回值为0。
为了避免这种情况的产生,建议分区键设置成NOT NULL
删除表的所有分区
Alter table emp removepartitioning;--不会丢失数据
alter table emp drop partition p1;
不可以删除hash或者key分区。
alter table emp drop partition p1,p2;
一次性删除多个分区,
合并分区:
Merge分区:把2个分区合并为一个。
alter tablereorganize partition p1,p3 into
(partition p1 values less than (1000));
----不会丢失数据
增加分区:
alter table emp add partition (partition p3 values less than (4000));
alter table empl add partition (partition p3 values in (40));
重新定义
----不会丢失数据,hash分区表:
Alter table emp partition by hash(salary)partitions 7;
----range分区表,不会丢失数据
Alter table emp partitionbyrange(salary)
(
partition p1 values less than (2000),
partition p2 values less than (4000)
);