首页 文章 精选 留言 我的

精选列表

搜索[全链路智能],共10000篇文章
优秀的个人博客,低调大师

DATA_FREE涨了几十G,表文件纹丝不动——InnoDB标记删除与页合并的完整链路

大家好,我是小耶,写功课只是为了我踩过的坑,你们别再踩了! 刚转行做DBA那会儿,我干过一件现在想起来还脸红的事。 业务方说订单表数据太多,让清理历史数据。我吭哧吭哧写了个`DELETE FROM orders WHERE create_time < '2024-01-01'`,删了三百多万行。删完还挺得意,跑去跟业务方邀功:“删完了,查询快了不少吧?” 业务方看了一眼磁盘监控,说:“快是快了,但磁盘空间怎么一点没少?还涨了?” 我打开`df -h`一看,表文件纹丝不动。再跑`du -sh`,果然还是那么大。当时脑子里只有一个念头:**删掉的数据去哪了?** 后来才搞明白,InnoDB的DELETE,**从来就不是“真删除”**。 先把这件事里绕不开的几个概念说清楚: **标记删除**——InnoDB执行DELETE时,不会立即从磁盘上抹掉这行数据。它只是在行的头部做一个标记,表示“这行已删除,空间可以被复用”。真正的物理清理,是purge线程在后台异步做的。 **purge线程**——InnoDB的后台清理线程。它负责清理标记删除的记录和MVCC的旧版本数据。如果它清理不过来,标记删除的记录会持续堆积。 **页合并**——当相邻两个数据页的空闲空间加起来足够装下一整页时,InnoDB会尝试把两个页合并成一个,释放出一页的空间。触发阈值由`MERGE_THRESHOLD`控制,默认50%。 **DATA_FREE**——`information_schema.TABLES`里的一个字段,表示表空间里已经分配但还没被使用的空间(字节)。它的大小,直接反映“删除空洞”有多严重。 ### 一、DELETE之后到底发生了什么 我那条DELETE执行的时候,InnoDB做的是这样一件事: 在聚簇索引和每一个二级索引里,把这三百万行**标记为删除**。数据页上的记录其实还在,只是被贴上了“可复用”的标签。 purge线程随后开始异步清理。清理完成后,页上的空间变成“空闲”,可以被后来的INSERT复用。但关键在于——**页本身不会被释放回操作系统**。除非这一页变空、且满足合并条件,否则它仍然属于这张表。 这就是“删除空洞”:空间在表内部被标记为可用,但表文件不缩小。 我后来查了一下,那三百多万行删除后,`DATA_FREE`涨了将近20GB。表文件大小?一点没变。 ### 二、页合并为什么没生效 页合并是InnoDB回收空间的唯一内部机制。但它的触发条件,比大多数人想象的苛刻得多。 **条件一:相邻页的空闲空间总和 ≥ 一页的容量。** 一个16KB的数据页,空闲空间得超过50%(超过8KB),它才“有资格”被考虑合并。但有资格不等于会合并——必须和相邻页的空闲空间加起来能装下一整页的内容,InnoDB才会动手。 **条件二:**`MERGE_THRESHOLD`**的阈值限制。** 默认`MERGE_THRESHOLD=50`。大量页的空闲空间卡在40%-49%之间——不够触发合并,但已经浪费了将近一半空间。这些页永远不会被合并,空间也永远不会释放。 **条件三:purge线程必须先完成清理。** 页合并的前提是页上的标记删除记录已经被purge清理掉。如果purge滞后(长事务阻塞、`innodb_purge_threads`不够),页上还有大量“标记删除但未清理”的记录,空闲空间的计算就不准,合并也无法正确触发。 三条凑在一起的结果:删了三百万行,`DATA_FREE`涨了20GB,页合并一次都没触发。表文件原地不动,甚至因为新数据插入继续增长。 ### 三、怎么确认空洞有多严重 那次教训之后,我把这个查询加进了日常巡检: ```javascript SELECT TABLE_NAME, DATA_LENGTH, DATA_FREE, ROUND(DATA_FREE / (DATA_LENGTH + DATA_FREE) * 100, 2) AS free_pct FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'orders'; ``` `free_pct`超过30%说明空洞严重,超过50%需要立即处理。 再看purge是不是跟上了: ```javascript SHOW GLOBAL STATUS LIKE 'Innodb_history_list_length'; ``` 这个值持续增长,说明purge清理不过来,旧版本在堆积。如果有长事务在跑,purge会被直接卡住。 ### 四、解决方案:OPTIMIZE的代价与替代 **方案一:OPTIMIZE TABLE** ```javascript OPTIMIZE TABLE orders; ``` 原理是重建表:建一个新表空间,把数据逐行复制过去,删旧表,重命名新表。重建后的表空间紧凑,空洞全部消除。 代价是**磁盘空间短暂翻倍**(新旧表同时存在),执行期间大量I/O,大表可能锁表或阻塞写入。MySQL 8.0的`OPTIMIZE TABLE`对InnoDB走Online DDL,支持并发DML,但大表执行时仍可能引发I/O峰值。 **方案二:ALTER TABLE ... ENGINE=InnoDB** ```javascript ALTER TABLE orders ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE; ``` 效果类似,但语法更明确地指定Online DDL。适用于MySQL 5.6+,对写入的阻塞更小。 **方案三:pt-online-schema-change** Percona的工具,创建影子表、触发器同步增量、原子rename。整个过程对业务透明,但需要额外磁盘空间和更长的执行时间。适合几十GB以上的超大表,且不能接受任何锁表。 **方案四:预防——别一次性大DELETE** 最根本的预防:不要用一次性大DELETE。用游标分批删除,每批几千行,提交后再删下一批。这样purge有机会跟上,页合并也更可能触发。配合`innodb_purge_threads=4`(默认值通常够用),减少长事务。 ### 五、小结 DELETE删了数据空间不释放,根因在InnoDB的标记删除机制和页合并的苛刻条件。`DATA_FREE`是判断空洞严重程度的核心指标。`OPTIMIZE TABLE`能解决但代价大,`ALTER TABLE ... ENGINE=InnoDB`是更安全的在线替代。最根本的方案是避免大删除——分批删除,让purge和页合并有机会工作。 那次教训之后,我再也没写过一次性大DELETE。 **小耶在手,SQL 不愁** 还有什么想了解的,欢迎留言!小耶一定知无不言言无不尽……我们下次见~

资源下载

更多资源
Mario

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长,特征是大鼻子、头戴帽子、身穿背带裤,还留着胡子。与他的双胞胎兄弟路易基一起,长年担任任天堂的招牌角色。

腾讯云软件源

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题,腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构,目前腾讯云软件源站支持公网访问和内网访问。

Rocky Linux

Rocky Linux

Rocky Linux(中文名:洛基)是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版,作为CentOS稳定版停止维护后与RHEL(Red Hat Enterprise Linux)完全兼容的开源替代方案,由社区拥有并管理,支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性,采用模块化包装和SELinux安全架构,默认包含GNOME桌面环境及XFS文件系统,支持十年生命周期更新。

WebStorm

WebStorm

WebStorm 是jetbrains公司旗下一款JavaScript 开发工具。目前已经被广大中国JS开发者誉为“Web前端开发神器”、“最强大的HTML5编辑器”、“最智能的JavaScript IDE”等。与IntelliJ IDEA同源,继承了IntelliJ IDEA强大的JS部分的功能。

用户登录
用户注册