当前位置: 萬仟网 > IT编程>数据库>MSSQL > SQL Server中关于基数估计计算预估行数的一些方法探讨

SQL Server中关于基数估计计算预估行数的一些方法探讨

2017年09月07日 16:27  | 萬仟网IT编程  | 我要评论

关于sql server 2014中的基数估计,官方文档optimizing your query plans with the sql server 2014 cardinality estimator里有大量细节介绍,但是全部是英文,估计也没有几个人仔细阅读。那么sql server 2014中基数估计的预估行数到底是怎么计算的呢? 有哪一些规律呢?我们下面通过一些例子来初略了解一下,下面测试案例仅供参考,如有不足或肤浅的地方,敬请指教!

下面实验测试的环境主要为sql server 2014 sp2 (standard edition (64-bit)) 具体版本号为12.0.5000.0 ,如有在其它版本测试,后面会做具体说明。如下所示,我们先创建一个测试表并插入一些测试数据后,方便后面的测试工作。

if exists(select 1 from sys.objects where type='u' and name='test_estimated_row')
begin
 drop table test_estimated_row;
end
if not exists(select 1 from sys.objects where type='u' and name='test_estimated_row')
begin
  create table test_estimated_row
  (
   id  int,
   name varchar(24)
  )
end
go
declare @index int =1;
while @index <= 100
begin
 insert into test_estimated_row
 values(10, 'id is 10');
 set @index+=1;
end
go
declare @index int =1;
while @index <= 200
begin
 insert into test_estimated_row
 values(20, 'id is 20');
 set @index+=1;
end
go
declare @index int =1;
while @index <= 300
begin
 insert into test_estimated_row
 values(30, 'id is 30');
 set @index+=1;
end
go
declare @index int =1;
while @index <= 400
begin
 insert into test_estimated_row
 values(40, 'id is 40');
 set @index+=1;
end
go
declare @index int =1;
while @index <= 500
begin
 insert into test_estimated_row
 values(50, 'id is 50');
 set @index+=1;
end
go
create index ix_test_estimated_row_n1 on test_estimated_row(id);
go

我们来看看这个表的统计信息以及直方图内容。

dbcc show_statistics ('dbo.test_estimated_row','ix_test_estimated_row_n1');
go

sql server中有两种谓词:过滤谓词和连接谓词 。 我们先来看看过滤谓词的基数估计(预估行数),测试过程,如果要保持测试的公正性或不被其他因素影响,你可以使用下面的dbcc命令来排除干扰,如下例子所示:

dbcc freeproccache;  --从执行计划缓冲区删除所有缓存的执行计划
go
dbcc dropcleanbuffers;  --从缓冲池中删除所有缓存,清除缓冲区
go
select * from dbo.test_estimated_row where id = 10;
go

(注意,执行时请勾选包含实际执行计划按钮)如上所示,预估行数(estimated number of rows)为100,跟实际行数一致。当然你换其他值,例如20, 30, 40 ,50,其预估行数(estimated number of rows)跟实际行数都是正确的(sql server 2012中测试结果也相同)。那么如果我换一个不存在的值呢?预估行数会是多少呢?

select * from dbo.test_estimated_row where id = 4;
go

如上所示,预估行数(estimated number of rows)为1. 你换其他任何不存在的值,预估行数(estimated number of rows)都为1。这个跟沿用了老的基数评估:超出统计信息范围,那么老的基数评估就认为不存在,评估行数为1。很显然,对于没有超出统计信息范畴的,但是确实不存在的记录,其预估行数(estimated number of rows)也是1,这个基数估计确实是合理,也是正确的。那么如果我使用变量呢?这个预估行数(estimated number of rows)又会是什么值呢? 

declare @sid int = 11; --换任何值都可以
select * from dbo.test_estimated_row where id = @sid;
go

如上截图所示,实际执行计划的预估行数(estimated number of rows)是300, 那么如何计算来的呢? 其实你可以根据公式来计算, 如果不相信,你可以构造各种案例测试验证一下就能得到答案了. 

  [row sampled ]* [all density ] = 1500 * 0.2 = 300 也就是统计信息中抽样总行数*all density(统计信息对象中各列的每个前缀的密度) 

如果你加上option(recompile), 那么预估行数(estimated number of rows)又会变成1 

declare @sid int = 11; 
select * from dbo.test_estimated_row where id = @sid option(recompile)
go

如果你赋予@sid值为20,并加上option(recompile)时,那么预估行数(estimated number of rows)就会变成eq_rows的值了

declare @sid int = 20; 
select * from dbo.test_estimated_row where id = @sid option(recompile)
go

