MySQL で大量のデータ (数千万) を素早く削除するためのいくつかの実用的なソリューションの詳細な説明

著者は最近、仕事でパフォーマンスのボトルネックの問題に遭遇しました。MySQL テーブルには毎日約 776 万件の新しいレコードが追加され、保存期間は 7 日間です。7 日以上経過したデータは、新しいレコードを追加する前に古くなる必要があります。 9日間連続で実行した後、1日分のデータを削除するのに約3.5時間かかります（環境：128G、32コア、4Tハードドライブ）。これは許容できません。もちろん、テーブル全体を削除したい場合は、

TRUNCATE TABLE を実行するだけです。

最初の解決策は（削除がそれほど遅くなることは予想されていなかったため）次のとおりです（最も単純で最も素朴な方法）。

cnt_date <= target_date の table_name から削除します

さらに研究を重ねた結果、ついに 770 万件以上のデータ レコードを超高速 (約 1 秒) で削除するという目標を達成しました。1 つのテーブルに含まれるデータの総量は約 4,600 万件でした。最適化プロセスはレイヤーごとに実行され、詳細は次のように記録されています。

• バッチ削除（毎回一定数を制限）し、すべてのデータが削除されるまでループで削除します。同時に、key_buffer_size がデフォルトの 8M から 512M に増加します。

運用効果：削除時間が約3.5時間から3時間に増加

（１）limit（具体的なサイズは適宜設定）を使って一度に削除するデータの量を制限し、データが削除されたかどうかを判定します。ソースコードは以下のとおりです（Python実装）。

def delete_expired_data(mysqlconn, 日):
 mysqlcur = mysqlconn.cursor()
 delete_sql = "table_name から DELETE を実行、cnt_date<='%s' の制限は 50000" % 日
 query_sql = "table_name から srcip を選択 where cnt_date <= '%s' limit 1" % 日
 試す： 
  df = pd.read_sql(query_sql、mysqlconn)
  真の場合:
   df が None または df.empty の場合:
    壊す
   mysqlcur.execute(delete_sql)
   mysqlconn.コミット()

   df = pd.read_sql(query_sql、mysqlconn)
 を除外する：
  mysqlconn.ロールバック()

（２）key_buffer_sizeを増やす

mysqlcur.execute("グローバル key_buffer_size を 536870912 に設定")

key_buffer_size はグローバル変数です。詳細については、MySQL の公式ドキュメントを参照してください: https://dev.mysql.com/doc/refman/5.7/en/server-configuration.html

• クイック削除 + 最適化可能

適用可能なシナリオ: MyISAM テーブル

理由: MyISAM は削除されたデータをリンク リストに保持し、スペースと行の位置は後でデータを挿入するときに再利用されます。 直接削除後、MySQL はインデックス ブロックをマージします。これには大量のメモリのコピーと移動が含まれますが、OPTIMIZE TABLE はインデックスを直接再構築します。つまり、データ ブロックの新しいコピーを直接作成します (JVM ガベージ コレクション アルゴリズムを考えてみてください)。

運用効果：削除時間が3.5時間から1時間40分に増加

具体的なコードは次のとおりです。

def delete_expired_data(mysqlconn, 日):
 mysqlcur = mysqlconn.cursor()
 delete_sql = "table_name から DELETE QUICK を実行、cnt_date<='%s' で制限 50000" % 日
 query_sql = "table_name から srcip を選択 where cnt_date <= '%s' limit 1" % 日
 optimize_sql = "テーブル g_visit_relation_asset を最適化"
 試す： 
  df = pd.read_sql(query_sql、mysqlconn)
  真の場合:
   df が None または df.empty の場合:
    壊す
   mysqlcur.execute(delete_sql)
   mysqlconn.コミット()

   df = pd.read_sql(query_sql、mysqlconn)
  mysqlcur.execute(optimize_sql)
  mysqlconn.コミット()
 を除外する：
  mysqlconn.ロールバック()

• テーブルパーティション、有効期限のあるパーティションを直接削除する（最終ソリューション - フラッシュセール）

MySQL テーブルをパーティション分割する方法には、RANGE、KEY、LIST、HASH などいくつかあります。詳細については、公式ドキュメントを参照してください。ここでのアプリケーション シナリオの日付は変更されるため、固定パーティション名を設定するために RANGE を使用することは適切ではありません。このシナリオには HASH パーティションの方が適しています。

