|
前言 經(jīng)過前幾篇的分析，其實大體已經(jīng)初窺到SQL Server統(tǒng)計信息的重要性了，所以本篇就要祭出這個神器了。 該篇內(nèi)容會很長，坐好板凳，瓜子零食之類... 不廢話，進正題 技術(shù)準備 數(shù)據(jù)庫版本為SQL Server2008R2，利用微軟的以前的案例庫（Northwind）進行分析，部分內(nèi)容也會應(yīng)用微軟的另一個案例庫AdventureWorks 相信了解SQL Server的朋友，對這兩個庫都不會太陌生。 概念理解 關(guān)于SQL Server中的統(tǒng)計信息，在聯(lián)機叢書中是這樣解釋的 查詢優(yōu)化的統(tǒng)計信息是一些對象，這些對象包含與值在表或索引 視圖的一列或多列中的分布有關(guān)的統(tǒng)計信息。查詢優(yōu)化器使用這些統(tǒng)計信息來估計查詢結(jié)果中的基數(shù)或行數(shù)。通過這些基數(shù)估計，查詢優(yōu)化器可以創(chuàng)建高質(zhì)量的查詢 計劃。例如，查詢優(yōu)化器可以使用基數(shù)估計選擇索引查找運算符而不是耗費更多資源的索引掃描運算符，從而提高查詢性能。 其實關(guān)于統(tǒng)計信息的作用通俗點將就是：SQL Server通過統(tǒng)計信息理解庫中每張表的數(shù)據(jù)內(nèi)容項分布，知道里面數(shù)據(jù)“長得啥德行，做到心中有數(shù)”，這樣每次查詢語句的時候就可以根據(jù)表中的數(shù)據(jù)分布，基本能定位到要查找數(shù)據(jù)的內(nèi)容位置。 比如，我記得我以前有篇文章寫過一個相同的查詢語句，但是產(chǎn)生了完全不同的查詢計劃，這里回顧下，基本如下： SELECT * FROM Person.Contact WHERE FirstName LIKE 'K%' SELECT * FROM Person.Contact WHERE FirstName LIKE 'Y%'
完全相同的查詢語句，只是查詢條件不同，一個查找以K開頭的顧客，一個查找以Y開頭的顧客，卻產(chǎn)生了完全不同的查詢計劃。 其實，這里的原因就是統(tǒng)計信息在作祟。 我們知道，在這張表的FirstName字段存在一個非聚集索引，目標就是為了提升如上面的查詢語句的性能。 但是這張表里面FirstName字段中的數(shù)據(jù)內(nèi)容以K開頭的顧客存在1255行，也就是如果利用非聚集索引查找的方式，需要產(chǎn)生1225次IO操 作，這可能不是最糟的，糟的還在后面，因為我們獲取的數(shù)據(jù)字段并不全部在FirstName字段中，而需要額外的書簽查找來獲取，而這個書簽查找會產(chǎn)生的 大量的隨機IO操作。記?。哼@里是隨機IO。關(guān)于這里的查找方式在我們第一篇文章中就有介紹。 所以相比利用非聚集索引所帶來的消耗相比，全部的所以索引掃描來的更劃算，因為它依次掃描就可以獲取想要的數(shù)據(jù)。 而以Y開頭的就只有37行，37行數(shù)據(jù)完全通過非聚集索引獲取，再加一部分的書簽查找很顯然是一個很劃算的方式。因為它數(shù)據(jù)量少，產(chǎn)生的隨機IO量相對也會少。 所以，這里的問題來了： SQL Server是如何知道這張表里FirstName字段中以K開頭的顧客會比較多，而以Y開頭反而少呢？。 這里就是統(tǒng)計信息在作祟了，它不但知道FirstName字段中各行數(shù)據(jù)的內(nèi)容“長啥樣”，并且還是知道每行數(shù)據(jù)的分布情況。 其實，這就好比在圖書庫中，每個書架就是一張表，而每本書就是一行數(shù)據(jù)，索引就好像圖書館書籍列表，比如按類區(qū)分，而統(tǒng)計信息就好像是每類書籍的多少以及存放書架位置。所以你借一本書的時候，需要借助索引來查看，然后利用統(tǒng)計信息指導(dǎo)位置，這樣才能獲取書本。 希望這樣解釋，看官已經(jīng)明白了統(tǒng)計信息的作用了。
這里多談點，有很多童鞋沒有深入了解索引和統(tǒng)計信息的作用前提下，在看過很多調(diào)優(yōu)的文章之后，只深諳了一句話：調(diào)優(yōu)嘛，創(chuàng)建索引就行了。 我不否認創(chuàng)建索引這種方式調(diào)優(yōu)方式的作用性，但是很多時候關(guān)于建索引的技巧卻不了解。更巧的是大部分情況下屬于誤打誤撞創(chuàng)建完索引后，性能果真提升了，而有時候創(chuàng)建的索引卻毫無用處，只會影響表的其它操作的性能（尤其是Insert），更有甚者會產(chǎn)生死鎖情況。 而且，關(guān)于索引項的作用，其實很多的情況下，并不想你想象的那么美好，后續(xù)文章我們會分析那些索引失效的原因。 所以遇到問題，其實還要通過表象理解其本質(zhì)，這樣才能做到真正的有的放矢，有把握的解決問題。
解析統(tǒng)計信息 我們來詳細分析一下統(tǒng)計信息中的內(nèi)容項，我們知道在上面的語句中，在表Customers中ContactName列中存在一個非聚集索引項，所以在該列存在統(tǒng)計信息，我們可以通過如下腳本查看該表的統(tǒng)計信息列表 sp_helpstats Customers
然后通過以下命令來查看該統(tǒng)計信息的詳細內(nèi)容，代碼如下 DBCC SHOW_STATISTICS(Customers,ContactName)
每一個統(tǒng)計信息的內(nèi)容都包含以上三部分的內(nèi)容。 我們依次來分析下，通過這三部分內(nèi)容SQL Server如何了解該列數(shù)據(jù)的內(nèi)容分布的。 a、統(tǒng)計信息的總體屬性項 該部分包含以下幾列：
經(jīng)過上面部分的數(shù)據(jù)，統(tǒng)計信息已經(jīng)分析出該列數(shù)據(jù)的最近更新時間、數(shù)據(jù)量、數(shù)據(jù)長度、數(shù)據(jù)類型等信息值。
b、統(tǒng)計信息的覆蓋索引項 All density：反映索引列的稠密度值。這是一個非常重要的值，SQL Server會根據(jù)這個評分項來決定該索引的有效程度。 該分值的計算公式為：density=1/表中非重復(fù)的行數(shù)。所以該稠密度值取值范圍為：0-1。 該值越小說明該列的索引項選擇性更強，也就說該索引更有效。理想的情況是全部為非重復(fù)值，也就是說都是唯一值，這樣它的數(shù)最小。 舉個例子：比如上面 的例子該列存在91行，假如顧客不存在重名的情況下，那么該密度值就為1/91=0.010989，該列為性別列，那么它只存在兩個值：男、女，那么該列 的密度值就為0.5，所以相比而言SQL Server在索引選擇的時候很顯然就會選擇ContactName（顧客名字）列。 簡單點講：就是當(dāng)前索引的選擇性高，它的稠密度值就小，那么它就重復(fù)值少，這樣篩選的時候更容易找到結(jié)果值。相反，重復(fù)值多選擇性就差，比如性別，一次過濾只能過濾掉一半的記錄。 Average Length:索引的平均長度。 Columns:索引列的名稱。這里因為我們是非聚集索引，所以會存在兩行，一行為ContactName索引列，一行為ContactName索引列和聚集索引的列值CustomerID組合列。希望能明白這里，索引基礎(chǔ)知識。 通過以上部分信息，SQL Server會知道該部分的數(shù)據(jù)獲取方式那個更快，更有效。
c、統(tǒng)計信息的直方圖信息 我們接著分析第三部分，該列直方圖信息，通過這塊SQL Server能直觀“掌控”該列的數(shù)據(jù)分布內(nèi)容，我們來看
經(jīng)過最后一部分的描述，SQL Server已經(jīng)完全掌控了該表中該字段的數(shù)據(jù)內(nèi)容分布了。想獲取那些數(shù)據(jù)根據(jù)它就可以從容獲取到，并且統(tǒng)計信息是排序了的。 所以當(dāng)我們每次寫的T-SQL語句，它都能根據(jù)統(tǒng)計信息評估出要獲取的數(shù)據(jù)量多少，并且找到最合適的執(zhí)行計劃來執(zhí)行。 我也相信經(jīng)過上面三部分的分析，關(guān)于文章開篇我們提到的那個關(guān)于‘K’和‘Y’的問題會找到答案了，這里不解釋了。 當(dāng)然，如果數(shù)據(jù)量特別大，統(tǒng)計信息的維護也會有小小的失誤，而這時候就需要我們來站出來及時的彌補。
創(chuàng)建統(tǒng)計信息 通過上面的介紹，其實我們已經(jīng)看到了統(tǒng)計信息的強大作用了，所以對于數(shù)據(jù)庫來說它的重要性就不言而喻了，因此，SQL Server會自動的創(chuàng)建統(tǒng)計信息，適時的更新統(tǒng)計信息，當(dāng)然我們可以關(guān)閉掉，但是我非常不建議這么做，原因很簡單：No Do No Die...
這兩項功能默認是開啟的，也就是說SQL Server會自己維護統(tǒng)計信息的準確性。 在日常維護中，我們大可不必要去更改這兩項，當(dāng)然也有比較極端的情況，因為我們知道更新統(tǒng)計信息也是一個消耗，在非常的大的并發(fā)的系統(tǒng)中需要關(guān)掉自動更新功能，這種情況非常的少之又少，所以基本采用默認值就可以。 在以下情況下，SQL Server會自動的創(chuàng)建統(tǒng)計信息： 1、在索引創(chuàng)建時，SQL Server會自動的在索引列上創(chuàng)建統(tǒng)計信息。 2、當(dāng)SQL Server想要使用某些列上的統(tǒng)計信息，發(fā)現(xiàn)沒有的時候，這時候會自動創(chuàng)建統(tǒng)計信息。 3、當(dāng)然，我們也可以手動創(chuàng)建。 比如，自動創(chuàng)建的例子 select * into CustomersStats from Customers sp_helpstats CustomersStats
來添加一個查詢語句，然后再查看統(tǒng)計信息 select * from CustomersStats where ContactName='Hanna Moos' go sp_helpstats CustomersStats go
當(dāng)然，我們也可以根據(jù)自己的情況來手動創(chuàng)建，創(chuàng)建腳本如下 USE [Northwind]
GO
CREATE STATISTICS [CoustomersOne] ON [dbo].[CustomersStats]([CompanyName])
GO
SQL Server也提供了GUI的圖像化操作窗口，方便操作
在以下情況下，SQL Server會自動的更新統(tǒng)計信息： 1、如果統(tǒng)計信息是定義在普通的表格上，那么當(dāng)發(fā)生以下任一種的變化后，統(tǒng)計信息就會被觸發(fā)更新動作。
2、臨時表上也可以有統(tǒng)計信息。這也是很多情況下采用臨時表優(yōu)化的原因之一。其維護策略基本和普通表格一樣，但是表變量不能創(chuàng)建統(tǒng)計信息。 當(dāng)然，我們也可以手動的更新統(tǒng)計信息，更新腳本如下： UPDATE STATISTICS Customers WITH FULLSCAN
文章寫的有點糙....但篇幅已經(jīng)稍長了....先到此吧...后續(xù)我再補充一部分關(guān)于統(tǒng)計信息的內(nèi)容。 關(guān)于調(diào)優(yōu)內(nèi)容太廣泛，我們放在以后的篇幅中介紹，有興趣的可以提前關(guān)注。
參考文獻
有問題可以留言或者私信，隨時恭候有興趣的童鞋加入SQL SERVER的深入研究。共同學(xué)習(xí)，一起進步。
文章最后給出前面幾篇的連接，以下內(nèi)容基本涵蓋我們?nèi)粘Ｖ兴鶎懙牟樵冞\算的分解，看來有必要整理一篇目錄了..... SQL Server調(diào)優(yōu)系列基礎(chǔ)篇 SQL Server調(diào)優(yōu)系列基礎(chǔ)篇（常用運算符總結(jié)） SQL Server調(diào)優(yōu)系列基礎(chǔ)篇（聯(lián)合運算符總結(jié)） SQL Server調(diào)優(yōu)系列基礎(chǔ)篇（并行運算總結(jié)） SQL Server調(diào)優(yōu)系列基礎(chǔ)篇（并行運算總結(jié)篇二） SQL Server調(diào)優(yōu)系列基礎(chǔ)篇（索引運算總結(jié)） SQL Server調(diào)優(yōu)系列基礎(chǔ)篇（子查詢運算總結(jié)） -----------------以下進階篇------------------- SQL Server調(diào)優(yōu)系列進階篇（查詢優(yōu)化器的運行方式） SQL Server調(diào)優(yōu)系列進階篇（查詢語句運行幾個指標值監(jiān)測）
如果您看了本篇博客,覺得對您有所收獲，請不要吝嗇您的“推薦”。 |
|
|
來自： 修行的嘟嘟 > 《數(shù)據(jù)庫》
