大家好，我是数据库小学妹👋 我踩过的坑，你别再踩。

有回清理历史数据，一张大表删掉了一大半。删完我去看磁盘，几乎没动。我以为删错了，又核对了一遍行数，确实少了。数据没了，空间却还占着。我第一反应是跑 OPTIMIZE TABLE，把空间收回来。结果那条命令跑了很久，磁盘更紧张了，业务也开始报慢查询。后来才分清楚，删数据和回收空间，中间隔着一道重建的成本。

## 删了数据，空间去哪了

InnoDB 删一行，不是马上把它从磁盘上抹掉。它把对应的页标记成可复用，放进表空间的空闲列表。这些页还能被新数据用上。DELETE 之后继续写入，空间是能复用的。可它们不会自动还给操作系统。你看到磁盘没降，是正常的。

碎片也不全是删出来的。主键随机插入、变长字段更新，同样会留空洞。页装满了再插，InnoDB 会做页分裂，把一页拆成两页。行变长放不下，就迁到另一页，原位置留个指针。

空隙攒多了，同样的行数要读更多页。表占的空间变大，扫表的 IO 也跟着涨。还有更隐蔽的一层。页里空着一半，一页能装的有效行就少。buffer pool 缓存的数据跟着变少，命中率会往下掉。空间的问题，最后会变成内存的问题。不过说到底，这些空隙都会算进同一个数。`information_schema.tables` 里的 `DATA_FREE` 就是这块空间。

```sql
SELECT table_name,
       ROUND(data_length/1024/1024, 1)  AS data_mb,
       ROUND(index_length/1024/1024, 1) AS index_mb,
       ROUND(data_free/1024/1024, 1)    AS free_mb
FROM information_schema.tables
WHERE table_schema = 'app_db'
ORDER BY data_free DESC;
```

`free_mb` 大，说明表里空着。但空着不等于能还回去，得先看它是不是在独立表空间里。顺便说一句，`TRUNCATE` 和 `DROP` 会把整张表的空间还给操作系统，`DELETE` 不会。所以要清空整表，别用 DELETE 一行一行删。

## 先问一句：你缺的是空间，还是性能

很多人跑到这一步就直接 OPTIMIZE。先停一下，问自己缺什么。缺空间，才谈回收。缺性能，碎片多半不是主因。查询慢先去查执行计划、索引、统计信息，别拿 OPTIMIZE 当万能药。碎片带来的收益，通常远小于它花的代价。

缺空间也分两种。磁盘还宽裕，那不用管，这些页迟早被新数据复用。磁盘真的紧，才需要缩一次。这里有个前提。只有 `innodb_file_per_table=ON`，每张表才有自己的 `.ibd` 文件，缩表才有意义。要是还开着共享表空间，空间都在 `ibdata1` 里，OPTIMIZE 单表也缩不回操作系统。

```sql
SHOW VARIABLES LIKE 'innodb_file_per_table';
```

## 怎么量碎片，别被 DATA_FREE 骗

`DATA_FREE` 能看个大概，但它有个坑。它统计的是表空间里的空闲页，不完全等于碎片。有些空闲页是预留的，有些才是碎片，混在一起。所以别只盯一个数，先把比例算出来。如果表是分区表，`information_schema.tables`里的DATA_FREE是估算值，更准确的方式是查`INFORMATION_SCHEMA.PARTITIONS`，把各分区的DATA_FREE加起来。

```sql
SELECT table_name,
       ROUND(data_free / (data_length + index_length + data_free) * 100, 1) AS frag_pct
FROM information_schema.tables
WHERE table_schema = 'app_db' AND data_free > 0
ORDER BY frag_pct DESC;
```

一般超过百分之二三十，才值得往下谈回收。可比例只是一个数，还得看两样：

- 删掉了多少行，表实际行数降了多少
- 查询有没有真的变慢

只有「删了很多、空间吃紧、查询也受影响」同时成立，OPTIMIZE 才值得做。单独看 `DATA_FREE` 大就动手，多数时候是白忙。

