一对多关系占用更多数据库空间
我们重构了用户特定的配置 创建表 user (id, name, ..., config1, config2, config3, ..)
create table user (id, name, ...);
create table user_config (id, user_id, config_val);
进行此更改并将用户从旧版本迁移后，我们的 MySQL 数据库大小增加了 2 倍表到较新的表。我们这样做是为了使用户配置可以扩展，但为什么空间需求会因此而增加。可能是什么原因。
We re-factored our user specific configuration fromcreate table user (id, name, ..., config1, config2, config3, ..) to
create table user (id, name, ...);
create table user_config (id, user_id, config_val);
Our MySQL database size increased by a factor of 2 after making this change and migrating the users from the older table to the newer table. We made this so that user configuration can be made extensible, but why does the space requirement go up because of this. What could be the reason.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您有一个包含 20 个字段和 1,000,000 个用户的原始表，那么这将是 20 * 1,000,000 = 20,000,000 个数据项。
举例来说，您现在拥有相同数量的用户，但将表减少到 10 个字段，并且有 10 个配置行，每行包含 3 个字段（根据您的代码）。这将是 10 * 1,000,000 + 10 * 3 * 1,000,000 = 50,000,000。这将是 2.5 的系数。
因此，基本上，对于每个配置变量，您现在添加一个 id（主键）和一个用户（外键）字段。除此之外，现在还需要生成更多索引数据。
因此，您的数据需求很可能急剧增加。
If you had an oritinal table with 20 fields, and 1,000,000 users, that would be 20 * 1,000,000 = 20,000,000 items of data.
Say, for example, you now have the same number of users, but decrease the table to 10 fields, and had 10 config rows with three fields each (as per your code). This would be 10 * 1,000,000 + 10 * 3 * 1,000,000 = 50,000,000. This would be a factor of 2.5.
So, basically, for each configuration variable, you are now adding an id (Primary Key), and a user (Foreign Key) field. Added to that, there is now more indexing data that has to be generated.
SO, it could very well be the case that your data requirements have dramatically increased.