关于MySQL存储空间的一些总结

MySql中表空间包含2部分 表结构、表数据。

表结构

在MySQL8.0以前表结构是保存在.frm文件中，在MySQL8.0之后表结构可以保存在系统数据表中了。

表数据

表数据可以存放于共享表空间，也可以单独存放于单独的文件中。该行为可以通过innodb_file_per_table=ON/OFF来控制，ON是将表数据单独保持于以.ibd为后缀的文件中，这也是推荐的设置。将各个表数据单独保存，可以方便管理。Drop table之后 对应的表空间会释放掉。如果表数据存放于共享空间中，那drop table之后是无法释放对应的空间的。

行数据删除

Innodb使用b+树来组织索引，并且索引是按页保存的。删除数据时，不会将该数据记录对应的存储空间释放，而是标记为删除。如果某一个数据页上的数据记录都被标记为删除，那么整个数据页就可以被复用了。数据记录复用时只能用来保存符合索引排序的数据。比如在3，5，7中将5的存储位置复用的话 那么只能保持3-7之间的数字。但是，整个数据页复用的话就没有这一限制，可以保存新的数据记录。除了删除数据，如果相邻的两个数据页使用率很低的话，那么Innodb也会将两个数据页合并到其中一个数据页，将另外一个标记为删除用于后续复用。

数据插入

在索引中，插入数据中，需要找到对应的合适有序位置。如果当前数据页已满，那么需要进行页分裂，将插入位置后的数据迁移到新的数据页，这时候被迁移的原位置也被标记删除了。至于，更新可以理解成先删除然后插入的操作。

综上，对表数据的增删改之后，数据页会产生很多的空间被标记为删除，如果只是单纯的delete只是标记删除，并不会真正的去释放存储空间。

重建表

通过重建表操作，可以将数据页被标记为删除的空间释放出来，从而提高页空间的利用率。重建的大概逻辑是：创建一个与原表相同结构的新表，然后按照主键顺序依次将原表数据复制到新表中，最后用新表替代旧表。

执行指令 alter table a engine=innodb;

OnLineDDL

在MySql5.6之前，重建表期间需要复制数据，因此无法对原表做写操作。但是在MySql5.6之后，重建表流程得到了优化，可以在重建表期间对原表进行写操作。具体优化逻辑是在复制表数据的同时，如果将原表的写操作记录日志，等待复制数据完成之后，将日志中的操作应用到新的表中，最后在进行替换操作。此处需要注意的是，重建表的alter操作需要先获取到MDL的写锁，然后再复制数据的时候写锁退化为读锁，此举是为了防止再重建表期间有其他的DDL操作。