如何计算SQL MySQL中INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES的碎片率

在MySQL的InnoDB存储引擎中，表空间碎片会随着数据的频繁增删改操作逐渐产生，过高的碎片率会导致数据存储不连续，增加磁盘IO开销，降低查询效率。通过INFORMATION_SCHEMA库中的INNODB_SYS_TABLESPACES表，我们可以获取到表空间的相关存储信息，进而计算出碎片率。

INNODB_SYS_TABLESPACES表核心字段说明

要计算碎片率，首先需要了解INNODB_SYS_TABLESPACES表中与存储相关的关键字段，这些字段的含义如下：

• SPACE：表空间的唯一ID标识
• NAME：表空间的名称，通常对应数据库名和表名
• FILE_SIZE：表空间文件在磁盘上的实际大小，单位为字节
• ALLOCATED_SIZE：表空间中已经被分配用于存储数据的空间大小，单位为字节

碎片率的计算逻辑

表空间碎片产生的本质是实际分配用于存储数据的空间和磁盘上文件实际占用的空间存在差异，未被有效数据占用的空间就是碎片空间。因此碎片率的计算公式为：

碎片率 = (FILE_SIZE - ALLOCATED_SIZE) / FILE_SIZE * 100%

当碎片率较高时，说明表空间中存在大量未被有效利用的存储碎片，需要考虑通过OPTIMIZE TABLE等命令进行碎片整理。

具体SQL查询示例

我们可以通过以下SQL语句直接查询所有InnoDB表空间的碎片率，并且筛选出碎片率超过30%的表空间，方便优先处理：

-- 查询InnoDB表空间碎片率，筛选碎片率超过30%的记录
SELECT 
    SPACE,
    NAME,
    FILE_SIZE,
    ALLOCATED_SIZE,
    -- 计算碎片率，保留2位小数
    ROUND((FILE_SIZE - ALLOCATED_SIZE) / FILE_SIZE * 100, 2) AS fragment_rate
FROM 
    INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES
WHERE 
    -- 排除系统表空间，只查询用户表空间
    NAME NOT LIKE 'mysql/%'
    -- 排除文件大小为0的异常记录
    AND FILE_SIZE > 0
    -- 筛选碎片率超过30%的表空间
    AND (FILE_SIZE - ALLOCATED_SIZE) / FILE_SIZE * 100 > 30
ORDER BY 
    fragment_rate DESC;

注意事项

• INNODB_SYS_TABLESPACES表中的数据是实时读取的，查询时可能会对数据库产生轻微的IO开销，建议不要在业务高峰期执行全量查询
• FILE_SIZE和ALLOCATED_SIZE的单位是字节，对于大表空间可以自行转换为MB或GB单位方便查看
• 碎片率计算仅作为参考，不是所有高碎片率的表空间都需要立即整理，需要结合表的读写频率和业务场景综合判断

碎片整理建议

对于确认需要整理的表空间，可以使用以下命令进行碎片整理：

-- 整理指定表的碎片，会重建表并释放碎片空间
OPTIMIZE TABLE 数据库名.表名;

执行OPTIMIZE TABLE命令时会对表加锁，建议在业务低峰期操作，避免影响正常业务访问。

MySQLINNODB_SYS_TABLESPACES碎片率计算SQL修改时间：2026-06-18 00:57:17