Posted at

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

More than 5 years have passed since last update.

非パーティショニングテーブルを後から、パーティショニングする際にかならずハマるのが”オートインクリメント列を含む”テーブルに関する、パーティショニングの時です

その回避策について、以下にまとめました。ご参考になればと思います。

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


現在のテーブル定義

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
;


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

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
) ;


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

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

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

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


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

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

 ALTER TABLE test_ptable DROP PRIMARY KEY;

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

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


回避策


ユニーク索引を作成する

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

create unique index ix01_test_ptable on test_ptable (id,updated_at) ;


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

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

ALTER TABLE test_ptable DROP PRIMARY KEY;

Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

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


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

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



ALTER TABLE test_ptable ADD PRIMARY KEY (id,updated_at) ;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0


索引の削除

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


  • 現在の状態

 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


  • 索引をDROPします

drop index ix01_test_ptable on test_ptable ;


  • 削除後の結果

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 ;


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

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

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
) ;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0


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

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

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) */

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

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