MySQL テーブルスペースの断片化の概念と関連する問題の解決策

背景

MySQL を頻繁に使用すると、MySQL データファイルのディスク容量が一般的に増加し続け、データを削除したり、データのバッチを挿入したりしても、ディスク容量が変更されないことがあります。この奇妙な現象の原因は、MySQL テーブルスペースの断片化です。

表領域の断片化とは何ですか?

表領域の断片化とは、表領域内に断片が存在することを意味します。よりわかりやすい比喩を使うと、これは A4 用紙のようなものです。「表領域の断片化」とは、この A4 用紙を細かく裂いてから、再びまとめるようなものです。断片の間には隙間ができ、この隙間が「表領域の断片」です。再構成された断片は、実際には完全な A4 用紙よりも 1 サイズ大きくなり、テーブルスペースによって簡単に引き起こされる問題、つまり無駄なスペースも表します。

背景で説明した現象は、図で説明できます。

図中の数字は実際のデータ行を表し、角丸四角形はテーブルの表スペースを表します。左から右へ、最初の操作はデータを削除することです。MySQL はスペースを積極的に解放しないように設計されているため、テーブル内のデータ行が削除されると、データは「削除」されますが、この部分のスペースは実際には解放されず、テーブル A によって引き続き占有されます。そのため、ログテーブル内の大量のデータが削除されても、MySQL のディスクスペースは削減されないというシナリオが発生します。

PS: スペースを解放しないこの設計は、主に遅延削除に関連しています。初期のデータベースの設計では、使用されていた IO デバイスは一般に機械式ディスクであり、その読み取りおよび書き込みパフォーマンスは SSD よりもはるかに劣っていたため、削除操作によってディスク上のデータが直接削除されることはほとんどありませんでした。

データが削除された後、元々連続していたスペースに 2 つの空白領域が出現していることがわかります。これは一般にテーブルスペースホールと呼ばれます。ホールが多すぎる場合は、テーブルスペースの断片化 (対応するテーブルスペースが連続している) と呼ばれます。この部分のスペースは解放されませんが、再利用可能としてマークされます。右端のテーブルスペース図 (3 番目の角丸四角形) を参照してください。新しいデータが挿入されると、新しいデータはテーブルスペースの穴に再度書き込まれます。これは、大規模なデータ削除されたテーブルにデータを書き込むときに、テーブルスペースが大幅に増加しないか、まったく増加しない可能性があることも意味します。

実際、テーブルスペースの穴を引き起こす可能性がある操作は削除だけではありません。更新もこの問題を引き起こす可能性があります。たとえば、varchar などの可変長文字列のデータを変更する場合、列が短くなると非常に小さな穴が発生します。列が長くなると、スペース不足のためにデータ行の一部のデータが他の場所に移動される可能性があります。

表領域の断片化を確認する方法

MySQL のシステムテーブルにはテーブルスペースの使用状況が記録されます。これは次のクエリで確認できます。

SELECT CONCAT(テーブルスキーマ,'.',テーブル名) AS 'テーブル名',
                table_rows AS '行数'、
                CONCAT(ROUND(データ長/(1024*1024),2),'M') AS 'データサイズ',
                CONCAT(ROUND(インデックス長/(1024*1024),2),'M') AS 'インデックスサイズ',
                CONCAT(ROUND(データフリー/(1024*1024),2),'M') AS'データフリー',
                CONCAT(ROUND(データ空き容量/データ長,2),'%') AS 'データ空き容量',
                ENGINE を「エンジン」として
information_schema.TABLES から
ここで、table_schema = 'テーブル名' 
data_free desc で ORDER します。

data_free はテーブルスペースの断片化の合計スペースサイズを指し、data_free_pct はこのテーブルの断片化の割合を指します。効果は次のとおりです。

mysql> SELECT CONCAT(table_schema,'.',table_name) AS 'table_name',
    -> table_rows AS '行数'、
    -> CONCAT(ROUND(データ長/(1024*1024),2),' M') AS 'データサイズ',
    -> CONCAT(ROUND(インデックス長/(1024*1024),2),' M') AS 'インデックスサイズ',
    -> CONCAT(ROUND(data_free/(1024*1024),2),' M') AS'data_free',
    -> CONCAT(ROUND(データ空き容量/データ長,2),' %') AS 'データ空き容量',
    -> ENGINE を 'engine' として
    -> information_schema.TABLES から
    -> WHERE table_schema = 'sbtest'
    -> data_free desc で ORDER します。
+----------------+----------------+-----------+------------+-----------+---------------+---------+
| table_name | 行数 | data_size | index_size | data_free | data_free_pct | engine |
+----------------+----------------+-----------+------------+-----------+---------------+---------+
| sbtest.sbtest5 | 0 | 0.02 M | 0.00 M | 44.00 M | 2816.00 % | InnoDB |
| sbtest.sbtest4 | 986400 | 214.70 M | 15.52 M | 4.00 M | 0.02 % | InnoDB |
| sbtest.sbtest3 | 986400 | 214.70 M | 15.52 M | 4.00 M | 0.02 % | InnoDB |
| sbtest.sbtest2 | 986400 | 214.70 M | 15.52 M | 4.00 M | 0.02 % | InnoDB |
| sbtest.sbtest1 | 987400 | 199.70 M | 15.52 M | 4.00 M | 0.02 % | InnoDB |
+----------------+----------------+-----------+------------+-----------+---------------+---------+
セット内の行数は 5 です (0.00 秒)

最初のデータ行はテストに使用されます。テーブル内のすべてのデータが削除されているため、計算された data_free_pct は 100% を超えています。

表スペースの断片化問題を解決する方法

現在、テーブルスペースを再利用する方法は 1 つしかなく、最適化、テーブルの変更などを含む、テーブルの再構築です。一部の alter table 操作は、テーブルを再構築することによってのみ完了できます。そのため、大規模なテーブルでメンテナンス操作を実行した後、ディスク領域の使用量が減少することがあります。これは、テーブル領域のフラグメントによって解放された領域が再利用されたためです。

一般的な経験から、テーブルスペースの断片化回復操作を頻繁に実行することは推奨されません。テーブルの再構築はサーバーリソースに大きな影響を与え、このテーブルの書き込み操作に影響を与えるため、月に 1 回で十分です。ディスク容量が非常に少なく、ログが基本的にクリアされていない限り、断片化率 (data_free_pct) が 20% 未満であれば、あまり心配する必要はありません。