企业官网建设流程全解析

事情是这样的：周一早上刚把一张业务表里的历史数据删了一大批，大概有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是不是ON：

SHOW 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.ibd

4. 真正把空间还给操作系统，有哪些手段

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 ... ENGINE=InnoDB和OPTIMIZE TABLE在这里做的事基本一样——重建表、压缩空间。在有些MySQL版本里，OPTIMIZE会直接映射成这个ALTER操作。

手动执行：

ALTER TABLE your_table ENGINE = InnoDB;

这个操作对InnoDB来说就是重建表，和OPTIMIZE效果大同小异。

但如果你的表特别大，比如1TB以上，即使是低峰期执行也会带来长时间的IO压力和复制延迟。这时候就要靠在线工具了。业内用得最多的两个是pt-online-schema-change（Percona Toolkit里的工具）和gh-ost（GitHub开源的在线表迁移工具）。

它们的基本思路是：创建一个影子表，然后在原表上加触发器（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_LENGTH+INDEX_LENGTH+DATA_FREE))*100,2) AS frag_pct FROM information_schema.TABLES WHERE table_schema = 'business_db' AND table_name = 'order_detail';

结果当时真是“名不虚传”——DATA_LENGTH大约95GB，DATA_FREE高达142GB。碎片率算下来接近60%。也就是说，这张表虽然显示总占用很大，但里面大部分空间其实已经空了，只是没还给操作系统。

随后我看了SHOW ENGINE INNODB STATUS里的History list length，值是2000多，不算高，说明purge没被卡住，碎片真的只是“内部空洞”。

5.3 处理方案和最终结果

因为这张表还有3800万行活跃数据，我的目标是既要收缩碎片，又不能长时间锁写。综合考虑了磁盘余量和业务容忍度之后，我决定用gh-ost来做在线重建。理由是gh-ost不需要触发器，对主库的影响更小，而且在迁移过程中可以做流量控制。

执行命令大致如下（精简过）：

gh-ost \ --host=127.0.0.1 \ --user=admin \ --password=xxx \ --database=business_db \ --table=order_detail \ --alter="ENGINE=InnoDB" \ --chunk-size=1000 \ --max-load="Threads_running=30" \ --execute

跑了一个多小时，完成切换。切换过程很快，业务几乎无感。再看表文件，从原来的240GB左右降到了105GB左右，磁盘空间多出来100多GB。业务查询的延迟也明显降了，因为之前扫描大量空洞页的代价没有了。

这个案例给我的启发很大：DELETE本身只是第一步，处理碎片才是收尾的关键。生产环境里，删除大表数据永远要把“后续空间回收”和“复制压力”考虑进去，别只盯着DELETE跑没跑完。

6. 常见问题速查与避坑清单

6.1 问题速查表

6.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可用空间不变化时，你心里就有底了。