mysql删除数据表空间不变原因是什么(mysql删除数据表空间不变原因有哪些)

导语：MySQL删除数据表空间不变原因

作为一名资深的CV 战士，我们可能很少去关注 MySQL的数据文件存储在哪里。当我们执行一批删除操作的时候，也不会去观察数据库文件的大小到底有没有变化。下面我们一起来看一下 MySQL 数据存储相关的原理。

在MySQL 8.0 之前，表结构定义是存储在 .frm 为后缀的文件里，如下图所示。因为表结构定义占用空间比较小，下面主要介绍一下 表数据的存储相关的东西。

innodb_file_per_table

表数据可以存储在共享表空间里（ibdata1、ibdata2......），也可以单独存储，这个是有innodb_file_per_table 参数决定的。innodb_file_per_table 设置为 OFF 时，表示表的数据存储在 共享表空间，也就是和数据字典存放在一起；当设置为ON 时，每个 InnoDB 的表数据都会存储在一个 .ibd 的文件中。（从 MySQL 5.6.6 开始默认为 ON）

innodb_file_per_table 建议设置为 ON，也就是每个表数据单独存储。当我们使用 drop 删除表时，就可以回收磁盘空间了。否则磁盘空间还不一定会被回收。

数据删除流程

上面是主键索引树的结构，比如当我们要删除 id = 500 的记录。此时500 位置上的空间就会空出来，磁盘的空间占用并不会减小。MySQL是按页存储的，当我们把整个 PageA 的数据都删除掉时。此时磁盘的空间也不会减小，而仅仅是PageA 是可以复用。当然，我们使用 delete 命令把所有数据都删除掉之后，其磁盘空间也不会减小的。也是只之前所有的数据页都是可复用而已。

不仅仅数据的删除会造成数据空洞，数据的插入也会造成数据的空洞。

如果所插入的数据是按照索引递增的插入的，那么索引就是紧凑的。如果是随机写入的，就有可能会造成索引的数据页分裂。

从上面的结果可以看出，当新写入一个 550 时，由于 PageA 满了，页分裂出来了 PageB ，也造成了数据页的空洞。而数据页的空洞可以通过 重建表的方式来达到去除空洞的目的。

重建表

我们可以使用 alter table A engine=InnoDB; 来重建表，其过程如下图所示：

从上图可以看出，首先MySQL 会创建出一个 tmp 临时表，然后将数据按照主键自增的方式插入，最后将 tmp 表覆盖 A 表。在MySQL 5.5 及以前，上面的重建表是 Offline的，也就是重建的过程中不对外提供服务。

在MySQL 5.6 版本开始引入 Online DDL ，也就是重建表的过程中也可以对外提供服务，如下图所示：

从上图可以看出，Online 的重建表与 Offline 的不同在于：如果在重建的过程中有表的更改，其会存储在 row log 当中，当重建完成后，再执行 row log 中的更改。

Online 和 inplace

上面重建表的第一张图，把表 A 导出来的数据是存放在 tmp 表中的，这个 tmp 表是在 Server 层创建的。而第二张图中 Online 操作，是 tmp_file 这个临时文件是在 InnoDB 内部创建的。对于 Server 来说，它是一个原地的操作，这就是 inplace 的由来。比如磁盘只有1.2TB，此时你需要对一张 1TB 大小的 table 做 inplace 操作，此时磁盘是不够用的。

默认的情况下，我们使用 alter table t engine=InnoDB; 其实就等于 alter table t engine=InnoDB, ALGORITHM=inplace; 。那么于此对应的就是 alter table t engine=InnoDB, ALGORITHM=copy; ，此时是强拷贝，就对应的是 第一张图中的过程。

从 MySQL 5.6 开始，alter table t engine=InnoDB 就是 Online的步骤（recreate过程）；analyze table t 其实不是重建表，只是对索引的信息做重新统计，没有修改数据，整个过程中加了 MDL 写锁；optimize table t 相当于 recreate + analyze 的过程。

参考：《极客时间：MySQL实战》、《高性能MySQL》

本文内容由小德整理编辑！