SQL Server統(tǒng)計(jì)信息更新時(shí)采樣百分比對(duì)數(shù)據(jù)預(yù)估準(zhǔn)確性的影響詳解

為什么要寫(xiě)統(tǒng)計(jì)信息

最近看到園子里有人寫(xiě)統(tǒng)計(jì)信息，樓主也來(lái)湊熱鬧。

話(huà)說(shuō)經(jīng)常做數(shù)據(jù)庫(kù)的，尤其是做開(kāi)發(fā)的或者優(yōu)化的，統(tǒng)計(jì)信息造成的性能問(wèn)題應(yīng)該說(shuō)是司空見(jiàn)慣。

當(dāng)然解決辦法也并非一成不變，“一招鮮吃遍天”的做法已經(jīng)行不通了（題外話(huà)：整個(gè)時(shí)代不都是這樣子嗎）

當(dāng)然，還是那句話(huà)，既然寫(xiě)了就不能太俗套，寫(xiě)點(diǎn)不一樣的，本文通過(guò)分析一個(gè)類(lèi)似實(shí)際案例來(lái)解讀統(tǒng)計(jì)信息的更新的相關(guān)問(wèn)題。

對(duì)于實(shí)際問(wèn)題，不但要解決問(wèn)題，更重要的是要從理論上深入分析，才能更好地駕馭數(shù)據(jù)庫(kù)。

何時(shí)更新統(tǒng)計(jì)信息

（1）查詢(xún)執(zhí)行緩慢，或者查詢(xún)語(yǔ)句突然執(zhí)行緩慢。這種場(chǎng)景很可能是由于統(tǒng)計(jì)信息沒(méi)有及時(shí)更新而遭遇了參數(shù)嗅探的問(wèn)題。

（2）當(dāng)大量數(shù)據(jù)更新（INSERT/DELETE/UPDATE）到升序或者降序的列時(shí)，這種情況下，統(tǒng)計(jì)信息直方圖可能沒(méi)有及時(shí)更新。

（3）建議在除索引維護(hù)（當(dāng)你重建、整理碎片或者重組索引時(shí)，數(shù)據(jù)分布不會(huì)改變）外的維護(hù)工作之后更新統(tǒng)計(jì)信息。

（4）數(shù)據(jù)庫(kù)的數(shù)據(jù)更改頻繁，建議最低限度每天更新一次統(tǒng)計(jì)信息。數(shù)據(jù)倉(cāng)庫(kù)可以適當(dāng)降低更新統(tǒng)計(jì)信息的頻率。

（5）當(dāng)執(zhí)行計(jì)劃出現(xiàn)統(tǒng)計(jì)信息缺失警告時(shí)，需要手動(dòng)建立統(tǒng)計(jì)信息

統(tǒng)計(jì)信息基礎(chǔ)

首先說(shuō)一個(gè)老掉牙的話(huà)題，統(tǒng)計(jì)信息的更新閾值：

1，表格從沒(méi)有數(shù)據(jù)變成有大于等于1條數(shù)據(jù)。

2，對(duì)于數(shù)據(jù)量小于500行的表格，當(dāng)統(tǒng)計(jì)信息的第一個(gè)字段數(shù)據(jù)累計(jì)變化量大于500以后。

3，對(duì)于數(shù)據(jù)量大于500行的表格，當(dāng)統(tǒng)計(jì)信息的第一個(gè)字段數(shù)據(jù)累計(jì)變化量大于500 + (20%×表格數(shù)據(jù)總量)以后。

做個(gè)查詢(xún)，觸發(fā)統(tǒng)計(jì)信息更新，rowmodct歸0（繼續(xù)累積直到下一個(gè)觸發(fā)的閾值，觸發(fā)更新之后再次歸0）

關(guān)于統(tǒng)計(jì)信息“過(guò)期”的問(wèn)題

下面開(kāi)始正文，網(wǎng)絡(luò)上很多關(guān)于統(tǒng)計(jì)信息的文章，提到統(tǒng)計(jì)信息，很多都是統(tǒng)計(jì)信息過(guò)期的問(wèn)題，然后跟新之后怎么怎么樣

尤其在觸發(fā)統(tǒng)計(jì)信息自動(dòng)更新閾值的第三個(gè)區(qū)間：也就是說(shuō)數(shù)據(jù)累計(jì)變化超過(guò)20%之后才能自動(dòng)觸發(fā)統(tǒng)計(jì)信息的更新

這一點(diǎn)對(duì)于大表來(lái)說(shuō)通常影響是比較大的，比如1000W的表，變化超過(guò)20%也+500也就是200W+500行之后才觸發(fā)統(tǒng)計(jì)信息更新，這個(gè)閾值區(qū)間的自動(dòng)觸發(fā)閾值，絕大多數(shù)情況是不能接受的，于是對(duì)于統(tǒng)計(jì)信息的診斷就變成了是否“過(guò)期”

判斷統(tǒng)計(jì)信息是否過(guò)期，然后通過(guò)更新統(tǒng)計(jì)信息來(lái)促使執(zhí)行計(jì)劃更加準(zhǔn)確地預(yù)估行數(shù)，這一點(diǎn)本無(wú)可厚非

但是，問(wèn)題也就出在這里了：那么怎么更新統(tǒng)計(jì)信息？一成不變的做法是否可行，這才是問(wèn)題的重點(diǎn)。

當(dāng)然肯定有人說(shuō)，我就是按照默認(rèn)方式更新的，更新完之后SQL也變得更加優(yōu)化了什么的

通過(guò)update statistics TableName StatisticName更新某一個(gè)索引的統(tǒng)計(jì)信息，

或者update statistics TableName更新全表的統(tǒng)計(jì)信息

這種情況下往往是小表上可以這么做，當(dāng)然對(duì)于大表或者小表沒(méi)有一個(gè)標(biāo)準(zhǔn)值，一切要結(jié)合事實(shí)來(lái)說(shuō)明問(wèn)題

下面開(kāi)始本文的主題：

抽象并簡(jiǎn)化出業(yè)務(wù)中的一個(gè)實(shí)際案例，創(chuàng)建這么一張表，類(lèi)似于訂單和訂單明細(xì)表（主子表），

這里你可以想象成是一個(gè)訂單表的子表，Id字段是唯一的，有一個(gè)ParentID字段，是非唯一的，

ParentID類(lèi)似于主表的Id，測(cè)試數(shù)據(jù)按照一個(gè)主表Id對(duì)應(yīng)50條子表明細(xì)的規(guī)律插入數(shù)據(jù)

CREATE TABLE [dbo].[TestStaitisticsSample](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [ParentId] [int] NULL,
 [OtherColumn] [varchar](50) NULL
) 


declare @i int=0
while(@i100000000)
begin

 insert into [TestStaitisticsSample](ParentId,OtherColumn)values(@i,NEWID())
 /*
 中間插入50條，也即一個(gè)主表Id對(duì)應(yīng)50條子表明細(xì)
 */
 insert into [TestStaitisticsSample](ParentId,OtherColumn)values(@i,NEWID())

 set @i=@i+1
end
go

create nonclustered index [idx_ParentId] ON [dbo].[TestStaitisticsSample]
(
 [ParentId] 
)
go

本來(lái)打算插入1億條的，中間我讓他執(zhí)行我睡午覺(jué)去了，醒來(lái)之后發(fā)現(xiàn)SSMS掛掉了，掛掉了算了，數(shù)據(jù)也接近1億了，能說(shuō)明問(wèn)題就夠了

現(xiàn)在數(shù)據(jù)分布的非常明確，就是一個(gè)ParentId有50條數(shù)據(jù)，這一點(diǎn)首先要澄清。

測(cè)試數(shù)據(jù)寫(xiě)入，以及所創(chuàng)建完成之后來(lái)更新idx_ParentId 索引上的統(tǒng)計(jì)信息，就按照默認(rèn)的方式來(lái)更新，然后來(lái)觀(guān)察統(tǒng)計(jì)信息

