专栏首页洁癖是一只狗Mysql删除表数据,表文件大小不变

Mysql删除表数据,表文件大小不变

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

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

参数innodb_file_per_table

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

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

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

数据删除流程

假如我删除500这个数据,innodb只会把这个记录标记为删除,如果之后要插入400的时候,就会直接复用这个位置,但是磁盘的文件并不变小,当然我如果删除了在跟个pageA的数据,当然也是被复用的,但是数据页的复用和记录的复用是不一样的

记录的复用,只限于符合条件的数据,正如上面的例子,但是如果插入一个800的数据,就不能复用这个位置了,而数据页可以复用任何位置,如上图为例,把pageA的数据全部删除,PageA页就会被标记为可复用,这个时候插入一条id=50的时候,要创建新的数据页的时候,就会复用pageA,

现在我们知道delete 命令只会把记录的位置或数据页标记为可复用,但是磁盘的文件大小不会变小,也就是说delete并不会回收表空间,这个可以进行复用,而没有使用的空间,看起来就像空洞,

实际上不止删除会造成空洞,插入数据也会.

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

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

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

重建表

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

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

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

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

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

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

可以看到上图和之前的不同之处在于,由于日志文件激励和重放操作这个功能的存在,这个方案在重建表的过程中,允许对表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

本文分享自微信公众号 - 洁癖是一只狗(rookie-dog),作者:洁癖汪

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-09-14

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 一条更新语句如何执行

    其实更新语句和查询语句的流程是基本一样的,但是他其中不一样的是涉及两个日志模块,也就是我们经常提到的redo log(重做日志)和binlog(归档日志)。

    小土豆Yuki
  • 如何解决高并发I/O瓶颈

    在现在这个大数据时代下,IO的性能问题更是尤为突出,IO读写已经成为应用场景的瓶颈,不容我们忽视,今天,我们就深入了解下Java IO在高并发,大数据场景下暴露...

    小土豆Yuki
  • 各大网站视频教程 要不要

    小土豆Yuki
  • 十日大数据参考

    我们将过去10天里播报的大数据新闻,浓缩成12条精选资讯,了解下这个行业的动向吧~ 1. 数说× “各行各业都在装备大数据” 2. 数说内参 ”大数据行业内的...

    数说君
  • 数据推动改革促进文明 《智能时代--大数据和智能革命重新定义未来》

    最近又开始读《智能时代--大数据和智能革命重新定义未来》,这本书是由吴军博士写的,目前读完了第一章,但这篇博客主要写的是序言部分,算是我对机器学习和大数据相关知...

    bye
  • 金融服务领域实时数据流的竞争性优势

    实时数据流为企业提供了激动人心的新机会,以改变其运营方式,利用实时洞察力来推动更好的决策制定并提高运营效率。

    大数据杂货铺
  • 从“大数据”到“智能数据”

    大部分专家都相信可以从巨量的数据中找到宝石和金子。英国牛津大学曾对全球各行业工作者做过一份调查问卷,2/3受访者认为,使用数据和分析软件可以使他们保持竞争优势。...

    华章科技
  • 投稿 | 神策数据CEO桑文锋:传统企业在大数据分析上所面临的关键问题

    <数据猿导读> 实现数据驱动有两个关键环节:一是数据采集,二是数据分析。对于传统企业来说,往往是这两点都不太具备条件。本文将给大家分享传统企业在数据采集和数据分...

    数据猿
  • 【案例】某城商行——数据平台建设项目

    数据猿导读 随着城商行业务的发展,银行内部业务系统的处理日益加重,处理逻辑日益复杂。若以传统方式直接从数据来源系统供给分析类业务系统,而又不影响各个业务系统的处...

    数据猿
  • 大数据相关的职业岗位及主要工作内容

    大数据正在改变全球商业运作方式,随着对合格大数据人才需求的增加,大数据行业的发展空间和待遇也越来越好,很多想转行大数据的入门学习者,不太清楚大数据的...

    加米谷大数据

扫码关注云+社区

领取腾讯云代金券