建立合適的索引,來降低Select Top Order By帶來的效能問題

Order By向來是為人垢弊的效能殺手,可是有時又不得排序,也許可以透過建立索引來降低效能的影響。

其實如果有Where條件式去抓出所需的少量資料後再Order By並不會有甚麼效能上的問題。但日前Rock遇見一個問題,有一個外購的系統其資料量不大,因此當時配的DB是4核16G RAM。這樣的DB規格對這資料量30MB的系統也算是綽綽有餘。

日前這系統被DDOS攻擊,前端AP Server直接躺平,而後端DB在當下是有撐住。但我事後看了一下DB當時的效能報表時,發現攻擊當下Batch Request/秒 最高也才3000,但是當下的CPU卻也飆到90% Up,Page Lookups也蠻高的,但IO跟Memory當時都沒負載。到底是哪一個環節去導致CPU飆到那樣高呢?

我事後檢視一下該系統首頁皆為一些訊息公告及焦點內容,接這看一下這些取出公告的Stored Procedure會發現,由於首頁一次只要顯示前10筆資料,因此廠商就利用Select Top 10跟Order By來取資料。而大家可以知道Order By是效能殺手,看來勢必要想辦法解決這一個問題。

 

首先我建立一個News資料表並寫入4635筆資料,如下圖所示。

 

資料表內容如下圖所示,流水號(叢集索引)跟公告抬頭,公告內容,還有一個超重要的關鍵就是公告起始時間欄位,如下圖的NewsBeginDate

 

首先我們先Select Top 10不搭配Order By並強制使用叢集索引,可以看見下圖執行計畫會用叢集掃描來取資料。但重點是只取前10筆就結束(下圖紅色圈選處)不會掃完整張Table

 

Select Top 10搭配遞減的Order By NewsID並強制用叢集索引(下圖左邊紅色圈選處),我們可以看見執行計畫跟上一步驟完全一樣,差異只是由遞增排序改為遞減排序而已。

 

上述兩個步驟的IO也極低(如下圖紅色圈選處)。既然IO也低,執行計畫也顯示沒有排序的額外耗損,為何我CPU會飆這樣高呢 ? 讓我們繼續看下去。

 

之前我有提到一個關鍵欄位叫NewsBeginDate,這一個欄位是紀錄本則公告起始日期。也就是說你9月1日可以上傳一則公告並選擇在10月1日才要顯示。因此廠商的Order By就不是只用流水號NewsID(叢集索引)來排序。而是 Order By NewsBeginDate Desc,NewsID Desc(如下圖左邊圈選處)。我們可以看見執行計畫一樣是用叢集索引掃描,但掃秒筆數是4635筆(ALL Table Scan),且多了排序耗損。

 

IO數也高達880個Page。

 

為了解決這個問題,我決定針對NewsBeginDateNewsID欄位來建一個非叢集索引,讓SQL利用該索引掃描。

 

加完索引後,如下圖所示SQL已使用該非叢集索引掃瞄,筆數也是只掃10筆而已,且沒有排序的耗損。

 

IO數更低,也只剩3個Page而已。

我們常常加索引只為了讓SQL在搜尋時可以利用索引搜尋(Seek)增加效能。但上述範例,我加索引是為了讓SQL可以快速掃描(Scan)資料表前10筆的資料來解決Select Top 10 Order By造成的效能問題。Rock會用這種方式來解決也是有評估過的歐,因為該資料表新增修改刪除的頻率極低,98%都是Select動作。且該資料表不大(4600筆才10MB),因此我可以再建一張幾乎包含所有欄位的索引來加速。所以我是利用增加空間來節省時間。如果您也有類似需求,記得要先評估,而不是盲隨歐。

 

我是ROCK

rockchang@mails.fju.edu.tw