13 | 为什么表数据删掉一半，表文件大小不变？

最新推荐文章于 2023-07-05 20:13:01 发布

笔仙笔仙

最新推荐文章于 2023-07-05 20:13:01 发布

阅读量223

点赞数

分类专栏： MySQL笔记文章标签： mysql java

原文链接：https://time.geekbang.org/column/article/72388

版权

MySQL笔记专栏收录该内容

48 篇文章 1 订阅

订阅专栏

一、抛砖引玉

开发过程中，我们难免存在大表数据，把一个最大的表删掉了一半的数据，怎么表文件的大小还是没变？

二、正文开始

🏁：1.想要知道为什么没有变首先我们要明确MySQL的Innodb引擎中，表的结构和数据是存放在哪里的？

表结构：MySQL8.0以前表的结构数据存放在以.frm 为后缀的文件里，以后允许把表结构定义放在系统数据表中了。
表数据：表数据的存放是通过参数 innodb_file_per_table控制的，OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。
PS：5.6.6以后默认是ON，推荐做法就是ON

🏁：2.明白了表数据的存储，具体的删除流程是怎么样的？
在这里插入图片描述
当我们要删除（delete）Page中一条数据时（如：R4），这条数据只会标记为已删除，因为如果之后插入数据再用到这个区间的数据，此时被复用。这也是为什么大小不会缩小了。

PS：如果数据页被删除了，也同样会被复用。区别就在于，数据行的复用是区间内的，数据页的复用是任意数据都可。

⚠️：如果相邻（相邻应该是指B+树上的逻辑相邻，而不是表空间文件的物理相邻）的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。

问题2总结就是：delete 命令把整个表就意味着所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小。

🏁：3.我们知道了原理后，深入想一下，这样删除或者插入都会造成表“空洞”，如何把这些空洞去掉达到收缩表的目的呢？

如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。

重建表。即：通过使用 alter table A engine=InnoDB 命令实现转存数据、交换表名、删除旧表的操作。

⚠️：时间最多的步骤是往临时表插入数据的过程，如果在这个过程中，有新的数据要写入到表 A 的话，就会造成数据丢失。

🏁：3.1.重建表原理（MySQL如何做到重建过程数据不丢失）

MySQL 5.6 版本开始引入的 Online DDL。

重建表的流程：
建立一个临时文件，扫描表 A 主键的所有数据页。（State1）
用数据页中表 A 的记录生成 B+ 树，存储到临时文件中。生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中。（State2）
临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件。（State3 ）
用临时文件替换表 A 的数据文件。（ State4 ）

上述过程中，alter 语句在启动的时候需要获取 MDL 写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。为了实现 Online，MDL 读锁不会阻塞增删改操作。
思考下，这里为啥不直接解锁呢？

👋：简化后的Online DDL

Online DDL的过程是这样的：
1. 拿MDL写锁
2. 降级成MDL读锁
3. 真正做DDL
4. 升级成MDL写锁
5. 释放MDL锁

⚠️⚠️⚠️：不要以为知道了这个语句就可以放飞自我了！！！
线上对于很大的表来说，这个操作是很消耗 IO 和 CPU 资源的。因此，如果是线上服务，你要很小心地控制操作时间，安全的操作的话使用 GitHub 开源的 gh-ost 来做。

🏁：4.除了Online还有一个姊妹操作Inplace

相同点是他们过程一毛一样
区别是什么？Online过程导出来的存放位置叫作 tmp_table。这是一个临时表，是在 server 层创建的。
Inplace数据是放在“tmp_file”里的，这个临时文件是 InnoDB 在内部创建出来的。整个 DDL 过程都在 InnoDB 内部完成。对于 server 层来说，没有把数据挪动到临时表，是一个“原地”操作，这就是“inplace”名称的来源。

因此我们用的这个语句：alter table t engine=InnoDB 等价于 alter table t engine=innodb,ALGORITHM=inplace;
Online就是ALGORITHM=copy

🏁：4.1.alter table t add FULLTEXT(field_name);这个是Online还是Inplace的？
这个过程是 inplace 的，但会阻塞增删改操作，是非 Online 的。
因为，截止到 MySQL 8.0，添加全文索引（FULLTEXT index）和空间索引 (SPATIAL index) 就属于非Online这种情况

三、课后题
一个表 t 文件大小为 1TB；对这个表执行 alter table t engine=InnoDB；发现执行完成后，空间不仅没变小，还稍微大了一点儿，比如变成了 1.01TB。你觉得可能是什么原因呢？

本身就已经没有空洞的了，比如说刚刚做过一次重建表操作。在 DDL 期间，如果刚好有外部的 DML 在执行，这期间可能会引入一些新的空洞。@飞翔提到了一个更深刻的机制，是我们在文章中没说的。在重建表的时候，InnoDB 不会把整张表占满，每个页留了 1/16 给后续的更新用。也就是说，其实重建表之后不是“最”紧凑的。假如是这么一个过程：将表 t 重建一次；插入一部分数据，但是插入的这些数据，用掉了一部分的预留空间；这种情况下，再重建一次表 t，就可能会出现问题中的现象。（个人没太看懂的地方）

四、评论区

🏁：5.使用 optimize table、analyze table 和 alter table 这三种方式重建表的区别。从 MySQL 5.6 版本开始，