[SQL SERVER] 多個索引 vs 單一寬索引

2014-07-13

7147
0
SQL_SERVER

實務上最快提高查詢效能莫過於建立和設計正當索引，

依照相關TSQL建立設計正確索引所帶來效能效益我個人認為是最高的，

這篇來討論一個索引設計問題，

我們知道大部分情況下index seek優於index scan(大海撈針)，

假設今天有一TSQL如下(where 條件有三個):

select POLICY_NO from QTPLG where tran_date>'2008-01-01 00:00:00' and tran_date<='2008-06-30 23:59:59' and mode_prem>='2000.00' and mode_prem<='2100' and POLICY_TYPE=N'J'

設計1:依照條件建立三個nonclustered index

create index idx1 on QTPLG(tran_date) create index idx2 on QTPLG(mode_prem) create index idx3 on QTPLG(POLICY_TYPE)

因為需要使用index join，所以TSQL改寫如下

select POLICY_NO from QTPLG with (index(idx3,idx1,idx2)) where tran_date>'2008-01-01 00:00:00' and tran_date<='2008-06-30 23:59:59' and mode_prem>='2000.00' and mode_prem<='2100' and POLICY_TYPE=N'J'

執行計畫

雖然一開始都個別使用index seek 運算子，但hash join運算子成本高的可怕，

並且出現遺漏索引警告。

統計資料

設計2:個別建立三個篩選索引(SQL2008後提供)

create index idx1 on QTPLG(tran_date) include(POLICY_NO) WHERE tran_date >'2008-01-01 00:00:00' and tran_date<='2008-06-30 23:59:59' and mode_prem>='2000.00' and mode_prem<='2100' and POLICY_TYPE=N'J' create index idx2 on QTPLG(mode_prem) include(POLICY_NO) WHERE tran_date >'2008-01-01 00:00:00' and tran_date<='2008-06-30 23:59:59' and mode_prem>='2000.00' and mode_prem<='2100' and POLICY_TYPE=N'J' create index idx3 on QTPLG(POLICY_TYPE) include(POLICY_NO) WHERE tran_date >'2008-01-01 00:00:00' and tran_date<='2008-06-30 23:59:59' and mode_prem>='2000.00' and mode_prem<='2100' and POLICY_TYPE=N'J'

執行計畫

整體執行計畫和設計1相差不多(少了點陣圖運算子)，但依然還是可以看到hash join高成本和遺漏索引資訊。

ps:以這篩選索引來說少了彈性，只要查詢條件一有改變，篩選索引馬上無用武之地，

如果資料表沒有其他索引的話將導致出現無法建立執行計畫錯誤。

統計資料

使用篩選索引降低很多I/O和CPU。

設計3:建立單一寬索引 with include

create index idx1 on QTPLG(POLICY_TYPE,tran_date,mode_prem) include(POLICY_NO)

執行計畫

執行計畫複雜度降低，而且也沒有任何遺漏索引訊息，但是依然還是使用平行執行計畫。

統計資料

I/O和CPU都有比面兩種設計來的低很多。

設計3.1:建立單一寬索引 without include

create index idx1 on QTPLG(tran_date,mode_prem,POLICY_TYPE,POLICY_NO)

ps:執行計畫和統計資料都和設計3相同，但是該索引大小比設計3來的大，

效率比設計3來的低(acid問題)。

設計4:建立單一篩選索引

create index idx1 on QTPLG(POLICY_TYPE,tran_date,mode_prem) include(POLICY_NO) WHERE tran_date >'2008-01-01 00:00:00' and tran_date<='2008-06-30 23:59:59' and mode_prem>='2000.00' and mode_prem<='2100' and POLICY_TYPE=N'J'

執行計畫

執行計畫不複雜是我們樂於看到的結果。

統計資料

I/O邏輯讀取目前是最低的，不過這篩選索引設計缺點前面有提到過了(缺乏彈性)。

調校

為了兼具彈性和效能，我決定從設計3下手調校，設計3執行計畫可看到是使用平行計畫，

查看該執行計劃發現最大問題在於資料型別不同所導致，

因為設計4執行計畫只有單一索引搜尋運算子，換句話說該執行計畫或許是最佳執行計畫，

修正TSQL並執行設計3索引

declare @p1 datetime,@p2 datetime,@p3 numeric(12,2),@p4 numeric(12,2),@p5 nvarchar(10) set @p1='2008-01-01 00:00:00' set @p2='2008-06-30 23:59:59' set @p3=2000.00 set @p4=2100.00 set @p5=N'J' select POLICY_NO from QTPLG where tran_date>@p1 and tran_date<=@p2 and mode_prem>=@p3 and mode_prem<=@p4 and POLICY_TYPE=@p5

create index idx1 on QTPLG(tran_date,mode_prem,POLICY_TYPE) include(POLICY_NO)

執行計畫