Table 的調教通常就三點

• 分割
• 壓縮
• 資料遷移到記憶體最佳化資料表
  這篇會先講分割

Partitioning 分割

這是一種針對大型 table & index 做的效能最佳化技術。

當後續存取這些資料或索引時，SQL Server 可以執行一個叫做 partition elimination 的最佳化；他會讓 SQL Server 指讀取查詢所需要的分割區，而不是讀取整張資料表。

每個分割區都可以放在不同的 filegroup 上

分割有四個概念

• partitioning key
• partition functions
• partition schemes
• partition alignment

Partitioning Key 分割鍵

這東西是用來決定資料表中每一筆資料列應該被放到哪一個分割區用的。

他有幾個條件 :

1. Table 如果有 clustered index，那分割鍵必須是 clustered index key 的一部分
2. Table 上的其他所有 UNIQUE index，包含 primary key，如果主鍵不是 clustered index，也都需要包含分割鍵
   
3. copmuted column 是可以當分割鍵的，前提是這個 computed column 必須是 persisted。

通常絕大多數情況，分割鍵都是做在”時間”這個欄位上。

必須是clustered index key 的一部分的原因是因為有 clustered index 的 table 是一個 b-tree 結構，這會在索引與效能調教的時候詳細說明

為了達到最好的效能，在分割鍵的選擇上，應該選一個能讓資料列平均分布的欄位，才能得到最大效益，同時也應該是查詢經常拿來當作篩選條件的欄位。

Partitioning Function 分割函數

就只是一個函數，他會要你設定邊界點，然後邊界點的左右側分別分割到左右側去。
使用方法如下

小日期 2019-01-01 2022-01-01 大日期

|-------------|-------------------|-------------|

左邊分割 中間區間 右邊分割

類似上面的結構。

Partitioning Scheme 分割結構

1. 每個分割區都可以存在不同的 filegroup 上
2. Scheme 就是用來指定每一個分割區要存放在哪一個 filegroup 上的
3. 按照上面 function 的案例可以看出，分割區的數量永遠會筆分割邊界點多一個
4. 但是，建立 scheme 也可以指定一個額外的 filegroup。這可以用來當作之後新增額外的邊界點時，下一個應該使用的檔案群組。
5. 還有一個 ALL 的選項，可以強制所有分割區都存在同一個 filegroup 上。

Partitioning Alignment 分割對齊

所謂的對齊通常主要是指，Table 跟 Index 對齊。

更準確地說，所有跟這張分割 Table 有關的、需要一起做分割操作的結構，都要使用相同的等價分割方式。

如果一個 index 是使用與 table 相同得 partition function，那這個 index 就會被視為跟 table 對齊。

就算用不同的 partition function，只要這兩個 function 是相同的，也就是他們有相同的 data type、相同的分區數量、相同的邊界點值，那也可以被視為對齊。

所以重點不是名字一樣，是分割邏輯一樣。

需要對齊的核心原因

讓 table 跟 index 的分割邏輯一致，這樣 SQL Server 才能用同一個分割區單位去查詢、維護、切換資料。

有對齊的話做分割才有意義。

沒做對齊 SQL Server 就變成不能只處理某個 partition，而是要跨過很多 partiion，那有分跟沒分就沒什麼差別，效能可能還因此降低。

Clustered index 的葉層本身就是 table 實際的資料頁，所以 clustered index 一定會跟 table 對齊。

但是，nonclustered index，可以被儲存在 heap 或 clustered index 不同的檔案群組上，也就是說 table 跟 nonclustered index 可以各自獨立分割，除非跟前面說的一樣，用同樣的方法去分割，否則就不對齊。

一樣如果第一次真的要學，你會不理解什麼 clustered index、nonclustered index 、葉層，為什麼非叢集可以獨立分割，這些會在索引說明。

除非有特定的理由讓他不對齊，否則讓 index 跟 table 對齊是一個良好的實務狀況。

特定理由大多都是 : 分割的 key 跟要作 index 篩選條件不同，所以才會故意不讓他對齊，但這種實務狀況很少見。

對齊可以幫助 Partition Elimination

對齊案例一

假設現在分割是這樣的結構
Partition 1：OrderDate < 2019-01-01
Partition 2：OrderDate >= 2019-01-01 AND < 2022-01-01
Partition 3：OrderDate >= 2022-01-01

然後查詢

如果資料表和索引都用 OrderDate 分割，SQL Server 可以知道：
只需要看 Partition 3
Partition 1、Partition 2 可以跳過
但如果 nonclustered index 沒有跟表對齊，
例如索引沒有依照 OrderDate 分割，SQL Server 用這個索引時，
無法直接只看對應的 partition，效益就會降低。

對齊案例二

對齊讓 SWITCH 可以運作

這是非常重要的其中一個原因。

而 SWITCH 最大主要功能是管理不是校能。

在正式環境中，常用 SWITCH 的地方是資料倉儲、報表庫、歷史資料封存、批次匯入、線上系統大表封存舊資料。

