MYSQL-45讲之为什么表数据删掉一半，表文件大小不变？

innodb表数据即可以存在共享表空间里，也可以是单独的文件。这个行为由参数innodb_file_per_table控制：

1.这个参数设置为off表示，表的数据放在系统共享表空间，也就是跟数据字典放在一起；

2.这个参数设置为on表示，每个innodb表数据存储在一个以.ibd为后缀的文件中。

从mysql5.6.6开始，它的默认值是on了。innodb表结构是存在.frm为后缀的文件里。而mysql8.0版本，则已经允许吧表结构定义放在系统数据表中了。

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

我们接下来讨论的都是基于将 innodb_file_per_table 设置为on展开的。

当我们删除的是数据表中的某些行时，如我们要删掉R4这个记录，innodb只会把R4这个记录标记为删除。如果之后要再插入一个ID在300到600之间（原始id前后两个id之间）的记录时,可能会复用这个位置。但是磁盘文件的大小并不会缩小。

innodb是按页存储的，如果我们删除了一个数据也上的所有记录，整个数据页就会被标记为可复用。与记录id复用不同的是，这个数据页可以被复用到任何位置。

进一步地，如果我们用delete命令把整个表的额数据删除，则所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小。

实际上，不止是删除数据会造成空洞，插入数据也会。

如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。新插入的id及大于改id的所有值都会被分配到一个新数据页，这就可能造成原来的数据页不止1个记录的位置是空洞。

另外，更新索引上的值，可以理解为删除一个旧的值，在插入一个新的值。这也会造成空洞。

也就是说，经过大量增删改的表，都是可能存在空洞的。所以如果能把这些空洞去掉，就能打到收缩表空间的目的。

而重建表，就可以达到这样的目的。

这里，可以使用 alter table A engine=InnoDB 命令来重建表。在mysql5.5版本之前，这个DDL不是Online的。因此在整个DDL过程中，表A不能有更新。

在mysql5.6版本开始引入的Online DDL，对这个操作流程做了优化。其中，alter语句在启动的时候需要获取DML写锁,但是这个写锁在真正拷贝数据之前就退化成毒锁了。为了实现Online，DML毒锁不会阻塞增删改操作。而增加读锁是为了保护自己，禁止其它现成对这个表同时做DDL。

上述的这些重建方法都会扫描元彪数据和构建临时文件。对于很大的表来说，这个操作是很消耗IO和CPU资源的。因此，如果是线上服务，需要很小心的控制操作时间,可以考虑在业务低峰期使用。如果想要比较安全的操作的话，推荐使用GitHub开源的gh-ost来做。

注意：在重建表的时候，，InnoDB不会把整张表占满，每个数据页留了1/16给后续的更新用。也就是说，其实重建表之后不是最紧凑的。

online和inplace之前的关系：

1.DDL过程如果是Online的，就一定是inplace的；

2.反过来未必，也就是说inplace的DDL,有可能不是Online的。截止到mysql8.0,添加全文索引（FULLTEXT index）和空间索引（SPATIAL index）就属于这种情况。如，

alter table t add FULLTEXT(field_name);

这个过程是inplace的，但会阻塞增删改操作，是非Online的。

三种重建表的方式及区别。

从mysql5.6版本开始，alter table t engine=InnoDB (也就是recreate)默认的就是上面介绍的流程了；

analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了DML读锁；

optimize table t 等于recreate + analyze。