日向夏特殊応援部隊

俺様向けメモ

今日の MySQL - Partitioning 編 -

さてと、ありがちな下記のようなテーブルを作ってみます。ちなみに 5.1.45 で試してます。

DELIMITER ;

DROP TABLE IF EXISTS diary;

CREATE TABLE diary (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `subject` varchar(64) NOT NULL,
  `content` text NOT NULL,
  `created_on` datetime NOT NULL,
  `updated_on` datetime NOT NULL,
  PRIMARY KEY (`id`,`updated_on`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE ( to_days(updated_on))
(
  PARTITION p20100810 VALUES LESS THAN (TO_DAYS('2010-08-11 00:00:00')) COMMENT = '2010-08-11 00:00:00' ENGINE = InnoDB
);

ALTER TABLE diary ADD PARTITION ( PARTITION p20100811 VALUES LESS THAN (TO_DAYS('2010-08-12 00:00:00')) COMMENT = '2010-08-12 00:00:00' ENGINE = InnoDB );
ALTER TABLE diary ADD PARTITION ( PARTITION p20100812 VALUES LESS THAN (TO_DAYS('2010-08-13 00:00:00')) COMMENT = '2010-08-13 00:00:00' ENGINE = InnoDB );
ALTER TABLE diary ADD PARTITION ( PARTITION p20100813 VALUES LESS THAN (TO_DAYS('2010-08-14 00:00:00')) COMMENT = '2010-08-14 00:00:00' ENGINE = InnoDB );
ALTER TABLE diary ADD PARTITION ( PARTITION p20100814 VALUES LESS THAN (TO_DAYS('2010-08-15 00:00:00')) COMMENT = '2010-08-15 00:00:00' ENGINE = InnoDB );
ALTER TABLE diary ADD PARTITION ( PARTITION p20100815 VALUES LESS THAN (TO_DAYS('2010-08-16 00:00:00')) COMMENT = '2010-08-16 00:00:00' ENGINE = InnoDB );
ALTER TABLE diary ADD PARTITION ( PARTITION p20100816 VALUES LESS THAN (TO_DAYS('2010-08-17 00:00:00')) COMMENT = '2010-08-17 00:00:00' ENGINE = InnoDB );
ALTER TABLE diary ADD PARTITION ( PARTITION p20100817 VALUES LESS THAN (TO_DAYS('2010-08-18 00:00:00')) COMMENT = '2010-08-18 00:00:00' ENGINE = InnoDB );
ALTER TABLE diary ADD PARTITION ( PARTITION p20100818 VALUES LESS THAN (TO_DAYS('2010-08-19 00:00:00')) COMMENT = '2010-08-19 00:00:00' ENGINE = InnoDB );

DELIMITER //

CREATE TRIGGER diary_on_after_delete AFTER DELETE ON diary
FOR EACH ROW BEGIN
  DELETE LOW_PRIORITY FROM comment WHERE diary_id = OLD.id;
END;
//

DELIMITER ;

DROP TABLE IF EXISTS comment;
CREATE TABLE comment (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `diary_id` int(10) unsigned NOT NULL,
  `content` text NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `comment_on_diary_id` (`diary_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

まずは SHOW CREATE TABLE diary をしてみると、

CREATE TABLE `diary` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `subject` varchar(64) NOT NULL,
  `content` text NOT NULL,
  `created_on` datetime NOT NULL,
  `updated_on` datetime NOT NULL,
  PRIMARY KEY (`id`,`updated_on`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( to_days(updated_on))
(PARTITION p20100810 VALUES LESS THAN (734360) COMMENT = '2010-08-11 00:00:00' ENGINE = InnoDB,
 PARTITION p20100811 VALUES LESS THAN (734361) COMMENT = '2010-08-12 00:00:00' ENGINE = InnoDB,
 PARTITION p20100812 VALUES LESS THAN (734362) COMMENT = '2010-08-13 00:00:00' ENGINE = InnoDB,
 PARTITION p20100813 VALUES LESS THAN (734363) COMMENT = '2010-08-14 00:00:00' ENGINE = InnoDB,
 PARTITION p20100814 VALUES LESS THAN (734364) COMMENT = '2010-08-15 00:00:00' ENGINE = InnoDB,
 PARTITION p20100815 VALUES LESS THAN (734365) COMMENT = '2010-08-16 00:00:00' ENGINE = InnoDB,
 PARTITION p20100816 VALUES LESS THAN (734366) COMMENT = '2010-08-17 00:00:00' ENGINE = InnoDB,
 PARTITION p20100817 VALUES LESS THAN (734367) COMMENT = '2010-08-18 00:00:00' ENGINE = InnoDB,
 PARTITION p20100818 VALUES LESS THAN (734368) COMMENT = '2010-08-19 00:00:00' ENGINE = InnoDB) */

となり、各 partition の条件になっている部分の TO_DAYS は展開されている事が分かります。それとコメント付けておくと分かりやすくて良いですね。

さて、日付で Range Partition してるので DROP PARTITION を想定している訳ですが、試しに trigger を使って関連 comment が消されるかどうか確認してみます。

INSERT INTO diary(subject, content, created_on, updated_on) VALUES('hidek lost glasses', 'but he bought contact lens', '2010-08-10 12:00:00', '2010-08-10 12:00:00');
INSERT INTO diary(subject, content, created_on, updated_on) VALUES('hidek is great guy', 'he always play dj music midnight', '2010-08-11 12:00:00', '2010-08-11 12:00:00');

と言う日記を投稿して、

INSERT INTO comment( diary_id, content ) VALUES( 1, 'be careful!' );
INSERT INTO comment( diary_id, content ) VALUES( 1, 'I feel good' );
INSERT INTO comment( diary_id, content ) VALUES( 2, 'dancing' );
INSERT INTO comment( diary_id, content ) VALUES( 2, 'I am listening his dj play.' );

念のため transaction を使って comment が消されるかどうか試してみます。

root@localhost[mypage]:7> BEGIN;
Query OK, 0 rows affected (0.00 sec)
root@localhost[mypage]:8> SELECT COUNT(*) FROM comment;
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)
root@localhost[mypage]:9> DELETE FROM diary WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
root@localhost[mypage]:10> SELECT COUNT(*) FROM comment;
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)
root@localhost[mypage]:11> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)

で、ALTER TABLE DROP PARTITON で 2010-08-10 分の Patition を消してみます。

root@localhost[mypage]:18> ALTER TABLE diary DROP PARTITION p20100810;
Query OK, 0 rows affected (0.91 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost[mypage]:19> SELECT COUNT(*) FROM comment;
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

ですよねー><

まとめ

  • PARTITON の条件に使った関数は定数化される
  • 各 PARTITION に COMMENT 振ると後で見た時に便利なので振るべし
  • DROP PARTITION では当然 DELETE の TRIGGER は発火しない
    • 恐らく foreign key 使った on delete cascade なんかも同様の挙動をすると思われる。
    • ついでに言うと partition で使う column は unique 制約に含まれてないと駄目と言う制約のせいで foreign key は甚だ使いづらい
  • 親子関係にあるテーブルは同等の条件で partitioning されている方が便利

5.5 だと multi column partition (って呼び方かどうか知らないけど) が使えるはずなんで、もう少し便利になってるのかなーなんて思いつつ 5.5 はまだ使った事がございません!

追記 (2010-08-17T17:33:45Z)

ブクマコメより。id:sh2 さんありがとうございます。

まとめの通りcommentもパーティション化してメンテナンスするのがよさそう。5.5からはDATETIME列で直接パーティション化できます。あと残念ながらパーティション化したテーブルにはそもそも外部キーが付与できません><

だそうで、そもそも外部キーは使えないようです。