MySql——表的空间回收

表的存放

一个InnoDB表包含两个部分，即表结构定义和数据，表结构是存在以.frm为后缀的文件中的，在MySql8.0版本，则已经允许把表结构定义放在系统数据表中。
参数innodb_file_per_table：
1. 设置为off表示表的数据放在系统共享表空间，即跟数据字典放在一起
2. 设置为on表示每个innodb表数据存储在一个以.ibd为后缀的文件中，从5.6.6版本开始，就默认为on了。
设置为on的好处是，一个表单独存储为一个文件更容易管理，而如果放在共享表空间，即使表删掉了，空间也是不会回收的。

数据删除流程

假设，我们删除掉R4这个记录，InnoDB会把R4这个记录标记为删除，如果后面要再插入一个ID在300和600之间的记录时，可能就会服用这个位置，但磁盘文件的大小并不会缩小。
如果删除了一个数据页或者把整个表的数据都删除呢？答案是数据页或者所有的数据页会被标记为删除，比如把pageA删除，pageA会被标记为复用，当要插入一条ID=50的记录需要用到新页时，pageA可以被复用。
如果相邻两个数据页的利用率很小，系统就会把这两个页上的数据和到其中一个页上，另一个数据页就会被标记为可复用。
而这种标记为可复用的方式并不会使数据文件变小，所以delete命令是不能回收表空间的。实际上，不止是删除数据会造成这种情况，插入数据也会。

数据插入流程

如果数据是按照索引递增顺序插入的，那么索引是紧凑的，但如果数据是随机插入的可能就会造成索引的数据页分裂（由于MySql的数据页的固定大小是16KB）。
如果pageA已经满了，在插入一条数据就会出现如下情况：
这时就会申请一个新的页面pageB，但发生了数据页的分裂，导致pageA留下了数据空洞。
总之，经过大量的增删改的表，都是可能存在数据空洞的，如果能把这些空洞去掉，就能达到收缩表空间的目的。而重建表就可以达到这种目的。

重建表

假如，你现在有一个表A，为了把表中存在的空洞去掉，你可以新建一个与表A结构相同的表B，然后按照主键ID递增的顺序，把数据一行行地从表A里读出来再插入到表B中。这样表B地主键索引会更紧凑，数据页利用率也比较高。可以使用alter table A engine=InnoDB命令来重建表。
但是，在整个DDL地过程中，表A中不能有更新，不然就会导致数据的丢失，也就是说，这个DDL不是online的。
而在MySql5.6版本开始引入了OnlineDDL，对重建表这个过程做了优化，过程如下：
1. 建立一个临时文件，扫描表 A 主键的所有数据页；
2. 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
3. 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中
4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件
5. 用临时文件替换表 A 的数据文件。
这时就有一个疑惑，DDL 之前是要拿 MDL 写锁的，这样还能叫 Online DDL 吗？
确实，上图的流程中，alter 语句在启动的时候需要获取 MDL 写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。
为什么要退化呢？为了实现 Online，MDL 读锁不会阻塞增删改操作。那为什么不干脆直接解锁呢？为了保护自己，禁止其他线程对这个表同时做 DDL。
而对于一个大表来说，Online DDL 最耗时的过程就是拷贝数据到临时表的过程，这个步骤的执行期间可以接受增删改操作。所以，相对于整个 DDL 过程来说，锁的时间非常短。对业务来说，就可以认为是 Online 的。
需要补充说明的是，上述的这些重建方法都会扫描原表数据和构建临时文件。对于很大的表来说，这个操作是很消耗 IO 和 CPU 资源的。因此，如果是线上服务，你要很小心地控制操作时间。如果想要比较安全的操作的话，我推荐你使用 GitHub 开源的 gh-ost 来做。

Online和Inplace

说到 Online，我还要再和你澄清一下它和另一个跟 DDL 有关的、容易混淆的概念 inplace 的区别。
在重建表的第一张图中，我们把表 A 中的数据导出来的存放位置叫作 tmp_table。这是一个临时表，是在 server 层创建的。
在重建表的第二张图中，根据表 A 重建出来的数据是放在“tmp_file”里的，这个临时文件是 InnoDB 在内部创建出来的。整个 DDL 过程都在 InnoDB 内部完成。对于 server 层来说，没有把数据挪动到临时表，是一个“原地”操作，这就是“inplace”名称的来源。
所以，我现在问你，如果你有一个 1TB 的表，现在磁盘间是 1.2TB，能不能做一个 inplace 的 DDL 呢？答案是不能。因为，tmp_file 也是要占用临时空间的。
我们重建表的这个语句 alter table t engine=InnoDB，其实隐含的意思是：alter table t engine=innodb,ALGORITHM=inplace;
跟 inplace 对应的就是拷贝表的方式了，用法是：alter table t engine=innodb,ALGORITHM=copy;当你使用 ALGORITHM=copy 的时候，表示的是强制拷贝表，对应的流程就是重建表的第二张图的操作过程。