MySQL数据库大小估计

问题描述

我有一个应用程序数据库，其中包含一个用于用户的表(每个用户1kbyte的数据(基于计数字段* typelength)，以及属于用户的大约100个相同大小的事物(每个事物0.5 kbyte)，它位于一个用户"表和一个事物"表.

I have an application database with a table for users (1kbyte of data per user based on counting fields * typelength), and about 100 things of the same size belonging to a user (0.5 kbyte per thing), and it is in a "user" table and a "thing" table.

这似乎会导致每个用户大约51 KB的数据.但是，我听说对于MySQL，我应该将其加倍以覆盖索引表，这会使我达到102 KB/用户.是这样吗?对于MySQL，还需要考虑其他数据扩展因素吗?还是102 KB是一个很好的估计?

That would seem to lead to about 51kbytes of data per user. However, I have heard that for MySQL, I should double it to cover index tables, which would get me to 102kbytes/user Is that true? Are there any other data expansion factors to consider for MySQL, or is 102 kbytes a good estimate?

除了索引因子(我认为是2)和存储效率(我也认为是2)之外，MySQL中是否还有其他乘数来存储数据?

Besides the indexing factor (which I think is 2) and the storage efficiency (which I also think is 2), are there any other multipliers for data storage in MySQL?

推荐答案

简短答案
大小通常比MyISAM增大2-3倍，很少4倍.

Short answer
Size increase 2-3x over MyISAM is common, 4x is rare.

关于InnODB引擎的所有信息:
http://dev.mysql.com/doc/refman/5.1/en/innodb-storage-engine.html

Everything about the InnODB engine:
http://dev.mysql.com/doc/refman/5.1/en/innodb-storage-engine.html

InnoDB，MyISAM和磁盘空间:
http://mysqlha.blogspot.com/2009/01/innodb-myisam-and-disk-space_16.html

InnoDB, MyISAM and disk space:
http://mysqlha.blogspot.com/2009/01/innodb-myisam-and-disk-space_16.html

MySQL引擎空间使用情况比较:
第1部分: http://marksverbiage.blogspot.com/2008/02/mysql-engines-and-space-usage.html
第2部分: http://marksverbiage.blogspot.com/2008/04/mysql-engines-space-usage-comparison.html

MySQL engines space usage comparison:
Part1: http://marksverbiage.blogspot.com/2008/02/mysql-engines-and-space-usage.html
Part2: http://marksverbiage.blogspot.com/2008/04/mysql-engines-space-usage-comparison.html

这是物理行结构:
http://dev.mysql.com/doc/refman/5.0/en/innodb-physical-record.html

Here's the physical row structure:
http://dev.mysql.com/doc/refman/5.0/en/innodb-physical-record.html

有很多变量和问题:

• 索引，请记住，InnoDB在每个辅助索引中都包含PK.
• 您在打包钥匙(慢)吗?
• 表是否多余?
• 不要忘记日志(二进制日志，慢速查询日志，错误日志....)
• 是否将行声明为可为空，如果是，则为每行的每个可为空的列添加一个额外的字节.
• 您使用的是什么字符集?

这篇关于MySQL数据库大小估计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！