Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.1Mb
EPUB - 7.5Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


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

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

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

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

OPTIMIZE TABLE は、InnoDBMyISAM、および 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 TABLEALTER 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 (INSERTUPDATEDELETE) が許可されません。また、主キーに現れる順序でキーが挿入されるため、セカンダリインデックスはそれほど効率的に作成されません。

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 statuserrorinfonote、または 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 と異なる場合は、mysqldroot ユーザーによって起動されていないかぎり、OPTIMIZE TABLE は "cannot change ownership of the file" エラーを生成します。


User Comments
  Posted by Dathan Pattishall on May 25, 2004
myisamchk --quick --check-only-changed --sort-index --analyze

do a myisamchk on the table.
--
notice the deleted blocks in the right hand corner of the dialog. The stat still indicates a number > 0 for tables with deleted blocks.

===
myisamchk -r --sort-index --analyze *.MYI fixes that number. I'm inclined to believe the myisamchk *.MYI number since the table I'm "optimizing" does get a lot of deletes.

ALTER TABLE [tbl_name] TYPE=innodb
- will OPTIMIZE an INNODB table in the table space as well

  Posted by Mihail Manolov on October 29, 2004
Don't forget to FLUSH TABLES after execution of any of the following - REPAIR TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, or ANALYZE TABLE on tables that are mapped into MERGE table.
  Posted by James Day on November 28, 2004
For InnoDB, if you have your tables in one tablespace, this will make a complete copy of the table within the tablespace, making the tablespace larger by the total table size less the free space you started with. It will not reduce the tablespace size. It can free fragmented space within a table to the tablespace, making that space available to other tables. If you're short of disk space and don't want to enlarge the tablespace you may be able to work around this by altering the table to MyISAM and then back to InnoDB.
  Posted by Jeff C on September 2, 2008
When using the InnoDB plugin with Barracuda+Compression, issue:

alter table your_table row_format=compressed; to rebuild the table.

  Posted by Ulrich Weiss on January 28, 2009
Also at MyISAM tables, the optimize needs a whole datafile of free hd space to free the not-used space in the file.
This means, that you need at least (database + data of biggest table) storage at the database directory (my case).

This may be very unfortunate, if you have (some, but) one very big table in your database, which needs almost all the storage... ;(

In this case, it is a very bad idea to optimize this table. Indeed, you can not optimize it, until the data of that big table is less then free space, which is very unlikely (This table must shrink to about eg. 1% its data).

Just tested: mysql will wait for space on the storage, if not enough is available for TABLE OPTIMIZE. Of course, this table will be locked all the time. I got out of this situation only by stopping the mysql server.

The only help I know, is to copy this table to another server with enough space, then optimized it there, and move this optimized table back (which must be done offline, because you have to remove the original first to get the space)
  Posted by Matthew Meyers on August 25, 2009
I wrote a quick dos script that seeks out fragmented tables and then runs optimize "in case anyone else can use it thought I would share". Used with MySQL 5.0.68 running on Server 2003.

:: Set Global Variables
set mysqlbin=D:\hyperic\mysql-5.0.68\bin
set mysqlhost=localhost
set mysqlport=3306
set mysqluser=*
set mysqlpw=*
set target_data_free=10

:: sql cmd to get a list of fragmented tables
set get_fragtables=%mysqlbin%\mysql -h%mysqlhost% -P%mysqlport% -u%mysqluser% -p%mysqlpw% --batch -Dinformation_schema --skip-column-names -e "SELECT table_schema, table_name FROM TABLES where table_schema not in ('information_schema','mysql') and data_free > %target_data_free%;"

:: loop all fragmented tables and optimize.
for /f "tokens=1,2* delims= " %%A in ('%get_fragtables%') do (
echo %mysqlbin%\mysql -h%mysqlhost% -P%mysqlport% -u%mysqluser% -p%mysqlpw% --batch -e "optimize table %%A.%%B;"
)

  Posted by Eelko de Vos on January 25, 2011
"Optimize table" locks the table, which made it impossible to use on the systems I worked with. We just couldn't wait for MySQL to clean up tens of tables which were huge (gigabytes) while the system was effectively down.

Thus I wrote my own "optimize table" perl-script: it builds the table from scratch while it still can be used by other scripts through a merge-table solution.

Of course there are a few cave-ats: see the explanation in the script. But for us this script turned out to be very very useful. We could clean tables while we could still use them as if nothing was happening.

Basically it sets up a new MyISAM merge-table and a copy of the old table, and then starts to fill a newly created clean table with all data from the old un-optimized table. At some point all data has been moved and the switch back is made: removal of the merge-table and renaming of the new and optimized table to the appropriate table-name.

Here's the link to the script:
http://www.vosoft.nl/perl/reduce_table.pl.txt

  Posted by Rodolfo Campos on February 27, 2011
I've shared an script here for "automated" InnoDB tables optimization:

http://camposer-techie.blogspot.com/2011/02/optimizando-tablas-innodb-en-mysql.html

Hope you find it useful (it's in spanish).
Sign Up Login You must be logged in to post a comment.