企业官网建设流程全解析

MySQL 删了一千万行数据，结果服务器磁盘占用率一点没降，甚至业务还卡了一下。这个问题做后端开发和运维的人迟早会遇到。很多人第一反应是：数据都删干净了，空间怎么还不给我吐出来？但如果你理解 InnoDB 的存储方式，就会知道这并不奇怪，甚至可以说这才是默认情况。本文要讲的就是：为什么 MySQL 删除大量数据后磁盘不释放，该怎么查，怎么真正把空间拿回来，以及千万级数据删除应该怎么安全地做。

先说结论：DELETE 只是把数据从逻辑上标记为删除，物理文件不会自动缩小。想让磁盘空间真正释放，必须触发一次表重建。下面把原因、验证方式、操作方法和避坑点全部拆开讲。

1. 删了千万行不代表数据库已经把空间还给了操作系统

1.1 DELETE 只是把行标记成“可用”

InnoDB 是 MySQL 默认的存储引擎，数据最终落在表空间文件里。执行 DELETE 的时候，InnoDB 做的事情并不是把文件里的字节直接抹掉，而是：

• 把 B+ 树中对应的记录标记为删除状态；
• 把这条记录所在的页放到空闲列表里，方便后续 INSERT 复用；
• 同时记录事务日志和回滚段信息，保证事务可以回滚。

换句话说，你“删掉”的那些数据，还在原来的物理文件里躺着，只是从索引结构上不再对外可见。只要表空间文件没有被重建，文件占用的磁盘空间就不会还给操作系统。

这个逻辑很多人第一次遇到时很难接受，但它是理解整篇文章的基础。你看着信息里面 row 没了，但ls -lh一看，.ibd文件还是老样子，甚至data_free列比删除前多了不少，都是正常现象。

1.2 空间能不能还回去，取决于表空间的物理组织方式

MySQL 里 InnoDB 的表空间有两种主要组织方式：

• 共享表空间（系统表空间）：所有表的数据都可能落在同一个ibdata1文件里；
• 独立表空间（file-per-table）：每个表对应一个单独的.ibd文件。

innodb_file_per_table参数控制这两种模式。MySQL 5.6.6 之后默认是 ON，新创建的表默认走独立表空间。

如果开启了独立表空间，表重建之后，这个表的.ibd文件会变小，磁盘空间能真正释放。如果表还在共享表空间里，情况会更麻烦：即使你对这个表做 OPTIMIZE，也只是把数据在ibdata1内部重新排列，ibdata1这个文件本身不会自动收缩，因为里面还存着表数据字典、回滚段、undo 等其他内容。

所以你在动手之前，第一件事不是直接执行删除，而是先确认自己到底处在哪种模式下。

2. 先确认表空间模式、数据组成和真实碎片率

2.1 第一步：查 innodb_file_per_table 是否开启

在 MySQL 命令行里执行：

SHOW VARIABLES LIKE 'innodb_file_per_table';

输出结果是ON，说明新表都是独立表空间。但已经存在的表不一定就是你想要的模式。如果之前的 DBA 曾经关闭过这个参数，老表可能已经落在ibdata1里面了，后面新建的表才是独立表空间。

想确认某个具体表是不是独立表空间，可以直接去看它的物理文件：

ls -lh /var/lib/mysql/your_db/your_table.ibd

有对应的.ibd文件，说明它是独立表空间。没有的话，就要考虑它可能还留在共享表空间里。

2.2 第二步：用 information_schema 拿到数据量、索引量和碎片量

不要凭感觉估计表有多大，直接查information_schema.TABLES：

SELECT table_schema, 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, table_rows FROM information_schema.TABLES WHERE table_schema = 'your_db' AND table_name = 'big_table';

几个字段的含义需要先讲清：

• data_length：聚簇索引数据页占用的字节数，可以近似看成表数据大小；
• index_length：二级索引占用的字节数；
• data_free：表空间里已经分配给该表、但当前没有被有效数据使用的空间；
• table_rows：估算行数，InnoDB 不会每次死记精确行数，这个值只能当参考。

如果你的data_free相对data_length已经非常可观，比如删除前表数据 100 GB，data_free到了 60 GB，说明表内部碎片很严重。这种状态下查询变慢、插入性能下降、磁盘空间白白被占都是正常的。

