你的位置:首页 > 数据库

[数据库]SELECT TOP 1 比不加TOP 1 慢的原因分析以及SELECT TOP 1语句执行计划预估原理


 

本文出处:http://www.cnblogs.com/wy123/p/6082338.html 

 

  现实中遇到过到这么一种情况:
  在某些特殊场景下:进行查询的时候,加了TOP 1比不加TOP 1要慢(而且是慢很多)的情况,
  也就是说对于符合条件的某种的数据,查询1条(复合该条件)数据比查询所有(符合该条件)数据慢的情况,
  这种情况往往只有在某些特殊条件下会出现,那么,就有两个问题:为什么加了TOP 1 会比不加TOP 1慢?这种“特殊条件”是什么条件?
  本文将对此情况进行演示和原理分析,以及针对此种情况采用什么方法来解决。

 

按照一贯风格,先造一个测试环境:1000W+的数据
数据的特点为:
1,表中有一个状态列BusinessStatus ,这个列的分布为1,2,3,4,5
2,表中有一个 业务ID列BusinessId , BusinessId列是呈递增趋势

CREATE TABLE TestTOP(  Id        INT IDENTITY(1,1) primary key,  BusinessColumn  VARCHAR(50),  BusinessId    INT,  BusinessStatus  TINYINT,  CreateDate    DATETIME)GO
--5年的时间,一分钟六条数据的数据频率DECLARE @i int = 0WHILE @i<24*60*365*5BEGIN INSERT INTO TestTOP VALUES (NEWID(),@i,RAND()*5+1, DATEADD(SS,@i,DATEADD(YEAR,-5,GETDATE()))) INSERT INTO TestTOP VALUES (NEWID(),@i,RAND()*5+1, DATEADD(SS,@i,DATEADD(YEAR,-5,GETDATE()))) INSERT INTO TestTOP VALUES (NEWID(),@i,RAND()*5+1, DATEADD(SS,@i,DATEADD(YEAR,-5,GETDATE()))) INSERT INTO TestTOP VALUES (NEWID(),@i,RAND()*5+1, DATEADD(SS,@i,DATEADD(YEAR,-5,GETDATE()))) INSERT INTO TestTOP VALUES (NEWID(),@i,RAND()*5+1, DATEADD(SS,@i,DATEADD(YEAR,-5,GETDATE()))) INSERT INTO TestTOP VALUES (NEWID(),@i,RAND()*5+1, DATEADD(SS,@i,DATEADD(YEAR,-5,GETDATE()))) SET @i=@i+1END

另外,在此表中查询一小部分BusinessStatus=0的分布较少的数据,且分布在最大的BusinessId上,这里暂定为5000行,利用如下脚本生成 

DECLARE @i int = 15768000WHILE @i<15768000+5000BEGIN  INSERT INTO TestTOP VALUES (NEWID(),@i,0, DATEADD(SS,@i,GETDATE()))  SET @i=@i+1END

  

  现在这个测试环境已经搭建完成,现在创建两个非聚集索引,一个是在BusinessStatus上,一个是在BusinessId

CREATE INDEX idx_BusinessStatus ON TestTOP(BusinessStatus)CREATE INDEX idx_BusinessId on TestTOP(BusinessId)

 

下面开始测试:

  说明:1,以下测试,不用考虑缓存之类的因素,本机测试,内存也足够大,全部缓存这么点数据还是够的。也暂不分析IO具体值,粗看执行时间已经很明显了
     2,读者要对SQL Server索引结构,统计信息,执行计划,执行计划预估等知识有一定的认识,否则很多理论上的东西就看的云里雾里

 

