パーティショニングとは
パーティショニングとは、1つのテーブルデータを分割して管理する仕組みのことです。MySQLでは、バージョン5.1から標準でパーティショニングの機能が提供されています。
パーティショニングを行うことで巨大なデータを複数に分割でき、分割されたデータをまるごと削除したり、SELECT時に特定のパーティションを参照を指定することで、特定のパーティションのみをSELECTの対象として絞り込むことができます。一般的に、データベースのパーティションには、行をベースに分割する水平パーティションと、カラムを分割する垂直パーティションの2種類ありますが、現在MySQL5.7でサポートしているのは水平パーティションのみとなっています。
今回は、このパーティショニングの機能について紹介していきたいと思います。なお今回の例では、InnoDBストレージエンジンによるパーティションを見ていきます。
パーティショニングの種類
パーティショニングには複数の種類があります。
RANGEパーティショニング
範囲で指定されるパーティショニング方法になります。数値型を用いて、事前に定義した範囲に収まる位置にデータが格納されます。データの格納位置は、VALUES LESS THAN 演算子を用いて評価されます。
LISTパーティショニング
事前に定義したデータのリストの情報をもとにパーティショニングする方法になります。パーティショニングを定義する際に、特定のカラムのデータのリストをセットし、その情報をもとにVALUES IN (データの値) で評価されます。
HASHパーティショニング
HASHパーティショニングは整数型のカラムをもとにHASH値を定め、MySQLが自動でデータを均等にパーティショニングします。
HASH値の計算式は以下の通りです。
KEYパーティショニング
KEYパーティショニングは主キーまたはユニークキーを内部でハッシュ化してパーティショニングします。
パーティションテーブルの操作方法
では実際にパーティショニングされたテーブルを作成、操作していきたいと思います。今回は、RANGE COLUMNSパーティショニングを用いて日付を分割する例を見ていきます。
パーティショニングされたテーブルを定義する場合、以下のようにPARTITION BYを用いてステートメントにパーティショニングする条件と分割するテーブルを記述していきます。
生成されたテーブルを確認してみます。
さらに、上記テーブルにデータを挿入してみましょう。
データを挿入することができました。
これらのデータは、実際は各パーティショニングされたテーブルに保持されています。実際に今これらのデータが各パーティショニングにどれくらい保持されているかは、INFORMATION_SCHEMAのPARTITIONSテーブルを参照することによって確認できます。
上記のようにPARTITION_NAMEにTABLE_ROWSの件数だけデータが挿入されていることが確認できます。
ここで対象のdatadirの対象のdatabaseからファイルを確認してみます。
idbファイルが4つ生成されています。パーティショニングを用いるとデータは各ファイルに格納されていますが、それをまとめて1つのテーブルのように見せかけています。
NULL値やパーティションの条件にそぐわない値をどう扱うのか
パーティショニングのKEYとして、NULL値やパーティションの条件にそぐわない値を扱う場合を考えてみます。下記のようなRANGEパーティショニングに対して、NULL値とパーティショニングの条件にそぐわないid=16を挿入してみます。
上記のようにNULL値を挿入することはできましたが、id=16を挿入することはできませんでした。
MySQLのパーティショニングは、厳密には各パーティショニングの種類によってパーティショニングするKEYのNULL値の扱い方が違います。詳細はMySQLのリファレンスマニュアル「MySQL パーティショニングによる NULL の扱い」をご確認ください。
パーティションの選択について
パーティションを選択してSQLを実行
パーティショニングされたテーブルに対して特定のパーティションのみにSQLを実行する場合は、PARTITIONオプションを追記します。t1テーブルで試してみましょう。
p1のパーティションに入っているデータのみを取得することができました。これは、SELECT * FROM t1 WHERE between days '1900-01-01' and '1949-12-31'
と同じ意味になります。
実際に実行計画をとってみると、パーティション句なしの場合は全パーティションをスキャンしますが、PARTITION句をつけるとp1パーティションのみにアクセスしていることがわかります。
パーティションプルーニング
パーティションプルーニングとは、WHERE句の条件によって不必要なパーティションをスキャンしない機能です。この機能はデータが膨大になった場合などに速度が大幅に向上することがあります。
パーティションプルーニングはWHERE句の条件が下記の条件のとき実行されます。
- パーティションのKEYとなるカラム = (または<、>、<=、>=,<>) 値
- パーティションのKEYとなるカラム in (値1,値2, ...)
先ほど作成したテーブルt1に対して、1930年以前のデータを取得する場合を想定します。1930年以前のデータはp0またはp1にのみ入っていることがパーティションの条件からわかります。なので、オプティマイザはp0、p1のみスキャンすればよいと判断するわけです。
実際に実行計画を取ってみます。
partitionsがp0、p1となっていることが確認できました。
EXCHANGE PARTITIONについて
EXCHANGE PARTITIONは5.6で追加された機能で、パーティション分割されていないテーブルとパーティションテーブルを交換することができます。例として、t1テーブルと同じ構造をもち、パーティショニングされていないテーブルt3を作成します。
t1テーブルのp2パーティションを単独のテーブルとしてt3にコピーします。
EXCHANGE PARTITIONを実行する時は、ALTER TABLEにEXCHANGE PARTITION <パーティション名> WITH TABLE <交換するテーブル名>
を用います。
t1テーブルのp3パーティションに入っているデータがt3テーブルのデータと交換されていることが確認できました。
まとめ
今回はパーティショニングについて簡単に説明していきました。パーティショニングはデータの管理等には便利ですが、実際に使用する際には制約や制限があります。公式ドキュメントを参考に、パーティショニングを用いたデータ管理のしやすいテーブル設計、運用を行ってみてください。