MySQL テーブルの断片化を解消し、スペースを再利用する方法

テーブルを最適化する
ALTER TABLE テーブル名 ENGINE = Innodb;
質問 1: OPTIMIZE TABLE と ALTER TABLE xxxx ENGINE = INNODB のどちらを使用する方が良いですか?
質問 2: ALTER TABLE xxxx ENGINE= INNODB テーブルのインデックスフラグメントはデフラグされますか?
その他のツール

参考文献:

MySQL テーブルの断片化の原因

MySQL におけるテーブルの断片化の原因について、簡単にまとめてみましょう。断片化の原因は、MySQL エンジンによって異なります。これらの違いについては、ここでは深く理解および分析しません。この記事では、InnoDB エンジンのみに焦点を当てます。要約に不備や誤りがありましたらご指摘ください。

InnoDB テーブルのデータはページに格納され、各ページには複数のレコードを格納できます。これらのレコードは、B+ ツリーインデックスと呼ばれるツリー構造に編成されます。テーブル内のデータと補助インデックスは両方とも B+ ツリー構造を使用します。テーブル内のすべてのデータを保持するこの B+ ツリーインデックスはクラスター化インデックスと呼ばれ、主キーによって編成されます。クラスター化インデックスのリーフノードには行内のすべてのフィールドの値が含まれ、セカンダリインデックスのリーフノードにはインデックス列と主キー列が含まれます。

InnoDB では、一部の行が削除されると、インデックスから物理的に削除されるのではなく、「削除済み」としてマークされるだけなので、スペースは実際には解放されず、再利用されません。 InnoDB の Purge スレッドは、これらの未使用のインデックスキーと行を非同期的にクリーンアップします。ただし、解放された領域は再利用のためにオペレーティングシステムに返されないため、ページに多くの穴が残ります。テーブル構造に動的な長さのフィールドが含まれている場合、スペースが不足しているため、InnoDB はこれらのホールを再利用して新しい行を格納できない可能性があります。詳細については、ブログ「断片化された MySQL InnoDB テーブルの概要」を参照してください。

さらに、データを削除すると、ページに空白スペースが表示されます。ランダムな DELETE 操作を多数実行すると、データファイル内に不連続な空白スペースが必然的に発生します。データを挿入するときにこれらの空白スペースが使用され、データの保存場所が不連続になります。物理的な保存順序が論理的なソート順序と異なるため、データの断片化が発生します。

大量の UPDATE もファイルの断片化を引き起こします。Innodb の最小の物理ストレージ割り当て単位はページであり、UPDATE によってページ分割が発生することもあります。ページ分割が頻繁に発生すると、ページがまばらになり、不規則に埋められるため、最終的にデータが断片化されます。

まず、Mysql テーブルは行が更新されると断片化されますが、これは正常な状況であることを理解する必要があります。テーブルが作成されると、たとえばデータを含むダンプを使用してインポートされると、すべての行が断片化されずに多くの固定サイズのページに格納されます。可変長の行を更新すると、この行を含むページは変更を格納するために 2 つ以上のページに分割され、これらの新しい 2 ページ (またはそれ以上) には未使用のスペースを埋める空白スペースが含まれます。

テーブルのデータストレージも断片化される可能性があります。ただし、データストレージの断片化はインデックス作成よりも複雑です。データの断片化には 3 つの種類があります。 ##以下の部分は[High Performance MySQL]より抜粋##

行の断片化

この断片化は、データ行が複数の場所に複数の部分として保存されることを意味します。クエリがインデックスから 1 行のみにアクセスする場合でも同様です。行の断片化もパフォーマンスの低下を引き起こす可能性があります。

行内断片化

行の断片化は、論理的に連続したページまたは行がディスク上に連続して保存されていない場合に発生します。行の断片化は、データがディスク上に順番に格納されることでメリットが得られる、テーブル全体のスキャンやクラスター化インデックスのスキャンなどの操作に大きな影響を与えます。

