MySQL でデータを削除してもテーブルファイルのサイズが変更されないのはなぜですか?

長期間稼働しているデータベースの場合、テーブルがストレージ領域を占有しすぎるという問題がよく発生します。ただし、多くの不要なテーブルを削除した後も、テーブルファイルのサイズは変わりません。この問題を解決するには、InnoDB がテーブル領域を再利用する方法を理解する必要があります。

テーブルの場合、占有されるスペースは主にテーブル構造とテーブルデータの 2 つの部分に分かれます。一般的に、テーブル構造の定義はごくわずかなスペースしか占有しません。したがって、スペースの問題は主にテーブルデータに関連しています。

MySQL 8.0 より前では、テーブル構造は .frm サフィックスを持つファイルに保存されていました。 8.0 では、システムデータテーブルでテーブル構造を定義できるようになりました。

テーブルデータの保存について

テーブルデータは、共有テーブルスペースまたはinnodb_file_per_tableによって制御される別のファイルに保存できます。

OFFの場合は、データディクショナリとともにシステム共有表領域に存在することを意味します。
ONの場合、各InnoDBテーブル構造は.idbという拡張子のファイルに保存されます。

5.6.6 以降では、デフォルト値は ON です。
このパラメータを ON に設定することをお勧めします。これにより、必要がない場合に、システムが drop table コマンドを通じてファイルを直接削除します。
ただし、共有表領域では、表が削除されても、領域は再利用されません。

切り捨てる = 削除する + 作成する

データ削除プロセス

ただし、 deleteを使用してデータを削除すると、一部の行のみが削除され、表スペースが再利用されない場合があります。

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 の主キーのすべてのデータページをスキャンします。
生成されたデータページを使用して B+ ツリーを生成し、一時ファイルに保存します。
一時ファイルを生成する際に、A に対して操作があった場合は、図の状態 2 に該当し、ログファイルに記録されます。
一時ファイルが生成された後、ログファイルが一時ファイルに適用され、状態 3 に対応するテーブル A と同じデータファイルが取得されます。
A テーブルのデータファイルを一時ファイルに置き換えます。

行ログファイルが存在するため、再構築中にテーブル 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プロセスがオンラインの場合、インプレースで実行する必要があります。
インプレース DDL の場合は、<= 8.0 でフルテキストインデックスや空間インデックスを追加するなど、オンラインにしないでください。

拡大する

最適化、分析、およびテーブル変更の違いについて説明します。

alter table t engine = InnoDB (つまり、再作成) は、デフォルトでオンライン DDL プロシージャを使用します。
analyze table t はテーブルを再構築しません。データを変更せずにテーブルのインデックス情報のみを再計算します。このプロセス中に、MDL 読み取りロックが追加されます。
テーブル t の最適化は、前の 2 つの手順の操作と同等です。