面试官:mysql 表删除一半数据,表空间会变小吗?

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 这期面试官提的问题是:MySQL 表删除一半数据,表空间是否会变小?为什么?

这期面试官提的问题是:

MySQL 表删除一半数据,表空间是否会变小?为什么?

我:

你这么问,肯定是不会?但是我不知道为什么(理直气壮.jpg)

国际惯例先上思维导图:

遇到这种问题先做一波实验,我的思路验证下是否会删除。声明:此次实验采用的 MySQL 版本是 5.7,引擎是 InnDB

往期精彩

MySQL 查询语句是怎么执行的?

MySQL 索引

MySQL 日志

MySQL 事务与 MVCC

MySQL 的锁机制

MySQL 字符串怎么设计索引?

面试官:数据库自增 ID 用完了会咋样?

面试官:order by 怎么优化?

面试官:count (*) 怎么优化?

面试官:explain 应该关注哪些指标?

01 做个实验

首先整一张表结构:订单表 order,主键是 id,另外还有一个索引 index_city 用 city 字段建索引。

CREATE TABLE `order`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户编号',
  `goods_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商品名称',
  `order_date` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
  `city` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '下单城市',
  `order_num` int(10) NOT NULL COMMENT '订单号数量',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `city_index`(`city`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2000002 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '商品订单表' ROW_FORMAT = Compact;

1.1 插入数据

造点数据,为了效果。我直接造 200W 条数据,然后直接 delete 删掉一半。

// 第一步:创建函数
delimiter //
DROP PROCEDURE
IF
 EXISTS proc_buildata;
CREATE PROCEDURE proc_buildata ( IN loop_times INT ) BEGIN
DECLARE var INT DEFAULT 0;
WHILE
 var < loop_times DO
 SET var = var + 1;
INSERT INTO `order` ( `id`, `user_code`, `goods_name`, `order_date`, `city` , `order_num`)
VALUES
 ( var, var + 1, '有线耳机', '2021-06-20 16:46:00', '杭州', 1 );
END WHILE;
END // delimiter;
// 第二步:调用上面生成的函数,即可插入数据
CALL proc_buildata(2000000);

插入完成,耗时贼久。建议批量插入:

插入完成,到 MySQL 查看文件大小对应文件大小(下图中的 .idb 文件)

200W 数据大概是 184M 左右的大小:

1.1.1 一些小知识

1、一个 InnoDB 表包含表结构定义和数据两部分,在 MySQL 8.0 版本以前,表结构是存在于 .frm 为后缀的文件里。而 MySQL 8.0 版本,则已经允许把表结构定义放在系统数据表中了

2、表数据既可以存在共享表空间里,也可以是单独的文件。由参数 innodb_file_per_table 控制。MySQL 5.6.6 版本之后,默认是 ON,也即每个 InnoDB 表数据以及索引存储在一个以 .ibd 为后缀的文件中。

3、为方便管理建议你设置为 ON,因为当你不需要这个表时,通过 drop table 命令,系统直接删除这个文件。而如果放在共享表空间中,即使表删掉了,空间也是不会回收的。

4、由于表结构文件一半很小,本文讨论的表空间是指表数据文件 .ibd 的变化。

1.2 删除数据

批量删除其中的 100W 的数据,此时的总数据量:

再次查看 order.ibd 文件的大小,还是 184M。也就是说 MySQL 表删除一半数据之后,表空间并没有随之减小,好特么奇怪呀。

这是为啥呢?这就得说说 MySQL 删除数据的流程

02 删除数据流程

还记得我之前讲的索引原理么?不清楚的朋友们,请看以下这篇文章,看看 InnDB 索引是怎么组织数据的。不然你是看不懂下面的过程的。

MySQL 索引原理

InnoDB 里的数据都是用 B+ 树的结构组织的,假设现在我们表里的数据长这样:

我删除 id = 10 的这行数据,MySQL 实际上只是把这行数据标记为已删除,并不会回收表空间,而是给后来的数据复用

那怎么复用呢?总得有规则吧?如果这时客户端申请插入的是 id 在 (8,18) 范围内的数据,此时 id = 10 的位置就会被复用。比如我插入 id=11 的记录就会复用 id=10 的空间。但如果插入的是 id = 20 的数据就没法复用这个空间了。

2.1 整页删除

InnoDB 的数据是按页存储的,如果删掉了一个数据页上的所有记录,会怎么样?那就是这个页的所有数据都能被复用

但是数据页的复用跟记录的复用是不同,记录的复用有限定范围,而数据页的复用并没有限制。举例:如果我现在把 P2 整页数据删除,那么限制我要插入 id = 50 的数据也是可以被复用,当然这时候 P2 页的范围就不再是 id (8,19) 了

2.2 什么是数据 "空洞"?

如果相邻的两个数据页利用率都很小,MySQL 会把这两个页的数据合到其中一个页,另外一个被标记为可复用

当然,如果用 delete 删除整个表数据的结果就是:所有的数据页都会被标记为可复用。但是磁盘上,文件不会变小

所以,delete 命令其实只是把记录的位置,或者数据页标记为了可复用,但磁盘文件的大小是不会变的。也就是说,通过 delete 命令是不能回收表空间的

这些可以复用,而没有被使用的空间,被称为空洞

03 新增数据

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

如果数据是随机插入,非主键自增的,就可能造成索引的数据页分裂。

下图中,假设数据页 P2 已满,这时再插入 id=16 的记录,就需要申请一个新的 P3 页来存储数据。等到页分裂完成后,P2 的末尾就留下了空洞(PS:实际上,可能不止 1 个记录的位置是空洞)。

但是如果数据是按照索引递增顺序插入的,索引就是紧凑的,就不会有页分裂这回事。这也是为什么数据库要设置自增 ID 的主要原因

04 修改数据

不仅是插入数据,更新数据也会造成空洞。很多人可能不理解这个过程,更新数据主键都没变怎么会造成数据空洞呢?实际上更新索引上的值,可以理解为删除一个旧的值,再插入一个新值

比如,我把 id = 10 的城市从北京改成东京,就会造成空洞。

你可能会说不对啊,上图中 id 都没变怎么会数据空洞呢?实际上文章开头就说了,city 这个字段是二级索引,索引 index_city 的值从北京变成南京,北京的索引数据会标记为删除,然后重新建立南京的索引数据,一删一增的过程就产生了空洞

总结一句:更新过程中如果有索引更新了,就会造成数据空洞。也就是二级索引树更新造成的数据空洞

05 重建表,回收空间

从上面的结论你也知道了,大量的增删改确实会造成空洞的。如果能够把这些空洞去掉,就能达到收缩表空间的目的。而重建表就能做到。具体怎么做呢?

那 order 表举例,可以新建一个临时表 order_tmp,它的表数据结构与 order 完全相同。然后按 id 从小到大的顺序把数据从 order 表读出来插入到 order_tmp 表。

此时,由于 order_tmp 并没有数据空洞,所以它的主键索引更紧凑,数据页利用率更高。等到迁移完成,可以用 order_tmp 表替代 order 表,从而收缩 order 表的空间

以上描述的一系列操作,是不是觉得超级麻烦?贴心的 MySQL 在 5.5 版本之前,提供了以下命令来重建表,回收空间。

alter table order engine=InnoDB

执行它,临时表 order_tmp 不需要你自己创建,MySQL 会自动完成转存数据、交换表名、删除旧表的操作。

我画个流程图,帮助大家理解下:

看到这里你可能觉得完美解决了空洞问题,其实不然,这个方案最大的缺点就是:表重构过程中,往临时表插入数据是很耗时的;如果有新的数据写入 order 时,不会被迁移,会造成数据丢失

5.2 Online DDL

那咋办呢?MySQL 5.6 版本开始引入的 Online DDL,解决了这个问题。引入了 Online DDL 之后,重建表的流程是这样的:

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

上图,方便你们理解:

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

06 总结

这篇文章我们聊了 MySQL 中大量的增删改都有可能造成数据空洞、数据库中收缩表空间的方法。其中 delete 命令是不会回收表空间的,还要通过 alter table 命令重建表,才能达到表文件变小的目的

这个命令在 5.6 版本以及之后可以考虑在业务低峰期使用的,但在 5.5 及之前的版本,这个命令是会阻塞 DML 的,建议你慎重。

另外,重建表都会扫描原表数据和构建临时文件。对于大表来说,这个操作是很消耗 IO 和 CPU 的。因此,如果是线上服务你要很小心地控制操作时间。如果想要比较安全的操作的话,推荐使用 GitHub 开源的 gh-ost 来做。

6.1 参考

本文就是愿天堂没有BUG给大家分享的内容,大家有收获的话可以分享下,想学习更多的话可以到微信公众号里找我,我等你哦。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
3天前
|
SQL 关系型数据库 MySQL
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
MySQL慢查询优化、索引优化,是必知必备,大厂面试高频,本文深入详解,建议收藏。关注【mikechen的互联网架构】,10年+BAT架构经验分享。
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
|
7天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
本文介绍了MySQL InnoDB存储引擎中的数据文件和重做日志文件。数据文件包括`.ibd`和`ibdata`文件,用于存放InnoDB数据和索引。重做日志文件(redo log)确保数据的可靠性和事务的持久性,其大小和路径可由相关参数配置。文章还提供了视频讲解和示例代码。
113 11
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
|
17天前
|
SQL 缓存 关系型数据库
美团面试:Mysql 有几级缓存? 每一级缓存,具体是什么?
在40岁老架构师尼恩的读者交流群中,近期有小伙伴因未能系统梳理MySQL缓存机制而在美团面试中失利。为此,尼恩对MySQL的缓存机制进行了系统化梳理,包括一级缓存(InnoDB缓存)和二级缓存(查询缓存)。同时,他还将这些知识点整理进《尼恩Java面试宝典PDF》V175版本,帮助大家提升技术水平,顺利通过面试。更多技术资料请关注公号【技术自由圈】。
美团面试:Mysql 有几级缓存? 每一级缓存,具体是什么?
|
7天前
|
缓存 NoSQL 关系型数据库
Redis和Mysql如何保证数据⼀致?
在项目中,为了解决Redis与Mysql的数据一致性问题,我们采用了多种策略:对于低一致性要求的数据,不做特别处理;时效性数据通过设置缓存过期时间来减少不一致风险;高一致性但时效性要求不高的数据,利用MQ异步同步确保最终一致性;而对一致性和时效性都有高要求的数据,则采用分布式事务(如Seata TCC模式)来保障。
39 14
|
7天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的表空间
InnoDB是MySQL默认的存储引擎,主要由存储结构、内存结构和线程结构组成。其存储结构分为逻辑和物理两部分,逻辑存储结构包括表空间、段、区和页。表空间是InnoDB逻辑结构的最高层,所有数据都存放在其中。默认情况下,InnoDB有一个共享表空间ibdata1,用于存放撤销信息、系统事务信息等。启用参数`innodb_file_per_table`后,每张表的数据可以单独存放在一个表空间内,但撤销信息等仍存放在共享表空间中。
|
10天前
|
SQL 前端开发 关系型数据库
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
41 9
|
11天前
|
SQL 算法 关系型数据库
面试:什么是死锁,如何避免或解决死锁;MySQL中的死锁现象,MySQL死锁如何解决
面试:什么是死锁,死锁产生的四个必要条件,如何避免或解决死锁;数据库锁,锁分类,控制事务;MySQL中的死锁现象,MySQL死锁如何解决
|
18天前
|
SQL 关系型数据库 MySQL
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
38 1
|
19天前
|
SQL 关系型数据库 MySQL
美团面试:Mysql如何选择最优 执行计划,为什么?
在40岁老架构师尼恩的读者交流群中,近期有小伙伴面试美团时遇到了关于MySQL执行计划的面试题:“MySQL如何选择最优执行计划,为什么?”由于缺乏系统化的准备,小伙伴未能给出满意的答案,面试失败。为此,尼恩为大家系统化地梳理了MySQL执行计划的相关知识,帮助大家提升技术水平,展示“技术肌肉”,让面试官“爱到不能自已”。相关内容已收录进《尼恩Java面试宝典PDF》V175版本,供大家参考学习。
|
8天前
|
SQL 关系型数据库 MySQL
go语言数据库中mysql驱动安装
【11月更文挑战第2天】
23 4