空き領域の断片化

残存領域の断片化とは、データページ内に大量の空き領域が存在することを指します。これにより、サーバーが大量の不要なデータを読み取る可能性があります。これにより無駄が生じます。

MyISAM テーブルの場合、3 種類の断片化すべてが発生する可能性があります。しかし、InnoDB には短い行の断片化はありません。InnoDB は短い行を移動し、それを 1 つのフラグメントに書き込みます。 InnoDb は短い行を移動し、 1 つのフラグメントに書き換えます。

公式ドキュメント 14.15.4 テーブルの断片化の解消では、テーブルの断片化を減らす方法が次のように説明されています (非常に簡潔です。MySQL の公式ドキュメントは簡潔な場合が多く、情報量が多いものの、詳細な紹介はありません)。

セカンダリインデックスへのランダムな挿入やセカンダリインデックスからのランダムな削除により、インデックスが断片化される可能性があります。断片化とは、ディスク上のインデックスページの物理的な順序が、ページ上のレコードのインデックス順序と近くないこと、またはインデックスに割り当てられた 64 ページのブロック内に未使用のページが多数あることを意味します。
断片化の症状の 1 つは、テーブルが「必要」な領域よりも多くの領域を占有することです。それが正確にどのくらいかを判断するのは困難です。すべての InnoDB データとインデックスは B ツリーに格納され、そのフィルファクターは 50% から 100% の範囲で変化します。

セカンダリインデックスへのランダムな挿入または削除により、インデックスが断片化される可能性があります。断片化とは、ディスク上のインデックスページの物理的な順序がページに記録されたインデックスの順序に近くないこと、またはインデックスに割り当てられた 64 ページのブロック内に未使用のページが多数あることを意味します。

断片化の症状の 1 つは、テーブルが「占有すべき」領域よりも多くの領域を占有していることです。正確には、どれくらいかを判断するのは難しいです。すべての InnoDB データとインデックスは B ツリーに格納され、そのフィルファクターは 50% から 100% の間で変化します。断片化のもう一つの症状は、このようなテーブルスキャンが「本来」かかる時間よりもずっと長くかかることです。

MySQL で極度に断片化されたテーブルを見つける方法

MySQL のテーブルの断片化に関しては、一般的に MySQL の断片化を見つける方法が 2 つあります。

方法 1: show table status from xxxx like 'xxxx' \G; を使用します。

最初の xxx: テーブルが配置されているデータベースの名前、2 番目の xxx: クエリされるテーブルの名前。この方法は実はあまり実用的ではありません。たとえば、単一のテーブルの断片化のみをクエリできます (データベース内のテーブルごとに試す必要がありますか?)。データベース内のすべてのテーブルまたはインスタンス全体の断片化をクエリすることはできません。これは単なる参考方法です。

mysql> テーブル frag_tab_myisam を作成します
    -> (
    -> id int、
    -> 名前varchar(63)
    ->) エンジン=MyISAM;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
 
mysql> frag_tab_myisamに挿入
    -> values(1, 'テスト行 1 のみです');
クエリは正常、1 行が影響を受けました (0.00 秒)
 
マイSQL> 
mysql> frag_tab_myisamに挿入
    -> values(2, 'テスト行2のみです');
クエリは正常、1 行が影響を受けました (0.00 秒)
 
マイSQL> 
マイSQL> 
mysql> frag_tab_myisamに挿入
    -> values(3, 'テスト行3のみです');
クエリは正常、1 行が影響を受けました (0.00 秒)
 
マイSQL> 
mysql> frag_tab_myisamに挿入
    -> values(4, 'テスト行4のみです');
クエリは正常、1 行が影響を受けました (0.00 秒)
 
マイSQL>
 
 
mysql> 'frag_tab_myisam' のような kkk からのテーブル ステータスを表示します \G;

