重建表优化空间与查询速度

当数据库运行一段时间后，服务器存储空间会越来越少。

此时我们仅仅删除数据是不够的。需要重建表才能减少表大小。

这是由于 InnoDB 里的数据都是用 B+ 树的结构组织的。删除某一个记录时候，仅仅标志为删除，后续可以复用。甚至来说，我们使用 delete 命令把整个表的数据删除时候，也不会增大磁盘的可用空间，仅仅会把所有的可用页面标志为可复用。

也就是说，通过 delete 命令是不能回收表空间的。而回收表空间需要对表进行重建。

而在 MySQL 5.6 之前的版本重建表需要执行下面语句。以下语句会先创建一张新表，不断的导入数据，最终删除原表，修改新的表名。但该语句执行过程中无法在进行表的写入。

alter table tbl_name engine=InnoDB

之后的版本开始引入的 Online DDL，可以在在线执行表的重建，原理是在拷贝的期间生成一个日志文件，方便拷贝完成后进行重放操作。

但还是要注意如果是在业务高峰的时候执行盖操作，很可能会造成日志过大，超过 innodb_online_alter_log_max_size 的限制。

语句如下所示：

ALTER TABLE tbl_name FORCE, ALGORITHM=INPLACE, LOCK=NONE;

使用 ALGORITHM=COPY 则是 MySQL 5.6 之前的版本对应的操作。

需要注意的是： 无论是 INPLACE 还是 COPY 都需要更多的空间。所以我们应该尽量在早期进行表的优化，否则到了存储空间稀缺的情况，我们就没办法在进行表的重建了。

表重建也会增快表的查询速度，这是由于数据库“空洞“多了，要扫描的页越来越多导致的。

注意：单纯增加字段是不会导致锁表的，但是如果更改字段类型是会导致锁表的

如果表本省没有“空洞”，使用表重建反而会把表变大。

• 在重建表的时，InnoDB 不会把整张表占满，每个页留了 1/16 给后续的更新用。
• 在DDL期间，如果刚好有外部的DML在执行，这期间可能会引入一些新的空洞。

如果需要较为安全的操作，可以学习使用 github 开发的 gh-ost 。