Mysql OPTIMIZE TABLE

13.7.2.4 OPTIMIZE TABLE Syntax

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

OPTIMIZE TABLE reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. The exact changes made to each table depend on the storage engine used by that table.

optimize table 重新组织表数据和相关索引数据的物理存储，以减少存储空间，提高访问表时的I/O效率。对每个表所做的确切更改取决于该表使用的存储引擎。

 

Use OPTIMIZE TABLE in these cases, depending on the type of table:

在这些情况下，根据表的类型使用“optimize table”：

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

在一个innodb表上操作大量的增删改操作，这个表有自己的.ibd文件，因为innodb_file_per_table选择被启用了。表和索引被重新组织，磁盘空间可以被回收以供操作系统使用。

 

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

在对一个innodb表的全文索引列做大量的增删改操作后。首先设置配置选项innodb_optimize_fulltext_only=1。要使索引维护周期保持在合理的时间内，请设置innodb_ft_num_word_optimize选项以指定搜索索引中要更新的单词数，并运行一系列optimize TABLE语句，直到搜索索引完全更新。

 

• After deleting a large part of a MyISAM or ARCHIVE table, or making many changes to a MyISAM or ARCHIVE table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE 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.

删除MyISAM或存档表的大部分，或对具有可变长度行（具有VARCHAR、VARBINARY、BLOB或TEXT列的表）的MyISAM或存档表进行多次更改之后。删除的行保存在链表中，后续的插入操作重用旧的行位置。可以使用“OPTIMIZE TABLE”回收未使用的空间并对数据文件进行碎片整理。在对表进行了大量更改之后，此语句还可以提高使用该表的语句的性能，有时会显著提高。

 

This statement requires SELECT and INSERT privileges for the table.

此语句需要表的SELECT和INSERT权限。（指optimize table语句）

 

OPTIMIZE TABLE works for InnoDB, MyISAM, and ARCHIVE tables. OPTIMIZE TABLE is also supported for dynamic columns of in-memory NDB tables. It does not work for fixed-width columns of in-memory tables, nor does it work for Disk Data tables. The performance of OPTIMIZE on NDB Cluster tables can be tuned using --ndb-optimization-delay, which controls the length of time to wait between processing batches of rows by OPTIMIZE TABLE. For more information, see Previous NDB Cluster Issues Resolved in NDB Cluster 7.3.

optimize table被用于innodb、myisam和archive三种存储引擎的表。同样也支持基于动态列的内存NDB表。它不适用于内存表的固定宽度列，也不适用于磁盘数据表。可以使用--ndb-optimization-delay来优化NDB集群表上的OPTIMIZE性能，它通过OPTIMIZE TABLE控制处理批处理行之间的等待时间长度。For more information, see Previous NDB Cluster Issues Resolved in NDB Cluster 7.3.

 

For NDB Cluster tables, OPTIMIZE TABLE can be interrupted by (for example) killing the SQL thread performing the OPTIMIZE operation.

对于NDB Cluster表，可以通过终止SQL线程来中断来执行优化操作。

 

By default, OPTIMIZE TABLE does not work for tables created using any other storage engine and returns a result indicating this lack of support. You can make OPTIMIZE TABLE work for other storage engines by starting mysqld with the --skip-new option. In this case, OPTIMIZE TABLE is just mapped to ALTER TABLE.

默认情况下，OPTIMIZE TABLE不适用于使用任何其他存储引擎创建的表，并返回一个结果，表明缺少支持。通过使用--skip-new选项启动mysqld，可以使优化表对其他存储引擎起作用。在这种情况下，OPTIMIZE TABLE只是映射到ALTER TABLE。

 

This statement does not work with views.

此语句不适用于视图。

 

OPTIMIZE TABLE is supported for partitioned tables. For information about using this statement with partitioned tables and table partitions, see Section 22.3.4, “Maintenance of Partitions”.

分区表支持OPTIMIZE TABLE。有关将此语句用于分区表和表分区的信息，请参阅第22.3.4节“分区的维护”。

 

By default, the server writes OPTIMIZE TABLE statements to the binary log so that they replicate to replication slaves. To suppress logging, specify the optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL.

默认情况下，服务器将OPTIMIZE TABLE语句写入二进制日志，以便它们复制到复制从库。要禁止日志记录，请指定可选的NO_WRITE_TO_BINLOG关键字或其别名LOCAL。

 

