登录 |  注册
首页 >  数据库 · 存储 >  MySql实战精选笔记 >  为什么数据删除了,表文件大小不变?

为什么数据删除了,表文件大小不变?

首先明确一个概念,innodb表包含两部分,表结构定义和数据,Mysql8.0以前表结构定义存放在.frm为后缀的文件里,而Mysql8.0版本以后允许表结构定义放到系统数据表中,因为表结构定义占用的空间很小,因此我今天主要说是表数据。

日常开发中,当我们删除一个表的的数据的时候,发现表空间大小并不会变小,我们要知道为什么会发生这样神奇的事。

参数innodb_file_per_table

表数据可以存在共享表空间里,也可以在单独的文件中,这个行为由参数innodb_file_per_table控制,这个参数为OFF的时候,说明表的数据存放在系统共享表空间,也就是跟数据字典放在一起,如果这个参数是ON的时候,说明表的数据放到单独的文件中,Mysql5.6.6以后默认就是ON

无论使用哪个版本我们都建议把这个值设置成ON,单独放到一个文件,方便管理,当我们不需要这个表的时候,通过drop table 就可以直接删除这个文件。如果放到系统共享表空间中,即使删除掉了,空间也就是不会回收的。

Mysql数据删除流程

mysql1.png

假如我删除500这个数据,innodb只会把这个记录标记为删除,如果之后要插入400的时候,就会直接复用这个位置,但是磁盘的文件并不变小,当然我如果删除了在跟个pageA的数据,当然也是被复用的,但是数据页的复用和记录的复用是不一样的。记录的复用,只限于符合条件的数据,正如上面的例子,但是如果插入一个800的数据,就不能复用这个位置了,而数据页可以复用任何位置,如上图为例,把pageA的数据全部删除,PageA页就会被标记为可复用,这个时候插入一条id=50的时候,要创建新的数据页的时候,就会复用pageA。

现在我们知道delete 命令只会把记录的位置或数据页标记为可复用,但是磁盘的文件大小不会变小,也就是说delete并不会回收表空间,这个可以进行复用,而没有使用的空间,看起来就像空洞,实际上不止删除会造成空洞,插入数据也会。如果数据按照索引递增顺序插入,那么索引是紧凑,但是如果数据是随机的,就可能存在数据页的分裂,如下图

mysql2.png

我们看到当先插入一个数据的时候,此时要申请一个数据页pageB,来保存数据了,页分裂完成后,PageA尾部就留下了空洞,另外更新索引的值,可以理解为删除一个数据,新增一条数据,不难理解这样也是会导致空洞的。

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

Mysql重建表去掉空洞

试想一下,如果我们需要去掉这些空洞,如何做呢,当然你可以建立一个和A表一样表B,把数据A的数据复制到表B,

由于表B是新建的表,所以表B不存在空洞,显然表B的主键索引更紧凑,数据页利用率也高,如果我们建立一个临时表B,把数据从表A导入表B的操作完成后,用表B替换A,效果上就起到了收缩表A的作用。

我们可以使用下面命令重建表,在mysql5.5版本之前,这个命令的执行流程跟我们前面描述的差不多,区别就是这个临时表B不需要手动创建,MySql会自动完成转存数据,就换表明,删除旧表的操作

mysql3.png

显然上图中最花时间的步骤是往临时表插入数据的过程,如果整个过程中,有新的数据写入是不被允许的,这个就会造成数据丢失,表A不会有数据的更新,这个DDL不是Online的。

但是在mysql5.6版本开始引入Online DDL,这个操作流程做了优化,如下面流程:

  • 建立一个临时表,扫描表A主键的所有数据页

  • 用数据页中表A记录生成B+树,存储到临时文件中,

  • 生成临时文件的过程,将所有对表A的操作应用到日志文件中

  • 临时文件生成后,将日志文件应用到临时文件中,得到一个逻辑数据和表A相同的数据文件

  • 用临时文件替换表A的数据文件

