参考:https://www.cnblogs.com/lanceblog/p/5532068.html
有一个表tb_3a_huandan_detail,每天有300W左右的数据。查询太慢了,网上了解了一下,可以做表分区。由于数据较大,所以决定做定时任务每天执行存过自动进行分区。
1、在进行自动增加分区前一定得先对表手动分几个区
ALTER TABLE tb_3a_huandan_detail PARTITION BY RANGE (TO_DAYS(ServiceStartTime))
(
PARTITION p20160523 VALUES LESS THAN (TO_DAYS('2016-05-23')),
PARTITION p20160524 VALUES LESS THAN (TO_DAYS('2016-05-24')),
PARTITION p20160525 VALUES LESS THAN (TO_DAYS('2016-05-25')),
PARTITION p20160526 VALUES LESS THAN (TO_DAYS('2016-05-26')),
PARTITION p20160527 VALUES LESS THAN (TO_DAYS('2016-05-27'))
)
2、分区存过如下:
DELIMITER $$
USE `nres`$$
DROP PROCEDURE IF EXISTS `create_Partition_3Ahuadan`$$
CREATE DEFINER=`nres`@