MySQL(Aurora)でパーティションテーブルを作成する方法

MySQL(Aurora)でパーティションテーブルを作成する方法
DB バージョン
MySQL 5.7.24

Oracleでは古くからパーティショニング機能がありますが、MySQLにもパーティショニング機能というのがあります。

一般的に時系列のトランザクションデータはパーティション(今回はRANGE COLUMNSパーティション)で区切ってパフォーマンスを保ちます。

経験則ですがMySQLはどうも100万件を超えるとレスポンスが著しく劣化するような気がしますので、パーティショニング機能を利用すればパフォーマンスは大幅に改善できます。

personテーブルというサロゲートキーと名前と生年月日のテーブルがあるとします。

上記はパーティションを3つ作成し、2017年12月のデータ、2018年1月のデータ、2018年2月のデータをborned(生年月日)というカラム値によってパーティショニングします。

パーティショニングすることによって、あらかじめデータを絞ることが可能になります。その他にも、パーティショニングされているデータのみを削除する、といった事が可能となるので非常に便利な機能です。

パーティションを条件に入れる

PARTITION句というのがあるので、PARTITION名で区切ることができます。上記例でいうと、p201712、p201801、p201802の3つがパーティション名となります。

こんなSQLが発行できてしまいます。さらに絞りたい場合はPARTITION句のあとにWHERE句を指定することもできます。

このようにパーティションを条件に入れることによってp201712、p201802にあるデータをスキャンしない為パフォーマンスが高速になります。不要なパーティションのスキャンを省くことをパーティションプルーニングと言ったりします。これはPARTION句を使用しなくてもWHERE句だけでもパーティションプルーニングが適用されます。

具体的にどのパーティションが使用されているかを見たいときはSQLの前に「EXPLAIN PARTITIONS」と付けます。

MySQL(Aurora)でパーティションテーブルを作成する方法

未来日のパーティションを作成するには

いずれ未来日になるとパーティションを随時作成していかなくてはいけません。上記テーブルですと2018年3月のデータにはパーティションが用意されていない状態です。

この状態で2018年3月のデータをインサートすると「Table has no partition for value from column_list」が発生します。

MySQLの場合ですと、運用でカバーするというのが一つの手段です。(ALTER文でメンテ時にパーティションを逐一追加していく)

もう一つはMAXVALUEを使用する方法ですが、個人的に面倒だなと思うところが多々あるのであまりお勧めではありませんが、これで2018年3月以降のデータをインサートするとpmiraiというパーティションに未来永劫データが入っていくようになるのでエラーは回避できます。

が、運用期間が長くなるにつれてpmiraiパーティションのデータが多くなることがわかりますので、現実的じゃない機能だなと思います。

これがOracle11gですとインターバルパーティショニングというのがあって、インサートと同時にパーティションが自動作成されるので、RDBはやっぱりOracleが素晴らしいです。

INDEXを張る際のデメリット

何でもかんでもINDEXを張ってしまうと登録更新処理が遅くなってしまうというデメリットがあります。なのでINDEXを張る際は登録更新処理の遅延の影響調査が必要になります。

パーティションごとの件数を調べる

以下のSQLでパーティションに対してデータが何件入っているかを調べることができます。

primary key must include all columns in the table’s partitioning function

RANGE COLUMNSパーティショニングの場合、上記エラーが出るのでPKにidとbornedの二つを含めた複合主キーにする必要があります。

liquibaseでパーティショニングを設定する

liquibaseでパーティショニングを設定する方法は、「Spring Bootでliquibaseを使う方法(MySQL)」に記載しています。

スポンサーリンク
  • このエントリーをはてなブックマークに追加
  • Evernoteに保存Evernoteに保存
スポンサーリンク

コメントをどうぞ

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

CAPTCHA