いきさつ

レポート用として1時間毎にデータを作成しているのですが、レコード数が増え続けた結果、ストレージが予想以上に減ってました。
しかも、このままのペースで減ると、3ヶ月後には残ストレージがゼロに。。。

現状の確認

MySQLのバージョンは5.6.34です。
RDSのt2.smallで起動しています。

まずは下記SQLでサイズの大きいテーブルを確認

SELECT
    table_name,
    table_rows,
    ROUND((data_length + index_length) / 1024 / 1024) AS total_size_MB, -- 合計サイズ
    ROUND(data_length / 1024 / 1024) AS data_size_MB, -- データサイズ
    ROUND(index_length / 1024 / 1024) AS index_size_MB -- インデックスサイズ
FROM
    INFORMATION_SCHEMA.TABLES
ORDER BY
    total_size_MB DESC
;

一番大きなテーブルで約5200万レコードありました。
このテーブルからデータを一部残して削除したいと思います。

削除その１　単純にDELETE（結果：失敗）

LIMITで件数指定しながら削除しました。
が、時間がかかり過ぎます。

10万件の削除で10分以上かかり、そんなこと何度もやってられませんね。。。

削除その２　既存テーブルにパーティション追加（結果：未実施）

年月単位のパーティションを作成し、古いものから消していこう！
と思ったのですが、パーティション作成中は書き込みロックがかかりますね。
試しに試験系の1600万レコードでパーティション作成してみると、完了まで約40分。
本番の5200万レコードだと単純計算でも約3倍。つまり120分以上。

冒頭でお話しした1時間毎のINSERT処理に影響が出るので、別の方法を考えることに。

削除その３　一時テーブル（結果：OK！）

残すレコードを一時テーブルに保存。
その後、テーブル名の変更で対応することができました。

なお、数ヶ月後に同じ状況になることが予想されるので、削除しやすいようパーティションも作成しておきます。

一時テーブルを作成

PKやINDEXの定義ごとコピーした、空のテーブルを作成します。

CREATE TABLE
    tbl_name_tmp  -- 一時テーブル
    LIKE tbl_name  -- 元のテーブル
;

パーティション作成

パーティションを作っておけば、次からの削除が楽になります。

ALTER TABLE												
    tbl_name_tmp
PARTITION BY RANGE COLUMNS(date_column) (
    PARTITION p201701 VALUES LESS THAN ('2017-02-01 00:00:00'),
    PARTITION p201702 VALUES LESS THAN ('2017-03-01 00:00:00'),
    PARTITION p201703 VALUES LESS THAN ('2017-04-01 00:00:00'),
    （省略）
    PARTITION p202110 VALUES LESS THAN ('2021-11-01 00:00:00'),
    PARTITION p202111 VALUES LESS THAN ('2021-12-01 00:00:00'),
    PARTITION p202112 VALUES LESS THAN ('2022-01-01 00:00:00'),
    PARTITION p999912 VALUES LESS THAN (MAXVALUE)
);

（参考）パーティション指定の削除
パーティション指定の削除は一瞬で終わります。

ALTER TABLE
    tbl_name_tmp
DROP PARTITION
    p201701  -- 削除するパーティション名
;

データのコピー

件数が多い場合は条件を指定し、何回かに分けたほうが早いかもしれません。

INSERT INTO
    tbl_name_tmp  -- 一時テーブル
SELECT
    *
FROM
    tbl_name  -- 元のテーブル
WHERE
    date_column >= '2017-02-01 00:00:00'  -- 残すデータの抽出条件
    AND date_column <= '2017-03-01 00:00:00'
;

テーブル名を変更

ALTER TABLE tbl_name RENAME TO tbl_name_old;  -- 元のテーブルに「_old」をつける
ALTER TABLE tbl_name_tmp RENAME TO tbl_name;  -- 一時テーブルの「_tmp」を消す

これで次回からはパーティション単位で削除できるはず。
つまり、作業時間は数分で終わるはず。

できればパーティションのDROPを定期実行するのが良いのですが、そこは後日調べます。(^ ^;)