通過手動創建統計信息優化sql查詢性能案例

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

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

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

如下一張業務表，主要看兩個“狀態”欄位，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 壓根無法預估到符合條件數據的準確分佈，從而無法做出合理的執行計劃的情況

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