MySQLのパーティショニングの詰まったところ

MySQLのパーティショニングの詰まったところです。
同じエラーになった方は参考になるかもしれません。

一覧

• A UNIQUE INDEX must include all columns in the table's partitioning function
• Foreign keys are not yet supported in conjunction with partitioning
• A PRIMARY KEY must include all columns in the table's partitioning function

サンプルテーブル作成

テーブル作成

SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE categories (
  id INT UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  name VARCHAR(255) NOT NULL UNIQUE
) character SET utf8mb4 collate utf8mb4_bin;

CREATE TABLE posts (
  id INT UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  category_id INT UNSIGNED NOT NULL,
  content text,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT posts_ibfk_1 FOREIGN KEY (category_id) REFERENCES categories(id)
) character SET utf8mb4 collate utf8mb4_bin;

SET FOREIGN_KEY_CHECKS=1;

パーテーション作成

categoriesテーブルのパーテーション作成

/* (A) */
ALTER TABLE categories
PARTITION BY RANGE COLUMNS(id) (
    PARTITION p10 VALUES LESS THAN (10),
    PARTITION p20 VALUES LESS THAN (20),
    PARTITION p30 VALUES LESS THAN (30)
);

postsテーブルのパーテーション作成

/* (B) */
ALTER TABLE posts
PARTITION BY RANGE COLUMNS(created_at) (
    PARTITION p202202 VALUES LESS THAN ('2022-03-01 00:00:00'),
    PARTITION p202203 VALUES LESS THAN ('2022-04-01 00:00:00'),
    PARTITION p202204 VALUES LESS THAN ('2022-05-01 00:00:00')
);

A UNIQUE INDEX must include all columns in the table's partitioning function

パーティショニングするテーブルに一意キー（unique key）のあるカラムがある場合は、そのカラムをパーティショニングに使うカラムに含めないといけないらしい。
なるほど、データ更新するときにユニークか判断するため、折角パーティションで分けたテーブルを全て見に行かないと行けないし、当たり前か。
サンプルでは(A)のnameカラムに一意キーを設定しているため、このエラーが発生する。

一意キーを解除

DROP INDEX name ON categories;

Foreign keys are not yet supported in conjunction with partitioning

外部キー（foregin key）制約がついているとパーティショニングは使えないらしい。
これは親テーブルでも、子テーブルでも適用される。
サンプルでは(A)でも(B)でもこのエラーが発生する。

外部キー制約を解除

ALTER TABLE posts DROP FOREIGN KEY posts_ibfk_1;

A PRIMARY KEY must include all columns in the table's partitioning function

パーティショニングに使うカラムは、主キー（primary key）に含まれていないといけないらしい。
サンプルでは(B)の主キーはidのみだけど、パーティショニングにcreated_atを利用しているため、このエラーが発生する。

主キーにcreated_atを追加

ALTER TABLE posts DROP PRIMARY KEY, ADD PRIMARY KEY(id, created_at);

公式サイト