02.17 Day 29 - 数据库表的空间回收

大家好，我是 Snow Hide，作为《MySQL 实战》这个专栏的学员之一，这是我打卡的第 29 天，也是我第 92 次进行这种操作。

今天我温习了该专栏里一篇叫《为什么表数据删掉一半，表文件大小不变？》的文章。

关键词总结：参数 innodb_file_per_table（推荐做法）、数据删除流程（两种复用的区别、删除数据造成的空洞、插入数据造成的空洞）、重建表（Online DDL 优化过的流程、Online DDL 最耗时的过程、线上的推荐做法）、Online 和 inplace（重建表时语句的隐含意思、给 InnoDB 表字段加全文索引的情况、两个逻辑之间的关系、三种重建表的区别）。

 

所学总结：

 

参数 innodb_file_per_table

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

从 MySQL 5.6.6 版本开始，它的默认值就是 ON 了。

推荐做法

将 innodb_file_per_table 设置为 ON。
 

数据删除流程

两种复用的区别

• 删掉了一个数据页上的所有记录，整个数据页就可以被复用了；
• 记录的复用，只限于符合范围条件的数据。

删除数据造成的空洞

delete 命令其实只是把记录的位置，或者数据页标记为了 “可复用”，但磁盘文件大小是不变的。通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是 “空洞”。

插入数据造成的空洞

• 如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂；
• 重建表可以将增删改查产生的空洞去掉，从而达到搜索表空间的目的。
   

重建表

Online DDL 优化过的流程

1. 建立一个临时文件，扫描表主键的所有数据页；
2. 用数据页中表的记录生成 B+ 树，存储到临时文件中；
3. 生成临时文件的过程中，将所有对表的操作记录在一个日志文件（row log）中；
4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表相同的数据文件；
5. 同临时文件替换表的数据文件。

Online DDL 最耗时的过程

拷贝数据到临时表的过程，这个步骤的执行期间可以接受增删改操作。所以，相对于整个 DDL 过程来说，锁的时间非常短。对业务来说，就可以认为是 Online 的。

线上的推荐做法

推荐使用 GitHub 开源的 gh-ost 来做。
 

Online 和 inplace

重建表时语句的隐含意思

alter table t engine=innodb,ALGORITHM=inplace;

跟以上对应的是拷贝表：

alter table t engine=innodb,ALGORITHM=copy;

给 InnoDB 表字段加全文索引的情况

alter table t add FULLTEXT(field_name);

过程是 inplace 的，但会阻塞增删改操作，是非 Online 的。

两个逻辑之间的关系

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

三种重建表的区别

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

末了

重新总结了一下文中提到的内容：数据库中收缩表空间的方法、delete 不会使表文件变小、通过 alter table 命令来重建表以使表文件变小。