MySQL :: MySQL 8.4 参考手册 :: 15.7.3.4 OPTIMIZE TABLE 语句

版本 8.4

8.0 日语

MySQL 8.4 参考手册 / ... / OPTIMIZE TABLE 语句

15.7.3.4 OPTIMIZE TABLE 语句

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

OPTIMIZE TABLE 重新组织表数据和关联索引数据的物理存储，以减少存储空间并在访问表时提高 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 表（具有 VARCHAR、VARBINARY、BLOB 或 TEXT 列的表）进行大量更改后。删除的行将保留在链表中，后续的 INSERT 操作将重复使用旧的行位置。您可以使用 OPTIMIZE TABLE 来回收未使用的空间并对数据文件进行碎片整理。在对表进行大量更改后，此语句还可以提高使用该表的语句的性能，有时会显著提高。

此语句需要对该表具有 SELECT 和 INSERT 权限。

OPTIMIZE TABLE 适用于 InnoDB、MyISAM 和 ARCHIVE 表。OPTIMIZE TABLE 还支持内存中 NDB 表的动态列。它不适用于内存中表的固定宽度列，也不适用于磁盘数据表。可以使用 --ndb-optimization-delay 来调整 OPTIMIZE 在 NDB 集群表上的性能，该选项控制 OPTIMIZE TABLE 处理行批次之间等待的时间长度。有关更多信息，请参阅第 25.2.7.11 节，“NDB 集群 8.4 中已解决的先前 NDB 集群问题”。

对于 NDB 集群表，可以通过（例如）终止执行 OPTIMIZE 操作的 SQL 线程来中断 OPTIMIZE TABLE。

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

此语句不适用于视图。

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

默认情况下，服务器会将 OPTIMIZE TABLE 语句写入二进制日志，以便它们复制到副本。要禁止日志记录，请指定可选的 NO_WRITE_TO_BINLOG 关键字或其别名 LOCAL。您必须具有 OPTIMIZE_LOCAL_TABLE 权限才能使用此选项。

OPTIMIZE TABLE 输出

OPTIMIZE TABLE 返回一个结果集，其中包含下表所示的列。

列	值
`表`	表名
`操作`	始终为 `optimize`
`消息类型`	`status`、`error`、`info`、`note` 或 `warning`
`消息文本`	一条信息性消息

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

InnoDB 详细信息

对于 InnoDB 表，OPTIMIZE TABLE 映射到 ALTER 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                                                                |
+----------+----------+----------+-------------------------------------------------------------------+

OPTIMIZE TABLE 对常规和分区 InnoDB 表使用联机 DDL，这减少了并发 DML 操作的停机时间。由 OPTIMIZE TABLE 触发的表重建将在原位完成。仅在操作的准备阶段和提交阶段才会短暂获取独占表锁。在准备阶段，将更新元数据并创建一个中间表。在提交阶段，将提交表元数据更改。

在以下情况下，OPTIMIZE TABLE 使用表复制方法重建表

启用 old_alter_table 系统变量时。
使用 --skip-new 选项启动服务器时。

对于包含 FULLTEXT 索引的 InnoDB 表，不支持使用联机 DDL 的 OPTIMIZE TABLE。而是使用表复制方法。

InnoDB 使用页面分配方法存储数据，并且不会像旧式存储引擎（例如 MyISAM）那样出现碎片化问题。在考虑是否运行优化时，请考虑服务器预期要处理的事务的工作负载

预计会出现一定程度的碎片。为了留出更新空间而无需拆分页面，InnoDB 仅填充 93% 的页面。
删除操作可能会留下间隙，从而导致页面的填充率低于预期，这可能使得优化表变得 worthwhile。
当有足够的空间可用时，对行的更新通常会在同一页面内重写数据，具体取决于数据类型和行格式。请参阅第 17.9.1.5 节，“压缩如何作用于 InnoDB 表” 和第 17.10 节，“InnoDB 行格式”。
随着时间的推移，高并发工作负载可能会在索引中留下间隙，因为 InnoDB 通过其 MVCC 机制保留相同数据的多个版本。请参阅第 17.3 节，“InnoDB 多版本控制”。

MyISAM 详细信息

对于 MyISAM 表，OPTIMIZE TABLE 的工作方式如下

如果表中有已删除或已拆分的行，请修复该表。
如果索引页未排序，请对其进行排序。
如果表的统计信息不是最新的（并且无法通过对索引进行排序来完成修复），请更新它们。

其他注意事项

对于常规和分区 InnoDB 表，OPTIMIZE TABLE 是联机执行的。否则，MySQL 会在 OPTIMIZE TABLE 运行期间锁定表。

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