MySQL InnoDBのテーブル断片化を最適化：OPTIMIZE TABLEでディスク容量を回収する方法 - ITFROMZERO - Share tobe shared! 数百万件のデータを削除してもMySQLのディスク容量が減らない原因とは？InnoDBテーブルの断片化を検出し、OPTIMIZE TABLEを用いて本番環境で安全にディスク容量を回収する方法を解説します。

Table of Contents

数百万件のデータを削除してもサーバーのディスク容量が全く減らない理由

半年前、運用中のデータベース領域がアラート閾値である使用率95%に達しました。過去のログレコード1500万件をDELETE文で削除し、少なくとも40GBの容量を取り戻せると見込んでいました。しかし、クエリが完了した後にサーバー上でdf -hを確認したところ、ディスクの空き容量は1MBすら増えていませんでした。

もし同じような経験をしたことがあるなら、それはあなただけではありません。DELETE文を実行したり、可変長カラム（VARCHAR、TEXT、BLOB）を更新したりしても、InnoDBはディスク容量をOSに返却しません。その代わりに、該当のブロックを単に「空き」としてマークし、後で再利用できるように保持します。この仕組みにより、データページ内に無駄な「隙間」が生じる現象をテーブルの断片化（Table Fragmentation）と呼びます。

この影響は、余計なNVMe SSDストレージの追加コストが発生するだけに留まりません。さらに深刻なのは、クエリ性能の低下です。1,000件のレコードを読み取るために通常なら10データページで済むところ、ページの半分が無駄な空き領域で占められていると、MySQLは50ページもスキャンしなければならず、Buffer Poolを直接圧迫してしまいます。

MySQL InnoDBの断片化メカニズム

本番環境をダウンさせることなく根本的に対処するには、まずInnoDBの物理ファイルの構造を理解する必要があります。

1. 前提条件：innodb_file_per_table の設定

InnoDBはテーブルスペースを介してデータを管理します。MySQL 5.6以降では、innodb_file_per_tableパラメータがデフォルトで有効（ON）になっています。この場合、各テーブルはディスク上で個別の.ibdファイルとして保存されます（通常は/var/lib/mysql/ten_database/配下）。

SHOW VARIABLES LIKE 'innodb_file_per_table';

この設定がOFFになっている場合、すべてのテーブルデータとインデックスはibdata1（システムテーブルスペース）にまとめて格納されます。ibdata1ファイルは一度肥大化すると、SQLコマンドで縮小させることはできません。その場合の唯一の解決策は、データベース全体をダンプし、ファイルを削除してMySQLを再起動した上で、データを一からインポートし直すことになります。

2. ページ断片化（Page Fragmentation）の仕組み

InnoDBのデータは固定16KBのページ単位で管理されます。レコードが削除されると、InnoDBはそのレコードに削除マーク（delete-mark）を付けます。この領域は解放されず、後続のINSERT処理で再利用するための空きリストに追加されます。

そのため、OS層から見た物理.ibdファイルのサイズは縮小されません。アプリケーションから空いた隙間を埋める新規データが書き込まれない限り、.ibdファイルは実際の有効データ量に対して肥大化した状態のまま残り続けます。

断片化の測定と解消手順

ステップ1：断片化率と無駄な空き領域の確認

データベース全体に対してむやみに最適化を実行するのは避けてください。まずはinformation_schema.TABLESメタデータテーブルを参照し、余剰領域（DATA_FREE）を多く抱えているテーブルを特定します。

SELECT 
    TABLE_SCHEMA AS `Database`,
    TABLE_NAME AS `Table`,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS `Total_Size_MB`,
    ROUND(DATA_FREE / 1024 / 1024, 2) AS `Free_Space_MB`,
    ROUND((DATA_FREE / (DATA_LENGTH + INDEX_LENGTH + DATA_FREE)) * 100, 2) AS `Fragment_Percent`
FROM 
    information_schema.TABLES
