外键上建立索引的好处

时间:2021-10-30 19:39:38

外键上建立索引的好处

 

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