MySQL探秘:为何删除半数表数据后文件大小依旧

ChatGPT / Claude 官方直充代充值

文章标题:

MySQL探索:删除半数表数据后文件大小未变的缘由

文章内容:

一个InnoDB表包含两部分:表的结构定义以及数据。在MySQL 8.0版本之前,表结构定义存于后缀为.frm的文件中。后续版本允许将表结构定义放置在系统数据表内。由于表结构定义所占空间极小,所以重点探讨表数据部分。

首先阐述为何简单删除表数据无法达成表空间回收效果,接着介绍正确回收空间的办法。

参数innodb_file_per_table

表数据既可存储在共享表空间中,也能是独立文件,这由参数innodb_file_per_table控制:
- 设为OFF时,表数据存于系统共享表空间,即与数据字典存放一处;
- 设为ON时,每个InnoDB表的数据存储在一个后缀为.ibd的文件里。

从MySQL 5.6.6版本起,该参数默认值为ON。建议使用ON,因为一个表单独存储成文件更便于管理,且不需要该表时通过drop table命令可直接删除文件;若存于共享表空间,即便表被删除,空间也不会回收。后续讨论基于innodb_file_per_table=ON的设置。

删除整张表时可使用drop table命令回收表空间,但日常更多场景是删除某些行。

数据删除流程

为明晰删除部分行的场景,需从数据删除流程说起。

看InnoDB中一个索引的示意图:
MySQL探秘:为何删除半数表数据后文件大小依旧

假设要删除R4记录,InnoDB仅会标记R4为删除。若后续插入ID在300 - 600间的记录,可能复用该位置,但磁盘文件大小不变。

若一个数据页的所有记录均被删除,会怎样?答案是整个数据页可被复用。

不过数据页复用与记录复用不同。记录复用限于符合范围条件的数据,而数据页可复用时,所有范围数据均可使用。例如上述索引中,若page A可复用,ID = 50的记录也能使用该页。

若相邻两个数据页利用率低,系统会将两页数据合并至其中一页,另一页标记为可复用。

进一步讲,用delete命令删除整个表数据时,所有数据页均标记为可复用,但磁盘文件不会变小。即delete命令无法回收表空间,这些可复用却未使用的空间形似“空洞”。

实际不仅删除数据会造成空洞,插入数据也会。若数据插入随机,可能致索引数据页分裂。如上述索引中,假设page A已满,插入ID = 550的数据时:
MySQL探秘:为何删除半数表数据后文件大小依旧

page A已满时插入数据,需申请新页面page B存数据。因页分裂致部分数据移动,page A出现空洞。

除插入外,更新可视为删除 + 插入,也可能造成空洞。即增删改均可能产生空洞。故消除这些空洞可收缩表空间。

重建表

假设有表A,欲去除空洞,有何办法?

可新建与表A结构相同的表B,按主键ID递增顺序,逐行从表A读取数据插入表B。因表B为新建,无表A的空洞。将表B作临时表,数据从表A导入表B后,用表B替换表A,表A即无空洞。

可用alter table A engine=InnoDB命令重建表。MySQL 5.5之前版本,执行流程与上述相近,区别是无需自建临时表,MySQL自动完成转存数据、交换表名、删除旧表操作。

往临时表插入数据时,若表A有新数据写入会致数据损失,故整个DDL过程表A不能更新,即DDL非Online。

MySQL 5.6起引入Online DDL优化流程:
- 建立临时文件;
- 扫描表A主键所有数据页,用记录生成B+树存于临时文件;
- 生成临时文件时,将对A的操作记录于日志文件(row log),对应图中state 2状态;
- 临时文件生成后,将日志文件操作应用于临时文件,得与表A逻辑数据相同的临时文件;
- 用临时文件替换表A。
MySQL探秘:为何删除半数表数据后文件大小依旧

该流程因日志文件与重放操作,重建表时允许表A增删改。

因对表改动有MDL锁,alter语句启动时获MDL写锁,拷贝数据前退化为读锁,禁其他线程同时做DDL,不阻塞增删改。

