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


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

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

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

    image

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

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

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

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

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

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

    image

    接下來分別建立給表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

    image

    Demo1表分區函數

    image

    Demo2表分區函數

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

    image

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

    Demo1表分區資料分佈

    image

    Demo2表分區資料分佈

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

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

    而Demo2只有一個區需要查找

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

    image

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

    image

    案例:當從預設分區查找

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

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

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

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

    image

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

    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(=)做為查找時,該執行計畫會顯示查找了所有分區內容
    具體原因如果有朋友知道,還請協助解答。

    以下是查找的比較圖

    image

    使用Equal(=)查找

    image

    使用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;

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

  • 相关阅读:
    CoreData数据库浅析
    FMDB第三方框架
    SQLite浅析
    iOS开发工程师面试题(二)
    iOS开发工程师面试题(一)
    RunTime&RunLoop初见
    GCD定时器
    2016年4月21百度iOS实习生在线笔试题&编程题
    网络天荒地老之UIWebView&WebKit
    expdp/impdp
  • 原文地址:https://www.cnblogs.com/KingJaja/p/6263177.html
Copyright © 2020-2023  润新知