MySQLで大きなテーブルを正常に削除する方法の詳細な説明

序文

テーブルを削除するには、無意識に思い浮かぶコマンドは、DROP TABLE "テーブル名" を直接使用することかもしれません。これは単純なアプローチです。削除する表現空間が数十 GB、さらには数百 GB のテーブルに達するからです。このようなコマンドが発行されると、MySQL が直接ブロックされる可能性があり、外部的には QPS が急激に低下し、顧客からのリクエストが遅くなるという現象が現れます。

解決

1. オフピーク時に手動で削除する

この場合、DBA はテーブルを削除するために休みなく作業し、夜遅くまで起きていなければならない可能性があります。

2. 最初にデータを消去し、最後に削除する

例えば、データが 1,000 万個ある場合、その都度 20 万個を削除し、しばらくスリープしてから実行を続けるスクリプトを作成します。これにより、ユーザーにとっても気付かれない状態になります。

3. 削除を高速化するためにテーブルファイル（idbファイル）へのハードリンクを作成します。

この方法は、Linux のハードリンクの知識を利用して、すばやく削除を実行します。覚えていない場合は、「Bird Brother の Linux プライベートレシピ」に戻って参照してください。

ln データセンター更新ログ.ibd データセンター更新ログ.ibd.hdlk

[root@mysql01 スポーツセンター]# ll
総使用量 19903792
-rw-r----- 1 mysql mysql 9076 10月17日 13:15 data_center_update_log.frm
-rw-r----- 2 mysql mysql 8447328256 12月23日 11:35 data_center_update_log.ibd
-rw-r----- 2 mysql mysql 8447328256 12月23日 11:35 data_center_update_log.ibd.hdlk

上記のコマンドを実行すると、data_center_update_log.ibd.hdlk ファイルが追加されます。この操作は実際にはディスク領域を占有せず、ディスク上のファイルへの参照を追加するだけです。

これらのファイルのいずれかを削除しても、ディスク上の実際のファイルには影響せず、参照カウントが 1 減少するだけです。参照数が 1 になると、ファイルが削除され、削除するための IO が実行されます。

まさにこの機能を利用することで、大きなファイルを削除するという MySQL の本来の操作が、単純なオペレーティングシステムレベルのファイル削除に変換され、MySQL への影響が軽減されます。

4. MySQLにログインし、テーブル削除操作を実行する

やがて、わずか 1 秒で 200 万個のデータが完了しました。この操作は、ハードリンクを作成した後に実行されました。

mysql> data_center_update_log テーブルを削除します。
クエリは正常、影響を受けた行は 0 行 (1.02 秒)


mysql>終了
さよなら

終了してデータディレクトリを再度確認します。data_center_update_log.ibd.hdlk ハードリンクファイルのみが残っています。[root@mysql01 sports_center]# ll
総使用量 19903792
-rw-r----- 2 mysql mysql 8447328256 12月23日 11:35 data_center_update_log.ibd.hdlk

5. ibd.hdlk ハードリンクファイルを正しく削除するにはどうすればよいですか?

テーブルを削除した後、残っているハードリンクファイルは mysql とは何の関係もありません。ただし、ファイルが大きすぎる場合、rm コマンドで直接削除すると、IO オーバーヘッドが急増し、CPU 負荷が過剰になり、MySQL に影響を及ぼします。
ここで使用する方法は、ループ内でファイルを削除し、ゆっくりとファイルをクリーンアップすることができます。これはスクリプトを使用して実行できます。
Truncate コマンドは、ファイルを指定されたサイズに縮小または拡大するためによく使用されます。ファイルが指定されたサイズより大きい場合、余分なデータは失われます。ファイルが短い場合は拡張され、拡張部分はゼロバイトとして読み取られます。

5.1 切り捨てコマンドをインストールする

[root@mysql01 ~]# 切り詰める
-bash: cruncate: コマンドが見つかりません 通常、オペレーティングシステムはcoreutilsインストールパッケージにあるtruncateコマンドをインストールします。 インストールされていない場合は、次のコマンドを使用してインストールできます [root@mysql01 ~]# yumはtruncateを提供します
coreutils-8.22-24.el7.x86_64 : シェルスクリプトでよく使用される基本的な GNU ツールのセット
出典: ベース
一致するソース:
ファイル名: /usr/bin/truncate

truncate は coreutils インストール パッケージによって提供されることがわかります。coreutils インストール パッケージをインストールしましょう。

[root@mysql01 ~]# yum install -y coreutils

5.2 切り捨ての共通オプション

-c, --no-create --> ファイルを作成しない
-o, --io-blocks --> サイズをバイト数ではなくストレージブロック数として扱います
-r, --reference=RFILE --> 指定されたファイルサイズを参照する
-s, --size=SIZE --> ファイルサイズを指定されたバイト数に設定します

5.3 truncate_bigfile.sh スクリプト

原則: truncate -s オプションを使用してファイルサイズを指定し、スクリプトを通じて各ファイルの縮小サイズを指定し、一定時間スリープすることで、制御可能なファイル削除を実現します。

添付ファイル: truncate_bigfile.sh スクリプト

#!/bin/bash
#

TRUNCATE=/usr/bin/truncate
ファイル=$1

[ x"$1" = x ]の場合;
 echo "ファイル名を入力してください"
 出口1;
それ以外
 SIZE_M=$(du -sm "$1" | awk '{print $1}')

 i が $(seq "${SIZE_M}" -100 0) の場合
 する
 睡眠1
 echo "${TRUNCATE} -s ${i}M ${FILE}"
 ${TRUNCATE} -s "${i}"M "${FILE}"
 終わり
フィ

[ $? -eq 0 ]の場合;
    \rm -f "${ファイル}"
それ以外
    echo "ファイルを確認してください"
フィ

要約する

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

以下もご興味があるかもしれません: