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行,大于上述阈值
如果此时将查询条件列和排序列做成一个复合索引,就可以避免这种情况,
目的是走这个索引之后,找到的第一条复合条件的数据一定是拍序列上最小的,并且不会因为找多而再次排序浪费CPU时间
比如 create index ix_indexName on TableName(查询字段列,排序字段列),且复合索引的顺序不能改变,自己结合B树索引的结构想清楚为什么
具体原因,就不多说了,非要说的话,合理的索引就是让优化器更加清楚地弄清楚数据分布,可以做出更加明智的选择。
另外可以针对具体情况做filter索引,使得索引更加精确
当然也有其他办法,比如强制索引等,但是一旦加了强制索引就屏蔽掉优化器的作用了,如果没办法保证索引实在任何时候都是比较高效的情况下,不建议加强制索引。
总结:
本文分析了在某些特定的场景下,重现了SELCET TOP 1比不加TOP 1慢的场景,导致的原因分析以及解决办法。
事实上为了简明期间,还有非常多有意思的问题尚未展开,怕是写的越多,本文的主题就凸显不出来,有机会再对此尚未展开的问题继续进行分析。
补充一点:事实上真要是测试的话,任何一点点小小的改变,
比如查询语句中BusinessId排序改为DESC,甚至没有BusinessId上的索引,或者聚集索引建立在其他列上
都可以避免TOP 1比不加TOP 1慢的问题,这里的目的是为了重现TOP 1比不加TOP 1慢的现象条件和原因,以及不改变外因的情况下如何解决这一问题
谢谢。
posted on 2016-11-20 14:07 MSSQL123 阅读(23328) 评论(7) 编辑 收藏 举报