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

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

例えば以下のようなテーブルの、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) */

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

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