More than 5 years have passed since last update.

すでにあるMySQLのテーブルにパーティションを追加してみる。2019年夏。とりあえずエラーで困ったところ

Last updated at 2019-08-20Posted at 2019-08-10

概要（と言い訳）

「すでにあるMySQLのテーブルにパーティションを追加」して出たエラーのメモです。
「MySQLにはパーティションというものがあって便利っぽい」ということしか知らず、ほぼ知識もない状態で試して出たエラーのメモです。
（このエラーを受けての対応策は検討中です。）
あくまでエラーメモなので解決方法については記してないのと、あまり知識がないのでほぼ解説なしです、すみません。

現状

すでに運用中のMySQLのテーブルがあります。
1日1回、ユーザの持っている商品情報のスナップショットを保持しておくテーブルです。
Xヶ月以上の前のデータは消していく運用です。

今は

delete from example_table
where batch_date < '201Y-MM-01'

のようにdelete文でお掃除していますが、データ量が増えて時間がかかったりとか、ロックされたりとかいろいろつらくなってきました。

このテーブルにパーティションを追加して drop partition で消せるようにします。

以上の現状に近い環境を作って試していきます。

環境

$ cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=18.04
DISTRIB_CODENAME=bionic
DISTRIB_DESCRIPTION="Ubuntu 18.04.3 LTS"

$ sudo apt-get install mysql-server-5.7 mysql-client-5.7
$ mysqld --version
mysqld  Ver 5.7.27-0ubuntu0.18.04.1 for Linux on x86_64 ((Ubuntu))

Ubuntuにインストールした状態のMySQL5.7 （= パラメータなど調整なし）で試します。

検証で使うテーブル定義

CREATE TABLE `example_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(36) NOT NULL,
  `item_id` int(11) NOT NULL,
  `batch_date` date NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id_item_id_batch_date` (`user_id`,`item_id`,`batch_date`),
  KEY `item_id_batch_date_index` (`item_id`,`batch_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1日1回、ユーザの持っている商品情報のスナップショットを保持しておくテーブルです。

パーティションの追加方針

要望

「batch_dateを見てXヶ月以上前のデータを消す」という運用がある。
消すときは drop partition で消したい。

以上から、batch_dateを見て月単位のパーティションを追加します

パーティションの追加

`to_days()` というのを使うらしい

to_days という経過した日数を返す関数があるのでこれを利用してRANGEパーティションを設定するらしい。
やってみる。

alter table example_table
partition by range (to_days(batch_date))
(
  partition p201907 values less than (to_days('2019-08-01')),
  partition p201908 values less than (to_days('2019-09-01'))
);

ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

batch_date でパーティションしたかったのだけれどもタイムゾーンに依存してるのはダメなんだって、残念。

補足 `to_days()`

mysql> select to_days('0000-01-01');
+-----------------------+
| to_days('0000-01-01') |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select to_days('0000-01-03');
+-----------------------+
| to_days('0000-01-03') |
+-----------------------+
|                     3 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select to_days('2019-01-03');
+-----------------------+
| to_days('2019-01-03') |
+-----------------------+
|                737427 |
+-----------------------+
1 row in set (0.00 sec)

`columns` というのが使えるんだって

上記で to_days() を使ったRANGEパーティションを試しましたが、ちょっと古いやり方みたい。
最近のMySQLでは columns というのが使えるからそれを使うみたい。
だから to_days() でエラーが出たんだなーということで columns を試す。

alter table example_table
partition by range columns(batch_date)
(
  partition p201907 values less than ('2019-08-01'),
  partition p201908 values less than ('2019-09-01')
);

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

スッキリしてわかりやすい！と思ったけど違うエラー。
パーティションにはPRIMARY KEYを使わないといけないっぽい。無念。

まとめ

パーティションを使うにはいろいろ制限があって、既存のテーブルに追加する場合にはちょっと考えないといけない

参考

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up