大表时,Online DDL最耗时为拷贝数据至临时表,写锁锁住时间短,可视为Online。

需知,上述重建方法扫描原表数据并构建临时文件,大表操作耗IO和CPU资源。线上服务控时推荐用开源gh-ost。

Online和inplace

说到Online,提易混淆概念inplace。

早版本重建表时,表A数据导出存放于tmp_table,由Server层创建。

后续版本,表A重建数据存于tmp_file(见前图),由InnoDB内部创建。因DDL在InnoDB内部完成,Server层无数据挪至临时表,为“原地”操作,称inplace。

若表大小1TB,磁盘空间1.2TB,能否做inplace DDL?不行,因tmp_file占临时空间。

重建表完整语句:

alter table t engine=innodb,ALGORITHM=inplace;
alter table t engine=innodb,ALGORITHM=copy;

其中,copy表强制拷贝用临时表,inplace表用临时文件。

inplace是否即Online?非也,仅重建表逻辑如此。

两者关系:
- DDL为Online则必为inplace;
- 反之不然,inplace DDL不一定是Online。MySQL 8.0前,添加全文索引(FULLTEXT index)和空间索引 (SPATIAL index) 属此情况。如给InnoDB表字段加全文索引,过程inplace但阻塞增删改。

文章整理自互联网,只做测试使用。发布者:Lomu,转转请注明出处:https://www.it1024doc.com/13055.html

赞 (0)
LomuLomu
上一篇 2025 年 8 月 5 日
下一篇 2025 年 8 月 5 日

相关推荐

  • Grok Super资料整理代充开通教程

    国内用户开通 Grok Super 的充值教程,整理 Grok 充值、代充、国内支付、账号 ID 获取和确认充值步骤。

    未分类 2026 年 6 月 24 日
    22800
  • IDEA破解版下载+使用方法详解,支持mac系统永久激活

    本教程适用于IDEA、PyCharm、DataGrip、Goland等,支持Jetbrains全家桶! 废话不多说,先上最新 IDEA 版本破解成功的截图,如下,可以看到已经成功破解到 2099 年辣,舒服! 接下来,我就将通过图文的方式, 来详细讲解如何激活 IDEA至 2099 年。 当然这个激活方法,同样适用于之前的旧版本! 不管你是什么操作系统,什么…

    2025 年 10 月 1 日
    73100
  • ChatGPT Plus充值教程:订单记录如何保留

    国内用户可以按卡密验证、账号信息提交和最终确认的顺序完成开通;重点不是反复尝试支付,而是先确认本人账号,再通过国内入口使用卡密并提交目标账号的 Session,处理“订单记录如何保留”时,以订单和账号确认页为准。

    未分类 2026 年 9 月 11 日
    13000
  • chatgpt plus中国怎么付费更省心

    真到要处理chatgpt plus中国怎么付费时,很多人最怕的不是付不过去,而是流程来回折返:刚看完一篇教程,又去翻别人的经验;刚准备付款,又担心后面续费麻烦。对国内用户来说,所谓更省心,不是只看这一次能不能买成,而是能不能把入口、支付、到账和后续使用一次理顺。 如果你最近就在处理 chatgpt plus中国怎么付费,先把入口和咨询方式固定下来,往往比反复…

    ChatGPT 2026 年 4 月 25 日
    42600
  • 2024 GoLand最新激活码,GoLand永久免费激活码2025-02-07 更新

    GoLand 2024最新激活码 以下是最新的GoLand激活码,更新时间:2025-02-07 🔑 激活码使用说明 1️⃣ 复制下方激活码 2️⃣ 打开 GoLand 软件 3️⃣ 在菜单栏中选择 Help -> Register 4️⃣ 选择 Activation Code 5️⃣ 粘贴激活码,点击 Activate ⚠️ 必看!必看! 🔥 获取最新激活…

    2025 年 2 月 7 日
    88700

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

联系我们

400-800-8888

在线咨询: QQ交谈

邮件:admin@example.com

工作时间:周一至周五,9:30-18:30,节假日休息

关注微信