關於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 是怎麼計算來的呢?其實這個問題就是http://www.cnblogs.com/wy123/p/6770258.html這篇博客裡面提出的問題,先前++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中關於基數估計計算預估行數的一些方法探討,希望對大家有所幫助,如果大家有任何疑問請給我留言,小編會及時回復大家的。在此也非常感謝大家對 網站的支持!