MySQL OPTIMIZE TABLEの使い方：InnoDBでの効果と注意点

1 1. OPTIMIZE TABLEはテーブルを再構築または整理するSQL
- 1.1 1.1 OPTIMIZE TABLEで期待できること
2 2. 実行前に確認すること
- 2.1 2.1 バックアップと復旧手順を確認する
- 2.2 2.2 ロックとディスク容量を見積もる
3 3. OPTIMIZE TABLEの基本手順
- 3.1 3.1 実行前後の記録を残す
- 3.2 3.2 権限不足エラーに対応する
4 4. InnoDBでの仕組みと注意点
- 4.1 4.1 FULLTEXTインデックスがある場合の制約
- 4.2 4.2 パーティションテーブルでは対象を絞る
5 5. ANALYZE TABLEやALTER TABLEとの違い
- 5.1 5.1 遅いSQLにはEXPLAINを先に使う
6 6. よくある失敗と避け方
7 7. 実務での判断基準
- 7.1 7.1 安全な実行チェックリスト
8 8. FAQ

1. OPTIMIZE TABLEはテーブルを再構築または整理するSQL

【結論】OPTIMIZE TABLEは、MySQLのテーブルを整理し、不要な空き領域や断片化を改善するためのSQLです。InnoDBでは多くの場合、テーブルを再構築する処理として考えると実務判断を誤りにくくなります。

【定義】OPTIMIZE TABLEとは、テーブルデータとインデックスを整理するMySQLのメンテナンス用SQLです。対象テーブルのストレージエンジンにより、実行内容、ロック、効果が変わります。

大量のDELETEや可変長カラムのUPDATEを繰り返すと、テーブル内部に再利用可能な空き領域が増えることがあります。OPTIMIZE TABLEはその整理に使えますが、検索が遅い原因を何でも解決する魔法のコマンドではありません。遅いSQL、インデックス不足、実行計画の問題、I/O負荷、ロック待ちは別に切り分けます。

MySQLの仕様はバージョンで変わるため、詳細は公式マニュアルのOPTIMIZE TABLE Statementで確認できます。この記事では、MySQL 8系のInnoDB運用を中心に、初心者が安全に判断できる手順をまとめます。

1.1 OPTIMIZE TABLEで期待できること

【結論】期待できる主な効果は、空き領域の整理、テーブル再構築、統計情報の更新です。ただし、実際にファイルサイズが減るか、クエリが速くなるかは環境により異なります。

InnoDBでは、OPTIMIZE TABLEは内部的にALTER TABLE ... FORCE相当の再構築とANALYZE TABLE相当の処理として扱われることがあります。つまり、テーブルを作り直す重い操作になり得ます。

代表的な使いどころです。

大量削除後に.ibdファイルや表領域のサイズを整理したい。
可変長カラムの更新が多く、テーブルの断片化が疑われる。
メンテナンス時間帯にテーブル再構築を計画している。
検証環境で実行時間、ロック、レプリケーション遅延を確認できる。

小さなテーブルや変更量が少ないテーブルでは、実行しても目に見える効果がない場合があります。

2. 実行前に確認すること

【結論】本番でいきなり実行せず、対象テーブル、ストレージエンジン、サイズ、空き領域、バックアップ、ロック影響を確認します。確認なしの定期実行は避けます。

まず対象テーブルの状態を見ます。

SHOW TABLE STATUS LIKE 'orders'\G

確認したい主な項目です。

EngineがInnoDBかどうか。
Rowsは概算値として見る。
Data_lengthとIndex_lengthでテーブルとインデックスの規模を見る。
Data_freeで再利用可能な空き領域の目安を見る。
テーブルサイズがメンテナンス時間に収まるか見積もる。

SHOW TABLE STATUSの値は概算を含みます。厳密な判断が必要な場合は、information_schema.tables、OS上のファイルサイズ、監視データ、過去の実行時間を合わせて確認します。

2.1 バックアップと復旧手順を確認する

【結論】OPTIMIZE TABLEはテーブル再構築を伴う可能性があるため、バックアップと復旧手順を先に確認します。バックアップがあるだけでなく、戻せることが重要です。

最低限の確認項目です。

直近バックアップの取得時刻を確認する。
復元手順と必要時間を確認する。
レプリケーション構成なら、実行先と遅延影響を確認する。
メンテナンス時間帯とアプリケーション停止要否を決める。
失敗時の連絡先と切り戻し判断を決める。

本番でのテーブル再構築は、SQLが成功するかだけでなく、失敗時に業務を戻せるかまで含めて計画します。

2.2 ロックとディスク容量を見積もる

【結論】InnoDBではオンラインDDLとして処理される場合がありますが、処理中に短時間の排他ロックが必要になることがあります。大きなテーブルでは一時的なディスク容量も問題になります。

OPTIMIZE TABLE中は、同時実行の書き込み、メタデータロック、レプリケーション遅延に注意します。特にWebアプリケーションが常時書き込むテーブルでは、短時間のロックでもエラーやタイムアウトにつながることがあります。

