带有40多个列的mysql表 [英] mysql table with 40+ columns

问题描述

我的表格中有40列以上的列，我还需要添加一些其他字段，例如当前的城市，家乡，学校，工作，大学，拼贴画.

I have 40+ columns in my table and i have to add few more fields like, current city, hometown, school, work, uni, collage..

这些用户数据将被拉给许多匹配的用户，这些用户是共同的朋友(与其他用户朋友一起加入朋友表以查看共同的朋友)，并且未被阻止，也尚未与该用户成为朋友.

These user data wil be pulled for many matching users who are mutual friends (joining friend table with other user friend to see mutual friends) and who are not blocked and also who is not already friend with the user.

上面的请求有点复杂，所以我认为将额外的数据放在同一用户表中以进行快速访问是个好主意，而不是向该表中添加更多的联接，这将使查询的速度变慢.但是我想得到你的建议

The above request is little complex, so i thought it would be good idea to put extra data in same user table to fast access, rather then adding more joins to the table, it will slow the query more down. but i wanted to get your suggestion on this

我的朋友告诉我添加额外的字段，这些字段不会作为序列化数据在一个字段中搜索.

my friend told me to add the extra fields, which wont be searched on one field as serialized data.

ERD图:

ERD Diagram:

• 我当前的表格: http://i.stack.imgur.com/KMwxb.png
• 如果我加入其他表格: http://i.stack.imgur.com/xhAxE.png

• My current table: http://i.stack.imgur.com/KMwxb.png
• If i join into more tables: http://i.stack.imgur.com/xhAxE.png

一些建议

Some Suggestions

1. 此表和列没有问题
2. 遵循这种方法 MySQL:优化具有很多列的表 -将多余的字段序列化为一个不可搜索的字段
3. 创建另一个表，并将大部分数据放在该表中. (如果我已经有3个或更多的表要联接以为用户(例如，朋友，用户，检查共同的朋友)拉记录，则联接起来会变得更加困难

1. nothing wrong with this table and columns
2. follow this approach MySQL: Optimize table with lots of columns - which serialize extra fields into one field, which are not searchable's
3. create another table and put most of the data there. (this gets harder on joins, if i already have 3 or more tables to join to pull the records for users (ex. friends, user, check mutual friends)

推荐答案

与往常一样-这要视情况而定.

As usual - it depends.

首先，有一个 MySQL的最大列数可以支持，而您真的不想到达那里.

Firstly, there is a maximum number of columns MySQL can support, and you don't really want to get there.

第二，如果您有很多带有索引的列，则在插入或更新时会影响性能(尽管我不确定这在现代硬件上是否很重要).

Secondly, there is a performance impact when inserting or updating if you have lots of columns with an index (though I'm not sure if this matters on modern hardware).

第三，大表通常是似乎与核心实体相关的所有数据的转储场；这很快使设计不清楚.例如，您呈现的设计显示了3个不同的状态"类型字段(状态，is_admin和fb_account_verified)-我怀疑应该将某些业务逻辑链接在一起(例如，管理员必须是经过验证的用户)，但是您的设计不支持这一点.

Thirdly, large tables are often a dumping ground for all data that seems related to the core entity; this rapidly makes the design unclear. For instance, the design you present shows 3 different "status" type fields (status, is_admin, and fb_account_verified) - I suspect there's some business logic that should link those together (an admin must be a verified user, for instance), but your design doesn't support that.

这可能是问题，也可能不是问题-与其说是性能/是否可行，不如说是概念，体系结构/设计问题.但是，在这种情况下，即使它没有多对多关系，您也可以考虑创建表以反映有关该帐户的相关信息.因此，您可以创建"user_profile"，"user_credentials"，"user_fb"，"user_activity"，并通过user_id进行链接. 这使其变得更整洁，并且如果您必须添加更多与Facebook相关的字段，则它们不会在表的末尾悬垂.但是，它不会使您的数据库更快或更可扩展.联接的成本可能微不足道.

This may or may not be a problem - it's more a conceptual, architecture/design question than a performance/will it work thing. However, in such cases, you may consider creating tables to reflect the related information about the account, even if it doesn't have a x-to-many relationship. So, you might create "user_profile", "user_credentials", "user_fb", "user_activity", all linked by user_id. This makes it neater, and if you have to add more facebook-related fields, they won't dangle at the end of the table. It won't make your database faster or more scalable, though. The cost of the joins is likely to be negligible.

无论您做什么，选项2-将很少使用的字段"序列化为单个文本字段-是一个糟糕的主意.您无法验证数据(因此日期可能无效，数字可能是文本，可能会丢失非null)，并且在"where"子句中的任何使用都变得非常慢.

Whatever you do, option 2 - serializing "rarely used fields" into a single text field - is a terrible idea. You can't validate the data (so dates could be invalid, numbers might be text, not-nulls might be missing), and any use in a "where" clause becomes very slow.

一个流行的替代方法是实体/属性/值"或键/值"存储.此解决方案有一些好处-即使架构发生更改或在设计时未知，您也可以将数据存储在关系数据库中.但是，它们也有缺点:很难在数据库级别验证数据(数据类型和可空性)，很难使用外键关系与其他表进行有意义的链接，并且查询数据可能会变得非常复杂-想象找到所有记录状态为1且facebook_id为null且注册日期大于昨天的记录.

A popular alternative is "Entity/Attribute/Value" or "Key/Value" stores. This solution has some benefits - you can store your data in a relational database even if your schema changes or is unknown at design time. However, they also have drawbacks: it's hard to validate the data at the database level (data type and nullability), it's hard to make meaningful links to other tables using foreign key relationships, and querying the data can become very complicated - imagine finding all records where the status is 1 and the facebook_id is null and the registration date is greater than yesterday.

鉴于您似乎了解数据的架构，因此我想说键/值"不是一个好选择.

Given that you appear to know the schema of your data, I'd say "key/value" is not a good choice.

这篇关于带有40多个列的mysql表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！