bucket-sort logo bucket-sort

プログラミングとインフラエンジニアリングの覚え書き

OPTIMIZE TABLEは何をしているのか？InnoDBの内部動作と実運用での注意点

MySQL , AWS bucket-sort

MySQL を運用していると、一度は目にするコマンド。

OPTIMIZE TABLE tbl;

なんとなく「最適化するらしい」というのは分かるけれど、

  • 実際に何をしているのか？
  • サービスは止まるのか？
  • 本当にやる価値があるのか？

今回は InnoDB 前提で、実運用目線で整理してみます。

OPTIMIZE TABLE で何が起きる？

InnoDB の場合、実質これと同じです。

ALTER TABLE tbl ENGINE=InnoDB;

つまり：

完全な再生成（rebuild）

内部では以下の処理が行われます：

  1. 新しいテーブルを作成
  2. 既存データをコピー
  3. インデックスを再構築
  4. 古いテーブルを削除
  5. 新旧テーブルを入れ替え（swap）

テーブルを作り直しているというのが本質です。

「最適化」というよりは、

テーブルを一度バラして組み直す

に近いです。

何のためにやるの？

主な効果はこちら。

断片化の解消（デフラグ）

DELETE / UPDATE を大量に行うと：

  • ページに空きが増える
  • データがバラバラに配置される
  • 無駄な I/O が増える
  • テーブルサイズが縮まらない

OPTIMIZE を実行すると：

  • データを詰め直す
  • テーブルサイズ縮小
  • インデックス再構築
  • フルスキャン高速化

特に 大量 DELETE 後は効果が分かりやすいです。

サービスは停止するの？

ここが一番重要です。

昔（MySQL 5.6 以前）

完全ロック → SELECT すら止まる

本番では怖くて実行できないコマンドでした。

今（MySQL 5.7 / 8.0 / Aurora / RDS）

InnoDB は基本的に ONLINE DDL になります。

  • SELECT → 可能
  • INSERT / UPDATE → ほぼ可能
  • 最後の入れ替え瞬間 → 数秒ロック

つまり：

ほぼ止まらない（最後だけ一瞬ロック）

体感目安：

  • 数百万行 → 数秒〜数十秒
  • 数千万行 → 数十秒〜数分

最後の メタデータロック だけ注意です。

なぜ「作り直しなのに止まらない」のか？

仕組みはこうです。

旧テーブル → 読み書き継続
        ↓
裏で新テーブル作成
        ↓
データコピー
        ↓
rename swap（瞬間）

MySQL は裏側で新しいテーブルを作り、

最後に rename で一瞬だけ切り替える。

だから：

  • 処理中 → 普通に動く
  • 最後だけ → 一瞬ロック

これが成立するわけです。

いつ使うべき？

やる価値があるケース：

  • 大量 DELETE 後（数十万以上）
  • テーブルサイズが縮まらない
  • フルスキャンが明らかに遅い

実運用での注意点（重要）

とにかく重い

テーブル全コピーなので：

  • CPU を使う
  • I/O を使う
  • レプリカ遅延の可能性
  • RDS では IOPS が跳ねる

特にクラウド環境では影響が見えやすいです。

おすすめ運用

  • 夜間バッチで実行
  • 小さいテーブルは不要
  • 巨大テーブルはメンテ時間に
  • レプリケーション環境では遅延監視

まとめ（超要約）

項目 内容
中身 テーブル再構築（rebuild）
効果 デフラグ・容量削減・高速化
停止 ほぼ止まらない（最後だけ短時間ロック）
コスト 重い（CPU + I/O）
おすすめ用途 大量 DELETE 後

参考リンク

MySQL :: MySQL 8.0 Reference Manual :: 15.7.3.4 OPTIMIZE TABLE Statement
https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html

InnoDB Details
For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE … FORCE, which rebuilds the table to update index statistics and free unused space in the clustered index.
An exclusive table lock is only taken briefly during the prepare phase and the commit phase of the operation.
短い排他テーブルロックがある (=ゼロ停止ではない)

MySQL :: MySQL 8.0 Reference Manual :: 17.12.1 Online DDL Operations
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

MySQL :: MySQL 8.0 Reference Manual :: 17.12.8 Online DDL Limitations
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-limitations.html

OPTIMIZE TABLE for an InnoDB table is mapped to an ALTER TABLE operation to rebuild the table and update index statistics and free unused space in the clustered index. Secondary indexes are not created as efficiently because keys are inserted in the order they appeared in the primary key. OPTIMIZE TABLE is supported with the addition of online DDL support for rebuilding regular and partitioned InnoDB tables.

Online DDLでサポートされている
“Online DDL” = データベースの構造変更を実行しながらデータの読み書きを止めない仕組み

Related Posts