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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 这期面试官提的问题是: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
相关文章
|
15天前
|
存储 关系型数据库 MySQL
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
|
8天前
|
SQL 缓存 关系型数据库
美团面试:Mysql 有几级缓存? 每一级缓存,具体是什么?
在40岁老架构师尼恩的读者交流群中,近期有小伙伴因未能系统梳理MySQL缓存机制而在美团面试中失利。为此,尼恩对MySQL的缓存机制进行了系统化梳理,包括一级缓存(InnoDB缓存)和二级缓存(查询缓存)。同时,他还将这些知识点整理进《尼恩Java面试宝典PDF》V175版本,帮助大家提升技术水平,顺利通过面试。更多技术资料请关注公号【技术自由圈】。
美团面试:Mysql 有几级缓存? 每一级缓存,具体是什么?
|
1天前
|
SQL 前端开发 关系型数据库
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
SpringBoot使用mysql查询昨天、今天、过去一周、过去半年、过去一年数据
23 9
|
14天前
|
存储 缓存 关系型数据库
滴滴面试:单表可以存200亿数据吗?单表真的只能存2000W,为什么?
40岁老架构师尼恩在其读者交流群中分享了一系列关于InnoDB B+树索引的面试题及解答。这些问题包括B+树的高度、存储容量、千万级大表的优化、单表数据量限制等。尼恩详细解释了InnoDB的存储结构、B+树的磁盘文件格式、索引数据结构、磁盘I/O次数和耗时,以及Buffer Pool缓存机制对性能的影响。他还提供了实际操作步骤,帮助读者通过元数据找到B+树的高度。尼恩强调,通过系统化的学习和准备,可以大幅提升面试表现,实现“offer直提”。相关资料和PDF可在其公众号【技术自由圈】获取。
|
3天前
|
SQL 算法 关系型数据库
面试:什么是死锁,如何避免或解决死锁;MySQL中的死锁现象,MySQL死锁如何解决
面试:什么是死锁,死锁产生的四个必要条件,如何避免或解决死锁;数据库锁,锁分类,控制事务;MySQL中的死锁现象,MySQL死锁如何解决
|
12天前
|
SQL Java 关系型数据库
java连接mysql查询数据(基础版,无框架)
【10月更文挑战第12天】该示例展示了如何使用Java通过JDBC连接MySQL数据库并查询数据。首先在项目中引入`mysql-connector-java`依赖,然后通过`JdbcUtil`类中的`main`方法实现数据库连接、执行SQL查询及结果处理,最后关闭相关资源。
|
9天前
|
SQL 关系型数据库 MySQL
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
23 1
|
10天前
|
SQL 关系型数据库 MySQL
mysql数据误删后的数据回滚
【11月更文挑战第1天】本文介绍了四种恢复误删数据的方法:1. 使用事务回滚,通过 `pymysql` 库在 Python 中实现;2. 使用备份恢复,通过 `mysqldump` 命令备份和恢复数据;3. 使用二进制日志恢复,通过 `mysqlbinlog` 工具恢复特定位置的事件;4. 使用延迟复制从副本恢复,通过停止和重启从库复制来恢复数据。每种方法都有详细的步骤和示例代码。
|
10天前
|
SQL 关系型数据库 MySQL
美团面试:Mysql如何选择最优 执行计划,为什么?
在40岁老架构师尼恩的读者交流群中,近期有小伙伴面试美团时遇到了关于MySQL执行计划的面试题:“MySQL如何选择最优执行计划,为什么?”由于缺乏系统化的准备,小伙伴未能给出满意的答案,面试失败。为此,尼恩为大家系统化地梳理了MySQL执行计划的相关知识,帮助大家提升技术水平,展示“技术肌肉”,让面试官“爱到不能自已”。相关内容已收录进《尼恩Java面试宝典PDF》V175版本,供大家参考学习。
|
23天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
56 3
Mysql(4)—数据库索引