淺談SQL Server中統計對於查詢的影響

簡介

    SQL Server查詢分析器是基於開銷的。通常來講，查詢分析器會根據謂詞來確定該如何選擇高效的查詢路線,比如該選擇哪個索引。而每次查詢分析器尋找路徑時，並不會每一次都去統計索引中包含的行數，值的範圍等，而是根據一定條件建立和更新這些資訊後儲存到資料庫中，這也就是所謂的統計資訊。

 

如何查看統計資訊

    查看SQL Server的統計資訊非常簡單，使用如下指令：

    DBCC SHOW_STATISTICS('表名','索引名')

 

    所得到的結果1所示。

   

    圖1.統計資訊

 

統計資訊如何影響查詢

    下面我們通過一個簡單的例子來看統計資訊是如何影響查詢分析器。我建立一個測試表，有兩個INT值的列，其中id為自增，ref上建立非叢集索引，插入100條資料，從1到100，再插入9900條等於100的資料。圖1中的統計資訊就是樣本資料的統計資訊。

    此時，我where後使用ref值作為查詢條件，但是給定不同的值，我們可以看出根據統計資訊，查詢分析器做出了不同的選擇，2所示。

   

     圖2.根據不同的謂詞，查詢最佳化工具做了不同的選擇

 

     其實，對於查詢分析器來說，柱狀圖對於直接可以確定的謂詞非常管用，這些謂詞比如:

    where date = getdate()
    where id= 12345
    where monthly_sales < 10000 / 12
    where name like “Careyson” + “%”

 

    但是對於比如

    where price = @vari
    where total_sales > (select sum(qty) from sales)
    where a.id =b.ref_id

    where col1 =1 and col2=2

 

    這類在運行時才能知道值的查詢，採樣步長就明顯不是那麼好用了。另外，上面第四行如果謂詞是兩個查詢條件，使用採樣步長也並不好用。因為無論索引有多少列，採樣步長僅僅儲存索引的第一列。當柱狀圖不再好用時，SQL Server使用密度來確定最佳的查詢路線。

    密度的公式是：1/表中唯一值的 個數。當密度越小時，索引越容易被選中。比1中的第二個表，我們可以通過如下公式來計算一下密度:

   

    圖3.某一列的密度

 

    根據公式可以推斷，當表中的資料量逐漸增大時，密度會越來越小。

    對於那些不能根據採樣步長做出選擇的查詢，查詢分析器使用密度來估計行數,這個公式為:估計的行數=表中的行數*密度

    那麼，根據這個公式，如果我做查詢時，估計的行數就會為4所示的數字。

   

    圖4.估計的行數

 

    我們來驗證一下這個結論,5所示。

   

    圖5.估計的行數

 

    因此，可以看出，估計的行數是和實際的行數有出入的，當資料分布均勻時，或者資料量大時，這個誤差將會變的非常小。

 

統計資訊的更新

    由上面的例子可以看到，查詢分析器由於依賴於統計資訊進行查詢，那麼過時的統計資訊則可能導致低效率的查詢。統計資訊既可以由SQL Server來進行管理，也可以手動進行更新，也可以由SQL Server管理更新時手動更新。

    當開啟了自動更新後，SQL Server監控表中的資料更改，當達到臨界值時則會自動更新資料。這個標準是:

•     向空表插入資料時
•     少於500行的表增加500行或者更多
•     當表中行多於500行時，資料的變化量大於20%時

    上述條件的滿足均會導致統計被更新。

    當然，我們也可以使用如下語句手動更新統計資料。

     

     UPDATE STATISTICS 表名[索引名]

 

列級統計資訊

    SQL Server還可以針對不屬於任何索引的列建立統計資訊來協助查詢分析器擷取”估計的行數“.當我們開啟資料庫層級的選項“自動建立統計資訊”6所示。

   

    圖6.自動建立統計資訊

 

   當這個選項設定為True時，當我們where謂詞指定了不在任何索引上的列時，列的統計資訊會被建立，但是會有以下兩種情況例外:

•     建立統計資訊的成本超過產生查詢計劃的成本
•     當SQL Server忙時不會自動產生統計資訊

 

   我們可以通過系統檢視表sys.stats來查看這些統計資訊,7所示。

   

    圖7.通過系統檢視表查看統計資訊

 

    當然，也可以通過如下語句手動建立統計資訊:

    CREATE STATISTICS 統計名稱 ON 表名 (列名 [,...n])

 