次のスクリーンショットに示すように、DML操作がない場合、Data_freeのサイズは0になります。

次に、データベースから 2 つのレコードを削除します。以下に示すように、Data_free のサイズは 64KB です。

mysql> id =1 の frag_tab_myisam から削除します。
クエリは正常、1 行が影響を受けました (0.00 秒)
 
mysql> id = 3 の frag_tab_myisam から削除します。
クエリは正常、1 行が影響を受けました (0.00 秒)

方法 2: information_schema.TABLES をクエリして、テーブルの断片化情報を取得します。

以下に示すように、これはテーブルの断片化を照会するために私がコンパイルした古典的なスクリプトです。これを基にさまざまな派生機能を作成できます。たとえば、データベースのテーブルの断片化をクエリできます。または、テーブルに 50M を超える空き領域があります。必要に応じてクエリ条件を設定できます。ここではスキップしてください。

SELECT CONCAT(テーブルスキーマ, '.', テーブル名) AS TABLE_NAME
      ,エンジン AS TABLE_ENGINE 
      ,テーブルタイプ AS TABLE_TYPE
      ,table_rows を TABLE_ROWS として
      ,CONCAT(ROUND(データ長 / ( 1024 * 1024), 2), 'M') AS TB_DATA_SIZE 
      ,CONCAT(ROUND(インデックス長 / ( 1024 * 1024), 2), 'M') AS TB_IDX_SIZE 
      ,CONCAT(ROUND((データ長 + インデックス長) 
            / ( 1024 * 1024 )、2)、'M') 合計サイズとして
      ,CASE WHEN data_length = 0 THEN 0
            それ以外の場合は ROUND(インデックス長 / データ長, 2) END AS TB_INDX_RATE
    ,CONCAT(ROUND( データフリー / 1024 / 1024,2), 'MB') AS TB_DATA_FREE 
    (データ長 + インデックス長) = 0 の場合、0
             それ以外の場合は ROUND(データ空き領域/(データ長 + インデックス長),2) 
     TB_FRAG_RATEとして終了
information_schema.TABLES から  
ORDER BY data_free DESC;
SELECT CONCAT(テーブルスキーマ, '.', テーブル名) AS TABLE_NAME
      ,エンジン AS TABLE_ENGINE 
      ,テーブルタイプ AS TABLE_TYPE
      ,table_rows を TABLE_ROWS として
      ,CONCAT(ROUND(データ長 / ( 1024 * 1024), 2), 'M') AS TB_DATA_SIZE 
      ,CONCAT(ROUND(インデックス長 / ( 1024 * 1024), 2), 'M') AS TB_IDX_SIZE 
      ,CONCAT(ROUND((データ長 + インデックス長) 
            / ( 1024 * 1024 )、2)、'M') 合計サイズとして
      ,CASE WHEN data_length = 0 THEN 0
            それ以外の場合は ROUND(インデックス長 / データ長, 2) END AS TB_INDX_RATE
    ,CONCAT(ROUND( データフリー / 1024 / 1024,2), 'MB') AS TB_DATA_FREE 
    (データ長 + インデックス長) = 0 の場合、0
             それ以外の場合は ROUND(データ空き領域/(データ長 + インデックス長),2) 
     TB_FRAG_RATEとして終了
information_schema.TABLES から  
ここで ROUND(DATA_FREE/1024/1024,2) >=50
ORDER BY data_free DESC;
 

テーブルスキーマを選択
      ,テーブル名 
      、エンジン
      ,ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS SIZE_MB
      ,ROUND(DATA_FREE/1024/1024,2) AS FREE_SIZ_MB
information_schema.TABLES から 
DATA_FREE >=10*1024*1024 の場合
FREE_SIZ_MB で並べ替え:

MySQL でテーブルの断片化を減らす方法

