🅿️ 🐳 🤵🏼 Mysql PARTITION BY YEAR（日付）/ MONTH（日付）/ DAYOFWEEK（日付） 👊🏽 👂🏼 🕣

多くの場合、更新されたデータがほとんどまたはまったく含まれていないテーブルを処理する必要があります。このようなデータの良い例は、さまざまなログです。いくつかのテーブルは、古くなったデータが定期的に消去され、一部は「永久に」レコードを保持しなければなりません。したがって、このようなテーブルは「膨張」し、それらを操作することはシステム全体にとって難しい操作になります。

ディスクとFSの負荷を軽減するために、彼らは最も単純なパーティション化を考え出しました。テーブルデータを含むファイルは、いくつかの大きなファイルではないいくつかの条件に分割されます-パーティション。ログの場合、イベントの日付を含むフィールドでテーブルを分割するのが合理的です。パーティションテーブルは、年ごと、月ごと、または月/週ごとにカットするのが賢明です。

タイムスタンプフィールドに沿ってカットする必要があることがわかります。

タブレットを作りましょう：

CREATE TABLE `foo` ( `id` int(11) NOT NULL AUTO_INCREMENT, `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `name` varchar(30) DEFAULT NULL, `email` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) );

しかし、たとえば、月ごとにログを含むテーブルを広げる必要がある場合はどうでしょうか？つまり、何かが1〜2か月保存されたテーブルに書き込まれ、その後削除されます。このようにカットしたい場合：

  ALTER TABLE foo PARTITION BY RANGE (YEAR(date_added)) ( PARTITION p2011 VALUES LESS THAN (2012) , PARTITION p2012 VALUES LESS THAN (2013) , PARTITION p2013 VALUES LESS THAN (2014) );

取得するもの：

 ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

これの説明は次のとおりです。「TIMESTAMPはローカルセッションのタイムゾーンに内部的に変換されます。」

さて：

 SELECT UNIX_TIMESTAMP('2012-01-01 00:00:00'); +---------------------------------------+ | UNIX_TIMESTAMP('2012-01-01 00:00:00') | +---------------------------------------+ | 1325361600 | +---------------------------------------+ SELECT UNIX_TIMESTAMP('2013-01-01 00:00:00'); +---------------------------------------+ | UNIX_TIMESTAMP('2013-01-01 00:00:00') | +---------------------------------------+ | 1356984000 | +---------------------------------------+ SELECT UNIX_TIMESTAMP('2014-01-01 00:00:00'); +---------------------------------------+ | UNIX_TIMESTAMP('2014-01-01 00:00:00') | +---------------------------------------+ | 1388520000 | +---------------------------------------+

今：

  ALTER TABLE foo PARTITION BY RANGE (UNIX_TIMESTAMP(date_added)) ( PARTITION p2011 VALUES LESS THAN (1325361600) , PARTITION p2012 VALUES LESS THAN (1356984000) , PARTITION p2013 VALUES LESS THAN (1388520000) , PARTITION pMAXVALUE VALUES LESS THAN (MAXVALUE) );

今、私達は得る：

 ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

それは扱われます：

 ALTER table foo DROP PRIMARY KEY, add PRIMARY KEY (`id`,`date_added`);

そしてまた：

  ALTER TABLE foo PARTITION BY RANGE (UNIX_TIMESTAMP(date_added)) ( PARTITION p2011 VALUES LESS THAN (1325361600) , PARTITION p2012 VALUES LESS THAN (1356984000) , PARTITION p2013 VALUES LESS THAN (1388520000) , PARTITION pMAXVALUE VALUES LESS THAN (MAXVALUE) );

すべてが大丈夫です。

取得するもの：

 CREATE TABLE `foo` ( `id` int(11) NOT NULL AUTO_INCREMENT, `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `name` varchar(30) DEFAULT NULL, `email` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`,`date_added`) ) ENGINE=InnoDB PARTITION BY RANGE (UNIX_TIMESTAMP(date_added)) (PARTITION p2011 VALUES LESS THAN (1325361600) ENGINE = InnoDB, PARTITION p2012 VALUES LESS THAN (1356984000) ENGINE = InnoDB, PARTITION p2013 VALUES LESS THAN (1388520000) ENGINE = InnoDB, PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ENGINE = InnoDB );

いいね！

このタイプの「スライス」は、「年ごと」または月ごとにアーカイブデータをファイルに分解する必要がある場合に適しています。

しかし、たとえば、月の日までにログでテーブルを広げる必要がある場合つまり、何かが1〜2か月保存されたテーブルに書き込まれ、その後、こすられます。

つまり、次のようにカットする場合はどうすればよいでしょうか。

  PARTITION BY RANGE (MONTH(date))

または：

  PARTITION BY RANGE (DAY(date_add))

タイプtimestampのフィールドは適切ではありません。

グーグルは、日時とポイントを使用する必要があると言います。

OK、テーブルを作成します。

 CREATE TABLE `foo` ( `id` int(11) NOT NULL AUTO_INCREMENT, `date_added` datetime DEFAULT NULL, `name` varchar(30) DEFAULT NULL, `email` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`,`date_added`) ) ENGINE=InnoDB;

