Background
MySQL 文档声明如下:
与 CHAR 相反,VARCHAR 值存储为 1 字节或 2 字节长度前缀加上数据。长度前缀指示值中的字节数。如果值需要不超过 255 个字节,则列使用一个长度字节;如果值可能需要超过 255 个字节,则使用两个长度字节。
为了自己对此进行测试,我创建了两个表:
CREATE TABLE `varchar_length_test_255` (
`characters` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `varchar_length_test_256` (
`characters` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
然后,我在每个表中插入了 10,000 行,每行的值具有characters
列的最大长度。
由于我使用的字符集的最大字节长度为每个字符一个字节 (latin1
),因此我希望看到两个表之间的存储大小相差 20,000 字节,源自以下内容:
varchar_length_test_256
表中的每一行都包含一个比varchar_length_test_255
表中的行更多的字符。使用latin1
个字符集,加起来为10,000
个字节,因为每个表中有 10,000 行。- 根据MySQL文档,
VARCHAR
超过255
字节的值需要一个额外的"长度"字节。由于varchar_length_test_256
表中的每一行在characters
列中包含一个长度为256
的值,这相当于自使用latin1
字符集以来每个值的256
个字节,因此加起来又使用了10,000
个字节。
问题
发出查询以检索每个表的大小时,这些表的大小似乎相同!我使用以下查询(基于此SO帖子)来确定每个表的大小:
SELECT
table_name AS `Table`,
(data_length + index_length) `Size in Bytes`
FROM
information_schema.TABLES
WHERE
table_schema = "test";
这产生了这个输出:
+-------------------------+---------------+
| Table | Size in Bytes |
+-------------------------+---------------+
| varchar_length_test_255 | 4734976 |
| varchar_length_test_256 | 4734976 |
+-------------------------+---------------+
2 rows in set (0.00 sec)
我在这里错过了什么?
- 我是否正确理解了 MySQL 文档?
- 我的测试是否有问题导致预期结果无法实现?
- 我用来计算表大小的查询是否正确?
- 如何正确观察 MySQL 文档中传达的信息?
检查他也data_free
列。
InnoDB将数据存储在所谓的"页面"上,这些页面的大小为16KB(默认情况下)。当页面几乎已满,并且您插入了一条新记录,但它无法放在页面上时,MySQL将打开一个新页面,将剩余空间留空。
我的假设是,MySQL将页面数乘以页面大小报告为数据/索引大小。
这是操作系统上用于存储表数据的有效大小,而不是存储在这些页面上的实际大小。
更新:https://mariadb.com/kb/en/library/information-schema-tables-table/
在此页面上(即使它是MariaDB,但存储引擎是相同的),data_lenght
的描述如下:
对于InnoDB/XtraDB,索引大小(以页面为单位)乘以页面 大小。对于 Aria 和 MyISAM,数据文件的长度(以字节为单位)。为 内存,近似分配的内存。
编辑(一些计算)
16 KB = 16384 B
Storage (B) # of record # of pages
on a page
---------------------------------------------------
varchar(255) 256 64 156.25
varchar(256) 258 63.5 158.73
如您所见,原始数据(带有长度标记)可以存储在几乎相同数量的页面上。
由于页面不需要填充到 100%(但是innodb_fill_factor
默认为 100),并且行结构中有一些开销,因此这种微小的差异不一定可见。
数据库文件不像csv文件,但它们必须处理多个事情,例如NULL 值、行大小变化时等,这会占用额外的空间。
有关InnoDB行结构的更多信息:https://dev.mysql.com/doc/refman/5.5/en/innodb-physical-record.html