外键上建立索引的好处
在主从表设计中，常常使用外键在这两个表之间建立关联。当从主表中删除一行数据时，SQL Server便会检查从表中是否有相同外键的行存在。如果从表没有包含外键列的索引，SQL SERVER需要扫描整个从表。可以想象：从表越大，删除的时间越长。更新主表时的情况也是一样。
|
INSERT |
DELETE |
UPDATE |
主表（被引用表） |
不作检查 |
检查从表是否有该键值的引用 |
检查从表是否有旧键值的引用 |
因为主表有唯一聚集或非聚集的索引，所以在从表中插入或修改时，能利用主表的索引快速定位。
下面来举例说明：
先创建两个表：主表ta(col1, col2)，从表tb(col3, col4, col5),tb表的col4列引用ta表的col1列。先不要再col4上创建索引。
|
if object_id('tb')isnotnull drop tabletb go if object_id('ta')isnotnull drop tableta go create tableta(col1intprimarykey,col2char(2000)) go createtabletb(col3intprimarykey,col4int,col5char(2000) constraint fk1 foreign key (col4)referencesta(col1)ondeletecascade ) go declare @i int set @i= 1 while @i<=30000 begin insert into ta values(@i,@i) set @i=@i+ 1 end; go declare@iint set @i= 1 while @i<=30000 begin insert into tb values(@i,@i,@i) set @i=@i+ 1 end; |
执行ta上的删除动作，看看计划与IO：
|
dbcc dropcleanbuffers go dbcc freeproccache go set statisticsioon set statisticstimeon go delete ta where col1 = 20087 |
计划如下：
可以看出，由于没有col4上的索引，所以SQL SERVER只得在tb表上进行聚集索引扫描。IO如下：
|
SQL Server 执行时间: CPU 时间= 0 毫秒，占用时间= 1 毫秒。 SQL Server 分析和编译时间: CPU 时间= 0 毫秒，占用时间= 21 毫秒。 SQL Server 分析和编译时间: CPU 时间= 0 毫秒，占用时间= 1 毫秒。 表'tb'。扫描计数1，逻辑读取7529 次，物理读取3 次，预读7521 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。 表'Worktable'。扫描计数2，逻辑读取7 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。 表'ta'。扫描计数0，逻辑读取3 次，物理读取3 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
|
下面在表tb的col4上创建一个非聚集索引，同样执行上面的删除动作，再看看结果：
|
create nonclusteredindexnc1on tb(col4) go dbcc dropcleanbuffers go dbcc freeproccache go set statisticsioon set statisticstimeon go delete ta where col1 = 20088 |
果然，SQL SERVER使用了索引查找。IO结果如下：
|
SQL Server 执行时间: CPU 时间= 0 毫秒，占用时间= 1 毫秒。 SQL Server 分析和编译时间: CPU 时间= 0 毫秒，占用时间= 14 毫秒。 SQL Server 分析和编译时间: CPU 时间= 0 毫秒，占用时间= 1 毫秒。 表'tb'。扫描计数1，逻辑读取9 次，物理读取4 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。 表'Worktable'。扫描计数2，逻辑读取7 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。 表'ta'。扫描计数0，逻辑读取3 次，物理读取2 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。 |
对比两个IO结果，会发现在外键上建立索引大大节省了时间。
参考文献
1. http://www.sqlperformance.com/2012/11/t-sql-queries/benefits-indexing-foreign-keys
2. http://msdn.microsoft.com/zh-cn/library/ms175464(v=sql.105).aspx