SQL 外键是否提高查询性能？

Does Foreign Key improve query performance?

提问by Chaowlert Chaisrichalermpol

Suppose I have 2 tables, Products and ProductCategories. Both tables have relationship on CategoryId. And this is the query.

假设我有 2 个表，Products 和 ProductCategories。两个表在 CategoryId 上都有关系。这就是查询。

SELECT p.ProductId, p.Name, c.CategoryId, c.Name AS Category
FROM Products p
INNER JOIN ProductCategories c ON p.CategoryId = c.CategoryId
WHERE c.CategoryId = 1;

When I create execution plan, table ProductCategories performs cluster index seek, which is as expectation. But for table Products, it performs cluster index scan, which make me doubt. Why FK does not help improve query performance?

当我创建执行计划时，表 ProductCategories 执行集群索引查找，这是预期的。但是对于表Products，它执行集群索引扫描，这让我怀疑。为什么 FK 无助于提高查询性能？

So I have to create index on Products.CategoryId. When I create execution plan again, both tables perform index seek. And estimated subtree cost is reduced a lot.

所以我必须在 Products.CategoryId 上创建索引。当我再次创建执行计划时，两个表都执行索引查找。并且估计子树成本降低了很多。

My questions are:

我的问题是：

1. Beside FK helps on relationship constraint, does it have any other usefulness? Does it improve query performance?

2. Should I create index on all FK columns (liked Products.CategoryId) in all tables?

1. 除了 FK 有助于关系约束之外，它还有其他用处吗？它是否提高了查询性能？

2. 我应该在所有表中的所有 FK 列（喜欢 Products.CategoryId）上创建索引吗？

回答by cmsjr

Foreign Keys are a referential integrity tool, not a performance tool. At least in SQL Server, the creation of an FK does not create an associated index, and you should create indexes on all FK fields to improve look up times.

外键是参照完整性工具，而不是性能工具。至少在 SQL Server 中，创建 FK 不会创建关联索引，您应该在所有 FK 字段上创建索引以提高查找时间。

回答by Lieven Keersmaekers

Foreign Keys can improve (and hurt) performance

外键可以提高（和损害）性能

1. As stated here: Foreign keys boost performance

2. You should always create indexes on FK columns to reduce lookups. SQL Server does not do this automatically.

1. 如此处所述：外键提高性能

2. 您应该始终在 FK 列上创建索引以减少查找。SQL Server 不会自动执行此操作。

Edit

编辑

As the link now seems to be dead (kudos to Chris for noticing), following shows the gist of why foreign keys can improve (and hurt) performance.

由于链接现在似乎已失效（感谢 Chris 的注意），下面显示了为什么外键可以提高（和损害）性能的要点。

Can Foreign key improve performance

外键可以提高性能吗

Foreign key constraint improve performance at the time of reading data but at the same time it slows down the performance at the time of inserting / modifying / deleting data.

In case of reading the query, the optimizer can use foreign key constraints to create more efficient query plans as foreign key constraints are pre declared rules. This usually involves skipping some part of the query plan because for example the optimizer can see that because of a foreign key constraint, it is unnecessary to execute that particular part of the plan.

外键约束提高了读取数据时的性能，但同时降低了插入/修改/删除数据时的性能。

在读取查询的情况下，优化器可以使用外键约束来创建更有效的查询计划，因为外键约束是预先声明的规则。这通常涉及跳过查询计划的某些部分，因为例如优化器可以看到由于外键约束，没有必要执行计划的特定部分。

回答by John Sansom

A foreign key is a DBMS concept for ensuring database integrity.

外键是用于确保数据库完整性的 DBMS 概念。

Any performance implications/improvements will be specific to the database technology being used and are secondary to the purpose of a foreign key.

任何性能影响/改进都将特定于所使用的数据库技术，并且次于外键的目的。

It is good practice in SQL Server to ensure that all foreign keys have at least a non clustered index on them.

在 SQL Server 中确保所有外键上至少有一个非聚集索引是一种很好的做法。

I hope this clears things up for you but please feel free to request more details.

我希望这可以为您解决问题，但请随时索取更多详细信息。

回答by Al Katawazi

Your best performance bet is to use Indexes on fields you use frequently. If you use SQL Server you can use profiler to profile a specific database and take the file that outputs and use the tuning wizard to recieve recommendations on where to place your indexes. I also like using profiler to flush out long running stored procedures, I have a top ten worst offenders list I publish every week, keeps people honest :D.

您最好的性能选择是在您经常使用的字段上使用索引。如果您使用 SQL Server，您可以使用探查器分析特定数据库并获取输出文件并使用调整向导接收有关放置索引的位置的建议。我也喜欢使用分析器来清除长时间运行的存储过程，我每周都会发布前十名最严重的违规者名单，让人们保持诚实：D。