2.3 第三步：对照操作系统里的 ibd 文件大小

SQL 层面的信息只能说明“表内空间使用情况”，最终你关心的是操作系统上的磁盘。执行下面几个命令：

ls -lh /var/lib/mysql/your_db/your_table.ibd du -h /var/lib/mysql/your_db/your_table.ibd df -h /var/lib/mysql

注意ls看到的是文件逻辑大小，du看到的是实际占用磁盘块的大小。多数情况下两者接近，但文件稀疏或者刚经过某些特殊操作时会有差异。排查问题时两个都看一眼更稳。

如果df -h显示分区剩余空间很小，同时这个.ibd文件非常大，那么你删除后磁盘没释放的问题，大概率就是表文件没有重建导致的。不要一上来往磁盘坏道、磁盘被写保护这类方向猜，先把数据库本身查清楚。

3. 真正释放磁盘的四种常规操作

3.1 OPTIMIZE TABLE：最直接的整表重建

重建表是释放 InnoDB 磁盘空间最常用的方式。它会把表的数据复制到一个新表空间，然后替换旧文件，同时重新整理索引，消除碎片。

OPTIMIZE TABLE your_db.big_table;

执行完之后，观察返回信息。通常情况下会出现optimize对应的ok状态。如果输出里带着recreate + analyze这类字样，说明 MySQL 实际选择了重建表的方式，不用太过紧张，这是正常执行路径之一。

OPTIMIZE TABLE 有几个前置条件和代价：

• 需要额外磁盘空间，大概和表当前大小相当；
• 执行期间会产生较高的 IO 和 CPU 压力；
• 大表重建耗时可能非常长，不要在业务高峰期直接执行；
• 执行前确认主从架构下从库是否有足够空间和处理能力。

我一般会先看data_free和表大小，如果表只有 2 GB，碎片也不多，直接 OPTIMIZE 没问题。如果表已经 500 GB，就要考虑下面提到的替代方案，或者选择低峰期分批处理。

3.2 ALTER TABLE ENGINE=InnoDB：重建表空间

在 InnoDB 表上执行：

ALTER TABLE your_db.big_table ENGINE = InnoDB;

效果上也是重建表。MySQL 会重新组织数据和索引，旧表空间文件会被替换，文件大小会缩小。

有些环境下OPTIMIZE TABLE会因为索引类型、版本或特殊配置出现提示，用ALTER TABLE ... ENGINE = InnoDB反而更直接。你也可以把它理解成“强制重建”的另一种写法。

MySQL 5.6 之后 InnoDB 的 DDL 支持 Online DDL 机制，不少情况下可以避免长时间锁表，但重建期间仍然有额外磁盘 IO 和临时空间消耗。这里不要被“在线”这个词误导，它只是说允许并发的 DML 操作，不代表不占空间、不耗时间。

3.3 新建表 + 分批搬迁：可控性更高的手工重建

对于超大表，直接 OPTIMIZE 可能不现实，因为执行时间不可控，中途失败恢复也麻烦。更可控的做法是手工建新表，把数据分成多批搬过去：

CREATE TABLE big_table_new LIKE big_table;

然后分批插入：

INSERT INTO big_table_new SELECT * FROM big_table WHERE id BETWEEN 1 AND 100000;

一批批推进，每批之间可以停顿几秒，避免一直占满 IO。全部搬完以后核对行数，再执行重命名：

RENAME TABLE big_table TO big_table_old, big_table_new TO big_table; DROP TABLE big_table_old;

这种方式的优点是每一步你都能控制，批大小、停顿时间、完成进度都清楚。缺点是整个过程如果有新的写入，需要考虑数据一致性问题。常见做法是在低峰期申请短暂停写窗口，或者用工具来完成在线无感迁移。

如果不想自己写脚本，可以用 Percona Toolkit 里的 pt-online-schema-change 一类工具。它能在不锁表的情况下重建表并同步增量数据。工具不是银弹，但处理几十 GB 到几百 GB 的表时，比手工搬迁更省心。

3.4 TRUNCATE 和 DROP：适合清空或归档场景

如果这张表已经不需要保留数据，比如只是临时表、缓存表、或者数据已经导到别处，直接用 TRUNCATE 或 DROP 更干净。

TRUNCATE TABLE 在独立表空间下会删除原来的表文件，并重新创建一个几乎为空的表文件，空间会立刻释放。它和 DELETE 有本质区别：

• TRUNCATE 是 DDL，不是 DML，不能按条件删除，只能清空整表；
• 执行后 AUTO_INCREMENT 会重置；
• 执行过程中表会被锁住，回滚成本极高；
• 在有外键引用的情况下可能无法执行。

DROP TABLE 则直接把表文件删掉，空间释放最快。但它连表结构都没了，通常用于归档完成后的清理，或者迁移后的旧表清理。

注意：TRUNCATE 虽然快，但它的空间释放效果取决于表空间模式。如果表还在共享ibdata1里，TRUNCATE 释放的也只是ibdata1内部的空闲空间，文件本身不一定缩小。

4. 千万行数据操作，不能一把梭

4.1 分批删除要控制圈定范围

很多人删除千万行时习惯写一条大 DELETE：

DELETE FROM big_table WHERE created_at < '2023-01-01';

这条语句看起来很直接，但实际执行时可能会：

• 长时间持有大量行锁；
• 产生巨大的 undo 日志；
• binlog 写入量暴涨；
• 主从复制延迟升高；
• 占满磁盘空间或临时空间。

更稳的方法是分批删。常见写法是：

DELETE t FROM big_table AS t JOIN ( SELECT id FROM big_table WHERE created_at < '2023-01-01' ORDER BY id LIMIT 2000 ) AS b ON t.id = b.id;

每次只删 2000 行，循环执行。批大小可以从 1000 起步，观察磁盘、CPU、锁等待和主从延迟后再调整。如果业务压力大，批与批之间加一个短暂停顿，比如执行SELECT SLEEP(0.2);，给数据库一个喘气窗口。

这里有个容易踩的坑：如果WHERE条件上的字段没有索引，每批都要全表扫描，删得越多越慢。所以删除前先确认筛选字段上有合适的索引，比如created_at字段。索引要建，但不要指望一条过滤条件能覆盖所有场景，实际还是要看执行计划。

4.2 监控锁等待和主从延迟

删除千万行数据，不只是“把语句跑完”的事。你要同时盯着几个指标：

• SHOW PROCESSLIST：看当前会话状态是不是updating，有没有waiting for table metadata lock；
• SHOW ENGINE INNODB STATUS：看锁信息、事务状态；
• 主从架构下：看从库延迟时间，一般能通过SHOW SLAVE STATUS查Seconds_Behind_Master字段；
• 系统层面：关注磁盘 IO 使用率、CPU 负载、剩余空间。

如果发现锁等待明显，或从库延迟持续上涨，先停掉后续批次，等数据库恢复平稳再继续。不要抱着“反正语句已经发出去，跑完就好”的心态硬等，大表操作最忌讳失去控制。

4.3 磁盘空间紧张时的操作顺序

如果磁盘已经告急了，快速释放空间的正确逻辑不是立刻做 OPTIMIZE，而是先找出占用最大的项。

优先按这个顺序排查：

1. binlog 日志：直接看数据目录下binlog.*文件大小，过期的可以用PURGE BINARY LOGS BEFORE ...清理；
2. undo 表空间：长期大事务可能导致 undo 文件膨胀，MySQL 8.0 有自动 truncate 机制，但未必能及时回收；
3. 慢查询日志、错误日志：日志文件被截断过吗？有没有因为没开启日志轮转导致一个文件几十 GB；
4. 临时文件：tmpdir或innodb_temp_tablespaces_dir下是否残留大量下载到一半的临时文件；
5. 大表本身的 .ibd 文件：确认它到底占了多少，判断是否值得重建。

磁盘满了以后，OPTIMIZE TABLE 基本没法执行，因为它本身就需要额外空间。这时候可以先清理 binlog 和日志，买出来一点空间，再规划表重建。

5. 执行之后怎么确认空间真的释放了

5.1 三个核心指标：DATA_FREE、ibd 文件大小、df 剩余空间

重建完表以后，不要只看“没报错”就算成功。我一般会检查三个东西：

• 重新查information_schema.TABLES，看data_free是否明显下降；
• 用ls -lh /var/lib/mysql/your_db/your_table.ibd看文件大小是否变小；
• 用df -h /var/lib/mysql看整个分区剩余空间是否增加。

这三个结果都正常，才能说空间真正释放了。