接下来,我们修改一下sql语句,将查询条件从等于符号改为大于符号,如下所示:

declare @sid int = 11;
select * from dbo.test_estimated_row where id > @sid;
go

如上所示,预估行数(estimated number of rows)变为了450,那么这个值是怎么计算得来的呢?

计算公式是: [row sampled ] * 0.3(30%)

1500 *0.3= 450

肯定会有人问,你怎么知道是 [row sampled ] * 0.3 呢? 不会是你逆推的吧。 不错,这里是一个推测(网上也有不少资料都确认是0.3,权且当做计算公式中的一个常量),而且也做了不少测试,确实就是30%。例如你将@sid赋值为41,预估行数(estimated number of rows)依然为450,如果你怀疑是缓存的执行计划缘故,你可以先清空缓存的执行计划,结果依然如此。根据我的测试,不管你给@sid赋予任何值,预估行数(estimated number of rows)全部为450

dbcc freeproccache;
go
dbcc dropcleanbuffers;
go
declare @sid int = 41;
select * from dbo.test_estimated_row where id > @sid;
go

 

如果sql加上 option(recompile) ,然后@sid赋予range_hi_key里的值,那么预估行数(estimated number of rows)又是如何计算的呢?

declare @sid int = 20;
select * from dbo.test_estimated_row where id > @sid option(recompile);
go


这个1200 是这样计算的,如下所示,大于20的range_hi_key有30 , 40, 50 ,他们对应的eq_rows值相加 300+ 400 + 500 =1200, 不信你可以测试一下,将@sid赋予30,那么预估行数(estimated number of rows)就会变成900. 

那么我们再修改一下sql查询语句,例如,我们要做一个区间查询,预估行数(estimated number of rows)又会有什么变化呢?

dbcc freeproccache;
go
dbcc dropcleanbuffers;
go
declare @min_value int = 20;
declare @max_value int = 50;
select * from dbo.test_estimated_row where id > @min_value and id < @max_value
go

如上所示,预估行数(estimated number of rows)为246.475 这个值怎么来的呢?其实它是这样计算的:

selectivity of most selective predicate * square root of (selectivity of second most selective predicate) * total number of rows
select 0.3*sqrt(0.3)*1500 --246.475150877325 --0.3是计算规则里面的一个常量

那么如果我在sql server 2012中执行该sql语句或者使用查询跟踪标记9481来关闭新的基数评估,数据库优化器使用老的基数评估,你会发现预估行数(estimated number of rows)为135了。如下所示:

dbcc freeproccache;
go
dbcc dropcleanbuffers;
go
declare @min_value int = 20;
declare @max_value int = 50;
select * from dbo.test_estimated_row where id > @min_value and id < @max_value
option (querytraceon 9481);
go


这里的计算公式是

((estimated number of rows for first predicate) *(estimated number of rows for second predicate)) /total number of rows
(0.3*1500)*(0.3*1500)/1500 = 0.09*1500 = 135 

那么现在我们往表test_estimated_row里面插入50条记录,此时这个数据量是不会触发统计信息更新的,而此时id=55的值超出了直方图中的rang_hi_ky的最大值50,也就是说直方图中没有统计这些新插入的数据,那这种情形称作升序键问题(ascending key problem)。在更统计信息新前就对这些数据运行查询,就会发生此类问题。

declare @index int =1;
while @index <= 50
begin
 insert into test_estimated_row
 values(55, 'id is 50');
 set @index+=1;
end
go

那么再来看看下面sql的预估行数(estimated number of rows),如下所示:

dbcc freeproccache;
go
dbcc dropcleanbuffers;
go
select * from dbo.test_estimated_row where id = 55;
go


那么预估行数(estimated number of rows)为39.37 是怎么计算来的呢?其实这个问题就是这篇博客里面提出的问题,先前++c++他在群里面讨论了一下。

optimizing your query plans with the sql server 2014 cardinality estimator里面介绍,这种是基数估计的计算公式为 [all density] * [rows sampled] 。但是实际测试发现这个例子并不是如此,那么我们先来亲自测试一下白皮书文档里面的例子(注意,数据库实例是sql server 2014,adventureworks2012的兼容级别为120),看看文档里面的例子是否正确。

select [salesorderid], [orderdate] 
from sales.[salesorderheader]
where [orderdate] = '2005-07-01 00:00:00.000';
select [s].[object_id],
  [s].[name],
  [s].[auto_created]
from sys.[stats] as s
inner join sys.[stats_columns] as [sc]
  on [s].[stats_id] = [sc].[stats_id] and
   [s].[object_id] = [sc].[object_id]
