RiCo技術農場

前幾天幫以前同事調校SQL 2012效能，整個系統雖然都沒有任何遺漏索引，但是相關複合索引選擇性卻不夠嚴謹

說白話一點就是索引設計不良，由於SQL Server中只會對第一個欄位(index key)存放統計值，

所以如果第一個欄位的選擇性過低或是查詢類型不符，那麼其實該索引對查詢效能就無明顯助益，下面我簡單測試

TSQL

select ProductID,ReferenceOrderLineID,TransactionType,Quantity

,TransactionDate

from [dbo].[TransactionHistory]

where

ProductID>=784 and ProductID<=990

and

ReferenceOrderLineID=0

and TransactionType='W'

and Quantity>0

 

範圍查詢不適合使用nonclustered index(適用clustered index)，ReferenceOrderLineID和TransactionType兩個欄位比較起來

ReferenceOrderLineID選擇性比較高，所以第一欄位使用ReferenceOrderLineID會比較好，

下面我建立3種nonclustered index實際比較看看效能如何

 

create index idx1 on [dbo].[TransactionHistory](ProductID,TransactionType,Quantity,ReferenceOrderLineID)

include(TransactionDate)

create index idx2 on [dbo].[TransactionHistory](ReferenceOrderLineID,TransactionType,ProductID,Quantity)

include(TransactionDate)

create index idx3 on [dbo].[TransactionHistory](TransactionType,ReferenceOrderLineID,ProductID,Quantity)

include(TransactionDate)

 

 

--idx1 原本使用索引
select ProductID,ReferenceOrderLineID,TransactionType,Quantity

,TransactionDate

from [dbo].[TransactionHistory] with(index(idx1))

where

ProductID>=784 and ProductID<=990

and

ReferenceOrderLineID=0

and TransactionType='W'

and Quantity>0

--idx2
select ProductID,ReferenceOrderLineID,TransactionType,Quantity

,TransactionDate

from [dbo].[TransactionHistory] with(index(idx2))

where

ProductID>=784 and ProductID<=990

and

ReferenceOrderLineID=0

and TransactionType='W'

and Quantity>0

--idx3
select ProductID,ReferenceOrderLineID,TransactionType,Quantity

,TransactionDate

from [dbo].[TransactionHistory] with(index(idx3))

where

ProductID>=784 and ProductID<=990

and

ReferenceOrderLineID=0

and TransactionType='W'

and Quantity>0

 

可以看到原本使用的索引邏輯讀取較高(309)，以這TSQL來說，

第一個欄位選擇ReferenceOrderLineID(選擇性較高)會是較好設計。