另一個MySQL DBA: MySQL的分區時間戳

Original post: http://anothermysqldba.blogspot.com/2014/04/mysql-partitions-timestamp-datetime.html

所以最近我意識到，我還沒有談過很多關於MySQL的分區。
在MySQL分區很多好的博客文章已經存在，並且我已經列出了幾個在下面。

我碰巧遇到以下的情況，我希望它證明了幫助他人。

而timestamp數據類型通常用於日期時間的作品更好地與分區。
（時間戳NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP）

所以下面是一個例子，如何如何解決這個問題。

首先我們有這個簡單的表。


 CREATE TABLE `t1` ( 
 `t1_id` int(11) NOT NULL AUTO_INCREMENT, 
 `field1` varchar(25) DEFAULT NULL, 
 `field2` int(10) DEFAULT '0', 
 `time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
 PRIMARY KEY (`t1_id`), 
 KEY `tr` (`time_recorded`) 
 ) ENGINE=InnoDB AUTO_INCREMENT=856964

我們希望在time_recorded場分區。為了讓我們直接將打破它每月。


 ALTER TABLE t1 
 PARTITION BY RANGE ( TO_DAYS(time_recorded) ) ( 
 PARTITION Jan2014 VALUES LESS THAN (TO_DAYS('2014-02-01')), 
 PARTITION Feb2014 VALUES LESS THAN (TO_DAYS('2014-03-01')), 
 PARTITION Mar2014 VALUES LESS THAN (TO_DAYS('2014-04-01')), 
 PARTITION Apr2014 VALUES LESS THAN (TO_DAYS('2014-05-01')), 
 PARTITION May2014 VALUES LESS THAN (TO_DAYS('2014-06-01')), 
 PARTITION Jun2014 VALUES LESS THAN (TO_DAYS('2014-07-01')), 
 PARTITION Jul2014 VALUES LESS THAN (TO_DAYS('2014-08-01')), 
 PARTITION Aug2014 VALUES LESS THAN (TO_DAYS('2014-09-01')), 
 PARTITION Sep2014 VALUES LESS THAN (TO_DAYS('2014-10-01')), 
 PARTITION Oct2014 VALUES LESS THAN (TO_DAYS('2014-11-01')), 
 PARTITION Nov2014 VALUES LESS THAN (TO_DAYS('2014-12-01')), 
 PARTITION Dec2014 VALUES LESS THAN (TO_DAYS('2015-01-01')), 
 PARTITION Jan2015 VALUES LESS THAN (TO_DAYS('2015-02-01')) 
 ); 
 ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

所以，現在什麼....

嗯，我知道我們需要的主鍵更新，如果我們想要的分區。


 ALTER TABLE t1  DROP PRIMARY KEY, ADD PRIMARY KEY (`t1_id`,`time_recorded`), LOCK=NONE; 
 Query OK, 0 rows affected (38.96 sec) 
 Records: 0  Duplicates: 0  Warnings: 0

請注意，我有鎖= NONE這是因為我使用的MySQL 5.6

主鍵更改將不會允許我雖然添加的分區，我還是需要調整的數據類型。我正在使用共享這個時候LOCKED =。請仔細閱讀上面的超鏈接獲取更多信息。如果你碰巧撿不工作一個類型，它通常意味著一個可行的解決方案。


 ALTER TABLE t1 CHANGE  time_recorded  time_recorded datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, LOCK=SHARED; 
 Query OK, 854312 rows affected (41.89 sec) 
 Records: 854312  Duplicates: 0  Warnings: 0

所以，現在我們可以添加我們的分區。


 ALTER TABLE t1 
 ->  PARTITION BY RANGE ( TO_DAYS(time_recorded) ) ( 
 ->      PARTITION Jan2014 VALUES LESS THAN (TO_DAYS('2014-02-01')), 
 ->      PARTITION Feb2014 VALUES LESS THAN (TO_DAYS('2014-03-01')), 
 ->      PARTITION Mar2014 VALUES LESS THAN (TO_DAYS('2014-04-01')), 
 ->      PARTITION Apr2014 VALUES LESS THAN (TO_DAYS('2014-05-01')), 
 ->      PARTITION May2014 VALUES LESS THAN (TO_DAYS('2014-06-01')), 
 ->      PARTITION Jun2014 VALUES LESS THAN (TO_DAYS('2014-07-01')), 
 ->      PARTITION Jul2014 VALUES LESS THAN (TO_DAYS('2014-08-01')), 
 ->      PARTITION Aug2014 VALUES LESS THAN (TO_DAYS('2014-09-01')), 
 ->      PARTITION Sep2014 VALUES LESS THAN (TO_DAYS('2014-10-01')), 
 ->      PARTITION Oct2014 VALUES LESS THAN (TO_DAYS('2014-11-01')), 
 ->      PARTITION Nov2014 VALUES LESS THAN (TO_DAYS('2014-12-01')), 
 ->      PARTITION Dec2014 VALUES LESS THAN (TO_DAYS('2015-01-01')), 
 ->      PARTITION Jan2015 VALUES LESS THAN (TO_DAYS('2015-02-01')) 
 ->  ); 
 Query OK, 854312 rows affected (50.74 sec) 
 Records: 854312  Duplicates: 0  Warnings: 0

我們可以選擇，DELETE，UPDATE，INSERT和每個分區等。更多關於這裡： https://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html 。


 SELECT COUNT(t1_id) FROM t1 PARTITION (Jan2014); 
 +--------------+ 
 | COUNT(t1_id) | 
 +--------------+ 
 |       661752 | 
 +--------------+ 
 1 row in set (0.55 sec) 
 SELECT COUNT(t1_id) FROM t1 PARTITION (Feb2014); 
 +--------------+ 
 | COUNT(t1_id) | 
 +--------------+ 
 |        64952 | 
 +--------------+ 
 1 row in set (0.04 sec) 
 SELECT COUNT(t1_id) FROM t1 PARTITION (Mar2014); 
 +--------------+ 
 | COUNT(t1_id) | 
 +--------------+ 
 |        71336 | 
 +--------------+ 
 1 row in set (0.04 sec) 
 SELECT COUNT(t1_id) FROM t1 PARTITION (Apr2014); 
 +--------------+ 
 | COUNT(t1_id) | 
 +--------------+ 
 |        56272 | 
 +--------------+ 
 1 row in set (0.05 sec)

而這個工程，我們現在要做有一個分區。我們現在也必須要考慮的分區維護。格林的博客上給出了一個非常漂亮的自動滴的例子和分區的增加。誠然，如果你不想放棄你將需要調整的例子分區。
我已經調整了我的表的例子。請仔細閱讀格林的博客更多的細節。


 DROP PROCEDURE IF EXISTS Rotate_t1_Partition; 
 DELIMITER ;; 
 CREATE PROCEDURE Rotate_t1_Partition (newPartValue DATETIME) 
 BEGIN 
 -- Setup 
 DECLARE keepStmt VARCHAR(2000) DEFAULT @stmt; 
 DECLARE partitionToDrop VARCHAR(64); 
 
 -- Find and drop the first partition in the table. 
 SELECT partition_name 
 INTO partitionToDrop 
 FROM INFORMATION_SCHEMA.PARTITIONS 
 WHERE table_schema='forums_mysql' 
 AND table_name='t1' 
 AND partition_ordinal_position=1; 
 SET @stmt = CONCAT('ALTER TABLE t1 DROP PARTITION ',  partitionToDrop); 
 PREPARE pStmt FROM @stmt; 
 EXECUTE pStmt; 
 DEALLOCATE PREPARE pStmt; 
 
 -- Add a new partition using the input date for a value limit. 

 SET @stmt = CONCAT('ALTER TABLE t1 ADD PARTITION (PARTITION ', 
DATE_FORMAT(newPartValue - interval 1 MONTH, '%b%Y'), ' VALUES LESS THAN
 (TO_DAYS(\'',  DATE_FORMAT(newPartValue, '%Y-%m-%d'),'\')))'); 
 PREPARE pStmt FROM @stmt; 
 EXECUTE pStmt; 
 DEALLOCATE PREPARE pStmt; 
 
 -- Cleanup 
 SET @stmt = keepStmt; 
 END;; 
 DELIMITER ;

所以，我現在可以更新這個表很容易地以下。


 CALL Rotate_t1_Partition('2015-03-01'); 
 Query OK, 0 rows affected (1.11 sec)

你也可以，因為格林的博客指出的那樣，你可以使用這個用NOW（）+間隔1個月，你決定為你的分區永遠的時間框架。格蘭特，你必須記住，如果你傳遞已經是一個分區的日期將會失敗。

要繼續與自動化，您可以將它添加到一個事件。


 CREATE EVENT Rotate_t1_Partition 
 -> ON SCHEDULE EVERY 1 MONTH 
 -> DISABLE ON SLAVE 
 -> COMMENT 'Remove oldest partition and add a new one ' 
 -> DO 
 -> CALL Rotate_t1_Partition(NOW() + interval 1 MONTH); 
 Query OK, 0 rows affected (0.04 sec)

記住對於上面的例子中的有效時間。我只是用這個作為一個例子。

另一個MySQL DBA

Pages

2014年4月26日星期六

MySQL的分區時間戳 - 日期時間