where [s].[object_id] = object_id('sales.salesorderheader') and
  col_name([s].[object_id], [sc].[column_id]) = 'orderdate';

可以看到orderdate的统计信息为_wa_sys_00000003_4b7734ff

 

dbcc show_statistics('sales.salesorderheader', _wa_sys_00000003_4b7734ff);

从上可以看到最后统计信息更新时,采集的range_hi_key的最大值为2008-07-31 00:00:00,那么我们插入50条记录,此时这个数据量并不会触发统计信息更新。

insert into sales.[salesorderheader] ( [revisionnumber], [orderdate],
           [duedate], [shipdate], [status],
           [onlineorderflag],
           [purchaseordernumber],
           [accountnumber], [customerid],
           [salespersonid], [territoryid],
           [billtoaddressid], [shiptoaddressid],
           [shipmethodid], [creditcardid],
           [creditcardapprovalcode],
           [currencyrateid], [subtotal],
           [taxamt], [freight], [comment] )
values ( 3, '2014-02-02 00:00:00.000', '5/1/2014', '4/1/2014', 5, 0, 'so43659', 'po522145787',29825, 279, 5, 985, 985, 5, 21, 'vi84182', null, 250.00,
25.00, 10.00, '' );
go 50 -- insert 50 rows, representing very recent data, with a current orderdate value

然后我们开启sql跟踪标志9481,你会发现下面sql的预估行数为1。因为此时优化器采用老的基数估计。 

select [salesorderid], [orderdate] 
from sales.[salesorderheader]
where [orderdate] = '2014-02-02 00:00:00.000'
option (querytraceon 9481); -- cardinalityestimationmodelversion 70

取消sql跟踪标志时,数据库使用新的基数估计时,预估函数变为了27.9938

dbcc freeproccache;
go
dbcc dropcleanbuffers;
go
select [salesorderid], [orderdate] 
from sales.[salesorderheader]
where [orderdate] = '2014-02-02 00:00:00.000'

31465 *0.0008896797 ~=27.9937717605 ~= 27.9938 (四舍五入)

白皮书里的例子确实是如此,但是最上面那个例子,不清楚预估行数是如何计算的,尽管做了一些推测,但是在其它例子中始终不能验证。不知是这个白皮书有误还是sql server的基数估计做了调整, 还是说基数估计(ce)的算法远远不止这么简单?我在这个问题上纠结了两天,依然没有搞清楚!在测试、推测过程中,我发现一个新的问题:当表里面新增了数据,那么之前的测试列子结果是否还是一样呢?答案是不一样了。如下所示:

select * from dbo.test_estimated_row where id = 10;
go

预估函数从100变为了103.333, 这个是怎么计算来的呢? 个人推测是这样得来的(如下所示)。

select 1550*(100.0/1500) --~= 103.332300 

也就是说升序键问题(ascending key problem)也会影响预估函数。上面都是简单sql的预估行数(estimated number of rows)的推演、实际情况中,sql要比这个复杂得多,那么在复杂情况下,例如多个过滤谓词的情况下,基数估计又是怎样预估行数的呢?由于前面例子构造的比较简单,不适合后面的演示,那么我们就用optimizing your query plans with the sql server 2014 cardinality estimator里的例子来简单演示一下: 

use [adventureworks2012];
go
select  [addressid],
  [addressline1],
  [addressline2]
from person.[address]
where [stateprovinceid] = 9 and
   [city] = n'burbank' and
   [postalcode] = n'91502'
option (querytraceon 9481); -- cardinalityestimationmodelversion 70
go

如下所示,过滤谓词[stateprovinceid]、[city]、 [postalcode]对应的统计信息分别为ix_address_stateprovinceid、_wa_sys_00000004_164452b1、_wa_sys_00000006_164452b1。

select [s].[object_id],
    [s].[name],
    [s].[auto_created],
    col_name([s].[object_id], [sc].[column_id]) as [col_name]
from  sys.[stats] as s
inner join sys.[stats_columns] as [sc]
    on [s].[stats_id] = [sc].[stats_id] and
      [s].[object_id] = [sc].[object_id]
where  [s].[object_id] = object_id('person.address');


dbcc show_statistics ('person.address', _wa_sys_00000004_164452b1); -- city


select 196.0/19614 ~= 0.0099928
dbcc show_statistics ('person.address', ix_address_stateprovinceid); -- stateprovinceid


select 4564.0/19614 ~= 0.2326909
dbcc show_statistics ('person.address', _wa_sys_00000006_164452b1); -- postalcode