## OPTIMIZE 到底做了什么

它本质是一次表重建。8.0 里 InnoDB 的 OPTIMIZE 会映射成 `ALTER TABLE ... FORCE`，把表重新写一遍，把空洞挤掉。

```sql
OPTIMIZE TABLE t_order;
-- 等价于重建表：ALTER TABLE t_order FORCE;
```

默认走在线 DDL，允许并发写入。但这不代表没代价。第一，开始和结束要拿元数据锁。正好有长事务或慢查询在表上，它就排队等，后面的 DML 也跟着堵。第二，要额外空间。重建时得写一份新的表文件，大小接近原表。磁盘本来就紧，这一下可能直接写满。这也解释了我那次磁盘为什么更紧张。

第三，很吃 IO，也会产生大量 redo 和 binlog。大表重建，主从延迟跟着起来。第四，它是一次性的。表以后继续删、继续写，还会再产生空洞。别把它当日常保养。

还有个附带效果。重建会把二级索引一起重写，统计信息也重新采样。有些表重建后查询变快，不是碎片少了，是统计信息终于对了。这个收益换个方式也能拿到。别把功劳全算在碎片上，更别为了它去动一张大表。

## 该不该做，看这张表

| 场景 | 建议 |
| --- | --- |
| 表不大、磁盘紧、低峰窗口够 | 可以 OPTIMIZE |
| 大表、写入热点、窗口不够 | 别直接做，走在线工具或分区 |
| 碎片占比不高 | 不值得，收益有限 |
| 查询慢但碎片不是主因 | 先查索引和执行计划 |
| 共享表空间（file_per_table=OFF） | OPTIMIZE 单表缩不回去 |
| 刚删完、后面还有大量写入 | 先别做，空页很快被复用 |
| 碎片高但表只有几十 MB | 收益太小，不值得开窗口 |

大表要重建，我更倾向两种做法。一种是分区滑动。按时间分区的表，历史分区直接 `DROP PARTITION`，空间立刻回来，不用重建整张表。前提是表和查询都按分区键走。

另一种是在线重建工具。gh-ost 或 pt-online-schema-change，边搬数据边限速，能控制对主库和主从的影响。

```bash
# pt-osc 在线重建，带限速
pt-online-schema-change \
  --alter "ENGINE=InnoDB" \
  --max-load Threads_running=30 \
  --critical-load Threads_running=60 \
  --sleep 0.5 \
  D=app_db,t=t_order
```

`--max-load` 和 `--critical-load` 是两道刹车，压力高了自动停。比裸跑 OPTIMIZE 稳得多。两个工具的路子不一样，选之前先看清楚。

| 工具 | 怎么搬增量 | 代价 |
| --- | --- | --- |
| pt-osc | 建触发器，把增量同步进影子表 | 每行写入多一层触发器开销，外键场景受限 |
| gh-ost | 订阅 binlog，把增量回放进影子表 | 依赖 ROW 格式，磁盘要留两份表的余量 |

两个都要额外磁盘，也都绕不开 cut-over 那一刻的短暂锁。工具能限速，省不掉空间。空间够不够，先算再决定。下面这条查出重建大致要多大，再拿 `df -h` 对一下余量。留不到原表的一点二倍，就别在这台机器上动手。

```sql
SELECT table_name,
       ROUND((data_length + index_length) / 1024 / 1024, 1) AS rebuild_mb
FROM information_schema.tables
WHERE table_schema = 'app_db' AND table_name = 't_order';
```

## 真要跑，盯住这几点

重建一开始，先确认别被元数据锁堵住。有长事务持着这张表的 MDL，OPTIMIZE 就得一直等，后面的 DML 跟着排队。

```sql
SELECT waiting_pid, waiting_table, blocking_pid, sql_kill_blocking_query
FROM sys.schema_table_lock_waits;
```

