一、为什么需要优化表空间？
在日常使用 MySQL 的过程中，我们经常会执行如下操作：
- 删除大量历史数据
- 更新字段内容（尤其是长文本或大字段）
- 建立和删除索引
这些操作虽然改变了表的数据，但底层物理文件（表空间）并不会自动缩小。
也就是说，即使你删除了一半的数据，.ibd 文件的大小可能仍然维持不变。
这就导致了：
数据库“看起来”空了很多，磁盘空间却依旧被占满。
解决这个问题的关键命令就是：
OPTIMIZE TABLE
二、OPTIMIZE TABLE 是什么？
OPTIMIZE TABLE 是 MySQL 提供的表维护命令，用于：
- 回收未使用的空间（即碎片整理）
- 重新统计索引信息
- 提高后续查询性能
它的本质是对表进行一次“重建”（rebuild），并在过程中整理数据和索引文件。
三、语法与基本用法
OPTIMIZE TABLE table_name;
执行该语句后，MySQL 会对指定表执行优化操作。
例如：
OPTIMIZE TABLE users;
对于 InnoDB 引擎（默认存储引擎），其内部等价于：
ALTER TABLE users ENGINE=InnoDB;
这会在后台新建一个临时表，将数据重新写入，然后替换原表，达到“压缩空间”的效果。
四、执行 OPTIMIZE TABLE 之后发生了什么？
在 InnoDB 表上执行优化操作时，MySQL 会：
- 创建一个临时表
- 将原表数据按主键顺序写入临时表
- 重建索引
- 替换原有表文件（
.ibd）
- 释放碎片和未使用的空间
⚠️ 注意：
在优化过程中，表会被锁定（阻塞写操作），因此建议在业务低峰期执行。
五、查看表空间占用情况
在优化前后，我们可以通过以下命令查看表的物理大小变化：
SHOW TABLE STATUS LIKE 'users'\G
重点关注以下字段：
Data_length：数据占用的字节数Index_length：索引占用的字节数Data_free：已分配但未使用的空间
当 Data_free 较大时，说明表内有很多“空洞”，可以通过 OPTIMIZE TABLE 释放掉。
六、批量优化所有表
如果你的数据库有很多表，可以用以下 SQL 批量执行优化：
SELECT CONCAT('OPTIMIZE TABLE ', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'your_database_name';
一、为什么需要优化表空间？
在日常使用 MySQL 的过程中，我们经常会执行如下操作：
- 删除大量历史数据
- 更新字段内容（尤其是长文本或大字段）
- 建立和删除索引
这些操作虽然改变了表的数据，但底层物理文件（表空间）并不会自动缩小。
也就是说，即使你删除了一半的数据，.ibd 文件的大小可能仍然维持不变。
这就导致了：
数据库“看起来”空了很多，磁盘空间却依旧被占满。
解决这个问题的关键命令就是：
OPTIMIZE TABLE
二、OPTIMIZE TABLE 是什么？
OPTIMIZE TABLE 是 MySQL 提供的表维护命令，用于：
- 回收未使用的空间（即碎片整理）
- 重新统计索引信息
- 提高后续查询性能
它的本质是对表进行一次“重建”（rebuild），并在过程中整理数据和索引文件。
三、语法与基本用法
OPTIMIZE TABLE table_name;
执行该语句后，MySQL 会对指定表执行优化操作。
例如：
OPTIMIZE TABLE users;
对于 InnoDB 引擎（默认存储引擎），其内部等价于：
ALTER TABLE users ENGINE=InnoDB;
这会在后台新建一个临时表，将数据重新写入，然后替换原表，达到“压缩空间”的效果。
四、执行 OPTIMIZE TABLE 之后发生了什么？
在 InnoDB 表上执行优化操作时，MySQL 会：
- 创建一个临时表
- 将原表数据按主键顺序写入临时表
- 重建索引
- 替换原有表文件（
.ibd）
- 释放碎片和未使用的空间
⚠️ 注意：
在优化过程中，表会被锁定（阻塞写操作），因此建议在业务低峰期执行。
五、查看表空间占用情况
在优化前后，我们可以通过以下命令查看表的物理大小变化：
SHOW TABLE STATUS LIKE 'users'\G
重点关注以下字段：
Data_length：数据占用的字节数Index_length：索引占用的字节数Data_free：已分配但未使用的空间
当 Data_free 较大时，说明表内有很多“空洞”，可以通过 OPTIMIZE TABLE 释放掉。
六、批量优化所有表
如果你的数据库有很多表，可以用以下 SQL 批量执行优化：
SELECT CONCAT('OPTIMIZE TABLE ', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'your_database_name';
然后复制执行生成的命令即可。
或者写个简单脚本：
mysqlcheck -o your_database_name -u root -p
-o 参数表示 optimize。
七、优化时的注意事项
- 锁表风险
- 对 InnoDB 来说，
OPTIMIZE TABLE 会锁表一段时间。
- 生产环境应在业务低谷执行，或者使用 Percona Toolkit 的
pt-online-schema-change 工具实现在线重建。
- 空间释放位置
- 如果启用了
innodb_file_per_table=1（默认开启），释放空间会直接反映在 .ibd 文件上。
- 若关闭，则释放的空间仍在共享表空间中，只能被重用，不能返还给操作系统。
- 执行频率建议
- 大量删除或更新后执行一次；
- 定期（如每月或每季度）执行一次维护。
八、实际案例演示
执行前查看表大小：
SHOW TABLE STATUS LIKE 'order_logs'\G
输出：
Data_length: 2,147,483,648
Data_free: 1,073,741,824
说明有 1GB 空间未被利用。
执行优化：
OPTIMIZE TABLE order_logs;
执行后再查看：
Data_length: 1,073,741,824
Data_free: 0
磁盘瞬间“瘦身”一半，查询性能也有所提升。
九、总结
| 项目 |
说明 |
| 命令 |
OPTIMIZE TABLE table_name; |
| 作用 |
回收表空间、重建索引、提高查询性能 |
| 风险 |
锁表、耗时、可能影响线上业务 |
| 建议 |
在业务低峰执行，或使用在线优化工具 |
💡 小结一句话：
当 MySQL 表越来越“胖”、查询变慢、磁盘告急时，别急着扩容，先执行一遍 OPTIMIZE TABLE，可能立竿见影。
📘延伸阅读
