MySQL分区表的基本入门教程

作者:袖梨 2022-06-29

本篇文章小编给大家分享一下MySQL分区表的基本入门教程,代码介绍的很详细,小编觉得挺不错的,现在分享给大家供大家参考,有需要的小伙伴们可以来看看。

分区类型

Range分区:最为常用,基于属于一个给定连续区间的列值,把多行分配给分区。最常见的是基于时间字段. 基于分区的列最好是整型,如果日期型的可以使用函数转换为整型。

pst分区:pST分区和RANGE分区类似,区别在于pST是枚举值列表的集合,RANGE是连续的区间值的集合。

Hash分区:基于给定的分区个数,将数据分配到不同的分区,HASH分区只能针对整数进行HASH,对于非整形的字段只能通过表达式将其转换成整数。

Key分区:KEY分区其实跟HASH分区差不多,不同点如下:

KEY分区允许多列,而HASH分区只允许一列。

如果在有主键或者唯一键的情况下,key中分区列可不指定,默认为主键或者唯一键,如果没有,则必须显性指定列。

KEY分区对象必须为列,而不能是基于列的表达式。

KEY分区和HASH分区的算法不一样,PARTITION BY HASH (expr),MOD取值的对象是expr返回的值,而PARTITION BY KEY (column_pst),基于的是列的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文件

分区表

新增分区

alter table access_log add partition(
 partition p4 values less than (to_days('20190105'))
);

删除分区

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)
)

Table has no partition for value 737425:因为分区的范围没有包含所有可能的记录的值

相关文章

精彩推荐