MYSQL 数据库分区

为什么要分区

一个表中的数据量太大，对数据的操作会变慢，不便于存储

注：当过滤条件为分区的字段时才会自动寻找分区，否则还是全表扫描

水平分区的几种类型

1. 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 在各个分区所对应着的列表里面都没找到，则会报错。

1. 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;

1. 子分区(复合分区)

对采用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)
);

参考：MySQL表分区_mysql 分区_atwdy的博客-CSDN博客