--优化学习(一)
我现有一表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
从该执行计划中发现,之前的聚集索引扫描已经消失,取而代之的是费聚集索引查找和检查找两部分,并且
分别占到总开销的%左右
如下图:
图-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开销达到最优。
文章来源：MSSQL数据库查询优化(一)