澳门金沙vip 21

【澳门金沙vip】SELECT TOP 1 比不加TOP 1 慢的原因分析以及SELECT TOP 1语句执行计划预估原理

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

 

如果在的查询里并不包含同样的WHERE子句,查询优化期在执行计划里访问的索引的统计信息还是原来默认的。如果你对刚才的查询启用9204的跟踪标记,你就可以看到在基数预估时,那个统计信息被查询优化器使用:

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

  那么这个4行是怎么计算出来的呢?

查询优化器使用称为统计信息对象作为基数预估。每次当你创建一个索引,SQL
Server在下面也会创建一个统计对象。这个对象描述了那个索引的数据分布。另外,在查询执行时,SQL
Server也能创建统计信息对象,在必须的时候(自动创建统计信息)。数据分布本身(复合索引键的第一列)被描述为所谓的直方图(Histogram)

    

 

感谢关注!

澳门金沙vip 1

    计算一下结果吧,

当你用Austria参数值执行同样的查询,SQL
Server又一次估计行数是501,但是查询本身值返回1行……当其他运算符使用这些估计做运算时,这个行为在执行计划里会有巨大的副作用。例如,Sort和Hash运算符根据这些估计作为内存授予需要的大小。如果低估,你的查询会涌向TempDb,如果高估,你就在浪费内存,当你有大量的并发查询是,就会导致竞争问题(查询内存的最大数量是有资源管理器限制的……)

      原因分析:

  

 1 -- Create a new database
 2 CREATE DATABASE FilteredStatistics
 3 GO
 4  
 5 -- Use it
 6 USE FilteredStatistics
 7 GO
 8  
 9 -- Create a new table
10 CREATE TABLE Country
11 (
12 ID INT PRIMARY KEY, 
13 Name VARCHAR(100)
14 ) 
15 GO
16  
17 -- Create a new table
18 CREATE TABLE Orders
19 (
20 ID INT, 
21 SalesAmount DECIMAL(18, 2)
22 ) 
23 GO

     澳门金沙vip 2

     澳门金沙vip 3

你可以使用过滤统计信息来帮助这些特殊场景。这个会给SQL
Server关于数据本身分布的更多信息,也会在基数预估里得到帮助。对于那个特殊场景,我创建2个不同的过滤统计信息,对于每个国家我都创建各自的过滤统计信息对象: 

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

     然后再看统计信息中Status2=88 的分布行数,3996行

 1 SELECT SalesAmount FROM Country
 2 INNER JOIN Orders ON Country.ID = Orders.ID
 3 WHERE Name = 'UK'
 4 OPTION
 5 (
 6 RECOMPILE,-- Used to see the Statistics Output
 7     QUERYTRACEON 3604,-- Redirects the output to SSMS
 8     QUERYTRACEON 9204-- Returns the Statistics that were used during Cardinality Estimation ("Stats loaded")
 9 )
10 GO

    此时复合条件(BusinessStatus=0)为一开始的5000行,大于上述阈值
     
如果此时将查询条件列和排序列做成一个复合索引,就可以避免这种情况,
    目的是走这个索引之后,找到的第一条复合条件的数据一定是拍序列上最小的,并且不会因为找多而再次排序浪费CPU时间
    比如 create index ix_indexName on
TableName(查询字段列,排序字段列),且复合索引的顺序不能改变,自己结合B树索引的结构想清楚为什么
    具体原因,就不多说了,非要说的话,合理的索引就是让优化器更加清楚地弄清楚数据分布,可以做出更加明智的选择。

  第一,上述示例是用两个字段查询的,为什么不拿三个字段做演示说明? 

希望这篇文章给你过滤统计信息的很好概述,对于给出的查询,你知道如何使用过滤统计信息帮助SQL
Server提高基数预估。

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

统计信息的作用

 1 SELECT SalesAmount FROM Country
 2 INNER JOIN Orders ON Country.ID = Orders.ID
 3 WHERE Name = 'Austria'
 4 OPTION
 5 (
 6     RECOMPILE,-- Used to see the Statistics Output
 7     QUERYTRACEON 3604,-- Redirects the output to SSMS
 8     QUERYTRACEON 9204 -- Returns the Statistics that were used during Cardinality Estimation ("Stats loaded")
 9 )
10 GO

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

 

 1 -- Insert a few records into the Lookup Table
 2 INSERT INTO Country VALUES(0, 'Austria') 
 3 INSERT INTO Country VALUES(1, 'UK')
 4 INSERT INTO Country VALUES(2, 'France') 
 5 GO
 6  
 7 -- Insert uneven distributed order data
 8 INSERT INTO Orders VALUES(0, 0)
 9  
