如果你熟悉 MySQL 缓冲池（不熟悉可以查看 一条 sql 的执行过程详解），可能会觉得是因为删除操作只更新到缓冲池和 redo log，并没有进行 flush 落盘，但如果关闭数据库，触发 flush ，会发现表文件大小还是不会改变，这是为什么？

原因

首先要了解数据的存储方式，存储方式共有两种，是由参数 innodb_file_per_table 来控制的。

off：共享表结构，表示所有的文件数据存储在同一个文件中，这样在删除整张表后空间也不会被回收，只是被位置被标记为可重用，下次创建表可能就在该位置创建。

on：表示每张表的数据各用一个文件来存储，在删除整张表后该文件也会被回收，减小总占用空间。这也是默认的使用方式。如果存储引擎是 InnoDB ，那么数据文件就是.ibd 格式的，如果是 MyISAM，那么文件就是 .MYD 格式的。

虽然执行 drop 删除表时会减小表文件大小，但在删除记录时还是不能减小结构，这个原因与上面的 off 共享表结构很像，因为 数据页是 InnoDB 管理数据的最小的磁盘单位，数据页就相当于上面的 "一张表的数据"，因为一张表的数据页都是存在同一个文件中的，所以在执行 delete 删除数据后只会将将改位置标记可重用，并不会回收，而如果删除整个页，那么也只能将该页标记为可重用而不会回收。这种删除了但是没有被回收的位置就称为 "数据空洞"。

页合并与页分裂

页合并：既然产生了数据空洞，那么数据文件将会变得越来越大，这样是很不利的，所以 MySQL 会在数据空洞达到一定比例后出触发 "页合并"，触发的页会找最靠近的可以合并的页进行合并来优化空间（只会将数据页使用权腾出来，并不会减小表文件大小），防止后续的数据插入使用更多的数据页造成文件更大。

页分裂：页分裂是在插入操作时操作的记录主键 ID 在原本的记录之间时产生的，因为记录存储在数据页中，如果该数据页没有合适的位置来存储这条记录，那么就会将该条记录以及后面的记录另开要一个数据页来存储。

优化：因为页合并和页分裂都需要消耗额外的性能。所以我们在插入数据时应当按主键递增顺序插入（主键可以使用自增ID 或 雪花算法，但如果业务字段有唯一字段且没有其他索引，那么可以使用其作为主键来避免每次查询都需要回表），删除数据时按主键顺序删除。

如何减小表文件

1、自动触发的页合并。

2、手动触发清理大部分的数据空洞（5.6 的 Online DDL 可能会存一些写操作，可能会产生一些数据空洞），具体做法就是执行 "Alter table 表名 engine = InnoDB"，因为 Alter 语句是修改表结构，而执行一个空修改操作就可以在不修改结构的情况下将数据空洞清除。具体原理是会先创建一个临时表，将当前表中的所有记录依次添加到临时表中，最后再将临时表替换原表的表。但是重建表并一定就是最紧凑的，因为在重建时每个数据页会留 1/16 用于更新，同时 5.6 后可能还会在向临时表迁移数据时积累一些写操作造成页分裂。而在这过程中不能有其他操作干扰，比如修改数据、读数据，所以在执行此操作时会添加 MDL 写锁，而在执行读写操作时会添加 MDL 读锁，两者互斥。

作者：萌新J

原文链接：
https://www.cnblogs.com/mengxinJ/p/14184458.html