MyException - 我的异常网
当前位置:我的异常网» Sql Server » SELECT TOP 一 比不加TOP 1 慢的原因分析以及SELECT

SELECT TOP 一 比不加TOP 1 慢的原因分析以及SELECT TOP 1语句执行计划预估原理

www.MyException.Cn  网友分享于:2013-12-03  浏览:0次
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 = 0 WHILE @i<24*60*365*5 BEGIN 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+1 END

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

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

  

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

CREATE INDEX idx_BusinessStatus ON TestTOP(BusinessStatus)

CREATE INDEX idx_BusinessId on TestTOP(BusinessId)

 

下面开始测试:

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

     为了证明上述推断,关于TOP的预估,我再补充一个小例子,希望各位看官能明白

    当符合条件的数据(BusinessStatus=0)为15000行的时候,我们看看TOP 1与TOP 2,以及继续增加TOP N的值得预估的行数,就大概明白了

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

TOP 1 的预估1052.2 = 1 * RowCount/15000

    TOP 2的预估行数 2014.4 = 2 * RowCount/15000

    

     TOP 14 的预估行数 2014.4 = 14 * RowCount/15000

     

 

    为什么TOP 15开始峰回路转,执行计划也变成index seek了,打破 N * RowCount/15000这个规律??请自行思考

    优化器会根据预估返回行数,因为TOP 15的时候,预估行数 =15 * RowCount/15000 =15783.0 >15000 ,

    优化器会回头选择一种他自己的预估方式较低的方式执行,选择一个它认为代价较小(预估行数较小)的执行方式.也即idx_BusinessStatus索引的Index Seek

  

 

 

什么情况下才会发生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慢的场景,导致的原因分析以及解决办法。
    事实上为了简明期间,还有非常多有意思的问题尚未展开,怕是写的越多,本文的主题就凸显不出来,有机会再对此尚未展开的问题继续进行分析。
    补充一点:事实上真要是测试的话,任何一点点小小的改变,
    比如查询语句中BusinessId排序改为DESC,甚至没有BusinessId上的索引,或者聚集索引建立在其他列上
    都可以避免TOP 1比不加TOP 1慢的问题,这里的目的是为了重现TOP 1比不加TOP 1慢的现象条件和原因,以及不改变外因的情况下如何解决这一问题
    谢谢。

4楼潇湘隐者
这个你参考一下宋大神写的那一篇 http://www.cnblogs.com/CareySon/p/5148943.html 就能理解得很透彻了!
Re: MSSQL123
@潇湘隐者,谢谢,看这篇大作我之前看过了,跟我这里写的还是稍微有些不一样的,还是那句话,任何一点点(查询语句)改变都会是问题变得很不一样。
3楼我叫So
论DBA的重要性!支持!
Re: MSSQL123
@我叫So,感谢支持,俺目前还是处于新手入门阶段,还有很多东西不懂
2楼金色海洋(jyk)阳光男孩
把这两个索引去掉,,CREATE INDEX idx_BusinessStatus ON TestTOP(BusinessStatus),,CREATE INDEX idx_BusinessId on TestTOP(BusinessId),,换成 ,,CREATE INDEX idx_BusinessStatus ON TestTOP(BusinessStatus,BusinessId),,试试。,,(不知道语法对不对,就是两个字段联合起来做非聚集索引),,另外问个事,id和 BusinessId的值 的排序是不是相同的?,,如果是的话,还是直接用主键做排序字段靠谱点。,,,在另外,还是那两个索引,排序字段变成 order by BusinessStatus,BusinessId 会怎么样呢?
Re: MSSQL123
@金色海洋(jyk)阳光男孩,我明白你的意思,,我也测过,如果没有CREATE INDEX idx_BusinessId on TestTOP(BusinessId)这个索引,也不会引发本文中提到的问题,演示中的情况也不会发生,,说白了,对于类似idx_BusinessId 这种索引,但实际中,不能一删了之,因为这里可能妨害了当前的查询,但是别的查询还要用到类似于idx_BusinessId这样的索引,,另外问个事,id和 BusinessId的值 的排序是不是相同的?,,是的,,如果是的话,还是直接用主键做排序字段靠谱点。,,是的,这么做在这个case中肯定没问题,,在另外,还是那两个索引,排序字段变成 order by BusinessStatus,BusinessId 会怎么样呢?,,实际上如果我测试建立的那两个非聚集索引不做任何调整,top 1查询语句的order by换成倒叙,也会很快,加TOP 1不会慢,,原理就是按照BusinessId 反向排序取第一条,TOP 1查询也不会走BusinessId 这个列的索引扫描,而是走BusinessStatus列的索引查询,这也是我没有展开这个问题的原因。,,我只是重现这种现象以及说明这种现象产生的原因。,,实际业务中,不太可能改变业务(原本是BusinessId 正序排序改为倒序排序),或者改变原始表上某些聚集索引(直接把原始聚集索引删了换成别的列做聚集索引),只有以最小的影响和代码来解决这个问题。,,,
1楼p712long
非常棒,好文章,有数据,有例子,事实胜于雄辩

文章评论

程序员的一天:一寸光阴一寸金
程序员的一天:一寸光阴一寸金
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
Google伦敦新总部 犹如星级庄园
Google伦敦新总部 犹如星级庄园
那些争议最大的编程观点
那些争议最大的编程观点
一个程序员的时间管理
一个程序员的时间管理
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
编程语言是女人
编程语言是女人
那些性感的让人尖叫的程序员
那些性感的让人尖叫的程序员
60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
如何区分一个程序员是“老手“还是“新手“?
如何区分一个程序员是“老手“还是“新手“?
程序员和编码员之间的区别
程序员和编码员之间的区别
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
程序员应该关注的一些事儿
程序员应该关注的一些事儿
程序员都该阅读的书
程序员都该阅读的书
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
为什么程序员都是夜猫子
为什么程序员都是夜猫子
每天工作4小时的程序员
每天工作4小时的程序员
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
中美印日四国程序员比较
中美印日四国程序员比较
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
代码女神横空出世
代码女神横空出世
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
鲜为人知的编程真相
鲜为人知的编程真相
程序员最害怕的5件事 你中招了吗?
程序员最害怕的5件事 你中招了吗?
程序员的鄙视链
程序员的鄙视链
我是如何打败拖延症的
我是如何打败拖延症的
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
漫画:程序员的工作
漫画:程序员的工作
程序员眼里IE浏览器是什么样的
程序员眼里IE浏览器是什么样的
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
Java程序员必看电影
Java程序员必看电影
做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
程序员必看的十大电影
程序员必看的十大电影
十大编程算法助程序员走上高手之路
十大编程算法助程序员走上高手之路
我的丈夫是个程序员
我的丈夫是个程序员
10个调试和排错的小建议
10个调试和排错的小建议
总结2014中国互联网十大段子
总结2014中国互联网十大段子
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
Java 与 .NET 的平台发展之争
Java 与 .NET 的平台发展之争
如何成为一名黑客
如何成为一名黑客
“懒”出效率是程序员的美德
“懒”出效率是程序员的美德
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有