MySQL INFORMATION_SCHEMA.TABLES data_free 判断碎片

data_free 是 INFORMATION_SCHEMA.tableS 中表示 InnoDB 表已分配但未使用的字节数的字段，反映因页分裂、删除等导致的磁盘空间浪费，常用于评估是否需 OPTIMIZE TABLE；其值通常为 0 或 4MB 整数倍，且仅在 innodb_file_per_table=ON 且 ROW_FORMAT=COMPACT/DYNAMIC 时有效。

data_free 是什么，为什么能反映碎片

data_free 是 INFORMATION_SCHEMA.TABLES 中的一个字段，表示表在磁盘上已分配但未被数据实际使用的字节数（单位：字节）。它主要来自 InnoDB 的“空闲页”（free pages）或“未填充的页间隙”，不是精确的碎片度量，但对判断是否需要 OPTIMIZE TABLE 有参考价值。

注意：data_free 在 MyISAM 表中含义不同（是删除行后留下的空洞），而 InnoDB 下它更接近“因页分裂、删除、更新导致的未利用空间”，但不等于可回收的碎片总量——比如自增主键连续插入后删除中间行，data_free 可能很小，但逻辑碎片仍存在。

• InnoDB 表的 data_free 值通常为 0 或 4MB 的整数倍（与 extent 分配机制有关）
• 只有 ROW_FORMAT=COMPACT 或 DYNAMIC 且启用了 innodb_file_per_table=ON 时，该值才较有意义
• data_free 不会实时更新，需执行 ANALYZE TABLE 或等待后台刷脏页后才可能变化

怎么查 data_free 并识别高碎片表

直接查 INFORMATION_SCHEMA.TABLES 即可，但要注意过滤条件和单位换算：

SELECT    table_schema,   table_name,   ROUND(data_length / 1024 / 1024, 2) AS data_mb,   ROUND(data_free / 1024 / 1024, 2) AS free_mb,   ROUND(data_free / data_length * 100, 2) AS free_pct FROM INFORMATION_SCHEMA.TABLES  WHERE engine = 'InnoDB'    AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')   AND data_length > 0    AND data_free > 0 ORDER BY free_pct DESC LIMIT 20;

• 必须加 data_length > 0，否则除零报错；data_free > 0 排除无意义的 0 值
• 按 free_pct 排序比只看 free_mb 更合理——10GB 表占 100MB 空闲 vs 100MB 表占 100MB 空闲，后者更值得优化
• 若 free_pct > 25% 且表频繁更新/删除，可视为高碎片候选；但 free_pct 一般无需干预

data_free 为 0 就代表没碎片？

不一定。常见误判场景：

• 表刚执行过 OPTIMIZE TABLE 或 ALTER TABLE ... ENGINE=InnoDB，data_free 被重置为 0，但后续写入仍会产生新碎片
• 小表（data_Length ）因最小 extent 分配（1MB）导致 data_free 恒为 0，实际页利用率可能很低
• InnoDB 的 purge 线程尚未清理已删除记录的页，data_free 还没反映出来；此时查 SHOW ENGINE INNODB STATUS 的 history list length 更准
• 使用压缩表（ROW_FORMAT=COMPRESSED）时，data_free 计算方式不同，数值不可比

真正要优化时，别只盯着 data_free

data_free 是个粗筛指标，决定是否执行 OPTIMIZE TABLE 前，还需交叉验证：

• 查 select count(*) 和 SELECT COUNT(*) FROM t FORCE INDEX (PRIMARY) 是否差异大——若后者慢很多，说明主键页严重离散
• 用 pt-table-checksum 或 innochecksum 观察页读取效率，而非仅看空间占用
• 监控 innodb_buffer_pool_read_requests 与 innodb_buffer_pool_reads 的比率，持续偏低说明随机 IO 多，可能和碎片相关
• OPTIMIZE TABLE 在 5.6+ 会重建表并释放空间，但期间锁表（online DDL 可缓解）；生产环境优先考虑 ALTER TABLE t ENGINE=InnoDB, ALgoRITHM=INPLACE

碎片不是越小越好——过度优化反而引发大量写放大和 buffer pool 冲击。真正影响性能的是随机访问密度，而不是 data_free 的绝对值。