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
は次のように機能します。
テーブルが行を削除または分割した場合は、そのテーブルを修復します。
インデックスページがソートされていない場合は、それをソートします。
テーブルの統計が最新でない (そのため、インデックスのソートによって修復を実行できない) 場合は、それを更新します。
その他の考慮事項
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" エラーを生成します。