MySQL45讲 第十三讲 为什么表数据删掉一半，表文件大小不变？

MySQL45讲 第十二讲 为什么表数据删掉一半，表文件大小不变？

一、引言

在 MySQL 数据库管理中，经常会遇到表空间占用过大的问题，而简单删除表数据却未能使表文件大小减小。本文针对 MySQL 中广泛应用的 InnoDB 引擎，深入探讨表空间回收相关问题，包括数据存储方式、删除数据流程、表空间未回收原因、重建表方法及其相关概念的区别等。

二、InnoDB 表数据存储方式

1. 参数 innodb_file_per_table，该参数控制表数据的存储位置：
   • 当设置为 OFF 时，表数据存放在系统共享表空间，与数据字典在一起；
   • 设置为 ON 时（MySQL 5.6.6 版本起默认值为 ON，建议设置为 ON），每个 InnoDB 表数据存储在一个以.ibd 为后缀的文件中，这样便于管理，删除表时可直接删除文件回收空间，后续讨论基于此设置展开。

三、数据删除流程及表空间未回收原因

1. 记录删除与复用

   • 当删除 InnoDB 中的记录（如删掉 R4）时，引擎仅将记录标记为删除，若后续插入符合范围条件的数据（如 ID 在 300 和 600 之间）可复用该位置，但磁盘文件大小不变。

   

2. 数据页删除与复用

   • 若整个数据页上的记录都被删除，该数据页可被复用，但复用方式与记录复用不同，数据页从 B + 树摘掉后可复用到任何位置。
   • 用 delete 命令删除整个表数据时，所有数据页被标记为可复用，但磁盘文件大小仍不会变小，这些未使用空间形成 “空洞”。

3. 插入数据造成空洞

   • 插入数据（随机插入导致索引页分裂）和更新索引值（可理解为删除旧值再插入新值）也会造成空洞，所以经过大量增删改的表可能存在空洞，这是 delete 命令不能回收表空间的原因。
   • 下图中，插入索引为550的行R6，在PageA和PageB都出现了一个空洞。

   

四、重建表以回收表空间

1. 重建表的原理

   • 简而言之，就是原表A有空洞，那我新建一个表B，将A的数据复制到B，空洞就跳过，这样新表B就没空洞啦。
   • 新建与原表结构相同的表，按主键 ID 递增顺序将原表数据读入新表，新表主键索引更紧凑、数据页利用率更高，用新表替换原表可达到收缩原表空间的目的，可使用 alter table A engine = InnoDB 命令重建表。

2. MySQL 5.5 版本之前的重建表流程

   • MySQL 会自动完成转存数据、交换表名、删除旧表操作，但整个过程中表 A 不能有更新，即该 DDL 不是 Online 的。

3. MySQL 5.6 版本引入的 Online DDL 流程

   • 建立临时文件，扫描表 A 主键所有数据页；用数据页中记录生成 B + 树存储到临时文件；生成临时文件时记录对 A 的操作到日志文件；临时文件生成后将日志文件操作应用到临时文件得到与表 A 逻辑相同的数据文件；用临时文件替换表 A 的数据文件。此过程允许对表 A 做增删改操作，实现了 Online DDL。虽然 alter 语句启动时需获取 MDL 写锁，但在拷贝数据前退化为读锁，既能实现 Online（MDL 读锁不阻塞增删改）又能保护自身（禁止其他线程同时做 DDL），对于大表，拷贝数据到临时表过程耗时最长，此期间可接受增删改操作，相对整个 DDL 过程锁时间短，可认为是 Online 的。但重建表操作扫描原表数据和构建临时文件，对大表消耗 IO 和 CPU 资源，线上服务需小心控制操作时间，也可使用 gh - ost 更安全地操作。
     

五、Online 与 inplace 概念区别

1. inplace 概念(“原地”操作)
   • 在重建表的 Online DDL 流程中，数据重建在 InnoDB 内部完成，对于server 层是 “原地” 操作，如 alter table t engine = InnoDB 隐含 ALGORITHM = inplace。但 inplace 不一定是 Online 的，如给 InnoDB 表字段加全文索引是 inplace 但会阻塞增删改操作，是非 Online 的。
2. Online 与 inplace 关系
   • DDL 过程如果是 Online 的，就一定是 inplace 的；反过来，inplace 的 DDL 有可能不是 Online 的，如添加全文索引和空间索引截至 MySQL 8.0 属于这种情况。
3. 与其他重建表方式的区别
   • 从 MySQL 5.6 版本开始，alter table t engine = InnoDB 默认是 Online DDL 流程；analyze table t 只是重新统计表的索引信息，加 MDL 读锁，未修改数据；optimize table t 等于 recreate + analyze。

六、总结

如果要收缩一个表，只是delete掉表里面不用的数据的话，表文件的大小是不会变的，你还要通过alter table命令重建表，才能达到表文件变小的目的。我跟你介绍了重建表的两种实现方式，Online DDL的方式是可以考虑在业务低峰期使用的，而MySQL 5.5及之前的版本，这个命令是会阻塞DML的，需要特别小心。