MySQL 笔记整理(13) --为什么数据表删掉一半,表文件大小不变?

发布时间:2019-03-29 22:00:06发布者:Mr.Zhang阅读(109)

笔记记录自林晓斌(丁奇)老师的《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

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

 





本文转自博客园,原文地址:https://www.cnblogs.com/dogtwo0214/p/10609284.html