语法
OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name [, tbl_name] ...
OPTIMIZE TABLE 重新组织表数据和关联索引数据的物理存储，以减少存储空间并提高访问表时的 I/O 效率。对每个表所做的确切更改取决于该表使用的存储引擎。
在以下情况下使用 OPTIMIZE TABLE，具体取决于表的类型：
-
在对具有自己的 .ibd 文件的 InnoDB 表执行大量插入、更新或删除操作后，因为该表是在启用了 innodb_file_per_table 选项的情况下创建的。表和索引被重新组织，并且磁盘空间可以被回收以供操作系统使用。
-
对 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 表。内存中 NDB 表的动态列也支持 OPTIMIZE TABLE。它不适用于内存表的固定宽度列，也不适用于磁盘数据表。 NDB Cluster 表上的 OPTIMIZE 性能可以使用 --ndb-optimization-delay 进行调整，它控制 OPTIMIZE TABLE 处理批量行之间等待的时间长度。
对于 NDB Cluster 表，可以通过（例如）终止执行 OPTIMIZE 操作的 SQL 线程来中断 OPTIMIZE TABLE。
默认情况下，OPTIMIZE TABLE 不适用于使用任何其他存储引擎创建的表，并返回指示缺乏支持的结果。您可以通过使用 --skip-new 选项启动 mysqld 来使 OPTIMIZE TABLE 适用于其他存储引擎。在这种情况下，OPTIMIZE TABLE 只是映射到 ALTER TABLE。
该语句不适用于视图。
分区表支持 OPTIMIZE TABLE。
默认情况下，服务器将 OPTIMIZE TABLE 语句写入二进制日志，以便它们复制到副本。要抑制日志记录，请指定可选的 NO_WRITE_TO_BINLOG 关键字或其别名 LOCAL。
优化表输出
OPTIMIZE TABLE 返回一个结果集，其中包含下表中所示的列。
OPTIMIZE TABLE 表捕获并抛出将表统计信息从旧文件复制到新创建的文件时发生的任何错误。例如。如果 .frm、.MYD 或 .MYI 文件所有者的用户 ID 与 mysqld 进程的用户 ID 不同，则 OPTIMIZE TABLE 会生成“cannot change ownership of the file”错误，除非 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%，以便为更新留出空间，而无需拆分页面。
-
删除操作可能会留下间隙，导致页面填充程度低于预期，这可能使得优化表变得值得。
-
当有足够的空间可用时，对行的更新通常会重写同一页中的数据，具体取决于数据类型和行格式。
-
随着时间的推移，高并发工作负载可能会在索引中留下间隙，因为 InnoDB 通过其 MVCC 机制保留同一数据的多个版本。
MyISAM 详细信息
对于 MyISAM 表，OPTIMIZE TABLE 的工作方式如下：
-
如果表已删除或拆分行，请修复该表。
-
如果索引页未排序，请对其进行排序。
-
如果表的统计信息不是最新的（并且无法通过对索引排序来完成修复），请更新它们。
其他考虑因素
OPTIMIZE TABLE 对常规 InnoDB 表和分区 InnoDB 表在线执行。否则，MySQL 在 OPTIMIZE TABLE 运行期间锁定表。
OPTIMIZE TABLE 不会对 R 树索引进行排序，例如 POINT 列上的空间索引。 （错误#23578）
引用
https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html
