MySQL でテーブル データを削除した後もディスク領域がまだ占有されているのはなぜですか?

最近、上位コンピュータが下位コンピュータから報告されたデータを取得するプロジェクトがありました。報告頻度が高く、データ量が多いため、データが急速に増加し、ディスクを大量に占有しました。

コストを節約するには、データを定期的にバックアップし、 deleteコマンドを使用してテーブル レコードを削除します。

deleteは実行されましたが、テーブルファイルのサイズが減少していないため、不可解です。

このプロジェクトでは、データベースとしてMysqlを使用します。テーブルの場合、通常はテーブル構造とテーブルデータです。テーブル構造が占めるスペースは比較的小さく、通常はテーブルデータが占めるスペースになります。

delete使用してデータを削除すると、テーブル内のデータ レコードは確かに削除されますが、テーブル ファイルのサイズは変更されません。

1.MySQLデータ構造

mysqlを使用したことがある人なら、 B+樹について聞いたことがあるはずです。MySQL MySQL InnoDB 、データを格納するための構造としてB+ツリー (インデックス構成テーブルとも呼ばれます) を使用し、データはページごとに格納されます。したがって、データを削除する場合、次の 2 つの状況が考えられます。

• データページ内のいくつかのレコードを削除する
• データページ全体の内容を削除する

2. テーブルファイルのサイズは変更されておらず、MySQLの設計に関連しています

たとえば、レコード R4 を削除する場合は、次のようにします。

InnoDB 、再利用可能な場所と呼ばれるレコードR4直接削除済みとしてマークします。後でIDが300から700までのレコードを挿入すると、この位置が再利用されます。

ディスクファイルのサイズは縮小されないことがわかります。

通常、ページ全体のデータを削除すると、レコード マークも削除され、その位置でデータが再利用されます。これは、ページ全体のレコードを削除するときに、後で挿入されたデータが元の範囲内にない場合に位置を再利用できるという点で、ディクテーション レコードの削除とは異なります。ただし、ディクテーション レコードのみを削除する場合は、削除されたレコードの位置に一致するデータを挿入してから、再利用する必要があります。

したがって、データ行またはデータ ページが削除された場合でも、再利用のために削除済みとしてマークされるため、ファイル サイズは縮小されません。

3. テーブルのサイズを小さくするにはどうすればよいでしょうか?

DELETEデータ マーカーを削除するだけで、データ ファイルの断片化を解消しません。新しいデータが挿入されると、削除済みとしてマークされたレコード領域が再び使用されます。OPTIMIZE OPTIMIZE TABLE使用すると、未使用の領域を再利用し、データ ファイルを断片化を解消できます。

OPTIMIZE TABLE テーブル名;

注意: OPTIMIZE TABLE MyISAM 、 BDB 、およびInnoDBテーブルに対してのみ機能します。

あるいは、 ALTER TABLEを使用してテーブルを再構築することもできます。

ALTER TABLE テーブル名 ENGINE=INNODB

OPTIMIZE TABLEとALTER TABLEの違いは何かと尋ねる人もいるかもしれません。

alter table t engine = InnoDB (つまり、再作成)、 optimize table t recreate+analyzeに等しい

4. オンラインDDL

最後に、 Online DDLについてお話ししましょう。DBA dba日常業務の 1 つは、間違いなく DDL の変更です。DDL の変更はテーブルをロックしますが、これはdbaにとって永遠の悩みの種と言えます。特にddl変更を実行すると、ライブラリ上の多数のスレッドが「 Waiting for meta data lock 」の状態になります。そのため、バージョン 5.6 以降ではOnline DDLが導入されました。

Online DDLがリリースされる前は、 DDL を実行する主な方法は、 copyとinplace 2 つでした。 inplace方式は ( fast index creation ) とも呼ばれます。 copy方式と​​比較すると、 inplace方式ではデータがコピーされないため、高速になります。ただし、この方法はインデックスの追加と削除のみをサポートしており、コピー方法と同様に、プロセス全体を通じてテーブルをロックする必要があるため、あまり実用的ではありません。前の 2 つの方法と比較すると、 Online方式では読み取りだけでなく書き込み操作もサポートされます。

online DDLステートメントを実行するときは、 ALGORITHMキーワードとLOCKキーワードを使用します。これらの 2 つのキーワードは、 DDLステートメントの最後にあり、カンマで区切られています。次に例を示します。

ALTER TABLE tbl_name ADD COLUMN col_name col_type、ALGORITHM=INPLACE、LOCK=NONE;

アルゴリズムオプション

• INPLACE:置換: 元のテーブルに対して直接DDL操作を実行します。
• COPY:コピー: 一時テーブルを使用して一時テーブルを複製し、一時テーブルでDDLを実行してから、一時テーブルにデータをインポートし、名前を変更するなどします。この期間中、このような操作をサポートするには 2 倍のディスク容量が必要になります。実行中は、テーブルに対するDML操作は許可されません。
• DEFAULT: MySQL自体によって選択されるデフォルトの方法で、 INPLACEメソッドが最初に使用されます。
  
• ロックオプション
• SHARE:共有ロック。DDL DDL実行されるテーブルは読み取り可能ですが、書き込みはできません。
• なし:制限はありません。DDL DDL実行されるテーブルは読み取りおよび書き込み可能です。
• EXCLUSIVE:排他ロック。DDL DDL実行されるテーブルは読み取りも書き込みもできません。
• DEFAULT:デフォルト値。DDL DDLでLOCK句が指定されていない場合に使用されるデフォルト値です。指定されたLOCK値が

DEFAULT 、つまり、テーブルをロックするかどうかはMySQLによって決定されます。推奨されません。DDL DDLがテーブルをロックしないことが確実な場合は、 lockを指定したり、その値をdefault指定したりすることはできません。それ以外の場合は、ロック タイプを指定することをお勧めします。
DDL操作を実行する場合、 ALGORITHMオプションを指定する必要はありません。この場合、 MySQL INSTANT 、 INPLACE 、 COPYの順に適切なモードを自動的に選択します。同じ効果を持つALGORITHM=DEFAULTを指定することもできます。 ALGORITHMオプションが指定されているがサポートされていない場合は、エラーが報告されます。

OPTIMIZE TABLEとALTER TABLE table name ENGINE=INNODBどちらもOline DDLをサポートしていますが、業務アクセス量が少ない場合には、これらを使用することをお勧めします。

5. まとめ

データdelete場合、対応するデータ行は実際には削除されず、単に再利用可能としてマークされるだけなので、表スペースは小さくなりません。

データdelete後、テーブルを再構築してテーブルのサイズをすばやく縮小することができます ( OPTIMIZE TABLEまたはALTER TABLE )。バージョン 5.6 以降では、テーブル作成でOnline操作がサポートされていますが、ビジネスのピーク時以外は使用することをお勧めします。

MySQL テーブルデータを削除した後もディスク領域が占有される理由については、これでこの記事は終了です。MySQL MySQLデータ削除の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。