13.7.2.4 OPTIMIZE TABLE 语句

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

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

在这种情况下，请根据 table 的类型使用OPTIMIZE TABLE：

• 对具有自己的.ibd file的InnoDBtable 执行大量的插入，更新或删除操作之后，因为它是在启用了innodb_file_per_table选项的情况下创建的。重组 table 和索引，并可以回收磁盘空间以供 os 使用。

• 对InnoDBtable 中FULLTEXT索引的一部分的列进行实质性的插入，更新或删除操作之后。首先设置配置选项innodb_optimize_fulltext_only=1。为了将索引维护期保持在合理的时间，请设置innodb_ft_num_word_optimize选项以指定要在搜索索引中更新的词数，然后运行OPTIMIZE TABLE语句序列，直到完全更新搜索索引。

• 删除MyISAM或ARCHIVEtable 的很大一部分，或对具有可变长度行(具有VARCHAR，VARBINARY，BLOB或TEXT列的 table)的MyISAM或ARCHIVEtable 进行许多更改之后。删除的行将保留在链接列 table 中，随后的INSERT操作将重用旧的行位置。您可以使用OPTIMIZE TABLE回收未使用的空间并对数据文件进行碎片整理。在对 table 进行大量更改之后，该语句还可以提高使用该 table 的语句的性能，有时甚至可以显着提高性能。

该语句要求 table 具有SELECT和INSERT特权。

OPTIMIZE TABLE适用于InnoDB，MyISAM和ARCHIVEtable。内存NDBtable 中的动态列也支持OPTIMIZE TABLE。它不适用于内存 table 中的固定宽度列，也不适用于磁盘数据 table。可以使用--ndb-optimization-delay调整OPTIMIZE在 NDB 群集 table 上的性能，该参数通过OPTIMIZE TABLE控制在处理一批行之间 await 的时间。有关更多信息，请参见NDB Cluster 8.0 中解决的以前的 NDB Cluster 问题。

对于 NDB 群集 table，可以通过(例如)杀死执行OPTIMIZE操作的 SQL 线程来中断OPTIMIZE TABLE。

默认情况下，OPTIMIZE TABLE不适用于使用任何其他存储引擎创建的 table，并返回指示缺少支持的结果。您可以通过使用--skip-new选项启动mysqld来使OPTIMIZE TABLE用于其他存储引擎。在这种情况下，OPTIMIZE TABLE仅 Map 到ALTER TABLE。

该语句不适用于视图。

分区 table 支持OPTIMIZE TABLE。有关将此语句与分区 table 和 table 分区一起使用的信息，请参见第 22.3.4 节“分区维护”。

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

• 优化 table 输出

• InnoDB Details

• MyISAM Details

• Other Considerations

OPTIMIZE TABLE 输出

OPTIMIZE TABLE返回具有下 table 所示列的结果集。

Column	Value
Table	table 名
Op	一律optimize
Msg_type	status，error，info，note或warning
Msg_text	信息性消息

OPTIMIZE TABLEtable 捕获并引发将 table 统计信息从旧文件复制到新创建的文件时发生的任何错误。例如。如果.frm，.MYD或.MYI文件的所有者的用户 ID 与mysqld进程的用户 ID 不同，则OPTIMIZE TABLE会生成“无法更改文件所有权”错误，除非root用户启动了mysqld。

InnoDB Details

对于InnoDBtable，OPTIMIZE TABLEMap 到ALTER TABLE ... FORCE，它将重建 table 以更新索引统计信息并释放聚集索引中的未使用空间。当在InnoDBtable 上运行OPTIMIZE TABLE时，它会显示在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                                                                |
+----------+----------+----------+-------------------------------------------------------------------+

OPTIMIZE TABLE将online DDL用于常规 table 和已分区的InnoDBtable，这减少了并发 DML 操作的停机时间。由OPTIMIZE TABLE触发并由ALTER TABLE ... FORCE进行隐藏的 table 重建已就位。排他 table 锁定仅在操作的准备阶段和提交阶段短暂进行。在准备阶段，将更新元数据并创建一个中间 table。在提交阶段，将提交 table 元数据更改。

OPTIMIZE TABLE在以下情况下使用 table 复制方法重建 table：

• 启用old_alter_table系统变量时。

• 使用--skip-new选项启动服务器时。

包含FULLTEXT索引的InnoDBtable 不支持OPTIMIZE TABLE使用online DDL。而是使用 table 复制方法。

InnoDB使用页面分配方法存储数据，并且不会像传统存储引擎(例如MyISAM)那样遭受碎片化的困扰。在考虑是否运行优化时，请考虑服务器将处理的事务的工作量：

• 预期会有某种程度的碎片。 InnoDB仅填满pages 93％的空间，为更新留出空间，而不必拆分页面。

• 删除操作可能会留下空白，从而使页面无法充满所需的空间，这可能值得优化 table。

• 当有足够的空间可用时，对行的更新通常会重写同一页中的数据，具体取决于数据类型和行格式。参见第 14.9.1.5 节“ InnoDBtable 的压缩方式”和第 14.11 节“ InnoDB 行格式”。

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

MyISAM Details

对于MyISAM个 table，OPTIMIZE TABLE的工作方式如下：

• 如果 table 已删除或拆分行，请修复该 table。

• 如果索引页未排序，请对其进行排序。

• 如果 table 的统计信息不是最新的(并且无法通过对索引进行排序来修复)，请对其进行更新。

Other Considerations

OPTIMIZE TABLE是针对常规和分区InnoDBtable 在线执行的。否则，在OPTIMIZE TABLE运行期间，MySQL 锁定桌子。

OPTIMIZE TABLE不对 R 树索引进行排序，例如POINT列上的空间索引。错误 23578)