回答by kemiller2002

You can use it to help make a query more efficient. It does allow you to restructure queries in SQL Server to use an outer join instead of an inner one which removes sql servers necesity of having to check if there is a null in the column. You don't need to put that qualifier in because the foreign key relationship already inforces that for you.

您可以使用它来帮助提高查询效率。它确实允许您在 SQL Server 中重构查询以使用外部联接而不是内部联接，从而消除了 sql server 必须检查列中是否存在空值的必要性。您不需要放入该限定符，因为外键关系已经为您强制执行了。

So this:

所以这：

    select p.ProductId, p.Name, c.CategoryId, c.Name AS Category 
from Products p inner join ProductCategories c on p.CategoryId = c.CategoryIdwhere c.CategoryId = 1;

Becomes this:

变成这样：

SELECT p.ProductId, p.Name, c.CategoryId, c.Name AS Category 
FROM ProductCategories c 
LEFT OUTER JOIN Products P ON
c.CategoryId = p.CategoryId 
WHERE c.CategoryId = 1;

This won't necessarily make a huge performance in small queries, but when tables get large it can be more efficient.

这不一定会在小型查询中产生巨大的性能，但是当表变大时，它会更有效率。

回答by Peter Bartlett

For MySQL 5.7, it definitely can speed up queries involving multiple joins amazingly well!

对于 MySQL 5.7，它绝对可以非常好地加速涉及多个连接的查询！

I used 'explain' to understand my query and found that I was joining 4-5 tables - where no keys were used at all. I did nothing but add a foreign key to these tables and the result was a 90% reduction in loadtime. Queries that took >5s now take 500ms or less.

我使用“解释”来理解我的查询，发现我加入了 4-5 个表 - 根本没有使用任何键。我只为这些表添加了一个外键，结果是加载时间减少了 90%。需要超过 5 秒的查询现在需要 500 毫秒或更短时间。

That is an ENORMOUS improvement!

这是一个巨大的改进！

AND, as others have mentioned, you get the added bonus of ensuring relational integrity.

而且，正如其他人所提到的，您可以获得确保关系完整性的额外好处。

Beyond this, ensuring referential integrity has it's own performance benefits as well. It has the second order effect of ensuring that the tables that have the foreign key are 'up to date' with the foreign table. Say you have a users table and a comments table, and you're doing some statistics on the comments table. Probably if you hard delete the user, you don't want their comments anymore, either.

除此之外，确保参照完整性也有其自身的性能优势。它具有确保具有外键的表与外部表“最新”的二阶效果。假设您有一个用户表和一个评论表，并且您正在对评论表进行一些统计。可能如果您硬删除用户，您也不再需要他们的评论。

回答by Pankaj Khairnar

Adding a foreign key in table will not improve the performance, simply saying if you are inserting a record in a ProductCategories table database will try to find the foreign key column has a value which exist in a products table's primary key value, this look up, operation is overhead on your database every time you add a new entry in ProductCategories table. So by adding a foreign key will not improve your database performance but it will take care about the integrity of your database. Yes it will improve the performance of you db if you are checking integrity using foreign key instead of running many queries for checking the record is exist in database in your program.

在表中添加外键不会提高性能，简单地说，如果您在 ProductCategories 表数据库中插入一条记录，将尝试查找外键列的值存在于产品表的主键值中，这样查找，每次在 ProductCategories 表中添加新条目时，操作都会对数据库产生开销。因此，添加外键不会提高您的数据库性能，但会注意数据库的完整性。是的，如果您使用外键检查完整性而不是运行许多查询来检查程序中数据库中是否存在记录，它将提高您的数据库性能。

回答by LucasF

As of SQL Server 2008 foreign keys can influence performance by influencing the way the database engine chooses to optimise the query. Refer to Star Join Heuristics in the following article: https://technet.microsoft.com/en-us/library/2008.04.dwperformance.aspx

从 SQL Server 2008 开始，外键可以通过影响数据库引擎选择优化查询的方式来影响性能。请参阅以下文章中的 Star Join Heuristics：https: //technet.microsoft.com/en-us/library/2008.04.dwperformance.aspx

回答by Shamik

I do not know much about SQL server, but in case of Oracle, having a foreign key column reduces the performance of data-loading. That is because database needs to check the data integrity for each insert. And yes, as it is already mentioned, having an index on foreign key column is a good practice.

我不太了解 SQL 服务器，但在 Oracle 的情况下，具有外键列会降低数据加载的性能。那是因为数据库需要检查每次插入的数据完整性。是的，正如已经提到的，在外键列上建立索引是一种很好的做法。