?

本文出處：http://www.cnblogs.com/wy123/p/6008477.html?

　　關(guān)于統(tǒng)計(jì)信息對(duì)數(shù)據(jù)行數(shù)做預(yù)估，之前寫過(guò)對(duì)非相關(guān)列（單獨(dú)或者單獨(dú)的索引列）進(jìn)行預(yù)估時(shí)候的算法，參考這里。
　　今天來(lái)寫一下統(tǒng)計(jì)信息對(duì)于復(fù)合索引在預(yù)估時(shí)候的計(jì)算方法和潛在問(wèn)題。
　　本文原形來(lái)自于是個(gè)實(shí)際業(yè)務(wù)問(wèn)題，某SQL在利用一個(gè)符合索引做查詢的時(shí)候，發(fā)現(xiàn)始終會(huì)出現(xiàn)預(yù)估誤差較大的情況，
　　而改變復(fù)合索引的列順序，這個(gè)預(yù)估行數(shù)的誤差會(huì)發(fā)生變化，
　　也就是說(shuō)，Create index idx_index1 ON TableName(col1,col2)與Create index idx_index2 on TableName(col2,col1)
　　用完全一樣的的查詢條件做查詢，兩個(gè)索引的執(zhí)行計(jì)劃對(duì)其預(yù)估的行數(shù)是不一樣的
　　究其原因在哪里呢？

　　

　　先造一個(gè)測(cè)試環(huán)境：

CREATE TABLE TestStatistics (COL1 INT IDENTITY(1,1) ,COL2 INT ,COL3 DATETIME ,COL4 VARCHAR(50) ) GOINSERT INTO TestStatistics VALUES (RAND()*10,CAST(GETDATE()-RAND()*300 AS date),NEWID()) GO 1000000

?

?問(wèn)題重現(xiàn)

首先看一個(gè)非常有意思的問(wèn)題，
在同一張表上，
先這么建一個(gè)索引：CREATE INDEX IDX_COL2_COL3 ON TestStatistics(COL2,COL3)
執(zhí)行一個(gè)查詢，預(yù)估為4127.86行
然后DROP掉上面的索引，繼續(xù)創(chuàng)建一個(gè)索引：CREATE INDEX IDX_COL3_COL2 ON TestStatistics(COL3,COL2)
注意COL2和COL3的順序不一致
繼續(xù)執(zhí)行上面的查詢（查詢條件不變，數(shù)據(jù)不變，僅僅是索引列順序發(fā)生了變化），這一次預(yù)估為2414.91行

?

查詢條件一樣，數(shù)據(jù)也一樣，為什么改變復(fù)合索引列順序會(huì)影響到執(zhí)行計(jì)劃對(duì)數(shù)據(jù)行的預(yù)估呢？

?

?

?

?

首先來(lái)看第一個(gè)索引時(shí)候的預(yù)估算法：

　　這個(gè)查詢他預(yù)估為4127.86行，如下圖

?

　　說(shuō)起來(lái)預(yù)估，就離不開統(tǒng)計(jì)信息，首先來(lái)看IDX_COL2_COL3這個(gè)索引的統(tǒng)計(jì)信息，
　　我們知道，對(duì)于復(fù)合索引，統(tǒng)計(jì)信息中只有前導(dǎo)列的統(tǒng)計(jì)數(shù)據(jù)，也就是說(shuō)IDX_COL3_COL2這個(gè)索引只有COL2這個(gè)列的統(tǒng)計(jì)信息，如下截圖
　　對(duì)于COL2=2的統(tǒng)計(jì)信息，統(tǒng)計(jì)為100336行，我們記住這個(gè)數(shù)字

?

?

　　統(tǒng)計(jì)信息的另外一個(gè)特點(diǎn)就是在會(huì)在查詢列（非索引列）上自動(dòng)創(chuàng)建統(tǒng)計(jì)信息，如下截圖
　　查詢執(zhí)行過(guò)程中，自動(dòng)創(chuàng)建了一個(gè)名字為：_WA_Sys_00000003_24E8431A的統(tǒng)計(jì)信息
　　這個(gè)統(tǒng)計(jì)信息就是對(duì)COL3列的統(tǒng)計(jì)，可以發(fā)現(xiàn)在大于等于2012-10-20之后的統(tǒng)計(jì)行數(shù)

　　在SQL Server 2012中，對(duì)數(shù)據(jù)行的預(yù)估計(jì)算方式是各個(gè)字段的選擇性的乘積，
　　假如P_n代表不同字段的密度，那么預(yù)估行數(shù)的計(jì)算方法就是: 預(yù)估行數(shù)=p₀*p₁*p₂*p₃……*RowCount
　　可以利用這個(gè)算法，計(jì)算目前數(shù)據(jù)下，預(yù)估出來(lái)的結(jié)果：4217.86，跟執(zhí)行計(jì)劃預(yù)估是一致的，非常完美！

?

?

?

?　　 當(dāng)刪除了IDX_COL2_COL3重建建立順序?yàn)镃OL3+COL2的索引的時(shí)候，預(yù)估如下

　　 與上面同樣的查詢條件，預(yù)估為2414.91行

?

