查询优化(一:Bookmark Lookup)

时间:2021-12-12 04:07:51

--优化学习()

 

我现有一表Orders,其中包含OrderId,UserId,CreateDate,TotalMoney,OrderType五个字段,

 

目前没有主键和其他索引

 

如今我想查询出在指定某个日期的订单数量,并返回OrderId,UserId,TotalMoney三字段,具体

 

查询语句如下:

 

select

   OrderId,

   UserId,

   TotalMoney

from

   Orders

where CreateDate='2012-12-17 14:59:53.463'

 

 

这个时候我执行以下查询语句,观察一下执行计划:

 查询优化(一:Bookmark Lookup)

                               -1

 


此时执行计划显示表扫描.分析表的数据存储结构,该表无主键和索引,数据存放形式

 

为堆存储,在查询的时候会读取表的每一行,CreateDate中评估此谓词,为真则返回

 

此行数据,输出列OrderId,UserId,TotalMoney

 

在这个查询中,扫描表操作涉及到该表的所有数据行,不管该行数据是否满足条件,所以

 

扫描的开销是与表的数据量成正比的.如果在表的数据量很少的时候或者满足where

 

谓词的数据行比例较大时,这样的执行计划是有效的,但是如果表的数据量大,且符合条

 

件的数据比例很小,那么这样的扫描显然是会涉及到很多不必要的数据页与行,同时造成

 

很多不必要的I/O开销

 

 

这时,我首先对表Orders加上一个聚集索引

 

create clusteredindexOrders_OrderId_idxonOrders(OrderId)

go

 

现在表的数据存放是按照B-Tree结构存储,现在执行该查询语句,查看执行计划:

 

 查询优化(一:Bookmark Lookup)


                                      图-2

 

这个时候观察执行计划,我们可以清晰地发现之前执行计划中出现的表扫描消失,取而代之的是

 

聚集索引扫描,那么这两者之间有什么区别?

 

当表不存在聚集索引的时候,是堆表.当建立聚集索引后,表是以B-Tree形式存储

 

而我在添加主键的时候,主键字段默认聚集索引,这个时候会默认创建统计信息,系统表sys.stats

 

中也会出现.这个时候优化器会选择聚集索引扫描,而不是之前的表扫描.但从I/O开销上来说,并没有

 

带来优化.这是由于聚焦索引和数据存储在一起,决定表数据的物理存储顺序,一个表只能有一个聚

 

集索引,其叶子结点是数据行,聚集索引扫描的是索引页,而索引页存放整个表(整个表的一个副本),

 

通过聚集索引找到一条记录的时候,这条记录相关的列的值也可以直接取出来,而表扫描则是扫描RID,

 

也是同数据存放在一起的,扫描到每一行的时候,也可以直接取出该行数据的值,两者在开销上并没有太

 

大的区别

 

 

现在我在字段CreateDate字段上创建一个非聚集索引:

 

create nonclusteredindexOrders_CreateDate_idxonOrders(CreateDate)

go

 

这个时候的执行计划如图

 

查询优化(一:Bookmark Lookup)

                                            图-3

 

 

从该执行计划中发现,之前的聚集索引扫描已经消失,取而代之的是费聚集索引查找和检查找两部分,并且

 

分别占到总开销的%左右

 

如下图:

 

查询优化(一:Bookmark Lookup)

                                    图-4


查询优化(一:Bookmark Lookup)


                                  图-5

 

那么很多人可能在这个时候误认为这已经是最优的执行计划了但其实不然我们分析可以看出-4

 

和图-5我们可以看出索引查找这一部分只输出了OrderId这一个字段而检查找是用与输出UserId

 

TotalMoney两个字段造成这个执行计划出现的原因是:

 

非聚集索引单独存储,如果查询的结果引用了非聚焦索引不包括的那些列,那么非聚焦索引还需要通过行

 

定位器去表中取该记录对应的列的数据,这里面就有一个再次查找的问题而这个时候我们的查询语句

 

返回了三个字段第一个为聚集索引所在的字段,第二个和第三个字段上没有任何索引那么为什么返回

 

的聚集索引字段没有出现二次查找的情况呢这是由于在OrderId字段简历聚集索引后,在这个表创建的

 

其它所有的非聚集索引都已经覆盖了聚集索引字段,具体原则如下图:

 

查询优化(一:Bookmark Lookup)

                                               图-6

 

这个时候为了返回两个非索引覆盖字段优化器就会通过二次查找返回这两个字段而二次查找则根据

 

之前建立的聚集索引来实现于是出现了键查找这一迭代器

 

此时我们如果让索引覆盖这两个字段就可以避免出现键查找的出现

 

 

drop indexOrders_CreateDate_idxonOrders

create nonclusteredindexOrders_CreateDate_idxonOrders(CreateDate)include(UserId,TotalMoney)

go

 

 

这个时候我们再来查看执行计划:

 

查询优化(一:Bookmark Lookup)

                              图-7

 

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

 查询优化(一:Bookmark Lookup)

                                     图-8

 

 

在这个例子中,如果我们不创建聚集索引那么又是会什么样呢

 

我删掉之前的索引,然后重新创建一个只包含CreateDate字段的非聚集索引

 

drop indexOrders_CreateDate_idxonOrders

drop indexOrders_OrderId_idxonOrders

create nonclusteredindexOrders_CreateDate_idxonOrders(CreateDate)

go

 

接下来看执行计划:

 查询优化(一:Bookmark Lookup)

                       图-9

 

 

我们比较之前的图-3可以发现,两者的区别在于现在是RID查找之前是键查找其实这样的

 

差别的出现是由于现在的表是堆表二次查找需要通过行定位符去查找索引不包含的那两列,

 

而之前存在聚集索引优化器选择了聚集索引键查找返回了那两列数据

 

这个时候我们也只需修改非聚集索引让它包含这两个字段即可达到目的

 

 

drop indexOrders_CreateDate_idxonOrders

create nonclusteredindexOrders_CreateDate_idxonOrders(CreateDate)include(OrderId,UserId,TotalMoney)

go

 

 

执行计划如图:

 查询优化(一:Bookmark Lookup)

                               图-10

 

 

 

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

 

不管是RIDSEEK还是CLUSTEREDSEEK,一般情况下我们都可以选择让索引包含这些返回字段的

 

方法优化他们从而避免二次查找的出现,力求I/O开销达到最优