这期面试官提的问题是:
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 之后,重建表的流程是这样的:
- 建立一个临时文件,扫描表 order 主键的所有数据页;
- 用数据页中表 order 的记录生成 B+ 树,存储到临时文件中;
- 生成临时文件的过程中,将所有对 order 的操作记录在一个日志文件(row log)中,对应的是图中 state2 的状态;
- 临时文件生成后,将日志文件中的操作应用到临时文件,得到一个逻辑数据上与表 order 相同的数据文件,对应的就是图中 state3 的状态;
- 用临时文件替换表 order 的数据文件。
上图,方便你们理解:
由于日志文件记录和重放操作这个功能的存在,这个方案在重建表的过程中,允许对表 A 做增删改操作。
06 总结
这篇文章我们聊了 MySQL 中大量的增删改都有可能造成数据空洞、数据库中收缩表空间的方法。其中 delete 命令是不会回收表空间的,还要通过 alter table 命令重建表,才能达到表文件变小的目的。
这个命令在 5.6 版本以及之后可以考虑在业务低峰期使用的,但在 5.5 及之前的版本,这个命令是会阻塞 DML 的,建议你慎重。
另外,重建表都会扫描原表数据和构建临时文件。对于大表来说,这个操作是很消耗 IO 和 CPU 的。因此,如果是线上服务你要很小心地控制操作时间。如果想要比较安全的操作的话,推荐使用 GitHub 开源的 gh-ost 来做。
6.1 参考
本文就是愿天堂没有BUG给大家分享的内容,大家有收获的话可以分享下,想学习更多的话可以到微信公众号里找我,我等你哦。