MySQL-パーティショニングのメモ

事前準備

分割に使用するカラムはPRIMARYかUNIQUEでないとダメ

メリット

• 区切られた期間内のデータ取得が高速化する
• データ削除出来るので容量の肥大化を防げる

デメリット

• 区切られた期間がまたがる場合負荷が高くなる

パーティショニング確認

SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='XXXXX';

既存のテーブルをパーティショニング (1年分)

ALTER TABLE `XXXXX` PARTITION BY RANGE COLUMNS(created_at) (
    PARTITION p201412 VALUES LESS THAN ('2015-01-01') ENGINE=InnoDB,
    PARTITION p201501 VALUES LESS THAN ('2015-02-01') ENGINE=InnoDB,
    PARTITION p201502 VALUES LESS THAN ('2015-03-01') ENGINE=InnoDB,
    PARTITION p201503 VALUES LESS THAN ('2015-04-01') ENGINE=InnoDB,
    PARTITION p201504 VALUES LESS THAN ('2015-05-01') ENGINE=InnoDB,
    PARTITION p201505 VALUES LESS THAN ('2015-06-01') ENGINE=InnoDB,
    PARTITION p201506 VALUES LESS THAN ('2015-07-01') ENGINE=InnoDB,
    PARTITION p201507 VALUES LESS THAN ('2015-08-01') ENGINE=InnoDB,
    PARTITION p201508 VALUES LESS THAN ('2015-09-01') ENGINE=InnoDB,
    PARTITION p201509 VALUES LESS THAN ('2015-10-01') ENGINE=InnoDB,
    PARTITION p201510 VALUES LESS THAN ('2015-11-01') ENGINE=InnoDB,
    PARTITION p201511 VALUES LESS THAN ('2015-12-01') ENGINE=InnoDB,
    PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE=InnoDB
);

パーティショニングされたテーブルに追加 (1年分)

ALTER TABLE `XXXXX` ADD PARTITION (
    PARTITION p201512 VALUES LESS THAN ('2016-01-01') ENGINE=InnoDB,
    PARTITION p201601 VALUES LESS THAN ('2016-02-01') ENGINE=InnoDB,
    PARTITION p201602 VALUES LESS THAN ('2016-03-01') ENGINE=InnoDB,
    PARTITION p201603 VALUES LESS THAN ('2016-04-01') ENGINE=InnoDB,
    PARTITION p201604 VALUES LESS THAN ('2016-05-01') ENGINE=InnoDB,
    PARTITION p201605 VALUES LESS THAN ('2016-06-01') ENGINE=InnoDB,
    PARTITION p201606 VALUES LESS THAN ('2016-07-01') ENGINE=InnoDB,
    PARTITION p201607 VALUES LESS THAN ('2016-08-01') ENGINE=InnoDB,
    PARTITION p201608 VALUES LESS THAN ('2016-09-01') ENGINE=InnoDB,
    PARTITION p201609 VALUES LESS THAN ('2016-10-01') ENGINE=InnoDB,
    PARTITION p201610 VALUES LESS THAN ('2016-11-01') ENGINE=InnoDB,
    PARTITION p201611 VALUES LESS THAN ('2016-12-01') ENGINE=InnoDB,
    PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE=InnoDB
);

パーティショニング削除

ALTER TABLE `XXXXX` DROP PARTITION p201601;

パーティショニングをやめたい

ALTER TABLE `XXXXX` REMOVE PARTITIONING;

プログラムやバッチ等で追加する場合はMAXVALUEを分割する。

ALTER TABLE `XXXXX` REORGANIZE PARTITION pmax INTO(
    PARTITION p201612 VALUES LESS THAN ('2017-01-01') ENGINE=InnoDB,
    PARTITION p201701 VALUES LESS THAN ('2017-02-01') ENGINE=InnoDB,
    PARTITION p201702 VALUES LESS THAN ('2017-03-01') ENGINE=InnoDB,
    PARTITION p201703 VALUES LESS THAN ('2017-04-01') ENGINE=InnoDB,
    PARTITION p201704 VALUES LESS THAN ('2017-05-01') ENGINE=InnoDB,
    PARTITION p201705 VALUES LESS THAN ('2017-06-01') ENGINE=InnoDB,
    PARTITION p201706 VALUES LESS THAN ('2017-07-01') ENGINE=InnoDB,
    PARTITION p201707 VALUES LESS THAN ('2017-08-01') ENGINE=InnoDB,
    PARTITION p201708 VALUES LESS THAN ('2017-09-01') ENGINE=InnoDB,
    PARTITION p201709 VALUES LESS THAN ('2017-10-01') ENGINE=InnoDB,
    PARTITION p201710 VALUES LESS THAN ('2017-11-01') ENGINE=InnoDB,
    PARTITION p201711 VALUES LESS THAN ('2017-12-01') ENGINE=InnoDB,
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

新規でテーブル作成時にパーティショニング

CREATE TABLE XXXXX (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `member_id` int(10) unsigned NOT NULL COMMENT 'メンバーID',
  `level` int(10) unsigned NOT NULL COMMENT 'レベル',
  `exp` int(10) unsigned NOT NULL COMMENT '経験値',
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`,`created_at`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY RANGE COLUMNS(created_at) (
    PARTITION p201712 VALUES LESS THAN ('2018-01-01') ENGINE=InnoDB,
    PARTITION p201801 VALUES LESS THAN ('2018-02-01') ENGINE=InnoDB,
    PARTITION p201802 VALUES LESS THAN ('2018-03-01') ENGINE=InnoDB,
    PARTITION p201803 VALUES LESS THAN ('2018-04-01') ENGINE=InnoDB,
    PARTITION p201804 VALUES LESS THAN ('2018-05-01') ENGINE=InnoDB,
    PARTITION p201805 VALUES LESS THAN ('2018-06-01') ENGINE=InnoDB,
    PARTITION p201806 VALUES LESS THAN ('2018-07-01') ENGINE=InnoDB,
    PARTITION p201807 VALUES LESS THAN ('2018-08-01') ENGINE=InnoDB,
    PARTITION p201808 VALUES LESS THAN ('2018-09-01') ENGINE=InnoDB,
    PARTITION p201809 VALUES LESS THAN ('2018-10-01') ENGINE=InnoDB,
    PARTITION p201810 VALUES LESS THAN ('2018-11-01') ENGINE=InnoDB,
    PARTITION p201811 VALUES LESS THAN ('2018-12-01') ENGINE=InnoDB,
    PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE=InnoDB
);