事情是这样的周一早上刚把一张业务表里的历史数据删了一大批大概有100多GB结果一查磁盘可用空间纹丝不动。当时心里咯噔一下第一反应是“DELETE没删干净”可SELECT COUNT(*)一看数据量确实少了七成。后来查了一圈资料、做了一堆实验才搞明白这根本不是没删干净而是我对InnoDB的存储机制理解得太浅。这个场景在MySQL的日常运维里太常见了——DELETE删完数据表空间文件.ibd却不缩水、操作系统磁盘空间也不释放。别说新手很多干了几年的后端开发遇到这个问题也会懵。这篇文章就专门把这件事讲透DELETE到底做了什么、为什么空间不释放、怎么量化碎片程度、真正要收缩空间该怎么做以及我在生产环境里踩过的坑和最终的处理方案。如果你负责MySQL的维护或者工作中经常要删大表数据这篇文章能帮你省下不少排查时间。1. 先搞清楚一件事DELETE 到底删了什么1.1 InnoDB 的“标记删除”机制很多人对DELETE的认知停留在“把数据从表里抹掉”但在InnoDB存储引擎内部事情远没有这么简单。当你执行DELETE语句时InnoDB并不会立刻把物理文件里的字节抹掉而是先把目标行记录标记为deleted状态。什么意思我打个比方你有一个装满文件的抽屉DELETE不是把文件拿出来丢掉而是往文件上贴了一张“已删除”的便利贴然后文件还占着原来的位置。后面再来新数据的时候InnoDB看到这张便利贴知道这个位置可以复用了就可以把新数据写上去。但如果一直没有新数据进来这些被打上标记的记录就那样“占着坑”文件大小自然一点都不会变小。这里要补充一个细节这些被标记为删除的记录最终是由后台的purge线程来清理的。InnoDB在事务提交后会由purge线程回收这些标记记录占用的空间让它们进入可复用状态。但注意是“可复用”不是“还给操作系统”。也就是说文件系统层面看到的表文件大小在这整个过程中始终不变。1.2 为什么数据文件不会自动收缩你可能会想既然purge线程把空间回收了为什么.ibd文件不跟着缩小这就涉及InnoDB表空间的管理策略了。InnoDB在向操作系统申请磁盘空间的时候通常是一次性申请比较大的“区”extent一个区默认是1MB里面包含64个连续的页page每页默认16KB。文件一旦扩张上去InnoDB就没有动力把它再缩回来——因为收缩文件需要额外的开销而且如果明天又要插入大量数据文件扩展又是一个耗时操作。与其反复横跳不如保持文件大小不变把内部空间循环利用。再说直白一点InnoDB把“空间是否释放”这件事分成了两层。第一层是在表空间内部标记哪些页可用于复用这一层每时每刻都在做第二层是真正把空间还给文件系统这一层默认情况下不做。文件就像一块地地是买断的哪怕上面只有几栋楼也不会退给政府。1.3 除了表数据还有 undo log 和 binlog 在“偷走”磁盘有时候你删了数据磁盘空间不减反增这里面还有个容易被忽略的因素——undo log。DELETE操作本身是一个需要支持回滚的操作所以InnoDB在删除记录之前会把旧值写入undo log。如果一张表的数据量特别大或者你一次性删除的行数特别多这个undo log的体量是非常可观的。尤其是当你开了独立undo表空间MySQL 8.0默认如此这些undo文件比如undo_001、undo_002会变大然后呢它们也不会自动收缩。另外MySQL的binlog也会记录DELETE语句。如果你删一百万行binlog里就会记录一百万行的完整前镜像短时间内binlog文件会暴涨。所以有些场景下DELETE一执行完你查磁盘空间发现不仅没释放反而多占了一截——这部分往往是binlog和undo log的“功劳”。2. 对号入座你遇到的是哪一种“空间没释放”2.1 第一步看表是独立表空间还是共享表空间MySQL的系统变量innodb_file_per_table控制着每张表的数据存放方式。默认情况下它是ON也就是每个表的数据和索引单独存放在一个.ibd文件里。这种情况下DROP表和TRUNCATE表都能立刻释放磁盘空间。但如果它被设置成了OFF那么所有表的数据都会存放在共享表空间ibdata1里面。ibdata1这个文件一旦扩大基本上就不可能自动收缩即使你把里面的表全删了文件还是那么大。共享表空间时代的老MySQL经常出现ibdata1膨胀到几十GB甚至上百GB的事故原因就在这里。所以排查空间不释放的第一步就是先确认你那台实例的innodb_file_per_table是不是ONSHOW VARIABLES LIKE innodb_file_per_table;如果是ON那么删除数据后空间不释放问题出在表内部如果是OFF那事情就麻烦了共享表空间不能简单通过OPTIMIZE来收缩只能重建整个实例或者迁移数据这是另一个级别的运维事故了。还好现在新装的MySQL基本都是ON。2.2 第二步区分“文件系统 df”和“MySQL 统计”两个维度很多人在排查时容易混淆两个概念操作系统的磁盘剩余空间和MySQL内部的表空间统计。它们是两个层面的事情。操作系统的df命令看到的是文件系统层面的信息也就是.ibd、ibdata1、undo这些文件实际占用的磁盘块。MySQL内部的information_schema.TABLES表里的DATA_FREE字段描述的是InnoDB内部已经识别的可复用空闲空间也就是页内碎片加上完全空闲的区。这个值跟你用df看到的空间没有直接对应关系。为什么要区分这两个维度因为有时候你会看到DATA_FREE很大比如有几GB但df显示磁盘空间没有减少。这说明InnoDB确实已经回收了页但这些页只是被标记为空闲并没有还给操作系统。换句话说“内部空间已经释放”和“磁盘空间已经释放”是两件完全不同的事遇到DELETE后磁盘空间没释放先搞清楚你问的是哪一个别拿着df的结果去骂InnoDB不干活。2.3 第三步小心长事务让 purge 线程干不完活DELETE一条SQL瞬间执行完但清理工作才刚刚开始。InnoDB的purge线程需要把被标记删除的记录真正从索引页里移除并且清理对应的undo log。这里有一个至关重要的影响因素是否有长事务持有了这些记录的旧版本。假设有一个事务在DELETE执行前就开始了它一直不提交那么它可能需要读到DELETE之前的数据快照。为了保证这个一致性视图InnoDB不能让purge线程把那些旧版本的undo log清除掉。结果就是DELETE执行完了但purge线程被卡住被标记删除的记录迟迟得不到清理表空间内部会出现大量无法复用的空间。怎么判断是不是这个原因最直接的方式是看这条命令的输出SHOW ENGINE INNODB STATUS\G重点关注History list length在TRANSACTIONS段落里。这个值表示当前有多少个“历史版本”没有被purge。正常情况下这个值应该很小或者接近0如果它涨到几十万甚至几百万基本可以断定是长事务或者purge线程疲劳导致的。3. 怎么量化表里的碎片和空间占用3.1 用 information_schema 查表空间关键字段在你决定要不要处理之前先量一下表的空间状态。information_schema.TABLES表里藏着最直接的数据关键字段有这几个DATA_LENGTH数据部分占用的字节数INDEX_LENGTH索引部分占用的字节数DATA_FREE表空间内部空闲空间碎片的字节数TABLE_ROWS估算的行数拿到这三个值之后可以算出一个“碎片率”公式很简单碎片率 DATA_FREE / (DATA_LENGTH INDEX_LENGTH DATA_FREE) * 100%这个值越高说明表里“空洞”越多空间利用率越低。一般来说碎片率超过30%就值得关注了超过50%基本上可以安排一次表重建。3.2 直接抄走的 SQL 查询脚本我平时排查空间问题习惯一次性把库里的表都扫一遍按数据量排序顺带看碎片率。下面这条SQL可以直接拿去用稍作修改就行SELECT table_schema AS 库名, table_name AS 表名, ROUND((DATA_LENGTH INDEX_LENGTH) / 1024 / 1024 / 1024, 2) AS 表总大小(GB), ROUND(DATA_FREE / 1024 / 1024 / 1024, 2) AS 空闲空间(GB), ROUND(DATA_FREE / (DATA_LENGTH INDEX_LENGTH DATA_FREE) * 100, 2) AS 碎片率(%), table_rows AS 估算行数 FROM information_schema.TABLES WHERE table_schema NOT IN (mysql, information_schema, performance_schema, sys) ORDER BY (DATA_LENGTH INDEX_LENGTH) DESC LIMIT 20;这条SQL会列出当前实例里最大的20张表以及它们的碎片率。执行完你就会知道到底哪些表是所谓的“虚胖”——总大小看着很大实际空闲空间占了相当比例。3.3 怎么看结果什么情况需要管看到碎片率高别急着动手。这里有几个判断原则第一如果这张表的数据量很少或者业务以后还会继续插入大量数据那么碎片空间会被慢慢利用起来不一定非要处理。比如一张表总量50GB删掉20GB数据后碎片率40%但如果下个月又要写回30GB数据那这个碎片相当于“预留空间”动了反而没好处。第二如果这张表删完之后就不再写入或者后续只会零星插入那么碎片就是实打实的浪费建议处理。第三如果碎片率很高且表的查询性能下降明显——比如原来走索引很快现在执行计划没变但IO变慢了——那是因为扫描的页变多了数据都散落在带洞的页里这种情况也该做一次表重建。另外提醒一句information_schema里的DATA_LENGTH和DATA_FREE是估算值不是精确值。在频繁插入删除的表上误差可能会比较大但作为判断依据已经足够了。想要精确数值去看实际的.ibd文件大小ls -lh /var/lib/mysql/your_db/your_table.ibd4. 真正把空间还给操作系统有哪些手段4.1 OPTIMIZE TABLE 的原理与限制如果确认了表碎片严重、需要把空间还给磁盘最经典的手段就是OPTIMIZE TABLE。它的原理扒开看其实不复杂创建一个新的临时表把原表的数据一行一行插入新表重建所有索引最后用新表替换原表。这个过程相当于把散落的数据“重新码放”到连续的页里排空了碎片新表文件写到哪里算哪里所以旧表文件会被清理掉磁盘空间随之释放。实际操作很简单OPTIMIZE TABLE your_table;MySQL 5.7和8.0里这条语句会直接触发一次在线表重建但要注意几个硬伤执行期间需要大约相当于原表1倍的额外磁盘空间。因为旧表还没删新表同时在写两边都要占地方。如果表很大执行时间会很长期间虽然允许DML继续在线DDL但大量的IO可能会拖垮业务。磁盘空间本身就紧张的时候OPTIMIZE可能因为空间不足直接失败甚至造成更严重的问题。所以我的习惯是执行OPTIMIZE之前先确认磁盘剩余空间大于表大小并且选择业务低峰期执行。如果条件不满足不要硬上。4.2 用 ALTER TABLE ENGINE 和在线工具曲线救国其实ALTER TABLE ... ENGINEInnoDB和OPTIMIZE TABLE在这里做的事基本一样——重建表、压缩空间。在有些MySQL版本里OPTIMIZE会直接映射成这个ALTER操作。手动执行ALTER TABLE your_table ENGINE InnoDB;这个操作对InnoDB来说就是重建表和OPTIMIZE效果大同小异。但如果你的表特别大比如1TB以上即使是低峰期执行也会带来长时间的IO压力和复制延迟。这时候就要靠在线工具了。业内用得最多的两个是pt-online-schema-changePercona Toolkit里的工具和gh-ostGitHub开源的在线表迁移工具。它们的基本思路是创建一个影子表然后在原表上加触发器pt-osc或者模拟从库的binlog应用gh-ost把DDL期间的新增修改同步到影子表全部同步完成后用一张rename操作把影子表切换成正式表。这样能在不阻塞写操作的情况下完成表重建对在线业务友好得多。我个人的建议是超过100GB的表做空间收缩优先考虑gh-ost或者pt-osc不要直接在生产上跑OPTIMIZE除非你能接受业务阻塞风险。4.3 什么时候可以一劳永逸DROP 和 TRUNCATE如果那张表本身就是要清空——比如日志表、临时表、过期数据表——那别用DELETE直接上TRUNCATE。TRUNCATE TABLE的原理是直接重建表空间也就是把原来的.ibd文件丢弃重新建一个空文件。这个过程干净利落磁盘空间能立刻释放。它没有逐行标记删除的过程也不会有碎片残留。DROP TABLE就更不用说了直接把整个表对象从实例里移除空间也立刻归还操作系统。但这里必须强调一个原则TRUNCATE和DROP都是不可回滚的操作前一定要确认备份和业务影响面。我见过不止一次有人本想清空一张临时表结果因为表名写错把一张核心业务表TRUNCATE了。这种时候再牛的DBA也救不回来。4.4 磁盘已经见底时的应急方案最棘手的情况是磁盘所剩空间不到10%表需要收缩但又没法直接OPTIMIZE因为没有足够额外空间。这时候有两条路可以走。第一条路先清掉占用空间的“非表数据”。比如flush掉过大的binlog、检查undo表空间是否能收缩、看看有没有慢查询日志或错误日志膨胀。这些操作相对轻量能先腾出一点间隙。PURGE BINARY LOGS BEFORE NOW() - INTERVAL 6 HOUR;这条命令可以清理几小时前的binlog释放空间立竿见影。第二条路如果腾出来的空间还是不够那就只能走“导出再导入”的老办法。用mysqldump或者mydumper导出数据然后删掉旧表再导入新库。这个过程要停机但是对磁盘的额外需求最小——只要你导出的dump文件本身不会塞满磁盘。一条应急参考命令mysqldump -uuser -p --single-transaction --quick your_db your_table /backup/your_table.sql导出完成后DROP旧表再导入mysql -uuser -p your_db /backup/your_table.sql这个办法丑是丑了点但关键时刻能救命。5. 一次生产环境“删除100GB后磁盘不降”的处理实录5.1 现场情况与初步排查去年我们有一套业务系统某张订单明细表两年积累了大概320GB的数据。产品说只需要保留最近3个月的数据历史数据可以清掉。我估算了一下要删除的数据差不多有220GB。当时我的第一反应是这不能用一条DELETE直接删。原因有三点一次性删除220GB的数据undo会爆炸binlog会爆炸从库延迟会追不上删完之后表空间必定产生大量碎片空间不一定降得下来业务高峰期这么干行锁竞争和IO压力会把库拖垮。所以我给产品提的方案是分批删除每批5万行循环执行。脚本长这样DELETE FROM order_detail WHERE create_time 2024-01-01 LIMIT 50000;这个SQL在存储过程里循环调用每跑完一批sleep 2秒既能控制压力又能逐步提交事务避免undo无限增长。5.2 拆解执行和后续观察用了大概3个小时删完了所有的历史数据。行数从1.2亿降到了3800万效果非常显著。但当我执行df -h一看可用的磁盘空间只增加了不到2GB。那一刻我确认了这就是典型的DELETE之后表文件不收缩。接着我查了这张表的碎片情况SELECT table_name, ROUND(DATA_LENGTH/1024/1024/1024,2) AS data_gb, ROUND(DATA_FREE/1024/1024/1024,2) AS free_gb, ROUND((DATA_FREE/(DATA_LENGTHINDEX_LENGTHDATA_FREE))*100,2) AS frag_pct FROM information_schema.TABLES WHERE table_schema business_db AND table_name order_detail;结果当时真是“名不虚传”——DATA_LENGTH大约95GBDATA_FREE高达142GB。碎片率算下来接近60%。也就是说这张表虽然显示总占用很大但里面大部分空间其实已经空了只是没还给操作系统。随后我看了SHOW ENGINE INNODB STATUS里的History list length值是2000多不算高说明purge没被卡住碎片真的只是“内部空洞”。5.3 处理方案和最终结果因为这张表还有3800万行活跃数据我的目标是既要收缩碎片又不能长时间锁写。综合考虑了磁盘余量和业务容忍度之后我决定用gh-ost来做在线重建。理由是gh-ost不需要触发器对主库的影响更小而且在迁移过程中可以做流量控制。执行命令大致如下精简过gh-ost \ --host127.0.0.1 \ --useradmin \ --passwordxxx \ --databasebusiness_db \ --tableorder_detail \ --alterENGINEInnoDB \ --chunk-size1000 \ --max-loadThreads_running30 \ --execute跑了一个多小时完成切换。切换过程很快业务几乎无感。再看表文件从原来的240GB左右降到了105GB左右磁盘空间多出来100多GB。业务查询的延迟也明显降了因为之前扫描大量空洞页的代价没有了。这个案例给我的启发很大DELETE本身只是第一步处理碎片才是收尾的关键。生产环境里删除大表数据永远要把“后续空间回收”和“复制压力”考虑进去别只盯着DELETE跑没跑完。6. 常见问题速查与避坑清单6.1 问题速查表现象底层原因处理建议DELETE后df空间没变InnoDB只做内部标记和复用不收缩文件用OPTIMIZE/ALTER TABLE重建或接受碎片预留DATA_FREE很大但df空间也没变内部页已释放但没归还操作系统执行表重建碎片率高于30%可以考虑DELETE很慢或undo膨胀单次删除行数太多undo log暴涨分批删除每批控制行数并循环提交表空间文件很大但查COUNT(*)很少大量已删除记录仍占用物理空间分析碎片率安排OPTIMIZEHistory list length持续很高长事务阻塞purge线程定位长事务并处理等待purge追平OPTIMIZE TABLE执行失败磁盘剩余空间不足先清理binlog/临时空间或用gh-ost迁移删了数据后binlog暴涨大批量DELETE记录完整前镜像分批删除或在低峰期操作并及时清理binlog6.2 避坑清单说几个我踩过或者看别人踩过的坑每一条都值得记下来。第一不要在大表上一次性DELETE大批量数据。哪怕你的条件筛选得很精确也要限制每次删除的行数。我习惯每次删5000到50000行视表大小和从库延迟来调整。一次删一百万行的后果是什么undo表空间暴涨主库IO飙升从库延迟直接拉满其他业务的查询全部跟着遭殃。第二不要磁盘已经报警了才想起来整理碎片。OPTIMIZE需要额外空间磁盘50%使用率的时候做最稳超过80%就非常危险。如果已经90%以上宁可先清理binlog、临时文件、慢日志也不要直接尝试OPTIMIZE。第三不要在业务高峰期做表空间收缩。不管是用OPTIMIZE还是gh-ost表重建都会有大量IO操作高峰期对CPU、磁盘、网络都是冲击。这类操作永远安排到凌晨或者业务无法感知的窗口里做。第四长事务是万恶之源。删除大量数据之前检查一下当前有没有长事务在跑。可以用以下SQL查一下SELECT trx_id, trx_started, trx_state, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_seconds FROM information_schema.INNODB_TRX ORDER BY trx_started ASC;如果发现某个事务已经跑了几分钟甚至几小时先找业务方确认能不能提交或者杀掉。带着长事务去DELETE大表purge线程会被卡住删完的空间虚挂着不放后续做什么都别扭。第五注意备份策略。有些备份工具比如基于LVM快照或XtraBackup的备份在处理大表重建时可能会有额外的坑。如果是用了gh-ost或pt-osc这类工具操作前要确认binlog格式和从库架构避免触发器冲突。这些都是老生常谈但操作前多想一遍能少熬一个通宵。第六能不用DELETE就不用DELETE。如果你的业务有“定期清理历史数据”的需求趁早设计分区表或者做数据归档。比如按月分区的日志表过期的分区直接DROP空间秒释放DELETE的种种问题全部绕开。我后来给那套订单系统做的改造就是引入了按月归档和分区策略现在清历史数据再也不用开脚本分批DELETE了。6.3 一点经验补充最后分享一个很多人忽略的小细节OPTIMIZE TABLE执行完成之后实际上表空间文件会比“当前数据量”稍大一点点这是因为InnoDB会预留一些空间给后续的索引页分裂和插入操作。别指望重建完的文件刚好等于数据量那是不可能也不健康的。看到文件大小和数据量在一个量级内碎片率降到10%以下就已经是理想状态了。还有如果实例里有多张表都需要收缩建议按碎片率从高到低排序每次处理一张每处理完一张就检查一次实例的IO和复制延迟稳扎稳打比一次性全做要安全得多。我在实际运维里的体会是数据库跑久了几乎都会出现“数据删了空间不释放”这类问题它本身不是故障而是一种存储空间管理策略的自然结果。真正需要关注的一是要不要把空间收回来二是用哪种方式收回来。搞清楚这两点看到df可用空间不变化时你心里就有底了。