select 194.0/19614 ~= 0.0098908 --记录

从sql server 7 ~ sql server 2012, 如果查询条件中,两个或多个谓词使用and联结,那么各个谓词的选择率si的乘积将作为查询预估函数的选择率

(s1 * s2 * s3....*sn)
(s1 * s2 * s3....*sn) *(rows sampled)
 
select 0.0098908 * -- postalcode predicate selectivity
    0.0099928 * -- city predicate selectivity
    0.2326909 * -- stateprovinceid predicate selectivity
    19614;   -- table cardinality

其计算结果为0.451091024458953138624 ,它低于1行。所以查询优化器使用估计的最小行数 (1)。下面看看sql server 2014中新的基数估计是如何计算预估行数的。

select  [addressid],
  [addressline1],
  [addressline2]
from person.[address]
where [stateprovinceid] = 9 and
   [city] = n'burbank' and
   [postalcode] = n'91502'
go

那么新的基数估计(sql server 2014)的预估行数(estimated number of rows)13.4692是怎么计算来的呢? 其实它们是选择率使用下面这样一个公式,其中p0 < p1 < p2 < p3 < p4


select 0.0098908        * -- postalcode predicate selectivity
    sqrt(0.0099928)     * -- city predicate selectivity
    sqrt(sqrt(0.2326909))  * -- stateprovinceid predicate selectivity
    19614; -- table cardinality

计算结果为13.4690212669225 ~= 13.4692 是否还是有一些差别呢?你使用下面sql对比,就会发现,其实原因是小数点后精确位数和四舍五入导致的。具体我也不知道计算估计精确位数。

那么or selectivity又是如何计算的,我们先来看看老的基数估计是是如何计算的,如下例子所示:

use [adventureworks2012];
go
select  [addressid],
     [addressline1],
     [addressline2]
from person.[address]
where ([stateprovinceid] = 9 or
   [city] = n'burbank' )and
   [postalcode] = n'91502'
option (querytraceon 9481); -- cardinalityestimationmodelversion 70


0.0098908 -- postalcode predicate selectivity
0.0099928 -- city predicate selectivity
0.2326909 -- stateprovinceid predicate selectivity

计算公式:(s1 + s2) – (s1 * s2) ,那么(s1 + s2) – (s1 * s2) 计算的值为

(0.0099928 + 0.2326909) - (0.0099928 * 0.2326909) ~= 0.24035846637448 

然后和and操作,我们执行sql server 2014以前的and的选择性是这样计算的s1 * s2

0.0098908 * ((0.0099928 + 0.2326909) - (0.0099928 * 0.2326909)) ~= 0.002377337519216706784

最后的计算结果如下:

0.002377337519216706784 *19614 ~= 46.629098101916486861376 ~= 46.6296 (注意这个误差是因为精确小数位数和四舍五入造成的) 

那么我们再来看看sql server 2014下or selectivity的计算公式

use [adventureworks2012];
go
select  [addressid],
     [addressline1],
     [addressline2]
from person.[address]
where ([stateprovinceid] = 9 or
   [city] = n'burbank' )and
   [postalcode] = n'91502'

那么这个预估行数(estimated number of rows)是怎么算出来的呢? paul white 的博客介绍,是通过下面这样计算来的。

0.0098908 -- postalcode predicate selectivity
0.0099928 -- city predicate selectivity
0.2326909 -- stateprovinceid predicate selectivity 

a or b = not (( not a) and (not b)) 就是说a or b 和 not (( not a) and (not b)) 是等价的。 

那么就可以这么推算,最后的预估行数(estimated number of rows)计算结果为94.3525, 跟结果94.3515有细微差别(这个是因为浮点数精度和四舍五入造成的)

select 1- (1- 0.2326909)*sqrt(( 1-0.0099928)) ~= 0.236534308898679
select 0.009891 *sqrt(1- (1- 0.2326909)*sqrt(( 1-0.0099928)) )*19614 ~= 94.3525070823501 ~= 94.3515

上面是关于sql server中的基数估计(ce)如何计算预估行数的一些初步的探讨和认识,纠结我的问题到目前还没有弄清楚。虽然有点遗憾,但是在测试过程,发现去探究这些规律是一件非常有意思的事情.

以上所述是小编给大家介绍的sql server中关于基数估计计算预估行数的一些方法探讨,希望对大家有所帮助

如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复

相关文章:

◎已有 0 人评论

Copyright © 2019  萬仟网 保留所有权利. 粤ICP备17035492号-1
站长QQ:2386932994 | 联系邮箱:2386932994@qq.com