哪个效率更高:多个MySQL表还是一个大表? [英] Which is more efficient: Multiple MySQL tables or one large table?

问题描述

我在MySQL数据库中存储了各种用户详细信息.最初，它是在各种表中设置的，这意味着数据与UserId链接在一起，并通过有时复杂的调用进行输出，以根据需要显示和操作数据.建立一个新系统，将所有这些表组合成一个大的相关内容表几乎是有意义的.

I store various user details in my MySQL database. Originally it was set up in various tables meaning data is linked with UserIds and outputting via sometimes complicated calls to display and manipulate the data as required. Setting up a new system, it almost makes sense to combine all of these tables into one big table of related content.

• 这是帮助还是阻碍?
• 调用，更新或搜索/操作时的速度考虑因素?

这是我的某些表结构的示例:

Here's an example of some of my table structure(s):

• 用户-用户ID，用户名，电子邮件，加密的密码，注册日期，ip
• user_details-Cookie数据，姓名，地址，联系方式，从属关系，人口统计数据
• user_activity-贡献，最近一次在线访问，最后一次查看
• user_settings-个人资料显示设置
• user_interests-广告可定位变量
• user_levels-访问权限
• user_stats-命中率，得分

编辑:到目前为止，我已经对所有答案进行了投票，它们都具有本质上可以回答我的问题的元素.

I've upvoted all answers so far, they all have elements that essentially answer my question.

大多数表具有1:1的关系，这是使它们归一化的主要原因.

Most of the tables have a 1:1 relationship which was the main reason for denormalising them.

如果表中的100多个列可能会大部分保留为空，那会不会有问题?

Are there going to be issues if the table spans across 100+ columns when a large portion of these cells are likely to remain empty?

推荐答案

多个表以下列方式/案例提供帮助:

Multiple tables help in the following ways / cases:

(a)如果不同的人要开发涉及不同表的应用程序，则将它们拆分是有意义的.

(a) if different people are going to be developing applications involving different tables, it makes sense to split them.

(b)如果您想为不同的人赋予不同的权限，以进行数据收集的不同部分，则拆分它们可能会更方便. (当然，您可以查看定义视图并对其进行适当授权).

(b) If you want to give different kind of authorities to different people for different part of the data collection, it may be more convenient to split them. (Of course, you can look at defining views and giving authorization on them appropriately).

(c)为了将数据移动到不同的地方，尤其是在开发过程中，使用导致较小文件大小的表可能是有意义的.

(c) For moving data to different places, especially during development, it may make sense to use tables resulting in smaller file sizes.

(d)在基于单个实体的特定数据收集开发应用程序时，较小的占地面积可能会给您带来舒适感.

(d) Smaller foot print may give comfort while you develop applications on specific data collection of a single entity.

(e)这是有可能的:您认为作为单个值数据的东西将来可能会变成真正的多个值.例如到目前为止，信用额度是一个单一值字段.但是明天，您可能会决定将值更改为(日期从，日期到，信用值).拆分表现在可能会派上用场.

(e) It is a possibility: what you thought as a single value data may turn out to be really multiple values in future. e.g. credit limit is a single value field as of now. But tomorrow, you may decide to change the values as (date from, date to, credit value). Split tables might come handy now.

我的投票将用于多个表-数据已适当拆分.

My vote would be for multiple tables - with data appropriately split.

祝你好运.

这篇关于哪个效率更高:多个MySQL表还是一个大表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！