MySQL では、断片化を減らすために OPTIMIZE TABLE と ALTER TABLE XXXX ENGINE = INNODB を使用できます。これら 2 つの方法の簡単な紹介は次のとおりです。

テーブルを最適化する

OPTIMIZE TABLE は、テーブルとインデックスの物理ストレージを再編成し、ストレージ領域の使用量を削減し、テーブルにアクセスする際の IO 効率を向上させます。各テーブルに加えられる正確な変更は、そのテーブルで使用されるストレージエンジンによって異なります。

OPTIMIZE TABLE は、INNODB、MYISAM、ARCHIVE、NDB のテーブルタイプをサポートしています。テーブルデータとインデックスの物理ページを再編成し、占有スペースを削減し、テーブルにアクセスする際の IO を最適化するのに効果的です。 OPTIMIZE 操作ではテーブルが一時的にロックされるため、データ量が多いほど時間がかかります。

OPTIMIZE TABLE の後、テーブル内の変更はストレージエンジンに関連付けられます。

MyISAM の場合、PTIMIZE TABLE は次のように動作します。

テーブルに削除された行や分割された行がある場合は、テーブルを修復します。
インデックスページがソートされていない場合はソートします。
テーブルの統計が最新でない場合 (インデックスをソートしても修復できない場合) は、統計を更新します。

元の英語テキストは次のとおりです。

MyISAM テーブルの場合、OPTIMIZE TABLE は次のように動作します。

1. テーブルで行が削除または分割されている場合は、テーブルを修復します。

2. インデックスページがソートされていない場合は、ソートします。

3. テーブルの統計が最新でない場合（インデックスをソートしても修復できない場合）、統計を更新します。

InnoDBの場合、PTIMIZE TABLEは次のように動作します。

InnoDB テーブルの場合、 OPTIMIZE TABLE は ALTER TABLE ... FORCE にマップされ、テーブルを再構築してインデックス統計を更新し、クラスター化インデックス内の未使用領域を解放します。 InnoDB テーブルで実行すると、OPTIMIZE TABLE の出力に次のように表示されます。

mysql> テーブル foo を最適化します。
 
+----------+----------+----------+----------------------------------------------------------------------+
 
| テーブル | Op | メッセージ タイプ | メッセージ テキスト |
 
+----------+----------+----------+----------------------------------------------------------------------+
 
| test.foo | 最適化 | 注記 | テーブルは最適化をサポートしていないため、代わりに再作成 + 分析を実行します |
 
| test.foo | 最適化 | ステータス | OK |
 
+----------+----------+----------+----------------------------------------------------------------------+

OPTIMIZE TABLE は、通常の InnoDB テーブルとパーティション化された InnoDB テーブルにオンライン DDL を使用するため、同時 DML 操作のダウンタイムが短縮されます。テーブルの再構築は OPTIMIZE TABLE によってトリガーされ、ALTER TABLE ... FORCE のカバーの下で実行されます。排他テーブルロックは、操作の準備フェーズとコミットフェーズ中にのみ短時間実行されます。準備フェーズでは、メタデータが更新され、中間テーブルが作成されます。コミットフェーズでは、テーブルメタデータの変更がコミットされます。

OPTIMIZE TABLE は、次の条件下でテーブルコピーメソッドを使用してテーブルを再構築します。

old_alter_table システム変数が有効になっている場合。
mysqld --skip-new オプションが有効になっている場合。

OPTIMIZE TABLE は、FULLTEXT インデックスを含む InnoDB テーブルのオンライン DDL をサポートしていません。代わりに、コピーテーブルメソッドを使用します。

InnoDB はページ割り当て方式を使用してデータを保存し、MyISAM などの従来のストレージエンジンのように断片化の影響を受けません。最適化を実行するかどうかを検討するときは、サーバーが処理するトランザクションのワークロードを考慮してください。

