MySQLパーティショニングの設定、追加、削除、再構成

まずこんなテーブルを作るとします。ここに毎月10万件以上のレコードが入ってくる予定です。
1レコードが57byteなので、月に5.7Mbyte、プライマリーキーを入れると60Mbyteくらいが入ってきます。
年間にすると720Mbyteなので、まぁデータ量的には余裕だと思うのですが、
100万レコードを超えるとレスポンスが鈍化するという印象があります。
というわけで、MySQLにあるパーティショニング機能を使い、データを振り分けたいと思います。

【参考】DB設計時のサイズ見積もり | よねのはてな

テーブル作成

注意する点として、パーティショニングのキーにしたいカラムを、プライマリーキーに含める必要があるようです。
なので、オートインクリメントのカラムがあるテーブルだと辛い。構成を考えなおした方がいいかも。

create_table

CREATE TABLE `list_rtx` (
 `member_id` varchar(40) CHARACTER SET sjis COLLATE sjis_bin NOT NULL,
 `platform` varchar(10) NOT NULL,
 `year` smallint(5) unsigned NOT NULL,
 `month` tinyint(2) unsigned NOT NULL,
 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`member_id`,`year`,`month`,`platform`)
) ENGINE=InnoDB DEFAULT CHARSET=utf-8

パーティションの作成

1年毎にパーティションを分けたいと思います。
年を基準に範囲を決めて、振り分けます。
なので、今回はRANGEを使います。

partitioning

ALTER TABLE `list_rtx` PARTITION BY RANGE (YEAR(`year`)) (
PARTITION p2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
PARTITION p2014 VALUES LESS THAN (2014) ENGINE = InnoDB,
PARTITION p2015 VALUES LESS THAN (2015) ENGINE = InnoDB,
PARTITION p2016 VALUES LESS THAN (2016) ENGINE = InnoDB,
PARTITION p2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE
);

パーティションを後から操作するのは、サービスが稼働している場合はメンテナンスを入れなくてはいけなくなリます。
なので、出来るだけ最初からユースケースを具体的に想定して、想定より少し多めのパーティションを作成します。

【参考】今更だけどMySQLのパーティショニング機能を試してみた | (ﾟ∀ﾟ)o彡 sasata299's blog

パーティションの確認

confirm_partition

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME =  'list_rtx';

どのパーティションが使用されているかは以下。EXPLAIN PARTITIONを上記に追加。

confirm_used_partition

EXPLAIN PARTITIONS
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME =  'list_rtx';

パーティションの削除

そのパーティション内のデータも消えます／(^o^)＼

delete_partition

ALTER TABLE list_rtx DROP PARTITION p2015;

パーティションの追加・再構成

パーティションの追加は基本的に今あるパーティションの後ろにしか出来ない様です。
なので、maxvalueを用いて振り分けてイた場合、パーティションを追加出来なくなります。
その為、追加ではなく、パーティション全体を再構成させます。
尚、データは消えません。

例: 2013年より前のデータを保存することになったから、パーティションを追加したい

reorganize_partition

ALTER TABLE mau_list_rtx REORGANIZE PARTITION p2013 INTO (
    PARTITION p2010 VALUES LESS THAN (2010),
    PARTITION p2011 VALUES LESS THAN (2011),
    PARTITION p2012 VALUES LESS THAN (2012),
    PARTITION p2013 VALUES LESS THAN (2013)
);

【参考】第15章 パーティショニング
【参考】MySQLパーティショニングでパフォーマンスアップ！ | QuickKnowLedge

複合パーティショニング

例えば「年月」でパーティションを分けたくて、テーブルには「年」と「月」が別のカラムにある場合。
サブパーティション（複合パーティショニング）を使います。

sub_partitioning

ALTER TABLE `list_rtx`
PARTITION BY RANGE (YEAR(`year`))
SUBPARTITION BY HASH (MONTH(`month`))
SUBPARTITIONS 12 (
    PARTITION p2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
    PARTITION p2014 VALUES LESS THAN (2014) ENGINE = InnoDB,
    PARTITION p2015 VALUES LESS THAN (2015) ENGINE = InnoDB,
    PARTITION p2016 VALUES LESS THAN (2016) ENGINE = InnoDB,
    PARTITION p2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
    PARTITION p2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
    PARTITION p2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
    PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

その他

多分、パーティションを作成するSQLではいちいち、ENGINE = hogehoge, を指定しなくても問題ないんじゃないかな？と思います。

【参考】15.2.1. RANGE パーティショニング | MySQL