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

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

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

DB	Version
MySQL	5.7.24

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

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

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

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

CREATE TABLE person(
    id INT NOT NULL,
    NAME VARCHAR(30),
    borned DATE)
PARTITION BY RANGE COLUMNS(borned) (
PARTITION p201712 VALUES LESS THAN ('2018-01-01 00:00:00') COMMENT = '2017-12-data',
PARTITION p201801 VALUES LESS THAN ('2018-02-01 00:00:00') COMMENT = '2018-01-data',
PARTITION p201802 VALUES LESS THAN ('2018-03-01 00:00:00') COMMENT = '2018-02-data'
);

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

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

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

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

SELECT *
FROM person
PARTITION (p201801)

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

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

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

EXPLAIN PARTITIONS
SELECT *
FROM person
PARTITION (p201801)

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

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

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

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

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

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

CREATE TABLE person(
    id INT NOT NULL,
    NAME VARCHAR(30),
    borned DATE)
PARTITION BY RANGE COLUMNS(borned) (
PARTITION p201712 VALUES LESS THAN ('2018-01-01 00:00:00') COMMENT = '2017-12-data',
PARTITION p201801 VALUES LESS THAN ('2018-02-01 00:00:00') COMMENT = '2018-01-data',
PARTITION p201802 VALUES LESS THAN ('2018-03-01 00:00:00') COMMENT = '2018-02-data',
PARTITION pmirai VALUES LESS THAN (MAXVALUE) // ココ
);

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

INDEXを張る際のデメリット

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

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

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

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