首先，SWITCH 的本質是去改 metadata，所以它才會給人一種幾乎瞬間完成資料移轉的感覺。

好又不知道什麼是 metadata，沒關係。他是一個描述資料，用來告訴 SQL Server 這東西現在的狀態，很多地方都有 page、extent、partition....等等。SQL Server 可以只看這些 metadata 就先知道這些物件的基本資訊。

但是也正因為它是改 metadata，所以事前要準備的條件很多，能夠 SWITCH 的限制也很多。來源表跟目標表的欄位、資料型別、nullability、索引、constraint、partition boundary 等，都要符合 SQL Server 的要求。

而且要注意，SWITCH 不是完全不鎖表。它是 ALTER TABLE SWITCH，屬於 DDL 操作，通常會需要短時間取得 schema modification lock，也就是 Sch-M lock。只是因為它不是逐筆搬資料，所以正常情況下鎖定時間會比大量 INSERT、DELETE 短很多。

Sch-M lock 會在鎖的篇章講解，他還有一個 Sch-S lock，這都是結構鎖。

舉例 1 :
有一個線上電商，每天都會有很多訂單，這些線上訂單會寫入 Order 這張 table。

然後每個月都要跑上個月的報表，但是如果直接在這個 Order table 上面做大量報表查詢、彙總、排序、索引掃描，會影響線上下訂單的效能，所以決定把上個月的 Order 資料轉到另外一張 table 上面去製作報表。

如果現在什麼都沒有設計，沒有做 partition scheme、沒有分割、沒有 staging table、沒有對齊 partition，什麼都沒有，是最原始的狀態，那通常只能靠一般的做法：

這樣做的問題是：

1. 會產生大量 transaction log。
2. INSERT INTO SELECT 會執行很久。
3. 來源表 Order 會被讀取，目標表會被寫入，過程中會拿鎖。
4. 如果資料量很大、交易時間很長，可能造成 blocking，甚至發生 lock escalation。
5. 寫入目標表時，也會維護目標表上的 index，所以 index 越多，寫入成本越高。

但是如果事前有做 partition、有對齊 partition、有設計 staging table，而且來源表與目標表符合 SWITCH 的條件，那上面大量搬資料的過程，就可以改成主要透過 metadata 變更來完成。

這個 metadata 變更的動作，就是 ALTER TABLE SWITCH。

這樣做的重點是：SQL Server 不是把每一筆資料從 Order 複製到 ReportOrder，而是把某個 partition 的資料歸屬從一張表切換到另一張表。因此速度通常會比大量 INSERT INTO SELECT 快非常多。

舉例 2 :

一樣是舉例 1 的狀況。

今天系統已經跑了一年，然後想要封存 Order 裡面 2019 年的資料。

如果什麼都沒做 partition 設計，那通常會變成：

這樣一樣會有問題：

1. INSERT 會產生大量 log。
2. DELETE 也會產生大量 log。
3. INSERT 跟 DELETE 都會執行很久。
4. DELETE 會造成大量鎖定與 blocking 風險。
5. DELETE 之後可能造成 index fragmentation。
6. 大量刪除後，可能還需要額外做 index rebuild、reorganize、statistics update。

但是如果一開始就有做 partition，而且 2019 年的資料剛好在獨立 partition 裡面，並且有對應的 archive table 或 staging table，那就可以用 SWITCH 把整個 2019 年的 partition 切出去。

這樣就不用逐筆 INSERT，也不用逐筆 DELETE。

所以在大表封存舊資料時，SWITCH 通常會比傳統的 INSERT 加 DELETE 快很多，而且對正式表的壓力也比較小。

用 SWITCH 做有幾個好處：

1. 正式大表不用長時間承受大量 INSERT INTO SELECT。
2. 正式大表不用長時間承受大量 DELETE。
3. 正式大表不用一邊被使用者查詢，一邊大量維護索引。
4. 正式大表不用承受大量資料清理、驗證、索引維護的壓力。
5. 可以先在 staging table 裡面把資料整理好、驗證好、建立好符合條件的索引，再 SWITCH 進正式表。
6. 可以把舊資料快速切到 archive table 或 report table，讓正式線上表保持比較乾淨。
7. SWITCH 之後，可以在被切出去的表上建立報表專用索引，正式表就不用維護那些只給報表用的索引。
8. 對資料倉儲來說，可以先把新資料匯入 staging table，驗證完後再 SWITCH 進 fact table，降低正式表的匯入壓力。

但是要注意：
不會因為單純做了 SWITCH，看到主表東西變少，就一定代表效能會大幅提升。
SWITCH 本身只是快速改變資料歸屬，它不是萬能的效能優化。

真正會讓效能提升的，是後面的配套設計，例如：

