开发者社区> 欢少的成长之路> 正文

面试高频:为什么数据删了,表空间不变呢?

简介: 大家好前面我们大概了解了为什么MySQL在查询数据的时候,有些时候会 "抖" 一下。以及分析了刷脏页的策略问题以及连坐机制。今天介绍一下为什么delete from表名,表的大小还是没有变小!
+关注继续查看

表结构存储


innodb表主要包含两个部分。一个部分是表结构的定义,另一部分是数据。在MySQL8.0之前表结构都是存在后缀 .fm 的文件里。而 MySQL 8.0 版本,则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小,所以我们今天主要讨论的是表数据。下列地址就是数据表数据的存储地方。

C:\Program Files\MySQL\MySQL Server 5.7\data


innodb_file_per_table

这个是MySQL的参数,用来控制表数据是存在共享表空间里,还是单独的文件。

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

image.png

我建议是还是存在一个文件里,这样便于管理。而且对性能的影响也不会很大。从 MySQL 5.6.6 版本开始,它的默认值就是 ON 了。而当你不需要这个表的时候,通过 drop table 命令,系统就会直接删除这个文件。而如果是放在共享表空间中,即使表删掉了,空间也是不会回收的。


我们接下来的讨论话题也是由这里展开的。请把这里了解清楚!


数据删除流程

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

我们要彻底搞明白这个问题的话,就要从数据删除流程说起了。

删除数据的操作不是直接删除磁盘IO上的数据。而且通过采用删除标记的方式删除数据。可以理解成断开引用。但是这里的断开引用不止是断开那么简单。断开了之后他不会释放空间,而是等下次插入数据的时候继续复用这块空间,这样就减少了开辟空间的消耗。

上面说到复用空间!这里的复用空间是有一个规则的。如下图,如果删除的那个数据是在300-700之间,并且插入的那个值的ID也是 300-700之间时,才会去复用这个空间,如果不是这个范围的就不会复用此空间。

image.png

下面介绍一下如何会复用任何空间。只有当用户删除了这一整页数据的时候,插入任何一个数据才会进行复用当前空间。


delete from user where 文件大小=4K

综上所述:删除数据只是把当前位置标记为可复用 但是磁盘大小是不会变的。也就是说通过 delete 命令是不能回收表空间的。这些可以复用,而没有被使用的空间,看起来就像是 “空洞”

实际上,不止删除会造成空洞现象,插入数据也会造成空间现象。这个是非常不好的。

借助一下丁奇老师画的图。如果数据是按照索引递增顺序插入的,那么索引是紧凑的。但如果数据是随机插入的,就可能造成索引的数据页分裂。

假设下图中 page A 已经满了,这时我要再插入一行数据,会怎样呢?


image.png


可以看到,由于 page A 满了,再插入一个 ID 是 550 的数据时,就不得不再申请一个新的页面 page B 来保存数据了。页分裂完成后,page A 的末尾就留下了空洞(注意:实际上,可能不止 1 个记录的位置是空洞)。

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

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

而重建表,就可以达到这样的目的。


重建表

你可以新建一个表A与表B使他们结构相同。然后按照主键 ID 递增的顺序,把数据一行一行地从表 A 里读出来再插入到表 B 中。

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

另一种方案也可以通过alter table A engine=InnoDB 命令来重建表。在 MySQL 5.5 版本之前,这个命令的执行流程跟我们前面描述的差不多,区别只是这个临时表 B 不需要你自己创建,MySQL 会自动完成转存数据、交换表名、删除旧表的操作。

重建表最耗时的地方不是新建表,而是把表A的数据转到表B的时候,会有一个数据插入的过程。这个过程是非常耗时的。而且插入过程中不能对表A进行任何新增修改删除操作。否则数据将会丢失或者乱掉。

因此可以这么说,MySQL5.5之前的重建表不是online()的。 online是啥,我们下面继续介绍!


Online 和 Inplace


Inplace方式

这是原生MySQL 5.5,以及innodb_plugin中提供的方式。所谓Inplace,也就是在原表上直接进行,不会拷贝临时表。相对于Copy Table方式,这比较高效率。原表同样可读的,但是不可写。

Online方式

