51dev.com IT技术开发者社区

51dev.com 技术开发者社区

mysql单表分区

代码星球阅读(18)2020-05-23 收藏0次评论

源 https://blog.csdn.net/apple001100/article/details/75451999 

目的:为了了解mysql单表分区方法,特此作为学习笔记记录一下。

一。准备表,创建一个学生表,包含主键sid和名称sname字段

create table students(
sid int(5) primary key,
sname varchar(24)
);

二。准备数据

insert into students(sid,sname) values(10003,'tom');
insert into students(sid,sname) values(10005,'jerry');
insert into students(sid,sname) values(10006,'hengte');
insert into students(sid,sname) values(10007,'weilian');
insert into students(sid,sname) values(10000,'tom1');
insert into students(sid,sname) values(10001,'jerry2');
insert into students(sid,sname) values(10002,'hengte3');
insert into students(sid,sname) values(10004,'weilian4');

三。查询结果

select * from sutdents

四。建立分区,按照主键ID的值进行设定分区规则如下:

alter table students partition by range(sid)
(
partition p0 values less than (10001),
partition p1 values less than (10003),
partition p2 values less than (10005),
partition p3 values less than maxvalue
);

五。查询结果,可以看到查询结果分布到不同的分区里

select  * from students partition (p0);

select  * from students partition (p1);

select  * from students partition (p2);

select  * from students partition (p3);

六。验证新插入数据

insert into students(sid,sname) values(10011,'tom12');
insert into students(sid,sname) values(10012,'jerry13');
insert into students(sid,sname) values(10013,'hengte14');
insert into students(sid,sname) values(10014,'weilian15');
insert into students(sid,sname) values(10015,'tom116');
insert into students(sid,sname) values(10016,'jerry22');
insert into students(sid,sname) values(10017,'hengte32');
insert into students(sid,sname) values(10018,'weilian42');

七。再次查询分区数据,会看到新插入的数据按照分区规则划分到对应的分区里了

select  * from students partition (p0);

select  * from students partition (p1);

select  * from students partition (p2);

select  * from students partition (p3);

以上就是mysql单表分区的全部内容。