統計資訊對執行計畫的影響(二)

上一篇文章 統計資訊對執行計畫的影響(一) 示範了統計資訊對串連方式的影響，這一篇將給大家示範統計資訊對單表資料擷取方式的影響

在上次的測試DB上執行以下代碼建立測試表及插入資料

--建立測試表CREATE TABLE CHECK2_T3(    ID INT,    COL1  CHAR(800),    COL2  INT,    COL3  CHAR(5000))---向表中插入資料BEGIN TRANDECLARE @I INT,@COL1 CHAR(1)=''SET @I=1WHILE @I<=10000 BEGIN    IF @I%4=0     BEGIN SET @COL1='B' END    ELSE IF @I%250=0    BEGIN SET @COL1='C' END    ELSE    BEGIN SET @COL1='A' END        INSERT INTO CHECK2_T3 SELECT @I,@COL1,cast(rand()*1000 AS INT),'X'    SET @I=@I+1 ENDCOMMIT TRAN

然後執行下面代碼在COL1上建立非叢集索引，我們知道建立索引的時候自動建立與之相關的統計資訊，WITH(STATISTICS_NORECOMPUTE = ON)是禁用其統計資訊自動更新

--在COL1列上建立非叢集索引，並禁用自動更新統計資訊CREATE NONCLUSTERED INDEX NCIX_COL1 ON CHECK2_T3(COL1) WITH(STATISTICS_NORECOMPUTE = ON)--查看剛建立的索引的統計資訊DBCC SHOW_STATISTICS(CHECK2_T3,NCIX_COL1)

然後再執行以下sql，看到執行計畫是用的索引尋找

--COL1為'C'的只有20行，執行以下語句查詢採用索引尋找SELECT AVG(COL2) FROM CHECK2_T3 WHERE COL1='C'

再往表插入入30W條C記錄，因為已經禁用自動更新統計資訊，所以統計資訊沒有變化

BEGIN TRANDECLARE @I INTSET @I=1WHILE @I<=300000 BEGIN        INSERT INTO CHECK2_T3 SELECT 100001,'C',cast(rand()*1000 AS INT),'XD'    SET @I=@I+1 ENDCOMMIT TRAN

這時C的記錄數變成30W+,接著執行以下兩條sql，②的sql使用了表提示 ，目的是讓查詢走全表掃描，①的執行計畫和之前的一樣，這時RID運算子的開銷就相當高，以下兩句對比，表掃描體現效能優勢

以下兩句在我本機測試的速度②比①快了一倍，多次執行注意清空緩衝

① SELECT AVG(COL2) FROM CHECK2_T3 WHERE COL1='C'② SELECT AVG(COL2) FROM CHECK2_T3 WITH(INDEX(0)) WHERE COL1='C'

 

①的執行計畫之所以沒有走表掃描，是因為我們沒有更新統計資料,我們更新統計資料後再次執行①，發現執行計畫變成了表掃描

到這裡示範完成。

總結，相比統計資訊對串連方式的影響，對單表查詢方式的影響效果不那麼顯著，因為表的大小變化對統計資訊更新前後的運算子都起作用。如上，雖然表掃描提升了效率，但依然不能被我們接受，我們希望更快，最好避開對原表資料頁的查詢，那就可以在COL1索引裡包含COL2欄位，這樣直接掃描COL1欄位上的索引便可以完成這個查詢，正如執行計畫上綠色提示行“缺少索引”。建立這個包含列索引後，sq效率大大提升，可以開啟STATISTICS IO觀察邏輯讀大量減少。

另外需要說的是，通常情況下sqlserver提示的缺失索引都能帶來查詢效率的提升，但你也應該明白每建立一個索引都會降低系統寫的效率，關於這些不在本篇文章討論。我會在以後的專門系列裡分享一些最佳化手法及注意事項。