MariaDBの最適化（OPTIMIZE TABLE）を実行する方法

データベースの最適化（OPTIMIZE TABLE）とは

データベースのデータの削除や更新を繰り返すと、ディスク上のデータ配置に隙間（断片化/フラグメンテーション）ができます。
これを放置すると、ファイルサイズが肥大化し、読み込み速度が低下します。
このデータ配置に隙間ができた「空き地（断片化）」を整理して、ファイルを詰め直す作業の事を言います

• メリット:
  • 不要な空き領域を解放し、ディスク容量を削減できる。
  • インデックスが再構築され、検索クエリが高速化する。
• デメリットとリスク:
  • テーブルロック: 実行中、そのテーブルへの書き込み（INSERT/UPDATE等）が止まる場合がある。
  • 高負荷: CPUとI/Oを激しく消費する。
• 注意点:
  • 最適化を実行するには、一時的に、現在のテーブルサイズと同じだけの空き容量が必要です。空き容量がギリギリだと失敗します
  • 最適化を実行する前に、事前にバックアップを取ることが推奨されます

最適化が必要かどうか確認する

いきなり最適化コマンドを実行する前に、ディスク上のデータ配置に隙間（断片化/フラグメンテーション）があるかを確認します。確認するには以下のSQLを実行します

SELECT 
    table_schema AS `Database`, 
    table_name AS `Table`, 
    engine AS `Engine`,
    round(((data_length + index_length) / 1024 / 1024), 2) `Total Size (MB)`, 
    round((data_free / 1024 / 1024), 2) `Free Space (MB)`,
    round((data_free / (data_length + index_length) * 100), 2) AS `Fragmentation (%)`
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY data_free DESC;

SQLの解説

• data_free: これがOSに返却されていない「空き地」のサイズ。
• Fragmentation (%): 全体サイズに対する空き地の割合。
• information_schema.tables: MariaDBが持っているメタデータ（管理情報）を直接参照している。

実行すべきかどうかの判断基準

数値が出たら、以下の基準で「掃除」が必要か判断する。

• 最適化すべきケース:
  • Fragmentation が 20% を超えている。
  • Free Space が 数GB 単位で存在し、ディスクを圧迫している。
  • 大量の DELETE を行った直後。
• 今回「不要」と判断した理由:
  • 5年運用した最大テーブルの Fragmentation が 0.8% 程度だった。
  • innodb_file_per_table が ON であり、効率的に再利用されていたため。
  • 0.8%のために長時間ロックのリスクを取るメリットが少ないと判断。

最適化を実行する手順

もし数値が悪かった場合、以下の方法で実行する。

SQLで行う場合

-- 特定のテーブルを最適化
OPTIMIZE TABLE `テーブル名`;

コマンドライン（ssh等）で行う場合

# 全データベースを対象に一括最適化
$ mysqlcheck -u root -p --optimize --all-databases