Innodb で MySQL の 2T テーブルをすばやく削除する方法の例

序文

この記事は主に、MySQL の Innodb で 2T の大きなテーブルをすばやく削除する方法に関する関連コンテンツを紹介します。これは、参考と学習のために共有されています。詳細な紹介を見てみましょう。

さあ、漫画を見て感情を養いましょう。

はい、以上です。 erpというテーブルがあるとします。次のコマンドを直接実行すると

ドロップテーブル ERP

この時点で、ドロップが完了するまですべての mysql 関連プロセスが停止し、その後 mysql は実行を再開します。その理由は、テーブルを削除するときに InnoDB がグローバル ロックを維持し、削除が完了するとロックが解除されるためです。

つまり、日中のトラフィックが非常に多いときに、何もせずに大きなテーブルを削除するコマンドを実行すると、MySQL サーバー全体がハングします。テーブルの削除中は QPS が急激に低下し、その後、プロダクト マネージャーがお茶を飲みにやって来ます。だから漫画にはそういうシーンがあるんです。夜の12時とか静かになったら消してもいいんです。

もちろん、これに同意しない人もいて、「テーブルを削除するストアド プロシージャを作成し、トラフィックがあまり多くない夜間に 1 回実行することができます」と言うかもしれません。
私はショックを受け、考えた後、「皆さん、議論はやめて、業界の常識について私が話すのを聞いてください」としか言えませんでした。

仮説

まず説明させてください。ここには前提があります。MySQL は独立したテーブルスペースを有効にしており、これは MySQL 5.6.7 以降ではデフォルトで有効になっています。

つまり、my.cnfにはこのような設定があります（これらはMySQLの最適化に関する知識であり、後で紹介します）

innodb_file_per_table = 1

次のコマンドでテーブルスペースのステータスを確認します。

mysql> '%per_table' のような変数を表示します。 
+-----------------------+-------+ 
| 変数名 | 値 | 
+-----------------------+-------+ 
| innodb_file_per_table | オフ | 
+-----------------------+-------+

innodb_file_per_table の値が OFF の場合、共有テーブルスペースが使用されていることを意味します。

innodb_file_per_table の値が ON の場合、独立した表領域が使用されることを意味します。

それで、誰もが私に尋ねるでしょう、独立した表領域と共有表領域の違いは何ですか?

共有テーブルスペース: データベースのすべてのテーブル データとインデックス ファイルは 1 つのファイルに配置されます。この共有テーブルスペースのデフォルトのファイル パスは、データ ディレクトリにあります。 デフォルトのファイル名は ibdata1 です (このファイルは複数のファイルに拡張できます)。このように、操作とメンテナンスが非常に不便になることに注意してください。ご存知のとおり、すべてのデータは 1 つのファイルに格納されており、単一のテーブルを維持するのは非常に不便です。さらに、削除操作を実行すると、ファイル内に多くのギャップが残り、ibdata1 ファイルは自動的に縮小されません。つまり、共有テーブルスペースを使用してデータを保存すると、テーブルを削除した後にスペースを解放できないという問題が発生する可能性があります。

独立した表領域: 各テーブルは独立して配置されます。各テーブルには、.frm テーブル記述ファイルと .ibd ファイルがあります。

.frm ファイル: テーブル構造の定義など、各テーブルのメタデータを保存します。このファイルはデータベース エンジンから独立しています。

.ibd ファイル: 各テーブルのデータとインデックスを保存するファイル。

この方法では、各テーブルに独自の独立したテーブル スペースがあるため、操作と保守が容易になり、単一のテーブルを異なるデータベース間で移動できるようになります。さらに、テーブル削除操作を実行すると、テーブルスペースが自動的に再利用されます。削除操作を実行した後、alter table TableName engine=innodb を使用してテーブル領域の一部をデフラグし、再利用することができます。

ps: my.cnfのdatadirはデータ保存ディレクトリを設定するために使用されます

さて、上でたくさん述べましたが、私が言いたいのはただ一つです。

ほとんどの場合、運用と保守では、パフォーマンスの最適化と運用と保守の難しさの観点から、独立したテーブルスペース方式の方がはるかに強力であるため、MySQL に対して独立したテーブルスペース ストレージ方式が確実に選択されます。

したがって、冒頭で述べた前提として、MySQL は独立したテーブルスペースを開く必要があります。この仮定は 90% の確率で真実です。実際に社内の MySQL が共有テーブル スペースを使用している状況に遭遇した場合は、運用および保守担当者と話し合い、共有テーブル スペースが使用される理由を尋ねてください。

正しい姿勢

datadir = /data/mysql/ であり、mytest という名前のデータベースがあると仮定します。データベースmytestにはerpというテーブルがあります。次のコマンドを実行します。

mysql> システム ls -l /data/mysql/mytest/

次のような出力が得られます（少しフィルタリングしました）

