mysql为表添加分区

mysql为表添加分区

mysql为表添加分区 mysql创建表分区 



一、MySQL表分区介绍

1、为什么要对表进行分区

由于MySQL默认是以文件的形式将表存储在磁盘上，如果一个表存放了太多数据，那么这个文件的体积就会变大，比如一个表文件就几十个G，这种情况对查询效率和数据安全都是不利的。为了解决这个问题就需要对表进行表分区操作，表分区和分表是不同的两个概念，表分区是将一个大表分成了多个逻辑块，在磁盘上也会按照表名+分区名创建出多个文件，但是对应用来说这些分区表是透明的，看上去仍然是一张表一个表名，属于集中式。分表则是将一张大表拆成了多张小表，可以存放在不同的实例上，属于分布式。除了分区分表外，还可以根据业务来对数据进行分库操作，比如用户信息都单独放在一个用户库里，又或者每个地区有每个地区自己的用户库等。

2、MySQL表分区有哪些限制

· 从5.6开始每张表支持8192个分区

· 分区字段必须有主键/唯一索引列

· 不能使用外键约束，不能使用存储过程，不支持全文索引，不支持临时表

· 分区还可以建立子分区，也叫复合分区

3、MySQL表分区类型

range：范围分区，适合按照月份或者编号范围进行分区，比如ID1-10000为一个分区，以此类推。

hash：哈希分区，按照HASH算法来随机分到每个区

list：列表分区，适合对字符进行分区，比如按省份分区，但是数据不会太均匀

key：类似HASH分区

二、MySQL表分区示例——范围分区

1、创建一张表，并且按年份字段进行分区

create table student (
`id` int not null,
`name` varchar(20) not null,
`age` varchar(10) not null,
`sex` varchar(10) not null,
`joindate` datetime not null,
`email` varchar(20) not null,
primary key (`id`,`joindate`)
)
partition by range(YEAR(joindate))
(
partition student_2018 values less than (2019), #2019以前的数据都在student_2018分区中
partition student_2019 values less than (2020), #2020以前的数据都在student_2019分区中
partition student_2020 VALUES LESS THAN MAXVALUE #大于2020的数据都在student_2020分区中
);




2、插入测试数据，一条是2019年，一条是2020年

insert into `student` (`id`, `name`, `age`, `sex`, `joindate`, `email`) values('1','tom','33','man','2019-01-24 15:16:39','12@qq.com');
insert into `student` (`id`, `name`, `age`, `sex`, `joindate`, `email`) values('2','tom2','11','man','2020-09-08 15:17:08','24@qq.com');



3、查询数据，通过执行计划检查是否使用到了分区表

EXPLAIN SELECT * FROM student WHERE joindate='2019-01-24 15:16:39'
EXPLAIN SELECT * FROM student WHERE joindate='2020-09-08 15:17:08'



4、执行结果中可以看到分区表信息，即便在一个分区中进行了全表扫描，影响的行数也比不分区的要小很多

mysql 为表添加分区 mysql创建表分区_MySQL

5、对已分区的表增加分区

show create table student;

alter table student drop partition student_2020; #先删除最后一个分区，这个过程不会清空该分区数据，只会重新分配到其它分区中

alter table student add partition (partition student_2020 values less than (2021)) #为2020新增分区

alter table student add partition (partition student_2021 values less than maxvalue) #再创建一个新的最后分区

三、MySQL表分区示例——列表分区

create table student (
id int not null,
name varchar(20) not null,
age int not null
)
partition by list(age)
(
partition age_1 values in (20),
partition age_2 values in (30,40)
);



四、MySQL表分区示例——HASH分区

用hash分区可以指定一个分区数量，然后数据会很平均的分配到这些分区中

登录后复制 
create table student (
id int not null,
name varchar(20) not null,
age int not null
)
partition by hash(id)


partitions 5 #创建了5个分区，假如插入5W数据，那么每个分区大概就是1W条数据