MySQL 笔记整理（13） --为什么数据表删掉一半，表文件大小不变？

笔记记录自林晓斌（丁奇）老师的《mysql实战45讲》

（本篇内图片均来自丁奇老师的讲解，如有侵权，请联系我删除）

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

　　我们还是以mysql中应用最广泛的innodb引擎为基础来展开讨论。一个表中包含两部分：表结构定义和数据。在mysql8.0版本以前，表结构是以.frm为后缀的文件存储的。而在mysql8.0版本，已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小，索引我们今天主要讨论的是表数据。

参数innodb_file_per_table

　　表数据既可以放在共享表空间里，也可以单独存储在文件里。这个行为是由参数innodb_file_per_table控制的。off时表的数据存放在共享表空间里，也就是跟数据字典放在一起。on时每个表存储在一个以.ibd为后缀的文件中。从mysql5.66版本开始，这个值默认是on了。建议你无论使用mysql的哪个版本都将这个值设置为on，因为单独存储为文件的表更方便管理。而且，当你不需要的时候，通过drop table命令，系统就会直接删除这个文件。而如果是在共享表空间中，即使表删掉了，空间也是不会回收的。

　　我们接下来的讨论都是基于这个设置展开的。（innodb_file_per_table设置为on）。

数据删除流程：

　　我们在删除整个表的时候，可以使用drop table命令回收表空间。但是，我们遇到的更多的是删除某些行，这时就遇到了我们文章开头的问题：表中的数据被删除了，但是表空间却没有被回收。

 图1 b+树索引示意图

　　我们之前有提到过，innodb里的数据是用b+树的结构组织的。如果要删除r4这条记录，innodb引擎会把r4这个记录标记为删除。如果之后要插入一个id在300~600之间的记录时，可能会复用这个位置。但是，磁盘的大小不会缩小。

　　我们知道，innodb的数据是按页存储的，如果我们删掉一个数据页上的所有数据会怎样呢？答案是，整个数据页都可以被复用。数据页复用与行记录的复用不同，记录的复用只限于符合范围条件的数据。比如上面这个例子中，如果插入的id是400则可以复用，如果id是800就不能复用记录的空间。而当整个数据页都被删掉时，这个数据页可以被复用到任何位置。比如page a上的所有记录被删除时，如果要插入一条id=50的记录并需要使用新页时，page a是可以被复用的。

　　进一步的说，如果我们使用delete命令把整个表的数据删除，所有的数据页都会被标为可复用，但是磁盘上，文件不会变小。你现在知道了，delete命令其实只是把记录的位置，活着数据页标记为“可复用”，但磁盘的大小是不会改变的。也就是说，通过delete命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

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

　　如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。还使用图1做例子，假设page a已经满了，如果我要插入一条id=550的记录，由于索引的组织关系，它应该在r4与r5之间。但由于page a已满，这个插入操作会导致page a分裂成两个新的数据页来存储数据。并且值得注意的是，我们之前假定page a已满，即这个页可以存放3条记录(只是个假设，实际上会远多于三条记录)，分裂之后的两个新的数据页每个都只存放了两条记录。这两个新的数据页上剩下的空间就是空洞了。实际上，可能不止1个记录的位置是空洞。

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

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

 重建表：

　　你可以使用alter table a engine=innodb命令来重建表。mysql会自动完成转存数据，交换表名，删除旧表的操作。在mysql5.6版本之前，当在重建表的过程中，如果向旧表插入数据，会造成数据丢失，因此整个ddl过程中，旧表是不能有更新的。也就是整个ddl不是online的。而在这个版本开始引入了online ddl，对这个操作流程做了优化。

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

　　这个过程还存在一个细节。我们知道，alter语句在启动的时候需要获取mdl写锁。这样还能叫online嘛？其实，这个写锁在真正拷贝数据之前就会退化成读锁。为什么要退化呢？为了实现online，mdl读锁不会阻塞增删改操作。那么为什么不直接就用读锁呢？为了保护自己，禁止除自身外其他线程对这个表同时做ddl。而相对来讲，这个过程中最耗时的是拷贝数据到临时表的过程。因此对于整个ddl过程来说，锁的时间非常短，可以认为是online的。

　　需要额外说明的是，对于大表来说，这个操作很消耗io和cpu资源，因此，如果是线上服务，你要小心地控制操作时间。

online和inplace:

　　对于非online模式，在重建表a的时候，会生成一个tmp table用来存放导出的数据。这个一个临时表，是在server层创建的。而online模式，表a重建出来的数据是放在“tmp_file”里的，这个临时文件是innodb在内部创建出来的。整个ddl过程都在innodb内部完成。对于server层来说，没有把数据挪动到临时表，是一个“原地”操作，这就是“inplace”名称的来源。因此，如果你有一个1tb的表，现在磁盘间是1.2tb，能不能做一个inplace的ddl呢？答案是不能。因为，tmp_file也是要占用临时空间的。我们重建表的语句alter table t engine = innodb，其实隐含的含义是：alter table t engine = innodb，algorithm=inplace;跟inplace对应的是拷贝表的方式，用法是alter table t engine = innodb，algorithm=copy;而强制拷贝表即off line 方式。
　　因此，举一个例子，我要给innodb表的一个字段加全文索引，写法是alter table t add fulltex(filed_name)；整个过程是inplace的，但会阻塞增删改操作，是非online的。

　　ddl过程如果是online的，就一定是inplace的。
　　反过来未必，也就是说inplace的ddl，有可能不是online的。截止到mysql8.0，添加全文索引（fulltext index）可空间索引（spatial index）就属于这种情况。

上期问题：

　　一个内存配置为128gb，innodb_io_capacity设置为20000的大规格实例，正常会建议你将redo log设置成4个1gb的文件，但是如果你配置时不小心将redo log设置为了4个100m的文件，会发生什么情况呢？为什么呢？

　　对于一个高配置的机器，如果redo log设置太小，很快就会被写满。也就是会不停地去移动redo log中的checkpoint，这个checkpoint可以表示哪些内容是已经更新到硬盘可以去掉的位置。而要移动check point就要去刷脏页，这时系统不得不停止所有更新。所以你会看到磁盘压力很小，但是数据库出现间歇性的性能下跌。

问题：

　　如果有一个情况是这样的：想要收缩表空间，结果适得其反，看上去是：

1. 一个表t文件大小为1tb；
2. 对这个表执行alter table t engine = innodb；
3. 执行完成后，空间不仅没有变小，还稍微大了一点，变为了1.01tb

　　请问这是什么原因导致的呢？