對SQLServer錯誤使用聚集索引的優化案例（千萬級資料量）

前言：

半個月前發了文章 SQLServer聚集索引導致的插入效能低

終於等到生產環境休整半天，這篇文章是對前文的實際操作。 

以下正文開始：

---

異常：近期發現偶爾有新資料插入超時。                                                       

分析：插入條碼有多種規則，導致資料表頁面重排，造成效能劇烈下降。                                                       

解決方案：

將基於Barcode的聚集索引更改到ID聚集索引（使新資料始終在尾部新增，避免資料頁面重排），

以損失微量Barcode查詢效能，換取大幅度提升新資料插入效能。     

                                                                                            

為避免快取干擾，進行了4次測試：

1，優化前(Barcode聚集)， ID查詢。

2，重啟，Barcode查詢。

3，優化後(ID聚集)，重啟，ID查詢。

4，重啟，Barcode查詢。

 

以下是基於隨機10條資料的測試用例：    

                                                       

----使用ID索引查詢:                                                   

SELECT *    FROM  [MESDATANow].[dbo].[BarcodeMain]  where ID in

(38126797,37116727,39113797,39116797,37116297

,37116397,37112797,37114797,37113797,37119797)                                                      

Go    

  SELECT *        FROM [MESDATANow].[dbo].BarcodeRecord                                                   

  where ID in (116522700,116527024,116528753,113409209

,113410162,112678638,112679077  ,112942761,112943627,112942850                                               

       ,112943727,116530104,116531318,114035125,114036196)                                                      

                                                      

----使用Barcode索引查詢:                                                 

  SELECT *        FROM [MESDATANow].[dbo].[BarcodeMain]                                                    

  where Barcode in ('LS-21351005000408748','CH2LCHBM13523153AU'

,'CH2LCHKM1351210AEM','CH2LCHKM1351210APG', 'CH2LCHKM1351210AR0','CH2LCHKM13512107FU'

,'LS-21351005000406516','LASLCHWM13721104XF',  'BROLCHBM1391110258','CH2LCHKM1386210DV3')                     

go                                                  

SELECT *  FROM [MESDATANow].[dbo].BarcodeRecord          Where Barcode in ('LS-21351005000408748'

,'CH2LCHBM13523153AU','CH2LCHKM1351210AEM','CH2LCHKM1351210APG'

, 'CH2LCHKM1351210AR0','CH2LCHKM13512107FU','LS-21351005000406516','LASLCHWM13721104XF',                          

         'BROLCHBM1391110258','CH2LCHKM1386210DV3')          

 

資料表	資料量	聚集索引	用Barcode查詢		用ID查詢
			讀取次數	耗時(ms)	讀取次數	耗時(ms)
BarcodeMain	7165446行	Barcode	438	36	468	42
		ID	488	32	412	27
BarcodeRecord	15752004行	Barcode	176	28	444	35
		ID	296	31	236	19

結論： 達到預期目的，耗時增加在可接受範圍。

(應用主要基於Barcode查詢，基於ID的查詢效能大幅提升無實際意義)