9.数据表的删除为什么大小不变呢？

9.数据表的删除为什么大小不变呢？

一个 InnoDB 表包含两部分，即：表结构定义和数据。在 MySQL 8.0 版本以前，表结构是存在以.frm 为后缀的文件里。而 MySQL 8.0 版本，则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小，所以我们今天主要讨论的是表数据。

参数 innodb_file_per_table表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数 innodb_file_per_table 控制的：

• 这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；

• 这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。

  我建议你不论使用 MySQL 的哪个版本，都将这个值设置为 ON。因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

也就是说如果innodb_file_per_table这个参数是 ON 代表是数据存储在-.IDB的文件中，那么drop删除表就会释放空间。

如果是OFF的话，在数据表空间存储的表数据，即使DROP也不会释放空间，

但是最多的情况是删除某些行，用delete

我们要删掉 R4 这个记录，InnoDB 引擎只会把 R4 这个记录标记为删除。如果之后要再插入一个 ID 在 300 和 600 之间的记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小。

现在，你已经知道了 InnoDB 的数据是按页存储的，那么如果我们删掉了一个数据页上的所有记录，会怎么样？答案是，整个数据页就可以被复用了。并且不用看范围

就算是，如果我们用 delete 命令把整个表的数据删除呢？结果就是，所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小。

也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

不止是删除数据会造成空洞，插入数据也会。如下图所示

在数据页中间插入数据的时候，因为数据页此时已满，所以会造成数据页的分裂，然后就会造成空洞。

更新索引上的值，可以理解为删除一个旧的值，再插入一个新值。不难理解，这也是会造成空洞的。

也就是说，经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。

所以说一张表如果重复的增删改就会造成空洞，并且页不会缩小表数据的大小。

那么重建表的话就会使空洞的表空间得以利用

试想一下，如果你现在有一个表 A，需要做空间收缩，为了把表中存在的空洞去掉，你可以怎么做呢？你可以新建一个与表 A 结构相同的表 B，然后按照主键 ID 递增的顺序，把数据一行一行地从表 A 里读出来再插入到表 B 中。由于表 B 是新建的表，所以表 A 主键索引上的空洞，在表 B 中就都不存在了。显然地，表 B 的主键索引更紧凑，数据页的利用率也更高。如果我们把表 B 作为临时表，数据从表 A 导入表 B 的操作完成后，用表 B 替换 A，从效果上看，就起到了收缩表 A 空间的作用。

alter table A engine=InnoD

在 MySQL 5.5 版本之前，这个命令的执行流程跟我们前面描述的差不多，区别只是这个临时表 B 不需要你自己创建，MySQL 会自动完成转存数据、交换表名、删除旧表的操作。花时间最多的步骤是往临时表插入数据的过程，如果在这个过程中，有新的数据要写入到表 A 的话，就会造成数据丢失。因此，在整个 DDL 过程中，表 A 中不能有更新。也就是说，这个 DDL 不是 Online 的。

MySQL 5.6 版本开始引入的 Online DDL，对这个操作流程做了优化。

1.建立一个临时文件，扫描表 A 主键的所有数据页；

2.用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；

3.生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；

4.临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态；

5.用临时文件替换表 A 的数据文件。

需要补充说明的是，上述的这些重建方法都会扫描原表数据和构建临时文件。对于很大的表来说，这个操作是很消耗 IO 和 CPU 资源的。因此，如果是线上服务，你要很小心地控制操作时间。如果想要比较安全的操作的话，我推荐你使用 GitHub 开源的 gh-ost 来做。

• 从 MySQL 5.6 版本开始，alter table t engine = InnoDB（也就是 recreate）默认的就是上面图 4 的流程了；

• analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；

• optimize table t 等于 recreate+analyze。小结

1：为啥删除了表的一半数8据，表文文件大小没变化？

因为delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也可以认为是一种逻辑删除，所以物理空间没有实际释放，只是标记为可复用，表文件的大小当然是不变的啦！

2：表的数据信息存在哪里？

表数据信息可能较小也可能巨大无比，她可以存储在共享表空间里，也可以单独存储在一个以.ibd为后缀的文件里，由参数innodb_file_per_table来控制，老师建议总是作为一个单独的文件来存储，这样非常容易管理，并且在不需要的时候，使用drop table命令也能直接把对应的文件删除，如果存储在共享空间之中即使表删除了空间也不会释放。

3：表的结构信息存在哪里？

首先，表结构定义占有的存储空间比较小，在MySQL8.0之前，表结构的定义信息存在以.frm为后缀的文件里，在MySQL8.0之后，则允许把表结构的定义信息存在系统数据表之中。

系统数据表，主要用于存储MySQL的系统数据，比如：数据字典、undo log(默认)等文件

4：如何才能删除表数据后，表文件大小就变小？

重建表，消除表因为进行大量的增删改操作而产生的空洞，使用如下命令：

1：alter table t engine=InnoDB

2：optimize table t( 等于 recreate+analyze)。

3：truntace table t (等于drop+create)

5：空洞是啥？咋产生的？

空洞就是那些被标记可复用但是还没被使用的存储空间。

使用delete命令删除数据会产生空洞，标记为可复用

插入新的数据可能引起页分裂，也可能产生空洞

修改操作，有时是一种先删后插的动作也可能产生空洞

count() 的实现方式你首先要明确的是，在不同的 MySQL 引擎中，count() 有不同的实现方式。MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count() 的时候会直接返回这个数，效率很高；而 InnoDB 引擎就麻烦了，它执行 count() 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。