MySQLで大きなテーブルをエレガントに削除する方法について簡単に説明します

時間が経過したり、業務量が増えたりすると、データベースのスペース使用率は着実に上昇し続けます。データベースのスペースがボトルネックになりそうになると、データベースに 1 つまたは 2 つの非常に大きなテーブルがあることに気付くことがあります。事業開始から現在までのあらゆるデータが蓄積されているが、その90%はビジネス価値のないデータだ。このような膨大なテーブルを、いったいどう扱えばいいのだろうか。

価値のないデータなので、通常は直接削除するか、アーカイブしてから削除するかを選択します。データ削除の操作方法は、次の 2 つのカテゴリに分けられます。

• 切り捨てによってテーブル内のすべてのデータを直接削除する
• 削除によってテーブル内の条件を満たすレコードを削除します

1. 切り捨て操作

論理的に言えば、切り捨て操作はテーブル内のすべての行を削除しますが、delete from table_name where 1=1 操作とは異なります。テーブルデータ全体を削除するパフォーマンスを向上させるために、MySQL の切り捨て操作では、実際には最初にテーブルを削除してからテーブルを再作成します。このため、切り捨て操作は非ロールバック DDL 操作になります。

1.1 MySQL truncate はどのような操作を実行しますか?

• 切り捨て操作は実際には削除と再作成の2つのステップに分かれています。
• ドロップ操作の最初の段階では、バッファ プール ページをクリアし、フラッシュ操作を必要とせずにフラッシュ チェーンからテーブル関連のデータ ページを削除します。このステップのボトルネックは、フラッシュ キューの削除操作が、対応するバッファー プール インスタンスのロックを保持し、トラバーサル検索を実行する必要があることです。バッファー プール インスタンスが大きく、フラッシュ チェーン内に削除するデータ ページが多数ある場合、この操作により、バッファー プール インスタンスのロックを取得するときに他のトランザクションがブロックされ、データベースのパフォーマンスに影響を及ぼします。
• ドロップ操作の 2 番目の段階は、ibd ディスク ファイルを削除するプロセスです。データベースの物理ファイルが大きいほど、I/O リソースの消費量が増え、削除操作にかかる時間が長くなります。
• 再作成操作フェーズでは、削除されたテーブルの .frm ファイルがそのままであれば、ドロップ テーブルを削除した後、元のテーブル構造情報に従ってテーブルを再構築できます。再構築されたテーブルの auto_increment 値はリセットされます。

1.2 切り捨て操作によって発生するリソース消費を最適化するにはどうすればよいでしょうか?

• 切り捨て操作のテーブル削除の最初の段階で、MySQL インスタンスに割り当てられた innodb_buffer_pool_size が 1 GB を超える場合、innodb_buffer_pool_instances パラメータを適切に設定して同時実行性を向上させるとともに、バッファ プール インスタンスをスキャンするときにリソースのロックに費やされる時間を短縮します。
• 切り捨て操作におけるテーブル削除の第 2 段階では、対応するテーブルを削除する前に、変更されたテーブルの .ibd ファイルへのハード リンクが作成され、MySQL レベルでの削除操作の実行効率が向上し、データベース レベルでのパフォーマンス損失が軽減されます。次に、オペレーティングシステムレベルで作成したハードリンクを手動でクリーンアップします。

2. 削除操作

2.1 MySQL の delete はどのような操作を実行しますか?

• where 条件に基づいて削除テーブルに対してインデックス/フル テーブル スキャンを実行し、where 条件が満たされているかどうかを確認します。この段階では、スキャン内のすべての行がロックされます。この段階は、リソース消費の最大の隠れた危険です。テーブルに大量のデータがあり、削除操作でインデックスを効果的に使用してスキャンされたデータの量を減らすことができない場合、このステップにより、データベースのロック競合と CPU/IO リソース消費が大量に発生します。
• where 条件に一致しない行に適用されたロックは、条件がチェックされた後に解除され、InnoDB は削除する必要がある行のみをロックします。これにより、ロックの競合を効果的に減らすことができますが、一度に大量のデータを削除すると、巨大な binlog トランザクション ログが生成され、マスター スレーブ アーキテクチャ内の MySQL 自体とスレーブ データベースにとって好ましくなく、レプリケーションの遅延が長くなる可能性があることに注意する必要があります。

2.2 削除操作を最適化するにはどうすればよいでしょうか?

