1. 问题现场为什么表文件没有变小先还原一下最常见的踩坑场景。我做过不少次数据库巡检很多同事第一次遇到这个问题时都是一脸懵一张核心订单表数据量大概有 800GB业务侧说历史数据不需要保留了于是干脆利落地执行了DELETE FROM orders WHERE create_time 2023-01-01删掉了大概 400GB 的数据。删完一看好逻辑上数据确实没了SELECT COUNT(*)结果只剩一半业务正常跑磁盘占用却纹丝不动——ls -lh看 ibd 文件还是 800GB。有同学这时候甚至怀疑是不是删错库了反复确认 WHERE 条件没问题数据也确实查不到了但文件大小一个字都不带变的。这个现象在 MySQL 的 InnoDB 存储引擎下非常典型不是 bug而是设计使然。InnoDB 管理数据的最小物理单位是数据页Page默认 16KB。删数据时InnoDB 做的事情远比我们想象得“保守”得多它不会直接把物理文件里对应的字节抹掉而是把记录标记为已删除在记录的删除标记位上打个标然后把这条记录从正常的链表里摘除。真正占用的磁盘空间还在原地躺着等待后续复用。一句话总结就是delete 操作是逻辑删除不是物理删除。表文件的大小几乎不会因为 delete 掉几百万行就立刻缩小除非你触发了某些特殊机制。这里牵扯出来的是 InnoDB 的 B 树索引结构、数据页管理方式、MVCC 多版本控制、purge 线程回收机制以及表空间文件的扩展与收缩策略整整一长串知识点。这篇文章就把这条链路彻底盘一遍看完你不仅能回答“为什么没减小”还能清楚知道“到底什么时候会减小”“怎么才能让它减小”“日常维护该怎么看待碎片问题”。这篇内容适合谁看刚接手 MySQL 运维、被磁盘告警逼疯的新人 DBA写业务代码但想搞明白数据库底层行为的后端开发以及所有被“删了数据空间没释放”这个问题困扰过的朋友。我会从原理讲到实操把排查手段和解决方案全部摆出来按我的经验把能避的坑都给你标出来。2. InnoDB 删数据的完整链路从标记删除到页内空洞2.1 数据页到底长什么样要搞清楚 delete 之后的连锁反应先得知道数据落在哪、长什么样。InnoDB 的数据是按 B 树组织的表里的每一行记录最终都存在叶子节点上而叶子节点本身就是一个一个的数据页。一个数据页默认 16KB内部大致分成三块区域页头Page Header、页体User Records Free Space Page Directory、页尾Page Trailer。页体里真正存业务数据的是一串单向链表每条记录都通过 next 指针指向下一条物理位置上相邻的记录。页目录Page Directory则是为了加速查找用的它把页内的记录按组划分每组选出几条“代表”记录把它们的地址存到页尾方向的 Slot 里查找时走二分搜索不用从头遍历整个链表。页里还有一个重要的概念叫“空闲空间”Free Space它夹在已用记录区和页目录之间新记录插入时就从这里分配空间。每页的记录并不是排得严丝合缝的中间可能存在一些已经打上删除标记、却还没被清理的旧记录。这就是空洞的雏形。2.2 DELETE 的瞬间标记删除与链表调整当你执行 DELETE 时InnoDB 在索引层面做的事情可以分为几步第一步通过 B 树定位到目标记录所在的叶子页再把具体记录找出来。第二步在这条记录的记录头信息里有一个专门的“delete_mask”标记位把它从 0 置为 1表示这条记录已删除。第三步调整页内链表的指针。已删除的记录会从正常的用户记录链表中摘除同时加入一个叫“垃圾链表”Garbage List也叫 Free List的链表里。第四步更新页头里的相关统计信息比如已删除记录数、空闲空间大小等。特别注意这些操作只发生在内存中的缓冲池Buffer Pool里然后把脏页通过后台线程异步刷到磁盘上的 .ibd 文件。也就是说哪怕你立刻去磁盘上看文件内容可能里面的数据都还没真正改写更别说文件大小了。所以从这一刻起页内的布局就是正常的业务记录还占着一些空间已删除的记录被打上标记、连进了垃圾链表页尾的目录 Slot 里那条被删记录所在的组也可能要做相应调整。但是页的总大小不会变文件的总大小当然也不会变。2.3 页内空洞是怎么产生的页内出现空洞本质上是记录被删除后原本的位置没有被立即腾出来。没有 delete 操作页面里的记录紧密排列空间利用率高。一旦开始高频删除情况就变成了这样假设一个 16KB 的页里原本排了 100 条记录你删了 50 条这 50 条记录还占着物理位置只是被标记为删除。在 InnoDB 眼里这个页实际能再容纳多少新记录呢它不会把新插入的记录直接物理覆盖到那些被标记删除的记录上面而是倾向于从页的空闲空间Free Space里分配。只有当 Free Space 不够用并且垃圾链表里的空间累计到一定程度时InnoDB 才可能触发页内的空间重整。这个过程很像我们住的老房子。你把旧家具搬走了但新家具不是直接放进旧家具原来的位置——如果新家具尺寸和旧的不匹配硬塞进去反而浪费。于是新家具放在空地旧位置继续闲置。时间一长屋里到处都是闲置角落但你还是觉得空间不够用。表文件就是这样被各种碎片撑大的。如果删除操作分散发生在多个页里那几乎所有页都会出现这种小空洞。更麻烦的是update 操作也会产生类似问题——update 可能先把旧记录标记删除、再插入新记录。MySQL 的 InnoDB 默认采用“先删后插”的更新方式所以很多你以为只是 update 的表内部碎片也在慢慢积累。2.4 为什么 InnoDB 不当下就把空间还给操作系统不少人会接着问页里有空洞没关系但 InnoDB 为什么不干脆把整个文件缩回去这里就要讲到 InnoDB 对表空间的区Extent和页Page的管理了。InnoDB 的表空间文件.ibd在物理上按区来扩展一个区默认 1MB也就是 64 个连续的页。无论表的实际数据量多大文件都会被事先划分成很多页而且 InnoDB 倾向于一次性向操作系统申请更大范围的空间而不是每次插入一条记录就去申请一点点。这是为了减少系统调用次数提升写入性能。当你删除数据时释放出来的页或者页内的空间在 InnoDB 内部是被标记为“可复用”的这些空间会进入表空间的空闲页列表Free List。后续如果有新数据插入InnoDB 优先复用这些已经属于本表空间、但暂时空闲的页。问题在于InnoDB 的页和区分配机制非常喜欢复用而不喜欢归还。归还意味着要把文件尾部的区段整体释放再调用操作系统的 ftruncate 或 fallocate 去收缩文件这在高并发环境下是个昂贵的操作而且容易引发性能抖动。更重要的是InnoDB 根本无法判断“未来到底还会不会用到这么多空间”。表可能马上又要大量插入数据如果刚把文件缩小紧接着又要扩展那才是真正的性能灾难。所以 InnoDB 的策略很务实空间复用优先文件收缩留给管理员手动决定。3. 卡住空间回收的几道关键锁链3.1 表空间文件与文件系统的关系先明确一个认知InnoDB 的 .ibd 文件在操作系统看来就是一个普通文件。文件当前的大小代表着它曾经扩展到的最大的“水位线”。只要没有做收缩操作即使文件内部已经有了大量空闲页这个水位线也不会下降——就像一条河流涨过水河岸留下了最高水痕水退了水痕却还在原地。这里有一个重要参数innodb_file_per_table。在 MySQL 5.6 及之后的版本默认开启每张表的数据和索引单独存储在自己的 .ibd 文件中删除数据后如果有回收机制触发也只影响这一张表的文件。如果这个参数是关闭的那么所有表的数据都存放在共享表空间 ibdata1 里文件回收会更麻烦VACUUM/OPTIMIZE 对整个共享表空间的效果也大打折扣。所以如果你看到有人问“我删了数据共享表空间为什么没变小”答案的一部分就藏在innodb_file_per_table的配置上。独立表空间是相对可控的共享表空间基本只能靠导出导入全库来收缩那工程量就大了。3.2 MVCC 与 undo log被删的行暂时还不能物理消失这是很多人容易忽略的另一个关键原因。InnoDB 要实现多版本并发控制MVCC一个事务在读取数据时需要看到某个时间点的快照。如果有人在你 delete 某行之前开启了一个长事务还没提交那么这个事务理论上还能看到这行数据——虽然它已经被打了删除标记。为了支持这种机制被删除的记录不能立即从物理文件里抹掉必须等到所有可能还需要看到它的旧事务都提交之后才能进行真正的清理。InnoDB 会把被删除行的旧版本信息写入 undo log回滚日志同时通过版本链roll pointer把各个版本串起来。当其他事务通过一致性读来访问这行数据时会沿着版本链找到符合自己可见性规则的那个版本。这里有个恶性循环如果系统里长期存在大事务、长事务或者干脆就是有人开启了事务不提交那么 undo log 会持续膨胀history list 长度居高不下purge 线程想清理也清理不动被删除的记录就一直躺着。这时候你去查SHOW ENGINE INNODB STATUS会看到 history list length 数值很大而且 purge 一直追不上。表空间自然就不会有任何缩小的可能因为连页内的垃圾记录都没法清走。3.3 purge 线程真正的空间搬运工InnoDB 的后台线程里有一个专门的 purge 线程负责清理那些已经不再被任何事务需要的旧版本数据。它的工作逻辑大概是定期检查 undo log 里的事务状态判断某些被标记删除的记录是否已经过了所有活跃事务的可见范围如果确认安全就把记录从垃圾链表里真正移除让页内的空间变为可分配的 Free Space。值得注意的是purge 线程只是在页内部做空间整理它不会主动把空闲页归还给操作系统。它的职责是把“不可用的记录”变成“可复用的空间”而不是把“可复用的空间”变成“减小的文件”。很多人以为只要 purge 跑完文件就会变小这是个重大误解。这就好比一间仓库里堆了很多废品已删除记录purge 线程是清洁工它负责把废品装进垃圾桶释放页内空间但运走垃圾桶、缩小仓库面积的是另一拨人——也就是我们要手动执行的 OPTIMIZE TABLE或者通过重建表的方式来实现。3.4 页合并机制唯一“自动”减少文件的机会InnoDB 也有会在某些场景下自动收缩文件的机会吗严格说有一种情况接近“自动”当某个页的数据被删得差不多时如果相邻页的记录总数可以合并到一个页里InnoDB 的 B 树会自动执行页合并Page Merge操作。比如页 A 里只剩 10 条记录页 B 里只剩 20 条记录两页加起来不到一个页的容量InnoDB 可能把 B 的记录合并进 A然后释放 B 这个空闲页。释放出的空闲页会进入表空间的 Free List后续插入数据时优先复用。但问题在于页合并只能减少“页的总数”如果释放出来的页位于表文件的中间区域文件还是不能缩小。只有当大量数据集中在文件尾部被删除InnoDB 才可能在某个时机把尾部的空闲区段Extent整体标记为“可释放”并且通过truncate类操作把文件缩小。这个操作在 InnoDB 里通常不是自动的——即便在页合并触发时也主要针对叶子节点进行整理文件整体收缩仍然缺乏自动化机制。综上你用力删除数据InnoDB 内部确实做了很多工作但从外部看文件一动不动非常正常。4. 怎么检测表文件的碎片与可回收空间问题聊到这里原理已经通透。接下来是实操环节如果你怀疑一张表的文件里有大量碎片或者想判断它到底能不能收缩、能收缩多少怎么查4.1 用 information_schema 查表空间状态最直观的入口是information_schema.TABLES表里面有 DATA_LENGTH、INDEX_LENGTH、DATA_FREE 几个字段。其中 DATA_FREE 代表该表的存储引擎已经分配、但尚未使用的空间字节数。对于 InnoDB 来说这个值可以粗略表示“可复用但没复用的碎片空间”。举一个实际查询SELECT table_name, ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb, ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb, ROUND(DATA_FREE / 1024 / 1024, 2) AS free_mb, ROUND((DATA_LENGTH INDEX_LENGTH) / 1024 / 1024, 2) AS total_mb FROM information_schema.TABLES WHERE table_schema your_db ORDER BY DATA_FREE DESC LIMIT 10;如果 free_mb 相对 total_mb 的比例很高说明这张表内部有不少空隙。但要注意DATA_FREE 统计的是“已分配的页中空闲的部分”它不仅包括 delete 留下的空洞还可能包括新建表时预先分配但尚未写入数据的页。所以 DATA_FREE 高不一定意味着你“浪费”了很多空间但通常可以作为碎片化程度的参考指标。有一种更直观的判断方法查看表的实际数据量然后对比文件物理大小。比如你用SELECT COUNT(*)和AVG_ROW_LENGTH估算出实际数据大约 200MB但 .ibd 文件已经膨胀到 1GB那显然有 800MB 左右的空间处于“已分配但未有效使用”的状态。在重建表之前可以先做这个估算做到心里有数。4.2 通过 SHOW TABLE STATUS 快速验证也可以用SHOW TABLE STATUS命令输出结果里同样包含 Data_length、Index_length、Data_free 字段。不过需要注意这些值是估算值不是精确值而且对于 InnoDB 来说它们来自表空间的元数据精度尚可但别用它做精确的容量规划只做趋势判断和横向对比。我在实际排查中习惯把两种方式结合先跑 information_schema 的全库扫描找出 Data_free 异常大的表再用 SHOW TABLE STATUS 看具体某张表的数据量和碎片情况最后如果要动手整理再结合业务窗口决定用哪种方案。4.3 利用 Performance_Schema 和系统视图进一步定位如果你用的是 MySQL 5.7 及以上版本performance_schema里也有不少关于表 I/O、等待事件的数据可以帮助你判断碎片化是否已经影响到了查询性能。比如同等数据量的表如果物理读的 avg latency 明显偏高页内空洞多导致扫描的行数变多都可能是碎片化严重的信号。但说实话对于绝大多数业务而言不需要把碎片检测搞得太复杂。日常巡检只需关注 Data_free 和文件大小的比例如果超过 30% 或者文件大小明显超出实际数据量就可以考虑空间整理了。性能问题反而通常不会单独由碎片引发更像是碎片加上不合适的索引共同作用的结果。5. 让表文件真正变小的几种实操方案既然 delete 不会自动收缩文件那我们就得自己动手。业界常用的方案大概有五类各有优劣我按推荐程度和适用场景逐个说清楚。5.1 方案一OPTIMIZE TABLE最直接适合中小型表OPTIMIZE TABLE 的表面语义是“优化表”实际上对于 InnoDB 来说它会重建整张表。执行时InnoDB 会新建一份临时表把原表的数据按主键顺序重新插入插入过程中只保留有效记录那些已删除的、标记为可复用的空间直接丢弃。临时表构建完成后在原表的表空间上做一次原子替换最后删除旧文件。效果立竿见影表文件变小索引更紧凑碎片小时DATA_FREE基本归零。但要注意OPTIMIZE TABLE 在 MySQL 5.7 之前对 InnoDB 是锁表的5.7 及之后允许 ONLINE DDL 进行 DML 并发操作但重建过程中仍然会占用大量磁盘空间——临时表需要与原表大小相当的空间。如果你的磁盘可用空间不足执行 OPTIMIZE TABLE 可能会直接报错比如ERROR 1114 (HY000): The table /tmp/#sql... is full。所以用这个方案前先 df -h 看看磁盘空余量。另外OPTIMIZE TABLE 在数据量大的时候耗时很长。我有一次优化一张 200GB 的表跑了一个多小时。期间虽然允许读写但 DDL 本身会对性能产生影响建议放到业务低峰期执行最好配合主从切换或者只读副本先行操作。对于 5.6 及以下版本的用户OPTIMIZE TABLE 会锁表生产环境基本没法直接操作务必评估后再使用。5.2 方案二ALTER TABLE ENGINE InnoDB重建表的等价手段这个命令的本质和 OPTIMIZE TABLE 几乎一样让 InnoDB 重建表。ALTER TABLE table_name ENGINE InnoDB;会把表的数据复制到新表空间然后切换。区别在于它还能顺便修改其他表选项比如行格式ROW_FORMAT、压缩等。如果你有这种需求用这个方案可以一举两得。不过它的时间成本和磁盘需求跟 OPTIMIZE TABLE 一样没有本质差别。在 MySQL 8.0 里官方文档甚至建议直接用ALTER TABLE ... ENGINE InnoDB替代 OPTIMIZE 来做空间回收因为 OPTIMIZE 底层本质上也是重建。具体用哪个看你习惯。5.3 方案三pt-online-schema-change工具党首选适合大表如果你面对的是几百 GB 甚至上 TB 的表OPTIMIZE TABLE 可能从晚间十点跑到第二天早上六点还没跑完业务影响难以接受。这时候就该请出pt-online-schema-change缩写 pt-osc了。它是 Percona Toolkit 里的明星工具专门用于在线表结构变更。pt-osc 的原理很有意思它不会直接改原表而是创建一张结构相同的新表然后在原表上建立三个触发器INSERT、UPDATE、DELETE把在线发生的增量数据实时同步到新表同时分批把原表的历史数据拷贝到新表。等数据拷贝得差不多了通过一个原子性的 RENAME 操作完成新旧切换。这个方案的最大优势是业务可以持续读写对线上影响小。过程可以控制你可以通过--max-lag参数控制复制延迟比如要求主从延迟超过 5 秒就暂停拷贝通过--chunk-size控制每批拷贝的行数通过--critical-load控制数据库负载过高时自动暂停。使用 pt-osc 的基本命令示例pt-online-schema-change \ --alter ENGINEInnoDB \ Dyour_db,tyour_table \ --host127.0.0.1 \ --userroot \ --passwordyourpass \ --max-lag5 \ --chunk-size500 \ --critical-load Threads_running100 \ --execute特别提醒pt-osc 的--alter参数如果是ENGINEInnoDB它只会在重建表时刷新表空间完成空间回收。如果你需要顺手增加字段、改索引也可以把需要执行的 DDL 语句写在 --alter 里一次性完成。缺点也有需要额外安装 Percona Toolkit且在三层主从架构上使用时要小心触发器带来的额外主从复制延迟。另外如果原表已经没有主键或唯一键pt-osc 会默认用PRIMARY KEY没有的话它可能拒绝执行——这是它的安全机制避免数据重放时无法定位记录。这种表建议先补主键。5.4 方案四gh-ost无触发器在线变更方案gh-ost 是 GitHub 开源的在线表变更工具它的核心思路比 pt-osc 更进一步不创建触发器而是通过解析 binlog 的方式把原表上的增量变更实时应用到影子表。这消除了触发器带来的额外开销也减少了对主库性能的影响。使用 gh-ost 回收表空间的思路跟 pt-osc 类似先创建影子表全量拷贝数据追 binlog最后切换。它的部署和配置稍微复杂但如果你管理的数据库规模很大主库负载敏感gh-ost 往往比 pt-osc 更合适。gh-ost \ --host127.0.0.1 \ --userroot \ --passwordyourpass \ --databaseyour_db \ --tableyour_table \ --alterENGINEInnoDB \ --executegh-ost 在执行时要求在 binlog_formatROW 的模式下运行它依赖行级 binlog 解析如果你的库不是 ROW 格式需要先调整参数并重启这个成本要提前评估。5.5 方案五逻辑导出导入最笨但最彻底如果上面几种方案都因为各种原因不能执行还有一个兜底办法把表导出然后导入到一个新库再切换。mysqldump --single-transaction --set-gtid-purgedOFF your_db your_table table.sql然后把 table.sql 导入到一个全新的库里确认数据完整后再通过 RENAME TABLE 或直接在应用层切换连接。这个方案不仅能把表文件缩小到极致还能顺带整理所有索引甚至可以做一次从 MyISAM 到 InnoDB 的迁移。缺点很明显在数据量大的场景下导出导入耗时极长而且为了保持一致性mysqldump 的 --single-transaction 依赖 MVCC长事务期间 undo log 会膨胀可能影响整个实例。所以这个方案更适合数据量中等的表或者做全库迁移时顺带完成碎片整理。6. 碎片整理实战一次完整操作记录与演练光讲方案不给实战记录总觉得少了点说服力。这里分享一个我实际做过的操作过程从排查到优化完的完整链路你可以直接顺着思路在自己环境里演练一遍。背景某业务系统有一张日志表 log_record平时写入量大业务定期删除超过 90 天的数据。表文件 45GB但实际数据只有约 18GB。业务反馈这表查询越来越慢查看执行计划之后发现全表扫描的频率变高怀疑碎片太多导致扫描页数过多同时磁盘空间告警也需要释放空间。第一步确认现状。SELECT ROUND(DATA_LENGTH / 1024 / 1024 / 1024, 2) AS data_gb, ROUND(INDEX_LENGTH / 1024 / 1024 / 1024, 2) AS index_gb, ROUND(DATA_FREE / 1024 / 1024 / 1024, 2) AS free_gb FROM information_schema.TABLES WHERE table_schema app_db AND table_name log_record;结果data 大约 14GBindex 4GBfree 占了 27GB 左右。文件 45GBfree 占比夸张到 60%已经非常需要整理了。第二步确认业务窗口和主从状态。这个表是日志表凌晨 2 点到 5 点写入量相对较低可以短暂接受额外负载。同时确认只读副本的延迟在正常范围方便切流量。因为没有在主库执行 DDL 的强烈需求最终选定用OPTIMIZE TABLE直接处理原因是表大小 45GB虽然不小但磁盘还有 100GB 可用空间足够支撑重建过程中的临时文件需求。第三步执行优化。OPTIMIZE TABLE app_db.log_record;执行期间我持续观察了三个指标主库的线程数有没有明显飙升磁盘空间是否足够redo log 的写入量是否异常整个过程大约花了 26 分钟。优化完成后再次查询 information_schemafree_gb 归零整个表文件从 45GB 降到约 18.5GBDATA_LENGTH 也有小幅减少因为紧凑重建后行存储更规整部分变长字段的存储效率也提升了。业务反馈全表扫描类查询的耗时平均降低了将近 40%。这个案例印证了一点对于日志类、流水类的周期性删除表碎片化是常态优化一次能管相当一段时间。但如果业务写入删除非常频繁优化完过几周碎片可能又积累起来了需要纳入常态化的空间巡检。7. 日常运维视角怎样对待表碎片与空间回收7.1 什么时候必须要整理碎片不是所有碎片都需要立刻处理。我建议按场景分类对待磁盘空间告警需要立刻释放空间这没有商量余地能整理立整理。表文件大小和实际数据量差距悬殊超过 1.5 倍到 2 倍但磁盘还有余量可以放在低峰期做一次整理。查询性能明显下降且能定位到碎片导致扫描大量无用页时需要整理。表长期只有 delete 和 insert没有 update 操作比如流水日志碎片会以较快的速度积累可以规划周期性整理。反过来如果表数据量不大、碎片不多就不必费劲去做 OPTIMIZE TABLE。每次重建表都有代价没必要为了“优化”而优化。7.2 共享表空间的使用者要特别小心如果你的实例里还存在使用共享表空间ibdata1的表整理起来要格外谨慎。共享表空间一旦膨胀基本只能通过导出全库、删除 ibdata1、重新导入来收缩复杂度极高。所以强烈建议确保innodb_file_per_tableON从源头保证每张表的空间独立可管理。这个参数虽然是默认开启的但仍值得在初始化实例时显式确认防患未然。另外注意即使开启了独立表空间ibdata1 里还存放着 undo log、数据字典等信息它本身也可能膨胀。如果你发现 ibdata1 很大单纯删除业务表数据不会让它缩小。需要结合innodb_undo_tablespaces参数MySQL 5.7 及以上把 undo 独立到单独的表空间文件里从机制上缓解共享表空间的膨胀问题。7.3 硬链接法快速释放表文件空间的另类技巧有一个冷门但实用的技巧值得分享通过硬链接 删除原文件的方式绕过 OPTIMIZE TABLE 的长时间锁表问题。思路是这样的当你确定一张表要做空间回收但无法忍受在线重建的耗时可以先用ln命令给 .ibd 文件创建一个硬链接。硬链接可以理解为给文件多起了一个名字但底层 inode 是同一个磁盘空间不会重复占用。然后你在数据库里执行DROP TABLE或先改名再 DROP数据库会删除它对应的那一个链接但文件数据仍然存在因为硬链接还指着它。这时候磁盘空间并没有真正释放文件还被硬链接占着。第三步在业务低峰期把硬链接文件删除比如用rm命令磁盘空间会真正归还给操作系统。同时因为 DROP TABLE 操作本身很快业务不可用窗口很短。这个技巧适合对可用性要求极高、又不方便跑在线 DDL 的场景。但必须强调这个方法有一定风险。如果你对硬链接机制不熟悉误删了唯一的硬链接数据就真的丢了。实施前一定要做好备份并且演练一遍。数据安全永远是第一位的。7.4 用分区表从根源上规避 delete 带来的空间问题如果你在设计表结构时就有清理历史数据的需求比频繁 delete 更高明的方式是使用分区表。按时间字段做 RANGE 分区比如按月分区清理一个月的数据时直接DROP PARTITION这个操作是纯元数据级的速度极快而且被删除分区占用的空间会立即被 InnoDB 释放表文件不会像 delete 那样残留空洞。举例来说ALTER TABLE log_record DROP PARTITION p202301;执行完p202301 分区的所有数据连同空间一起被释放不需要重建表也不需要 OPTIMIZE。这是目前处理超大规模历史数据清理的主流方案。当然分区表也有自己的坑比如分区数量过多会产生大量文件句柄查询没有带分区键时会扫描全部分区导致性能下降。用之前要充分测试。7.5 设置合理的 innodb_page_cleaners 和 purge 参数如果碎片问题经常出现还是建议顺便检查一下 InnoDB 的清理相关参数配置。比如 MySQL 5.7 及以上的版本innodb_purge_threads用于控制 purge 线程数量适当调大比如从默认的 1 调到 4可以加快清理速度减少 history list 积压。innodb_max_purge_lag可以设置触发 purge 延迟的阈值防止大量事务并发时影响性能。不过参数调整要适度过多线程在某些负载下反而会争抢内部锁。我一般建议先观察 SHOW ENGINE INNODB STATUS 里的 history list length 变化趋势如果持续增长再考虑调参。8. 常见问题排查与避坑指南根据这些年踩过的坑和群里同行们的常见问题整理了一份速查表基本覆盖了 delete 后空间不释放的各种场景和辅助判断场景现象核心原因解决方向删了一半数据文件没变小表文件大小不变InnoDB 标记删除 文件高水位不降重建表OPTIMIZE / ALTER TABLE ENGINE文件变大了但数据没增多表空间膨胀高频 delete/update 导致页内碎片文件持续扩展重建表整理碎片调整写入模式文件一直不回收即使重建了也没效果操作之后大小不变可能有长事务持有 undopurge 无法推进检查长事务等待提交或 kill 后重试存在历史大事务删除后空间一直不释放history list 持续增长MVCC 拦截旧版本数据无法清除排查并处理长事务观察 purge 指标主从库同步延迟删除操作同步慢从库文件更大主库删除后脏页刷盘和 binlog 应用有延迟等待追平或优化大事务拆分删除共享表空间 ibdata1 很大ibdata1 无法缩小系统表空间文件不支持在线收缩转独立表空间导出导入迁移使用 mysqldump 导出导入后新库文件更小文件变小且查询更快逻辑导出只包含有效数据重建了所有索引如果逻辑一致性要求高这是最稳妥方案8.1 注意删除数据时也要关注主从延迟如果你是在主库上执行大范围的 delete一定要分批或者限速。我见过不止一次因为一条大 delete 导致主从延迟十几分钟、甚至小时级的案例。大事务产生的 binlog 量大从库应用这些 binlog 时是串行执行的很容易落后。而且大 delete 本身会持有大量行锁影响并发写入。更推荐的做法是分批次删除比如每次删除一万行配合 sleep 控制节奏。DELETE FROM big_table WHERE create_time 2023-01-01 LIMIT 10000;循环执行直到没有数据可删。这样既能保证主从延迟可控也能减少 undo log 的暴增对后续 purge 的压力也小得多。8.2 注意重建表期间不要随便 kill 会话OPTIMIZE TABLE 或者 ALTER TABLE 执行过程中如果由于误操作比如磁盘满了导致 DDL 失败MySQL 会自动清理临时文件但有些场景下清理不干净留下一些后缀为#sql-*.ibd的临时文件它们也会占用磁盘空间而且不会自动删除。遇到这种情况需要手动到数据目录里查找并处理——但前提是确认没有正在运行的 DDL 使用这些文件。还有一个细节在 MySQL 8.0 中ALTER TABLE 支持ALGORITHMINPLACE, LOCKNONE但在底层还是会触发重建。执行前建议用EXPLAIN查看 DDL 的算法选择策略MySQL 8.0 支持EXPLAIN FORMATTREE查看 DDL 计划提前预判是否会导致锁表或者重建。如果你不太确定就先在测试环境变更一次计量时间和空间开销再上生产。8.3 注意监控别只看 table 大小日常巡检时除了看表文件的逻辑大小和 DATA_FREE还要关注文件系统的 inode 使用情况和磁盘块分配情况。如果文件系统在创建大文件时用了稀疏文件特性ls -lh看到的大小可能和实际占用的磁盘块不一致。这时候可以用du -h 表名.ibd看看真实占用。我遇到过一次奇怪的现象ls显示 20GBdu显示只有 5GB这说明文件在文件系统层面是稀疏的实际块没占那么多。这种情况下空间并不会告急也不需要特意做碎片整理。但反过来更要警惕如果你的表文件已经 800GB而磁盘分区只有 1TB那即便你只删了一半数据也不要立刻乐观因为 InnoDB 内部仍可能认为需要保留空间。建议删除前和删除后都做一次du和df记录通过实际数据变化做出判断而不是只盯着ls的结果。8.4 注意binlog 格式和 delete 的空间回收MySQL 的 binlog 格式也会影响 delete 对空间的影响。在 ROW 格式下binlog 会记录每一行被删除前的完整镜像这会让 binlog 文件在删除大批量数据时急剧膨胀。如果你有定期备份和 binlog 清理策略大量 delete 后要注意 binlog 的磁盘占用否则可能出现“数据删了但磁盘反而更满”的情况。举个例子一张大表删除一亿行在 ROW 格式下产生的 binlog 可能比原表还要大因为每行数据都完整记录。这是运维中很容易被忽视的一个坑。有效的处理方式是在批量删除前先把 binlog 切一个新文件FLUSH BINARY LOGS删除完成后及时清理过期 binlog避免磁盘被 binlog 撑爆。如果你用的是 STATEMENT 格式binlog 会小很多但主从同步的准确性在某些场景下需要更小心。9. 留给后续的几个扩展思路空间回收这件事做到 OPTIMIZE TABLE 基本就够解决日常问题了。但如果你管理的数据库规模很大、删数场景很频繁有几个方向可以继续深入冷热数据分离。历史数据定期迁移到归档库或者数据湖在线库只保留热数据从源头上减少大表的存在感。使用 MySQL 8.0 的即时 DDL 能力。8.0 对部分 DDL 做了优化比如INSTANT ADD COLUMN虽然它不直接解决空间回收但可以减少日常表结构变更对重建表的依赖。考虑使用 ClickHouse 等列式存储承载日志类数据。如果你对 MySQL 的碎片清理已经不胜其烦而业务场景主要是写入大、更新小、分析多列式存储可能更合适。这不代表 MySQL 不行而是每个引擎有自己最擅长的领域。按我个人的经验来说数据库的很多“小问题”往深里挖都能通到底层设计哲学。delete 后空间不释放这件事本质上就是 InnoDB 在性能、MVCC 并发控制和文件管理之间做出的权衡。理解了它为什么这么做你再遇到类似问题就不会慌也能在容量规划、表结构设计、清理策略上做出更合理的决策。先把这些基础打牢后面再遇到更复杂的存储引擎调优也就有了底气。