RiCo技術農場

後續文章將發佈在https://medium.com/ricos-note

2010-01-22

[SQL SERVER][Memo]Index組合比較測試

[SQL SERVER][Memo]Index組合比較測試

我想大家都會建立Index，但建立Index的組合卻有很多種

而通常針對一句效能差的Sql Statement，也都是加Index居多

但有時所建的Index並不是最佳組合，這裡我大概測試一下各種組合

順便記錄一下。

 

Table初始相關資訊

image 資料總筆數:5008907

image PK

image Index

假設有一句SQL Statement需要調校，搜尋資料筆數有1299071，我們準備來測試各種組合狀況 

select t1.POLICY_NO,t1.TRAN_DATE from dbo.QTPLG t1 
where t1.TRAN_DATE between '2007-01-01' and '2008-01-01' 
order by t1.TRAN_DATE desc

image

Init

執行計畫

clip_image002

花費時間

clip_image002[5]

A1 Noncluster index(TRAN_DATE) desc

CREATE NONCLUSTERED INDEX nidx_tran_date

ON dbo.QTPLG (TRAN_DATE desc);

image

clip_image002[8]

clip_image004

A2 Noncluster index(TRAN_DATE) asc

Drop index nidx_tran_date on dbo.QTPLG
CREATE NONCLUSTERED INDEX nidx_tran_date 
ON dbo.QTPLG (TRAN_DATE asc);

image

clip_image002[10]

clip_image004[5]

B1 Cluster index(TRAN_DATE) desc

CREATE CLUSTERED INDEX cidx_tran_date 
    ON dbo.QTPLG (TRAN_DATE desc);

 image

clip_image002[12]

clip_image004[7]

B2 Cluster index(TRAN_DATE) asc

Drop index cidx_tran_date on dbo.QTPLG
   CREATE CLUSTERED INDEX cidx_tran_date 
    ON dbo.QTPLG (TRAN_DATE asc);

 image

clip_image002[14]

clip_image004[9]

C1 Noncluster index(POLICY_NO) desc

CREATE NONCLUSTERED INDEX nidx_policy_no 
ON dbo.QTPLG (POLICY_NO desc);

image

clip_image002[16]出現遺漏索引，看來這索引建了也不會提高多大效果。

clip_image004[11]

D1 Cluster index(POLICY_NO) desc

CREATE CLUSTERED INDEX cidx_policy_no 
ON dbo.QTPLG (POLICY_NO desc);

 image

clip_image002[18]

clip_image004[13]

D2 Cluster index(POLICY_NO) asc

CREATE CLUSTERED INDEX cidx_policy_no 
ON dbo.QTPLG (POLICY_NO asc);

image

clip_image002[20]這組合看來也是來浪費硬碟空間的。

clip_image004[15]

E1 Noncluster index(TRAN_DATE, POLICY_NO) desc

CREATE NONCLUSTERED INDEX nidx_tran_date 
    ON dbo.QTPLG (TRAN_DATE desc, POLICY_NO desc);

 image

clip_image002[22]

clip_image004[17]

E2 Noncluster index(TRAN_DATE, POLICY_NO) asc

CREATE NONCLUSTERED INDEX nidx_tran_date 
    ON dbo.QTPLG (TRAN_DATE asc, POLICY_NO asc);

 image

clip_image002[24]

clip_image004[19]

F1 Noncluster index(POLICY_NO, TRAN_DATE) desc

Drop index nidx_tran_date on dbo.QTPLG
CREATE NONCLUSTERED INDEX nidx_policy_no 
    ON dbo.QTPLG (POLICY_NO desc, TRAN_DATE desc);

 image

clip_image002[26]又是一個浪費空間的組合。

clip_image004[21]

G1 Noncluster(TRAN_DATE) include(POLICY_NO) desc

CREATE NONCLUSTERED INDEX nidx_tran_date 
    ON dbo.QTPLG (TRAN_DATE desc) INCLUDE (POLICY_NO);

image

clip_image002[28]

clip_image004[23]

G2 Noncluster(TRAN_DATE) include(POLICY_NO) asc

CREATE NONCLUSTERED INDEX nidx_tran_date 
    ON dbo.QTPLG (TRAN_DATE asc) INCLUDE (POLICY_NO);

 image

clip_image002[30]

clip_image004[25]

H1 Cluster(TRAN_DATE) desc +noncluster(POLICY_NO) desc

CREATE CLUSTERED INDEX cidx_tran_date
ON dbo.QTPLG ([TRAN_DATE] desc)
CREATE NONCLUSTERED INDEX nidx_policy_no 
    ON dbo.QTPLG (POLICY_NO desc);
GO

 image

clip_image002[32]

clip_image004[27]

H2 Cluster(TRAN_DATE) desc +noncluster(POLICY_NO) asc

CREATE CLUSTERED INDEX cidx_tran_date
ON dbo.QTPLG ([TRAN_DATE] asc)
CREATE NONCLUSTERED INDEX nidx_policy_no 
    ON dbo.QTPLG (POLICY_NO asc);
GO

 image

clip_image002[34]

clip_image004[29]

I1 Cluster(POLICY_NO)+noncluster(TRAN_DATE) desc

Drop index nidx_policy_no on dbo.QTPLG
Drop index cidx_tran_date on dbo.QTPLG
CREATE CLUSTERED INDEX cidx_policy_no
ON dbo.QTPLG (POLICY_NO desc)
CREATE NONCLUSTERED INDEX nidx_tran_date 
    ON dbo.QTPLG (TRAN_DATE desc);
GO

 image

clip_image002[36]

clip_image004[31]

I2 Cluster(POLICY_NO)+noncluster(TRAN_DATE) asc

Drop index nidx_policy_no on dbo.QTPLG
Drop index cidx_tran_date on dbo.QTPLG
CREATE CLUSTERED INDEX cidx_policy_no
ON dbo.QTPLG (POLICY_NO asc)
CREATE NONCLUSTERED INDEX nidx_tran_date 
    ON dbo.QTPLG (TRAN_DATE asc);
GO

 image

clip_image002[38]

clip_image004[33]

結果一覽表

image

image

image

經過上面測試，我們挑選(E和I)效能最好的組合，來測試資料壓縮新技術，看看效能是否會有增長。

(SQL2008企業版才支援資料壓縮)

J1 Row Index Compression

CREATE NONCLUSTERED INDEX nidx_tran_date 
    ON dbo.QTPLG (TRAN_DATE asc, POLICY_NO asc)
WITH ( DATA_COMPRESSION = ROW ) ;

 image

clip_image002[40]

clip_image004[35]

J2 Row Index Compression

CREATE CLUSTERED INDEX cidx_policy_no
ON dbo.QTPLG (POLICY_NO desc)
WITH ( DATA_COMPRESSION = ROW ) ;

CREATE NONCLUSTERED INDEX nidx_tran_date 
    ON dbo.QTPLG (TRAN_DATE desc)
WITH ( DATA_COMPRESSION = ROW ) ;
GO

 image

clip_image002[42]

clip_image004[37]

K1 Page Index Compression

CREATE NONCLUSTERED INDEX nidx_tran_date 
    ON dbo.QTPLG (TRAN_DATE asc, POLICY_NO asc)
WITH ( DATA_COMPRESSION = PAGE ) ;

image

clip_image002[44]

clip_image004[39]

K2 Page Index Compression

CREATE CLUSTERED INDEX cidx_policy_no
ON dbo.QTPLG (POLICY_NO desc)
WITH ( DATA_COMPRESSION = ROW ) ;

CREATE NONCLUSTERED INDEX nidx_tran_date 
    ON dbo.QTPLG (TRAN_DATE desc)
WITH ( DATA_COMPRESSION = PAGE ) ;
GO

image

clip_image002[46]

clip_image004[41]

結果一覽表

image

結論:

在本測試中，只是大概測試資料壓縮效果，雖然增加查詢和CPU時間，

但資料列壓縮確實有減少一定的IO讀取量和記憶體(剛好型別有支援)，

所以還是要有所取捨，對症下藥才可藥到病除。

最後的Index最佳組合

E2:建立非叢集索引，欄位順序為TRAN_DATE、POLICY_NO

CREATE NONCLUSTERED INDEX nidx_tran_date 
    ON dbo.QTPLG (TRAN_DATE asc, POLICY_NO asc);

Index次佳組合

I1:POLICY_NO建立叢集索引，TRAN_DATE建立非叢集索引 

CREATE CLUSTERED INDEX cidx_policy_no
ON dbo.QTPLG (POLICY_NO desc)
CREATE NONCLUSTERED INDEX nidx_tran_date 
    ON dbo.QTPLG (TRAN_DATE desc);
GO

由此可知，建立Index選擇欄位順序是相當重要的，

請先依Where最右邊開始選擇，其次在包含Select欄位，

如果我選用了E2組合建立Index，也就代表該Table沒有任何叢集索引但也能大大提高效能，

但不管如何決定叢集索引還是非常重要的。

RiCo技術農場粉絲頁

標籤雲

系列文章

最新留言