I have seen few questions related to this but I felt they weren't exactly the same situation. This is also not a normalization related question.
我看到几个与此相关的问题，但我觉得它们并非完全相同。这也不是与标准化相关的问题。
Lets assume that we have a product which has some properties such as name,description,price,last_update_date,stock_amount
让我们假设我们的产品有一些属性，如名称，描述，价格，last_update_date，stock_amount
Lets assume, there will never be 2 different prices or stocks etc. for these 'product's and we don't have to keep historic data etc.
让我们假设，这些'产品永远不会有2种不同的价格或库存等，我们不必保留历史数据等。
From a performance point of view, would it be better to keep all of these data in a single table? or divide it into seperate tables? such as:
从性能的角度来看，将所有这些数据保存在一个表中会更好吗？或将其划分为单独的表格？如：
products -> id, name, last_update_date, stock_amount, price
product_info -> id, products_id, description
I know data is not divided very logically but that is besides the point right now.
我知道数据在逻辑上没有划分，但现在除了这一点之外。
I can think of 2 arguments perhaps,
我可以想到两个论点，
- If you separate data into 2 tables, for example to update description, one would need to find products_id then update the data, which may cost more. On the other hand the products table's storage footprint would be so much smaller. Does this help in efficiency when finding the product, for example by name? or since we would have an index for 'name' it wouldn't matter how big the table is on disk?
- 如果将数据分成2个表，例如更新描述，则需要查找products_id然后更新数据，这可能会花费更多。另一方面，产品表的存储空间将小得多。这在查找产品时是否有助于提高效率，例如通过名称？或者因为我们有一个'name'的索引，所以表在磁盘上有多大并不重要？
- Well, if everything was in one table, we wouldn't need to work on separate tables and this may increase efficiency?
- 好吧，如果一切都在一个表中，我们就不需要在单独的表上工作了，这可能会提高效率？
What do you think? and what do you base your opinion on? Links and benchmark results are welcome.
你怎么看？你的观点是什么？欢迎链接和基准测试结果。
Thanks!
谢谢！
2 个解决方案
#1
1
If everything is a 1-to-1 mapping, there's no strong reason not to keep it all in one table. You should still have an ID column, so that if you have other data that's 1-to-many or many-to-many, you can refer to the products by ID in those tables.
如果所有内容都是一对一的映射，那么就没有充分的理由不将它全部保存在一个表中。您仍然应该有一个ID列，这样，如果您有其他数据是1对多或多对多，您可以在这些表中按ID引用产品。
However, one benefit of splitting it up into different tables can be improved concurrency. If everything is in one table, then an update to that table will lock the entire row (or the entire table if you use MyISAM). If you split it into multiple tables, then an update to one of the those tables won't interfere with queries that use the other tables.
但是，将其拆分为不同的表的一个好处是可以提高并发性。如果所有内容都在一个表中，那么对该表的更新将锁定整个行（如果使用MyISAM，则锁定整个表）。如果将其拆分为多个表，则对其中一个表的更新不会干扰使用其他表的查询。
#2
-1
I think efficiency is better with a single table. Two tables may be useful for further scalability.
我认为使用单个表可以提高效率。两个表可用于进一步的可伸缩性。
#1
1
If everything is a 1-to-1 mapping, there's no strong reason not to keep it all in one table. You should still have an ID column, so that if you have other data that's 1-to-many or many-to-many, you can refer to the products by ID in those tables.
如果所有内容都是一对一的映射，那么就没有充分的理由不将它全部保存在一个表中。您仍然应该有一个ID列，这样，如果您有其他数据是1对多或多对多，您可以在这些表中按ID引用产品。
However, one benefit of splitting it up into different tables can be improved concurrency. If everything is in one table, then an update to that table will lock the entire row (or the entire table if you use MyISAM). If you split it into multiple tables, then an update to one of the those tables won't interfere with queries that use the other tables.
但是，将其拆分为不同的表的一个好处是可以提高并发性。如果所有内容都在一个表中，那么对该表的更新将锁定整个行（如果使用MyISAM，则锁定整个表）。如果将其拆分为多个表，则对其中一个表的更新不会干扰使用其他表的查询。
#2
-1
I think efficiency is better with a single table. Two tables may be useful for further scalability.
我认为使用单个表可以提高效率。两个表可用于进一步的可伸缩性。