史丹利好熱

第二篇，排序欄位的選擇 

• 取總筆數的效能(COUNT(1) OVER() VS CTE)
• 排序條件選擇
• 取資料序號(ROW_NUMBER() OVER(ORDER BY NOTHING))
• Offset Fetch Vs Row_number(總筆數及資料序號)

 

微軟DOCS

叢集索引將資料表或檢視中的資料列依其索引鍵值排序與儲存。
只有當資料表包含叢集索引時，資料表中的資料列才會以排序順序儲存。 
當資料表有叢集索引時，資料表又稱為叢集資料表。 如果資料表沒有任何叢集索引，它的資料列就儲存在未排序的結構中，這個結構稱為堆積(heap)。

這次實驗我們還是用AdventureWorks2014資料庫中的PRODUCTION.TRANSACTIONHISTORY資料表作DB分頁測試，3次的執行，排序條件依序是:

• TransactionID(叢集索引)
• ProductID(非叢集索引)
• ModifiedDate(非索引欄位)

但先不抓取總筆數。

 

---

實驗

--叢集索引、非叢集索引及非索引欄位來排序
SET STATISTICS IO ON 
SET STATISTICS TIME ON 
DECLARE @PageSize INT = 10;
DECLARE @PageNumber INT = 10;

--叢集索引
SELECT   *
	FROM PRODUCTION.TRANSACTIONHISTORY
ORDER BY TransactionID
OFFSET (@PageNumber - 1) * (@PageSize) ROWS
FETCH NEXT @PageSize ROWS ONLY;


--非叢集索引
SELECT   *
	FROM PRODUCTION.TRANSACTIONHISTORY
ORDER BY ProductID
OFFSET (@PageNumber - 1) * (@PageSize) ROWS
FETCH NEXT @PageSize ROWS ONLY;

--非索引條件
SELECT   *
	FROM PRODUCTION.TRANSACTIONHISTORY
ORDER BY ModifiedDate
OFFSET (@PageNumber - 1) * (@PageSize) ROWS
FETCH NEXT @PageSize ROWS ONLY

 

先用數據比較，整理測試結果:

排序條件	CPU Time	經過時間	邏輯讀取
叢集索引欄位	0ms	42ms	3
非叢集索引欄位	0ms	75ms	317
非索引欄位	109ms	145ms	876

11萬筆資料的比較

 

執行計畫

 

查詢1的叢集索引掃描，資料已排序。

查詢2的索引鍵查閱(key lookup)，資料已排序。

查詢3沒有，只好自己排。

 

比較成本表格:

排序條件	占比	子樹成本
叢集索引欄位	0%	0.0039133
非叢集索引欄位	10%	0.316297
非索引欄位	90%	2.83824

 

比較成本圖示:

 

 

---

小結

• 資料表設計叢集索引很重要，除了排序的效能影響外，也是空間是否能重複使用的關鍵。
• 理想的叢集索引設計: 最短、不常變更、AP能更按照順序新增、唯一(選擇)。
• Cluster Index並不一定要和Primary key相同。
• 用非叢集索引作為排序條件時，索引鍵查閱(key lookup)將耗用較多的資源，可以再調整語法，先挑出key column再撈其他欄位。
• 排序條件無法識別唯一性時，會不會有非預期的順序? 

 

---

參考

叢集與非叢集索引說明

[SQL Server][Pagination]DB端的分頁處理(一)取總筆數的效能(COUNT(1) OVER() VS CTE)