mysql的表空间

对于运行很长时间的数据库来说，往往会出现表占用存储空间过大的问题，可是将许多没用的表删除之后，表文件的大小并没有改变，想解决这个问题，就需要了解 InnoDB 如何回收表空间的。对于一张表来说，占用空间重要分为两部分，表结构和表数据。通常来说，表结构定义占用的空间很小。所以空间的问题主要和表数据有关。

在 MySQL 8.0 前，表结构存储在以 .frm 为后缀的文件里。在 8.0，允许将表结构定义在系统数据表中。

表数据的存放

可以将表数据存在共享表空间，或者单独的文件中，通过 innodb_file_per_table来控制。

OFF：表示存在系统共享表空间中，和数据字典一起

ON：每个 InnoDB 表结构存储在 .idb 为后缀的文件中

在 5.6.6 以后，默认值为 ON，建议将该参数设置为 ON，这样在不需要时，通过 drop table 命令，系统就会直接删除该文件。但在共享表空间中，即使表删掉，空间也不会回收。

truncate = drop + create

数据删除流程

delete仅仅是将记录标记为删除（可复用的位置），磁盘文件大小并不会减少。

有时使用 delete删除数据时，仅仅删除的是某些行，但这可能就会出现表空间没有被回收的情况。MySQL InnoDB 中采用了 B+ 树作为存储数据的结构，也就是索引组织表，并且数据是按照页来存在删除数据时，会有两种情况：

数据页PageA中有R1、R2、R3、R4

  1. 删除数据页中的某些记录，例如删除R4

InnoDB 直接将 R4 这条记录标记为删除，称为可复用的位置。当某次插入数据ID符合时，就会复用该位置。但记录的复用，只限于符合范围条件的数据；不符合的，则位置就不能被复用。由此可见，磁盘文件的大小并不会减少。

  1. 删除整个数据页的内容，例如删除R1、R2、R3、R4

InnoDB 就会将整个 Page A 标记为删除状态，之后整个数据都可以被复用，没有范围的限制

  1. …

如果两个相邻的数据页利用率都很小，就会把两个页中的数据合到其中一个页上，另一个页标记为可复用。综上，无论是数据行的删除还是数据页的删除，都是将其标记为删除的状态，用于复用，所以文件并不会减小。对应到具体的操作就是使用 delete 命令。

在插入数据时，如果数据按照索引递增顺序插入，索引的结构会是紧凑的。但如果是随机插入的，很可能造成索引数据页分裂。

由于 Page A 满了，所以要申请 Page B，调整 Page A 的过程到 Page B，这也称为页分裂。结束后 Page A 就有了空隙。另外对于更新操作也是，先删除再插入，也会造成空隙。进而对于大量进行增删改的表，都有可能存在空洞。如果把空洞去掉，自然空间就被释放了。

使用重建表

为了把表中的空隙去掉，这时就可以采用重新建一个与表 A 结构相同的表 B，然后按照主键 ID 递增的顺序，把数据依次插入到 B 表中。由于是顺序插入，自然 B 表的空隙不存在，数据页的利用率也更高。之后用表 B 代替表 A，好像起到了收缩表 A 空间的作用。