淺談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重要的工作之一。
浙公網安備 33010602011771號