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 使用的存储引擎

在这些情况下使用OPTIMIZE TABLE,具体取决于 table 的类型:

  • 在具有自己的.ibd 文件InnoDB table 上执行实质 insert,更新或删除操作后,因为它是在启用了innodb_file_pertable选项的情况下创建的。重新组织 table 和索引,并且可以回收磁盘空间以供操作系统使用。

  • 在对InnoDB table 中的FULLTEXT索引的一部分的列执行实质 insert,更新或删除操作之后。首先设置 configuration 选项innodb_optimize_fulltext_only=1。要将索引维护期保持在合理的 time,请设置innodb_ft_num_word_optimize选项以指定要在搜索索引中更新的单词数,并运行OPTIMIZE TABLE statements 序列,直到搜索索引完全更新为止。

  • 删除MyISAMARCHIVE table 的大部分内容,或者对 variable-length 行(具有VARCHARVARBINARYBLOB文本列的表)进行MyISAMARCHIVE table 的许多更改。删除的行在链表中维护,随后的插入操作重用旧的行位置。您可以使用OPTIMIZE TABLE来回收未使用的空间并对数据文件进行碎片整理。在对 table 进行大量更改后,此语句还可以改善使用 table 的 statements 的 performance,有时甚至是显着的。

此语句需要 table 的选择插入权限。

OPTIMIZE TABLE适用于InnoDBMyISAM 数据ARCHIVE表。 in-memory 导航台表的动态列也支持OPTIMIZE TABLE。它不适用于 in-memory 列的 fixed-width 列,也不适用于磁盘数据表。可以使用--ndb-optimization-delay调整OPTIMIZE在 NDB Cluster 表上的_per_,它控制 time处理批处理之间等待的 time 长度。有关更多信息,请参阅以前在 NDB 中解决的 NDB Cluster 问题 Cluster 7.3

对于 NDB Cluster 表,可以通过(for example)中断执行OPTIMIZE操作的 SQL 线程来中断OPTIMIZE TABLE

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

此语句不适用于视图。

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

默认情况下,服务器将OPTIMIZE TABLE statements 写入二进制 log,以便它们复制到复制从属服务器。要禁止 logging,请指定可选的NO_WRITE_TO_BINLOG关键字或其别名LOCAL

OPTIMIZE TABLE 输出

OPTIMIZE TABLE返回结果集,其中包含以下 table 中显示的列。

Tabletable name
Op总是optimize
Msg_typestatuserrorinfonotewarning
Msg_text信息性消息

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

InnoDB 详细信息

对于InnoDB表,OPTIMIZE TABLE映射到ALTER TABLE ... FORCE,它重建 table 以更新索引统计信息并释放聚簇索引中未使用的空间。当您在InnoDB 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对常规和分区InnoDB表使用在线 DDL,这减少了并发 DML 操作的停机时间。由OPTIMIZE TABLE触发并在ALTER TABLE ... FORCE的掩护下执行的 table 重建已完成。只有在操作的准备阶段和提交阶段才会进行独占 table 锁定。在准备阶段,更新元数据并创建中间 table。在提交阶段,提交 table 元数据更改。

OPTIMIZE TABLE在以下条件下使用 table 复制方法重建 table:

对于包含FULLTEXT索引的InnoDB表,不支持OPTIMIZE TABLE使用在线 DDL。而是使用 table 复制方法。

InnoDB 使用 page-allocation 方法存储数据,并且不会像 legacy 存储引擎(例如MyISAM)那样受到碎片的影响。在考虑是否进行 run 优化时,请考虑服务器将处理的 transactions 的工作负载:

MyISAM 详情

对于MyISAM表,OPTIMIZE TABLE的工作原理如下:

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

  • 如果索引页未排序,请对它们进行排序。

  • 如果 table 的统计信息不符合 date(并且无法通过对索引进行排序来完成修复),请更新它们。

其他考虑因素

OPTIMIZE TABLE在线执行常规和分区InnoDB表。否则,time OPTIMIZE TABLE期间的 MySQL 锁定 table是 running。

OPTIMIZE TABLE不对 R-tree 索引进行排序,例如POINT列上的空间索引。 (缺陷号 23578)

Updated at: 9 months ago
CHECKSUM TABLE 语法Table of contentREPAIR TABLE 语法