統(tǒng)計信息如何影響查詢

下面我們通過一個簡單的例子來看統(tǒng)計信息是如何影響查詢分析器。我建立一個測試表，有兩個INT值的列，其中id為自增，ref上建立非聚集索引，插入100條數(shù)據(jù)，從1到100，再插入9900條等于100的數(shù)據(jù)。圖1中的統(tǒng)計信息就是示例數(shù)據(jù)的統(tǒng)計信息。

此時，我where后使用ref值作為查詢條件，但是給定不同的值，我們可以看出根據(jù)統(tǒng)計信息，查詢分析器做出了不同的選擇，如圖2所示。

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

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

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

根據(jù)公式可以推斷，當(dāng)表中的數(shù)據(jù)量逐漸增大時，密度會越來越小。

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

那么，根據(jù)這個公式，如果我做查詢時，估計的行數(shù)就會為如圖4所示的數(shù)字。

我們來驗證一下這個結(jié)論,如圖5所示。

因此，可以看出，估計的行數(shù)是和實際的行數(shù)有出入的，當(dāng)數(shù)據(jù)分布均勻時，或者數(shù)據(jù)量大時，這個誤差將會變的非常小。

由上面的例子可以看到，查詢分析器由于依賴于統(tǒng)計信息進行查詢，那么過時的統(tǒng)計信息則可能導(dǎo)致低效率的查詢。統(tǒng)計信息既可以由SQL Server來進行管理，也可以手動進行更新，也可以由SQL Server管理更新時手動更新。

當(dāng)開啟了自動更新后，SQL Server監(jiān)控表中的數(shù)據(jù)更改，當(dāng)達到臨界值時則會自動更新數(shù)據(jù)。這個標(biāo)準(zhǔn)是:

上述條件的滿足均會導(dǎo)致統(tǒng)計被更新。

當(dāng)然，我們也可以使用如下語句手動更新統(tǒng)計信息。

UPDATE STATISTICS 表名[索引名]

SQL Server還可以針對不屬于任何索引的列創(chuàng)建統(tǒng)計信息來幫助查詢分析器獲取”估計的行數(shù)“.當(dāng)我們開啟數(shù)據(jù)庫級別的選項“自動創(chuàng)建統(tǒng)計信息”如圖6所示。

當(dāng)這個選項設(shè)置為True時，當(dāng)我們where謂詞指定了不在任何索引上的列時，列的統(tǒng)計信息會被創(chuàng)建，但是會有以下兩種情況例外:

我們可以通過系統(tǒng)視圖sys.stats來查看這些統(tǒng)計信息,如圖7所示。

當(dāng)然，也可以通過如下語句手動創(chuàng)建統(tǒng)計信息:

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

本文簡單談了統(tǒng)計信息對于查詢路徑選擇的影響。過時的統(tǒng)計信息很容易造成查詢性能的降低。因此，定期更新統(tǒng)計信息是DBA重要的工作之一。