多表会降低结果速度吗？
我确实有一个包含多个表的数据库。
例如，此多个表与单个名称相关。
Table 1 contains name of the person, joined date,position,salary..etc
Table2 contains name of the person,current projects,finished,assigned...etc
Table 3 contains name of the person,time sheets,in,out,etc...
Table 4 contains name of the person,personal details,skill set,previous experiance,...etc
所有表包含超过 50000 个名称及其详细信息。
所以我的问题是所有表都包含与名称相关的信息，比如 Jose20856 这个名称是所有 4 个表的唯一索引。当我搜索Jose20856时，所有四个表都会给出结果并输出到前端软件/html。 那么我需要保留多个表还是合并为一个表？
如果是这样，
CASE 1
Single table -> what are the advantages? will result will be faster? what about the system resource usage?
CASE 2
Multiple table ->what are the advantages? will result will be faster? what about the system resource usage?
由于我是 MySQL 新手，我希望获得您的宝贵意见以继续前进
I do have a datbase with multiple tables.
this multiple table is related to single name for example..
Table 1 contains name of the person, joined date,position,salary..etc
Table2 contains name of the person,current projects,finished,assigned...etc
Table 3 contains name of the person,time sheets,in,out,etc...
Table 4 contains name of the person,personal details,skill set,previous experiance,...etc
All table contains morethan 50000 names, and their details.
so my question is all tables contains information related to a name say Jose20856 this name is unique index of all 4 tables. when I search for Jose20856 all four table will give result and output to a front end software/html.
so do I need to keep multiple table or combined to a single table??
If so
CASE 1
Single table -> what are the advantages? will result will be faster? what about the system resource usage?
CASE 2
Multiple table ->what are the advantages? will result will be faster? what about the system resource usage?
As I am new to MySQL I would like to have your valuable opinion to move ahead
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可以将它们合并到一个表中，但前提是有意义。很难判断表中的关系是一对一还是一对多，但似乎是一对多。例如，表 1 中的单个员工应该能够在其他表中拥有多个项目、技能和时间表。这些都是一对多的关系。
因此，保留多表设计。您还应该考虑对员工使用基于整数的主键而不是姓名。使用此 pkey 作为其他表中的 fkey，您将看到性能改进。 （还要考虑如果您想要更改名称，以及何时更改名称，您需要做的工作量。您必须更改所有表中的所有名称。如果您使用代理键，即 int pkey，如上面所建议的，您只需必须更新一行。）
You can combine these into a single table but only if it makes sense. It's hard to tell if the relationships in your tables are one-to-one or one-to-many but seem to be one-to-many. e.g. A single employee from table 1 should be able to have multiple projects, skills, time sheets in the other tables. These are all one-to-many relationships.
So, keep the multiple table design. You also should consider using an integer-based primary key for the employee rather than the name. Use this pkey as the fkey in your other tables and you'll see performance improvement. (Also consider the amount of work you need to do if and when you want to change the name. You have to change all the names in all the tables. If you use a surrogate key, the int pkey, as suggested above, you only have to update a single row.)
在网上阅读有关数据库规范化的信息。
例如 http://en.wikipedia.org/wiki/Database_normalization
我认为你甚至可以添加更多表给它。这一切都取决于数据和关系。
我认为您的表 4 可以合并到表 1 中，因为它还包含特定于 1 个用户的数据。
您可能还可以做更多事情，但正如已经说过的，这一切都取决于关系。
Read on the web about database normalization.
E.g. http://en.wikipedia.org/wiki/Database_normalization
I think you can even add more tables to it. It all depends on the data and the relations.
I think your table 4 can be merged into table 1 cause it also contains data specific to 1 user.
There is probably more you can do but as already stated it all depends and the relations.
我们在这里讨论的是垂直表分区（与水平表相反）分区）。这是一种有效的数据库设计模式，在以下情况下很有用：
加入日期、职位、薪水列，而很少需要显示个人详细信息、技能组合、先前经验列，那么移动是有意义的这些列来分隔表，因为它（可能）会提高访问最常用的列的性能。在 MySQL 中，对于 TEXT 和 BLOB 列尤其如此，因为它们与其余文件分开存储，因此访问它们需要更多时间。最后但并非最不重要的。我建议使用数字字段作为连接所有这些表的键，而不是字符串。
有关 MySQL 分区的其他阅读（有点过时了，因为 MySQL 5.5添加了一些新功能）
What we're talking about here is vertical table partitioning (as opposed to horizontal table partitioning). It is a valid database design pattern, which can be useful in these cases:
joined date,position,salaryand columnspersonal details,skill set,previous experiancevery rarely, then it makes sense to move these columns to separate a table, as it will (probably) improve performance in accessing those most commonly used. In MySQL this is especially true in case of TEXT and BLOB columns, since they're stored apart from the rest of the fileds, so accessing them takes more time.Last but not least. I'd suggest using a numeric field as a key joining all these tables, not a string.
Additional reading aboout MySQL partitioning (a bit outdated, since MySQL 5.5 added some new features)