1. 主表資料量真的因為舊資料切出去而變小。
2. 查詢條件能吃到 partition elimination。
3. 報表查詢真的被移到 report table 或 archive table。
4. 正式表上的報表用索引被移除，降低線上寫入成本。
5. 舊資料切出去後，正式表的 index 維護、statistics 更新、備份、檢查成本下降。
6. archive table 或 report table 有自己專用的索引設計。
7. 線上交易與報表查詢被隔離，不再互相搶資源。

如果只是把資料用 SWITCH 切到同一台伺服器、同一個 database、甚至同一個 filegroup 裡面，但是報表查詢、索引設計、partition 查詢條件都沒有改，那效能不一定會有明顯提升。

SWITCH 的主要價值不是直接讓查詢變快，而是讓大量資料移入、移出、封存、報表隔離這些動作變得非常快，並且降低正式大表長時間被大量 DML 影響的風險。

真正的效能提升，通常來自於 SWITCH 搭配 partition 設計、staging table、archive table、report table、索引策略、查詢條件與資料生命週期管理。

實作

因為 iT邦幫忙 不能貼 SQL 語法的緣故，所以有一些很長的 SQL 我會貼成兩張圖有點難看，但沒辦法。
你也可以截圖給 AI，叫 AI 幫你寫一個語法出來觀察

1.首先要建立分割函數

2.建立新的表

最重要的是要注意 ON 子句。
一般情況下，會把資料表建立在某個檔案群組上；
但在這個例子中，是把資料表建立在分割配置上，
並且傳入要作為分割鍵的欄位名稱。
分割鍵的資料型別必須和分割函數中指定的資料型別相符。

3.假裝建立一個既有的表，當作對照組

這時候去看一下這張表的屬性

然後開始真的分割

分割完之後可以看到 TABLE 屬性多了一個分割區

既有 table 如果有 clustered index 那必須先刪除的原因是，那張表的資料本體就是 clustered index 的葉層。

什麼是 clustered index、什麼是葉層先不管，在這裡只要知道，現在這張表建立的時候就是 ON PRIMARY filegroup，而且 clustered index 也是在 PRIMARY filegroup。

那如果要把他變成 partitioned table，本質上就是要把資料本體從 PRIMAY 移動到 PartScheme(OrderDate)。

然而在有 clustered index 的資料中，他的本體在哪裡，是由clustered index 決定的。

因為要繞過這個限制，所以才要先刪掉重建。

4.監控 $PARTITION 函數

這是用來判斷資料表中每個分割區各有多少資料列的函數。他需要指定分割函數，並將分割鍵欄位名稱作為參數傳入。

結果可以看到，資料表中的資料列幾乎都位於同一個分割區中。
有11筆資料在 PARTITION2，有389筆在 PARTITION 3

這基本上就違背了分割的目的，也代表這個時候應該重新評估目前的分割策略。

5. Sliding Windows

按照前面的操作，可以看出，如果接下來的時間往後推，2022/03/28之後的資料會怎麼辦?

他們會全部都進入到最後一個分割區，這邊就會越來越大。

為了解決這個問題，SQL Server 提供 sliding windows 工具；在上面1-3點的實作例子中，接下來要做的是，每一周都會建立一個新的分割區給下一週使用，同時最早的分割區會被移除。

要達成這件事有三個操作

1. SPLIT 操作會新增一個新的邊界值，因此建立出新的分割區。
2. MERGE 操作會移除一個邊界值，因此把兩個分割區合併在一起。
3. SWITCH 操作會把某個分割區移到一張空的資料表或空的分割區中。

接下來我的操作流程大致上會是

1. 建立一張 OldOrdersStaging 的表，這張表用來當作暫存區，用來存放最早那個分割區的資料。
2. 然後找出分割資料表中最高跟最低的邊界值，並將他們作為 SPLIT 跟 MERGE 操作的參數。
3. 然後用 ALTER PARTITION FUNCTION 指令去移除最低邊界值，並加入新的邊界值。
4. 最後重新執行 $PARTITION 函數來顯示新的資料列分布，並查詢 sys.partition_functions 與 sys.partition_range_values catalog views，以顯示 PartFuncWeek 分割函數新的邊界值。

這裡雖然 OldOrdersStaging 是當作暫存用，但是不能用 temporary table，因為 temporary table 他是會存在 TempDB，會跟現在的表不同 filegroup，那 SWITCH 又無法運作。

6. Partition elimination

最後去看看，實際在執行查詢的時候，有沒有真的使用到分割的概念。

打開執行計畫然後執行這個查詢

會看到上面那個查詢的執行計劃，實際分割區計數 = 1 ，代表他只去一個分割區查詢

下面那個查詢的執行計劃，實際分割區計數 = 5 ，代表他去五個分割區查詢，在這個案例中相當於全部都去查一遍了，等於根本沒分割。

會造成這樣的結果是因為在 WHERE 的時候，把 OrderDate 欄位轉換成 DATETIME2 資料型，跟我們分割的 DATA TYPE 不一樣，所以分割就沒用了；I/O成本也從 0.0052 暴漲到 0.0156。

下一篇 : 是TABLE 壓縮