~/bGZo/notes

12 min read

MySQL 13 为什么表数据删掉一半，表文件大小不变？ - 叁沐

一个 InnoDB 表包含两部分：表结构定义和数据。在 MySQL 8.0 版本前，表结构存在以.frm 为后缀的文件里。之后的版本允许把表结构定义放在系统数据表中。由于表结构定义占用空间很小，所以主要讨论表数据。

接下来，先说明为什么简单删除表数据达不到表空间回收的效果，再介绍正确回收空间的方法。

参数 innodb_file_per_table

表数据既可以存在共享表空间里，也可以是单独的文件，这由参数 innodb_file_per_table 控制：

  • \n 设为 OFF，表示表数据放在系统共享表空间，也就是跟数据字典放在一起；\n
  • \n 设为 ON，表示每个 InnoDB 表数据存储在一个以.ibd 为后缀的文件中。\n 设为 OFF，表示表数据放在系统共享表空间，也就是跟数据字典放在一起；

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

从 MySQL 5.6.6 版本开始，默认值为 ON。建议也是使用 ON，因为一个表单独存储为一个文件更容易管理，而且在不需要该表时通过 drop table 命令，系统就会直接删除文件；如果是放在共享表空间中，即使表删除，空间也是不会回收的。

接下来的讨论也是基于 innodb_file_per_table=ON 的设置。

在删除整张表的时候，可以使用 drop table 命令回收表空间。但是，平时更多的场景是删除某些行。

数据删除流程

为了搞懂删除部分行的场景，需要先从数据删除流程开始说。

看一下 InnoDB 中一个索引的示意图：

假设要删除 R4 这个记录，InnoDB 只会把 R4 这个记录标记为删除。如果之后插入一个 ID 在 300-600 间的记录，可能会复用这个位置，但磁盘文件的大小不会缩小。

那么如果将一个数据页上的所有记录都删除，会怎么样呢？答案是整个数据页可以复用。

但是数据页的复用和记录的复用还是不一样的。记录的复用只限于符合范围条件的数据，而一旦一个数据页可以复用，所有范围的数据都可以使用。比如在上面的索引中，若 page A 是可复用的，ID=50 这样的记录也能使用该页。

如果相邻两个数据页利用率都很小，系统会把这两个页上的数据合到其中一个页上，另一个页就会被标记为可以复用。

进一步地，如果用 delete 命令删除整个表的数据，那么所有数据页都会被标记为可复用，而磁盘上的文件并不会变小。也就是说，delete 命令不能回收表空间，这些可以复用却没被使用的空间，看起来就像“空洞”。

实际上不止删除数据会造成空洞，插入数据也会。如果数据的插入是随机的，可能造成索引的数据页分裂。比如在上面的索引中，假设 page A 已满，这时若要再插入一行数据 ID=550：

当 page A 已满的情况下进行插入，就必须再申请一个新的页面 page B 来保存数据。由于页分裂导致部分数据移动，page A 就出现了空洞。

除了插入，由于更新可以看为删除 + 插入，也可能造成空洞。即，增删改都可能出现空洞。所以，如果能把这些空洞去掉，就能达到收缩表空间的目的。

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

重建表

假设现在有一个表 A，需要去除其中的空洞，有什么办法呢？

可以新建一个与表 A 结构相同的表 B，然后按照主键 ID 递增的顺序，把数据逐行从表 A 读取出来再插入到表 B 中。由于表 B 是新建的表，所以没有表 A 上的空洞。把表 B 作为临时表，数据从表 A 导入表 B 后，再用表 B 替换表 A，从效果上就是表 A 没有空洞了。

可以使用 alter table A engine=InnoDB 的命令重建表。在 MySQL 5.5 版本前，这个命令的执行流程和上面描述的差不多，区别只是不需要自己创建临时表，MySQL 会自动完成转存数据、交换表名、删除旧表的操作。

在往临时表插入数据的过程中，如果有新的数据要写入表 A，会造成数据损失，因此整个 DDL 的过程中，表 A 不能有更新，即 DDL 不是 Online 的。

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

  • \n 建立一个临时文件；\n
  • \n 扫描表 A 主键的所有数据页，用里面的记录生成 B+ 树，存储到临时文件中；\n
  • \n 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应下图中 state 2 的状态；\n
  • \n 临时文件生成以后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的临时文件；\n
  • \n 用临时文件替换表 A。\n 建立一个临时文件；

扫描表 A 主键的所有数据页，用里面的记录生成 B+ 树，存储到临时文件中；

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

临时文件生成以后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的临时文件；

用临时文件替换表 A。

该操作流程由于日志文件和重放操作的功能，在重建表的过程中允许对表 A 做增删改操作。

当然，由于对表做改动，会有 MDL 锁的存在。alter 语句在启动时会获取 MDL 写锁，但这个锁在真正拷贝数据之前就会退化成读锁，目的是禁止其他线程对这个表同时做 DDL，又不会阻塞增删改操作。

对于一个大表来说，Online DDL 最耗时的过程就是拷贝数据到临时表的过程，所以相对整个 DDL 过程来说，写锁锁住的时间非常短，可以认为是 Online 的。

需要说明的是，上述这些重建方法都会扫描原表数据和构建临时文件，对于很大的表来说，该操作很消耗 IO 和 CPU 资源。因此，如果是线上服务需要控制操作时间，推荐使用开源的 gh-ost 来做。

Online 和 inplace

说到 Online，再讲一个容易混淆的概念 inplace。

在早版本的重建表过程中，表 A 数据导出来的存放位置叫做 tmp_table，这个临时表是在 Server 层创建的。

而在后面的版本，表 A 重建出来的数据是放在 tmp_file 里的（见前面的图），这个临时文件是 InnoDB 在内部创建出来的。由于整个 DDL 过程在 InnoDB 内部完成，对于 Server 层来说，没有把数据挪动到临时表，是一个“原地”操作，因此叫 inplace。

那么假如表大小为 1TB，磁盘空间为 1.2TB，是否能做 inplace 的 DDL 呢？答案是不行的，因为 tmp_file 会占用临时空间。

重建表的完整语句其实是下面这样：

其中，copy 表示强制拷贝表，即使用临时表；inplace 表示使用临时文件。

那是否表示，inplace 就是 Online？也不是，只是在重建表这个逻辑中刚好是这样。

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

  • \nDDL 过程如果是 Online 的，就一定是 inplace 的；\n
  • \n 反之不正确，inplace 的 DDL，不一定是 Online 的。截止到 MySQL 8.0，添加全文索引（FULLTEXT index）和空间索引 (SPATIAL index) 就属于这种情况。比如要给 InnoDB 表的一个字段加全文索引，过程是 inplace 的，但会阻塞增删改。\n DDL 过程如果是 Online 的，就一定是 inplace 的；

反之不正确，inplace 的 DDL，不一定是 Online 的。截止到 MySQL 8.0，添加全文索引（FULLTEXT index）和空间索引 (SPATIAL index) 就属于这种情况。比如要给 InnoDB 表的一个字段加全文索引，过程是 inplace 的，但会阻塞增删改。

Graph View