注意してください：

  `date_added` datetime DEFAULT NULL

実際には、CURRENT_TIMESTAMPはdatetime型のフィールドのデフォルト値としてロールされないため、NOW（）はデフォルト値として指定できません。機能。

ただし、date_addedを自動的に設定する必要があります。

2つの出力があります。

1.または、INSERTのすべてのクエリで、NOW（）を追加します。

2.挿入ごとにdate_added = NOW（）になるトリガーをハングアップします。

INSERTコードが多くの場所で行われ、どこでもINSERT ... NOW（）に修正されると、トリガーを使用することができなくなります。

次のようなもの：

 DELIMITER $$ USE `test_db`$$ CREATE TRIGGER `foo_add` BEFORE INSERT ON `foo` FOR EACH ROW BEGIN SET NEW.date_added = IFNULL(NEW.date_added, NOW()); END; $$

これで、必要なタイプ、キー、トリガーを含むテーブルが作成されました。

また、月ごとにテーブルを簡単にカットできます。

  ALTER TABLE foo PARTITION BY RANGE (MONTH(date_added)) ( PARTITION p01 VALUES LESS THAN (02) , PARTITION p02 VALUES LESS THAN (03) , PARTITION p03 VALUES LESS THAN (04) , PARTITION p04 VALUES LESS THAN (05) , PARTITION p05 VALUES LESS THAN (06) , PARTITION p06 VALUES LESS THAN (07) , PARTITION p07 VALUES LESS THAN (08) , PARTITION p08 VALUES LESS THAN (09) , PARTITION p09 VALUES LESS THAN (10) , PARTITION p10 VALUES LESS THAN (11) , PARTITION p11 VALUES LESS THAN (12) , PARTITION p12 VALUES LESS THAN (13) , PARTITION pmaxval VALUES LESS THAN MAXVALUE );

または、曜日でも：

  ALTER TABLE foo PARTITION BY RANGE (DAYOFWEEK(date_added)) ( PARTITION p01 VALUES LESS THAN (2) , PARTITION p02 VALUES LESS THAN (3) , PARTITION p03 VALUES LESS THAN (4) , PARTITION p04 VALUES LESS THAN (5) , PARTITION p05 VALUES LESS THAN (6) , PARTITION p06 VALUES LESS THAN (7) , PARTITION p07 VALUES LESS THAN (8) , PARTITION pmaxval VALUES LESS THAN MAXVALUE );

またはパーティションで2日間：

 ALTER TABLE foo PARTITION BY LIST (DAY(date_added)) ( PARTITION p00 VALUES IN (0,1) , PARTITION p02 VALUES IN (2,3) , PARTITION p04 VALUES IN (4,5) , PARTITION p06 VALUES IN (6,7) , PARTITION p08 VALUES IN (8,9) , PARTITION p10 VALUES IN (10,11), PARTITION p12 VALUES IN (12,13), PARTITION p14 VALUES IN (14,15), PARTITION p16 VALUES IN (16,17), PARTITION p18 VALUES IN (18,19), PARTITION p20 VALUES IN (20,21), PARTITION p22 VALUES IN (22,23), PARTITION p24 VALUES IN (24,25), PARTITION p26 VALUES IN (26,27), PARTITION p28 VALUES IN (28,29), PARTITION p30 VALUES IN (30,31) );

一般的に、今ではすべてがあなたの手にあります。

Mysql PARTITION BY YEAR（日付）/ MONTH（日付）/ DAYOFWEEK（日付）

More articles: