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

问题描述

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

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):

• users - UserId、用户名、电子邮件、加密密码、注册日期、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屋！