MySQL里的回收表空间

前言

MySQL里面有好几种回收表空间的方法，但是他们之间的异同是什么呢？非常感谢提问问题的同学，我在这里总结下。

回收表空间

有三种方法：

1. OPTIMIZE TABLE tbname
2. ALTER TABLE ... FORCE
3. ALTER TABLE tbname ENGINE= INNODB

OPTIMIZE TABLE的支持表类型：INNODB,MYISAM, ARCHIVE，NDB；会重组表数据和索引的物理页，对于减少所占空间和在访问表时优化IO有效果。
在InnoDB表中等价 ALTER TABLE ... FORCE，ALTER TABLE ENGINE= INNODB,会重新整理在聚簇索引上的数据和索引；
这意味着，我们在内部创建一个新的表，就像现有的表一样，然后我们将数据一次一行地加载到一行中。对于聚集索引(又称主键)，如果数据被插入到顺序之外，或者如果已经进行了修改，这就造成了一些影响填充因素的间隙，可能会导致一些空间浪费。
对于二级索引，它们将按聚集索引顺序依次加载一行，这可能导致他们直接回到支离破碎的状态。而且InnoDB的MVCC实现在二级索引中确实有多个版本，因此可能在这里回收空间。

在MySQL 5.5中，InnoDB引入了一个称为“快速索引创建”的特性，它可以通过先造数据再创建索引来更优化地创建这些二级索引。然而，在官方的Oracle MySQL版本中，这个特性并没有被绑定到优化表中。可以查看BUG# 57583。
对于InnoDB和分区表， 在MySQL 5.6.17之前，OPTIMIZE TABLE在拷贝表的过程中,在SQL层会锁表，执行时DML会阻塞；在MySQL 5.6.4之后在执行OPTIMIZE TABLE时可以同时执行DML。
阿里云的RDS 5.6已经支持Online OPTIMIZE.
对于MYISAM表，OPTIMIZE TABLE非常重要：

1. 如果表已删除或分隔行，就修复该表。
2. 如果索引页没有排序，就排序它们。
3. 如果表的统计信息不是最新的(而且修复不能通过对索引进行排序)，就更新它们。

注：
需要有足够的空间才能进行OPTIMIZE TABLE。如果没有磁盘空间，MySQL将不能进行优化，表也无法使用。

参考

https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html
https://mysqlserverteam.com/mysql-5-6-17-improved-online-optimize-table-for-innodb-and-partitioned-innodb-tables/
http://www.tocker.ca/2013/05/02/optimize-check-repair-analyze-table-innodb-edition.html