Movatterモバイル変換

• After doing substantial insert, update, or delete operations on anInnoDB table that has its own.ibd file because it was created with theinnodb_file_per_table option enabled. The table and indexes are reorganized, and disk space can be reclaimed for use by the operating system.

• After doing substantial insert, update, or delete operations on columns that are part of aFULLTEXT index in anInnoDB table. Set the configuration optioninnodb_optimize_fulltext_only=1 first. To keep the index maintenance period to a reasonable time, set theinnodb_ft_num_word_optimize option to specify how many words to update in the search index, and run a sequence ofOPTIMIZE TABLE statements until the search index is fully updated.

• After deleting a large part of aMyISAM orARCHIVE table, or making many changes to aMyISAM orARCHIVEtable with variable-length rows (tables that haveVARCHAR,VARBINARY,BLOB, orTEXT columns). Deleted rows are maintained in a linked list and subsequentINSERT operations reuse old row positions. You can useOPTIMIZE TABLE to reclaim the unused space and to defragment the data file. After extensive changes to a table, this statement may also improve performance of statements that use the table, sometimes significantly.

• OPTIMIZE TABLE Output

• InnoDB Details

• MyISAM Details

• Other Considerations

OPTIMIZE TABLE returns a result set with the columns shown in the following table.

OPTIMIZE TABLE table catches and throws any errors that occur while copying table statistics from the old file to the newly created file. For example. if the user ID of the owner of the.MYD or.MYI file is different from the user ID of themysqld process,OPTIMIZE TABLE generates a "cannot change ownership of the file" error unlessmysqld is started by theroot user.

ForInnoDB tables,OPTIMIZE TABLE is mapped toALTER TABLE ... FORCE, which rebuilds the table to update index statistics and free unused space in the clustered index. This is displayed in the output ofOPTIMIZE TABLE when you run it on anInnoDB table, as shown here:

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                                                                |+----------+----------+----------+-------------------------------------------------------------------+

OPTIMIZE TABLE usesonline DDL for regular and partitionedInnoDB tables, which reduces downtime for concurrent DML operations. The table rebuild triggered byOPTIMIZE TABLE is completed in place. An exclusive table lock is only taken briefly during the prepare phase and the commit phase of the operation. During the prepare phase, metadata is updated and an intermediate table is created. During the commit phase, table metadata changes are committed.

OPTIMIZE TABLE rebuilds the table using the table copy method under the following conditions:

OPTIMIZE TABLE usingonline DDL is not supported forInnoDB tables that containFULLTEXT indexes. The table copy method is used instead.

InnoDB stores data using a page-allocation method and does not suffer from fragmentation in the same way that legacy storage engines (such asMyISAM) do. When considering whether or not to run optimize, consider the workload of transactions that your server is expected to process:

ForMyISAM tables,OPTIMIZE TABLE works as follows:

OPTIMIZE TABLE is performed online for regular and partitionedInnoDB tables. Otherwise, MySQLlocks the table during the timeOPTIMIZE TABLE is running.

OPTIMIZE TABLE does not sort R-tree indexes, such as spatial indexes onPOINT columns. (Bug #23578)