13.7.2.4 OPTIMIZE TABLE 構文

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name] ...

ストレージ領域を削減し、テーブルアクセス時の I/O 効率を向上させるために、テーブルデータとそれに関連付けられたインデックスデータの物理ストレージを再編成します。各テーブルに加えられる正確な変更は、そのテーブルによって使用されているストレージエンジンによって異なります。

OPTIMIZE TABLE は、テーブルのタイプに応じて次の場合に使用します。

• innodb_file_per_table オプションが有効な状態で作成されたために独自の .ibd ファイル を含む InnoDB テーブルに対して大量の挿入、更新、または削除操作を行なったあと。テーブルとインデックスが再編成されるため、ディスク領域をオペレーティングシステムによる使用のために再利用できます。

• InnoDB テーブル内の FULLTEXT インデックスの一部であるカラムに対して大量の挿入、更新、または削除操作を行なったあと。最初に、構成オプション innodb_optimize_fulltext_only=1 を設定します。インデックスの保守期間を妥当な時間に維持するために、検索インデックスで更新するワード数を指定する innodb_ft_num_word_optimize オプションを設定し、検索インデックスが完全に更新されるまで OPTIMIZE TABLE ステートメントのシーケンスを実行します。

• MyISAM または ARCHIVE テーブルの大きな部分を削除するか、あるいは可変長行を含む MyISAM または ARCHIVE テーブル (VARCHAR、VARBINARY、BLOB、または TEXT カラムを含むテーブル) に多くの変更を行なったあと。削除された行はリンクリスト内に保持され、以降の INSERT 操作は古い行の位置を再利用します。OPTIMIZE TABLE を使用すると、未使用領域を再利用したり、データファイルをデフラグしたりできます。テーブルを大幅に変更したあとは、このステートメントにより、そのテーブルを使用するステートメントのパフォーマンスを (場合によっては大幅に) 向上させることができます。

このステートメントには、このテーブルに対する SELECT および INSERT 権限が必要です。

OPTIMIZE TABLE は、パーティション化されたテーブルでもサポートされます。このステートメントのパーティション化されたテーブルでの使用やテーブルパーティションについては、セクション19.3.4「パーティションの保守」を参照してください。

