Posted inMySQL

数百万行のMySQLテーブルを安全にクリーンアップする方法：DELETE文によるシステム停止を防ぐ

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

DELETE FROM table_name という名の悪夢

2018年、私は一見簡単そうに見えるタスクで「クビになりかけた」ことがあります。それは、ストレージ容量を確保するために古い注文データを削除するというものでした。当時、ordersテーブルは4,000万行に達していました。私は自信満々に、たった一行のコマンドを実行しました。

DELETE FROM orders WHERE created_at < '2022-01-01';

わずか10秒後、惨劇が起こりました。システムのレイテンシは50msから10sへと急上昇。サーバーのCPU使用率は100%に達し、コネクション待機列は延々と伸び続け、上司からは「なぜウェブサイトにアクセスできないんだ」と電話がかかってきました。これは、MySQLで大規模データを扱う際の痛恨の教訓となりました。

問題は動作メカニズムにありました。大規模なDELETEを実行すると、MySQLはデータの行をロックし、ロールバックに備えてUndoログに記録します。同時に、関連するすべてのインデックスも更新します。数百万行の場合、この組み合わせがI/Oを使い果たし、瞬時にシステムのボトルネックを引き起こします。

クリーンアップ戦略：ただ消すのではなく、退路を考える

本番環境では、古いデータでも後で照合やレポート作成に必要になることがあります。完全に抹消するのではなく、私は通常、以下の安全な3ステップの手順を採用しています。

  1. アーカイブ (Archive): 古いデータを中間テーブルまたはバックアップ用データベースにコピーする。
  2. 検証 (Verify): 両方のテーブルのレコード数が一致していることを確認する。
  3. 削除 (Purge): メインテーブルから少量のバッチ（Batching）に分けて削除を実行する。

テクニック1：バッチ処理（Batching） — 分割して統治せよ

これが最もシンプルな解決策です。100万行を一度に消すのではなく、1回につき約5,000行ずつに分割して処理します。各バッチの間に、スクリプトを1〜2秒休止（sleep）させます。これにより、サーバーがユーザーからの実際のリクエストを処理する「呼吸の間」を確保できます。

以下は、私が自動クリーンアップによく使用するプロシージャです。

DELIMITER //

CREATE PROCEDURE PurgeOldOrders()
BEGIN
    DECLARE rows_affected INT DEFAULT 1;
    
    WHILE rows_affected > 0 DO
        -- テーブルのロック時間を短くするため、少量のバッチで削除する
        DELETE FROM orders 
        WHERE created_at < '2022-01-01' 
        LIMIT 5000;
        
        SET rows_affected = ROW_COUNT();
        
        -- I/Oとレプリケーションの負荷を軽減するために1秒待機する
        SELECT SLEEP(1);
    END WHILE;
END //

DELIMITER ;

注意： MySQLレプリケーションを使用している場合、バッチ処理は必須です。マスター側で一度に大量の削除を行うと、スレーブ側でその巨大なコマンドを再実行するのに時間がかかり、何時間もの遅延（Replication Lag）が発生してしまいます。

テクニック2：パーティショニング — 一瞬でデータを削除する

レコード数が1億件を超えるようなテーブルでは、テーブルパーティショニング（Table Partitioning）を優先的に使用します。テーブルを一つの巨大な塊として扱うのではなく、MySQLは時間軸などのカラムに基づいて物理的な小さなファイルに分割します。

例えば、logsテーブルを年ごとに分割する場合：

ALTER TABLE logs PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024)
);

2022年のデータを削除したい場合、DELETE文を使う必要はありません。たった一つの DROPコマンドで済みます。

ALTER TABLE logs DROP PARTITION p2022;

この操作はほぼ瞬時に完了します。MySQLはディスク上の対応する.ibdファイルを削除するだけで済み、CPUリソースを消費したり行ロックを発生させたりすることもありません。

テクニック3：強力なツール pt-archiver

スクリプトを自作したくない場合は、Percona Toolkitに含まれるpt-archiverを使いましょう。これはプロのDBAにとっての標準ツールです。

これは、データをアーカイブテーブルにコピーし、元のテーブルから「少しずつかじる（nibbling）」ように削除するプロセスを自動化してくれます。私がよく使う実際のコマンドは以下の通りです。

pt-archiver --source h=localhost,D=my_db,t=orders \
            --dest h=archive_server,D=archive_db,t=orders_history \
            --where "created_at < '2022-01-01'" \
            --limit 1000 --commit-each --sleep 1

pt-archiverの賢い点は、スレーブの遅延やサーバーの過負荷を検知すると自動的に停止する機能があることです。

サーバー停止を防ぐための3つの重要な注意点

1. インデックスを常に優先する

created_atカラムで削除を実行する前に、そのカラムにインデックスが貼られていることを必ず確認してください。インデックスがない場合、MySQLはフルテーブルスキャン（Full Table Scan）を行います。その場合、削除が完了する前にサーバーが確実にダウンします。

2. ディスク容量のパラドックスを解決する

MySQL (InnoDB) では、DELETEコマンドを実行しても.ibdファイルがすぐに小さくなるわけではありません。空き領域（断片化されたスペース）が発生するだけです。容量を回収するにはOPTIMIZE TABLEを実行する必要があります。ただし、このコマンドはテーブルを完全にロックするため、アクセスが最も少ない時間帯にのみ実行してください。

3. 外部キー制約を確認する

ON DELETE CASCADEには注意が必要です。親テーブルの1行を削除することで、子テーブルの数千行が連鎖的に削除される可能性があります。このドミノ倒しのような効果は、開発者が理由もわからぬままI/Oが100%に跳ね上がる主な原因となります。

まとめ

大規模データの処理に必要なのは速さではなく、安定性です。一度にすべてを消し去ろうとするのではなく、最も安全なアプローチを選んでください。常にデータを分割し、モニタリングチャートを注意深く監視し、クリーンアップ操作を行う前には必ずバックアップを取るようにしましょう。

この共有が、巨大なMySQLテーブルを扱う際の自信に繋がれば幸いです。スムーズなシステム最適化を！

Share:
Tags: