あけましておめでとうございます
皆様あけましておめでとうございます。本年も宜しくお願い致します。
早いものでDeNA に入社してから二年経ってしまいました。実はプログラマとなってから二年間在籍したのはこの会社が初めてで、まだまだ楽しい出来事が待っていそうだなと思える会社だなと改めて思います。
今年は恐らくスマートフォンにおけるビジネスが本格化する年になるんじゃないかなと思います。そちらの方も少しずつキャッチアップしていけたらなと思っております。
自分も今年、35歳となるので今後の事を見据えて気を引き締めて頑張って行こうと思います。ブログに関してはもう少し頑張って書かないとなーと。
SQL だけで動的に ADD PARTITION する
DEALLOCATE PREPARE し忘れていたので修正しました。id:tokuhirom++
結論から言えば プリペアド ステートメントの為の SQL 構文 を使えば出来る。
CREATE TABLE `timeline` ( `id` int(10) unsigned NOT NULL, `guid` int(10) unsigned NOT NULL, `title` varchar(140) DEFAULT NULL, `created_on` datetime NOT NULL, PRIMARY KEY (`id`,`created_on`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY RANGE (TO_DAYS(created_on)) (PARTITION p20101110 VALUES LESS THAN (734452)) ENGINE = InnoDB; DROP PROCEDURE add_partition; DELIMITER // CREATE PROCEDURE add_partition(IN a_table TEXT, IN a_date DATE) BEGIN SELECT CONCAT( 'ALTER TABLE timeline ADD PARTITION ( PARTITION ', DATE_FORMAT(a_date, 'p%Y%m%d'), ' VALUES LESS THAN(', TO_DAYS(a_date + INTERVAL 1 DAY), ') )') INTO @ddl; PREPARE ddl_stmt FROM @ddl; EXECUTE ddl_stmt; DEALLOCATE PREPARE ddl_stmt; END; // DELIMITER ; CALL add_partition('timeline', '2010-11-11');
みたいな感じ。PREPARE の FROM 句は文字列リテラルかユーザー変数*1しか使えないのでプロシージャのローカル変数*2は使えないので注意。
Time::Piece::MySQL とタイムゾーン (訂正あり)
重要
id:holidays-l さんがこの記事の誤りと、ちゃんとした解説を書いてくれているので、そっちを参照して下さいませ。
以下、そのつもりで読んで下さい。
MySQL から UNIX_TIMESTAMP() と NOW() の値をこんな感じで出します。
[12:13:13 root@bopobo/test :4] SELECT UNIX_TIMESTAMP(), NOW(); +------------------+---------------------+ | UNIX_TIMESTAMP() | NOW() | +------------------+---------------------+ | 1288926767 | 2010-11-05 12:12:47 | +------------------+---------------------+ 1 row in set (0.00 sec)
最近 Time::Piece を使っているので、MySQL の datetime 型を扱う際には Time::Piece::MySQL を使ってこんな感じにします。
$ perl -MTime::Piece -MTime::Piece::MySQL -e 'my $t = Time::Piece->from_mysql_datetime("2010-11-05 12:12:47"); warn $t;'
Fri Nov 5 12:12:47 2010 at -e line 1.これは正しいんですけど、
$ perl -MTime::Piece -MTime::Piece::MySQL -e 'my $t = Time::Piece->from_mysql_datetime("2010-11-05 12:12:47"); warn $t->epoch;'
1288959167 at -e line 1.は元の timestamp と異なる値 (gmtime) になっちゃうと。これは $ENV{TZ} を変更しても全然変わらない。
$ perl -MTime::Piece -MTime::Piece::MySQL -e 'my $t = Time::Piece->from_mysql_datetime("2010-11-05 12:12:47"); warn $t->epoch - 9 * 60 * 60;'
1288926767 at -e line 1.なら正しいけどかっこわるい。
$ perl -MTime::Piece -MTime::Piece::MySQL -e 'my $t = Time::Piece->from_mysql_datetime("2010-11-05 12:12:47"); warn $t->strftime("%s")'
1288926767 at -e line 1.これなら正しいみたい。詳しくは man strftime すれば分かります。
余談ですが DateTime::Format::MySQL だと、
$ perl -MDateTime::Format::MySQL -e 'my $dt = DateTime::Format::MySQL->parse_datetime("2010-11-05 12:12:47"); warn $dt->set_time_zone("Asia/Tokyo")->epoch'
1288926767 at -e line 1.とかで書けます。
YAPC Asia 2010 でのスライド公開と補足、あと感想など
はい、@941 さんを初めとして、@lestrrat さん、JPA の皆様って僕もですけど、それとボランティアスタッフの皆さん、スピーカーの皆さん、ご来場者の皆さん3日間お疲れ様でした。そして、今年も盛り上がりましたね。
さて、後半だいぶ時間が押して早口になってしまいましたが当日のスライドを公開します。
Inside mobage platform
View more presentations from zigorou.
さてと特に後半の Q4M を複数台構成にした際の負荷のばらつきですが、INSERT の方は DNSRR とかでやればいいのですが、queue_wait() して実際に何かタスクを実行する際に、処理時間に偏りがある場合、queue_wait() の sleep が無視出来なくなって片側に接続が偏ってしまう現象が何度か起きました。
これを解決するために、ひとつのQ4Mに接続する worker のプロセス数が常に固定となるように改修しましたってのをアニメーション交えて解説するはずだったのにまったく時間が足りずでしたorz..
TRIGGER + Q4M ってのもちょっとした実験中で、枠組みそのものは当然出来るんですが障害時のリカバリプランとかは要検討だなーとか思ってます。こちらに関しては追々。
MySQL Partitioning に関しては結果的に purge が楽とかそういう理由で使ってるケースがほとんどだなーと。一方で unique 制約が妙なことになるので、わざと unique 性を担保する為だけに別のテーブル作ってそこに暗黙的に trigger で insert することによって unique 性の担保をしてみたりとか割と実験的なこともやってるんですけど、制約必要ならそもそも partitioning するなとかとか。。。
とりあえず、久しぶりに長時間のプレゼンだとか思ってたら完璧に時間配分間違えたのと、もうちょっとスライドが手軽に作れるようにしないとなーとか思ったしだいなのでした。
さて、最後の @miyagawa さんの KeyNote は最高でしたねー。社内でもあの KeyNote でやる気出たとか何人かいました。自分もそのうちの一人です。
改めて思い立ったらコード書くぞーって思った訳なのです。
と、とりとめもないですがまた来年の YAPC Asia も無事開催なるかっ！乞うご期待。
Partitioning と information_schema.PARTITIONS のメモ
例によって information_schema に partitioning されたテーブルの情報がある訳ですが、ちょっとメモ。ちなみに SQL としてはこんな感じです。
SELECT PARTITION_NAME, SUBPARTITION_NAME, PARTITION_ORDINAL_POSITION, SUBPARTITION_ORDINAL_POSITION, PARTITION_METHOD, SUBPARTITION_METHOD, PARTITION_EXPRESSION, SUBPARTITION_EXPRESSION, PARTITION_DESCRIPTION, PARTITION_COMMENT, NODEGROUP, TABLESPACE_NAME FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = 'test_partition' AND TABLE_NAME = 'k1'
で抽出はこんな感じ。
perl -MDBI=neat -e 'my $dbh = DBI->connect("dbi:mysql:dbname=test_partition", "root", ""); my @columns = qw(PARTITION_NAME SUBPARTITION_
NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DE
SCRIPTION PARTITION_COMMENT NODEGROUP TABLESPACE_NAME); my @short_names = qw(name s_name ord_pos s_ord_pos method s_method exp s_exp desc comment nodegroup
tablespace_name); my $rs = $dbh->selectall_arrayref("SELECT " . join(",", @columns) . " FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = ? AND TABLE_
NAME = ?", undef, @ARGV); sub print_row { print "|| " . join(" || ", @_) . " ||\n" } print_row(@short_names); for (@$rs) { print_row(map { defined $_ ? $_ :
neat $_ } @$_) }' test_partition k1
Key
15.2.4. KEY パーティショニング からテーブル定義を拝借。
CREATE TABLE `k1` ( `id` int(11) NOT NULL, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY () PARTITIONS 2 */
| name | s_name | ord_pos | s_ord_pos | method | s_method | exp | s_exp | desc | comment | nodegroup | tablespace_name | ||||||||||||
| p0 | undef | 1 | undef | KEY | undef | undef | undef | default | undef | ||||||||||||||
| p1 | undef | 2 | undef | KEY | undef | undef | undef | default | undef |
HASH
CREATE TABLE `employees` ( `id` int(11) NOT NULL, `fname` varchar(30) DEFAULT NULL, `lname` varchar(30) DEFAULT NULL, `hired` date NOT NULL DEFAULT '1970-01-01', `separated` date NOT NULL DEFAULT '9999-12-31', `job_code` int(11) DEFAULT NULL, `store_id` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH ( YEAR(hired)) PARTITIONS 4 */
| name | s_name | ord_pos | s_ord_pos | method | s_method | exp | s_exp | desc | comment | nodegroup | tablespace_name | ||||||||||||
| p0 | undef | 1 | undef | HASH | undef | YEAR(hired) | undef | undef | default | undef | |||||||||||||
| p1 | undef | 2 | undef | HASH | undef | YEAR(hired) | undef | undef | default | undef | |||||||||||||
| p2 | undef | 3 | undef | HASH | undef | YEAR(hired) | undef | undef | default | undef | |||||||||||||
| p3 | undef | 4 | undef | HASH | undef | YEAR(hired) | undef | undef | default | undef |
LIST
CREATE TABLE `employees2` ( `id` int(11) NOT NULL, `fname` varchar(30) DEFAULT NULL, `lname` varchar(30) DEFAULT NULL, `hired` date NOT NULL DEFAULT '1970-01-01', `separated` date NOT NULL DEFAULT '9999-12-31', `job_code` int(11) DEFAULT NULL, `store_id` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (store_id) (PARTITION pNorth VALUES IN (3,5,6,9,17) ENGINE = MyISAM, PARTITION pEast VALUES IN (1,2,10,11,19,20) ENGINE = MyISAM, PARTITION pWest VALUES IN (4,12,13,14,18) ENGINE = MyISAM, PARTITION pCentral VALUES IN (7,8,15,16) ENGINE = MyISAM) */
| name | s_name | ord_pos | s_ord_pos | method | s_method | exp | s_exp | desc | comment | nodegroup | tablespace_name | ||||||||||||
| pNorth | undef | 1 | undef | LIST | undef | store_id | undef | 3,5,6,9,17 | default | undef | |||||||||||||
| pEast | undef | 2 | undef | LIST | undef | store_id | undef | 1,2,10,11,19,20 | default | undef | |||||||||||||
| pWest | undef | 3 | undef | LIST | undef | store_id | undef | 4,12,13,14,18 | default | undef | |||||||||||||
| pCentral | undef | 4 | undef | LIST | undef | store_id | undef | 7,8,15,16 | default | undef |
RANGE
CREATE TABLE `employees3` ( `id` int(11) NOT NULL, `fname` varchar(30) DEFAULT NULL, `lname` varchar(30) DEFAULT NULL, `hired` date NOT NULL DEFAULT '1970-01-01', `separated` date NOT NULL DEFAULT '9999-12-31', `job_code` int(11) NOT NULL, `store_id` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (store_id) (PARTITION p0 VALUES LESS THAN (6) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (11) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (16) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN (21) ENGINE = MyISAM) */
| name | s_name | ord_pos | s_ord_pos | method | s_method | exp | s_exp | desc | comment | nodegroup | tablespace_name | ||||||||||||
| p0 | undef | 1 | undef | RANGE | undef | store_id | undef | 6 | default | undef | |||||||||||||
| p1 | undef | 2 | undef | RANGE | undef | store_id | undef | 11 | default | undef | |||||||||||||
| p2 | undef | 3 | undef | RANGE | undef | store_id | undef | 16 | default | undef | |||||||||||||
| p3 | undef | 4 | undef | RANGE | undef | store_id | undef | 21 | default | undef |
Sub Partitioning
CREATE TABLE `ts` ( `id` int(11) DEFAULT NULL, `purchased` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=sjis /*!50100 PARTITION BY RANGE ( YEAR(purchased)) SUBPARTITION BY HASH ( TO_DAYS(purchased)) (PARTITION p0 VALUES LESS THAN (1990) (SUBPARTITION s0 ENGINE = MyISAM, SUBPARTITION s1 ENGINE = MyISAM), PARTITION p1 VALUES LESS THAN (2000) (SUBPARTITION s2 ENGINE = MyISAM, SUBPARTITION s3 ENGINE = MyISAM), PARTITION p2 VALUES LESS THAN MAXVALUE (SUBPARTITION s4 ENGINE = MyISAM, SUBPARTITION s5 ENGINE = MyISAM)) */
| name | s_name | ord_pos | s_ord_pos | method | s_method | exp | s_exp | desc | comment | nodegroup | tablespace_name | ||||||||||||
| p0 | s0 | 1 | 1 | RANGE | HASH | YEAR(purchased) | TO_DAYS(purchased) | 1990 | default | undef | |||||||||||||
| p0 | s1 | 1 | 2 | RANGE | HASH | YEAR(purchased) | TO_DAYS(purchased) | 1990 | default | undef | |||||||||||||
| p1 | s2 | 2 | 1 | RANGE | HASH | YEAR(purchased) | TO_DAYS(purchased) | 2000 | default | undef | |||||||||||||
| p1 | s3 | 2 | 2 | RANGE | HASH | YEAR(purchased) | TO_DAYS(purchased) | 2000 | default | undef | |||||||||||||
| p2 | s4 | 3 | 1 | RANGE | HASH | YEAR(purchased) | TO_DAYS(purchased) | MAXVALUE | default | undef | |||||||||||||
| p2 | s5 | 3 | 2 | RANGE | HASH | YEAR(purchased) | TO_DAYS(purchased) | MAXVALUE | default | undef |
Iteration あれこれ
sharding やら partitioning とかし始めると段々とその key が特定のノートやパーティションに収まるように分類した上で処理とかしたくなる訳です。
最近、モバゲーのオープンプラットフォームのプロダクト全てで使っている、DBIx-DBHResolver にちょこちょこ新機能を入れたりしてるんですが、これに resolve_node_keys ってメソッドを最近つけたりしました。
#!/usr/bin/perl use strict; use warnings; use feature qw(say); use Data::Dump qw(dump); use DBIx::DBHResolver; my $resolver = DBIx::DBHResolver->new; $resolver->config( +{ clusters => +{ TIMELINE => +{ nodes => [qw/TIMELINE001 TIMELINE002/], strategy => 'Key' } }, connect_info => +{ TIMELINE001 => +{}, TIMELINE002 => +{}, } } ); my %node_keys = $resolver->resolve_node_keys( TIMELINE => [ 1 .. 20 ] ); while ( my ( $node, $keys ) = each %node_keys ) { say $node; say dump $keys; }
まぁこんな感じで使うんですが、
TIMELINE001 [2, 4, 6, 8, 10, 12, 14, 16, 18, 20] TIMELINE002 [1, 3, 5, 7, 9, 11, 13, 15, 17, 19]
という出力となります。各ノードに分割アルゴリズムに則った形でノート名とキーの配列として返してくれます。余談ですがこの分割アルゴリズムは現在は Key, Range, List に対応してます。
このキーが今度はまた万単位であってそれらのキーを元に bulk insert するとかそんな処理が出てきたりする訳ですが、毎回のごとく
my @all_values = ( 1 .. 100000 ); my @values; while ( ( @values = splice(@all_values, 0, 1000) ) > 0 ) { ### bulk insert by each @values }
みたいな処理を書いて居た訳ですがどうにもスマートじゃないなぁと前から思ってました。
でこんな風にしてみるのはどうかなと思い立ったのがこちら。
#!/usr/bin/perl use strict; use warnings; use feature qw(say); use Array::AsHash; use Data::Dump qw(dump); use Data::Util qw(is_scalar_ref is_number); use Iterator::Simple qw(iterator); use SQL::Abstract; use SQL::Abstract::Plugin::InsertMulti; sub bulk_insert_iterator { my ( $values, $size ) = @_; $size ||= 100; iterator { my @next = splice( @$values, 0, $size ); return unless ( @next > 0 ); \@next; }; } sub as_sql { my ( $stmt, @bind ) = @_; for my $v (@bind) { if ( is_scalar_ref $v ) { $stmt =~ s/\?/%s/; } elsif ( is_number $v ) { $stmt =~ s/\?/%d/; } else { $stmt =~ s/\?/'%s'/; } } sprintf( $stmt, @bind ); } my $sql = SQL::Abstract->new; my ( $stmt, @bind ); my %node_keys = ( PEOPLE001_MASTER => [ map { $_ * 2 } ( 1 .. 25 ) ], PEOPLE002_MASTER => [ map { ( $_ * 2 ) - 1 } ( 1 .. 25 ) ] ); my @cols = qw( id ref_id created_on ); my $ah = Array::AsHash->new( +{ array => [%node_keys] } ); while ( my ( $node, $keys ) = $ah->each ) { say $node; say '-' x 100; my $iter = bulk_insert_iterator( [ map { [ 1, $_, \'NOW()' ] } @$keys ], 10 ); while ( my $values = $iter->next ) { ( $stmt, @bind ) = $sql->insert_multi( 'hidek', \@cols, $values ); say as_sql( $stmt, @bind ); } say '-' x 100; }
で出力はこんな風になります。
PEOPLE001_MASTER ---------------------------------------------------------------------------------------------------- INSERT INTO hidek ( id, ref_id, created_on ) VALUES ( 1, 2, NOW() ), ( 1, 4, NOW() ), ( 1, 6, NOW() ), ( 1, 8, NOW() ), ( 1, 10, NOW() ), ( 1, 12, NOW() ), ( 1, 14, NOW() ), ( 1, 16, NOW() ), ( 1, 18, NOW() ), ( 1, 20, NOW() ) INSERT INTO hidek ( id, ref_id, created_on ) VALUES ( 1, 22, NOW() ), ( 1, 24, NOW() ), ( 1, 26, NOW() ), ( 1, 28, NOW() ), ( 1, 30, NOW() ), ( 1, 32, NOW() ), ( 1, 34, NOW() ), ( 1, 36, NOW() ), ( 1, 38, NOW() ), ( 1, 40, NOW() ) INSERT INTO hidek ( id, ref_id, created_on ) VALUES ( 1, 42, NOW() ), ( 1, 44, NOW() ), ( 1, 46, NOW() ), ( 1, 48, NOW() ), ( 1, 50, NOW() ) ---------------------------------------------------------------------------------------------------- PEOPLE002_MASTER ---------------------------------------------------------------------------------------------------- INSERT INTO hidek ( id, ref_id, created_on ) VALUES ( 1, 1, NOW() ), ( 1, 3, NOW() ), ( 1, 5, NOW() ), ( 1, 7, NOW() ), ( 1, 9, NOW() ), ( 1, 11, NOW() ), ( 1, 13, NOW() ), ( 1, 15, NOW() ), ( 1, 17, NOW() ), ( 1, 19, NOW() ) INSERT INTO hidek ( id, ref_id, created_on ) VALUES ( 1, 21, NOW() ), ( 1, 23, NOW() ), ( 1, 25, NOW() ), ( 1, 27, NOW() ), ( 1, 29, NOW() ), ( 1, 31, NOW() ), ( 1, 33, NOW() ), ( 1, 35, NOW() ), ( 1, 37, NOW() ), ( 1, 39, NOW() ) INSERT INTO hidek ( id, ref_id, created_on ) VALUES ( 1, 41, NOW() ), ( 1, 43, NOW() ), ( 1, 45, NOW() ), ( 1, 47, NOW() ), ( 1, 49, NOW() ) ----------------------------------------------------------------------------------------------------
まぁ最初の Array::AsHash は each でも一向に構わない訳ですが、何となく試しに使ってみました。
汎用的な Iterator を作りたいのであれば、Iterator::Simple が一番軽量で汎用的だなーという印象。
今日の 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 はまだ使った事がございません！
