每个 SQL Server 外键都应该有一个匹配的索引吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3650690/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Should every SQL Server foreign key have a matching index?
提问by GRGodoi
Is it good practice to create an index for every foreign key in a SQL Server database?
为 SQL Server 数据库中的每个外键创建索引是一种好习惯吗？
采纳答案by SQLMenace
Yes it's a good practice, see here: When did SQL Server stop putting indexes on Foreign Key columns?scroll down to the Are there any benefits to indexing foreign key columns?section
是的，这是一个很好的做法，请参阅此处：SQL Server 何时停止在外键列上放置索引？向下滚动到索引外键列有什么好处吗？部分
回答by onedaywhen
Every foreign key? No. Where the selectivity is low (i.e. many values are duplicated), an index may be more costly than a table scan. Also, in a high activity environment (much more insert/update/delete activity than querying) the cost of maintaining the indexes may affect the overall performance of the system.
每个外键？否。在选择性低的情况下（即许多值重复），索引可能比表扫描成本更高。此外，在高活动环境（插入/更新/删除活动比查询多得多）中，维护索引的成本可能会影响系统的整体性能。
回答by ItsMe
The reason for indexing a foreign key column is the same as the reason for indexing any other column: create an index if you are going to filter rows by the column.
索引外键列的原因与索引任何其他列的原因相同：如果要按列过滤行，则创建索引。
For example, if you have table [User] (ID int, Name varchar(50)) and table [UserAction] (UserID int, Action varchar(50)) you will most probably want to be able to find what actions a particular user did. For example, you are going to run the following query:
例如，如果您有表 [User] (ID int, Name varchar(50)) 和表 [UserAction] (UserID int, Action varchar(50))，您很可能希望能够找到特定用户的操作做过。例如，您将运行以下查询：
select ActionName from [UserAction] where UserID = @UserID
If you do not intend to filter rows by the column then there is no need to put an index on it. And even if you do it's worth it only if you have more than 20 - 30 rows.
如果您不打算按列过滤行，则无需在其上放置索引。即使你这样做，只有当你有超过 20 - 30 行时才值得。
回答by Serghei Belyi
From MSDN: FOREIGN KEY Constraints
来自 MSDN：外键约束
Creating an index on a foreign key is often useful for the following reasons:
由于以下原因，在外键上创建索引通常很有用：
- Changes to PRIMARY KEY constraints are checked with FOREIGN KEY constraints in related tables.
- Foreign key columns are frequently used in join criteria when the data from related tables is combined in queries by matching the column or columns in the FOREIGN KEY constraint of one table with the primary or unique key column or columns in the other table.
- 使用相关表中的 FOREIGN KEY 约束检查对 PRIMARY KEY 约束的更改。
- 通过将一个表的 FOREIGN KEY 约束中的一个或多个列与另一个表中的一个或多个主键列或唯一键列匹配，在查询中组合相关表中的数据时，外键列经常用于连接条件。