• すべてのテーブルを削除する操作は慎重に行う必要があるため、切り捨て操作の使用を検討することができます。
• delete ... where ... では、where フィルター条件によって、インデックスを効果的に使用してデータ スキャンの量を減らし、テーブル全体のスキャンを回避できるようにする必要があります。
• 大規模なデータ削除の場合や条件にインデックスがない場合、削除操作では、自動増分主キーまたはインデックス付き時間フィールドを追加してバッチ削除操作を実行し、毎回少量のデータを削除して複数のバッチで実行することができます。
• 最近のデータは保持し、履歴データは削除するという典型的なシナリオでは、同じ構造の xxx_tmp テーブルを作成し、 insert xxx_tmp select ... 操作を使用して、必要なデータを tmp テーブルに保持します。次に、名前変更操作を使用して、現在のビジネス テーブル xxx を xxx_bak テーブルに置き換え、xxx_tmp テーブルを現在のビジネス テーブル名 xxx に置き換えます。次に、役に立たない大きなテーブル xxx_bak を手動で削除します。

2.3 削除の一般的な2つのシナリオ

2.3.1 条件に有効なインデックスフィルタリングがない場合は削除する

よくあるシナリオは、ビジネスで t1 condition1=xxx の値を削除する必要があることです。条件フィールドはインデックスを効果的に使用できません。この場合、通常は次の操作を行います。

• 現在のテーブル構造で効果的に使用できるインデックスを確認し、テーブルの自己増分主キーまたは時間インデックスフィールドを使用するようにします。
• 自己増分主キーインデックスまたは時間インデックスを効果的に活用し、インデックスフィールドの範囲フィルタリングを削除操作に追加し、毎回少量のデータを削除し、複数のバッチで実行します。一度に大量のデータを削除しないようにするには、実際のビジネス状況に基づいて具体的なバッチ処理を評価する必要があります。

-- 自己増分主キー インデックスを使用して、condition1=xxx かつ id >=1 かつ id < 50000 の t1 から削除します。
condition1=xxx かつ id >=50000 かつ id < 100000 の場合、t1 から削除します。

-- 時間インデックスを使用して、condition1=xxx かつ create_time >= '2021-01-01 00:00:00' かつ create_time < '2021-02-01 00:00:00' の場合、t1 から削除します。
condition1=xxx かつ create_time >= '2021-02-01 00:00:00' かつ create_time < '2021-03-01 00:00:00' の場合、t1 から削除します。

2.3.2 最近のデータを保存し、過去のデータを削除する

よくあるシナリオとしては、過去 3 か月間の t1 テーブルのデータのみを保持し、残りの履歴データを削除する必要がある場合があります。通常のアプローチは次のとおりです。

保持する必要があるデータを一時的に保存するためのt1_tmpテーブルを作成します。

t1 のようなテーブル t1_tmp を作成します。

インデックスされた時間フィールドに従って、保持するデータをバッチで t1_tmp テーブルに書き込みます。最後のバッチの時間の操作は一時的に無視されることに注意してください。

-- インスタンスビジネスの数に応じてデータをバッチに分割し、各バッチで処理するデータが多すぎないようにします。 insert into t1_tmp select * from t1 where create_time >= '2021-01-01 00:00:00' and create_time < '2021-02-01 00:00:00';
t1_tmp に挿入し、create_time >= '2021-02-01 00:00:00' かつ create_time < '2021-03-01 00:00:00' となる t1 から * を選択します。

-- 最後のデータ バッチはまだ操作されません -- insert into t1_tmp select * from t1 where create_time >= '2021-03-01 00:00:00' and create_time < '2021-04-01 00:00:00';

名前変更操作を使用して、現在のビジネス テーブル t1 を t1_bak テーブルに置き換え、t1_tmp テーブルを現在のビジネス テーブル名 t1 に置き換えます。削除されたテーブルに対して DML 操作が頻繁に実行される場合、この手順により、短期間のビジネス アクセス障害が発生します。

テーブル t1 の名前を t1_bak に変更します。
テーブル t1_tmp を t1 に名前変更します。

最後のデータ バッチを現在のビジネス テーブルに書き込みます。この手順の目的は、変更操作プロセス中のデータ損失を減らすことです。

t1 に挿入し、create_time >= '2021-03-01 00:00:00' かつ create_time < '2021-04-01 00:00:00' となる t1_bak から * を選択します。

名前変更操作ステップでは、変更テーブルの主キーが自己増分であるか、ビジネス固有の UUID であるかにも注意する必要があります。自己増分主キーの場合は、t1_tmp テーブルの自己増分値を変更して、最終設定値に変更期間中に書き込まれたデータが含まれるように注意する必要があります。

alter table t1_tmp auto_increment={t1テーブルの現在の自動値}+{変更期間中の推定増加値}

III. 切り捨て/削除の長所と短所の比較

これで、MySQL で大きなテーブルをエレガントに削除する方法に関するこの記事は終了です。MySQL で大きなテーブルを削除する方法の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。