Mysql 表分区

常用操作

建表时创建分区

create table user(
	id int auto_increment,
	name varchar(200),
	birtday date,
	gender char(4),
	age tinyint
	primary key (id,age) -- 在有主键的表内，分区的字段必须是主键或存在于联合主键内
) partition by range(age)
(
	partition p1 values less than (20),
	partition p2 values less than(30),
	partition p3 values less than(40),
	partition p3 values less than MAXVALUE --必须加上这个，否则插入>=40会报错
);
-- 如果不加 MAXVALUE 插入年龄>=40的会报错
insert into user values (null,'zs','2021-01-01','F',40);

创建表后创建分区

alter table user partition by range( year(birthday) )
(
    partition p2019 values less than (2020),
    partition p2020 values less than (2021),
    partition p2021 values less than (2022),
    partition pMAXVALUE values less than MAXVALUE
)

删除分区

alter table user remove partitioning;

分区合并与拆分

-- 将pMAXVALUE拆分成2个分区一个存放2023年的数据，一个存放其他数据
alter table user reorganize partition pMAXVALUE into 
(
	partition p2022 values less than 2023,
    partition pMAXVALUE values less than MAXVALUE
);
-- 将p2020，p2021分区合并
alter table user reorganize partition p2020,p2021 into 
(
	partition p2021 values less than 2021
);

查看分区

-- information_schema.partitions 内存放了所有表的分区信息，这里只查询几个关键字段
select 
  partition_name part,  
  partition_expression expr,  
  partition_description descr,  
  table_rows  
from information_schema.partitions  where 
  table_schema = schema()  
  and table_name='p4';

Range分区

使用int类型的字段 或 返回值为 int 的表达式(int) 进行分区

create table user(
	id int,
    name varchar(20),
    age int,
    birthday date
) partition by range( year(birthday) )
(
	partition p2019 values less than (2020),
    partition p2020 values less than (2021),
    partition pMAXVALUE values less than MAXVALUE
)

List分区

list分区类似于Range分区，不同的时list分区支持一个分区存放多个值，缺点是 不能使用 MAXVALUE

create table user(
	id int,
    name varchar(200),
    age int,
    birthday date
) partition by list(year(birthday))
(
  partition p2019-2020 values in (2019,2020),
  partition p2021-2022 values in (2021,2022)
)

Hash分区

使用返回值不为负数的函数或表达式 进行hash运算后分区，mysql 还支持线性hash

-- 根据birthday分5个区，mysql会使用hash取模尽量使数据分布在5个分区内
create table user (
	id int ,
    name varchar(10),
    age int,
    birthday date
);
alter table p4 partition by hash (to_days(birthday)) partitions 5;

线性hash

按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有极其大量（1000吉）数据的表。它的缺点在于，与使用常规HASH分区得到的数据分布相比，各个分区间数据的分布不大可能均衡。

create table user (
	id int ,
    name varchar(10),
    age int,
    birthday date
);
-- 线性hash语法上只是在hash前面加了 linar 表示使用线性hash
alter table p4 partition by linar hash (to_days(birthday)) partitions 5;

key分区

key分区 和hash分区不同的是 只能使用字段，mysql会根据提供的字段选择合适的hash算法运算,key也支持线性hash

create table user (
	id int ,
    name varchar(10),
    age int,
    birthday date
);
-- 线性hash语法上只是在hash前面加了 linar 表示使用线性hash
alter table p4 partition by linar key (birthday) partitions 5;

range 多列区

range 分区允许使用多列进行分区

create table user(
	id int,
    name varchar(20),
    age int,
    salary int
) partition by range columns (age,money)
(
	partition 18_5000 values less than (19,5000),
    partition 18_8000 values less than (19,8000),
    partition 30_8000 values less than (31,5000),
    partition 30_8000 values less than (31,8000),
    partition max_max values less than (MAXVALUE,MAXVALUE)
);
insert into user values (1,'zs',18,9000); -- 存放在 max_max 分区
insert into user values (1,'zs',18,5000); -- 存放在 30_8000 分区

子分区

可以为分区创建子分区（只分区只能是hash，key类型的分区）

create table user(
	id int,
    name varchar(200),
    age int,
    birthday date
) partition by range columns (birthday) subpartition by key(name) subpartitions 5
(
    partition p2022 values less than ('2023-01-01'),
    partition pMAXVALUE values less than MAXVALUE
)

https://blog.csdn.net/qq_35726305/article/details/81221721

https://blog.csdn.net/youzhouliu/article/details/52757043