在MySQL中，在一个表中有500列会好吗？ [英] in MySQL, is it good to have 500 Columns in one table?

问题描述

在MySQL中，在一个表中有500列会很好吗？
的行将每天增加，最大行数将小于或等于100万。

in MySQL, is it good to have 500 Columns in one table? the rows will be increasing daily, the maximum row count would be less than or equal to 1million.

为了简单起见，这些是我的列标题

just to give a brief, these are my column headers

表名：process_detail
id，process_id，item_id，item_category，attribute1，attribute2，attribute3。 ..，attribute500，user_id1_update_time，user_id2_update_time，user_id1_comments，user_id2_comments

所有属性都是长度最大为30但小于30的varchar。

all attributes are varchar with length maximum 30. but less than 30.

并且我有25个item_categories。

and i have 25 item_categories.

我应该创建一个像'process_detail'这样的单个表，还是应该创建多个表？按类别分类的表格，例如category1_process_detail，category2_process_detail，..，category25_process_detail

should i create one single table like 'process_detail', or should i create multiple tables categorywise like category1_process_detail,category2_process_detail,..,category25_process_detail

每个item_category的列数不同，有些将有200列，有些则只有50列。假设category1将具有200列，category2将具有50列。

each item_category has different number of columns, some will have 200 columns and some will have only 50 columns. lets say category1 will have 200 columns, category2 will have 50 columns.

category1中的某些列将不在category2中。

some columns in category1 will not be in category2.

item_ids可能在process_id2中。就良好的表现而言，最好的方法是什么？

item_ids in process_id1 may come in process_id2. what is the best approach in terms of good performance ? please adivce.

推荐答案

不，这不是一个好主意。而是使用多对一关系映射。

No it is not a good idea. Instead use a many to one relational mapping.

例如，按照您的建议创建 process_detail 表，但不包含属性列。然后创建另一个表 process_detail_attributes

For example, create the process_detail table as you propose but without the attribute columns. Then create another table process_detail_attributes

CREATE TABLE `process_detail_attributes`  
(`pda_id` INT NOT NULL AUTO_INCREMENT,  
 `id` INT NOT NULL,   
 `attribute_key` INT NOT NULL,  
 `attribute_value` VARCHAR(30) NOT NULL,  
  PRIMARY KEY(`pda_id`),  
  FOREIGN KEY (id) REFERENCES process_detail (id)
) ENGINE...

然后为每个需要的属性（ attribute1 ... attribute500 ）只需在属性表中输入一行，并在其中插入适当的ID

Then for each attribute (attribute1...attribute500) needed just enter a row into the attribute table with the appropriate id inserted into the foreign key column.

这样做的好处很多。 Doomenik提到的链接可能是理解原因的一个很好的起点，但要简洁地说...

The benefits of doing this are numerous. The link Doomenik mentions is probably a good starting point to understand why, but to put it tersely...

-如果不使用所有属性，则不会浪费

-即使使用属性，数据也将存储在实际索引B-Tree节点内，从而极大地增加了每页的数据量并减少了可容纳的页数缓冲池（即RAM）并减小键的位置。

-如果这些属性将需要索引（该属性通常需要索引），则该表的不合理性将不合情理。

-If all attributes aren't used there will be no wasted storage space.
-Even if the attributes are used, the data will be stored within the actual index B-Tree node, exorbitantly inflating the amount of data per page and decreasing the amount of pages able to fit in the buffer pool (i.e RAM) and decreasing the locality of the keys. This will subsequently slow the index traversal.
-If these attributes are going to require indices (which attributes often do) then the unruliness of this table will be unconscionable.

当然，有时为了性能考虑可以考虑去标准化，但这似乎不是其中之一。

There are of course times when you can consider de-normalization for the sake of performance but this does not seem like one of them.

然后，您可以从process_detail中选择具有其所有属性的数据，如下所示：

You can then select the data from process_detail with all of its attributes like this:

SELECT a.process_id,  
a.user_id1_u‌​pdate_time,  
a.user_id2_u‌​pdate_time,  
a.user_id1_comments,  
a.user_id2_comments,  
b.*  
FROM process_detail a INNER JOIN process_detail_attributes b  
WHERE a.id = b.id AND whatever_condition_you_want_to_filter_by_here;

这篇关于在MySQL中，在一个表中有500列会好吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！