【学习计划】《MySQL实战45讲》13 | 为什么表数据删掉一半，表文件大小不变？

---

一、新名词

1. “Online DDL”

重建表的过程中，允许对表做增删改操作。

Online DDL，重建表的流程：

1. 建立一个临时文件，扫描表A主键的所有数据页；
2. 用数据页中表A的记录生成B+树，存储到临时文件中；
3. 生成临时文件的过程中，将所有对A的操作记录在一个日志文件（row log）中，对应的是图中state2的状态；
4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表A相同的数据文件，对应的就是图中state3的状态；
5. 用临时文件替换表A的数据文件。
   
   特别注意：重建方法都会扫描原表数据和构建临时文件。对于很大的表来说，这个操作是很消耗IO和CPU资源的。因此，如果是线上服务，你要很小心地控制操作时间。如果想要比较安全的操作的话，推荐使用GitHub开源的gh-ost来做。

2. “inplace”

表A重建出来的数据是放在“tmp_file”里的，这个临时文件是InnoDB在内部创建出来的。整个DDL过程都在InnoDB内部完成。对于server层来说，没有把数据挪动到临时表，是一个“原地”操作。

---

二、新指令

alter table

重建表

alter table A engine=InnoDB;

特别注意：MySQL 5.5及之前的版本，这个命令是会阻塞DML

---

三、新参数

1. innodb_file_per_table

表数据的存储方式

• 这个参数设置为OFF表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
• 这个参数设置为ON表示的是，每个InnoDB表数据存储在一个以 .ibd为后缀的文件中。

---

四、新知识点

1. drop table 和 delete

• drop table 命令回收表空间。
• delete 命令不能回收表空间。delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。

2. 数据页的复用

数据页的复用：可以复用到任何位置。

3. 删除数据造成的空洞

当有ID（300,400,500）的数据时，删除ID（400），插入ID（800）时，ID（400）的位置不会被复用。

4. 插入数据造成的空洞

page A满了，ID（300,400,500）。这时插入ID（450），会申请一个新的页面page B来保存数据了。页分裂完成后，page A的末尾就留下了空洞。此时page A，ID（300,400）；page B，ID（450,500）。

5. Online 和 inplace 的关系

• DDL过程如果是Online的，就一定是inplace的；
• 反过来未必，也就是说inplace的DDL，有可能不是Online的。截止到MySQL 8.0，添加全文索引（FULLTEXT index）和空间索引(SPATIAL index)就属于这种情况。

6. optimize table、analyze table和alter table这三种方式重建表的区别。

• 从MySQL 5.6版本开始，alter table t engine = InnoDB（也就是recreate）默认的就是上面图的流程了；
• analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了MDL读锁；
• optimize table t 等于recreate+analyze。

---

五、小结

数据库中收缩表空间的方法

• 如果要收缩一个表，只是delete掉表里面不用的数据的话，表文件的大小是不会变的，还要通过alter table命令重建表，才能达到表文件变小的目的。
• 重建表的两种实现方式，Online DDL的方式是可以考虑在业务低峰期使用的，而MySQL 5.5及之前的版本，这个命令是会阻塞DML的，这个需要特别小心。