外键会影响查询性能吗 [英] Can foreign keys hurt query performance

问题描述

正如我从 这篇文章 中了解到的，有些情况下外国键可以提高查询性能.

As I understood from this post, there are some scenarios where foreign keys can improve query performance.

不过，我听说过相反的说法，即由于参照完整性检查，外键实际上会损害查询性能.在什么条件下(如果有的话)这是真的?

I've heard the opposite claim though, that because of referential integrity checks, foreign keys can actually hurt query performance. Under which conditions (if at all) is this true?

1) 术语查询似乎具有误导性.我对各种性能惩罚感兴趣.

1) The term query seems to be misleading. I am interested in all kinds of performance penalties.

2) 是否有人对 INSERT、DELETE 或 UPDATE 语句的负面影响有任何真实世界的数字(我知道这取决于特定系统，但无论如何都会感谢任何类型的真实世界测量)?

2) Does anyone have any real-world numbers about the negative impact on INSERT, DELETE or UPDATE statements (I know it depends on the specific system, but nevertheless any kind of real-world measurements would be appreciated)?

推荐答案

我假设对于 INSERT 查询，约束(包括外键约束)会在一定程度上降低性能.数据库必须检查您告诉它插入的内容是否是您的约束允许它插入的内容.

I'm assuming that for INSERT queries, constraints - including foreign key constraints - will slow performance somewhat. The database has to check that whatever you've told it to insert is something that your constraints allow it to insert.

对于 SELECT 查询，外键约束不应对性能造成任何改变.

For SELECT queries, foreign key constraints shouldn't make any changes to performance.

由于 INSERTS 几乎总是非常快，因此少量的额外时间不会被注意到，除了在边缘情况下.(构建一个数 GB 的数据库，您可能希望禁用约束，然后稍后重新启用，只要您确定数据是好的.)

Since INSERTS are almost always very quick, the small amount of extra time won't be noticeable, except in fringe cases. (Building a several gigabyte database, you might want to disable constraints and then re-enable later, as long as you're sure the data is good.)

这篇关于外键会影响查询性能吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！