MySQLのテーブルにPartitionを追加/削除/確認する

2013年に作成した個人的なWebサービスがいつの間にか動かくなってる…？と思って調べて見るとPHP経由で動かしているMySQLが以下のようなエラーを吐いていました。

PHP Fatal error:  Uncaught Exception: [_runsql] HY000 1526 Table has no partition for value 20180827

どうやら開発当初に設定したMySQLのpartitionをすべて使い切ってしまったようです。アクセスログ用のテーブルで5年分くらいのpartitionをまとめて切っていたのですが、まさか5年後も動かしているとは夢にも思わずｗ (もしくは将来の自分が何とかするだろうと思っていたらしいｗ)

今回はこいつを動くようにします。

パーティションの確認
パーティションを追加する
パーティションを削除する
未来永劫パーティションをいじりたくない
参考ページ

パーティションの確認

テーブル作成時のSQLがリポジトリに残っていたので確認すると思った通りちょうど今年で切れてますね。

CREATE TABLE IF NOT EXISTS `mi53`.`bucket` (
  `id`      INT          NOT NULL AUTO_INCREMENT,
  `created` INT UNSIGNED NOT NULL,
  `ip`      VARCHAR(15)  NULL,
  `ua`      VARCHAR(255) NULL,
  /* 中略 */
  PRIMARY KEY (`id`, `created`))
ENGINE = InnoDB
PARTITION BY RANGE(`created`)(
      PARTITION p20131102 VALUES LESS THAN (20131103)  /* 1週間置きに切ったらしい */
    , PARTITION p20131109 VALUES LESS THAN (20131110)
    , PARTITION p20131116 VALUES LESS THAN (20131117)
         /* 中略 */
    , PARTITION p20180428 VALUES LESS THAN (20180429)
    , PARTITION p20180505 VALUES LESS THAN (20180506)
);

念の為、上記のCREATE TABLE文の通りに実行されているかINFORMATION_SCHEMAを覗いて確認します。

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='bucket';
+--------------+------------+----------------+----------------------------+------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS |
+--------------+------------+----------------+----------------------------+------------+
| mi53         | bucket     | p20131102      |                          1 |          0 |
| mi53         | bucket     | p20131109      |                          2 |         48 |
| mi53         | bucket     | p20131116      |                          3 |       1188 |
〜中略〜
| mi53         | bucket     | p20180428      |                        235 |        218 |
| mi53         | bucket     | p20180505      |                        236 |         67 |
+--------------+------------+----------------+----------------------------+------------+

パーティションを追加する

普段は自分でもほぼ使わないサービスなんですが、お勉強がてらパーティションを追加してみます。

ALTER TABLEでADD PARTITIONしてやればOK。

ALTER TABLE bucket ADD PARTITION (
    PARTITION p201806 VALUES LESS THAN (20180630),  /* 月次のパーティションに変更(面倒になったので←) */
    PARTITION p201807 VALUES LESS THAN (20180731),
    PARTITION p201808 VALUES LESS THAN (20180831),
    PARTITION p201809 VALUES LESS THAN (20180930),
    PARTITION p201810 VALUES LESS THAN (20181031),
    PARTITION p201811 VALUES LESS THAN (20181131),
    PARTITION p201812 VALUES LESS THAN (20181231)
);

先程のSELECT文を打って意図通り反映されたか確認します。

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='bucket';
+--------------+------------+----------------+----------------------------+------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS |
+--------------+------------+----------------+----------------------------+------------+
| mi53         | bucket     | p20131102      |                          1 |          0 |
| mi53         | bucket     | p20131109      |                          2 |         48 |
| mi53         | bucket     | p20131116      |                          3 |       1188 |
〜中略〜
| mi53         | bucket     | p20180428      |                        235 |        218 |
| mi53         | bucket     | p20180505      |                        236 |         67 |
| mi53         | bucket     | p201806        |                        237 |          0 |
| mi53         | bucket     | p201807        |                        238 |          0 |
| mi53         | bucket     | p201808        |                        239 |          0 |
| mi53         | bucket     | p201809        |                        240 |          0 |
| mi53         | bucket     | p201810        |                        241 |          0 |
| mi53         | bucket     | p201811        |                        242 |          0 |
| mi53         | bucket     | p201812        |                        243 |          0 |
+--------------+------------+----------------+----------------------------+------------+

パーティションを削除する

もし間違えて追加してしまった場合や、意図的にパーティションを削除したい場合は、DROP PARTITIONしてやります。パーティション内のデータも消えます。 実行する際は十分に注意してください。

ALTER TABLE bucket DROP PARTITION p201812;

このパーティションの削除は、パーティションを利用する目的の一つでもありますよね。パーティションを作成した場合、物理的には以下のようにパーティション毎にファイルが作成され、INSERTなどを行うと各ファイルに記録されています。これが非常に重要。

$ pwd
/var/lib/mysql/mi53

$ ls bucket*
bucket.frm              bucket#P#p20150523.ibd  bucket#P#p20161217.ibd  bucket#P#p201903.ibd
bucket#P#p20131102.ibd  bucket#P#p20150530.ibd  bucket#P#p20161224.ibd  bucket#P#p201904.ibd
bucket#P#p20131109.ibd  bucket#P#p20150606.ibd  bucket#P#p20161231.ibd  bucket#P#p201905.ibd
bucket#P#p20131116.ibd  bucket#P#p20150613.ibd  bucket#P#p20170107.ibd  bucket#P#p201906.ibd

SELECT文を実行した場合は、該当するファイルだけを開いて検索を行えば不要なファイルを開く必要がないため非常に高速に動作しますし、DELETE文を使用してレコードを削除する場合はWHERE句の内容を吟味し1レコードずつ比較が走りデータ量によっては非常に時間がかかります。しかしパーティション毎削除する場合は、ファイルを1つ削除すれば完了してしまうというわけです。

未来永劫パーティションをいじりたくない

今回設定した方法だと、一定時間が経過するたびに同じ作業をする必要が出てきますし、今回のようにパーティションが設定されてないキーが出現した場合にはエラーとなってしまいます。これを解消する方法としてMAXVALUEを設定してやれば、最後に設定したレンジ移行のデータはすべてMAXVALUEで指定したパーティションに保存されます。

ALTER TABLE bucket ADD PARTITION (
    PARTITION p201901 VALUES LESS THAN (20190131),
    PARTITION pover   VALUES LESS THAN MAXVALUE
);

一見便利に見えますが、副作用としてALTER TABLEで新規にパーティションを追加してやることができなくなります。以下のように上記のSQL実行後にALTER TABLEでさらにパーティションを追加しようとするとエラーになってしまうのです。ここらへんは一長一短ありますので自身のサービスに合わせて方針を決めるのが良いのではないかと。

mysql> ALTER TABLE bucket ADD PARTITION (
    ->     PARTITION p201902 VALUES LESS THAN (20190228)
    -> );

ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition

もしMAXVALUEのパーティションを削除したい場合、他と同様にDROP PARTITIONで削除してやれば消えてくれます。もちろんデータは消えてしまいますのでくれぐれもご注意を。

ALTER TABLE bucket DROP PARTITION pover;

参考ページ

qiita.com

詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド (NEXT ONE)

posted with amazlet at 18.08.27

奥野幹也
翔泳社
売り上げランキング: 240,539

Amazon.co.jpで詳細を見る