2014年4月26日土曜日

MySQLのパーティションのタイムスタンプ - 日時

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

だから私は最近、私はまだMySQLのパーティションについてはあまり話していないことに気づきました。
MySQLのパーティション上の多くの良いブログの記事は、すでに存在しており、私は以下のいくつかを記載しております。
私は、次のような状況に遭遇してしまったと私はそれが他の人に役立つことを証明願っています。

timestampデータ型は、しばしば使用されるが、日時はパーティションで良い作品。
（タイムスタンプはないUPDATE CURRENT_TIMESTAMPにNOT NULLにDEFAULT 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

私は、MySQL 5.6を使用していたためであるLOCK = NONEを持っていないことに注意してください
主キーの変更は、私はまだ、データ型を調整する必要があり、私はかかわらず、パーティションを追加することはできません。 今回はSHARED = 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


我々は、SELECT、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)


のようにしても、できグリンのブログが指摘して、あなたは今、（）+区間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)


念頭に置いて、上記の例のための有効な時刻を保つ。 私は一例として、これを使用しました。

投稿者 時刻:
ラベル: , , , , , ,