MSSQL数据库查询优化(一)

我现有一表Orders,其中包含OrderId,UserId,CreateDate,TotalMoney,OrderType五个字段，目前没有主键和其他索引，如今我想查询出在指定某个日期的订单数量,并返回OrderId,UserId,TotalMoney三字段,具体查询语句如下:
select OrderId,UserId,TotalMoney
from Orders
where CreateDate='2012-12-17 14:59:53.463'
 
这个时候我执行以下查询语句,观察一下执行计划:
 
 图-1
此时执行计划显示表扫描.分析表的数据存储结构,该表无主键和索引,数据存放形式为堆存储,在查询的时候会读取表的每一行,在CreateDate中评估此谓词,为真则返回此行数据,输出列OrderId,UserId,TotalMoney
在这个查询中,扫描表操作涉及到该表的所有数据行,不管该行数据是否满足条件,所以扫描的开销是与表的数据量成正比的.如果在表的数据量很少的时候或者满足where后谓词的数据行比例较大时,这样的执行计划是有效的,但是如果表的数据量大,且符合条件的数据比例很小,那么这样的扫描显然是会涉及到很多不必要的数据页与行,同时造成很多不必要的I/O开销这时,我首先对表Orders加上一个聚集索引
 
create clustered index Orders_OrderId_idxon Orders(OrderId)
go
 
现在表的数据存放是按照B-Tree结构存储,现在执行该查询语句,查看执行计划:
 

  图-2
这个时候观察执行计划,我们可以清晰地发现之前执行计划中出现的表扫描消失,取而代之的是聚集索引扫描,那么这两者之间有什么区别?当表不存在聚集索引的时候,是堆表.当建立聚集索引后,表是以B-Tree形式存储而我在添加主键的时候,主键字段默认聚集索引,这个时候会默认创建统计信息, 系统表 sys.stats中也会出现.这个时候优化器会选择聚集索引扫描,而不是之前的表扫描.但从I/O开销上来说,并没有带来优化.这是由于聚焦索引和数据存储在一起, 决定表数据的物理存储顺序, 一个表只能有一个聚集索引, 其叶子结点是数据行,聚集索引扫描的是索引页,而索引页存放整个表(整个表的一个副本),通过聚集索引找到一条记录的时候, 这条记录相关的列的值也可以直接取出来,而表扫描则是扫描RID,也是同数据存放在一起的,扫描到每一行的时候,也可以直接取出该行数据的值,两者在开销上并没有太大的区别，现在我在字段CreateDate字段上创建一个非聚集索引:
 
create nonclustered index Orders_CreateDate_idxon Orders(CreateDate)
go
 
这个时候的执行计划如图
 

  图-3

从该执行计划中发现,之前的聚集索引扫描已经消失,取而代之的是非聚集索引查找和键查找两部分,并且分别占到总开销的50%左右如下图:
 

  图-4



  图-5

那么很多人可能在这个时候误认为这已经是最优的执行计划了，但其实不然。我们分析可以看出，图-4和图-5我们可以看出，索引查找这一部分只输出了OrderId这一个字段，而检查找是用与输出UserId和TotalMoney两个字段。造成这个执行计划出现的原因是:非聚集索引单独存储,如果查询的结果引用了非聚焦索引不包括的那些列,那么非聚焦索引还需要通过行定位器去表中取该记录对应的列的数据, 这里面就有一个再次查找的问题。而这个时候我们的查询语句返回了三个字段，第一个为聚集索引所在的字段,第二个和第三个字段上没有任何索引。那么为什么返回的聚集索引字段没有出现二次查找的情况呢？这是由于在OrderId字段建立聚集索引后,在这个表创建的其它所有的非聚集索引都已经覆盖了聚集索引字段,具体原则如下图:
 

 图-6

这个时候为了返回两个非索引覆盖字段，优化器就会通过二次查找返回这两个字段，而二次查找则根据之前建立的聚集索引来实现，于是出现了键查找这一迭代器。
此时，我们如果让索引覆盖这两个字段就可以避免出现键查找的出现。

drop index Orders_CreateDate_idx onOrders
create nonclustered index Orders_CreateDate_idxon Orders(CreateDate) include(UserId,TotalMoney)
go
 
这个时候我们再来查看执行计划:
 

  图-7

现在之前出现的部分已经被索引查找给取代,键查找部分的开销也已经被优化掉:
 
 图-8

在这个例子中,如果我们不创建聚集索引，那么又是会什么样呢？我删掉之前的索引,然后重新创建一个只包含CreateDate字段的非聚集索引
 
drop index Orders_CreateDate_idx onOrders
drop index Orders_OrderId_idx onOrders
create nonclustered index Orders_CreateDate_idxon Orders(CreateDate)
go
 
接下来看执行计划:
 
 图-9

我们比较之前的图-3可以发现,两者的区别在于现在是RID查找，之前是键查找。其实这样的差别的出现是由于现在的表是堆表，二次查找需要通过行定位符去查找索引不包含的那两列,而之前存在聚集索引，优化器选择了聚集索引键查找返回了那两列数据。这个时候我们也只需修改非聚集索引，让它包含这两个字段即可达到目的。

drop index Orders_CreateDate_idx onOrders
create nonclustered index Orders_CreateDate_idxon Orders(CreateDate) include(OrderId,UserId,TotalMoney)
go
 
执行计划如图:

 图-10

这里大家需要认真观察我每个索引的创建，特别注意存在聚集索引和不存在聚集索引的时候。

不管是RID SEEK还是CLUSTERED SEEK,一般情况下我们都可以选择让索引包含这些返回字段的方法优化他们，从而避免二次查找的出现,力求I/O开销达到最优。

 

原文链接：http://www.2cto.com/database/201304/205578.html