OPTIMIZE TABLE Output

 

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

OPTIMIZE 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 .frm, .MYD, or .MYI file is different from the user ID of the mysqld process, OPTIMIZE TABLE generates a "cannot change ownership of the file" error unless mysqld is started by the root user.

OPTIMIZE TABLE TABLE捕获并抛出将表统计信息从旧文件复制到新创建的文件时发生的任何错误。例如。如果.frm、.MYD的所有者的用户ID，或.MYI文件与mysqld进程的用户ID不同，OPTIMIZE table 生成一个“cannot change ownership of the file“错误，除非mysqld由根用户启动。

 

InnoDB Details

For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE ... FORCE, which rebuilds the table to update index statistics and free unused space in the clustered index. This is displayed in the output of OPTIMIZE TABLE when you run it on an InnoDB table, as shown here:

对于InnoDB表，OPTIMIZE TABLE映射到ALTER TABLE。。。FORCE，它重建表以更新索引统计信息并释放聚集索引中未使用的空间。这将显示在InnoDB表上运行OPTIMIZE表时的输出，如下所示：

 

OPTIMIZE TABLE uses online DDL for regular and partitioned InnoDB tables, which reduces downtime for concurrent DML operations. The table rebuild triggered by OPTIMIZE TABLE and performed under the cover by ALTER TABLE ... FORCE 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对常规和分区的InnoDB表使用在线DDL，这减少了并发DML操作的停机时间。由OPTIMIZE table触发的表重建，由ALTER TABLE ... FORCE 在后台执行。独占表锁仅在操作的准备阶段和提交阶段短暂使用。在准备阶段，更新元数据并创建中间表。在提交阶段，表元数据更改被提交。

 

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

优化表在以下条件下使用table copy method重建表：

• When the old_alter_table system variable is enabled.

•当启用old_alter_table系统变量时。

• When the mysqld --skip-new option is enabled

•当启用--skip-new选项时启动mysql时。

 

OPTIMIZE TABLE using online DDL is not supported for InnoDB tables that contain FULLTEXT indexes. The table copy method is used instead.

对于包含全文索引的InnoDB表，不支持使用在线DDL优化表。改为使用表复制方法。

 

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

InnoDB使用page-allocation方法存储数据，不会像传统存储引擎（如MyISAM）那样出现碎片。在考虑是否运行优化时，请考虑服务器将处理的事务的工作负载：

• Some level of fragmentation is expected. InnoDB only fills pages 93% full, to leave room for updates without having to split pages.

•预计会出现一定程度的碎片。InnoDB只填充93%的页面，为更新留出空间，而不必拆分页。

• Delete operations might leave gaps that leave pages less filled than desired, which could make it worthwhile to optimize the table.

•删除操作可能会留下gaps，使页面的填充量低于预期，这可能会使优化表变得有价值。

• Updates to rows usually rewrite the data within the same page, depending on the data type and row format, when sufficient space is available. See Section 14.9.1.5, “How Compression Works for InnoDB Tables” and Section 14.11, “InnoDB Row Formats”.

•更新行通常会重写同一页中的数据，具体取决于数据类型和行格式，前提是有足够的空间可用。见第14.9.1.5节，“压缩工作原理”“InnoDB表”和第14.11节“InnoDB行格式”。

• High-concurrency workloads might leave gaps in indexes over time, as InnoDB retains multiple versions of the same data due through its MVCC mechanism. See Section 14.3, “InnoDB Multi-Versioning”.

•随着时间的推移，高并发性工作负载可能会在索引中留下gaps，因为InnoDB通过其MVCC机制保留同一数据的多个版本。见第14.3节，“InnoDB Multi-版本控制”。

 

 

MyISAM Details

For MyISAM tables, OPTIMIZE TABLE works as follows:
1. If the table has deleted or split rows, repair the table.
2. If the index pages are not sorted, sort them.
3. If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.

如果表的统计信息不是最新的（并且无法通过排序索引来完成修复），请更新它们。

 

Other Considerations

OPTIMIZE TABLE is performed online for regular and partitioned InnoDB tables. Otherwise, MySQL
locks the table during the time OPTIMIZE TABLE is running.

定期在线执行optimize table和分区InnoDB表。因为，MySQL在OPTIMIZE table运行期间锁定表。

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

optimize table不会排序R-tree索引，例如空间在point列上。(Bug#23578)

 

完