mysql - 为什么 MySQL 的 data_free 大于数据和索引的总和？

Question

我无法从 MySql 5.5 数据库中解释以下统计数据。

可能相关的辅助信息:一些表包含 TEXT 和 MEDIUMTEXT 列，大约 50%他们有空值。我对占用大部分空间的表进行了优化。

1. 问题:我是如何从 Mysql 文档中理解 data_free 的:分配的空间由 DDL 但当前未使用 - 所以行中基本上是空值。这种解释是否正确？如果不正确，它是如何准确地定义的？

2. 问题:我不明白 data_free 怎么会比索引和数据长度加起来更大

select (sum(data_length) + sum(INDEX_LENGTH))/ 1024 / 1024 as total, 
sum(data_free)/ 1024 / 1024  as free ,
sum(data_length)/ 1024 / 1024 as data, 
sum(INDEX_LENGTH/ 1024 / 1024) as index_data 
from information_schema.tables t
where t.TABLE_SCHEMA = 'foo'

结果:

total | free | data | index_data

19.5469   20.0000   18.1875 1.3594

所以我有 20MB data_free 和 19.5MB data+index。怎么可能？

谢谢

Answer 1

我假设您正在使用 InnoDB，因为它是 MySQL 5.5 中的默认存储引擎。

InnoDB 表空间会随着您插入数据而增长，但文件不会在您删除数据时缩小。因此，例如，如果您插入 100 万行，然后删除它们，文件将有大量物理分配的空间，但不再使用。 InnoDB 将在再次增长表空间文件之前尽可能重新使用该空间。

此外，即使你不删除，也可能会有一些“浪费”的空间，因为当表空间文件的大小增加时，它们会扩展一大块页面，由配置选项innodb_autoextend_increment<决定 以兆字节为单位。在这些页面被数据填满之前，它们是可用空间。

InnoDB 报告的 Data_free 是中央表空间文件中空页中“浪费”的空间量。它与 NULL 值无关，它与其中没有行的数据页有关。

此外，在 MySQL 5.5 中，所有表默认共享一个名为 ibdata 的中央表空间。此表空间中所有表的 data_Free 将报告相同的数字，即整个表空间中空闲页的空间量，而不仅仅是一个表。

您还可以为每个表分配一个单独的表空间 (innodb_file_per_table=1)，对于单独表空间中的表，您将看到每个表的 data_free 值不同。

Data_free 仅报告完全空的 extents 剩余的空间(extents 是等于 1MB 的页面 block )。您会注意到 data_free 始终是 1MB 的倍数。较小的空闲页面 block 不计入 data_free，部分填充的页面也不计入。所以“浪费”的空间可能要大得多，但我们无从知晓。