星空网 > 软件开发 > 数据库

Sql Server 聚集索引扫描 Scan Direction的两种方式

最近发现一个分页查询存储过程中的的一个SQL语句,当排序方式不同的时候,效率差别达到数十倍,让我感到非常吃惊

由此引发出来分页查询的情况下对大表做Clustered Scan的时候,

不同情况下会选择FORWARD 或者 BACKWARD差别,以及建立聚集索引时,选择索引列的排序方式的一些思考

 

废话不多,上代码

先建立一张测试表,在Col1上建立聚集索引,写入100W条数据

create table ClusteredIndexScanDirection(  Col1 int identity(1,1),  Col2 varchar(50),  Col3 varchar(50),  Col4 Datetime)create unique clustered index idx_Col1 on ClusteredIndexScanDirection(Col1 ASC) insert into ClusteredIndexScanDirection values (NEWID(),NEWID(),GETDATE()-RAND()*100)go 1000000

 

  先直观地看一下聚集索引扫描时候的FORWARD 和 BACKWARD

 

 

 

 FORWARD

  执行如下分页查询,当按照Col4符合2016-5-1和2016-5-15,并且Col1 正序排序的时候

  从执行计划看,Clustered Index Scan的Scan Direction的方式是BACKWARD

  Sql Server 聚集索引扫描 Scan Direction的两种方式

 BACKWARD

  执行如下分页查询,当按照Col4符合2016-5-1和2016-5-15,并且Col1 倒叙排序的时候

  从执行计划看,Clustered Index Scan的Scan Direction的方式是BACKWARD

Sql Server 聚集索引扫描 Scan Direction的两种方式

 

  那么性能上有么有差别?肯定有,如果没有,本文也就没有什么意义了

  如图是上述两种查询方式在我本机的测试结果,同样是前1000条数据,因为排序方式不同,其代价也是不同的

  或许你认为逻辑读,一个是100次,一个是97次,没多大差别啊,当然这里这是测试,在实际场景中,这个差别是非常非常大的

  Sql Server 聚集索引扫描 Scan Direction的两种方式

 

 

 

  对FORWARD和BACKWARD有一个直观的感受之后,来说说这两者的区别

  如果了解B树索引结构的话,应该知道聚集索引是以类似于B树结构的方式来组织的,既然是B树结构,

  那么下面这个图就不难理解了,

  在索引列按照某事方式排序的情况下,比如

  create unique clustered index idx_Col1 on ClusteredIndexScanDirection(Col1 ASC) 
  或者是
  create unique clustered index idx_Col1 on ClusteredIndexScanDirection(Col1 DESC)

  下面这张图分别是FORWARD和BACKWARD两种Scan direction的实现方式

 

  Sql Server 聚集索引扫描 Scan Direction的两种方式 

                FORWARD

 

Sql Server 聚集索引扫描 Scan Direction的两种方式

                    BACKWARD

  

    Sql Server究竟选中哪种方式,是FORWARD还是BACKWARD,是依赖于你的索引情况和查询情况的

    以我上面的查询为例

    如果是按照查询结果正序排序的方式查询

    

SELECT *FROM ClusteredIndexScanDirection WITH (NOLOCK) WHERE Col4 >= '2016-5-1'	AND Col4 <= '2016-5-15'  ORDER BY 1 ASC OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY 

  

    也就是要求查询结果的排序方式与聚集索引的排序方式一致,聚集索引是ASC的,Sql Server就会采用FORWARD的方式,

    也即是从左到右的Scan方式,找到满足1000条的数据后返回,查询终止

    

    如果是按照查询结果的倒序排序的方式查询

    

