快捷搜索:
来自 网络数据库 2019-11-17 13:47 的文章
当前位置: 67677新澳门手机版 > 网络数据库 > 正文

Server中关于基数估计如何计算预估行数的一些探

   关于SQL Server 20第114中学的基数预计,官方文书档案Optimizing Your Query Plans with the SQL Server 2015 Cardinality Estimator里有雅量细节介绍,不过总体是罗马尼亚(罗曼ia卡塔 尔(阿拉伯语:قطر‎语,推测也从未多少人留心阅读。那么SQL Server 20第114中学基数估摸的预估行数到底是怎么总结的呢? 有哪部分规律呢?大家下边通过一些例子来初略理解一下,上边测量检验案例仅供参考,如有不足或肤浅的地点,敬请指教!

 

上边实验测量试验的条件至关心重视要为SQL Server 二零一六 SP2 (斯坦dard 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

图片 1

 

SQL Server中有两种谓词:过滤谓词和一连谓词 。 大家先来探视过滤谓词的基数估计(预估行数卡塔尔,测验进度,若是要保险测量试验的公正性或不被其余因素影响,你能够应用上面包车型大巴DBCC命令来裁撤压抑,如下例子所示:

DBCC FREEPROCCACHE;       --从执行计划缓冲区删除所有缓存的执行计划

GO

DBCC DROPCLEANBUFFERS;     --从缓冲池中删除所有缓存,清除缓冲区

GO

 

SELECT * FROM dbo.TEST_ESTIMATED_ROW WHERE ID = 10;

GO

图片 2

 

(注意,实施时请勾选蕴涵实际奉行布署开关卡塔 尔(阿拉伯语:قطر‎如上所示,预估行数(Estimated Number of Rows卡塔尔为100,跟实际行数一致。当然你换别的值,举例20, 30, 40 ,50,其预估行数(Estimated Number of Rows卡塔尔跟实际行数都以不易的(SQL SE奇骏VER二〇一二中测验结果也长久以来卡塔 尔(阿拉伯语:قطر‎。那么只要笔者换三个不设有的值吗?预估行数会是多少吗?

SELECT * FROM dbo.TEST_ESTIMATED_ROW WHERE ID = 4;

GO

图片 3

 

如上所示,预估行数(Estimated Number of Rows卡塔 尔(阿拉伯语:قطر‎为1. 你换其余任何不设有的值,预估行数(Estimated Number of Rows卡塔 尔(英语:State of Qatar)都为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

图片 4

 

如上截图所示,实际施行安顿的预估行数(Estimated Number of Rows卡塔 尔(阿拉伯语:قطر‎是300, 那么如何计算来的呢? 其实您能够依赖公式来计量, 纵然不信,你能够协会各样案例测量检验表明大器晚成(Wissu卡塔尔国下就能够收获答案了.

 

  [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

图片 5

 

倘若您赋予@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

图片 6

 

接下去,大家改正一下SQL语句,将查询条件从等于符号改为超越标识,如下所示:

DECLARE @SID INT = 11;

SELECT * FROM dbo.TEST_ESTIMATED_ROW WHERE ID > @SID;

GO

图片 7

 

如上所示,预估行数(Estimated Number of Rows卡塔尔变为了450,那么这些值是怎么总结得来的吗?

总计公式是: [Row Sampled ] * 0.3(30%)

1500 *0.3= 450

明确会有人问,你怎么精晓是 [Row Sampled ] * 0.3 呢? 不会是你逆推的吗。 不错,这里是四个测算(网络也许有不菲素材都承认是0.3,一时充当计算公式中的三个常量卡塔尔国,而且也做了成都百货上千测验,确实正是四分之一。比方你将@SID赋值为41,预估行数(Estimated Number of Rows卡塔 尔(英语:State of Qatar)依旧为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

图片 8

 

如果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

图片 9

 

本条1200 是那般测算的,如下所示,大于20的RANGE_HI_KEY有30 , 40, 50 ,他们相应的EQ_ROWS值相加 300 400 500 =1200, 不相信你可以测验一下,将@SID赋予30,那么预估行数(Estimated Number of Rows卡塔尔国就能够成为900.

 

图片 10

 

那就是说大家再改正一下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

图片 11

 

如上所示,预估行数(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 二零一二中实行该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

图片 12

 

此处的总计公式是

((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卡塔 尔(英语:State of Qatar),如下所示:

DBCC FREEPROCCACHE;

GO

DBCC DROPCLEANBUFFERS;

GO

SELECT * FROM dbo.TEST_ESTIMATED_ROW WHERE ID = 55;

GO

图片 13

 

那么预估行数(Estimated Number of Rows卡塔 尔(阿拉伯语:قطر‎为39.37 是怎么总括来的呢?其实那些题目正是 Your Query Plans with the SQL Server 2015 Cardinality Estimator里面介绍,这种是基数估计的总括公式为 [All density] * [Rows Sampled] 。唯独其实地衡量试发掘这一个事例并不是这么,那么我们先来亲自测量试验一下白皮书文书档案里面包车型大巴例子(注意,数据库实例是SQL Server 2016,AdventureWorks二零一一的特别等级为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

 

图片 14

DBCC SHOW_STATISTICS('Sales.SalesOrderHeader', _WA_Sys_00000003_4B7734FF);

 

图片 15

 

从上能够见见最终总计新闻更新时,搜集的RANGE_HI_KEY的最大值为二〇〇九-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

图片 16

废除SQL追踪标识时,数据库使用新的基数估算时,预估函数成为了27.9938

DBCC FREEPROCCACHE;

GO

DBCC DROPCLEANBUFFERS;

GO

SELECT [SalesOrderID], [OrderDate] 

FROM Sales.[SalesOrderHeader]

WHERE [OrderDate] = '2014-02-02 00:00:00.000'

图片 17

图片 18

 

31465 *0.0008896797 ~=27.9937717605 ~= 27.9938 (四舍五入卡塔 尔(英语:State of Qatar)

 

黄皮书里的例子确实是如此,但是最上边十三分例子,不清楚预估行数是怎么样总计的,固然做了有个别测度,可是在其他例子中始终不能印证。不知是这些黄皮书有误依旧SQL Server的基数预计做了调节, 仍然说基数估量(CE)的算法远远不独有这么轻巧?本身在这里个难题上纠缠了两日,还是未有搞通晓!在测量试验、猜测进度中,作者开采四个新的标题:当表里边新扩充了多少,那么此前的测量检验列子结果是不是还是相近吧?答案是不一致了。如下所示:

SELECT * FROM dbo.TEST_ESTIMATED_ROW WHERE ID = 10;

 

GO

图片 19

预估函数从100变为了103.333, 那么些是怎么总结来的呢? 个人猜度是这么得来的(如下所示卡塔尔国。

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

 

约等于说升序键难题(ascending key problem卡塔 尔(英语:State of Qatar)也会潜濡默化预估函数。上边都以总结SQL的预估行数(Estimated Number of Rows卡塔尔国的推理、真实境况中,SQL要比那些纷纭得多,那么在良莠不齐情况下,比如多少个过滤谓词的景色下,基数测度又是怎么预估行数的呢?由于前边例子构造的比较轻便,不切合前面包车型大巴演示,那么大家就用Optimizing Your Query Plans with the SQL Server 二〇一四 卡德inality 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

图片 20

如下所示,过滤谓词[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');

图片 21

 

DBCC SHOW_STATISTICS ('Person.Address', _WA_Sys_00000004_164452B1); -- City

图片 22

 

SELECT 196.0/19614 ~= 0.0099928

 

DBCC SHOW_STATISTICS ('Person.Address', IX_Address_StateProvinceID); -- StateProvinceID

图片 23

 

SELECT 4564.0/19614 ~= 0.2326909

 

DBCC SHOW_STATISTICS ('Person.Address', _WA_Sys_00000006_164452B1); -- PostalCode

图片 24

 

SELECT 194.0/19614 ~= 0.0098908 --记录

 

从SQL Server 7 ~ SQL Server 二〇一三, 假使查询条件中,多少个或三个谓词使用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 20第114中学新的基数估量是哪些总结预估行数的。

 

SELECT    [AddressID],

    [AddressLine1],

    [AddressLine2]

FROM Person.[Address]

WHERE [StateProvinceID] = 9 AND

      [City] = N'Burbank' AND

      [PostalCode] = N'91502'

 

GO

图片 25

 

那么新的基数揣测(SQL Server 二零一四卡塔 尔(阿拉伯语:قطر‎的预估行数(Estimated Number of Rows卡塔 尔(阿拉伯语:قطر‎13.4692是怎么总计来的吗? 其实它们是选择率使用上面那样一个公式,当中p0 < p1 < p2 < p3 < p4

图片 26

 

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相比较,就能够开采,其实原因是小数点后正确位数和四舍五入招致的。具体作者也不亮堂总括估摸准确位数。

图片 27

 

那么O普拉多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

图片 28

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 二零一四下OKoleos Selectivity的总计公式

USE [AdventureWorks2012];

GO

 

SELECT    [AddressID],

          [AddressLine1],

          [AddressLine2]

FROM Person.[Address]

WHERE ([StateProvinceID] = 9 OR

      [City] = N'Burbank' )AND

      [PostalCode] = N'91502'

图片 29

 

那么这些预估行数(Estimated Number of Rows卡塔尔国是怎么算出来的啊? PaulWhite 的博客介绍,是透过上边那样估测计算来的。

0.0098908 -- PostalCode predicate selectivity

0.0099928 -- City predicate selectivity

0.2326909 -- StateProvinceID predicate selectivity

 

A O福特Explorer B = NOT (( NOT A) AND (NOT B)) 便是说A O奥迪Q5 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)如何总结预估行数的片段开始的探幽索隐和认得,郁结我的难题到当前还尚无弄精晓。即使某些不满,但是在测量试验进度,开采去商量那么些规律是风流倜傥件极其风趣的事情.

 

参谋资料:

 

本文由67677新澳门手机版发布于网络数据库,转载请注明出处:Server中关于基数估计如何计算预估行数的一些探

关键词: