【20211204】MySQL删除一半表数据，表文件却不变小

时间：2021年12月04日

作者：小蒋聊技术

大家好，欢迎来到小蒋聊技术。小蒋准备和大家一起聊聊技术的那些事。

最近，小蒋在做系统优化，发现了一个运行了很长时间的MySQL数据库，其中一个业务表中50%都是冗余数据。可是将表中冗余数据删除（delete from table_name where 条件）后，表文件大小却丝毫没有减小，今天小蒋就准备和大家一起来看看，这到底是怎么一回事。

业务场景

线上某个业务系统，最近运行速度越来越慢，已经严重影响到日常使用。针对系统变慢的问题，一般需要运维人员和开发工程师联合进行排查。

系统运行效率低的原因：

业务系统，不停的在MySQL数据库，表上做增删改查操作。随着时间的推移，MySQL表数据文件越来越大，数据库占用的空间自然也逐渐增长。当运行环境资源不足时（CPU、内存、磁盘），就会导致业务系统运行效率逐渐变低。

解决方案：

为了保证业务系统的运行效率，运维人员要定期对系统运行环境进行检查和维护。清理系统中的垃圾并对业务系统历史数据定期做备份和转移。

遇到的现象：

本次，小蒋在问题排查时，发现MySQL数据库中，某个业务表中有3千多万条数据，占用了40多G容量，但其中50%的数据都是冗余数据。

为了缩小磁盘上表数据文件占用的空间，我们用delete命令删除了其中50%的冗余数据。但，删除之后，磁盘上表数据文件并没有缩小，这是为什么呢？

针对这个问题，小蒋和大家一起来分析一下。

MySQL表空间回收的正确姿势

目前业务系统，后端数据库使用的是MySQL，使用的是InnoDB引擎。咱们先从MySQL使用最广泛的InnoDB引擎开始分析。

InnoDB引擎：

InnoDB类型的表，包含两部分：

1. 表结构定义
2. 数据

在MySQL8.0版本以前，表结构是存在.frm为后缀的文件里。

在MySQL8.0版本及以后的版本，允许将表结构定义在系统数据表中。

参数innodb_file_per_table：

表数据即可以存在共享表空间里，也可以是单独的文件。由参数innodb_file_per_table 控制。

1. 这个参数ON：表示每个InnoDB表数据存储在“.ibd为后缀的文件”中。
2. 这个参数OFF：表示数据存放在“系统共享空间”中，也就是和数据字典放在一起。

※从MySQL5.6.6版本开始，innodb_file_per_table它的默认值就是ON了。一个表单独存储为一个文件更容易管理，在你不需要这个表的时候，通过drop table命令，系统就会直接删除这个文件。但，如果innodb_file_per_table设置的是OFF，也就是说数据放在共享表空间中，即使表删除了，空间也是不会回收的。

数据删除流程：

MySQL InnoDB引擎中采用了B+ Tree（B+ 树）作为存储结构。也就是常说的索引组织表，数据是按照页来存储的。（见下图）

假设，我们要删除ID是500的这条记录，InnoDB引擎只会把500的这条记录“标记删除”。（磁盘文件的大小并不会缩小）

如果，之后要再插入一个300 —— 600 之间的记录时，可能会复用这个位置。

如果我们删除了数据页上的所有记录（例如，page A），那么整个数据页就可以被整体复用了。

结论：

也就是说，通过Delete命令其实只是把记录的位置，或者数据页作“标记删除”，之后，它们就成为“可复用”项。

但，磁盘文件的大小是不会变的。也就是说，Delete命令是不能回收表空间的。

这些可以被“复用”，而没有“被使用”的空间，看起来就像是“空洞”。

实际上，不仅仅删除数据会照成“空洞”，插入数据也会。

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

综上所述，我们可以推理出结论。经过大量增删改查的表，都是可能存在空洞的。

如果把“空洞”去掉，自然空间就被释放了。

重建表(去除空洞)：

既然，咱们已经弄清楚“空洞”的原理了，那如果需要重建表（去除空洞），那么你能想出MySQL它是如何做到的吗？

这点小事，小蒋觉得难不倒你：

• 建立临时文件tmp，把A表的数据根据递增的关系，放入临时文件tmp中，然后用临时文件tmp替换A，完成重构。（A表的空间收缩，“空洞”消失，见下图：）

具体的命令是：

alter table A engine=InnoDB

在MySQL 5.5版本后，该命令和上面的流程基本一致。MySQL自己会完成数据填充、交换表名、删除旧表的操作。

但，细心的小伙伴会发现一个问题。整个“重建表”这个过程花时间最多的步骤是“state 2”，也就是往临时表插入数据的过程。如果在这个过程中，有新的数据要写入表A，就会造成数据丢失。所以，这个操作要离线进行。

对这个问题，MySQL 5.6版本做了优化，引入了Online DDL：

1. 建立一个临时文件（tmp-file），扫描A的所有数据页；
2. 根据数据页生成B+ Tree(B+ 树)，存入临时文件（tmp-file）；
3. 生成临时文件（tmp-file）的过程中，将对A的操作记录在一个日志文件（row log）中；
4. 临时文件（tmp-file）做好之后，根据日志文件（row log）的操作应用到临时文件（tmp-file），然后得到一个逻辑数据上和A相同的数据文件，对应state 3；
5. 用临时文件（tmp-file）替换表A的数据文件；

（见下图：）

这个方案，在重建表的过程中，允许对表A做增删改查，也也就是Online DDL名字的来源。

但是，不得不说，这个操作在操作大表上非常消耗资源。

架构师要弄清楚产品的定位

如果你现在是一名工程师，将来想要成为一名架构师。或者你已经是一名架构师了，还想再进一步提升。接下来，小蒋准备为大家分享一些自己工作中的心得和体会。

小蒋在公司里，负责过很多系统。这些系统都有固定的人群在使用，并不是服务所有人。

所以，小蒋在每次接手产品/项目前，都会认真的想一个问题，那就是到底要接手的这个产品/项目能够赖以生存的最小服务人群是什么？也就是所谓的最小有效市场。

小蒋我知道，马上要接手的产品/项目，不可能会像人民币一样吸引所有人。必须找到最忠实的用户或者粉丝，帮助他们实现需求，做好他们的用户体验，接手的这个产品/项目才能一直干下去。

接下来要接手的产品/项目，肯定不是服务所有人的。当你满足一小部分群体的同时，肯定有一大部分群体对你的产品/服务提出意见甚至是批评。

就像榴莲，喜欢吃榴莲的人，就非常的爱，但是不喜欢的就会嗤之以鼻，觉得臭味熏天。这个时候，就必须的清楚意识到自己的客户是谁。

决不能，为了大部分人的口味而改变。那么你的产品/项目就会变得没有特点而中庸，最后那一小部分用户人群也会失去。

所以，有一句话叫众口难调，抓住你固定受众群就好。

我们做产品，做服务，一定要像榴莲一样，让那些爱上你的人，爱死你！他们会对你的产品形成口碑二次传播，从而聚聚更多的价值观相同的用户。

有时候，我们的产品/项目要做的很大，并不需要几亿用户。只要有几千、几万、几十万真爱的用户，高频次的用户，其实就足够了。

所以，做架构师的我和你，一定要弄清楚自己产品/项目的定位。并找到我们产品/项目真正的用户群。

总结：

这是小蒋，随着工作的时间和经验的积累，逐渐明白的一个道理。一定要找到产品/项目真正的客户群，对着你产品/项目真正的客户传播你的理念，而不是对着大海去用功。

这样对大家都好，你不会浪费其他人的时间，更可以对你的潜在用户说，这就是为你量身定做的，也凸显了对客户的尊重。

最后，回到小蒋之前谈的老问题，思考一下：

你在为什么人而服务？他们的需求是什么，想要的是什么？

年龄的增长不可怕，可怕的是从未成长！

感谢大家支持小蒋，小蒋希望和大家共同成长，谢谢。

音频地址：

【20211204】MySQL删除一半表数据，表文件却不变小https://www.ximalaya.com/keji/51588599/479202048