Mysql表大小与我的计算不符

Question

我在 MySQL 中有下表:

CREATE TABLE `ParaTable` (
    `id_1` INT(10) UNSIGNED NULL DEFAULT '0',
    `id_2` INT(10) UNSIGNED NULL DEFAULT '0',
    `id_3` TINYINT(3) UNSIGNED NULL DEFAULT '0',
    `id_4` TINYINT(3) UNSIGNED NULL DEFAULT '0',
    `id_5` INT(10) UNSIGNED NULL DEFAULT '0',
    `date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `id_1` (`id_1`),
    INDEX `id_2` (`id_2`),
    INDEX `date` (`date`),
    INDEX `id_3` (`id_3`),
    INDEX `id_4` (`id_4`),
    INDEX `id_5` (`id_5`),
    INDEX `multi_index` (`id_1`, `id_3`, `id_4`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

它总共有大约 70,000,000 个条目，即使列可以为空，但没有一个条目在任何列中具有 NULL(这里的表结构不是问题)。

如果我查看information_schema，我可以看到索引长度为10272899072，数据长度为3201302528。

总共 12,850MB，或约 12.54GB。

这个数字是如何计算的？

SHOW TABLE STATUS ... LIKE ParaTable 的输出显示:

Rows: 68129609
Avg_row_length: 47
Data_length: 3201302528       (=3053MB)
Index_length: 10272899072     (=9797MB)

我读到关于数据存储大小in the MySQL manual并进行了以下粗略计算:

(int+int+tinyint+tinyint+int+时间戳)

4+4+1+1+4+4 = 每行 18 个字节(+ 6 位，因为每列都可以为空，我假设我可以将这 6 位计算为另一个字节并且安全，请参阅 the manual ) = 每行 19 字节。

(即使 6 位中的每一位在磁盘上占用 1 个字节(我猜这不太可能)，每行也将占用 24 个字节。)

18 bytes * 70,000,000 rows = 1260000000B = ~1200MB
(19 bytes * 70,000,000 rows = 1330000000B = ~1270MB)
(24 bytes * 70,000,000 rows = 1680000000B = ~1600MB)

我不知道mysql索引占用了多少空间(我只能从SHOW TABLES中获取值，但它是如何计算的？)。这是计算所需总大小时缺少的一环。但即使索引与它没有任何关系，Data_length 本身似乎也太高了。

为什么Avg_row_length是47而不是我计算的18-24字节？我在这里缺少什么？

Answer 1

您错过了计算 InnoDB 存储这些行的所有开销。你应该有:

  4 (INT)
+ 4 (INT)
+ 1 (TINYINT)
+ 1 (TINYINT)
+ 4 (INT)
+ 4 (TIMESTAMP)
+ 1 (Null bitmap, rounded up to whole bytes)
+ 5 (Row header)
+ 6 (ROW_ID: Implicit cluster key, because you are missing a PRIMARY KEY)
+ 6 (TRX_ID: Transaction ID)
+ 7 (ROLL_PTR: Rollback/undo pointer)
= 43 bytes per row

然后，您还需要考虑页面填充率(根据设计，页面填充率不会达到 100%)，这至少会增加约 7%:

  43
* 1 / (15/16)
= 45.86 bytes per row

此外，您还会在已分配但未使用的空间中产生开销。

所以实际上，每行获得约 47 字节一点也不坏。最坏的情况是开销消耗约 50%，导致表每行占用约 86 字节。

对于每个辅助键，请注意它们的空间消耗情况如下(以 id_1 为例):

  4 (INT)
+ 1 (Null bitmap, rounded up to whole bytes)
+ 5 (Row header)
+ 6 (ROW_ID: Implicit cluster key)
= 16 bytes per row
* 1 / (15/16)
= 17.06 bytes per row

阅读以下有关 InnoDB 数据结构的文章以了解更多信息可能会很有用:

• B+Tree index structures in InnoDB
• The physical structure of InnoDB index pages
• The physical structure of records in InnoDB