ディスク容量も確認します。再構築では一時的に追加領域が必要になる場合があります。空き容量が不足すると処理失敗だけでなく、同じサーバー上の他のDBやログ出力にも影響します。

3. OPTIMIZE TABLEの基本手順

【結論】基本構文はOPTIMIZE TABLE テーブル名;です。実行前後で状態を記録し、効果と副作用を確認します。

単一テーブルの実行例です。

OPTIMIZE TABLE orders;

複数テーブルを指定することもできます。

OPTIMIZE TABLE orders, order_items;

ただし、複数テーブルをまとめると影響範囲が広がります。初心者は、検証環境で時間とロック影響を測ってから、重要度の高いテーブルを分けて実行する方が安全です。

3.1 実行前後の記録を残す

【結論】実行したかどうかだけでなく、実行前後のサイズ、時間、警告、アプリケーション影響を記録します。次回の判断材料になります。

記録例です。

SHOW TABLE STATUS LIKE 'orders'\G

OPTIMIZE TABLE orders;

SHOW TABLE STATUS LIKE 'orders'\G
SHOW WARNINGS;

結果には、テーブル名、操作、メッセージが返ります。警告がある場合は必ず確認します。Table does not support optimize, doing recreate + analyze insteadのようなメッセージは、対象ストレージエンジンで再構築と統計更新として処理されたことを示します。

3.2 権限不足エラーに対応する

【結論】OPTIMIZE TABLEには対象テーブルへの適切な権限が必要です。権限不足の場合は、アプリケーション用ユーザーではなく、管理用ユーザーで実行可否を確認します。

代表的なエラーです。

ERROR 1142 (42000): INSERT command denied to user 'app'@'%' for table 'orders'

環境により必要権限や運用ルールは異なります。アプリケーション接続ユーザーに広いDDL権限を付けるのではなく、メンテナンス用の権限管理と監査ログを用意します。

4. InnoDBでの仕組みと注意点

【結論】InnoDBのOPTIMIZE TABLEは、テーブルを再構築して領域を整理する重いメンテナンスとして扱います。通常のSELECT改善だけを目的に実行する前に、実行計画とインデックスを確認します。

InnoDBは行データとインデックスを表領域に保持します。innodb_file_per_tableが有効で各テーブルが個別の.ibdファイルを持つ構成では、再構築によりOS上のファイルサイズが縮小する場合があります。一方、共有表領域や構成によっては、空き領域がMySQL内部で再利用されるだけで、OSへ返らないことがあります。

4.1 FULLTEXTインデックスがある場合の制約

【結論】InnoDBテーブルにFULLTEXTインデックスがある場合、OPTIMIZE TABLEの挙動や制約を確認します。大量のFULLTEXTメンテナンスは通常のインデックスより影響が大きくなることがあります。

全文検索インデックスを持つテーブルでは、削除済みドキュメントの整理や再構築に関する設定が関係します。innodb_optimize_fulltext_onlyやinnodb_ft_num_word_optimizeのような設定を使う場面もありますが、通常のテーブル整理とは目的が異なります。全文検索を使う本番テーブルでは、必ず検証環境で実行時間と検索影響を確認します。

4.2 パーティションテーブルでは対象を絞る

【結論】パーティションテーブルでは、全体ではなく対象パーティションを最適化できる場合があります。削除が集中した期間パーティションだけを対象にすると、影響範囲を小さくできます。

例です。

ALTER TABLE access_logs OPTIMIZE PARTITION p202605;

月別や日別のログテーブルでは、古いパーティションだけを削除または整理する設計の方が、全体にOPTIMIZE TABLEをかけるより扱いやすいことがあります。パーティション設計、バックアップ、レプリケーションの運用と合わせて判断します。

5. ANALYZE TABLEやALTER TABLEとの違い

【結論】OPTIMIZE TABLE、ANALYZE TABLE、ALTER TABLE ... FORCEは目的が違います。検索が遅いときは、まず実行計画と統計情報を見てから再構築が必要か判断します。

SQL	主な目的	代表的な使いどころ	注意点
`OPTIMIZE TABLE`	テーブル再構築や領域整理	大量削除後の空き領域整理	ロック、時間、ディスク容量に注意
`ANALYZE TABLE`	統計情報の更新	実行計画が不自然なとき	データ領域の整理は目的ではない
`ALTER TABLE ... FORCE`	テーブル再構築	明示的に再構築したいとき	DDLとしての影響を確認

ANALYZE TABLEの詳細は公式マニュアルのANALYZE TABLE Statementで確認できます。

5.1 遅いSQLにはEXPLAINを先に使う

【結論】検索やJOINが遅い場合、OPTIMIZE TABLEより先にEXPLAINで実行計画を確認します。テーブル再構築ではインデックス不足や条件式の問題は解決しません。

確認例です。

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 1001
  AND ordered_at >= '2026-01-01';

