对于运行很长时间的数据库来说，往往会出现表占用存储空间过大的问题，可是将许多没用的表删除之后，表文件的大小并没有改变，想解决这个问题，就需要了解 innodb 如何回收表空间的。
对于一张表来说，占用空间重要分为两部分，表结构和表数据。通常来说，表结构定义占用的空间很小。所以空间的问题主要和表数据有关。
在 mysql 8.0 前，表结构存储在以 .frm 为后缀的文件里。在 8.0，允许将表结构定义在系统数据表中。
关于表数据的存放
可以将表数据存在共享表空间，或者单独的文件中，通过 innodb_file_per_table 来控制。
- 如果为 off ，表示存在系统共享表空间中，和数据字典一起
- 如果为 on，每个 innodb 表结构存储在 .idb 为后缀的文件中
在 5.6.6 以后，默认值为 on.
建议将该参数设置为 on，这样在不需要时，通过 drop table 命令，系统就会直接删除该文件。
但在共享表空间中，即使表删掉，空间也不会回收。
|
1
|
truncate = drop + create
|
数据删除流程
但有时使用 delete删除数据时，仅仅删除的是某些行，但这可能就会出现表空间没有被回收的情况。
我们知道，mysql innodb 中采用了 b+ 树作为存储数据的结构，也就是常说的索引组织表，并且数据时按照页来存储的。
在删除数据时，会有两种情况：
- 删除数据页中的某些记录
- 删除整个数据页的内容
比如想要删除 r4 这条记录：
innodb 直接将 r4 这条记录标记为删除，称为可复用的位置。如果之后要插入 id 在 300 到 700 间的记录时，就会复用该位置。由此可见，磁盘文件的大小并不会减少。
而且记录的复用，只限于符合范围条件的数据。之后要插入 id 为 800 的记录，r4 的位置就不能被复用了。
再比如要是删除了整个数据页的内容，假设删除 r3 r4 r5，为 page a 数据页。
这时 innodb 就会将整个 page a 标记为删除状态，之后整个数据都可以被复用，没有范围的限制。比如要插入 id=50 的内容就可以直接复用。
并且如果两个相邻的数据页利用率都很小，就会把两个页中的数据合到其中一个页上，另一个页标记为可复用。
综上，无论是数据行的删除还是数据页的删除，都是将其标记为删除的状态，用于复用，所以文件并不会减小。对应到具体的操作就是使用 delete 命令.
而且，我们还可以发现，对于第一种删除记录的情况，由于复用时会有范围的限制，所以就会出现很多空隙的情况，比如删除 r4，插入的却是 id=800.
插入操作也会造成空隙
在插入数据时，如果数据按照索引递增顺序插入，索引的结构会是紧凑的。但如果是随机插入的，很可能造成索引数据页分裂。
比如给已满的 page a 插入数据。
由于 page a 满了，所以要申请 page b，调整 page a 的过程到 page b，这也称为页分裂。
结束后 page a 就有了空隙。
另外对于更新操作也是，先删除再插入，也会造成空隙。
进而对于大量进行增删改的表，都有可能存在空洞。如果把空洞去掉，自然空间就被释放了。
使用重建表
为了把表中的空隙去掉，这时就可以采用重新建一个与表 a 结构相同的表 b，然后按照主键 id 递增的顺序，把数据依次插入到 b 表中。
由于是顺序插入，自然 b 表的空隙不存在，数据页的利用率也更高。之后用表 b 代替表 a，好像起到了收缩表 a 空间的作用。
具体通过:
|
1
|
alter table a engine=innodb
|
在 5.5 版本后，该命令和上面提到的流程差不多，而且 mysql 会自己完成数据，交换表名，删除旧表的操作。
但这就有一个问题，在 ddl 中，表 a 不能有更新，此时有数据写入表 a 的话，就会造成数据丢失。
在 5.6 版本后引入了 online ddl。
online ddl
online ddl 在其基础上做了如下的更新：
重建表的过程如下：
- 建立一个临时文件，扫描表 a 主键的所有数据页。
- 用生成的数据页生成 b+ 树，存储到临时文件中。
- 生成临时文件时，如果有对 a 的操作，将其记录在日志文件中，对应图中 state 2 的状态。
- 临时文件生成后，将日志文件应用到临时文件中，得到与 a 表相同的数据文件，对应 state 3 状态。
- 用临时文件替换 a 表的数据文件。
由于 row log 日志文件存在，可以在重建表示，对表 a 进行 dml 操作。
需要注意的是，在 alter 语句执行前，会先申请 mdl 写锁，但在拷贝数据前会退化成 mdl 读锁，从而支持 dml 操作。
至于为什么不大 mdl 去掉，是防止其他线程对这个表同时做 ddl 操作。
对于大表来说，该操作很耗 io 和 cpu 资源，所以在线上操作时，要控制操作时间。如果为了保证安全，推荐使用 gh-ost 来迁移。
online 和 inplace
首先说一下 inplace 和 copy 的区别：
在 online ddl 中，表 a 重建后的数据放在 tmp_file 中，这个临时文件是在 innodb 内部创建出来的。整个 ddl 在 innodb 内部完成。进而对于 server 层来说，并没有数据移动到临时表中，是一个 "原地" 操作，所以叫 "inplace" .
而在之前普通的 ddl 中，创建后的表 a 是在 tmp_table 是 server 创建的，所以叫 "copy"
对应到语句其实就是：
|
1
2
3
4
5
|
-- alter table t engine=innodb 默认为下面
alter table t engine=innodb,algorithm=inplace;
-- 走的就是 server 拷贝的过程
alter table t engine=innodb,algorithm=copy;
|
需要注意的是 inplace 和 online 并不是对应关系：
- ddl 过程是 online，则一定是 inplace
- 如果是 inplace 的 ddl 不应当是 online，如在 <= 8.0, 添加全文索引和空间索引就属于这种情况。
拓展
说一下 optimize，analyze，alter table 三种重建表之间的区别：
- alter table t engine = innodb（也就是 recreate）默认的是 oline ddl 过程。
- analyze table t 不是重建表，仅仅是对表的索引信息做重新统计，没有修改数据，期间加 mdl 读锁。
- optimize table t 等于上两步的操作。
在事务里面使用 alter table 默认会自动提交事务，保持事务一致性
如果有时，在重建某张表后，空间不仅没有变小，甚至还变大了一点点。这时因为，重建的这张表本身没有空隙，在 ddl 期间，刚好有一些 dml 执行，引入了一些新的空隙。
而且 innodb 不会把整张表填满，每个页留下 1/16 给后续的更新用，所以可能远离是紧凑的，但重建后变成的稍有空隙。
总结
现在我们知道，在使用 delete 删除数据时，其实对应的数据行并不是真正的删除，innodb 仅仅是将其标记成可复用的状态，所以表空间不会变小。
通常来说，在标记复用空间时分为两种，一种是仅将某些数据页中的位置标记为删除状态，但这样的位置只会在一定范围内使用，会出现空隙的情况。
另一种是将整个数据页标记成可复用的状态，这样的数据页没有限制，可直接复用。
为了解决这个问题，我们可以采用重建表的方式，其中在 5.6 版本后，创建表已经支持 online 的操作，但最后是在业务低峰时使用
以上就是mysql删除数据，表文件大小依然没变的原因的详细内容，更多关于mysql表文件大小的资料请关注服务器之家其它相关文章！
原文链接：https://www.cnblogs.com/michael9/p/13532972.html