-rw-r----- 1 mysql mysql 9023 8 18 05:21 erp.frm
-rw-r----- 1 mysql mysql 2356792000512 8 18 05:21 erp.ibd

上記では、frm と ibd の機能について紹介しました。現在、erp.ibd ファイルが大きすぎるため、削除が停止しています。

この問題を解決するにはどうすればいいでしょうか?

ここでは、Linux のハード リンクに関する知識を使用して、迅速な削除を実行する必要があります。 「Bird Brother's Private Kitchen」のコンテンツの一部をご紹介します。

実はソフトリンクはWindowsのショートカットとして理解していただければ良いので、ここでは詳しく紹介せず、主にハードリンクについて紹介します。

このハードリンクについては、簡単に説明します。ここで多くの言葉を投稿するのは面倒なので、やめておきます。

実際に保存されたファイルについては、

次に、上のノードインデックスを指すファイル名があります

したがって、いわゆるハード リンクとは、ノード インデックスを指すファイル名が複数存在し、ノード インデックスを指すファイル名が複数存在することを意味します。

上記のノードインデックスを指す別のファイル名があると仮定します。

このとき、ファイル名（1）を削除します。Linuxシステムは、ノードインデックスを指すファイル名（2）があることを検出します。したがって、実際にファイルが削除されるのではなく、手順（2）の参照が削除されます。この操作は参照のみを削除するため、非常に高速です。それでこの絵はこうなりました

次に、ファイル名を削除します（2）。Linuxシステムは、ノードインデックスを指す他のファイル名がないことを検出し、実際のストレージファイルを削除します。この操作は実際のファイルを削除するため、比較的遅くなります。

さて、上記の原則を使用します。

まず、lnコマンドを使用してerp.ibdのハードリンクを作成します。

mysql> システム ln /data/mysql/mytest/erp.ibd /data/mysql/mytest/erp.ibd.hdlk

この時点で、ファイルディレクトリは次のようになります

-rw-r----- 1 mysql mysql 9023 8 18 05:21 erp.frm
-rw-r----- 2 mysql mysql 2356792000512 8 18 05:21 erp.ibd
-rw-r----- 2 mysql mysql 2356792000512 8 18 05:21 erp.ibd.hdlk

追加の erp.ibd.hdlk ファイルがあり、erp.ibd と erp.ibd.hdlk の inode は両方とも 2 であることがわかります。

この時点で、テーブル削除操作を実行します。

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

1 秒以内に削除されることがわかります。なぜなら、この時点では、同じ inode を指す 2 つのファイル名 (erp.ibd と erp.ibd.hdlk) が存在するためです。このとき、削除操作を実行すると参照のみが削除されるため、非常に高速です。

そして、今回の削除により、mysql からテーブルが削除されました。ただし、erp.ibd.hdlk ファイルがまだ残っているため、ディスク領域は解放されていません。

erp.ibd.hdlk を正しく削除するにはどうすればいいですか?

経験がない方は必ず回答してrmコマンドで削除してください。ここで注意すべき点は、実稼働環境では、rm コマンドを使用して大きなファイルを直接削除すると、ディスク IO オーバーヘッドが急増し、CPU 負荷が過剰になり、他のプログラムの動作に影響が出るということです。

さて、この時点では、truncate コマンドを使用して削除する必要があります。truncate コマンドは coreutils ツール セットにあります。

詳細については、Baidu を参照してください。rm コマンドと truncate コマンドをテストした人がいて、truncate コマンドはディスク IO と CPU 負荷にほとんど影響を与えないことが分かりました。

削除スクリプトは以下のとおりです

TRUNCATE=/usr/local/bin/truncate
i が `seq 2194 -10 10 ` の場合; 
する 
 睡眠2
 $TRUNCATE -s ${i}G /data/mysql/mytest/erp.ibd.hdlk 
終わり
rm -rf /data/mysql/mytest/erp.ibd.hdlk;

2194G から開始し、10G ずつ減らして 2 秒間停止し、ファイルに 10G だけが残るまで続けます。最後に、rm コマンドを使用して残りを削除します。

その他の状況

これは、データベースが Windows に展開されている場合に何をすべきかを意味します。この質問に対する私の答えは、実のところ十分に専門的ではありません。デビュー以来、本番環境のWindowsにMySQLがインストールされているという状況に遭遇したことは一度もありません。実際にこの問題が発生した場合、Windows には mklink というツールがあります。これは Windows でハード リンク ロックを作成するために使用され、同様の機能を実行できるはずです。

要約する

この記事で解説した内容は、中小企業の研究開発の現場で遭遇する可能性が高い内容です。中小企業にはプロの DBA がいないため、R&D 担当者がすべてを行わなければなりません。皆さんが何か得るものがあったら幸いです。

さて、以上がこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に一定の参考学習価値を持つことを願っています。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM をご愛顧いただきありがとうございます。