type、key、rows、Extraを見て、想定したインデックスが使われているか確認します。WHERE句に関数をかけている、結合条件の型が違う、必要な複合インデックスがない、といった問題はOPTIMIZE TABLEでは直りません。

6. よくある失敗と避け方

【結論】失敗の多くは、効果の見積もり不足、ロック影響の見落とし、バックアップ不足、原因切り分け不足から起きます。実行前チェックリストを使います。

6.1 毎日自動実行して負荷を増やす

【結論】OPTIMIZE TABLEを根拠なく毎日実行する運用は避けます。変更量、空き領域、実行時間、業務影響を見て必要な時だけ実行します。

定期メンテナンスが必要な場合でも、対象テーブルを絞ります。全テーブルを順番に最適化する処理は、レプリケーション遅延やI/O負荷を長時間発生させることがあります。

6.2 実行中のロック待ちを見逃す

【結論】メンテナンスSQLは、対象テーブルだけでなく利用中のアプリケーションにも影響します。実行中はプロセスリスト、ロック待ち、エラー率を監視します。

確認例です。

SHOW PROCESSLIST;

MySQL 8系ではPerformance Schemaを使って待ちやロックを詳しく追跡できます。運用監視が整っていない環境では、まず検証環境で実行時間を測り、メンテナンス時間帯を確保します。

6.3 容量不足のまま実行する

【結論】テーブル再構築では一時領域が必要になる場合があります。空き容量が少ないサーバーでは、実行前にディスク使用率とログ出力先を確認します。

OS上の容量不足は、MySQLだけでなくアプリケーションログ、バイナリログ、バックアップ処理にも影響します。レプリケーション構成では、実行によるバイナリログ増加やレプリカ側の遅延も考慮します。

7. 実務での判断基準

【結論】OPTIMIZE TABLEは、目的、対象、影響、復旧手順が説明できるときに実行します。説明できない場合は、先に原因調査を行います。

実行判断の目安です。

大量削除後にテーブルサイズを縮小したい明確な理由がある。
SHOW TABLE STATUSや監視データで空き領域やサイズ増加を確認した。
検証環境で実行時間とロック影響を測った。
バックアップと復旧手順を確認した。
レプリケーション遅延や業務影響を許容できる時間帯がある。
遅いSQLの原因がインデックス不足ではないことを確認した。

逆に、単に「最近遅い気がする」という理由だけなら、EXPLAIN、スロークエリログ、インデックス、I/O、ロック待ちを先に確認します。

7.1 安全な実行チェックリスト

【結論】本番実行前に、対象・時間・復旧・監視・完了確認を1つずつ確認します。チェックリスト化すると、属人的な作業ミスを減らせます。

対象テーブル名とデータベース名を確認する。
ストレージエンジンがInnoDBか確認する。
実行前のサイズと空き領域を記録する。
バックアップと復旧時間を確認する。
メンテナンス時間帯を確保する。
アプリケーションのエラー率とDB負荷を監視する。
実行後に警告、サイズ、主要SQLの挙動を確認する。

OPTIMIZE TABLEは、正しく使えばテーブル整理に役立つメンテナンスSQLです。一方で、目的なく実行すると、負荷だけ増えて効果が薄い作業になります。実行前の確認が最も重要です。

8. FAQ

8.1 OPTIMIZE TABLEとは何ですか？

【結論】OPTIMIZE TABLEは、MySQLのテーブルを再構築または整理して、データとインデックス領域を最適化するSQLです。InnoDBでは多くの場合、テーブル再構築として動作します。

8.2 InnoDBでOPTIMIZE TABLEを実行すると必ず速くなりますか？

【結論】必ず速くなるわけではありません。大量削除後の空き領域整理や断片化の改善に役立つ場合がありますが、通常の検索性能はインデックス設計や実行計画の影響も大きいです。

8.3 OPTIMIZE TABLEはいつ実行すべきですか？

【結論】大量のDELETEやUPDATE後にテーブルサイズを確認し、空き領域やファイルサイズの整理が必要な場合に検討します。小さな変更のたびに定期実行する運用は避けます。

8.4 OPTIMIZE TABLEはロックされますか？

【結論】InnoDBではオンラインDDLとして処理される場合がありますが、処理中に短時間の排他ロックが必要になることがあります。実行前に業務影響とメンテナンス時間帯を確認します。

8.5 OPTIMIZE TABLEとANALYZE TABLEの違いは何ですか？

【結論】OPTIMIZE TABLEはテーブルの再構築や領域整理を目的に使い、ANALYZE TABLEは統計情報を更新して実行計画の判断材料を整えるために使います。目的が違うため、性能問題では先に原因を切り分けます。

8.6 OPTIMIZE TABLEの前にバックアップは必要ですか？

【結論】本番テーブルでは事前バックアップまたは復旧手順の確認が必要です。テーブル再構築を伴うため、失敗時に戻せる状態を用意してから実行します。