MySQL删除数据前后,表文件大小相同,原因是...

本文阅读 9 分钟

  表数据既可以存在共享表空间里,也可以是单独的文件。这个行为是由参数innodb_file_per_table控制的:

  1. 设置为OFF:表的数据放在系统共享表空间,也就是跟数据字典放在一起;
  2. 设置为ON:每个InnoDB表数据存储在一个以 .ibd为后缀的文件中。

从MySQL 5.6.6版本开始,它的默认值是ON。

  一个表单独存储为一个文件更容易管理,而且在不需要这个表的时候,通过drop table命令,系统就会直接删除这个文件。而如果是放在共享表空间中,即使表删掉了,空间也是不会回收的。

  所以,将innodb_file_per_table设置为ON,是推荐做法,接下来的讨论都是基于这个设置展开的

  在删除整个表的时候,可以使用drop table命令回收表空间。但我们遇到的更多的删除数据的场景是删除某些行,这时就会有:表中的数据被删除了,但是表空间却没有被回收。

  InnoDB里的数据都是用B+树的结构组织的。 img

  假设要删掉R4,InnoDB引擎只会把R4这个记录标记为删除。如果之后要再插入一个ID在300和600之间的记录时,可能会复用这个位置。磁盘文件的大小并不会缩小。   如果删掉了一个数据页上的所有记录,整个数据页就可以被复用了。

2.1 数据页的复用不同于记录的复用

  记录的复用,只限于符合范围条件的数据。比如R4这条记录被删除后,如果插入一个ID是400的行,可以直接复用这个空间。但如果插入的是一个ID是800的行,就不能复用这个位置。

  当整个页从B+树里面摘掉以后,可以复用到任何位置。如果将数据页page A上的所有记录删除以后,page A会被标记为可复用。这时候如果要插入一条ID=50的记录需要使用新页的时候,page A是可以被复用的。   如果相邻的两个数据页利用率都很小,系统就会把这两个页上的数据合到其中一个页上,另外一个数据页就被标记为可复用。

  如果我们用delete命令把整个表的数据删除,结果是所有的数据页都会被标记为可复用。但是磁盘上文件不会变小。这些可以复用,而没有被使用的空间,看起来就像是“空洞”。

2.2 不止是删除数据会造成空洞,插入数据也会

  如果数据是按照索引递增顺序插入的,那么索引是紧凑的。但如果数据是随机插入的,就可能造成索引的数据页分裂。

  如果page A已经满了,这时要再插入一行数据 img

  由于page A满了,再插入一个ID是550的数据时,会申请一个新的页面page B来保存数据。页分裂完成后,page A的末尾就留下了空洞。

  此外,更新索引上的值,可以理解为删除一个旧的值,再插入一个新值。也会造成空洞。

  经过大量增删改的表,都是可能是存在空洞的,重建表是能够把这些空洞去掉,就能达到收缩表空间的目的。

2.3 重建表

有以下几种做法。

  新建一个与表A结构相同的表B,然后按照主键ID递增的顺序,把数据一行一行地从表A里读出来再插入到表B中。表B的主键索引更紧凑,数据页的利用率也更高。数据从表A导入表B的操作完成后,用表B替换A,从效果上看,起到了收缩表A空间的作用。

  使用alter table A engine=InnoDB命令来重建表。在MySQL 5.5版本之前,这个命令的执行流程跟前面描述的差不多,MySQL会自动完成转存数据、交换表名、删除旧表的操作。

img

  花时间最多的步骤是往临时表插入数据的过程,如果在这个过程中,有新的数据要写入到表A的话,就会造成数据丢失。因此,在整个DDL过程中,表A中不能有更新(非Online)。

MySQL 5.6版本开始引入的Online DDL,对这个操作流程做了优化。引入了Online DDL之后重建表的流程如下:

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

img

  由于日志文件记录和重放操作这个功能的存在,这个方案在重建表的过程中,允许对表A做增删改操作。

  图4的流程中,alter语句在启动的时候需要获取MDL写锁,但是这个写锁在真正拷贝数据之前就退化成读锁。

为什么退化?

  • 为了实现Online,MDL读锁不会阻塞增删改操作。

为什么不直接解锁?

  • 为了保护自己,禁止其他线程对这个表同时做DDL。

上述的这些重建方法都会扫描原表数据和构建临时文件。对于很大的表来说,这个操作是很消耗IO和CPU资源的,所以要在业务低峰期使用。

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

  • 从MySQL 5.6版本开始,alter table t engine = InnoDB默认的是图4的流程;
  • analyze table t 不是重建表,只是对表的索引信息做重新统计,没有修改数据,这个过程中加了MDL读锁;
  • optimize table t 等于recreate+analyze。
  1. 一个表t文件大小为1TB;
  2. 对这个表执行 alter table t engine=InnoDB;
  3. 发现执行完成后,空间不仅没变小,还稍微大了一点儿,比如变成了1.01TB。

  在重建表的时候,InnoDB不会把整张表占满,每个页留了1/16给后续的更新用。其实重建表之后不是“最”紧凑的。假如是这么一个过程:

  1. 将表t重建一次;
  2. 插入一部分数据,但是插入的这些数据,用掉了一部分的预留空间;
  3. 这种情况下,再重建一次表t,就可能会出现问题中的现象。
本文为互联网自动采集或经作者授权后发布,本文观点不代表立场,若侵权下架请联系我们删帖处理!文章出自:https://wangjiawei.blog.csdn.net/article/details/109911226
-- 展开阅读全文 --
安全面试之XSS(跨站脚本攻击)
« 上一篇 07-24

发表评论

成为第一个评论的人

热门文章

标签TAG

最近回复