当我们使用MySQL进行delete数据，空间文件ibd并没有减少。查询information_schema.TABLES表可知，DATA_LENGTH会有减少，INDEX_LENGTH不会减少，这就导致了总占用空间减少的并不明显。这些碎片不仅占用了磁盘空间，也会增加访问表时的 IO。MySQL 可以通过OPTIMIZE TABLE语句释放表空间，重组表数据和索引的物理页，减少表所占空间和优化读写性能。

OPTIMIZE TABLE

官方的解释：

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效率。对每个表所做的确切更改取决于该表使用的存储引擎。

• MySQL 官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可，可以写成定时任务来做，这是因为在 OPTIMIZE TABLE 运行过程中，MySQL 会锁定表 ，其本质是将当前表复制到临时表操作后再删除当前表，最后将临时表改名。

• OPTIMIZE TABLE 只对 MyISAM，Archive，InnoDB 和 NDB 表起作用，尤其是MyISAM表的作用最为明显, 对于 MyISAM，Archive 和 NDB，OPTIMIZE TABLE 被映射为 analyze table 操作。此外，并不是所有表都需要进行碎片整理，一般只需要对包含上述可变长度的文本数据类型的表和有大量删除记录操作的表进行整理。

• 默认情况下，直接对 InnoDB 引擎的数据表使用OPTIMIZE TABLE，可能会显示「 Table does not support optimize, doing recreate + analyze instead」的提示信息，但这并不代表 optimize 不支持 InnoDB 引擎，只是因为 optimize 对 InnoDB 引擎的表操作时，是分成两步的：

  optimize 对 InnoDB 引擎的表操作时无法作为 a single operation，实际的操作是：
  1：ALTER TABLE test ENGINE=InnoDB;
  2：ANALYZE TABLE test;
  注：MySQL5.7 已经推荐对于 InnoDB 的 table 使用 alter table table_name engine=innodb;语句的方式来进行表碎片优化，也就是说其效果与 OPTIMIZE TABLE test 一样了。

注意事项

因为OPTIMIZE TABLE的本质，是alter table，改表过程如下：

• 创建一张新的临时表

• 把旧表锁住，禁止插入删除，只允许读写（waiting for table metadata lock）。

• 把数据不断的从旧表，拷贝到新的临时表，(copy to tmp table)。

• 表拷贝完后，进行瞬间的 rename 操作。

• 旧表删除掉。

从过程上看：

1. OPTIMIZE TABLE 操作会导致表空间先增大（创建了临时表并拷贝数据到了临时表）后减小（删除了原表），如果数据库空间不足，会导致磁盘爆满引发异常。这个坑是切实踩过的坑 ~~~

2. OPTIMIZE TABLE过程伴随着锁表，在业务高峰期可能影响业务使用，建议在业务空闲期进行。

3. 在使用OPTIMIZE TABLE的时候，确保不要有任何DML语句，确保业务切走，否则可能会出事故。

4. 一般只需要对包含上述可变长度的文本数据类型的表和有大量删除记录操作的表进行整理，无需滥用。