表数据删除了一半，表文件大小却不变？MySQL删除执行流程

本文讨论的就如题目所示，为什么在 MySQL 中，把表数据删除了一半，表文件大小却不变的？以下讨论前提都是使用InnoDB存储引擎，使用DELETE删除，并非DROP和TRUNCATE

一、案例演示

1. 首先创建一个 user 表，新增大概 1000 条记录
2. 查看该表的占用空间

SELECT table_name AS "Table",
  ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "demo"
ORDER BY (data_length + index_length) DESC;

-- 刷新一下最新数据
analyze table `user`

从上图可以看出，user 表有 0.11MB，而其他空表则只有 0.02MB 3. 删除 user 表数据

-- 使用delete删除全表数据
delete from user
-- 刷新一下数据
analyze table `user`

然后再调用一下上面的查看 user 表占用空间的 sql，发现还是 0.11 4. 执行重建表

-- 重建表
alter table demo.`user` engine=InnoDB
-- 刷新
analyze table `user`

这时候再查看 user 表的占用空间已经变成了 0.02MB 了

二、表文件存储

一个 InnoDB 表中，包含表结构和数据，在 MySQL8 之前，表结构是以.frm为后缀的文件里，在 MySQL8 之后，则已经允许把表结构定义放在系统数据表里了，因为表结构定义占用的空间小;

而数据存放位置由参数innodb_file_pre_table控制的：

• 这个参数设置为 OFF，表的数据放在系统共享表空间，也就是跟数据字典放在一起
• 这个参数设置为 ON，则每个 InnoDB 表数据存储在一个以.ibd 为后缀的文件中
  
  从 MySQL5.6.6 版本开始，他的默认值就是 ON 了，当然我们也推荐设置成 ON。
  因为一个表数据单独存储更容易管理，而且在调用 drop table 命令时，系统就会直接删除这个文件，而如果放在共享表空间的话，即使表删掉了，空间也是不会回收的

三、数据删除流程

当然我们在删除数据的时候，大多数使用的都是DELETE FROM table

假设我们删除了 ID=5 这条记录，InnoDB 引擎汇中只会把 ID=5 这条记录标记为删除，如果之后我们在新增一条 ID=5 的记录，则会复用这个位置，但是磁盘文件的大小不会缩小，当然，如果我们删除掉了一个数据页上的所有记录，那么这个整个数据页都是可以被复用的

所以 InnoDB 的删除有点像我们业务上的逻辑删除，只是有个删除标记，delete 命令其实只是把记录的位置，或者数据页标记为了"可复用"，但是磁盘文件的小大不会变的，也就是说使用delete命令不能回收表空间，而这些可以复用，没有被使用的空间，我们称之为空洞

当然不止只有删除会造成空洞，新增数据也会造成空洞

由于 PageA 满了，所以要申请 PageB，调整 PageA 到 PageB 的过程，这也称之为分裂，结束后 PageA 就有了空洞，另外更新也是，先删除后新增，也会造成空洞。进而对于大量的增删改的表，都可能造成大龄的空洞，如果把空洞去掉，自然空间也就被释放了

四、重建表

假设现在有一张表 A，为了做空间搜索，把表中存在的空洞给去掉，该怎么做呢？

1. 可以重建一个与表 A 结构相同的表
2. 然后按照主键 ID 递增的顺序，把数据一行一行的从表 A 复制到表 B 中
3. 由于 B 是新建的表，所以表 A 上的空洞，在表 B 上就不存在了，表 B 的索引更加紧凑，数据页利用也更高
4. 然后把表 B 当作临时表，当表 A 数据都迁移到了表 B，用表 B 替换表 A，从效果上看，就起到了收缩表 A 空间的作用

这里，可以使用alert table A engin=InnoDB命令来重建，这个命令跟我们上面的流程差不多，只不过这个临时表 B 是它自己创建，MySQL 会自动完成转存数据，交换表明，删除旧表操作；

在 MySQL5.6 之前，花时间做多的步骤是往临时表里查数据的过程，如果这个过程中有新的数据要插入到表 A 的话，会造成数据丢失，因为在整个 DDL 过程中，表 A 是不能有更新，而在 MySQL5.6 版本之后，开始引入了Online DDL,对这个操作做了优化，可以在重建表的过程中，允许对表 A 的增删改操作

在生成临时文件的过程中，将所有的对表A的操作都放到rowlog日志中，最后把这个 rowlog 数据应用到最新的表 A 中，就做到了 online DDL，alert 语句在启动的时候就获取了 MDL 写锁，但是这个锁在正在拷贝的时候就退化成了读锁，为了实现 Online DDL，MDL 读锁不会阻塞增删改查操作

optimize table，analyze table 和 alert table这三种方式重建表的区别：

• 从 MySQL5.6 后，alert table t engine = InnoDB（recreate）默认使用Online DDL的流程
• analyze table t其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了个 MDL 锁
• optimize table t相当于 recreate+analyze

值得注意的是，上述中重建方法都会扫描原数据和构建临时文件。对于数据量很大的表来说，这个操作是非常消耗 IO 和 CPU 资源的，因此，如果是线上服务，请谨慎操作。

---

我是一零贰肆，一个关注Java技术和记录生活的博主。

欢迎扫码关注“一零贰肆”的公众号，一起学习，共同进步，多看路，少踩坑。