MySQL分区表的基本入门教程

2021-01-20 18:28十毛tenmao Mysql

这篇文章主要给大家介绍了关于MySQL分区表的基本入门教程，文中通过示例代码介绍的非常详细，对大家学习或者使用MySQL具有一定的参考学习价值，需要的朋友们下面来一起学习学习吧

前言

在最近的项目中，我们需要保存大量的数据，而且这些数据是有有效期的，为了提供查询效率以及快速删除过期数据，我们选择了MySQL的分区机制。把数据按照时间进行分区。

分区类型

Range分区：最为常用，基于属于一个给定连续区间的列值，把多行分配给分区。最常见的是基于时间字段. 基于分区的列最好是整型，如果日期型的可以使用函数转换为整型。
List分区：LIST分区和RANGE分区类似，区别在于LIST是枚举值列表的集合，RANGE是连续的区间值的集合。
Hash分区：基于给定的分区个数，将数据分配到不同的分区，HASH分区只能针对整数进行HASH，对于非整形的字段只能通过表达式将其转换成整数。
Key分区：KEY分区其实跟HASH分区差不多，不同点如下：
- KEY分区允许多列，而HASH分区只允许一列。
- 如果在有主键或者唯一键的情况下，key中分区列可不指定，默认为主键或者唯一键，如果没有，则必须显性指定列。
- KEY分区对象必须为列，而不能是基于列的表达式。
- KEY分区和HASH分区的算法不一样，PARTITION BY HASH (expr)，MOD取值的对象是expr返回的值，而PARTITION BY KEY (column_list)，基于的是列的MD5值。

分区命令

创建分区

									CREATE TABLE `access_log` (

									 `id` int(11) NOT NULL AUTO_INCREMENT,

									 `access_time` datetime NOT NULL,

									 PRIMARY KEY (`id`,`access_time`)

									) ENGINE=InnoDB DEFAULT CHARSET=utf8

									/*!50100 PARTITION BY RANGE (to_days(access_time))

									(PARTITION p1 VALUES LESS THAN (to_days(20190101)) ENGINE = InnoDB,

									 PARTITION p2 VALUES LESS THAN (to_days(20190102)) ENGINE = InnoDB,

									 PARTITION p3 VALUES LESS THAN (to_days(20190103)) ENGINE = InnoDB) */;

创建后可以看到，每个分区都会对应1个ibd文件

MySQL分区表的基本入门教程
分区表

新增分区

									alter table access_log add partition(

									 partition p4 values less than (to_days('20190105'))

									);

删除分区

1	`alter` `table` `access_log` `drop` `partition p1;`

拆分分区

									alter table access_log reorganize partition p4 into(

									 -> partition s0 values less than(to_days('20190104')),

									 -> partition s1 values less than(to_days('20190105'))

									 -> );

合并分区

									alter table access_log reorganize partition s0,s1 into ( 

									 partition p4 values less than (to_days('20190105')) 

									);

注意事项

MySQL分区中如果存在主键或唯一键，则分区列必须包含在其中（否则判断主键或唯一时，需要扫描所有分区）
分区字段不能为NULL，要不然怎么确定分区范围呢，所以尽量NOT NULL
最大分区数目不能超过1024
不支持外键
只能对数据表的整型列进行分区，或者数据列可以通过分区函数转化成整型列
分区表不影响自增列

常见问题

A PRIMARY KEY must include all columns in the table's partitioning function：这样的话判断主键是否唯一就可以在单个分区内部完成，否则就需要跨所有的分区
MAXVALUE can only be used in last partition definition：RANGE表分区后不能带MAXVALUE分区，否则无法增加分区。或者就只能重新分区了

									alter table access_log partition by range(to_days(access_time))(

									partition p1 values less than (to_days('20191202')), 

									partition p2 values less than (to_days('20191203')), 

									 partition po values less than (maxvalue)

									)