通過手動建立統計資訊優化sql查詢性能案例

原文: 通過手動建立統計資訊優化sql查詢性能案例

本質原因在于：SQL Server 統計資訊隻包含複合索引的第一個列的資訊，而不包含複合索引資料組合的資訊

來源于工作中的一個實際問題，

這裡是組合列資料不均勻導緻查詢無法預估資料行數，進而導緻無法選擇合理的執行計劃導緻性能低下的情況

我這裡把問題簡單化，主要是為了說明問題

如下一張業務表，主要看兩個“狀态”字段，BusinessStatus1 和 BusinessStatus2

create table BusinessTable
(
    Id int identity(1,1),
    Col2 varchar(50),
    Col3 varchar(50),
    Col4 varchar(50),
    BusinessStatus1 tinyint,
    BusinessStatus2 tinyint,
    CreateDate Datetime
)
GO

--向測試表中寫入資料：

begin tran
    declare @i int
    set @i=0
    while @i<500000
    begin
        insert into BusinessTable values (NEWID(),NEWID(),NEWID(),1,10,GETDATE()-RAND()*1000)
        insert into BusinessTable values (NEWID(),NEWID(),NEWID(),1,20,GETDATE()-RAND()*1000)
        insert into BusinessTable values (NEWID(),NEWID(),NEWID(),1,30,GETDATE()-RAND()*1000)
        
        insert into BusinessTable values (NEWID(),NEWID(),NEWID(),2,20,GETDATE()-RAND()*1000)
        insert into BusinessTable values (NEWID(),NEWID(),NEWID(),2,30,GETDATE()-RAND()*1000)
        insert into BusinessTable values (NEWID(),NEWID(),NEWID(),2,40,GETDATE()-RAND()*1000)

        insert into BusinessTable values (NEWID(),NEWID(),NEWID(),3,30,GETDATE()-RAND()*1000)
        insert into BusinessTable values (NEWID(),NEWID(),NEWID(),3,40,GETDATE()-RAND()*1000)
        insert into BusinessTable values (NEWID(),NEWID(),NEWID(),3,50,GETDATE()-RAND()*1000)


        set @i=@i+1
    end
commit

--插入一條特殊資料，也就是實際業務場景中：
insert into BusinessTable values (NEWID(),NEWID(),NEWID(),3,10,GETDATE()-RAND()*1000)

       

--測試資料的特點是：

--BusinessStatus1 的分布位：1，2,3,
--BusinessStatus2 的分布位：10，20,30,40,50

--目前資料的對應關系，

--但是注意插入的一條特殊資料：
--BusinessStatus1 和 BusinessStatus2 的組合為：BusinessStatus1=3 and BusinessStatus2=10，在451W條資料中是唯一的一個組合

--建立如下索引：
Create Clustered index idx_createDate on BusinessTable(CreateDate)

Create Index idx_status on BusinessTable(BusinessStatus1,BusinessStatus2)      

進行如下查詢，就是查詢那條所謂的特殊資料

select * 
from BusinessTable 
where BusinessStatus1=3 and BusinessStatus2=10
      

發現執行計劃如下:走的是全表掃描，IO代價也不小，

這種情況下，明明隻有一條資料，卻要走全表掃描

（實際業務中類似資料也不僅隻有一條這麼巧，但是在千萬級的表中，符合類似條件的資料很少，

打個比方好了解一點，就像訂單表一樣，訂單是退訂狀态，且尚未退款，這種資料的分布是少之又少吧

隻是舉例，不要較真）

上面查詢的IO資訊

再通過強制索引提示的情況下，發現同樣的查詢，IO有一個非常大的下降

分析上述sql為什麼不走索引？因為畢竟符合條件的資料隻有一條，走全表掃描代價也過于大了，尤其是實際情況中，業務表更大，邏輯也沒有這麼直白

這個還要從索引統計資訊說起，在符合索引中，索引統計資訊隻是統計前導列的，對于組合列的分布，sqlserver是無法預估到的，這一點可以通過第一個查詢的執行計劃發現

sqlserver隻是能夠預估到 BusinessStatus1 =3 的情況下的資料分布，但是無法預估到 BusinessStatus1=3 and BusinessStatus2=10這個組合情況下的資料分布情況

當然通過統計資訊也可以看到，統計資訊隻記錄了BusinessStatus1的列的資料分布情況，但是實際執行的過程中，無法預估BusinessStatus1=3 and BusinessStatus2=10的準确分布

找到了問題的原因，就容易解決了，既然sqlserver無法預估到BusinessStatus1=3 and BusinessStatus2=10這個組合條件的資料分布請，

那麼就建立一個過濾統計資訊，讓sqlserver準确地知道這個條件下資料的分布請，就容易做出相對準确的執行計劃了

通過如下語句，建立一個該條件的統計資訊

create statistics BusinessTableFilterStatistics 
on BusinessTable(BusinessStatus1,BusinessStatus2)
where BusinessStatus1=3 and BusinessStatus2=10


--建立完統計資訊之後注意要做個更新
UPDATE STATISTICS BusinessTable BusinessTableFilterStatistics with fullscan      

建立完統計資訊之後，發現表上會增加一個剛剛建立的統計資訊

現在再來看這個查詢的執行計劃情況，發現其按照預期的走了索引

同時觀察起IO情況，也有一個大幅度的下降

總結：

以上通過手動建立統計資訊，來促使sqlserver在生成執行計劃的時候，準确地知道資料的分布情況，做出較為優化的執行計劃，在某些特殊的情況下，可以作為優化的一個考慮方向

後記：

或許有人認為這個問題該歸結于parameter sniff的問題，其實這個問題跟parameter sniff還不太一樣（當然也有一點像）

通常情況下，所說的parameter sniff問題是單列資料分布不均勻的情況下，因為執行計劃重用導緻性能地下的一個現象，重點是執行計劃的不合理重用

這裡的問題在于，由于統計資訊的資料計算方式，sqlserver 壓根無法預估到符合條件資料的準确分布，進而無法做出合理的執行計劃的情況

當然這種情況也比較特殊，在強制索引提示以外，可以通過手動建立統計資訊來達到優化的目的