这是MySQL 5.6以上版本中提供的方式,也是今天我们重点说明的方式。无论是Copy Table方式,还是Inplace方式,原表只能允许读取,不可写。对应用有较大的限制,因此MySQL最新版本中,InnoDB支持了所谓的Online方式DDL。与以上两种方式相比,online方式支持DDL时不仅可以读,还可以写,对于dba来说,这是一个非常棒的改进。

online的核心就是,重建期间,可以通过row log达到在线online操作。如下图!图3 图4

Copy Table方式(了解即可)

这是InnoDB最早支持的方式。顾名思义,通过临时表拷贝的方式实现的。新建一个带有新结构的临时表,将原表数据全部拷贝到临时表,然后Rename,完成创建操作。这个方式过程中,原表是可读的,不可写。但是会消耗一倍的存储空间。

分析

我们先介绍一下online的流程吧

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

image.png

图 3 改锁表 DDL

image.png

图 4 Online DDL

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

图4中在进行insert R7 的时候也是需要获取MDL写锁的。这种常规操作没啥好讲的。真正有意思的是下面这段。这个写锁在真正拷贝数据之前就退化成读锁了。 写锁还是退化成读锁?

online的alter为什么会退化?为了实现 Online,MDL 读锁不会阻塞增删改操作

这里我相信肯定肯定会有人疑问了。用读锁为什么不干脆直接解锁呢?为了保护自己,禁止其他线程对这个表同时做 DDL。

继续介绍这个流程吧。图3中的tmp 是一个临时表,这是由Server层自动创建的。而图4中的tmp_file 这个是由innodb创建的,整个过程都是在innodb完成的。与图3的Server相比,没有把数据挪动到临时表,是一个“原地”操作,这就是“inplace”名称的来源。

所以如果你的内存为1TB,你要处理一个2TB的表。能不能用inplace的DDL呢?答案是不行的,因为inplace是占用内存空间的,如果转储的空间大于自身空间。就会执行失败。

我们用SQL表示一下图3与图4的区别

alter table t engine=innodb,ALGORITHM=inplace;  图4
alter table t engine=innodb,ALGORITHM=copy;     图3

当使用 ALGORITHM=copy 的时候,表示的是强制拷贝表,对应的流程就是图 3 的操作过程。

区别

  1. inplace 会阻塞增删改操作,是非 Online 的。
  2. DDL 过程如果是 Online 的,就一定是 inplace 的;
  3. 反过来未必,也就是说 inplace 的 DDL,有可能不是 Online 的。截止到 MySQL 8.0,添加全文索引(FULLTEXT index)和空间索引 (SPATIAL index) 就属于这种情况。
  4. Online DDL 的方式是可以考虑在业务低峰期使用的,而 MySQL 5.5 及之前的版本,这个命令是会阻塞 DML 的


结尾总结


大概介绍了收缩表空间的方法。重建表的两种实现方式。delete 掉表里面不用的数据的话,表文件的大小是不会变的,你还要通过 alter table 命令重建表,才能达到表文件变小的目的。


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
如何设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云安全组设置详细图文教程(收藏起来) 阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程。阿里云会要求客户设置安全组,如果不设置,阿里云会指定默认的安全组。那么,这个安全组是什么呢?顾名思义,就是为了服务器安全设置的。安全组其实就是一个虚拟的防火墙,可以让用户从端口、IP的维度来筛选对应服务器的访问者,从而形成一个云上的安全域。
18582 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
27727 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,大概有三种登录方式:
12968 0
阿里云服务器安全组设置内网互通的方法
虽然0.0.0.0/0使用非常方便,但是发现很多同学使用它来做内网互通,这是有安全风险的,实例有可能会在经典网络被内网IP访问到。下面介绍一下四种安全的内网互联设置方法。 购买前请先:领取阿里云幸运券,有很多优惠,可到下文中领取。
21935 0
阿里云服务器ECS登录用户名是什么?系统不同默认账号也不同
阿里云服务器Windows系统默认用户名administrator,Linux镜像服务器用户名root
15292 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
19980 0
腾讯云服务器 设置ngxin + fastdfs +tomcat 开机自启动
在tomcat中新建一个可以启动的 .sh 脚本文件 /usr/local/tomcat7/bin/ export JAVA_HOME=/usr/local/java/jdk7 export PATH=$JAVA_HOME/bin/:$PATH export CLASSPATH=.
14855 0
+关注
欢少的成长之路
有物流,电商经验,RocketMQ领域专家,csdn/掘金等平台优质作者,就职于物流企业Java开发岗位
98
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载