ある程度の断片化が予想されます。 InnoDB はページの 93% のみを埋め、ページを分割せずに更新のための余地を残します。
削除によってギャップが残り、ページの内容が予想よりも少なくなる可能性があるため、テーブルを最適化する価値がある場合があります。

行を更新すると、通常、データ型と行の形式に応じて、行に十分なスペースがある場合に同じページのデータが書き換えられます。セクション14.9.1.5「InnoDB テーブルの圧縮の仕組み」およびセクション14.11「InnoDB 行フォーマット」を参照してください。

InnoDB は MVCC メカニズムを通じて同じデータの複数のバージョンを保持するため、同時実行性の高いワークロードでは時間の経過とともにインデックスにギャップが生じる可能性があります。セクション14.3「InnoDB マルチバージョン」を参照してください。

さらに、innodb_file_per_table=1 の InnoDB テーブルの場合、OPTIMIZE TABLE はテーブルとインデックスの物理ストレージを再編成し、空き領域をオペレーティングシステムに解放します。つまり、OPTIMIZE TABLE [tablename] は独立した表スペースにのみ適用されます。

OPTIMIZE TABLE の詳細については、https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html を参照してください。公式ドキュメントはかなり詳細であると感じます。

ALTER TABLE テーブル名 ENGINE = Innodb;

これは実際には NULL 操作です。表面的には何もしませんが、実際にはフラグメントを再編成します。最適化操作を実行すると、実際には空の ALTER コマンドが実行されますが、このコマンドも最適化の役割を果たし、テーブル全体を再構築し、未使用の空白スペースを削除します。

既存の InnoDB テーブルで ALTER TABLE tbl_name ENGINE=INNODB を実行すると、セクション 15.11.4「テーブルのデフラグ」で説明されているように、InnoDB テーブルのデフラグに使用できる「null」 ALTER TABLE 操作が実行されます。InnoDB テーブルで ALTER TABLE tbl_name FORCE を実行しても、同じ機能が実行されます。

質問 1: OPTIMIZE TABLE と ALTER TABLE xxxx ENGINE = INNODB のどちらを使用する方が良いですか?

実際、InnoDB エンジンの場合、ALTER TABLE xxxx ENGINE = INNODB は空の ALTER TABLE 操作を実行します。 OPTIMIZE TABLE は ALTER TABLE ... FORCE と同等です。上記の説明を参考にすると、場合によっては、OPTIMIZE TABLE または ALTER TABLE xxxx ENGINE= INNODB は基本的に同じです。ただし、場合によっては、ALTER TABLE xxxx ENGINE= INNODB の方が適しています。たとえば、old_alter_table システム変数が有効になっていないなどです。さらに、MyISAM タイプのテーブルの場合、OPTIMIZE TABLE よりも ALTER TABLE xxxx ENGINE = INNODB を使用する方が明らかに優れています。

質問 2: ALTER TABLE xxxx ENGINE= INNODB テーブルのインデックスフラグメントはデフラグされますか?

ALTER TABLE ENGINE = INNODB は、クラスター化インデックス上のデータとインデックスを再編成します。実験的に検証したい場合は、このコマンドを実行する前と実行後の index_length のサイズを比較することができます。

その他のツール

ネットユーザーは、テーブルの断片化を減らすために pt ツールまたは gh-ost を使用することを提案しています。私はまだそのようなツールを使ったことはありませんが、おそらく上記の 2 つのコマンドをカプセル化しているのでしょう。ここでは詳しく紹介しません。

参考文献:

高性能MySQL

最適化テーブル

https://dev.mysql.com/doc/refman/8.0/en/innodb-file-defragmenting.html

https://lefred.be/content/overview-of-fragmented-mysql-innodb-tables/

https://yq.aliyun.com/articles/41166

http://mysql.taobao.org/monthly/2015/08/05/

これで、MySQL テーブルのデフラグとスペース回復の方法に関するこの記事は終了です。MySQL テーブルのデフラグとスペース回復の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: