使用OPTIMIZE TABLE命令释放MySQL实例的表空间

MySQL表数据量较大时,通过 DELETE 语句清理数据并不会直接释放磁盘空间,仅会将数据库记录或数据页标记为可重用。若需要真正回收表空间并减少磁盘占用,可通过OPTIMIZE TABLE实现。

前提条件

  • InnoDBMyISAM引擎支持OPTIMIZE TABLE语句。

  • 实例剩余磁盘空间必须大于等于需释放表的空间。

    说明

    如果实例剩余磁盘空间不足,请务必先扩容磁盘空间。后续操作完成后,可按需缩容磁盘空间,系统会退还差价

注意事项

  • 必须先删除大量数据:如果未先通过DELETE删除大量数据,直接执行OPTIMIZE TABLE将无法有效降低表空间使用率。

  • 磁盘空间占用的短暂增加:执行OPTIMIZE TABLE时,MySQL会创建一个临时表来存储重组后的数据,这会导致磁盘空间在短时间内增加。操作完成后,临时表会被删除,磁盘空间占用会恢复正常。

  • 释放后表和索引的统计信息可能没变化:实际磁盘空间已释放,但因MySQL表统计信息未及时刷新所致,详情请参见执行OPTIMIZE TABLE后,RDS MySQL磁盘空间值没变化

  • 性能影响与高峰期风险:在RDS MySQL 5.78.0 中,OPTIMIZE TABLE使用Online DDL方式执行,支持并发DML操作。然而,对大表执行该操作可能引发突发的IOBuffer资源占用,存在锁表或资源抢占风险,业务高峰期还可能导致实例不可用或监控中断。因此,建议选择业务低峰期执行以避免对正常业务造成影响。

通过命令行操作

  1. 通过客户端连接RDS MySQL实例

  2. 使用DELETE语句清理不需要的数据,根据业务实际情况删除即可。

  3. 执行OPTIMIZE TABLE命令,释放表空间。

    OPTIMIZE TABLE <$Database1>.<Table1>,<$Database2>.<Table2>;
    说明
    • <$Database1>与<$Database2>为数据库名,<Table1>与<Table2>为表名。

    • InnoDB引擎中执行OPTIMIZE TABLE语句时,会出现以下提示信息,该信息是正常执行返回的结果,您可忽略信息,确认返回“ok”即可。详情请参见OPTIMIZE TABLE Statement

      Table does not support optimize, doing recreate + analyze instead

通过DMS操作

  1. 通过DMS登录RDS MySQL实例

  2. 在左侧选择目标实例的实例ID,然后双击目标库,右键单击任意表名,然后选择批量操作表

  3. 勾选需要释放空间的表名,然后选择表维护 > 优化表优化表

  4. 在弹出的对话框中确认变更信息正确,然后单击确认即可。

相关文档

回收表碎片空间

常见问题

执行OPTIMIZE TABLE后,RDS MySQL磁盘空间值没变化?

问题描述

用户参考RDS MySQL官网教程执行DELETE删除大量数据并运行OPTIMIZE TABLE回收表空间后,立即查询information_schema.tables中的DATA_FREE字段,发现数值未更新,认为磁盘空间未释放,回收操作无效。

问题原因

实际磁盘空间已释放,但因MySQL表统计信息未及时刷新所致,常见于RDS MySQL 5.6、5.7以及8.0(内核小版本在20250531之前)的版本中,执行OPTIMIZE TABLE后不会自动更新表和索引的统计信息,导致information_schema.tables中的DATA_FREE数据仍保留旧值,无法准确反映实际空间使用情况。问题详情,请参见Bug #117426:optimize table does not update table and index stats

解决方案

  • 推荐方案:升级内核小版本至20250531(适用于MySQL 8.0)

    RDS MySQL 8.0内核小版本20250531已修复该问题,OPTIMIZE TABLE执行后将自动刷新统计信息,DATA_FREE可正确反映实际空间使用情况。

  • 临时规避方案:强制刷新统计信息(适用于短期无法升级的场景) ​若暂无法升级数据库版本,可对已执行OPTIMIZE TABLE的表执行命令ALTER TABLE table_name ENGINE=InnoDB;强制重建表并更新统计信息,执行后information_schema.tables中的DATA_FREE会正确显示已释放后的空间。

执行DELETERDS MySQL空间未释放如何处理?

RDS MySQL中,使用DELETE语句删除数据时,该命令仅会将记录的位置或数据页标记为可复用,而磁盘文件大小并不会改变,即表空间不会直接回收。这种行为会导致表空间无法直接回收,形成实例存储空间碎片,占用实例存储空间。

如下提供原生DDLDMS无锁表结构变更两种方案,需注意,执行两种方案前均需确保实例剩余空间充足,避免实例空间打满引起实例锁定

  • 通过命令整理空间碎片:执行OPTIMIZE TABLEALTER TABLE <table_name> ENGINE=InnoDB;DDL操作重新组织表数据和索引结构,从而释放碎片空间。

    重要

    使用原生DDL命令,需注意在业务低峰期执行,避免元数据锁阻塞的情况。更多详情,请参见注意事项

  • 通过DMS无锁表结构变更:若想避免元数据锁情况,可以考虑使用DMS无锁结构变更回收表碎片空间。

TRUNCATEDROPRDS MySQL空间未释放如何处理?

RDS MySQL中,执行TRUNCATEDROP操作后,若发现磁盘空间未释放,可按照以下步骤处理:

  1. 确认空间释放逻辑

    执行TRUNCATEDROP后,通过监控实例的磁盘空间使用率确认空间是否已释放。通常情况下,被删除表的大小占总实例空间的比例会反映为磁盘使用率的下降。

  2. 避免依赖过时信息

    若通过information_schema.tablesRDS控制台(自治服务 > 一键诊断 > 空间分析) 查看表大小,可能会因数据更新延迟导致表空间显示未变化。因此,建议优先以磁盘使用率作为判断依据。

  3. 异步删除的影响

    若实例开启了异步删除功能(如阿里云的Purge Large File Asynchronously),表文件占用的空间不会立即释放,而是由后台进程逐步清理。此时需等待异步进程完成,磁盘空间才会最终释放。