SELECT TOP 1 比不加 TOP 1慢

 

  1,首先执行TOP 1 *的查询,耗时13秒

  

   2,然后执行不加TOP 1 *的查询,也即SELECT * ,如下,耗时0秒(当然不是0秒,意思是很快就可以完成这个查询)

    

 

  3,上面两个查询就可以重现第一个问题了,也就是说在当前这种查询条件下,TOP 1要比不加TOP 1慢很多  

    分析两者的执行计划:

    首先看加了 TOP 1 的执行计划:可以看到走的是idx_BusinessId的索引扫描

    

    接着看不加TOP 1 的执行计划:可以看到走的是idx_BusinessStatus这个索引的索引查找

    

 

      原因分析:

    那么为什么加了TOP 1就走BusinessId列上的索引扫描,不加TOP 1就走BusinessStatus上的索引扫描?
    因为在加了TOP 1之后,只要求返回一条数据,
    优化器认为(应该说是误认为)可以很快找到符合条件的那条记录,采用了idx_BusinessId列上的索引扫描
    由于数据的分布可知,符合BusinessStatus=0的BusinessId,是分布在BusinessId值最大的一小部分数据中,而BusinessId又是递增的,
    也就是说复合条件的数据是集中分布在idx_BusinessId索引树的一个很小的特定区域,
    采用的是与idx_BusinessId顺序一致的(ForWard顺序)索引扫描,有数据分布特点可知,一开始找到的绝大多数的BusinessId,都不是符合BusinessStatus=0的
    以至于几乎要扫描整个idx_BusinessId索引树才能找到符合BusinessStatus=0条件的数据,因此效率就会很低
    反观不加TOP 1的时候,因为是要找所有符合BusinessStatus=0的数据,优化器就索引采取了idx_BusinessStatus索引查找的方式,至此,原因大概是这样的。

 

问题到这里才刚刚开始

    如果说上述推断不足以说明问题,那么我们继续看在加了TOP 1的时候,执行计划是怎么预估的?

    继续观察加了TOP 1的时候的预估,发现此时走idx_BusinessId的索引扫描,预估行数为3154.6行,这个数字是怎么得到的?

    

 

    现在观察idx_BusinessStatus列上的统计信息,统计信息是100%取样的,先不考虑统计信息不准确的问题
    因为在加了TOP 1的时候,优化器认为复合条件的数据是平均分布在整个表中的,
    也就是说BusinessStatus=0的5000行数据是平均分布在15773000行数据中,查询条件又要求按照BusinessId正向排序,
    那么干脆走BusinessId列上的索引扫描,(误以为)平均找15773000/5000 行数据,就可以找到一条(TOP 1)符合条件的数据

    

     实际上是不是这样子呢?用总行数处于BusinessStatus=0的行数,与预估的值比较,都是3154.6呢?那么上面的推断也就是成立的

     

    这里查询加了TOP 1比不加TOP 1慢的根本原因就是如下:
      事实情况下是复合条件的数据分布是不均匀的,而优化器误以为符合条件的数据分布(在整张表中)是均匀的,
    正是因为有了这么一个矛盾,所以在加了TOP 1 的时候,优化器采用非最优化的方式造成的。

  