10 DECLARE @i INT = 1 
11  
12 WHILE @i <= 1000
13 BEGIN 
14 INSERT INTO Orders VALUES (1, @i) 
15 SET @i += 1
16 END
17 GO

    澳门金沙vip 4

    首选,做一个同样的测试,利用两个变量查询的查询条件做查询,看看SQL Server
2014预估的算法有什么变化。

点击工具栏的澳门金沙vip 5显示包含实际的执行计划。我们来执行下列的查询:

 

  

在复合索引键里其他列,SQL
Server在统计信息对象里用所谓的密度向量(Density
Vector)
来保存,它是复合索引键唯一值是如何的情况描述(彼此结合在一起)。例如在某列里有3个不同值,那列的密度向量是0.33333(1/3)。

本文出处: 

 

澳门金沙vip 6

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

  照这么计算,其余两个字段的选择度分别是1/250=0.004 和1/50=0.02,分别如下截图的
All density。

澳门金沙vip 7

 

  个人认为,(控制在一定范围之内的)估多的情况下可以通过获取更多的系统资源来提升SQL的执行效率,
  正常情况下也不会说是跟实际值差的太离谱造成资源的浪费。
  当然也有特殊情况,那就另当别论

 当你在你表上上创建了过滤统计信息时,你也要注意维护。从整个表本身——如果有20%的数据改变时,SQL
Server会自动更新统计信息!!! 假设你有10000行的表,你在表的子集上创建了过滤统计信息,就定子集行数是500条。在这个情况下,当指定列有2000行改变时,SQL
Server会更新过滤统计信息对象。因此你要更新过滤统计信息对象里4倍的数据,才会使统计信息失效然后它被更新(在过滤统计信息区间外,没有数据发生改变)。这是很糟糕的情况,当你使用过滤统计信息时,要记住这个。

澳门金沙vip 8

      再看Status2=88的数据分布情况

 我们在表上建立相应的索引:

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

 

 从上面代码可以看到,你用WHERE子句限制表数据的子集,那会通过新的过滤统计信息对象来描述这些数据。但也只有的你的查询也包含这个where条件,查询优化器才可以只用这个新的统计信息对象,就像这样:

    那么为什么加了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索引查找的方式,至此,原因大概是这样的。

     澳门金沙vip 9

SQL Server
估计行数是501,聚集索引查找运算符的实际行数是1000。SQL
Server这里使用idx_ID_SalesAmount统计信息对象的密度向量来做那个估计:密度向量是0.5(在那列我们只有2个不同值),因此估计行数是501(1001
* 0.5)。

下面开始测试:

   补充测试2:

 现在当你重新执行查询时,最后你会看到基数预估是正确的:

 

  

参考文章:

https://www.sqlpassion.at/archive/2013/10/29/fixing-cardinality-estimation-errors-with-filtered-statistics/

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

    澳门金沙vip 10

直方图最痛苦之一就是最大只有200的步长。步长是对于你所给定列数据一部分的数据分布情况描述。你的表变得越大,你的直方图就越不准确,因为你有最大200的步长(直方图必须尽可能紧凑,它必须复核8kb的页)。

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

统计信息是什么

以过滤统计信息的简单介绍为基础,我想给你通过实例展示下,过滤统计信息是如何提高执行计划质量的。 

总结:

  微软为什么在SQL Server
2014中,对非相关且未超出统计信息范围的预估行数算法做这么一个变化,
  因为PN的值是小于1的
  预估行数的计算方法从p0*p1*p2*p3……*RowCount变化为P0*P11/2
* P21/4 * P31/8……*
RowCount,显然是增加了预估行数的大小,
  同时本文未提及的另外一种情况:对于超出统计信息范围的情况下,新的预估方法也增加预估行数的大小,
  从整体上看,算法是倾向于”估多不估少”的,有这么一个改变
  至于为什么要做出这个改变?
  如果经常做SQL优化的就会发现,不少问题都是少估了预期的数据行数(因为种种原因吧,这里暂时不讨论为什么少估),
  造成执行SQL时分配的资源不够,从而拖慢了SQL的执行效率
  一个非常典型的问题就是,预估的数据比实际的数据行数小,造成比如内存授予的不够大,以及实际运算过程中采用不合理的执行计划

查询本身也会编译(因为RECOMPLIE查询提示,即使查询计划已被缓存),因此在SSMS的消息窗,你就可以看到拿个统计信息被用做基数预估。

    澳门金沙vip 11

     

最后往2个表里插入初始数据: 

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

       因为即便是创建了单列上的索引,执行计划变了,但是统计信息还是非相关的,也就是一个统计信息只描述一列字段的分布情况。

1 -- Fix the problem by creating Filtered Statistics Objects
2 CREATE STATISTICS Country_UK ON Country(ID) 
3 WHERE Name = 'UK'
4  
5 CREATE STATISTICS Country_Austria ON Country(ID) 
6 WHERE Name = 'Austria' 
7 GO

 

    

