我在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;
它总共有大约70000000个条目,即使这些列可以为null,但没有一个条目在任何列中有NULL
(表结构不是问题所在)。
如果我查看information_schema
,我可以看到索引长度是10272899072,数据长度是3201302528。
这使得总容量达到12850MB,即大约12.54GB
这个数字是如何计算的?
SHOW TABLE STATUS ... LIKE ParaTable
的输出显示:
Rows: 68129609
Avg_row_length: 47
Data_length: 3201302528 (=3053MB)
Index_length: 10272899072 (=9797MB)
我在MySQL手册中阅读了有关数据存储大小的内容,并进行了以下粗略计算:
(int+int+tinyint+tinyint+int+时间戳)
4+4+1+1+4+4=每行18个字节(+6位,因为每列都可以为空,我假设我可以将这6位计算为另一个字节,并且是安全的,请参阅手册)=每行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字节?我在这里错过了什么?
您错过了计算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数据结构的帖子可能会很有用,以了解更多信息:
- InnoDB中的B+树索引结构
- InnoDB索引页的物理结构
- InnoDB中记录的物理结构