MySQL45讲 第十三讲 为什么表数据删掉一半，表文件大小不变？
文章目录
- MySQL45讲 第十二讲 为什么表数据删掉一半，表文件大小不变？
- 一、引言
- 二、InnoDB 表数据存储方式
- 三、数据删除流程及表空间未回收原因
- 四、重建表以回收表空间
- 五、Online 与 inplace 概念区别
- 六、总结
MySQL45讲 第十二讲 为什么表数据删掉一半，表文件大小不变？
一、引言
在 MySQL 数据库管理中，经常会遇到表空间占用过大的问题，而简单删除表数据却未能使表文件大小减小。本文针对 MySQL 中广泛应用的 InnoDB 引擎，深入探讨表空间回收相关问题，包括数据存储方式、删除数据流程、表空间未回收原因、重建表方法及其相关概念的区别等。
二、InnoDB 表数据存储方式
- 参数
innodb_file_per_table，该参数控制表数据的存储位置：- 当设置为 OFF 时，表数据存放在系统共享表空间，与数据字典在一起；
- 设置为 ON 时（MySQL 5.6.6 版本起默认值为 ON，建议设置为 ON），每个 InnoDB 表数据存储在一个以
.ibd为后缀的文件中，这样便于管理，删除表时可直接删除文件回收空间，后续讨论基于此设置展开。
三、数据删除流程及表空间未回收原因
-
记录删除与复用
- 当删除 InnoDB 中的记录（如删掉 R4）时，引擎仅将记录标记为删除，若后续插入符合范围条件的数据（如 ID 在 300 和 600 之间）可复用该位置，但磁盘文件大小不变。
-
数据页删除与复用
- 若整个数据页上的记录都被删除，该数据页可被复用，但复用方式与记录复用不同，数据页从 B + 树摘掉后可复用到任何位置。
- 用
delete命令删除整个表数据时，所有数据页被标记为可复用，但磁盘文件大小仍不会变小，这些未使用空间形成 “空洞”。
-
插入数据造成空洞
- 插入数据（随机插入导致索引页分裂）和更新索引值（可理解为删除旧值再插入新值）也会造成空洞，所以经过大量增删改的表可能存在空洞，这是
delete命令不能回收表空间的原因。 - 下图中，插入索引为550的行R6，在PageA和PageB都出现了一个空洞。
- 插入数据（随机插入导致索引页分裂）和更新索引值（可理解为删除旧值再插入新值）也会造成空洞，所以经过大量增删改的表可能存在空洞，这是
四、重建表以回收表空间
-
重建表的原理
- 简而言之，就是原表A有空洞，那我新建一个表B，将A的数据复制到B，空洞就跳过，这样新表B就没空洞啦。
- 新建与原表结构相同的表，按主键 ID 递增顺序将原表数据读入新表，新表主键索引更紧凑、数据页利用率更高，用新表替换原表可达到收缩原表空间的目的，可使用
alter table A engine = InnoDB命令重建表。
-
MySQL 5.5 版本之前的重建表流程
- MySQL 会自动完成转存数据、交换表名、删除旧表操作，但整个过程中表 A 不能有更新，即该 DDL 不是 Online 的。
-
MySQL 5.6 版本引入的 Online DDL 流程
- 建立临时文件，扫描表 A 主键所有数据页；用数据页中记录生成 B + 树存储到临时文件；生成临时文件时记录对 A 的操作到日志文件；临时文件生成后将日志文件操作应用到临时文件得到与表 A 逻辑相同的数据文件；用临时文件替换表 A 的数据文件。此过程允许对表 A 做增删改操作，实现了 Online DDL。虽然 alter 语句启动时需获取 MDL 写锁，但在拷贝数据前退化为读锁，既能实现 Online（MDL 读锁不阻塞增删改）又能保护自身（禁止其他线程同时做 DDL），对于大表，拷贝数据到临时表过程耗时最长，此期间可接受增删改操作，相对整个 DDL 过程锁时间短，可认为是 Online 的。但重建表操作扫描原表数据和构建临时文件，对大表消耗 IO 和 CPU 资源，线上服务需小心控制操作时间，也可使用 gh - ost 更安全地操作。
- 建立临时文件，扫描表 A 主键所有数据页；用数据页中记录生成 B + 树存储到临时文件；生成临时文件时记录对 A 的操作到日志文件；临时文件生成后将日志文件操作应用到临时文件得到与表 A 逻辑相同的数据文件；用临时文件替换表 A 的数据文件。此过程允许对表 A 做增删改操作，实现了 Online DDL。虽然 alter 语句启动时需获取 MDL 写锁，但在拷贝数据前退化为读锁，既能实现 Online（MDL 读锁不阻塞增删改）又能保护自身（禁止其他线程同时做 DDL），对于大表，拷贝数据到临时表过程耗时最长，此期间可接受增删改操作，相对整个 DDL 过程锁时间短，可认为是 Online 的。但重建表操作扫描原表数据和构建临时文件，对大表消耗 IO 和 CPU 资源，线上服务需小心控制操作时间，也可使用 gh - ost 更安全地操作。
五、Online 与 inplace 概念区别
- inplace 概念(“原地”操作)
- 在重建表的 Online DDL 流程中，数据重建在 InnoDB 内部完成，对于server 层是 “原地” 操作，如
alter table t engine = InnoDB隐含ALGORITHM = inplace。但 inplace 不一定是 Online 的，如给 InnoDB 表字段加全文索引是 inplace 但会阻塞增删改操作，是非 Online 的。
- 在重建表的 Online DDL 流程中，数据重建在 InnoDB 内部完成，对于server 层是 “原地” 操作，如
- Online 与 inplace 关系
- DDL 过程如果是 Online 的，就一定是 inplace 的；反过来，inplace 的 DDL 有可能不是 Online 的，如添加全文索引和空间索引截至 MySQL 8.0 属于这种情况。
- 与其他重建表方式的区别
- 从 MySQL 5.6 版本开始，
alter table t engine = InnoDB默认是 Online DDL 流程；analyze table t只是重新统计表的索引信息，加 MDL 读锁，未修改数据；optimize table t 等于 recreate + analyze。
- 从 MySQL 5.6 版本开始，
六、总结
如果要收缩一个表，只是delete掉表里面不用的数据的话，表文件的大小是不会变的，你还要通过alter table命令重建表，才能达到表文件变小的目的。我跟你介绍了重建表的两种实现方式，Online DDL的方式是可以考虑在业务低峰期使用的，而MySQL 5.5及之前的版本，这个命令是会阻塞DML的，需要特别小心。