默認(rèn)方式更新統(tǒng)計(jì)信息（未指定采樣密度）

表里現(xiàn)在是8000W多一點(diǎn)記錄，默認(rèn)更新統(tǒng)計(jì)信息時(shí)取樣行數(shù)是462239行，那么這個(gè)統(tǒng)計(jì)信息靠譜嗎？

上面說(shuō)了，造數(shù)據(jù)的時(shí)候，我一個(gè)ParentId對(duì)應(yīng)的是50行記錄，這一點(diǎn)非常明確，他這里統(tǒng)計(jì)出來(lái)的多少？

1，對(duì)于取樣的RANG_HI_Key值，比如51632，預(yù)估了862.212行

2，對(duì)于A(yíng)VG_RANG_ROW，比如45189到51632之間的每個(gè)Id的數(shù)據(jù)對(duì)應(yīng)的數(shù)據(jù)行，預(yù)估是6682.490行

之前造數(shù)據(jù)的時(shí)候每個(gè)Id都是50行，這里的預(yù)估靠譜嗎，這個(gè)誤差是無(wú)法接受的，

很多時(shí)候，對(duì)于大表，采用默認(rèn)（未指定采樣密度）的情況下，默認(rèn)的采樣密度并不足以準(zhǔn)確地描述數(shù)據(jù)分布情況

指定一個(gè)采樣密度的方式更新統(tǒng)計(jì)信息（20%采樣）

這一次用20%的采樣密度，可以看到取樣的行數(shù)是15898626行

1，對(duì)于取樣的RANG_HI_Key值，比如216305，他給我預(yù)估了24.9295行

2，對(duì)于A(yíng)VG_RANG_ROW，比如186302到216305之間的每個(gè)Id的行數(shù)，預(yù)估是197.4439行

觀(guān)察比如上面默認(rèn)的取樣密度，這一次不管是RANG_HI_Key還是AVG_RANG_ROW得預(yù)估，都有不一個(gè)非常高的下降，開(kāi)始趨于接近于真實(shí)的數(shù)據(jù)分布（每個(gè)Id有50行數(shù)據(jù)）

整體上看，但是這個(gè)誤差還是比較大的，如果繼續(xù)提高采樣密度，看看有什么變化？

指定一個(gè)采樣密度的方式更新統(tǒng)計(jì)信息（70%采樣）　

這一次用70%的采樣密度，可以看到取樣行數(shù)是55962290行

1，對(duì)于取樣的RANG_HI_Key值，比如1978668，預(yù)估了71.15906行

2，對(duì)于A(yíng)VG_RANG_ROW，比如1124024到1978668之間的每個(gè)Id，預(yù)估為61.89334行

可以說(shuō)，對(duì)于絕大多數(shù)值得預(yù)估（AVG_RANG_ROW），都愈發(fā)接近于真實(shí)值

指定一個(gè)采樣密度的方式更新統(tǒng)計(jì)信息（100%采樣）

可以看到，取樣行數(shù)等于總行數(shù)，也就是所謂的全部（100%）取樣

看一下預(yù)估結(jié)果：

比如Id=3981622，預(yù)估是50行，3981622與4131988之間的Id的行數(shù)，預(yù)估為49.99874行，基本上等于真實(shí)數(shù)據(jù)分布

這個(gè)就不做過(guò)多解釋了，基本上跟真實(shí)值是一樣的，只是AVG_RANG_ROW有一點(diǎn)非常非常小的誤差。

取樣密度高低與統(tǒng)計(jì)信息準(zhǔn)確性的關(guān)系

至于為什么默認(rèn)取樣密度和較低取樣密度情況下，誤差很大的情況我簡(jiǎn)單解釋一下，也非常容易理解，因?yàn)椤白颖怼敝写鎯?chǔ)主表ID的ParentId值允許重復(fù)，在存在重復(fù)值的情況下，如果采樣密度不夠，極有可能造成“以偏概全”的情況