總結

    本文簡單談了統計資訊對於查詢路徑選擇的影響。過時的統計資訊很容易造成查詢效能的降低。因此，定期更新統計資料是DBA重要的工作之一。

   轉自：http://www.cnblogs.com/CareySon/archive/2012/05/14/HowStatisticImpactQuery.html簡介

    SQL Server查詢分析器是基於開銷的。通常來講，查詢分析器會根據謂詞來確定該如何選擇高效的查詢路線,比如該選擇哪個索引。而每次查詢分析器尋找路徑時，並不會每一次都去統計索引中包含的行數，值的範圍等，而是根據一定條件建立和更新這些資訊後儲存到資料庫中，這也就是所謂的統計資訊。

 

如何查看統計資訊

    查看SQL Server的統計資訊非常簡單，使用如下指令：

    DBCC SHOW_STATISTICS('表名','索引名')

 

    所得到的結果1所示。

   

    圖1.統計資訊

 

統計資訊如何影響查詢

    下面我們通過一個簡單的例子來看統計資訊是如何影響查詢分析器。我建立一個測試表，有兩個INT值的列，其中id為自增，ref上建立非叢集索引，插入100條資料，從1到100，再插入9900條等於100的資料。圖1中的統計資訊就是樣本資料的統計資訊。

    此時，我where後使用ref值作為查詢條件，但是給定不同的值，我們可以看出根據統計資訊，查詢分析器做出了不同的選擇，2所示。

   

     圖2.根據不同的謂詞，查詢最佳化工具做了不同的選擇

 

     其實，對於查詢分析器來說，柱狀圖對於直接可以確定的謂詞非常管用，這些謂詞比如:

    where date = getdate()
    where id= 12345
    where monthly_sales < 10000 / 12
    where name like “Careyson” + “%”

 

    但是對於比如

    where price = @vari
    where total_sales > (select sum(qty) from sales)
    where a.id =b.ref_id

    where col1 =1 and col2=2

 

    這類在運行時才能知道值的查詢，採樣步長就明顯不是那麼好用了。另外，上面第四行如果謂詞是兩個查詢條件，使用採樣步長也並不好用。因為無論索引有多少列，採樣步長僅僅儲存索引的第一列。當柱狀圖不再好用時，SQL Server使用密度來確定最佳的查詢路線。

    密度的公式是：1/表中唯一值的 個數。當密度越小時，索引越容易被選中。比1中的第二個表，我們可以通過如下公式來計算一下密度:

   

    圖3.某一列的密度

 

    根據公式可以推斷，當表中的資料量逐漸增大時，密度會越來越小。

    對於那些不能根據採樣步長做出選擇的查詢，查詢分析器使用密度來估計行數,這個公式為:估計的行數=表中的行數*密度

    那麼，根據這個公式，如果我做查詢時，估計的行數就會為4所示的數字。

   

    圖4.估計的行數

 

    我們來驗證一下這個結論,5所示。

   

    圖5.估計的行數

 

    因此，可以看出，估計的行數是和實際的行數有出入的，當資料分布均勻時，或者資料量大時，這個誤差將會變的非常小。

 

統計資訊的更新

    由上面的例子可以看到，查詢分析器由於依賴於統計資訊進行查詢，那麼過時的統計資訊則可能導致低效率的查詢。統計資訊既可以由SQL Server來進行管理，也可以手動進行更新，也可以由SQL Server管理更新時手動更新。

    當開啟了自動更新後，SQL Server監控表中的資料更改，當達到臨界值時則會自動更新資料。這個標準是:

•     向空表插入資料時
•     少於500行的表增加500行或者更多
•     當表中行多於500行時，資料的變化量大於20%時

    上述條件的滿足均會導致統計被更新。

    當然，我們也可以使用如下語句手動更新統計資料。

     

     UPDATE STATISTICS 表名[索引名]

 

列級統計資訊

    SQL Server還可以針對不屬於任何索引的列建立統計資訊來協助查詢分析器擷取”估計的行數“.當我們開啟資料庫層級的選項“自動建立統計資訊”6所示。

   

    圖6.自動建立統計資訊

 

   當這個選項設定為True時，當我們where謂詞指定了不在任何索引上的列時，列的統計資訊會被建立，但是會有以下兩種情況例外:

•     建立統計資訊的成本超過產生查詢計劃的成本
•     當SQL Server忙時不會自動產生統計資訊

 

   我們可以通過系統檢視表sys.stats來查看這些統計資訊,7所示。

   

    圖7.通過系統檢視表查看統計資訊

 

    當然，也可以通過如下語句手動建立統計資訊:

    CREATE STATISTICS 統計名稱 ON 表名 (列名 [,...n])

 

總結

    本文簡單談了統計資訊對於查詢路徑選擇的影響。過時的統計資訊很容易造成查詢效能的降低。因此，定期更新統計資料是DBA重要的工作之一。