外键上建立索引的好处
外键上建立索引的好处
在主从表设计中，常常使用外键在这两个表之间建立关联。当从主表中删除一行数据时，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
