在最近的项目中,我们需要保存大量的数据,而且这些数据是有有效期的,为了提供查询效率以及快速删除过期数据,我们选择了MySQL的分区机制。把数据按照时间进行分区。
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'))
);
NULL
,要不然怎么确定分区范围呢,所以尽量NOT NULLA 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
:因为分区的范围没有包含所有可能的记录的值