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

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 をご愛顧いただきありがとうございます。

以下もご興味があるかもしれません:
  • MySQL の大きなテーブルで大量のデータを一括削除する方法
  • MySQLで大きなテーブルを正常に削除する方法の詳細な説明
  • 大きなテーブルを削除する際の MySQL パフォーマンス問題の解決策
  • MySQLで大きなテーブルをエレガントに削除する方法について簡単に説明します

<<:  Jupyter Notebook で JavaScript を実行する方法

>>:  Dockerを使用してGitlabを素早くデプロイする方法

推薦する

MySQL sql_mode の分析と設定の説明

昨夜、MySQL データベースにデータセットを挿入したときにエラーが発生しました。データベースは容赦...

mysql8.0.18 で winx64 をインストールするための詳細なチュートリアル (画像とテキスト付き)

MySQLデータベースをダウンロードするには、https://dev.mysql.com/down...

MySQL ロック制御同時実行方法

目次序文1. 楽観的ロックバージョンフィールドを追加する2. 悲観的ロック読み取りロック完全なテーブ...

Docker+Jenkinsによる自動デプロイの実現方法

Code Cloud を使用して Git コード ストレージ ウェアハウスを構築するhttps://...

Echarts は 1 つのグラフ内で異なる X 軸を切り替える機能を実装します (サンプル コード)

レンダリング下の画像のような効果を実現したい場合は、読み続けてアニメーション画像に直接進んでください...

MySQLで重複行を削除する方法

SQL文 /* MySQL で重複行を削除するいくつかの方法 ---Chu Minfei ---20...

MySQL 8.0 の降順インデックス

序文インデックスが順序付けられていることは誰もが知っていると思いますが、MySQL の以前のバージョ...

Nginx を使用して https ルートドメイン名への 301 リダイレクトを実装するためのサンプル コード

SEO とセキュリティを考慮して、301 リダイレクトが必要です。以下の一般的な処理には Nginx...

Linux での MySQL 5.6 バイナリのインストール プロセス

1.1 バイナリインストールパッケージをダウンロードするhttps://dev.mysql.com/...

Linux で最も頻繁に使用されるターミナル コマンドのトップ 10 のリストを取得します。

私が最も頻繁に使用するコマンドは次の通りです:選択肢CDギットls ssh須藤数週間前、私はこの R...

JavaScript 戦略パターンを使用してフォームを検証する方法

目次概要戦略パターンを使用しないフォーム検証戦略パターンを使用して最適化する戦略パターンの利点要約す...

Linux での sshd サービスとサービス管理コマンドの詳細な説明

sshd SSH は Secure Shell の略で、アプリケーション層のセキュリティ プロトコル...

Vue命令の動作原理と実装方法

Vue の紹介現在のビッグフロントエンドの時代は、混乱と衝突の時代です。世界は多くの派閥に分かれてお...

WeChatアプレット+mqtt、esp8266温度と湿度の読み取り実装方法

まず、 esp8266 は mqtt を通じてメッセージを公開し、WeChat アプレットは mqt...

Gokudōゲームにおけるフロントエンド知識のまとめ

背景日本語を学び始めた当初は、日本語の50音を覚えるのは簡単ではなく、特にカタカナを覚えるのは困難で...