详解MySQL分区表
小编这次要给大家分享的是详解MySQL分区表,文章内容丰富,感兴趣的小伙伴可以来了解一下,希望大家阅读完这篇文章之后能够有所收获。
因为努力和真诚,有更多的客户和我们聚集在一起,为了共同目标,成都创新互联公司在工作上密切配合,从创业型企业到如今不断成长,要感谢客户对我们的高要求,让我们敢于面对挑战,才有今天的进步与发展。从网站到微信小程序定制开发,软件开发,成都App定制开发,10年企业网站建设服务经验,为企业提供网站设计,网站托管一条龙服务.为企业提供营销型网站建设,定制制作,原创设计,10年品质,值得您的信赖.
前言
在最近的项目中,我们需要保存大量的数据,而且这些数据是有有效期的,为了提供查询效率以及快速删除过期数据,我们选择了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文件
分区表
新增分区
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:因为分区的范围没有包含所有可能的记录的值
看完这篇关于详解MySQL分区表的文章,如果觉得文章内容写得不错的话,可以把它分享出去给更多人看到。
当前题目:详解MySQL分区表
网站链接:http://myzitong.com/article/ppgogc.html