数据库关系
在数据库中建立正确的关系除了数据完整性之外还有其他帮助吗？
它们会提高还是阻碍绩效？
does setting up proper relationships in a database help with anything else other than data integrity?
do they improve or hinder performance?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
只要您有与外键相对应的明显索引，就不会对性能产生明显的负面影响。 这是您必须使用的最简单的数据库功能之一。
As long as you have the obvious indexes in place corresponding to the foreign keys, there should be no perceptible negative effect on performance. It's one of the more foolproof database features you have to work with.
我不得不说，与忽略它们相比，正确的关系将帮助人们更好地理解数据（或数据的意图），特别是因为维护它们的总体成本相当低。
它们的存在不会影响性能，除了架构方面（正如其他人指出的那样，数据完整性偶尔会导致外键违规，这可能会产生一些影响），但恕我直言，它的许多好处超过了（如果使用正确）。
我知道你不是问是否使用 FK，但我想我只是添加一些关于为什么使用它们的观点（并且必须处理后果）：
还有其他考虑因素，例如如果如果您计划使用 ORM（也许稍后），您将需要外键。 它们对于 ETL/数据导入和导出以及随后的报告和数据仓库也非常有帮助。
如果其他应用程序使用该模式，这也会很有帮助 - 因为外键实现了基本的业务逻辑。 因此，您的应用程序（以及任何其他应用程序）只需要了解这些关系（并尊重它们）。 它将保持数据的一致性，并且很可能减少任何消费应用程序中的数据错误数量。
最后，它为您提供了关于在何处放置索引的相当不错的提示 - 因为您可能会通过 FK 值查找表数据。
I'd have to say that proper relationships will help people to understand the data (or the intention of the data) better than if omitting them, especially as the overall cost is quite low in maintaining them.
Their presence doesn't hinder performance except in terms of architecture (as others have pointed out, data integrity will occasionally cause foreign key violations which may have some effect) but IMHO is outweighed by the many benefits (if used correctly).
I know you weren't asking whether to use FKs or not, but I thought I'd just add a couple of viewpoints about why to use them (and have to deal with the consequences):
There are other considerations too, such as if you ever plan to use an ORM (perhaps later on) you'll require foreign keys. They can also be very helpful for ETL/Data Import and Export and later for reporting and data warehousing.
It's also helpful if other applications will make use of the schema - since Foreign Keys implement a basic business logic. So your application (and any others) only need to be aware of the relationships (and honour them). It'll keep the data consistent and most likely reduce the number of data errors in any consuming applications.
Lastly, it gives you a pretty decent hint as to where to put indexes - since it's likely you'll lookup table data by an FK value.
它既不会以任何显着的方式帮助也不会损害性能。 唯一的障碍是插入/更新/删除时的完整性检查。
外键是数据库设计的重要组成部分，因为它们确保一致性。 您应该使用它们，因为它提供了最低级别的保护，防止可能破坏您的应用程序的数据错误。 另一个好处是数据库工具（可视化/分析/代码生成）使用外键来关联数据。
It neither helps nor hurts performance in any significant way. The only hindrance is the check for integrity when inserting/updating/deleting.
Foreign keys are an important part of database design because they ensure consistency. You should use them because it offers the lowest level of protection against data screw ups that can wreck your applications. Another benefit is that database tools (visualization/analysis/code generation) use foreign keys to relate data.
与工具箱中的任何工具一样，您获得的结果取决于您如何使用它。 正确指定的关系和精心设计的逻辑数据库可以极大地提高性能——例如，考虑搜索规范化数据和非规范化数据之间的差异。
Like any tool in your toolbox, the results you'll get depend on how you use it. Properly specified relationships and a well-designed logical database can be an enormous boon to performance -- consider the difference between searching through normalized and denormalized data, for example.
根据您的数据库引擎，通过外键约束定义的关系可以提高性能。 该约束允许引擎对键父端表中数据的存在做出某些假设。
有关 MS SQL Server 的简要说明，请访问 http://www .microsoft.com/technet/abouttn/flash/tips/tips_122104.mspx。 我不知道其他引擎，但这个概念在其他平台上是有意义的。
Depending on your database engine, relationships defined through foreign key constraints can benefit performance. The constraint allows the engine to make certain assumptions about the existence of data in tables on the parent side of the key.
A brief explanation for MS SQL Server can be found at http://www.microsoft.com/technet/abouttn/flash/tips/tips_122104.mspx. I don't know about other engines, but the concept would make sense in other platforms.
无论您是否声明，数据中的关系都存在。 通过 FK 约束声明和强制执行关系将防止数据中出现某些类型的错误，并且在发生插入/更新/删除时检查数据的成本很小。
通过关系声明级联删除有助于防止删除数据时出现某些类型的错误。
了解这些关系有助于在形成查询时灵活、正确地使用数据。
设计好表格可以让关系更明显、更有用。 使用数据中的关系是使用关系数据库的主要动力。
Relationships in the data exist whether you declare them or not. Declaring and enforcing the relationships via FK constraints will prevent certain kinds of errors in the data, at a small cost of checking data when inserts/updates/deletes occur.
Declaring cascading deletes via relationships helps prevent certain kinds of errors when deleting data.
Knowing the relationships helps to make flexible and correct use of the data when forming queries.
Designing the tables well can make the relationships more obvious and more useful. Using relationships in the data is the primary power behind using relational databases in the first place.
关于对性能的影响：根据我使用 MS Access 2003 的经验，如果您有一个多用户应用程序并使用关系来强制执行大量引用完整性，那么最终用户的响应时间可能会受到很大影响。
有不同的方法可以实现引用完整性。 我决定在关系中删除一些规则，在前端建立更多的强制执行，并忍受 RI 的一些损失。 当然，在多用户环境中，您需要非常小心地对待这种自由。
About impact on performance: In my experience with MS Access 2003, if you have a multi-user application and use Relationships to enforce a lot of referential integrity, you can take a big hit in terms of response time for the end-user.
There are different ways to take care of enforcing referential integrity. I decided to take out some rules in Relationships, build more enforcement into the front-end and live with some loss of RI. Of course in the multi-user environment, you want to be very careful with that bit of liberty.
根据我构建性能敏感数据库的经验，外键会严重损害性能，因为每次插入/更新引用记录或删除主记录时都必须检查外键。 如果需要证明，只要看执行计划就可以了。
我仍然保留它们作为文档和使用工具，但我通常禁用它们，特别是在只能通过应用程序层访问数据库的高性能系统中。
In my experience building performance-sensitive databases, Foreign Keys hurt performance pretty significantly, since they have to be checked every time the referring record is inserted/updated or master record is deleted. If you need a proof, just look at the execution plan.
I still keep them for documentation and for tools to use but I usually disable them, especially in high-performance systems where access to DB is only through the application layer.