MySQL 数据库大小计算？

Database size calculation?

提问by coderama

What is the most accurate way to estimate how big a database would be with the following characteristics:

估计具有以下特征的数据库有多大的最准确方法是什么：

• MySQL
• 1 Table with three columns:
  • id --> big int)
  • field1 --> varchar 32
  • field2 --> char 32
• there is an index on field2

• MySQL
• 1 包含三列的表：
  • id --> 大整数)
  • 字段 1 --> varchar 32
  • 字段 2 --> 字符 32
• field2 上有一个索引

You can assume varchar 32 is fully populated (all 32 characters). How big would it be if each field is populated and there are:

您可以假设 varchar 32 已完全填充（所有 32 个字符）。如果每个字段都被填充并且有：

1. 1 Million rows
2. 5 Million rows
3. 1 Billion rows
4. 5 Billion rows

1. 100 万行
2. 500 万行
3. 10 亿行
4. 50 亿行

My rough estimate works out to: 1 byte for id, 32 bits each for the other two fields. Making it roughly:

我的粗略估计为：id 为 1 个字节，其他两个字段各为 32 位。大致制作：

  1 + 32 + 32 = 65 * 1 000 000 = 65 million bytes for 1 million rows
= 62 Megabyte

Therefore:

所以：

1. 62 Mb
2. 310 Mb
3. 310 000 Mb = +- 302Gb
4. 1 550 000 Mb = 1513 Gb

1. 62 兆字节
2. 310 兆字节
3. 310 000 Mb = +- 302Gb
4. 1 550 000 Mb = 1513 Gb

Is this an accurate estimation?

这是一个准确的估计吗？

回答by Matteo Tassinari

If you want to know the current size of a database you can try this:

如果你想知道一个数据库的当前大小，你可以试试这个：

SELECT table_schema "Database Name"
     , SUM(data_length + index_length) / (1024 * 1024) "Database Size in MB"
FROM information_schema.TABLES
GROUP BY table_schema

回答by duskwuff -inactive-

My rough estimate works out to: 1 byte for id, 32 bits each for the other two fields.

我的粗略估计为：id 为 1 个字节，其他两个字段各为 32 位。

You're wayoff. Please refer to the MySQL Data Type Storage Requirementsdocumentation. In particular:

你的方式了。请参阅 MySQL数据类型存储要求文档。特别是：

• A BIGINTis 8 bytes, not 1.

• The storage required for a CHARor VARCHARcolumn will depend on the character set in use by your database (!), but will be at least 32 bytes (not bits!) for CHAR(32)and 33 for VARCHAR(32).

• You have not accounted at all for the size of the index. The size of this will depend on the database engine, but it's definitely not zero. See the documentation on the InnoDB row structurefor more information.

• ABIGINT是 8 个字节，而不是 1。

• aCHAR或VARCHAR列所需的存储空间将取决于您的数据库使用的字符集 (!)，但至少为 32 字节（不是位！） forCHAR(32)和 33 for VARCHAR(32)。

• 您根本没有考虑索引的大小。它的大小取决于数据库引擎，但绝对不是零。有关更多信息，请参阅有关InnoDB 行结构的文档。

回答by dwtm.ts

On the MySQL website you'll find quite comprehensive information about storage requirements: http://dev.mysql.com/doc/refman/5.6/en/storage-requirements.html

在 MySQL 网站上，您会找到有关存储要求的非常全面的信息：http: //dev.mysql.com/doc/refman/5.6/en/storage-requirements.html

It also depends if you use utf8 or not.

这也取决于您是否使用 utf8。