外键会提高查询性能吗? [英] Does Foreign Key improve query performance?

问题描述

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

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

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;

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

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?

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

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.

我的问题是:

1. FK除了有助于关系约束，还有其他用处吗?它会提高查询性能吗?

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

我应该在所有表中的所有 FK 列(喜欢的 Products.CategoryId)上创建索引吗?

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

推荐答案

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

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.

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