MySQL 中是拥有 1 个大表还是 2 个较小的表更好？
这是我的情况：我需要为 1 条记录存储大约 50 个不同的数据字段（没有一个是相同或重复的）。其中大约 15 个字段是我在查询中经常需要使用的字段，而其余字段偶尔会使用（约占查询的 40%）。
我是否应该设置 2 个表，一个包含公共字段，另一个包含不太常见的字段，并将它们以 1:1 关系连接？或者我应该把它们全部放在一张桌子上？
执行其中一项或另一项是否有任何优势（速度或其他方面）？
Here is my situation: I have about 50 different fields of data that I need to store for 1 record (none are the same or repeating). About 15 of the fields are ones that I commonly need to use in queries, while the remainder are used on occasion (~40% of the queries).
Should I setup 2 tables, one containing the common fields and the other with the less common fields and join them in a 1:1 relationship? Or should I just put them all in one table?
Is there any advantage, speed or otherwise, do doing one or the other?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
两个表意味着 40% 的查询有联接，60% 的查询没有联接。
总体而言，您并没有节省存储空间。
40% 的查询速度较慢。
60% 的查询速度更快有两个原因。 1）没有加入。 2）更少的物理数据块。
这场表演是否不同“重要”？这会让事情变得“更好”吗？
除非您更清楚地定义每个查询所花费的时间，并运行基准测试来衡量差异，否则这并不重要。
在决定哪个“更好”之前，您必须实际构建并测量性能。
Two tables means 40% of your queries have joins and 60% don't have joins.
You don't save storage overall.
40% of your queries are slower.
60% of your queries are faster for two reasons. 1) no join. 2) fewer physical data blocks.
Is this performance different "important"? Does this make things "better"?
Until you more clearly define the time spent on each query -- and run benchmark tests to measure the difference -- it doesn't matter.
You have to actually build and measure the performance before you can decide which is "better".
假设表的行在逻辑上是单独的实体，并且所有字段都与该实体相关，那么它们都应该位于同一个表中。
然而，您可能有一个表，其中每一行在逻辑上都是多个实体； 重构，使其成为每个表的实体，通常称为规范化，或这样的模式：规范化。
规范化模式通常被认为是“正确的”，但在表之间连接时需要考虑性能。
Assuming the rows of your table are logically individual entities and all the fields are relevant to the entity, then they should all be in the same table.
You may have a table, however, where each row is logically multiple entities; refactoring so that this is an entity-per-table is usually called normalization, or such a schema: normalized.
A normalized schema is usually considered "correct" but there are performance considerations when joining between tables.
最重要的是尽量减少 NULL。如果表中的其他 35 个字段通常为 NULL，请考虑使用单独的表方法。如果它们通常为非 NULL，则一张表可能就可以了。
The main thing is to minimize NULLs. If you the other 35 fields in your table are typically NULL, think about going with the separate table approach. If they're usually non-NULL, one table is probably fine.