MySQL每天自動增加分區

標籤：

有一個表tb_3a_huandan_detail，每天有300W左右的資料。查詢太慢了，網上瞭解了一下，可以做表分區。由於資料較大，所以決定做定時任務每天執行存過自動進行分區。

1、在進行自動增加分區前一定得先對錶手動分幾個區

1 ALTER TABLE tb_3a_huandan_detail PARTITION BY RANGE (TO_DAYS(ServiceStartTime))2 (3     PARTITION p20160523 VALUES LESS THAN (TO_DAYS(‘2016-05-23‘)),4     PARTITION p20160524 VALUES LESS THAN (TO_DAYS(‘2016-05-24‘)),5     PARTITION p20160525 VALUES LESS THAN (TO_DAYS(‘2016-05-25‘)),6     PARTITION p20160526 VALUES LESS THAN (TO_DAYS(‘2016-05-26‘)),7     PARTITION p20160527 VALUES LESS THAN (TO_DAYS(‘2016-05-27‘))8 )

2、分區存過如下：

 1 DELIMITER $$ 2  3 USE `nres`$$ 4  5 DROP PROCEDURE IF EXISTS `create_Partition_3Ahuadan`$$ 6  7 CREATE DEFINER=`nres`@`%` PROCEDURE `create_Partition_3Ahuadan`() 8 BEGIN 9 /* 交易回復，其實放這裡沒什麼作用，ALTER TABLE是隱式提交，復原不了的。*/10     DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;11     START TRANSACTION;12  13 /* 到系統資料表查出這個表的最大分區，得到最大分區的日期。在建立分區的時候，名稱就以日期格式存放，方便後面維護 */14     SELECT REPLACE(partition_name,‘p‘,‘‘) INTO @P12_Name FROM INFORMATION_SCHEMA.PARTITIONS 15     WHERE table_name=‘tb_3a_huandan_detail‘ ORDER BY partition_ordinal_position DESC LIMIT 1;16      SET @Max_date= DATE(DATE_ADD(@P12_Name+0, INTERVAL 1 DAY))+0;17 /* 修改表，在最大分區的後面增加一個分區，時間範圍加1天 */18     SET @s1=CONCAT(‘ALTER TABLE tb_3a_huandan_detail ADD PARTITION (PARTITION p‘,@Max_date,‘ VALUES LESS THAN (TO_DAYS (‘‘‘,DATE(@Max_date),‘‘‘)))‘);19     /* 輸出查看增加分區語句*/20     SELECT @s1;21     PREPARE stmt2 FROM @s1;22     EXECUTE stmt2;23     DEALLOCATE PREPARE stmt2;24 /* 取出最小的分區的名稱，並刪除掉 。25     注意：刪除分區會同時刪除分區內的資料，謹慎 */26     /*select partition_name into @P0_Name from INFORMATION_SCHEMA.PARTITIONS 27     where table_name=‘tb_3a_huandan_detail‘ order by partition_ordinal_position limit 1;28     SET @s=concat(‘ALTER TABLE tb_3a_huandan_detail DROP PARTITION ‘,@P0_Name);29     PREPARE stmt1 FROM @s; 30     EXECUTE stmt1; 31     DEALLOCATE PREPARE stmt1; */32 /* 提交 */33     COMMIT ;34  END$$35 36 DELIMITER ;

3、增加定時事件

 1 DELIMITER ||   2 CREATE EVENT Partition_3Ahuadan_event   3           ON SCHEDULE   4           EVERY 15 day STARTS ‘2016-05-27 23:59:59‘    5           DO   6       BEGIN   7  8           CALL nres.`create_Partition_3Ahuadan`;   9         10  END ||  11 DELIMITER ;  

 

參考：

http://blog.csdn.net/m582445672/article/details/7670743

http://blog.chinaunix.net/uid-24086995-id-127389.html

 

MySQL每天自動增加分區