专栏首页老男孩成长之路面试官:数据库delete表数据,为啥磁盘空间还是被一直占用

面试官:数据库delete表数据,为啥磁盘空间还是被一直占用

最近有个上位机获取下位机上报数据的项目,由于上报频率比较频繁且数据量大,导致数据增长过快,磁盘占用多。

为了节约成本,定期进行数据备份,并通过delete删除表记录。

明明已经执行了delete,可表文件的大小却没减小,令人费解

项目中使用Mysql作为数据库,对于表来说,一般为表结构和表数据。表结构占用空间都是比较小的,一般都是表数据占用的空间。

当我们使用 delete删除数据时,确实删除了表中的数据记录,但查看表文件大小却没什么变化。

Mysql数据结构

凡是使用过mysql,对B+树肯定是有所耳闻的,MySQL InnoDB 中采用了 B+ 树作为存储数据的结构,也就是常说的索引组织表,并且数据时按照页来存储的。因此在删除数据时,会有两种情况:

  1. 删除数据页中的某些记录
  2. 删除整个数据页的内容

表文件大小未更改和mysql设计有关

比如想要删除 R4 这条记录:

InnoDB 直接将 R4 这条记录标记为删除,称为可复用的位置。如果之后要插入 ID 在 300 到 700 间的记录时,就会复用该位置。由此可见,磁盘文件的大小并不会减少。

通用删除整页数据也将记录标记删除,数据就复用用该位置,与删除默写记录不同的是,删除整页记录,当后来插入的数据不在原来的范围时,都可以复用位置,而如果只是删除默写记录,是需要插入数据符合删除记录位置的时候才能复用。

因此,无论是数据行的删除还是数据页的删除,都是将其标记为删除的状态,用于复用,所以文件并不会减小。

那怎么才能让表大小变小

DELETE只是将数据标识位删除,并没有整理数据文件,当插入新数据后,会再次使用这些被置为删除标识的记录空间,可以使用OPTIMIZE TABLE来回收未使用的空间,并整理数据文件的碎片。

OPTIMIZE TABLE 表名;

注意:OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。

另外,也可以执行通过ALTER TABLE重建表

ALTER TABLE 表名 ENGINE=INNODB

有人会问OPTIMIZE TABLE和ALTER TABLE有什么区别?

alter table t engine = InnoDB(也就是recreate),而optimize table t 等于recreate+analyze

Online DDL

最后,再说一下Online DDL,dba的日常工作肯定有一项是ddl变更,ddl变更会锁表,这个可以说是dba心中永远的痛,特别是执行ddl变更,导致库上大量线程处于“Waiting for meta data lock”状态的时候。因此在 5.6 版本后引入了 Online DDL。

Online DDL推出以前,执行ddl主要有两种方式copy方式和inplace方式,inplace方式又称为(fast index creation)。相对于copy方式,inplace方式不拷贝数据,因此较快。但是这种方式仅支持添加、删除索引两种方式,而且与copy方式一样需要全程锁表,实用性不是很强。Online方式与前两种方式相比,不仅可以读,还可以支持写操作。

执行online DDL语句的时候,使用ALGORITHM和LOCK关键字,这两个关键字在我们的DDL语句的最后面,用逗号隔开即可。示例如下:

ALTER TABLE tbl_name ADD COLUMN col_name col_type, ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM选项

  1. INPLACE:替换:直接在原表上面执行DDL的操作。
  2. COPY:复制:使用一种临时表的方式,克隆出一个临时表,在临时表上执行DDL,然后再把数据导入到临时表中,在重命名等。这期间需要多出一倍的磁盘空间来支撑这样的 操作。执行期间,表不允许DML的操作。
  3. DEFAULT:默认方式,有MySQL自己选择,优先使用INPLACE的方式。

LOCK选项

  1. SHARE:共享锁,执行DDL的表可以读,但是不可以写。
  2. NONE:没有任何限制,执行DDL的表可读可写。
  3. EXCLUSIVE:排它锁,执行DDL的表不可以读,也不可以写。
  4. DEFAULT:默认值,也就是在DDL语句中不指定LOCK子句的时候使用的默认值。如果指定LOCK的值为DEFAULT,那就是交给MySQL子句去觉得锁还是不锁表。不建议使用,如果你确定你的DDL语句不会锁表,你可以不指定lock或者指定它的值为default,否则建议指定它的锁类型。

执行DDL操作时,ALGORITHM选项可以不指定,这时候MySQL按照INSTANT、INPLACE、COPY的顺序自动选择合适的模式。也可以指定ALGORITHM=DEFAULT,也是同样的效果。如果指定了ALGORITHM选项,但不支持的话,会直接报错。

OPTIMIZE TABLE 和 ALTER TABLE 表名 ENGINE=INNODB都支持Oline DDL,但依旧建议在业务访问量低的时候使用

总结