如果做的是手工搬迁重建，搬迁完成后最好再执行一次：

ANALYZE TABLE your_db.big_table;

目的是更新优化器统计信息，避免执行计划因为旧统计信息选错索引。

5.2 为什么有时执行完空间还是没有立刻变化

空间没变化，通常有几种情况。

第一种是表还在共享表空间里。前面说过，ibdata1不会因为你 rebuild 一张表就自动缩小。这种情况要确认所有业务表都改成独立表空间，然后把数据迁移到新表或新实例，最终重建整个实例才能彻底解决。

第二种是执行过程中有其他文件同步变大。比如你白天做 OPTIMIZE，同时 binlog 写得很猛，那么df看到的剩余空间可能不升反降。这时候把 binlog 生命周期列出来，看看时间点是否和你的操作重合。

第三种是高水位问题。.ibd文件重新创建后，InnoDB 会按一定初始大小扩容。如果你刚重建完，还没写入多少数据，文件可能本身就小。但如果随后立刻有大量并发写入，文件又会扩展。所以判断空间释放要以“重建结束后的稳定状态”为准，不要重建后半小时内就下结论。

5.3 常见报错和对应的排查顺序

我见过不少同学在大表删除或重建时遇到问题，第一反应是“工具不行”或者“MySQL 坏了”。实际上大多数问题出在权限、依赖、输入、参数和资源这几个层面。

核心排查顺序永远是：先看现象，再看输入和条件，然后看环境和参数，最后才怀疑工具本身。不要一上来就把生产表的 OPTIMIZE 操作挂在一台空间不足的实例上硬跑。

6. 防止大表删除变成日常事故

6.1 按时间范围分区，淘汰数据直接删分区

如果一张表的核心查询经常按时间过滤，而且业务允许按时间分段，分区表是很值得考虑的方向。

比如按年份做 RANGE 分区：

ALTER TABLE big_table PARTITION BY RANGE (YEAR(created_at)) ( PARTITION p2020 VALUES LESS THAN (2021), PARTITION p2021 VALUES LESS THAN (2022), PARTITION p2022 VALUES LESS THAN (2023), PARTITION p2023 VALUES LESS THAN MAXVALUE );

后面想删掉 2020 年之前的数据，直接：

ALTER TABLE big_table DROP PARTITION p2020;

这比 DELETE 快得多，而且分区删除后，独立表空间下的文件空间释放也相对干净。注意分区表不能所有场景都无脑用，分区键必须和查询条件匹配，否则分区反而增加复杂度。

6.2 冷热数据分离和归档表

不要把一张表无限期放大。生产环境最怕的不是删除慢，而是压根没有删除和归档策略。

常见做法是：

1. 在业务表旁边建一张归档表，结构保持一致；
2. 每月或每季度把超过保留期的数据搬到归档表；
3. 业务表继续保持轻量；
4. 归档表按季度或年份分区，后续直接删分区。

搬迁同样要分批，可以先：

CREATE TABLE big_table_archive LIKE big_table;

然后按主键范围或时间范围把数据搬过去，搬完核对行数，再从主表分批删除。这个过程不需要停机，但需要脚本和监控配合。

6.3 删除动作要写进运维规范

个人经验里，大表删除最危险的地方不是磁盘没释放，而是操作前没人记录，操作中没人监控，操作后没人验证。

建议在运维规范里固定这几条：

• 任何超过百万行的 DELETE，必须提前确认条件、索引和预计影响行数；
• 执行前记录表大小、索引大小、data_free、剩余磁盘空间；
• 分批删除的脚本必须包含日志输出，每批完成后打印删除行数和当前状态；
• 操作期间连接数、锁等待、主从延迟必须持续监控；
• 操作结束后对比重建前各项指标，验证空间、性能和统计信息。

平时把这些流程做好了，遇到问题才不会手忙脚乱。真到磁盘告急再去研究 MySQL 文件怎么收缩，成本要比平时高很多。

最后说一句：MySQL 删除大量数据后磁盘没释放，不是功能缺陷，而是存储引擎的物理机制决定的。你只需要理解它，然后选择合适的方式触发表重建。小表直接 OPTIMIZE，大表分批重建，生产环境提前做好归档和分区。这套思路明确了，以后再看到千万行级别的数据删除任务，心里就有底了。