在SQL Server中使用外键是否有严重的性能损失？ [英] Is there a severe performance hit for using Foreign Keys in SQL Server?

问题描述

我尽力劝说我的老板让我们在我们的数据库中使用外键 - 到目前为止没有运气。

I'm trying my best to persuade my boss into letting us use foreign keys in our databases - so far without luck.

他声称它花费了大量的钱的表现，并表示我们现在只需要清理无效引用的工作，然后再进行。

He claims it costs a significant amount of performance, and says we'll just have jobs to cleanup the invalid references now and then.

显然，这在实践中不起作用，数据库充斥着有没有参考资料。

Obviously this doesn't work in practice, and the database is flooded with invalid references.

有谁知道比较，基准或类似的，这证明使用外键没有显着的性能打击？ （我希望会说服他）

Does anyone know of a comparison, benchmark or similar which proves there's no significant performance hit to using foreign keys? (Which I hope will convince him)

推荐答案

由于FK必须被检查。对于单个记录，这通常是如此轻微，以至于不明显，除非你开始有一个可笑的数量的FKs关联到表（显然，需要更长时间检查100其他表而不是2）。这是一件好事不是一件坏事，因为没有完整性的数据库是不可信的，因此没有用。你不应该以速度进行诚信。通常由优化执行计划的能力来抵消这种表现。

There is a tiny performance hit on inserts, updates and deletes because the FK has to be checked. For an individual record this would normally be so slight as to be unnoticeable unless you start having a ridiculous number of FKs associated to the table (Clearly it takes longer to check 100 other tables than 2). This is a good thing not a bad thing as databases without integrity are untrustworthy and thus useless. You should not trade integrity for speed. That performance hit is usually offset by the better ability to optimize execution plans.

我们有一个中型数据库，其中约900万条记录和FKs应该是应该的，很少注意到一个表演命中（除了一个设计不佳的表，有超过100个外键，从这里删除记录是有点慢，因为都必须检查）。几乎每个dba我知道谁处理大型TB大小的数据库，而且对大型数据集的高性能的真正需求坚持外键约束，因为完整性是任何数据库的关键。如果拥有太字节大小的数据库的人能够承受极小的性能影响，那么你也可以。

We have a medium sized database with around 9 million records and FKs everywhere they should be and rarely notice a performance hit (except on one badly designed table that has well over 100 foreign keys, it is a bit slow to delete records from this as all must be checked). Almost every dba I know of who deals with large, terabyte sized databases and a true need for high performance on large data sets insists on foreign key constraints because integrity is key to any database. If the people with terabyte-sized databases can afford the very small performance hit, then so can you.

FK不会自动编入索引，如果没有编入索引，可能会导致性能问题。

FKs are not automatically indexed and if they are not indexed this can cause performance problems.

老实说，我'拿一个数据库的副本，添加正确索引的FK，并显示时间差，从这些表中插入，删除，更新和选择，而不需要FK就可以从数据库中进行比较。显示您不会导致性能下降。然后显示查询的结果，显示孤立的记录不再有意义，因为它们相关的PK不再存在。对于包含财务信息的表（我们有2700个订单，我们不能与客户联系）将使管理层坐起来并注意，这是特别有效的。

Honestly, I'd take a copy of your database, add properly indexed FKs and show the time difference to insert, delete, update and select from those tables in comparision with the same from your database without the FKs. Show that you won't be causing a performance hit. Then show the results of queries that show orphaned records that no longer have meaning because the PK they are related to no longer exists. It is especially effective to show this for tables which contain financial information ("We have 2700 orders that we can't associate with a customer" will make management sit up and take notice).

这篇关于在SQL Server中使用外键是否有严重的性能损失？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！