比如對(duì)10W行數(shù)據(jù)取樣1W行，原本10W行數(shù)劇中有2000個(gè)不重復(fù)的ParentId值，如果是10%的取樣，在1W行取樣數(shù)據(jù)中，因?yàn)槊芏炔粔虼?，只找到?0個(gè)不重復(fù)的ParentId值，那么就會(huì)認(rèn)為每一行ParentId對(duì)應(yīng)500行數(shù)據(jù)，這根實(shí)際的分布的每個(gè)ParentId有一個(gè)非常大的誤差范圍

如果提高采樣密度，那么這個(gè)誤差就會(huì)越來(lái)越小?！　?/p>

更新統(tǒng)計(jì)信息的時(shí)候，高比例的取樣是否可取（可行）　

因此在觀(guān)察統(tǒng)計(jì)信息是否過(guò)期，決定更新統(tǒng)計(jì)信息的時(shí)候，一定要注意取樣的密度，就是說(shuō)表中有多少行數(shù)據(jù)，統(tǒng)計(jì)信息更新的時(shí)候取了多少采樣行，密度有多高。

當(dāng)然，肯定有人質(zhì)疑，那你說(shuō)采樣密度越高，也就是取樣行數(shù)越高越準(zhǔn)確，那么我就100%取樣。

這樣行不行？

還要分情況看，對(duì)于幾百萬(wàn)或者十幾萬(wàn)的小表來(lái)說(shuō)，當(dāng)然沒(méi)有問(wèn)題，這也是為什么數(shù)據(jù)庫(kù)越小，表數(shù)據(jù)越少越容易掩蓋問(wèn)題的原因。

對(duì)于大表，上億的，甚至是十幾億的，你按照100%采樣試一試？　

舉個(gè)實(shí)際例子：

我這里對(duì)一個(gè)稍微大一點(diǎn)的表做個(gè)全表統(tǒng)計(jì)信息的更新，測(cè)試環(huán)境，服務(wù)器沒(méi)負(fù)載，存儲(chǔ)是比普通的機(jī)械硬盤(pán)要強(qiáng)很多的SAN存儲(chǔ)

采用full scan，也就是100%采樣的更新操作，看一下，僅僅這一樣表的update statistic操作就花費(fèi)了51分鐘

試想一下，對(duì)一個(gè)數(shù)百GB甚至數(shù)TB的庫(kù)來(lái)說(shuō)，你敢這么搞一下。

扯一句，這個(gè)中秋節(jié)過(guò)的，折騰了大半天，話(huà)說(shuō)做測(cè)試過(guò)程中電腦有開(kāi)始有點(diǎn)卡，

做完測(cè)試之后停掉SQLServer服務(wù)，瞬間內(nèi)存釋放了7個(gè)G，可見(jiàn)這些個(gè)操作還是比較耗內(nèi)存的

總結(jié)：

本文通過(guò)對(duì)于某些場(chǎng)景下，在對(duì)較大的表的索引統(tǒng)計(jì)信息更新時(shí)，采樣密度的分析，闡述了不同采樣密度下，對(duì)統(tǒng)計(jì)信息預(yù)估的準(zhǔn)確性的影響。

當(dāng)然對(duì)于小表，一些都好說(shuō)。

隨著單表數(shù)據(jù)量的增加，統(tǒng)計(jì)信息的更新策略也要做相應(yīng)的調(diào)整，

不光要看統(tǒng)計(jì)信息是否“過(guò)期”，更重要的是注意統(tǒng)計(jì)信息更新時(shí)究竟取樣了全表的多少行數(shù)據(jù)做統(tǒng)計(jì)。

對(duì)于大表，采用FULL SCAN或者100%采樣往往是不可行的，這時(shí)候就需要做出權(quán)衡，做到既能準(zhǔn)確地預(yù)估，又能夠以合理的代價(jià)執(zhí)行。

好了，以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問(wèn)大家可以留言交流，謝謝大家對(duì)腳本之家的支持