?　　

　　依據(jù)上面的分析步驟，首先來(lái)分析索引列上的統(tǒng)計(jì)信息，如下截圖為大于等于2016-10-20之后的預(yù)估行數(shù)

?

?

同理，本次查詢也會(huì)自動(dòng)建立COL2列上的統(tǒng)計(jì)信息（IDX_COL2_COL3索引被刪除），觀察這個(gè)統(tǒng)計(jì)信息對(duì)COL2=2的預(yù)估為83711.36行

　　　

　　　同樣我們利用上述公式，來(lái)計(jì)算預(yù)估的行數(shù)：2414.9035行，也非常完美地吻合和執(zhí)行計(jì)劃預(yù)估的結(jié)果

　　　

?

　　至此，應(yīng)該很清楚一開始的問(wèn)題了，就是為什么復(fù)合索引列順序不一致，在查詢的時(shí)候?qū)е骂A(yù)估也不一致的原因。
　　最根本的原因有就是：
　　符合索引上只有前導(dǎo)列的統(tǒng)計(jì)信息，查詢引擎會(huì)根據(jù)需要自動(dòng)創(chuàng)建非前導(dǎo)列的統(tǒng)計(jì)信息，
　　但是，非常關(guān)鍵一點(diǎn)，如果細(xì)心的話，你會(huì)發(fā)現(xiàn)查詢引擎自動(dòng)創(chuàng)建的統(tǒng)計(jì)信息的取樣行數(shù)都不是100%取樣的，這一點(diǎn)非常關(guān)鍵
　　正是因?yàn)榉乔皩?dǎo)列取樣有一定的誤差，導(dǎo)致在預(yù)估算法的時(shí)候，也即 預(yù)估行數(shù)=p₀*p₁*p₂*p₃……*RowCount的時(shí)候，密度值是不一樣的
　　也即在創(chuàng)建IDX_COL2_COL3的時(shí)候，統(tǒng)計(jì)出來(lái)的COL2密度為P_{1_1}，COL3密度為P_{2_1}，
　　創(chuàng)建IDX_COL3_COL2的時(shí)候，統(tǒng)計(jì)出來(lái)的COL2密度為P_{1_2}，COL3密度為P_{2_2}，因?yàn)镻_{1_1}<>P_{1_2},P_{2_1}<>P_{2_2}
　　因此，計(jì)算出的結(jié)果就是P_{1_1}*P_{2_1}<>P_{2_1}*P_{2_2，}原理很簡(jiǎn)單，希望看官能明白。

　　

?

　　照這么計(jì)算，對(duì)于兩個(gè)順序不同的統(tǒng)計(jì)信息，如果P_{1_1=}P_{2_1}并且P_{2_1=}P_{2_2，}那么乘積就是一樣的，預(yù)估行數(shù)也就是一樣的，那么是不是呢？

　　

　　對(duì)于不同順序的兩個(gè)索引，先看COL2,COL3順序的索引
　　在查詢一次之后（建立了統(tǒng)計(jì)信息），執(zhí)行一個(gè)百分之百取樣（WITH FULLSCAN）的統(tǒng)計(jì)信息更新
　　重新來(lái)看其預(yù)估行數(shù)，這一次預(yù)估為：2894.49

　　

?

?

?　　刪除COL2,COL3順序的索引，建立COL3,COL2為順序的索引
　　在查詢一次之后（建立了統(tǒng)計(jì)信息），執(zhí)行一個(gè)百分之百取樣（WITH FULLSCAN）的統(tǒng)計(jì)信息更新
　　重新來(lái)看其預(yù)估行數(shù)，這一次預(yù)估為：同樣為2894.49，是吻合上述算法

?

?

?

?總結(jié)：

　　文本簡(jiǎn)單演示了執(zhí)行計(jì)劃利用統(tǒng)計(jì)信息預(yù)估的算法和原理，以及在計(jì)算預(yù)估行數(shù)時(shí)候可能受到的干擾因素，
　　這就要求我們?cè)诮⑺饕臅r(shí)候，不僅僅是說(shuō)我建一個(gè)復(fù)合索引就完事了，也要注意其索引列的順序?qū)?zhí)行計(jì)劃預(yù)估的影響，
　　更重要的是，要注意查詢引擎自動(dòng)生成的統(tǒng)計(jì)信息對(duì)預(yù)估的影響程度。

　　拋開統(tǒng)計(jì)信息談索引的，都是耍流氓。拋開統(tǒng)計(jì)信息取樣百分比談統(tǒng)計(jì)信息的，也是耍流氓。

　　

　　引申出來(lái)另外一個(gè)問(wèn)題：維護(hù)統(tǒng)計(jì)信息的時(shí)候，能只更新索引列的統(tǒng)計(jì)信息，忽略非索引列的統(tǒng)計(jì)信息嗎？

?

本人技術(shù)能力還很菜，寫的不對(duì)的地方還請(qǐng)各位看官指出，謝謝。

?

總結(jié)

以上是生活随笔為你收集整理的SQL Server 执行计划利用统计信息对数据行的预估原理二（为什么复合索引列顺序会影响到执行计划对数据行的预估）...的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。