mysql4.png

可以看到上图和之前的不同之处在于,由于日志文件激励和重放操作这个功能的存在,这个方案在重建表的过程中,允许对表A增删改,这就是Online DDL的来源。

之前我们介绍过在执行DDL的时候会先拿到MDL的写锁,但是这个写锁在真正拷贝数据之前就会退化成读锁,因为这样才能实现Online DDL,不会阻塞增删改的操作。

但是为什么不直接接触锁呢,那是因为防止其他线程对这个表进行DDL操作。

而对于一个达标来说,Online DDL最耗时的过程就是拷贝数据,这个步骤执行期间可以增删改,所以相对于真个DDL锁的时间非常短,对业务来说,可以容忍,当然对于大表的重建表,这个操作是很消耗IO和CPU资源的,生产环境要谨慎执行。

Online 和inplace

我们看到第一张状态的图是把表A的数据导出来放到temp_table,这个是临时表,在server层建立的,但是,第二章状态的图是把表A的数据放到临时文件temo_file,这个临时文件是innodb的内部创建出来的,整个DDL过程都在InnoDB内部完成的,对于server层来说,没有把数据挪到临时表,是一个原地操作,这就是inplace名称的来源,但是我们发现在重建表的过程中,inplace和online好像是一个意思,其实他们是不一样的,比如如果我要在innodb中给一个字段加全文多音,写法是

alter table t add fulltext(file_name)

这个过程是inplace的,但是会阻塞增删改操作,是非Online的,

他们的关系如下:

  1. DDL的过程如果是Online,就一定是inplace的

  2. 反过来未必,也就是inplace的DDL,有可能不是online,截止到Mysql8.0,添加全文索引和空间索引就属于这种情况,

这个说明一个重建表的区别

  • mysql5.6版本开始alter table t engin=innodb(recreate),默认就是第二张状态图描述的

  • analyze table t 其实不是重建表,只是对表的索引信息做重新统计,没有修改数据,这个过程中加了MDL读锁

  • aptimize table t =recreate+analyze

上一篇: 为什么有的时候SQL执行很慢“抖一下”
下一篇: 尽量使用count(*)统计条数
推荐文章
  • mysql只支持一种join算法:Nested-LoopJoin(嵌套循环连接),但Nested-LoopJoin有三种变种:SimpleNested-LoopJoin,IndexNested-LoopJoin,BlockNested-LoopJoin(简单-索引-缓冲区)原理:1.SimpleNe
  • redis是一个内存数据库,一旦断电或服务器进程退出,内存数据库中的数据将全部丢失,所以需要redis持久化 redis持久化就是把数据保存在磁盘上,利用永久性存储介质将数据保存,在特定的时间将保存的数据进行恢复的工作机制redis提供两种持久化机制RDB:存储数据结果,关注点在数据AOF:存储操作
  • 通过SQL的执行过程来介绍MySQL的基础结构.     首先有一个user_info表,表里有一个id字段,执行下面这条查询语句:Select * form user_info where i
  • 索引(Index)是帮助MySQL高效获取数据的数据结构,索引的目的在于提高查询效率,就像字典和书籍的目录一样,有了目录,可以帮助你快速查找你需要的内容。可以理解为一个排好序的快速查找数据结构。也就是
  • 说到数据库事务,大家脑子里一定很容易蹦出一堆事务的相关知识,如事务的ACID特性,隔离级别,解决的问题(脏读,不可重复读,幻读)等等,但是可能很少有人真正的清楚事务的这些特性又是怎么实现的,为什么要有四个隔离级别。今天我们就先来聊聊MySQL中事务的隔离性的实现原理,后续还会继续出文章分析其他特性的
  • 前面我们系统了解了一个查询语句的执行流程,并介绍了执行过程中涉及的处理模块。相信你还记得,一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块,最后到达存储引擎。那么,一条更新语句
学习大纲