使用 delete 删除数据不会使表空间变小
前文有提过，delete 只是将记录标记为删除，并没有真正删除。只有当其对应的 update undo logs 被清除时才会由后台 purge 任务物理删除，即没有事务再需要这些版本的记录时会执行物理删除。
即便是物理删除，也不会把磁盘空间返还给操作系统。究其原因，出于性能考虑，物理删除后的空间会被复用。删除记录后，当插入符合范围条件的数据时，原空间会被复用。删除一整个数据页上的所有记录后，当需要使用新页的时候，原数据页会被复用。
可通过 show table status like 't' 来查看表碎片大小，如果过大，可通过重建表来释放表空间。
几个字段的含义，都是针对 Innodb，MyISAM略有不同：
data_length：聚簇索引大小近似值，单位是字节。index_length：非聚簇索引大小近似值，单位是字节。data_free：已分配但未使用的空间大小近似值，单位是字节。表碎片大小即是查看这个字段。这三个值之和近似接近 ibd 文件大小。
重建表
不光删除数据会造成空洞(可被复用但没被使用的空间)，插入和更新也会。
因为插入数据往往都是随机的，即基本不可能按索引递增顺序插入，就很有可能造成数据页的分裂。比如当一个数据页满了，此时再插入一行数据到此节点就会造成数据页分裂，原数据页末尾就会产生空洞。
更新可理解为先删除后插入，同理。
重建表就是通过去掉这些空洞，来达到收缩表空间的目的。
命令如下：
1
alter table A engine=InnoDB
5.6版本之前
内部流程如下：
- 新建一张与原表结构相同的临时表
- 按主键ID递增的顺序，将数据一行一行的从原表读出来再插入到临时表中。这一步就可以去掉原表主键索引上的空洞。
- 用临时表替换掉原表
这个流程有个问题，在第2步中，原表不能有更新操作。也就是说，这个ddl不是online的。
从5.6版本开始
- 建立一个临时文件，扫描原表的所有数据页
- 用数据页中原表的记录生成B+树，存储到临时文件中
- 在生成临时文件的过程中，将对原表的操作记录在一个日志文件(row log)中
- 临时文件生成后，将日志文件中的操作应用到临时文件
- 用临时文件替换掉原表的数据文件
很明显，在该过程执行中是允许对原表做增删改操作的，这也是 Online DDL 名字的由来。
注意：对于大表来说，因为需要扫描原表数据和构建临时文件，这个步骤是很消耗IO和CPU资源的。尤其对于线上业务，要很小心的控制操作时间。如果想要安全操作的话，推荐使用 github 的 gh-ost。
online 和 inplace
online ddl 构建的临时文件位于 Innodb 内部，整个过程也都是在 Innodb 内部完成。对于 Server 层来说，没有把数据挪动到临时表(5.6版本之前的操作流程)，相当于是一个 “原地” 操作，因此叫做 inplace。
1
2
3
4
5
alter table A engine=InnoDB;
其实隐含的意思是
alter table A engine=InnoDB,ALGORITHM=inplace;
相对的，就有
1
alter table A engine=InnoDB,ALGORITHM=copy;
对应的就是5.6版本之前的操作流程。
所以，如果现在有一个1TB的表，磁盘空间为1.2TB，能不能做一个inplace的ddl呢？
答案是不能，因为临时文件也是要占用空间的。
总结来说，online 是指在操作过程会不会阻塞对原表的增删改操作。inplace 指的是在 Serve 层建临时表还是直接在存储引擎内建临时文件。
两种重建表的方式及区别
-
alter table A engine=InnoDB如上文所述
-
optimize table A等同于
alter table A engine=InnoDB+analyze table A(analyze：重新统计索引信息)
什么情况下重建表空间反而变大了？
-
表本身已经没有空洞了，比如刚刚重建过一次，这时候再重建，如果恰好在重建期间有外部的 DML 在执行，就有可能会引入新的空洞。
-
而且重建表的时候并不是把每个数据页都占满，而是会留下 $1/16$ 的预留空间给后续的更新用，也就是说重建后的表并不是百分百紧凑的。
以下过程就可能会出现这种情况：
- 重建一次
- 插入一部分数据，但是这部分数据使用的是预留空间
- 再重建一次。
这时由于预留空间被使用了，再次重建时就需要再额外留出 $1/16$ 的预留空间，所以空间反而变大了。
