MySQL中开启innodb_file_per_table参数后，每个InnoDB表的数据和索引会单独存储在对应的.ibd文件中，这种存储方式方便单表管理和迁移，但表经过大量增删改操作后，会产生大量碎片化的空闲空间，既占用磁盘存储，也会影响查询时的IO效率。因此定期检测并整理表碎片化是数据库运维的重要工作。
什么是表碎片化
当对InnoDB表执行DELETE操作时，InnoDB不会立即把占用的空间归还给操作系统，而是标记为可复用；执行UPDATE操作时如果修改了变长字段的长度，也可能导致原有空间无法被新数据使用，这些无法被有效利用的空间就是碎片化空间。碎片化严重时，表的物理文件大小会远大于实际存储的数据量。
检测表碎片化的方法
开启innodb_file_per_table后，可以通过information_schema库的TABLES表查询表的碎片化情况，核心字段说明如下：
- DATA_LENGTH：表数据占用的字节数
- INDEX_LENGTH：表索引占用的字节数
- DATA_FREE：表中未使用的碎片化空间字节数
我们可以通过计算碎片率来判断是否需要整理，碎片率的计算公式为：碎片率 = DATA_FREE / (DATA_LENGTH + INDEX_LENGTH) * 100%，通常碎片率超过30%就建议进行整理。
查询指定库下所有表的碎片情况的示例SQL如下：
-- 查询test库下所有InnoDB表的碎片信息
SELECT
TABLE_SCHEMA AS 库名,
TABLE_NAME AS 表名,
ROUND(DATA_LENGTH/1024/1024, 2) AS 数据大小_MB,
ROUND(INDEX_LENGTH/1024/1024, 2) AS 索引大小_MB,
ROUND(DATA_FREE/1024/1024, 2) AS 碎片大小_MB,
ROUND(DATA_FREE/(DATA_LENGTH+INDEX_LENGTH)*100, 2) AS 碎片率_百分比
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'test'
AND ENGINE = 'InnoDB'
AND DATA_FREE > 0;
整理表碎片化的方法
方法一：使用ALTER TABLE重建表
这是最常用的整理方式，原理是创建一个新表复制原表数据，再删除原表重命名新表，整个过程会重新组织数据和索引的存储，释放碎片化空间。操作语法如下：
-- 整理test库的user表，ENGINE=InnoDB指定存储引擎，避免修改表引擎 ALTER TABLE test.user ENGINE = InnoDB;
注意事项：
- 该操作会锁表，建议在业务低峰期执行
- 执行前需要确保磁盘有足够空间存放新表文件
- 操作完成后可以再次查询DATA_FREE字段确认碎片是否释放
方法二：使用OPTIMIZE TABLE命令
OPTIMIZE TABLE命令对于InnoDB表（开启innodb_file_per_table时）的效果和ALTER TABLE重建表类似，语法更简洁：
-- 整理test库的user表碎片 OPTIMIZE TABLE test.user;
注意事项：
- 该命令执行时也会锁表，且会触发表的重建操作
- 执行后可能会返回"Table does not support optimize, doing recreate + analyze instead"的提示，属于正常情况，不影响整理效果
- 执行完成后会自动更新表的索引统计信息
整理后的验证
整理完成后，可以再次执行之前的碎片查询SQL，确认DATA_FREE字段已经变为0或者远小于整理前的值，同时可以对比.ibd文件的大小，确认空间已经被释放。如果整理后碎片率仍然较高，需要检查是否存在长事务未提交，导致碎片化空间无法被回收。
日常维护建议
不需要频繁整理表碎片，建议每月或者每季度在业务低峰期检测一次，仅对碎片率超过30%的表进行整理。对于写入和更新非常频繁的表，可以适当提高检测频率。另外尽量避免对大表执行全量的DELETE操作，优先使用分批删除的方式，减少碎片的产生。
MySQLinnodb_file_per_table表碎片化碎片整理修改时间：2026-07-16 19:30:50