More than 5 years have passed since last update.

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

Last updated at2013-12-20Posted at 2013-12-18

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

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

テーブル作成

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

create_table

CREATETABLE`list_rtx`(`member_id`varchar(40)CHARACTERSETsjisCOLLATEsjis_binNOTNULL,`platform`varchar(10)NOTNULL,`year`smallint(5)unsignedNOTNULL,`month`tinyint(2)unsignedNOTNULL,`updated_at`timestampNOTNULLDEFAULTCURRENT_TIMESTAMP,PRIMARYKEY(`member_id`,`year`,`month`,`platform`))ENGINE=InnoDBDEFAULTCHARSET=utf-8

パーティションの作成

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

partitioning

ALTERTABLE`list_rtx`PARTITIONBYRANGE(YEAR(`year`))(PARTITIONp2013VALUESLESSTHAN(2013)ENGINE=InnoDB,PARTITIONp2014VALUESLESSTHAN(2014)ENGINE=InnoDB,PARTITIONp2015VALUESLESSTHAN(2015)ENGINE=InnoDB,PARTITIONp2016VALUESLESSTHAN(2016)ENGINE=InnoDB,PARTITIONp2017VALUESLESSTHAN(2017)ENGINE=InnoDB,PARTITIONp2018VALUESLESSTHAN(2018)ENGINE=InnoDB,PARTITIONp2019VALUESLESSTHAN(2019)ENGINE=InnoDB,PARTITIONp2020VALUESLESSTHAN(2020)ENGINE=InnoDB,PARTITIONpmaxVALUESLESSTHANMAXVALUE);

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

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

パーティションの確認

confirm_partition

SELECTTABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,TABLE_ROWSFROMINFORMATION_SCHEMA.PARTITIONSWHERETABLE_NAME='list_rtx';

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

confirm_used_partition

EXPLAINPARTITIONSSELECTTABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,TABLE_ROWSFROMINFORMATION_SCHEMA.PARTITIONSWHERETABLE_NAME='list_rtx';

パーティションの削除

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

delete_partition

ALTERTABLElist_rtxDROPPARTITIONp2015;

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

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

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

reorganize_partition

ALTERTABLEmau_list_rtxREORGANIZEPARTITIONp2013INTO(PARTITIONp2010VALUESLESSTHAN(2010),PARTITIONp2011VALUESLESSTHAN(2011),PARTITIONp2012VALUESLESSTHAN(2012),PARTITIONp2013VALUESLESSTHAN(2013));

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

複合パーティショニング

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

sub_partitioning

ALTERTABLE`list_rtx`PARTITIONBYRANGE(YEAR(`year`))SUBPARTITIONBYHASH(MONTH(`month`))SUBPARTITIONS12(PARTITIONp2013VALUESLESSTHAN(2013)ENGINE=InnoDB,PARTITIONp2014VALUESLESSTHAN(2014)ENGINE=InnoDB,PARTITIONp2015VALUESLESSTHAN(2015)ENGINE=InnoDB,PARTITIONp2016VALUESLESSTHAN(2016)ENGINE=InnoDB,PARTITIONp2017VALUESLESSTHAN(2017)ENGINE=InnoDB,PARTITIONp2018VALUESLESSTHAN(2018)ENGINE=InnoDB,PARTITIONp2019VALUESLESSTHAN(2019)ENGINE=InnoDB,PARTITIONp2020VALUESLESSTHAN(2020)ENGINE=InnoDB,PARTITIONpmaxVALUESLESSTHANMAXVALUE);

その他

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

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

293

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

Movatterモバイル変換