waiting 里是 ALTER TABLE，blocking 是一条长事务，那就别干等。先把那条事务处理掉。跑起来之后，盯三样：主从延迟、`Threads_running`、剩余磁盘。哪个不对就停。要停可以 KILL。但在线 DDL 有回滚，回滚本身也要时间，别以为停一下就立刻干净。

## 缺的是空间，才谈回收

OPTIMIZE 不是日常保养，是一次手术。手术要有指征，要备血，要挑时间。你缺的是磁盘，才谈回收。缺的是速度，先查别处。把两件事混在一起，就容易在不该动刀的时候动刀。结果业务也跟着慢。

还有一种情况容易被忽略。表定期清，机器却一直没扩容。每次靠 OPTIMIZE 硬挤，挤出几十 G，过两个月又满。这是在拿手术替代体检。空间规划得回到容量和分区设计上。一张表一张表地救，救不过来。

## 避坑清单

删了数据磁盘不降是常态，InnoDB 不会自动把页还给操作系统。别一看没降就慌，先看磁盘还够不够。

OPTIMIZE 要额外空间、要拿 MDL、吃 IO。大表别直接跑，低峰、留足空间，或用在线工具限速。

最后一条是我自己踩的。我有回磁盘紧，直接对大表跑了 OPTIMIZE，没算临时空间。跑到一半磁盘更满了，业务查询也跟着慢。后来才知道，重建要一份接近原表大小的空间。现在我做之前，先看剩余空间够不够原表的一点二倍。不够就先扩容，或者换方案。

## 写在最后

删数据容易，回收空间要算账。重建的成本、锁的等待、IO 的冲击，还有一份额外空间，全在账上。这笔账也让我重新认识了「删除」。在数据库里，大多数删除只是逻辑上的。行没了，空间还在，空洞替你记着发生过什么。

想让它真的回来，得有人挑时间、留余量、承担一次完整的重建。这件事没有捷径。代价不会消失，只会换个地方付。

所以碎片这道题，难在判断，不在命令。你缺的是磁盘还是速度，现在该动还是该等，都得自己拿主意。磁盘会满，业务会忙。能在这两件事之间做对一次选择，比会敲十条命令值钱。

你们的表碎片是定期清，还是磁盘告警了才清？评论区聊聊。

我是数据库小学妹，帮你少走弯路少踩坑，咱们下篇见👋


有回清理历史数据，一张大表删掉了一大半。删完我去看磁盘，几乎没动。我以为删错了，又核对了一遍行数，确实少了。数据没了，空间却还占着。我第一反应是跑 OPTIMIZE TABLE，把空间收回来。结果那条命令跑了很久，磁盘更紧张了，业务也开始报慢查询。后来才分清楚，删数据和回收空间，中间隔着一道重建的成本。

MySQL表碎片怎么清理？OPTIMIZE TABLE的3个代价与替代方案

从删了大半张表磁盘没降、跑 OPTIMIZE 反倒更紧张切入，讲清 InnoDB 删数据不还空间的原因、DATA_FREE 的判断边界、OPTIMIZE 的在线重建代价与额外空间，给出该不该做的决策表，以及分区滑动与在线工具两种大表替代方案。

编程语言

MySQL删除大表数据后磁盘空间未释放，这是InnoDB的页复用机制所致，需用OPTIMIZE重建表回收空间，但成本高、需额外磁盘、有锁风险。先通过DATA_FREE与碎片比例评估，结合查询性能判断是否值得，大表建议用在线工具或分区方案替代，空间充足时也可暂不处理。

代码解释

数据库

腾讯云数据库特惠，新用户首单0.5折起


文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

MySQL表碎片怎么清理？OPTIMIZE TABLE的3个代价与替代方案-腾讯云开发者社区-腾讯云

MySQL表碎片怎么清理？OPTIMIZE TABLE的3个代价与替代方案

MySQL表碎片怎么清理？OPTIMIZE TABLE的3个代价与替代方案

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