MYSQL45讲学习笔记

13.为什么表数据删掉一半，表文件大小不变？

表结构文件

在MYSQL8.0以前， 表结构文件存放在一个.frm后缀的文件里；在MYSQL8.0之后，已经允许表结构文件与表数据文件一起存放。因为表结构文件占用空间很小，所以我们主要关注表数据文件。

表数据文件与innodb_file_per_table

说到表数据文件，我们必须说说innodb_file_per_table这个参数，

当值为ON时，表示表数据文件单独存放在 .ibd 后缀的文件中，如果我们使用drop table 命令，那么系统会删除这个文件。

当值为OFF时，表示表数据文件存放在 系统共享表空间中，也就是和数据字典放在一起。当我们使用drop table命令时，即使表删掉了， 系统也不会释放表空间。

推荐该值任何时候都为ON，从MYSQL5.5.6 版本开始，该值就默认为ON.

这是删除表的场景，但是我们经常遇到的还是删除表中的某些数据。

数据删除流程

MYSQL索引结构图，如图所示，如果我们删除了ID为500的数据，MYSQL会将这行记录标记为已删除，但是并不是释放表空间，我们下一次插入一条ID为400的数据，有可能会复用这个位置。

MYSQL的数据是按页来存储的，页大小默认为16K，那么如果我们删除掉整页的数据会怎么样？

当我们删除掉整页数据后，表文件大小还是不变，这是因为整个页被复用了。

但是，数据页的复用和记录复用是不同的。

在上图我们删掉了R4的记录，之后如果我们插入一条ID为800的数据，那么是不能复用这个记录的。但是如果pageA整个数据页都被删除了，那么整页都可以复用，我们插入一个ID为400的数据，就可以复用pageA.

如果相邻的两个页使用率较小，会将这两个页的数据合到其中一个页上，将另一个页标记为可复用，此过程称为页合并。可以提交页面空间使用率。

再进一步，如果我们用delete命令删除掉整个表的数据，那么这个表的所有空间都会被标记为可复用，但是表空间大小不变。

如果我们使用delete命令删除表数据，那么只会使记录或者数据页被标记为已复用，不会释放表空间。没有被复用的记录，会造成空洞，导致数据页使用率下降。

不止删除数据会造成空洞，插入数据也会造成空洞。

如果我们在上图插入一个ID为550的记录，那么pageA的数据页不够用了，会造成页分裂，

申请一个新的数据页，然后将pageA的部分数据移到新页上。

如图所示，页分裂完成后，pageA末尾就留下了空洞。

所以，一个大表，是非常有可能留下这种空洞的，导致空间使用率低。

重建表空间

要解决这种空洞，我们可以重建表空间。

如果要释放表空间，我们可以使用 alter table t engine = innodb 命令，来重建表和索引。

重建表流成如下：

将表数据复制到一个临时文件tmp_file中，然后将tmp_file改为表A,最后将原表A删除。

在重建表空间时，因为是DDL操作，会获取MDL写锁，阻塞其线程的DDL和DML操作。

online DDL

MYSQL5.5.6之后增加了online DDL功能，对其进行了修改，流程如下：

1.获取MDL写锁，将其退化为MDL读锁

2.创建一个临时表，将表中数据复制到临时表中

3.如果此时有DML操作，将操作记录到 row log中

4.将row log应用到临时表中

5.转换临时表表名，删除原表

online DDL和 inplace

alert table t engine = innodb 相当于

alter table t engine = innodb,algorithm  = inlace

inplace相当于

alter table t engine = innodb,algorithm  = copy

online DDL一定是inplace的，反过来，inplace不一定是online的。

alter table ；analyze table ; optimize table 异同点

alter table 相当于重建表

analyze table 重建索引

optimize table 相当于 recreate+analyze