WHERE 
    TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    AND DATA_FREE > 0
ORDER BY 
    DATA_FREE DESC;

Free_Space_MBカラムに表示される数値が、最適化後にOSへ解放できる見込みのディスク容量です。

ステップ2：OPTIMIZE TABLE によるディスク容量の回収

Fragment_Percentが20%を超え、Free_Space_MBが数GB以上あるテーブルを見つけたら、テーブルの最適化を実行します。

OPTIMIZE TABLE ten_database.ten_bang;

InnoDBテーブルで実行すると、MySQLは以下のような結果を返します。

+-----------------------+----------+----------+-------------------------------------------------------------------+
| Table                 | Op       | Msg_type | Msg_text                                                          |
+-----------------------+----------+----------+-------------------------------------------------------------------+
| ten_database.ten_bang | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| ten_database.ten_bang | optimize | status   | OK                                                                |
+-----------------------+----------+----------+-------------------------------------------------------------------+

「Table does not support optimize…」というメッセージは一見エラーのように見えますが、全く問題のない正常な挙動です。実際には、InnoDBは従来のMyISAMのようなインプレースでの最適化をサポートしておらず、内部的に以下のコマンドを実行しています。

ALTER TABLE ten_database.ten_bang ENGINE=InnoDB;

ストレージエンジンは一時的な.ibdファイルを作成し、有効なデータのみをコピー（空きページを完全に排除）してインデックスを再構築した後、新しいファイルで古いファイルを置き換えます。

ステップ3：本番環境で実行する際の安全原則

テーブルの再構築（table rebuild）は大量のI/OとCPUリソースを消費します。以下の4つの重要な注意点を必ず把握しておきましょう。

ディスクの空き容量の確保：サーバーには対象テーブルの1.5〜2倍以上の空き容量が必須です。例えば80GBのテーブルの場合、少なくとも120GBの空きが必要です。処理中にディスクが枯渇すると、トランザクションがロールバックされ、システムが停止する恐れがあります。
innodb_online_alter_log_max_size の確認：MySQLはOnline DDLに対応しており、テーブル再構築中もINSERT、UPDATE、DELETEが可能です。ただし、実行中の新規書き込みは一時バッファに保持されます。書き込み量が設定値（デフォルト128MB）を超えると、処理は即座にエラーとなります。
大規模テーブル（50GB超）への対策：巨大なテーブルに対して直接OPTIMIZE TABLEを実行すると、I/Oのボトルネックが発生し、レプリカで深刻なレプリケーション遅延（Replication Lag）を引き起こしやすくなります。このような場合は、Percona Toolkitのpt-online-schema-changeやgh-ostを使用し、小さなチャンク単位でデータをコピーしてください。
実行前のバックアップを徹底：極めて稀ですが、再構築中にI/O障害やカーネルパニック等が発生するとテーブルスペースが破損するリスクがあります。作業前には必ずスナップショットや直近のバックアップを取得しておきましょう。

# pt-online-schema-change を使用した安全な大規模テーブル最適化コマンド
pt-online-schema-change \
  --alter "ENGINE=InnoDB" \
  --chunk-size=1000 \
  --max-load="Threads_running=25" \
  --critical-load="Threads_running=50" \
  --execute D=ten_database,t=ten_bang

適切な監視と自動化のベストプラクティス

データベース全体に対するOPTIMIZE TABLEの定期実行を、安易にcronジョブへ組み込むのは絶対に避けてください。不要なI/O負荷を発生させるだけでなく、リソースロックのリスクも伴います。

推奨されるアプローチは、週次で実行されるスクリプトを用意し、断片化率が30%超かつ空き領域が5GB以上存在するテーブルのみを抽出することです。その上でアラート通知を行うか、深夜などのオフピーク時間帯（午前2時〜4時頃）に順次実行させます。テーブルスペースの仕組みを正しく理解し、安定的かつ持続可能なサーバーリソース管理を実現しましょう。