MySQL千万級データテーブルpartition実戦応用

現在、システムのStatテーブルは1日20 W本のデータ量で増加しており、3ヶ月以上のデータを他の場所にdumpしているにもかかわらず、テーブルには2 KWに近いデータがあり、容量は2 GBに近い.
Statテーブルにインデックスが付けられていて、直接select...where...limitを選択すると、まだ速度は速いですが、group byのページングに触れると、遅くなります.
7日間のグループbyは35~50 s程度が必要と観察された.運営は体験が極めて友好的ではないことを反映している.そこでインターネットでMySQLパーティションスキームを検索します.ネット上の基本的にはpartitionの概念と種類、およびいくつかの実験的性質の効果を系統的に説明しており、実戦に近いわけではないことが分かった.
MySQLマニュアルと独自の模索を参考にして、最終的に現在のシステムでパーティションを実現しました.記録するからです.
パーティションタイプの選択
Statテーブル自体は統計レポートなので、データは日付で保存され、ホットデータは一般的に当日、7日以内に限られています.だから私はRangeタイプを選んでパーティションを作りました.
現在のテーブルのパーティションの作成
既存のテーブルを改造するのでalter方式しか使えません.

ALTER TABLE stat
    PARTITION BY RANGE(TO_DAYS(dt)) (
        PARTITION p0 VALUES LESS THAN(0),
        PARTITION p190214 VALUES LESS THAN(TO_DAYS('2019-02-14')),
        PARTITION pm VALUES LESS THAN(MAXVALUE)
    );

ここで2つ注意しなければならないことがあります.
1つはp 0パーティションです.これはMySQL(私は5.7版)にバグがあるからです.あなたが調べたデータがどのエリアにあるかにかかわらず、最初のエリアをスキャンします.私たちの各エリアのデータは数十万本あります.スキャンすると肉が痛いです.だから、不要なスキャンを避けるために、直接0データパーティションを作ってください.
二つ目はpmパーティションで、これは最大パーティションです.pmが要らない場合は、2019-02-15のデータを保存するとエラーが発生します.だからpmは実際には未来のデータに予約されたパーティションを与えます.
定期的にパーティションを拡張
MySQLのパーティションは自分で動的に拡張できないので、動的にパーティションを追加するコードを書きます.
パーティションを追加するには、REORGANIZEコマンドが必要です.このコマンドは、パーティションを再割り当てする役割を果たします.例えば明日は15日です.15日にもパーティションを追加します.実際にはpmパーティションを2つのパーティションに分割します.

ALTER TABLE stat
    REORGANIZE PARTITION pm INTO (
        PARTITION p190215 VALUES LESS THAN(TO_DAYS('2019-02-15')),
        PARTITION pm VALUES LESS THAN(MAXVALUE)
    );

ここでは、現在のテーブルのすべてのパーティションをどのように取得するかという問題について説明します.ネット上にはいろいろな方法がありますが、show create table statを試してみました.それから正則ですべてのパーティションをマッチングしたほうが便利だと思います.
定期的にパーティションを削除
データベースが大きくなるにつれて、古いデータを消去し、古いパーティションを消去するに違いありません.これも簡単です.

ALTER TABLE stat DROP PARTITION p190214, p190215