如何从列数据类和行数中计算MySQL表使用的磁盘空间

我有一个MySQL表，在Google Cloud SQL上托管了超过30亿行。

我想了解如何从列数据类型，行数和索引中解释磁盘上的总尺寸。

我希望那会像

size_of_table_in_bytes = num_rows * [ Sum over i {bytes_for_datatype_of_column(i))}
                                    + Sum over j  {Bytes_for_index(j)} ]

，但我最终要比数据库大小显示的磁盘大小不正确。

在

上使用每个数据类型的字节

https://dev.mysql.com/doc/refman/5.7/en/storage-requirentess.html

和InnoDB标题中的其他字节和

的索引

https://dev.mysql.com/doc/refman/5.7/en/innodb-physical-record.html#innodb-compact-row-format-format-characteristical

这是我对标头占用的字节，每列和每一索引

的理解

TABLE `depth` (
Bytes| Column/Header/Index
    2| variable length header Ceil(num columns/8) = Ceil (10/8)
    5| Fixed Length Header
    3|`date` date DEFAULT NULL,
    7|`receive_time` datetime(3) DEFAULT NULL,
    8|`instrument_token` bigint(20) unsigned DEFAULT NULL,
    1|`level `tinyint(3) unsigned DEFAULT NULL,
    2|`bid_count` smallint(5) unsigned DEFAULT NULL,
    8|`bid_size` bigint(20) unsigned DEFAULT NULL,
    4|`bid_price` float DEFAULT NULL,
    4|`ask_price` float DEFAULT NULL,
    8|`ask_size` bigint(20) unsigned DEFAULT NULL,
    2|`ask_count` smallint(5) unsigned DEFAULT NULL,
    6|KEY `date_time_sym (`date`,`receive_time`,`instrument_token`),
    6|KEY `date_sym_time (`date`,`instrument_token`,`receive_time`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8`

总计为72个字节。

但按照显示表状态，avg_row_length =79。

问题1：我在哪里获取每个行的字节数？

我可以合理地确定我的数据中没有空。

假设我在计数字节上犯了一些错误，并且每行使用79个字节使用SELECT COUNT(*)作为3,017,513,240：

计数行

size_of_table_in_bytes = 79*3,017,513,240 = 238,383,545,960

获得大小的另一种方法是使用mysql查询

SHOW TABLE STATUS from mktdata where Name = "depth";

在这里，我获得了一个带有一排的表输出，其中几个重要字段的值为：

Name: depth
Engine:InnoDB
Version:10
Row_format:Dynamic
Rows: 1,72,08,21,447 
Avg_row_length: 78
Index_length: 1,83,90,03,07,456
Data_length:  1,35,24,53,32,480

首先，我感到震惊，Rows是17亿而不是30.1亿，但我在文档中找到了这一点

行
行的数量。一些存储引擎（例如Myisam）存储确切的数数。对于其他存储引擎（例如InnoDB），此值是一个近似值，并且可能从实际值不高达40％至50％。在这种情况下，使用Select Count（*）获得准确的计数。

so，30.1亿的行似乎适合行，因此我预计桌子尺寸将为238 GB。

但是，如果我加起来，Data_length和Index_length，我会得到319,145,639,936

问题2：为什么我要通过一种方法获得319 GB，而238 GB作为另一种方法。哪一个是正确的？

此外，Google Cloud SQL控制台为SQL数据库显示的整体大小为742 GB。我唯一的表格trade完全具有depth和5列的行数的1/5。总结Data_length和Index_length的大小为57 GB。如果我添加两个桌子尺寸，我会得到376 GB。

问题3：742 GB似乎大约是376 GB的两次（实际上是752）。这可能是因为备份吗？我知道Google Cloud SQL每天进行一次自动备份？

由于上述问题3的合理性，我怀疑我的简单size = num_rows*num_bytes_per_row方法是错误的！这确实使我感到困扰，并感谢您解决这一问题的任何帮助。

开销比您提到的更多。20个字节/行可能 be close 。
不要相信SHOW TABLE STATUS给出"行"，请使用 SELECT COUNT(*) ...注意它的消失方式几乎为2倍。
计算另一种方式：135245332480/3017513240 = 45字节。
从45个字节开始，我推断出许多单元格是null的？
每行的每一列都有1-或2字节的开销。
ROW_FORMAT很重要。
TEXT和BLOB（etc）具有与简单数据类型的根本不同的规则。
索引所需要的远远超过您提到的6个字节（请参阅其他帖子）。
btree结构有一些开销。按顺序加载时，每个块中的15/16被填充（在文档中的某个地方提到）。流失后，范围很容易被填充50-100％。btree已吸引到69％（因此在另一个帖子中为1.45）。

保留等量的备份空间...

我不知道那是他们在做什么。
如果他们使用mysqldump（或类似），它不是一个安全的公式 - 数据库的 text 转储可能会大得多或更小。
如果他们使用LVM，那么他们就有一个完整的二进制垃圾场的空间。但这是由于牛而没有意义的。
（所以，我放弃了Q3。）

云服务可以进行某种压缩吗？

相关内容

最新更新

热门标签：