前言：
半個月前發了文章 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的查詢效能大幅提升無實際意義)