delete 删除数据时,其实对应的数据行并不是真正的删除,仅仅是将其标记成可复用的状态,所以表空间不会变小。

可以重建表的方式,快速将delete数据后的表变小(OPTIMIZE TABLE 或ALTER TABLE),在 5.6 版本后,创建表已经支持 Online 的操作,但最好是在业务低峰时使用

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • delete、truncate、drop的区别有哪些,该如何选择

    来源:blog.csdn.net/qq_39390545/article/details/107144859

    好好学java
  • mysql删除操作其实是假删除

    在 InnoDB 中,你的 delete 操作,并不会真的把数据删除,mysql 实际上只是给删除的数据打了个标记,标记为删除,因此你使用 delete 删除表...

    谭小谭
  • delete、truncate、drop的区别有哪些,该如何选择

    咱们常用的三种删除方式:通过 delete、truncate、drop 关键字进行删除;这三种都可以用来删除数据,但场景不同。

    肉眼品世界
  • delete、truncate、drop的区别有哪些,该如何选择

    咱们常用的三种删除方式:通过 delete、truncate、drop 关键字进行删除;这三种都可以用来删除数据,但场景不同。

    程序员白楠楠
  • 面试官:生产服务器变慢了,你能谈谈诊断思路吗?

    生产服务器变慢了,一般都是从这几点去分析:服务器整体情况, CPU 使用情况,内存,磁盘,磁盘 IO ,网络 IO

    macrozheng
  • Linux如何处理文件已删除但空间不释放的问题

    一个业务系统的服务器监控系统发来预警通知,磁盘空间使用率已经达到90%了,然后就登陆服务器搜索了下比较大的日志文件,全部都删除了(坑在此处埋上了),磁盘空间释放...

    砸漏
  • 被敖丙用烂的「数据库调优」连招?真香,淦!

    哈哈开头这个场景是我臆想的一个面试场景,但是大家是不是觉得很真实,每个人的简历上但凡写到了数据库,都会在后面顺便写一句,会数据库调优。

    敖丙
  • 面试官灵魂一问: MySQL 的 delete、truncate、drop 有什么区别?

    来源:blog.csdn.net/qq_39390545/article/details/107144859

    java进阶架构师
  • MySQL核心知识学习之路(6)

    作为一个后端工程师,想必没有人没用过数据库,跟我一起复习一下MySQL吧,本文是我学习《MySQL实战45讲》的总结笔记的第六篇,总结了MySQL的InnoDB...

    Edison Zhou
  • 教同事小姐姐删除MySQL数据的几种姿势

    上周四下班后我正在工位上梳理一些文档,同事小姐姐阿侨来找我,“哈哥,晚上有空么?”

    陈哈哈
  • MYSQL 塞进去的吐不出来,磁盘空间浪费了吗?

    问题是这样产生的,有一个同学问一次性操作(big transaction)对数据库有什么不好的地方,当然可以从很多地方来切入,某些BT 对数据库的操作中的影响...

    AustinDatabases
  • 为什么代码规范要求SQL语句不要过多的join?

    面试官:sync; echo 3 > /proc/sys/vm/drop_caches就可以清理buff/cache了,你说说我在线上执行这条命令做好不好?

    lyb-geek
  • 使用 Delete By Query API 的方式删除ES索引中的数据

    ES作为现今最流行的搜索存储库,我们需要定期去清理ES集群的数据以保证集群处在一个最佳负载状态,那么如何去删除这些数据呢,我们今天来介绍一种比较常见的通过Del...

    南非骆驼说大数据
  • MySQL 案例:Delete 删数据后磁盘空间未释放

    在 MySQL 中使用 delete 语句删除数据之后,监控视图中可用的磁盘空间没有增加,磁盘使用率没有下降等等。

    王文安@DBA
  • delete后加 limit是个好习惯么 ?

    公众号改版后文章乱序推荐,希望你可以点击上方“Java进阶架构师”,点击右上角,将我们设为★“星标”!这样才不会错过每日进阶架构文章呀。

    java进阶架构师
  • Kubernetes之容器数据写满磁盘解决方法

    上面步骤操作完后(上面清理日志方法,可能对于收集日志程序会丢失一些日志,但一般情况能接受),可以选择驱赶节点上所有pod(kubectl drain ${nod...

    YP小站
  • delete 后加 limit 是个好习惯么

    来自:blog.csdn.net/qq_39390545/article/details/107519747

    良月柒
  • MySQL 存储引擎(2)原

    顾名思义,存储引擎就是用于存储我们的数据的。在关系型数据库中我们一般将数据库存放在表中(Table)。

    兜兜毛毛
  • 一文解析:MySQL事务ACID原理让你面试不再害怕

    欢迎关注专栏:Java架构技术进阶。里面有大量batj面试题集锦,还有各种技术分享,如有好文章也欢迎投稿哦。 照例,我们先来一个场景~

    慕容千语

扫码关注云+社区

领取腾讯云代金券