这篇文章聊聊 MySQL 表数据的删除。

表数据存储

以 InnoDB 存储引擎为例。

一张表包含两部分：表结构定义和数据。

表结构

表结构定义占用空间较小：在 MySQL 8.0 版本以前，表结构存储在以 .frm 为后缀的文件里，在 MySQL 8.0 之后，表结构定义可以存储在数据表中。

表数据

表数据的存储分两种：共享表空间和单独的文件。

这两种存储方式的选择通过设置参数 innodb_file_per_table 进行控制。

  • 参数值设为 ON 时，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中；
  • 参数值设为 OFF 时，每个 InnoDB 表数据存储在系统共享表空间。

在 MySQL 5.6.6 版本之后，此参数值默认为 ON。

删除表自然是使用 drop table 命令。

对于参数值为 ON 时，因为表数据都放在一个单独的文件中，所以会直接删除该文件；对于参数值为 OFF 时，表数据是放在共享的表空间中，即使表删除了，空间也不会回收。

如果要删除的不是整张表而是表中的某些行呢？

行数据如何删除

InnoDB 中的表数据都是放在一棵 B+ 树中，如图。

索引结构
如果想删除 R4 这个记录，InnoDB 引擎只会把 R4 这个记录标记为删除。

如果之后，再要插入一个 ID 在 300 和600之间的记录时，可能还会复用这个位置，也就是说只有满足限定条件的记录，才能够对该位置进行重复利用，磁盘大小并不会缩小。

前面的文章我们知道：InnoDB 中的记录都是在存放在 Page 中的。

以上图为例，如果 PageA 中的记录全被标记为删除会发生什么？

这个时候整个 PageA 都会标记为可复用，对于该页中存储的新记录没有限制。

如果相邻的两个 page 页利用率都很小，系统会把这两个页上的数据合并到其中一个页上，另外一个页可以标记为可复用，有点类似 JVM 垃圾回收算法中的标记复制算法。

如果使用 delete 命令将整张表上的所有记录删除，结果就是该表的所有数据页都会被标记为可复用，但是磁盘占用的大小不变。

结论：delete 命令并不能释放表空间，但是可以复用表空间，没有被利用的表空间就成为了一个个“空洞”。

插入数据造成“空洞”

上面讲使用 delete 命令删除数据会造成空洞，这部分讲插入数据也会造成空洞，但必须是在随机插入的情况下。

空洞

由于 PageA 已满，再插入一个 ID 为 550 的记录时，就需要再申请一个新的页面 PageB 保存数据，也就是页分裂的过程。

从图中可以看到：PageA 留下了空洞。

消除空洞

消除空洞的方法还是采用了标记-复制的思想。

假如表A需要做空间收缩，为了把表中的空洞去掉，最简单的办法就是再新建一张大小一样的表B，然后把表A中的数据按照主键ID递增的顺序（这样才能保证不会出现空洞），把数据一行一行拷贝到B中。

这样在B中的索引树一定是紧凑的，因为不是随机写入的，而是顺序写入的。

操作完成之后，将表A用表B替换。

整个示意图如下：

收缩过程

  • state1状态下：出现了空洞
  • state2 状态下：A表拷贝到B表，B表作为一张临时表。
  • state3状态下：A表成为临时表，B表摇身一变成为A表。
  • state4状态下：临时表删除，只有新的A表存在。

我们可以使用如下命令进行表替换：

alter table A engine=InnoDB

那么现在就有一个问题：如果我在拷贝表A的数据到表B的过程中，有新数据插入到表A中怎么办？

答：低版本的 MySQL 中，此时表A中不能有更新，要给表A上锁；在 MySQL 5.6版本之后，支持一边消除空洞，一边更新数据，即 Online DDL。

收缩过程

与非 online 的区别就是：在生成临时表的过程中，如果有的数据插入，可以先把操作记录到一个日志文件（row log）中，旧表数据都拷贝到临时表之后，再将日志文件上的所有操作应用到临时表中。

锁退化

alter 语句在启动前要拿到 MDL 写锁。

为了防止阻塞对数据表的增删改操作，在真正拷贝数据之前，锁退化为读锁了。

Online vs Inplace

非 Online DDL 情况下，表 A 中拷贝出来的数据都是放在一张临时表（tmp_table）中，而在 Online DDL 情况下，表 A 中重建出来的数据都是放在临时文件（tmp_file）中。

这两种操作的区别在于：前者是在 Server 层创建的，而后者是在 InnoDB 存储引擎中创建的，对于 Server 层来说，这就是一个“原地操作”，即 Inplace。

alter table t engine=InnoDB，语句隐含意思：

alter table t engine=InnoDB,Algorithm=inplace

而如果强制使用拷贝表的方式：

alter table t engine=InnoDB,Algorithm=copy

这样相当于使用非 Online DDL 的方式。

可以看到：在重建表的逻辑中， Inplace 的方式就是 Online 。

但是在某些情况下，二者不同。

比如如果我要给 InnoDB 表的一个字段加全文索引，如下：

alter table t add FULLTEXT(field_name)

这个操作是 Inplace 的，但是确实非 online 的。

（全文完）

参考资料

  1. 丁奇 《MySQL 45 讲》