LoginSignup
11

More than 5 years have passed since last update.

MySQL大量データの削除

Posted at

いきさつ

レポート用として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万レコードありました。
このテーブルからデータを一部残して削除したいと思います。

削除その1 単純にDELETE(結果:失敗)

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

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

削除その2 既存テーブルにパーティション追加(結果:未実施)

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

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

削除その3 一時テーブル(結果: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を定期実行するのが良いのですが、そこは後日調べます。(^ ^;)

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
11