[心得] SQL Server Partition(表分區) 資料分佈探討

最近在群裡有個朋友問了個問題是這樣的

用户表有一千多万行，主键是用户ID，我做了分区。但经常查询时，其它的表根据用户ID来关联，这样跨区查询，reads非常高。有什么好的处理办法？不分区的话，索引维护要好久的时间

在查看了他提供的分區資訊後，發現只有23個分區(包含一定要有的Null分區)

Null分區在這裡的定義其實很簡單，當你的資料沒有辦法放到你先前建立的分區時，就會將該資料放到所謂的Null分區(預設分區)。

因此如果在探尋分區規則時沒有依照現有的資料進行分區的設計，將會很容易導致資料偏斜(Data Bias)，一但資料出現了偏斜時在查找時就會很容易在NULL區出現過多的讀取

以今天的案例來看待，當要比對的ID不在這22個分區中時就會到NULL分區進行查找的動作。而在群友提供的資料中其實有出現了oGpI0w_ 、mGpI0w等字眼

可以想見的是，該NULL分區的資料是相當多的

以下就一個測試情境來探討在分區規則不同時的效能比較

首先建立二張結構一樣的表，資料量約一千二百萬筆

接下來分別建立給表Demo1與Demo2的表分區函數(請注意圖中的註解)
(注意，以下示範並沒有利用到分區FileGroup優化，當你用了分區時請一定要同時利用FileGroup進行優化)

一個是利用UserID前五碼分區另一個則利用前一碼進行分區

這裡要注意的是SQL Server 2016一個資料表或索引最多可以有 15,000 個資料分割

SQL Server 2005 與 2008 則需為SP2才可使用 (否則只能合計有1000個分區)

Refer : New Limit for Number of Partitions in SQL Server 2008 SP2

Demo1表分區函數

Demo2表分區函數

而在表中不重複前五碼的資料筆數約9百多萬，如下圖
(可以想見的是在NULL區中會有大量的資料存放)

接下來我們來看看分區後的Demo1與Demo2分區表資料分佈情形

 

Demo1表分區資料分佈

 

Demo2表分區資料分佈

案例:當利用LIKE做前綴查找

這裡從前述的資訊可以知道在Demo1 a0%最少有6個區需要查找

而Demo2只有一個區需要查找

接下來我們先簡單的看一下兩張表在相同查詢時IO的差異 (可以看到第二張表較優)

接下來我們仔細看一下相關的執行計畫與查找的分區數
可以發現在執行時Demo1會查找七個分區，而Demo2只會從一個分區中進行查找

案例:當從預設分區查找

這次我們簡單的查找z開頭的UserID ，從先前的資訊可以知道。

表Demo1並沒有建立z開頭的分區，因此z相關的資料將會存放到預設分區(Null區)

表Demo1的預設分區統計約有643萬筆，而表Demo2的z分區約有45萬筆

由此可見在Demo2表上查找應該會優於Demo1的(當資料筆數再更多時，差異會更大)
見下圖

以上便是今天的表分區探討，替各位總結一下。

1.在規劃表分區時，首先要注意該表的相關查詢語句，以最常用在條件式的字段做為分區依據是較佳的。

2.承上，即使使用最常用的字段做為分區依據，仍然要確認資料是否適合做為分區。

例如：即使常用的查詢字段為姓別 (男、女)，用此字段做為分區，僅能將資料最多分為三個區。在大資料時，性能並無法顯著的增加。簡單的評估可以用目前的資料筆數除以分區數，可得知每個分區的資料分佈進而做分區建立的評估依據

比如可以用下列這種簡單的語法計算每個分區數

--12228608 / 37 
SELECT COUNT(1) / 
(
SELECT COUNT(1) FROM 
(
SELECT 1 as Counts FROM Demo1 GROUP BY SUBSTRING(UserID,1,1)
) as X
)
FROM Demo1

後記

在寫本篇時，還發現了一個需要注意的問題，當利用VARCHAR字段做為分區依據時。

在查詢時需要在該字段使用 LIKE 而不是一般的Equal (=)做為查找。
如果採用一般的Equal(=)做為查找時，該執行計畫會顯示查找了所有分區內容
具體原因如果有朋友知道，還請協助解答。

以下是查找的比較圖

使用Equal(=)查找

使用LIKE查找

本次用來查詢表分區相關資訊的語法

SELECT t.name AS TableName, i.name AS IndexName, p.partition_number,
 p.partition_id, i.data_space_id, f.function_id, f.type_desc, 
 r.boundary_id, r.value AS BoundaryValue,p.rows
FROM sys.tables AS t  
JOIN sys.indexes AS i  
  ON t.object_id = i.object_id  
JOIN sys.partitions AS p  
  ON i.object_id = p.object_id AND i.index_id = p.index_id  
JOIN sys.partition_schemes AS s  
  ON i.data_space_id = s.data_space_id  
JOIN sys.partition_functions AS f  
  ON s.function_id = f.function_id  
LEFT JOIN sys.partition_range_values AS r  
  ON f.function_id = r.function_id and r.boundary_id = p.partition_number  
WHERE t.name = '已分區表名稱' AND i.type <= 1  
ORDER BY p.partition_number;

最後謝謝各位觀看囉！如果有問題歡迎在底下留言與我討論