MySQL 5.6.11 でのみ、このステートメントを発行する前に、gtid_next を AUTOMATIC に設定する必要があります。(Bug #16062608、Bug #16715809、Bug #69045)

OPTIMIZE TABLE は、InnoDB、MyISAM、および ARCHIVE テーブルに対して機能します。OPTIMIZE TABLE は、インメモリー NDB テーブルの動的なカラムに対してもサポートされます。ディスクデータテーブルに対しては機能しません。クラスタテーブルに対する OPTIMIZE のパフォーマンスは、OPTIMIZE TABLE による行のバッチの処理間で待機するミリ秒数を制御する ndb_optimization_delay システム変数の値を調整することによってチューニングできます。詳細は、セクション18.1.6.11「MySQL Cluster NDB 7.3 で解決された以前の MySQL Cluster の問題」を参照してください。

MySQL Cluster テーブルの場合、OPTIMIZE TABLE は、OPTIMIZE 操作を実行している SQL スレッドを (たとえば) 強制終了することによって中断できます。

デフォルトでは、OPTIMIZE TABLE はその他のストレージエンジンを使用して作成されたテーブルに対しては機能せず、このサポートがないことを示す結果を返します。--skip-new オプションを使用して mysqld を起動することによって、その他のストレージエンジンに対して OPTIMIZE TABLE を機能させることができます。この場合、OPTIMIZE TABLE は単に ALTER TABLE にマップされます。

InnoDB の詳細

InnoDB テーブルの場合、OPTIMIZE TABLE は ALTER TABLE ... FORCE にマップされます。これは、インデックス統計を更新し、クラスタ化されたインデックス内の未使用領域を解放するためにテーブルを再構築します。これは、次に示すように、InnoDB テーブルに対して実行したときに OPTIMIZE TABLE の出力に表示されます。

mysql> OPTIMIZE TABLE foo;
+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| test.foo | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.foo | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+

Mysql 5.6.17 より前は、OPTIMIZE TABLE はオンライン DDL (ALGORITHM=INPLACE) を使用しません。その結果、OPTIMIZE TABLE が実行中のテーブルに対しては (つまり、そのテーブルのロック中は) 並列 DML (INSERT、UPDATE、DELETE) が許可されません。また、主キーに現れる順序でキーが挿入されるため、セカンダリインデックスはそれほど効率的に作成されません。

5.6.17 の時点では、OPTIMIZE TABLE は、InnoDB の通常のテーブルとパーティション化されたテーブルの両方に対してオンライン DDL (ALGORITHM=INPLACE) を使用します。OPTIMIZE TABLE によってトリガーされ、ALTER TABLE ... FORCE の下で実行されるテーブル再構築は現在、オンライン DDL (ALGORITHM=INPLACE) を使用して実行され、短期間しかテーブルをロックしないため、並列 DML 操作のためのダウンタイムが短縮されます。

OPTIMIZE TABLE は引き続き、次の条件の下で ALGORITHM=COPY を使用します。

• old_alter_table システム変数が ON になっている場合。

• mysqld --skip-new オプションが有効になっている場合。

オンライン DDL (ALGORITHM=INPLACE) を使用した OPTIMIZE TABLE は、FULLTEXT インデックスを含む InnoDB テーブルではサポートされません。代わりに ALGORITHM=COPY を使用する必要があります。

InnoDB はページ割り当ての方法を使用してデータを格納するため、従来のストレージエンジン (MyISAM など) の場合のような断片化は発生しません。最適化を実行するかどうかを検討する場合は、サーバーが処理するトランザクションのワークロードを考慮してください。

• ある程度の断片化は予測されます。InnoDB は、ページを分割しなくても更新できる余地を残すために、ページを 93% までしかいっぱいにしません。

• 削除操作によってギャップが残され、ページの空きが目的より多くなることがあります。これにより、テーブルを最適化する価値が生まれる可能性があります。

• 行を更新すると通常、十分な領域が使用可能であれば、データ型と行フォーマットに応じて同じページ内のデータが書き換えられます。セクション14.7.5「InnoDB テーブルでの圧縮の動作」およびセクション14.9.1「InnoDB 行ストレージの概要」を参照してください。

• InnoDB はその MVCC メカニズムのために同じデータの複数のバージョンを保持するため、並列性の高いワークロードでは、時間の経過とともにインデックス内にギャップが残される可能性があります。セクション14.2.12「InnoDB マルチバージョン」を参照してください。

MyISAM の詳細

MyISAM テーブルの場合、OPTIMIZE TABLE は次のように機能します。

1. テーブルが行を削除または分割した場合は、そのテーブルを修復します。

2. インデックスページがソートされていない場合は、それをソートします。

3. テーブルの統計が最新でない (そのため、インデックスのソートによって修復を実行できない) 場合は、それを更新します。

その他の考慮事項

OPTIMIZE TABLE は、次のカラムを含む結果セットを返します。

カラム	値
Table	テーブル名
Op	常に optimize
Msg_type	status、error、info、note、または warning
Msg_text	情報メッセージ

5.6.17 より前の InnoDB テーブルやその他のテーブルタイプの場合、MySQL は、OPTIMIZE TABLE の実行中はそのテーブルをロックします。MySQL 5.6.17 の時点では、OPTIMIZE TABLE は、InnoDB の通常のテーブルとパーティション化されたテーブルに対してオンラインで実行されます。

デフォルトでは、サーバーは OPTIMIZE TABLE ステートメントをバイナリログに書き込み、それらがレプリケーションスレーブにレプリケートされるようにします。ロギングを抑制するには、オプションの NO_WRITE_TO_BINLOG キーワード、またはそのエイリアス LOCAL を指定します。

OPTIMIZE TABLE は、POINT カラム上の空間インデックスなどの R ツリーインデックスをソートしません。(Bug #23578)

OPTIMIZE TABLE テーブルは、古いファイルから新しく作成されたファイルへのテーブル統計のコピー中に発生したすべてのエラーをキャッチしてスローします。たとえば、.frm、.MYD、または .MYI ファイルの所有者のユーザー ID が mysqld プロセスのユーザー ID と異なる場合は、mysqld が root ユーザーによって起動されていないかぎり、OPTIMIZE TABLE は "cannot change ownership of the file" エラーを生成します。