《MySQL实战45讲》——学习笔记13 “数据删除流程、表空间释放、重建表过程、inplace&online DDL“

本篇从问题"为什么表数据删掉一半，表文件大小不变的原因？"引入，介绍了MYSQL据库表的空间回收相关的知识，内容包含：数据删除流程、数据页和记录的复用、数据页空洞（删除/插入/更新都会导致）、以及通过重建表来优化表空间减小空洞，最后介绍了下online 和inplace的区别；

MySQL的表结构和表数据存储

一个InnoDB表包含两部分：表结构定义和表数据；

在MySQL8.0版本以前，表结构是存在以.frm为后缀的磁盘文件里；而MySQL 8.0版本，则已经允许把表结构定义放在系统数据表中了；

表数据既可以存在共享表空间里，也可以是单独的文件，这个行为是由参数innodb_file_per_table控制：若设置为OFF，则表的数据放在系统共享表空间，也就是跟数据字典放在一起；若设置为ON，则每个InnoDB表数据各自单独存储在一个以.ibd为后缀的文件中；

默认值是ON，并且也建议使用默认值；因为——一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过drop table命令，系统就会直接删除这个文件；而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的；

数据删除流程与数据"空洞"

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

InnoDB的数据是按页存储的，如果我们删掉了一个数据页上的所有记录，会怎么样？同样的，整个页都可以被复用；

整个页的复用与页上被标记"删除"的数据的复用是不同的

记录的复用，只限于符合范围条件的数据；

比如上面的这个例子，R4这条记录被删除后，如果插入一个ID是400的行，可以直接复用这个空间；但如果插入的是一个ID是800的行，就不能复用这个位置了；因为ID=800的行的父节点是ID=700，不能复用已经被ID=300的父节点指向的PageA；而当整个页从B+树里面摘掉以后，可以复用到任何位置；

如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用；或者一次删除了整个页上的所有数据，这一整个数据页也会被标记为可复用；

无论是删除记录还是整个页数据删除，磁盘上的文件不会变小，因为delete只是标记为已删除，而不是真正的物理删除，即表空间数据回收；

除了删除，插入和更新操作也会导致数据"空洞"

• 随机插入

如果数据是按照索引递增顺序插入的，页满申请新页时是不移动原有页面的任何记录的，因此索引是紧凑的；这里的"按照索引递增顺序插入"并不是说一定要自然数值连续，而是大小关系连续即可，如 1 2 4 7 9；这也是为什么推荐使用自增主键的原因；

如图，传统B+树页面分裂是按照原页面中50%的数据量进行分裂，随机插入就可能导致原先的数据页放不下了，造成索引的数据页分裂，从而导致分裂后的数据页产生了"空洞"；

• 更新操作

更新索引值，可以理解为删除一个旧的值，再插入一个新值；删除会导致"空洞"，并且如果新插入的位置所在的数据页触发了页分裂，也是会导致"空洞"的；

重建表

通过上面的分析可知，经过大量增/删/改的表，都是可能是存在空洞的；所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的，同时数据更加集中可以提升查询效率；而重建表，就可以达到这样的目的；

你可能遇到这样的情况：某个存在很久的表的SQL执行较慢，表的数据量不大但表的数据会频繁的增删改，尝试执行一次optimize table后，性能就好了很多；这个optimize table其实也是重建表的一种方式；

可以通过alter table t engine=InnoDB重建表空间，释放"空洞"；MySQL5.6之前要求在整个DDL过程中锁表，阻塞更新，也就是说这个DDL不是Online的，如下：

而在MySQL5.6版本开始引入的Online DDL，对这个操作流程做了优化；如下：

上述两个图的不同之处在于，由于日志文件记录和重放操作这个功能的存在，这个方案在重建表的过程中，允许对表A的数据做增删改操作，这也就是OnlineDDL名字的来源；

Online DDL

在中有介绍，DDL之前是要拿MDL（元数据锁）写锁的，此情况下，是不允许其他线程对表执行增删改查的；实际上，alter语句仅在启动的时候需要获取MDL写锁，但是这个写锁在真正拷贝数据之前就退化成MDL读锁读锁了，目的是为了实现Online，因为MDL读锁不会阻塞增删改操作；

一开始获取写锁，目的是保证在一些准备动作（如row log的创建）还未完成之前，主表不允许做任何修改或读取；之后降级是为了允许其他线程DML，因为这时row log文件已经就绪，他们的DML都会进入row log文件中；

开始复制数据的时候退化成读锁，不解锁的原因是防止有其他线程尝试获取DML写锁，从而对阻止其它线程修改表结构；最后把row log的数据复制到临时表的时候还会获取一次MDL写锁，防止其他DML操作影响数据的一致性；

需要补充说明的是，重建表时会扫描原表数据和构建临时文件；对于很大的表来说，这个操作是很消耗IO和CPU资源的；因此，如果是线上服务，你要很小心地控制操作时间，避免业务高峰；或者使用离线表操作；

Online 和 inplace

对于Online，有另一个跟DDL有关的、容易混淆的概念inplace；其实这两个概念不是描述同一个维度的问题的；

在重建表的过程中，如果把表A中的数据导出来的存于tmp_table（第一张图），这是一个临时表，是在MySQL的server层创建的；如果表 A 重建出来的数据是放在“tmp_file”里的（第而张图），这个临时文件是InnoDB在内部创建出来的，server 层来说，没有把数据挪动到临时表，是一个“原地”操作，这就是“inplace”名称的来源；

Online和inplace的概念主要关注这里建"临时表"是通过server还是innoDB，而无论哪种"临时表"都是会占用存储空间的；举个例子，如果你有一个1TB的表，现在磁盘间是1.2TB，能不能做一个inplace的DDL呢？答案是不能；因为，tmp_file也是要占用临时空间的；

这样说你可能会觉得，inplace跟Online是不是没什么区别？其实不是的，只是在重建表这个逻辑中差不多；如果说这两个逻辑之间的关系是什么的话，可以概括为：

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

optimize table | analyze table | alter table 区别

• 从MySQL5.6版本开始，alter tablet engine=InnoDB（也就是recreate）默认的就是上面图4的流程了；
• analyze tablet其实不是重建表，只是对表的索引信息做重新统计，为了让优化器评估执行性能时能更加准确点，这个过程没有修改数据，仅加了MDL读锁；
• optimize tablet等于recreate+analyze；

问题

假设碰到了一个“想要收缩表空间，结果适得其反”的情况，看上去是这样的：一个表t文件大小为1TB；对这个表执行alter tablet engine=InnoDB；发现执行完成后，空间不仅没变小，还稍微大了一点儿，比如变成了1.01TB；你觉得可能是什么原因呢？

答案：

1. 首先这个表本身就已经几乎没有"空洞"了，比如说刚刚做过一次重建表操作；
2. 在DDL期间，如果刚好有外部的DML在执行，这期间可能会引入一些新的空洞；
3. 存在一个机制：在重建表的时候，InnoDB不会把整张表占满，每个页留了1/16给后续的更新用；也就是说，其实重建表之后不是“最”紧凑的；

假如是这么一个过程可能导致上述现象：
（1）将表t重建一次；
（2）插入一部分数据，但是插入的这些数据，用掉了一部分的预留空间；或者DDL期间的DML引入了一些新的"空洞"；
这种情况下，再重建一次表t，就可能会出现问题中的现象；

 下篇文章：待定

本章参考：13 | 为什么表数据删掉一半，表文件大小不变？-极客时间