長期間稼働しているデータベースの場合、テーブルがストレージ領域を占有しすぎるという問題がよく発生します。ただし、多くの不要なテーブルを削除した後も、テーブルファイルのサイズは変わりません。この問題を解決するには、InnoDB がテーブル領域を再利用する方法を理解する必要があります。 テーブルの場合、占有されるスペースは主にテーブル構造とテーブル データの 2 つの部分に分かれます。一般的に、テーブル構造の定義はごくわずかなスペースしか占有しません。したがって、スペースの問題は主にテーブル データに関連しています。 MySQL 8.0 より前では、テーブル構造は .frm サフィックスを持つファイルに保存されていました。 8.0 では、システム データ テーブルでテーブル構造を定義できるようになりました。 テーブルデータの保存について テーブル データは、共有テーブルスペースまたは
切り捨てる = 削除する + 作成する データ削除プロセス ただし、 MySQL InnoDB は、データを格納するための構造として B+ ツリー (インデックス構成テーブルとも呼ばれる) を使用し、データはページに格納されることがわかっています。 データを削除する場合、次の 2 つの状況があります。
たとえば、レコード R4 を削除する場合は、次のようにします。 InnoDB は、再利用可能な場所と呼ばれるレコード R4 を直接削除済みとしてマークします。その後、ID が 300 ~ 700 のレコードが挿入されると、その位置は再利用されます。ディスクファイルのサイズは縮小されないことがわかります。 さらに、レコードの再利用は、スコープ条件を満たすデータに限定されます。後で ID 800 のレコードを挿入する場合、R4 の位置は再利用できません。 別の例として、データ ページ全体の内容が削除される場合、ページ A のデータ ページである R3、R4、R5 が削除されるとします。 この時点で、InnoDB はページ A 全体を削除済みとしてマークし、スコープ制限なしにデータ全体を再利用できるようになります。たとえば、ID=50 のコンテンツを挿入する場合は、それを直接再利用できます。 また、隣接する 2 つのデータ ページの使用率が非常に低い場合、2 つのページのデータはいずれかのページにマージされ、もう一方のページは再利用可能としてマークされます。 要約すると、データ行またはデータ ページが削除された場合、再利用のために削除済みとしてマークされるため、ファイル サイズは縮小されません。対応する具体的な操作は、削除コマンドを使用することです。 さらに、レコードを削除する最初のケースでは、R4 を削除して ID=800 を挿入するなど、再利用時の範囲制限により多くのギャップが発生することもわかります。 挿入操作によってギャップも生じる データを挿入する際に、インデックスの昇順でデータを挿入すると、インデックスの構造がコンパクトになります。ただし、ランダムに挿入すると、インデックス データ ページが分割される可能性があります。 たとえば、いっぱいになっているページ A にデータを挿入します。 ページ A がいっぱいなので、ページ B を申請する必要があります。ページ A をページ B に調整するプロセスは、ページ分割とも呼ばれます。 終了後、ページAに隙間ができます。 さらに、更新操作の場合、最初に削除してから挿入するとギャップが発生します。 さらに、追加、削除、変更が大量に行われたテーブルでは、穴があいてしまう可能性があります。穴をなくすと自然な空間が生まれます。 テーブルの再構築を使用する テーブル内のギャップを削除するには、テーブル A と同じ構造のテーブル B を再構築し、主キー ID の昇順でデータをテーブル B に挿入します。 挿入は順次行われるため、当然テーブル B にギャップはなく、データ ページの使用率も高くなります。その後、テーブル B がテーブル A の代わりに使用され、テーブル A のスペースが縮小される効果があるように見えました。 具体的には、次のとおりです。 テーブルAを変更するエンジン=InnoDB バージョン 5.5 以降では、コマンドは上記のプロセスと同様になり、MySQL はデータ交換、テーブル名交換、古いテーブルの削除の操作を自動的に完了します。 しかし、問題があります。DDLではテーブルAを更新できません。この時にテーブルAにデータを書き込むと、データ損失が発生します。 オンライン DDL はバージョン 5.6 以降で導入されました。 オンラインDDL Online DDL はこれに基づいて次の更新を行いました。 テーブルを再構築するプロセスは次のとおりです。
行ログ ファイルが存在するため、再構築中にテーブル A に対して DML 操作を実行できます。 変更ステートメントが実行される前に、最初に MDL 書き込みロックが要求されますが、データのコピー前に MDL 読み取りロックに退化し、DML 操作をサポートすることに注意してください。 MDL が削除されない理由は、他のスレッドが同時にこのテーブルに対して DDL 操作を実行するのを防ぐためです。 大きなテーブルの場合、この操作は大量の IO および CPU リソースを消費するため、オンライン操作を実行する場合は操作時間を制御する必要があります。安全を確保するために、移行には gh-ost を使用することをお勧めします。 オンラインとインプレース まず、インプレースとコピーの違いについて説明します。 オンライン DDL では、テーブル A の再構築されたデータは、InnoDB 内に作成された一時ファイルである tmp_file に配置されます。 DDL 全体は InnoDB 内で実行されます。また、サーバー層では、一時テーブルにデータは移動されません。これは「インプレース」操作であるため、「インプレース」と呼ばれます。 前の共通DDLでは、作成されたテーブルAはサーバーによってtmp_tableに作成されるため、「コピー」と呼ばれます。 対応する文は実際には次のようになります。 -- alter table t engine=InnoDB デフォルトは alter table t engine=innodb,ALGORITHM=inplace; です。 -- プロセスはサーバー コピーです alter table t engine=innodb,ALGORITHM=copy; inplace と online は対応する関係ではないことに注意してください。
拡大する 最適化、分析、およびテーブル変更の違いについて説明します。
テーブルを作り直した後、スペースが狭くなるだけでなく、少し広くなることもあります。これは、再構築されたテーブル自体にギャップがないためです。DDL 期間中に、いくつかの DML 実行によって新しいギャップが発生しました。 InnoDB はテーブル全体を埋めることはなく、各ページの 1/16 を後続の更新用に残します。そのため、最初はコンパクトであっても、再構築後にはギャップが生じます。 要約する これで、delete を使用してデータを削除しても、対応するデータ行は実際には削除されないことがわかりました。InnoDB はそれを再利用可能としてマークするだけなので、テーブル スペースは小さくなりません。 一般的に、再利用されたスペースをマークする方法は 2 つあります。1 つは、データ ページ内の特定の場所のみを削除済みとしてマークする方法ですが、このような場所は特定の範囲内でのみ使用されるため、ギャップが発生します。 もう 1 つは、データ ページ全体を再利用可能としてマークすることです。このようなデータ ページには制限がなく、直接再利用できます。 この問題を解決するには、テーブルを再構築する方法を使用できます。バージョン 5.6 以降では、テーブルの作成はオンライン操作をサポートしていますが、最終的にはビジネスの低ピーク時に使用されます。 上記は、MySQL がデータを削除した後もテーブル ファイル サイズが変更されない理由の詳細です。MySQL テーブル ファイル サイズの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。 以下もご興味があるかもしれません:
|
<<: Vue2とVue3の兄弟コンポーネント通信バスの違いと使い方
目次序文provide/inject を通じて Vuex 関数を実装する方法このプラグインをアプリケ...
目的: vuex で管理されている状態データを同時にローカルに保存できるようにします。独自のストレー...
この記事では、Webページの画像の回転を実現するためのjsの具体的なコードを参考までに共有します。具...
タブバー: 異なるタブをクリックすると異なるコンテンツが表示され、クリックしたタブのスタイルが変更さ...
目次01 よくある故障 1 02 よくある欠陥 2 03 よくある欠陥 3 04 よくある欠陥 4 ...
この記事では、MySQL の整合性制約について説明します。ご参考までに、詳細は以下の通りです。メイン...
目次Samba サーバーの紹介Samba サーバー コンポーネントSamba サーバー関連の設定ファ...
前に書いたものは複雑すぎるので、シンプルなコアにしましょう <html> <ヘッド...
今日、MySQL の新しいバージョン (8.0.21) をインストールしましたが、ノード フレームワ...
現在、ほとんどのプロジェクトが Docker 上にデプロイされ始めていますが、デプロイのプロセスはま...
Windows フォームと同様の効果を得るには、中央をドラッグして div の位置を変更し、端をド...
ステップ1: MySQLでbinlogが有効になっていることを確認する '%log_bin%...
<br />前のチュートリアル:Webデザインチュートリアル(2):模倣と盗作について。...
Web サーバーは、独立したドメイン名を持つ複数の Web サイトを構築できるほか、通信経路上のトラ...
目次1. はじめに2. レンダリング3. コード4. 学生情報管理システムのメインインターフェース1...