【MySQL】パーティショニングをやってみた

とあるポータルサイトにて膨大なデータ数（約1,000万）のテーブルにアクセスする必要性が出てきた。そのままではDBの負荷が上がることが想定されるため、とりあえず負荷を減らすためにもパーティショニングでデータを小分けにして負荷を上げない対策を講じる。その時の備忘録。

パーティショニングを既存テーブルに追加する時の注意点

• 対象となるカラムをprimary keyに含める
• 最初、パーティショニングをするために使用するカラム（日付型）を主キーに含めていなかった。
• それで実行しようとするとエラーが発生。
• そもそもの主キーのカラムがオートインクリメントされるカラムなので、まずはこれを削除して、主キーに追加した。

ここで使ったSQL

sql

ALTER TABLE `テーブル名` DROP COLUMN 'もともと主キーだったカラム';
ALTER TABLE `テーブル名` ADD PRIMARY KEY (`パーティショニングで使用するカラム`);

年月でパーティショニング

• 今回の要件として、日毎の集計データを年月でパーティショニング
• 年月でパーティショニングするためにサブパーティショニング（複合パーティショニング）を使用
• どうやらサブパーティショニングにはRANGEが使えないらしい

sql

ALTER TABLE `テーブル名`
PARTITION BY RANGE (YEAR(`カラム`))
SUBPARTITION BY HASH (MONTH(`カラム`))
SUBPARTITIONS 12 (
    PARTITION p_2012 VALUES LESS THAN (2012),
    PARTITION p_2013 VALUES LESS THAN (2013),
    PARTITION p_2014 VALUES LESS THAN (2014)
);

さぁ、確認

sql

SELECT
TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'テーブル名';