MySQL の大きなテーブルで大量のデータを一括削除する方法

質問はhttps://www.zhihu.com/question/440066129/answer/1685329456から参照されています。MySQLでは、テーブルに3億のデータがあり、テーブルに分割されていません。フィールドの1つは企業タイプです。企業タイプは一般企業と自営業者です。自営業者のデータ量は約50％を占めています。条件に従って、自営業者の行はすべて削除されます。どうすればいいですか?答えはオリジナルです

テーブルエンジンはInnodb、MySQL 5.7以降であると仮定します。

レコードを削除するには、まずレコードをロックし、元のデータを破棄し、主に削除マークを追加してレコード ヘッダーを変更します。つまり、元のデータのdeleted_flagが1になり、データが削除されたことを示します。ただし、データはクリアされず、新しい行のデータのサイズがこの行のサイズよりも小さい場合は、この行を占有する可能性があります。これは実際にはストレージの断片化です。

その後、データをクリアするには、関連データのインデックスを更新する必要があります。さらに、対応する binlog および redolog ログが生成されます。
削除するデータが大量のデータである場合は、次のようになります。

• 制限を追加しないと、大量のデータを更新する必要があり、インデックスが無効になり、テーブルをロックするためのフルスキャンが発生します。同時に、大量のインデックスの変更により、大量のログが生成され、更新時間が長くなり、テーブルロック時間が長くなります。この期間中、テーブルはオンラインビジネスを処理できません。
• 大量のバイナリログが生成されると、マスターとスレーブの同期に対する負荷が増大する
• マークの削除により、ストレージの断片化が大量に発生します。 MySQL はページごとにデータをロードするため、これらのストレージフラグメントによりランダム読み取りの数が大幅に増加するだけでなく、ページヒット率が低下し、ページスワップが増加します。
• 生成されたログの量が多いため、このテーブルが占めるスペースが大幅に増加していることがわかります。

解決

削除後に制限を追加することで削除回数を制御できると考えるのは簡単です。この回数はインデックスを通過するため、テーブル全体がロックされることはありません。

しかし、ストレージの断片化、マスターとスレーブの同期、スペースの占有などの問題は解決されていません。削除が完了したら、次のステートメントを使用してテーブルを再構築できます。

テーブルを変更します。engine=InnoDB、ALGORITHM=INPLACE、LOCK=NONE;

この文は実際にテーブルを再構築することに注意してください。テーブルのエンジンはすでに InnoDB ですが、最後に ALGORITHM=INPLACE, LOCK=NONE を追加すると、テーブルをロックせずに再構築できます。

別の解決策としては、同じ構造を持つ新しいテーブルを作成し、元のテーブルにトリガーを追加することです。

各行の元のテーブルに更新後にトリガー person_trigger_update を作成します。 
begin set @x = "トリガーUPDATE";
新しいテーブルに置き換えます。SELECT * from original table where new table.id = original table.id;
終了の場合;
終わり;

これにより、オンライン ビジネスの新しいデータが同期されるようになります。その後、すべてのエンタープライズ タイプのデータを新しいテーブルに挿入します。データがすでに存在する場合、更新同期が行われていれば挿入されません。業務変更により、このテーブルでは各世帯のデータが更新されないため、テーブルロックなしの同期によって大規模テーブルのデータクリーニングが実現されます。

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