什么情况下才会发生TOP 1要比不加TOP 1慢(或者慢很多)

    事实上,类似结构的数据分布,并非所有的情况下都会出现TOP 1比不加TOP 1慢的情况
    那么什么时候TOP 1 可以选择正确的执行计划,而非采用低效的执行计划(排序列上的索引扫描)?
    当然是跟符合条件的数据BusinessStatus=0的数据行数有关,只有符合条件的数据(BusinessStatus=0)达到一定数量之后才会发生(TOP 1比不加TOP 1慢)
    上面说了,优化器误以为符合条件的数据(BusinessStatus=0)分布是均匀的,采用了排序列上的索引扫描的执行方式,
    即便是优化器误以为符合条件的数据(BusinessStatus=0)分布是均匀的,
    采用一开始的预估算法(平均分布:总行数/符合条件的数据行数)得到一个值,与符合条件的数据的行数本身对比,如果前者较大,就不会采用排序列上的索引扫描
    

    这里太拗口了也很难表达清楚,直接上例子吧。
    首先我改变符合条件(BusinessStatus=0)的数据的行数,让复合条件的数据变的少一些,
    这里删除原来的BusinessStatus=0的5000行数据,插入符合条件的数据为1000行,然后重建索引,试试看TOP 1 的效果

     

    (插入之后注意重建一下BusinessStatus上的索引,得到最准确的统计信息)

 

    此时再看SELECT TOP 1的查询方式,不会走排序列上的索引扫描了,走了查询条件列(idx_BusinessStatus)的索引查找,效率也上来了。

    

    事实上我这里说了这么多,一直在想引出一个问题,那么符合条件(BusinessStatus=0)这个数据分布多少,SELECT TOP 1不会引起问题(比不加TOP 1慢)?
    根据上述推论,这个值是动态的,大概如下:
    假如:X=总行数/符合条件数据行数,Y = 符合条件数据行数
    在统计信息完全准确的请下
    如果X>Y,也即:总行数/符合条件数据行数>符合条件数据行数,则会导致在SELECT TOP 1的时候使用排序列的索引扫描替代查询列的索引查找。
    那么这个阈值是多少?按照这种算法推论,理论上讲,就是符合条件的数据的行数等于总行数的平方根,数学推到也很简单,事实上下面也测试了。

    

    这个阈值在理论上是:3970行左右,

    

    那么插入符合条件的数据为3900的时候(小于阈值,也即小于总行数的平方根),SELECT TOP 1是可以走索引的,如下两个截图

     

     

     修改符合条件(BusinessStatus=0)的数据分布
     而符合条件的数据大于阈值(大于阈值,也即大于总行数的平方根,)的时候,SELECT TOP 1 就开始走排序列的索引扫描,效率开始变慢

         

    

    事实上导致SELECT TOP 1执行计划发生变化的这个阈值,具体的数值可以弄得更加精确,可以做到大于总行数的平方根一行,或者小于总行数的平方根一行。
    但实际上测试发现,这个误差在三行左右,也就是说阈值具体的值为总行数的平方根加减三条:POWER(TableRowCount,0.5)±3左右。

 

 

    当然也不是说“SELECT TOP 1的时候使用排序列的索引扫描替代查询列的索引查找”永远是低效的,
    想象一下,整个表中绝大多数数据是复合条件的(BusinessStatus=0)的条件下,SELECT TOP 1可以很快地找到符合条件的一条数据
     只是说,在某个阈值区间内,SQL Server查询引擎在生成执行计划的时候有一个盲区,此时查询引擎无法做出最明智的决定。

    实际条件是千变万化的,规律是可寻的,不能认死了规律而不考虑实际情况。

 

 

如何解决SELECT TOP 1比不加TOP 1慢的情况:

    上文中说了,查询加了TOP 1比不加TOP 1慢的根本原因就是如下:
      事实情况下是复合条件的数据分布是不均匀的,而优化器误以为符合条件的数据分布(在整张表中)是均匀的,
    正是因为有了这么一个矛盾,所以在加了TOP 1 的时候,优化器采用非最优化的方式造成的。

     

    此时复合条件(BusinessStatus=0)为一开始的5000行,大于上述阈值

      如果此时将查询条件列和排序列做成一个复合索引,就可以避免这种情况,
    具体原因,就不多说了,非要说的话,就是让优化器更加清楚地弄清楚数据分布,可以做出更加明智的选择。

    

     

    当然也有其他办法,比如强制索引等,但是一旦加了强制索引就屏蔽掉优化器的作用了,如果没办法保证索引实在任何时候都是比较高效的情况下,不建议加强制索引。

 

总结:

    本文分析了在某些特定的场景下,重现了SELCET TOP 1比不加TOP 1慢的场景,导致的原因分析以及解决办法。
    事实上为了简明期间,还有非常多有意思的问题尚未展开,怕是写的越多,本文的主题就凸显不出来,有机会再对此尚未展开的问题继续进行分析。
    谢谢。