（１）パーティションテーブルの定義、SQL文は次のようになります。

ALTER TABLE table_name PARTITION BY HASH(TO_DAYS(cnt_date)) PARTITIONS 7;

TO_DAYS は、日付 (日付型である必要があり、そうでない場合はエラーが報告されます: (サブ) パーティション関数内の定数、ランダム、またはタイムゾーンに依存する式は許可されません) を日数 (年、月、日の合計日数) に変換し、HASH して 7 つのパーティションを作成します。実はMOD7日目です。

（２）エージングする日付が格納されているパーティションをクエリします。SQL文は次のようになります。

"explain パーティション select * from g_visit_relation_asset where cnt_date = '%s'" % expired_day

実行結果は次のとおりです (partitions 列はパーティションを示します)。

+----+-------------+------------------+------------+-------+----------------+-------+---------+----------+-----------+------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+------------------+------------+-------+----------------+-------+---------+----------+-----------+------------+
| 1 | SIMPLE | table_name | p1 | ALL | cnt_date_index | NULL | NULL | NULL | 1325238 | 100.00 | where の使用 |
+----+-------------+------------------+------------+-------+----------------+-------+---------+----------+-----------+------------+
セットに 1 行、警告 2 件 (0.00 秒)

（3）パーティションをOPTIMIZEまたはREBUILDする場合、SQL文は次のようになります。

"ALTER TABLE g_visit_relation_asset OPTIMIZE PARTITION '%s'" % パーティション

完全なコードは次のとおりです [Python 実装]。これはループして、指定された日付より小さいデータを削除します。

def clear_partition_data(mysqlconn, 日):
 mysqlcur = mysqlconn.cursor()
 expired_day = 日
 query_partition_sql = "explain パーティション select * from table_name where cnt_date = '%s'" % expired_day
 # パーティションを切り捨てた後に OPTIMIZE または REBUILD を実行する
 試す： 
  真の場合:
   df = pd.read_sql(クエリパーティションsql、mysqlconn)
   df が None または df.empty の場合:
    壊す
   パーティション = df.loc[0, 'パーティション']
   パーティションがNoneでない場合:
    clear_partition_sql = "テーブル table_name を変更してパーティション %s を削除" % パーティション
    mysqlcur.execute(clear_partition_sql)
    mysqlconn.コミット()

    optimize_partition_sql = "ALTER TABLE table_name OPTIMIZE PARTITION %s" % パーティション
    mysqlcur.execute(optimize_partition_sql)
    mysqlconn.コミット()
   
   期限切れ日 = (期限切れ日 - timedelta(日数 = 1)).strftime("%Y-%m-%d")
   df = pd.read_sql(クエリパーティションsql、mysqlconn)
 を除外する：
  mysqlconn.ロールバック()

• 他の

削除されたデータがテーブルデータの 50% を超える場合は、必要なデータを一時テーブルにコピーしてから元のテーブルを削除し、一時テーブルの名前を元のテーブルに変更することをお勧めします。MySQL の場合は次のとおりです。

 新規に挿入
  メインから*を選択
   WHERE ...; -- 保持したい行のみ
 テーブル main の名前を Old に、New の名前を Main に変更します。
 DROP TABLE Old; -- ここでスペースが解放されます

パーティションを削除するには、対応するデータを削除せずに、ALTER TABLE table_name REMOVE PARTITIONINGを実行します。

参照:

1) https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html 具体的なパーティション分割手順

2) http://mysql.rjweb.org/doc.php/deletebig#solutions 大容量データを削除するためのソリューション

この記事の著作権は著者と Blog Garden が共有しています。転載は歓迎しますが、著者の同意なしにこの声明を残し、元のリンクを記事ページの目立つ位置に表示する必要があります。そうでない場合、法的責任を追及する権利を留保します。

************************************************************************

エネルギーは限られており、アイデアは多すぎます。ただ 1 つのことをうまく行うことに集中してください。

私はただのプログラマーです。 5年以内に良いコードを書き、技術ブログのすべての単語を磨き、コピーゼロと独創性にこだわります。ブログの意義は、文章スタイルを磨き、論理と秩序を訓練し、知識の体系的な理解を深めることです。それが他の人に役立つとしたら、それは本当に幸せなことです。

これで、MySQL から大量のデータ (数千万) をすばやく削除するためのいくつかの実用的なソリューションに関するこの記事は終了です。MySQL から大量のデータをすばやく削除する方法の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。