最近发现一个分页查询存储过程中的的一个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
BACKWARD
执行如下分页查询,当按照Col4符合2016-5-1和2016-5-15,并且Col1 倒叙排序的时候
从执行计划看,Clustered Index Scan的Scan Direction的方式是BACKWARD
那么性能上有么有差别?肯定有,如果没有,本文也就没有什么意义了
如图是上述两种查询方式在我本机的测试结果,同样是前1000条数据,因为排序方式不同,其代价也是不同的
或许你认为逻辑读,一个是100次,一个是97次,没多大差别啊,当然这里这是测试,在实际场景中,这个差别是非常非常大的
对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的实现方式
FORWARD
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