外键会自动创建索引吗? [英] Does a foreign key automatically create an index?

问题描述

有人告诉我，如果我为两个表设置外键，SQL Server 将在子表中创建类似于索引的内容.我很难相信这是真的，但找不到太多与此相关的内容.

I've been told that if I foreign key two tables, that SQL Server will create something akin to an index in the child table. I have a hard time believing this to be true, but can't find much out there related specifically to this.

我提出这个问题的真正原因是，我们在针对一个可能有 15 个相关表的表的删除语句中遇到了一些非常缓慢的响应时间.我问过我们的数据库人员，他说如果字段上有外键，那么它就像一个索引.您对此有何经验?我应该在所有外键字段上添加索引还是它们只是不必要的开销?

My real reason for asking this is because we're experiencing some very slow response time in a delete statement against a table that has probably 15 related tables. I've asked our database guy and he says that if there is a foreign key on the fields, then it acts like an index. What is your experience with this? Should I add indexes on all foreign key fields or are they just unnecessary overhead?

推荐答案

外键是一种约束，是两个表之间的关系 - 与索引本身无关.

A foreign key is a constraint, a relationship between two tables - that has nothing to do with an index per se.

但众所周知的事实是，索引属于任何外键关系的所有列是很有意义的，因为通过 FK 关系，您经常需要查找相关表并提取某些基于单个值或一系列值的行.

But it is a known fact that it makes a lot of sense to index all the columns that are part of any foreign key relationship, because through a FK-relationship, you'll often need to lookup a relating table and extract certain rows based on a single value or a range of values.

因此对 FK 中涉及的任何列进行索引是很有意义的，但 FK 本身并不是索引.

So it makes good sense to index any columns involved in a FK, but a FK per se is not an index.

查看 Kimberly Tripp 的优秀文章 "SQL Server 何时停止在外键列上放置索引?".

Check out Kimberly Tripp's excellent article "When did SQL Server stop putting indexes on Foreign Key columns?".

这篇关于外键会自动创建索引吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！