从执行计划里可以看到,基数预估出现了大问题。

SELECT TOP 1 比不加 TOP 1慢

     澳门金沙vip 12

1 SELECT SalesAmount FROM Country
2 INNER JOIN Orders ON Country.ID = Orders.ID
3 WHERE Name = 'Austria'
4 GO

  澳门金沙vip 13

  

1 -- Update the Statistics on both tables
2 UPDATE STATISTICS Country WITH FULLSCAN 
3 UPDATE STATISTICS Orders WITH FULLSCAN 
4 GO

    分析两者的执行计划:

 

从SQL Server 2008开始,SQL
Server支持所谓的过滤统计信息(Filtered
Statistics)
(和过滤索引对应)。使用过滤统计信息,你可以为数据的子集创建统计信息对象。对于那个数据子集,你也会有直方图和密度向量。如果在你的数据里有极端值,你可以对那个范围的数据创建过滤统计信息对象,当那个范围的数据被查询时,就可以让查询优化器更好的估计返回的行数。因此使用过滤统计信息,你就提高了基数预估的准确性,SQL
Server就会给更好的执行计划性能。下面代码显示在SQL Server
2008及后续版本里如何创建过滤统计信息对象: 

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

    
 按照公式来计算预估行数,选择性按照整体计算出来的选择性来,同样也是吻合的。

基数预估是SQL
Server里一颗隐藏的宝石。一般而言,基数预估指的是,在查询编译期间,查询优化器尝试找出在执行计划里从各个运算符平均返回的行数。这个估计用来驱动计划本身生成并选择正确的计划运算符——例如像Nested
Loop, Merge Join,还是Hash
Join的物理连接。当这些估计错误时,查询优化器就会选择错误的计划运算符,相信我——你的查询就会非常非常非常慢!

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

 首选,不管是多少个字段查询,预估行数符合上述计算方式是没有问题的,
 但是如果通过上述公式计算出来的结果非常小,在少于1的情况下,SQL
Server显示预估为1行。
 按照上述计算方法,用三个字段做查询,
 预估行数=0.001*0.004*0.02*总行数(也即1000000)=
0.08<1,所以预估为1行。

 为了保证所有的统计信息都已经是最新的,我用全扫描更新了统计信息:

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

  澳门金沙vip 14 
     

1 CREATE STATISTICS Country_Austria ON Country(ID) 
2 WHERE Name = 'Austria' 
3 GO

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

 

1 -- Create a Non-Clustered Index
2 CREATE NONCLUSTERED INDEX idx_Name ON Country(Name) 
3 GO
4  
5 -- Create a Clustered Index
6 CREATE CLUSTERED INDEX idx_ID_SalesAmount ON Orders(ID, SalesAmount) 
7 GO

问题到这里才刚刚开始

       熟悉SQL Server的同学应该都知道,直接用变量查询的时候,SQL
Server编译的时候不知道具体的参数值,
       在不知道具体参数值的情况下,它是使用字段的选择性的时候是用到一般性(或者说是平均)的值,
       也就是统计信息中整体计算出来字段的选择性,也即All
density=0.001
       这里暂定认为数据分布是均匀的,也即每个值分布差别不大。
       但事实上每个值的分布的差别还有存在的,
       尤其是分布不均匀的时候,当然这个是另外一个非常大的话题了,这里暂不讨论。

 

  执行计划对数据行的预估

    澳门金沙vip 15

    澳门金沙vip 16

  澳门金沙vip 17

       澳门金沙vip 18 

    澳门金沙vip 19

 澳门金沙vip 20       

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

  要注意的是我这里有个前提,非相关的统计信息,不管是没有任何索引,还是是创建和单列上的索引,对应的统计信息,都属于非相关统计信息,
  如果创建复合索引(有人习惯叫组合索引),那么执行计划对于数据行的预估并不符合上述算法,具体算法我也不清楚。
  此种情况下,在SQL Server 2012和SQL Server
2014中预估算法也不一样,这个有机会再研究吧。

 

总结:

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

    还记得上面在 SQL Server
2012中同样的写法,同样的数据的预估的情况吧,刚才预估的是4行,现在怎么变成63.2456行了?
    预估行数的计算公式变了吗,当然变了,这正是本文要说的重点。
    那么SQL Server 2014中是怎么预估的呢?公式是这么来的:预估行数 =
P0*P11/2  *
P21/4 * P31/8……*
RowCount 
    
 那么来根据此计算方式来计算预估行数的问题:预估行数=0.001*0.0041/2*1000000
= ?
    这里我就不做开方运算了,拿来主义,直接用SQL
Server来算拉倒了,SQL Server给我们提供了一个开方函数(SQRT),真JB好用。

 

统计信息的分类

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

      澳门金沙vip 21

 

  

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