在 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，因此插入、更新和删除的性能会受到很小的影响.对于单个记录，除非您开始将大量 FK 关联到该表(显然，检查 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 万条记录和 FK，它们应该出现在任何地方，很少注意到性能下降(除了一个设计糟糕的表，它有超过 100 个外键，从其中删除记录有点慢这一切都必须检查).我认识的几乎所有处理 TB 级大型数据库的 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屋！