今日の 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 は発火しない
- 親子関係にあるテーブルは同等の条件で partitioning されている方が便利
5.5 だと multi column partition (って呼び方かどうか知らないけど) が使えるはずなんで、もう少し便利になってるのかなーなんて思いつつ 5.5 はまだ使った事がございません!