パーティションテーブルについて

はてなの日記からはてなのブログに移り今まで数本の記事を書いてきましたが、Markdownが使えることに今回初めて気づきました（早く言ってよね）ここからは、Markdownで書きます。

パーティションテーブルはなかなか利用されません

パーティションテーブルはなかなか利用されません。なぜ利用されにくいかというと、技術の蓄積が薄いからでしょう。 RDBMS についてかつてデファクトスタンダードだった Oracle の影響が非常に大きいためです。というのも、以前の Oracle ではパーティションテーブルは Enterprise版にしか搭載されておらず、「ライセンス費が1000万円以上（年間サポート費20％）」と、無料でつかえる OSS などと比べると異常に高かったため、大手企業でも、「情報系（DWH など大量のデータを扱うものでも）では、費用面で使えない」という現象が起きていました。そのためか業界全体として経験値が少なく、利用されにくい傾向にあるようです。しかし、非常に効果が高いので正しく理解して利用しましょう。

※ MySQL では外部キー（Foreign key）を付けたテーブルをパーティション化できないという制約があります。

パーティションテーブルとは

大きいテーブルを内部的に、小さいテーブルに分けることです。同じテーブル名でアクセスしても、WHERE句などによって抽出条件がある場合、必要な内部の小さなテーブル（パーティションテーブル）しかアクセスしません。

f:id:Sikushima:20190424103617p:plain

f:id:Sikushima:20190424103653p:plain

パーティショニングの種類

RDBMS によって違いますが、主に3つの種類があります。 RDBMS によって違いますが、サブパティーションまで作れるものがほとんどです。

※ 今回、キーパーティショニングは、ハッシュパーティショニングの一種としました。

リストパーティショニング

個別の値によって分割する方式です。例えば、支店ごとの集計が多い、都道府県ごとの集計が多い。などのときに利用します。

レンジパーティショニング

レンジ（Range）つまり、範囲によって分割する方式です。例えば、月別の集計が多いというようなときに1ヵ月分ずつに分割して利用します。

ハッシュパーティショニング

カラム、または、カラムを含む式と、何個に分割するかを指定することによって、それぞれの RDBMS が持つハッシュ関数である程度均等に分割する方式です。ユーザIDのように、単純な連番のデータに適応されることが多いです。

利用の例

インデックスの代わりに

例えば、多言語対応するサイトで、すべてのテーブルに Languege というカラムがあったとすると、すべてテーブルを Languegeカラムのリストパーティショニングで分割します。

こういうシステムの場合、Languegeカラムにインデックス（複合キーを含む）を付けたり、最悪なものとしてはテーブル名に xxxxxx_jp、xxxxxx_en、などという対応をしていることが結構あります。言語の種類は数が少ない（カーディナリティが低い）ため、インデックスはあまり効きません（利用されないかも）。テーブル名を変えると、文字列連結をして SQL文を構築する必要が出るため好ましくありません。

しかし、リストパーティションにしておけば WHERE句に Languege = 'jp' などの絞り込みがあるだけで必要最小限のデータアクセスにできます。

※ Languege カラムに（複合を含む）インデックスを付けている場合は不要ですので削除しましょう。

デイリーで溜まるデータを日付で

レンジパーティショニングで分割しましょう。もちろん、日、週、月、など、システムの要件に合わせた単位で分割し利用します。保持期限を過ぎたデータを削除するときも、DELETE ではなく、DROPで処理できますから、インデックスの更新もないため一瞬で終わります。（もちろん、ロールバックはできませんが）

ユーザが数十万以上いる場合

ユーザが数十万人以上いる場合、ユーザIDが保存されているすべてのテーブルを、すべて同じ個数に分割するハッシュパーティショニングを実施します。

例えば、ゲームのシステムでユーザIDごとにハッシュパティーションで分割するとします。 ※ MySQL 5.6.7 以降であれば、8192個、それより前のバージョンでは1024個までに分割できました。

f:id:Sikushima:20190424113315p:plain

上図の通り、同じ個数でハッシュパーティショニングしていれば、ハッシュ値は必ず同じになりますから、8192個に分割すれば、ほぼ8192分の1のサイズのテーブルにアクセスするコストで処理できます。 違う個数でハッシュパーティショニングすると、同じハッシュ値にならないため逆にコストが掛かってしまうため、注意が必要です。

このように分割することで、ゲーム会社のように数百万のユーザを抱え激しいアクセスをしていても、ユーザ数が数百人だったころのコストで処理できるわけです。（8192個はやりすぎと思いますが）

巨大なバッチをループして

私は、バッチ処理は基本的に SQL の一括処理で作るのですが、巨大なテーブルに対する一括処理ではワークメモリが足りなくなります。そのためかレコード単位で処理しているプロジェクトは非常に多いです。しかし、レコード単位になると極端に時間が掛かりますから、高速化するには、以下のように、基本的に一括処理だけれど、パーティションだけを切り替える処理にするべきです。

DROP PROCEDURE IF EXISTS sp_test;

DELIMITER //
CREATE PROCEDURE sp_test()
BEGIN

    -- ハンドラで利用する変数 v_done を宣言
    DECLARE v_done INT DEFAULT 0;

    -- フェッチした値を格納する変数
    DECLARE v_name VARCHAR(255);
    -- カーソル宣言
    SELECT PARTITION_NAME 
    FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE PARTITION_NAME IS NOT NULL
    -- WHERE 必要なパーティションの絞り込み
    ;

    -- SQLステートが02000の場合にv_doneを1にするハンドラを宣言する
    DECLARE continue handler FOR sqlstate '02000' SET v_done = 1;
    -- エラー処理に関するハンドラを宣言する
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- エラーログを出力するなどのエラー処理
        ROLLBACK;
    END;

    -- カーソルを開く
    OPEN v_cur;
    FETCH v_cur INTO v_name;

    WHILE v_done != 1 DO

        -- トランザクションの開始
        START TRANSACTION;

        -- 文字列連結してパーティションテーブルを切り替えながら処理する
        SET @stm = CONCAT('
        INSERT INTO 処理結果テーブル(カラム)
        SELECT カラム
        FROM テーブル名 PARTITION(', v_name, ')');
        
        -- SQLを準備して
        PREPARE pst FROM @stm;
        -- 実行する
        EXECUTE pst;
        -- ログの出力処理
        COMMIT;
        
        -- 準備したSQLを開放
        DEALLOCATE PREPARE pst;
        -- 次のレコードに
        FETCH v_cur INTO v_name;

    END WHILE;

    CLOSE v_cur;

END;
//

DELIMITER //

CALL sp_test();

まとめ

テーブルのパーティショニングの機能は、現在では Oracleなどの商用DB の Express版でも利用できますし、MySQL のような OSSデータベースでも利用できます。非常に効果が高いので、正しく理解して利用するようにしましょう。

SQLer 生島勘富のブログ

RDB・SQLの話題を中心に情報発信をしています。

目次