MySQL InnoDB中无法正确计算(DATA_LENGTH + INDEX_LENGTH)的问题

我使用MySQL InnoDB存储数据，需要将特定表的大小限制在指定值内。插入数据后，我会通过检查DATA_LENGTH + INDEX_LENGTH判断是否超出限制，若超出则删除旧数据直至符合要求。

但获取的DATA_LENGTH + INDEX_LENGTH值不符合预期，具体情况如下：

• 初始时，tableTime表的DATA_LENGTH + INDEX_LENGTH约15GB，DATA_FREE为0GB，共200万行数据。
• 执行删除语句：

DELETE FROM tableTime
LIMIT 1000000;

• 删除100万行后，等待mysqld.exe完成磁盘I/O（通过任务管理器手动确认）并重启系统。
• 随后执行：

ANALYZE TABLE tableTime;
SELECT ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS TABLE_SIZE_MB FROM information_schema.TABLES
where TABLE_NAME = 'tableTime';

查询得到DATA_LENGTH + INDEX_LENGTH约13GB，DATA_FREE约2GB。执行OPTIMIZE TABLE tableTime;后再次查询，得到预期的8-9GB的DATA_LENGTH + INDEX_LENGTH。

由于表体积较大，无法每次通过OPTIMIZE获取准确表大小，请问为何ANALYZE的效果如此不理想？

为什么ANALYZE TABLE无法更新表的实际占用大小 #

ANALYZE TABLE的核心作用是更新表的统计信息（比如索引基数、数据分布情况），供查询优化器生成执行计划使用，它完全不涉及表物理存储结构的修改，也不会回收删除行释放的空间：

• InnoDB删除行时，只会标记这些行所在的数据页为“可复用”，不会立即将物理空间归还操作系统，这部分待复用的空间就是DATA_FREE统计的内容。
• DATA_LENGTH和INDEX_LENGTH统计的是表当前占用的物理磁盘空间（包含已标记为可复用但未释放的空间），ANALYZE TABLE不会修改这两个字段的值，因为它的职责不包括空间回收。

无需OPTIMIZE TABLE的替代方案 #

如果要获取更准确的实际数据占用大小，或者不需要立即回收空间但要判断是否需要继续删除数据，可以采用以下方法：

1. 结合DATA_FREE计算实际使用空间
   用(DATA_LENGTH + INDEX_LENGTH) - DATA_FREE来估算当前实际存储数据的空间，这个值会更接近删除后的真实数据大小。
2. 通过行统计估算实际大小
   对于时间序列类表（从tableTime表名推测），可以利用平均行长度和当前行数估算实际占用空间：

   SELECT 
     ROUND((AVG_ROW_LENGTH * TABLE_ROWS) / 1024 / 1024) AS ACTUAL_DATA_SIZE_MB
   FROM information_schema.TABLES 
   WHERE TABLE_NAME = 'tableTime';
   

   注：AVG_ROW_LENGTH是统计值，存在一定误差，但对于行数较多的表，误差在可接受范围内。
3. 利用InnoDB的自动空间复用
   若只是需要后续插入数据时能复用空闲空间，不需要将空间还给操作系统，完全无需执行OPTIMIZE TABLE——InnoDB会自动将新数据写入DATA_FREE对应的空闲页，此时DATA_LENGTH + INDEX_LENGTH数值虽大，但不会额外占用磁盘空间。

内容的提问来源于stack exchange，提问作者Rishad C