数据库表的空间回收

为什么简单地删除表数据达不到表空间回收的效果？

首先讲一下参数innodb_file_per_table，表数据既可以存在共享表空间里（调为OFF），也可以是单独的文件（调为ON）。

我建议你不论使用MySQL的哪个版本，都将这个值设置为ON。因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过drop table命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

InnoDB里的数据都是用B+树的结构组织的。如果删除数据，InnoDB引擎只会把这个记录标记为删除。可能会复用这个位置。但是，磁盘文件的大小并不会缩小。

现在，你已经知道了InnoDB的数据是按页存储的，那么如果我们删掉了一个数据页上的所有记录，会怎么样？

答案是：整个数据页就可以被复用了。

通过delete命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。实际上，不止是删除数据会造成空洞，插入数据也会。（随机插入会导致，尽量保证索引递增）

解决方法：

可以使用alter table A engine=InnoDB命令来重建表。在MySQL 5.5版本之前这个命令就是重新建表移植数据，如果有新的数据就会造成数据丢失。

而在MySQL 5.6版本开始引入的Online DDL，对这个操作流程做了优化。

Online DDL重建表的流程：

  1. 建立一个临时文件，扫描表A主键的所有数据页；
  2. 用数据页中表A的记录生成B+树，存储到临时文件中；
  3. 生成临时文件的过程中，将所有对A的操作记录在一个日志文件（row log）中，对应的是图中state2的状态；
  4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表A相同的数据文件，对应的就是图中state3的状态；
  5. 用临时文件替换表A的数据文件。

上述流程中，alter语句在启动的时候需要获取MDL写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。因为不会阻塞增删改操作并且为了保护自己，禁止其他线程对这个表同时做DDL。

Online 和 inplace

整个DDL过程都在InnoDB内部完成。对于server层来说，没有把数据挪动到临时表，是一个“原地”操作，这就是“inplace”名称的来源。

如果说这两个逻辑之间的关系是什么的话，可以概括为：

  1. DDL过程如果是Online的，就一定是inplace的；
  2. 反过来未必，也就是说inplace的DDL，有可能不是Online的。截止到MySQL 8.0，添加全文索引（FULLTEXT index）和空间索引(SPATIAL index)就属于这种情况。

optimize table、analyze table和alter table这三种方式重建表的区别：

  • 从MySQL 5.6版本开始，alter table t engine = InnoDB（也就是recreate）默认的就是上面图4的流程了；
  • analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了MDL读锁；
  • optimize table t 等于recreate+analyze。