mysql - 多少列太多了？

mysql - how many columns is too many?

提问by Brad

I'm setting up a table that might have upwards of 70 columns. I'm now thinking about splitting it up as some of the data in the columns won't be needed every time the table is accessed. Then again, if I do this I'm left with having to use joins.

我正在设置一个可能有 70 列以上的表格。我现在正在考虑将其拆分，因为每次访问表时都不需要列中的某些数据。再说一次，如果我这样做，我就不得不使用连接。

At what point, if any, is it considered too many columns?

在什么时候，如果有的话，它被认为是太多的列？

回答by ChssPly76

It's considered too many once it's above the maximum limit supported by the database.

一旦超过了数据库支持的最大限制，就被认为是太多了。

The fact that you don't need every column to be returned by every query is perfectly normal; that's why SELECT statement lets you explicitly name the columns you need.

您不需要每个查询都返回每一列的事实是完全正常的；这就是为什么 SELECT 语句让您明确命名您需要的列。

As a general rule, your table structure should reflect your domain model; if you really do have 70 (100, what have you) attributes that belong to the same entity there's no reason to separate them into multiple tables.

作为一般规则，您的表结构应该反映您的域模型；如果您确实有 70 个（100，您拥有什么）属于同一实体的属性，则没有理由将它们分成多个表。

回答by jonstjohn

There are some benefits to splitting up the table into several with fewer columns, which is also called Vertical Partitioning. Here are a few:

将表拆分为几个列数较少的表有一些好处，这也称为垂直分区。以下是一些：

1. If you have tables with many rows, modifying the indexes can take a very long time, as MySQL needs to rebuild all of the indexes in the table. Having the indexes split over several table could make that faster.

2. Depending on your queries and column types, MySQL could be writing temporary tables (used in more complex select queries) to disk. This is bad, as disk i/o can be a big bottle-neck. This occurs if you have binary data (text or blob) in the query.

3. Wider table can lead to slower query performance.

1. 如果你有很多行的表，修改索引可能需要很长时间，因为 MySQL 需要重建表中的所有索引。将索引拆分到多个表可以加快速度。

2. 根据您的查询和列类型，MySQL 可能会将临时表（用于更复杂的选择查询）写入磁盘。这很糟糕，因为磁盘 i/o 可能是一个很大的瓶颈。如果查询中有二进制数据（文本或 blob），就会发生这种情况。

3. 更宽的表会导致查询性能变慢。

Don't prematurely optimize, but in some cases, you can get improvements from narrower tables.

不要过早地优化，但在某些情况下，您可以从更窄的表中获得改进。

回答by JohnFx

It is too many when it violates the rules of normalization. It is pretty hard to get that many columns if you are normalizing your database. Design your database to model the problem, not around any artificial rules or ideas about optimizing for a specific db platform.

当它违反规范化规则时，它就太多了。如果您正在规范化数据库，则很难获得这么多列。设计您的数据库以对问题进行建模，而不是围绕针对特定数据库平台进行优化的任何人为规则或想法。

Apply the following rules to the wide table and you will likely have far fewer columns in a single table.

将以下规则应用于宽表，单个表中的列可能会少得多。

1. No repeating elements or groups of elements
2. No partial dependencies on a concatenated key
3. No dependencies on non-key attributes

1. 没有重复元素或元素组
2. 对连接的键没有部分依赖
3. 不依赖非关键属性

Here is a linkto help you along.

这是一个可以帮助您的链接。

回答by Zeeshan Ch

That's not a problem unless all attributes belong to the same entity and do not depend on each other. To make life easier you can have one text column with JSON array stored in it. Obviously, if you don't have a problem with getting all the attributes every time. Although this would entirely defeat the purpose of storing it in an RDBMS and would greatly complicate every database transaction. So its not recommended approach to be followed throughout the database.

除非所有属性都属于同一个实体并且不相互依赖，否则这不是问题。为了让生活更轻松，您可以在其中存储一个带有 JSON 数组的文本列。显然，如果您每次都没有获取所有属性的问题。尽管这将完全违背将其存储在 RDBMS 中的目的，并且会使每个数据库事务变得非常复杂。因此，不建议在整个数据库中遵循它的方法。

回答by Today

Having too many columns in the same table can cause huge problems in the replication as well. You should know that the changes that happen in the master will replicate to the slave.. for example, if you update one field in the table, the whole row will be w

同一个表中有太多列也会在复制中导致巨大的问题。你应该知道master发生的变化会复制到slave..例如，如果你更新表中的一个字段，整行将是w