オートナンバー型のカラムを含むテーブルをパーティショニング化する方法

非パーティショニングテーブルを後から、パーティショニングする際にかならずハマるのが”オートインクリメント列を含む”テーブルに関する、パーティショニングの時ですその回避策について、以下にまとめました。ご参考になればと思います。

例えば以下のようなテーブルの、updated_at をレンジパーティションキーとして、変更したいなんていう場合に

現在のテーブル定義

[code lang=sql] CREATE TABLE test_ptable ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, updated_at datetime DEFAULT NULL COMMENT 'UTC-DATE', domain varchar(128) DEFAULT NULL COMMENT 'ex: www.yourdomain.com', PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=8177588 DEFAULT CHARSET=utf8 ; [/code]

こんな変更を加えると・・・・

[code lang=sql] alter table test_ptable PARTITION BY RANGE COLUMNS(updated_at) ( PARTITION p20140901 VALUES LESS THAN ('2014-09-01') ENGINE = InnoDB, PARTITION p20141001 VALUES LESS THAN ('2014-10-01') ENGINE = InnoDB, PARTITION p20141101 VALUES LESS THAN ('2014-11-01') ENGINE = InnoDB ) ; [/code]

こんな感じで怒られて作ることができません

[code lang=text] ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function [/code]

つまり、パーティショニングには、プライマリキーが含まれていないといけないってことなのですが、例えば、そのプライマリーキーが、上記の表のようにオートインクリメントだったケースが多いのではないかと思います。

どうすればいいのか？・・

プライマリーキーを一旦DROPしちゃって作りなおそう・・

はい、残念、エラーになります

[code lang=sql] ALTER TABLE test_ptable DROP PRIMARY KEY; [/code]

じゃあ、オートインクリメントは諦めて、全部作りなおそう・・ってすでに稼働中のサービスだった場合なんか、そんなに簡単にできないですよね・・

ってことで。どうやればいいのか？

回避策

ユニーク索引を作成する

ひとまずユニーク索引を作成します。複合ユニーク索引を作成するわけですが、その際に、レンジに含めたいカラムと、プライマリーキーを加えてください

[code lang=text] create unique index ix01_test_ptable on test_ptable (id,updated_at) ; [/code]

プライマリーキーをDROPする

既存のプライマリーキーを先ほどと同様の手順でDROPします

[code lang=text] ALTER TABLE test_ptable DROP PRIMARY KEY; Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0 [/code]

どうでしょうか？こんどは、DROPできたかと思います

再度プライマリーキーを作成する

新規作成し直すプライマリーキーは、updated_at を含めます

[code lang=text] ALTER TABLE test_ptable ADD PRIMARY KEY (id,updated_at) ; Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0 [/code]

索引の削除

別に削除してもしなくてもいいのですが、同様のカラム構成で、プライマリキーと索引があるというのも気持ち悪いので、索引のほうは消してしまいましょう

現在の状態

[code lang=sql] CREATE TABLE test_ptable ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'UTC-DATE', domain varchar(128) DEFAULT NULL COMMENT 'ex: www.yourdomain.com', PRIMARY KEY (id,updated_at), UNIQUE KEY ix01_test_ptable (id,updated_at) ) ENGINE=InnoDB AUTO_INCREMENT=8177588 DEFAULT CHARSET=utf8 [/code]

索引をDROPします

[code lang=sql] drop index ix01_test_ptable on test_ptable ; [/code]

削除後の結果

[code lang=sql] CREATE TABLE test_ptable ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'UTC-DATE', domain varchar(128) DEFAULT NULL COMMENT 'ex: www.yourdomain.com', PRIMARY KEY (id,updated_at) ) ENGINE=InnoDB AUTO_INCREMENT=8177588 DEFAULT CHARSET=utf8 ; [/code]

レンジパーティショニング化を再度チャレンジ

こんどはうまくいきましたね。

レンジパーティショニング化後のテーブル定義

こんな感じに変わったかと思います。

[code lang=text] CREATE TABLE test_ptable ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, updated_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'UTC-DATE', domain varchar(128) DEFAULT NULL COMMENT 'ex: www.yourdomain.com', PRIMARY KEY (id,updated_at) ) ENGINE=InnoDB AUTO_INCREMENT=8177588 DEFAULT CHARSET=utf8 /!50500 PARTITION BY RANGE COLUMNS(updated_at) (PARTITION p20140901 VALUES LESS THAN ('2014-09-01') ENGINE = InnoDB, PARTITION p20141001 VALUES LESS THAN ('2014-10-01') ENGINE = InnoDB, PARTITION p20141101 VALUES LESS THAN ('2014-11-01') ENGINE = InnoDB) / [/code]

如何でしょうか？お試しください。

作業前は予めテーブルはバックアップとっておいてくださいね

Database JUNKY

MySQL,MariaDBを中心としたブログです