SELECT *FROM ClusteredIndexScanDirection WITH (NOLOCK) WHERE Col4 >= '2016-5-1'  AND Col4 <= '2016-5-15'  ORDER BY 1 ASC OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY 

  

    也就是要求查询结果的排序方式与聚集索引的排序方式不一致,聚集索引是ASC的,Sql Server就会采用BACKWARD的方式,

    也即是从右到左的Scan方式,找到满足1000条的数据后返回,查询终止

    

 

    

    现在就存在一个问题,如果聚集索引是按照ASC正序排列的,也就是说在聚集索引排序一定的情况下,

    聚集索引列和查询条件(CreateDate)上的时候都是递增的,也就是说,查询目标数据分布在B树的右边,

    (当然这么说不严谨,物理存储中并没有左右的概念,这些都是逻辑上的,并不是完全物理上的概念),

    实际业务中,差不多的意思就是查询最近N天的数据

    如果查询结果是按照聚集索引正序排序,

    Sql Server 采用FORWARD的方式,也即从左至右,那么这个查询就要经历B树种从左到右很大一部分数据扫描之后,才能找到所需要的数据

    

    如果查询结果是按照聚集索引倒叙排序,

    Sql Server 采用BACKWARD的方式,也即从右至左,那么这个查询直接从最右边开始Scan,很快就能找到符合条件的1000条数据。

    

    聚集索引是ASC或者DESC的方式,也会影响到这个查询,这些概念都是相对的,当然实际场景中,索引情况和查询条件可能更复杂,

    可见,一个查询的实现,是通过FORWARD还是BACKWARD,跟聚集索引的排序方式和查询结果的排序方式,以及查询条件都有关。

    Sql Server 选择FORWARD或者BACKWARD,本身都没有错,如果出现不同排序方式下性能差别非常大的时候,

    就要注意到是不是,聚集索引的方式与查询排序方式之间存在类似上述的问题。

    不管是FORWARD或者BACKWARD,避免让Scan整个表的大部分数据才找到符合条件的数据

      当然实际情况也比例子中复杂很多,还是那句话,具体情况具体分析。

    比如业务系统查询数据时,排序方式是固定的(比如你网购的订单信息,总是按照时间倒叙排列的),当然也不排除其他情况

    这就要求我们在创建聚集索引的时候,要考虑到查询的方式以及排序的方式,慎重地作出选择,

    而不是像我现在遇到的个别数据库中,在选择聚集索引的排序方式上,都是开发人员建立的,非常随意,

    比如在建聚集索引的时候不指定顺序,默认按照正序ASC,有人只要建索引,一定要指定成倒叙(DESC)的方式排序

    究竟是正序还是倒叙,要看你系统是怎么查询,以及查询结果是怎么排序的。

 

 总结:本文通过聚集索引Scan的两种方式,FORWARD和BACKWARD,粗浅第分析了表上的聚集索引的排序对查询时的影响,

    我们在选择聚集索引排序方式的时候,一定要考虑到对表的查询的排序结果是怎么样的,以便做出更加明智的选择。

    

 

 

    




原标题:Sql Server 聚集索引扫描 Scan Direction的两种方式

关键词:sql

sql
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。

Prime Day亚马逊定价系统出意外1.3 万美元的佳能镜头以 94.48美元卖出:https://www.ikjzd.com/articles/101675
关于2019年Prime Day的5个统计数据:https://www.ikjzd.com/articles/101677
亚马逊运营“失败之树”:开枝散叶6要素!:https://www.ikjzd.com/articles/101678
亚马逊运营中,如何建立自己的关键词词库?:https://www.ikjzd.com/articles/101682
你被Amazon Warehouse跟卖过么?:https://www.ikjzd.com/articles/101683
亚马逊涉收集第三方数据 欧盟将对其展开反垄断调查:https://www.ikjzd.com/articles/101684
长治婚庆女司仪和主持人:https://www.vstour.cn/a/366176.html
北京丰台区水上乐园哪家好玩?:https://www.vstour.cn/a/366177.html
相关文章
我的浏览记录
最新相关资讯
海外公司注册 | 跨境电商服务平台 | 深圳旅行社 | 东南亚物流