MySQL回收表空间

MySQL在使用delete命令删除innodb表的数据时并不会释放磁盘空间

释放空间的方法有

1. OPTIMIZE TABLE tbname

2. ALTER TABLE tbname FORCE

3. ALTER TABLE tbname ENGINE= INNODB

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

对于InnoDB和分区表， 在MySQL 5.6.17之前，OPTIMIZE TABLE在拷贝表的过程中,在SQL层会锁表，执行时DML会阻塞；在MySQL 5.6.4之后在执行OPTIMIZE TABLE时可以同时执行DML。

对于MYISAM表，OPTIMIZE TABLE非常重要：

1. 如果表已删除或分隔行，就修复该表。

2. 如果索引页没有排序，就排序它们。

3. 如果表的统计信息不是最新的(而且修复不能通过对索引进行排序)，就更新它们。

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

innodb后台的purge_thread会定期清理已经删除的索引和文件。

官方文档

InnoDB does not physically remove a row from the database immediately when you delete it with an SQL statement. A row and its index records are only physically removed when InnoDB discards the undo log record written for the deletion. This removal operation, which only occurs after the row is no longer required for multi-version concurrency control (MVCC) or rollback, is called a purge.