面试高频：为什么数据删了，表空间不变呢？

比较喜欢的一段话：不经一番寒彻骨，怎得梅花扑鼻香，学习是枯燥的请大家坚持！ 阅读这篇文章大概需要20分钟！

大家好前面我们大概了解了为什么MySQL在查询数据的时候，有些时候会 "抖" 一下。以及分析了刷脏页的策略问题以及连坐机制。今天介绍一下为什么delete from表名，表的大小还是没有变小！

表结构存储

innodb表主要包含两个部分。一个部分是表结构的定义，另一部分是数据。在MySQL8.0之前表结构都是存在后缀 .fm 的文件里。而 MySQL 8.0 版本，则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小，所以我们今天主要讨论的是表数据。下列地址就是数据表数据的存储地方。

C:\Program Files\MySQL\MySQL Server 5.7\data

innodb_file_per_table

这个是MySQL的参数，用来控制表数据是存在共享表空间里，还是单独的文件。

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

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

我建议是还是存在一个文件里，这样便于管理。而且对性能的影响也不会很大。从 MySQL 5.6.6 版本开始，它的默认值就是 ON 了。而当你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

我们接下来的讨论话题也是由这里展开的。请把这里了解清楚！

数据删除流程

我们在删除整个表的时候，可以使用 drop table 命令回收表空间。但是，我们遇到的更多的删除数据的场景是删除某些行，这时就遇到了我们文章开头的问题：表中的数据被删除了，但是表空间却没有被回收。

我们要彻底搞明白这个问题的话，就要从数据删除流程说起了。

删除数据的操作不是直接删除磁盘IO上的数据。而且通过采用删除标记的方式删除数据。可以理解成断开引用。但是这里的断开引用不止是断开那么简单。断开了之后他不会释放空间，而是等下次插入数据的时候继续复用这块空间，这样就减少了开辟空间的消耗。

上面说到复用空间！这里的复用空间是有一个规则的。如下图，如果删除的那个数据是在300-700之间，并且插入的那个值的ID也是 300-700之间时，才会去复用这个空间，如果不是这个范围的就不会复用此空间。

下面介绍一下如何会复用任何空间。只有当用户删除了这一整页数据的时候，插入任何一个数据才会进行复用当前空间。

delete from user where 文件大小=4K

综上所述：删除数据只是把当前位置标记为可复用 但是磁盘大小是不会变的。也就是说通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是 “空洞”

实际上，不止删除会造成空洞现象，插入数据也会造成空间现象。这个是非常不好的。

借助一下丁奇老师画的图。如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。

假设下图中 page A 已经满了，这时我要再插入一行数据，会怎样呢？

可以看到，由于 page A 满了，再插入一个 ID 是 550 的数据时，就不得不再申请一个新的页面 page B 来保存数据了。页分裂完成后，page A 的末尾就留下了空洞（注意：实际上，可能不止 1 个记录的位置是空洞）。

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

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

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

重建表

你可以新建一个表A与表B使他们结构相同。然后按照主键 ID 递增的顺序，把数据一行一行地从表 A 里读出来再插入到表 B 中。

由于表 B 是新建的表，所以表 A 主键索引上的空洞，在表 B 中就都不存在了。显然地，表 B 的主键索引更紧凑，数据页的利用率也更高。如果我们把表 B 作为临时表，数据从表 A 导入表 B 的操作完成后，用表 B 替换 A，从效果上看，就起到了收缩表 A 空间的作用。

另一种方案也可以通过alter table A engine=InnoDB 命令来重建表。在 MySQL 5.5 版本之前，这个命令的执行流程跟我们前面描述的差不多，区别只是这个临时表 B 不需要你自己创建，MySQL 会自动完成转存数据、交换表名、删除旧表的操作。

重建表最耗时的地方不是新建表，而是把表A的数据转到表B的时候，会有一个数据插入的过程。这个过程是非常耗时的。而且插入过程中不能对表A进行任何新增修改删除操作。否则数据将会丢失或者乱掉。

因此可以这么说，MySQL5.5之前的重建表不是online()的。online是啥，我们下面继续介绍！

Online 和 Inplace

Inplace方式

这是原生MySQL 5.5，以及innodb_plugin中提供的方式。所谓Inplace，也就是在原表上直接进行，不会拷贝临时表。相对于Copy Table方式，这比较高效率。原表同样可读的，但是不可写。

Online方式

这是MySQL 5.6以上版本中提供的方式，也是今天我们重点说明的方式。无论是Copy Table方式，还是Inplace方式，原表只能允许读取，不可写。对应用有较大的限制，因此MySQL最新版本中，InnoDB支持了所谓的Online方式DDL。与以上两种方式相比，online方式支持DDL时不仅可以读，还可以写，对于dba来说，这是一个非常棒的改进。

online的核心就是，重建期间，可以通过row log达到在线online操作。如下图！图3 图4

Copy Table方式（了解即可）

这是InnoDB最早支持的方式。顾名思义，通过临时表拷贝的方式实现的。新建一个带有新结构的临时表，将原表数据全部拷贝到临时表，然后Rename，完成创建操作。这个方式过程中，原表是可读的，不可写。但是会消耗一倍的存储空间。

分析

我们先介绍一下online的流程吧

1. 建立一个临时文件，扫描表 A 主键的所有数据页；

2. 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；

3. 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；

4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态；

5. 用临时文件替换表 A 的数据文件。

图 3 改锁表 DDL

图 4 Online DDL

可以看到，与图 3 过程的不同之处在于，由于日志文件记录和重放操作这个功能的存在，这个方案在重建表的过程中，允许对表 A 做增删改操作。这也就是 Online DDL 名字的来源。

图4中在进行insert R7 的时候也是需要获取MDL写锁的。这种常规操作没啥好讲的。真正有意思的是下面这段。这个写锁在真正拷贝数据之前就退化成读锁了。 写锁还是退化成读锁？

online的alter为什么会退化？为了实现 Online，MDL 读锁不会阻塞增删改操作。

这里我相信肯定肯定会有人疑问了。用读锁为什么不干脆直接解锁呢？为了保护自己，禁止其他线程对这个表同时做 DDL。

继续介绍这个流程吧。图3中的tmp 是一个临时表，这是由Server层自动创建的。而图4中的tmp_file 这个是由innodb创建的，整个过程都是在innodb完成的。与图3的Server相比，没有把数据挪动到临时表，是一个“原地”操作，这就是“inplace”名称的来源。

所以如果你的内存为1TB，你要处理一个2TB的表。能不能用inplace的DDL呢？答案是不行的，因为inplace是占用内存空间的，如果转储的空间大于自身空间。就会执行失败。

我们用SQL表示一下图3与图4的区别

alter table t engine=innodb,ALGORITHM=inplace;  图4
alter table t engine=innodb,ALGORITHM=copy;     图3

当使用 ALGORITHM=copy 的时候，表示的是强制拷贝表，对应的流程就是图 3 的操作过程。

区别

1. inplace 会阻塞增删改操作，是非 Online 的。

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

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

4. Online DDL 的方式是可以考虑在业务低峰期使用的，而 MySQL 5.5 及之前的版本，这个命令是会阻塞 DML 的

结尾总结

大概介绍了收缩表空间的方法。重建表的两种实现方式。delete 掉表里面不用的数据的话，表文件的大小是不会变的，你还要通过 alter table 命令重建表，才能达到表文件变小的目的。