資料分割資料表與索引

適用於：SQL ServerAzure SQL 資料庫Azure SQL 受控執行個體

分割數據表和索引的數據會分割成可能分散在資料庫中多個檔案群組或儲存在單一檔案群組中的單位。 當檔案群組有多個檔案時，系統會使用比例填滿演算法將資料分散到檔案。 資料是以水平方式分割，因此資料列的群組可對應至個別的資料分割。 單一索引或資料表的所有分割區必須在同一個資料庫中。 在資料上執行查詢或更新時，資料表或索引會被視為單一邏輯實體。

資料分割的優點

對大型資料表或索引進行資料分割，可以具有下列管理能力和效能優點。

• 您可以快速並有效率地傳送或存取資料子集，同時維護資料集合的完整性。 例如，將資料從 OLTP 載入至 OLAP 系統這類作業只需要數秒的時間，而不用像未對資料進行資料分割時，需要數分鐘或數小時的時間才能執行作業。

• 您可以更快的速度對一個或多個分割區執行維護或資料保留作業。 作業只處理這些資料子集，而非整個資料表，因此會更有效率。 例如，您可選擇壓縮一個或多個分割區的資料、重建一個或多個分割區的索引或截斷單一分割區的資料。 您也可以將個別分割區從一個數據表切換到封存數據表。

• 您可以根據您經常執行的查詢類型來改善查詢效能。 例如，當分割資料行與要在其上聯結資料表的資料行相同時，查詢最佳化工具可以更快速地同等聯結兩個或更多資料分割資料表間的查詢。 如需詳細資訊，請參閱 查詢一節。

您可啟用分割區層級的鎖定擴大 (而非整個資料表) 來提升效能。 這可以減少資料表上的鎖定競爭。 若要允許鎖定擴大到資料分割，以減少鎖定競爭，請將 LOCK_ESCALATION 陳述式的 ALTER TABLE 選項設定為 AUTO。

元件和概念

下列詞彙適用於資料表和索引資料分割。

Partition function

資料分割函數為一種資料庫物件，其定義資料表或索引的資料列如何根據某些資料行 (稱為分割資料行) 的值對應至資料分割集。 分割資料行中的每個值都是分割區函數其輸入，而函數會傳回分割值。

分割區函數會定義分割區的數目，以及資料表擁有的分割區界限。 例如，假設數據表包含銷售訂單數據，您可能會想要根據銷售日期等 日期時間 欄位，將數據表分割成12個（每月）分區。

範圍類型 (LEFT 或 RIGHT)　則指定資料分割函數的界限值如何放入最終的分割區中：

• 當資料庫引擎從左至右遞增排序間隔值時，LEFT 範圍會指定界限值屬於界限值間隔的左側。 換句話說，最高界限值會包含在分割區中。
• 當資料庫引擎從左至右遞增排序間隔值時，RIGHT 範圍會指定界限值屬於界限值間隔的右側。 換句話說，每個分割區都會包含最低界限值。

如未指定 LEFT 或 RIGHT，則 LEFT 範圍為預設值。

例如，下列資料分割函數會將資料表或是索引分割成為 12 個分割區，各分割區代表一個 datetime 資料行中一年各個月份的數值。 使用 RIGHT 範圍，表示界限值會作為每個分割區的下限值。 根據 datetime 或 datetime2 資料類型的資料行來分割資料表時，RIGHT 範圍通常更容易操作，因為值發生在午夜的資料行，會與值發生在同天稍後的資料行儲存在同一個分割區中。 同樣地，如果使用 date 的資料類型，並使用一個月或更長時間的分割，則 RIGHT 範圍會將月份的第一天與該月之後的天數保留在同一分割區。 這有助於在查詢一整天的資料時，精確排除分割區。

CREATE PARTITION FUNCTION [myDateRangePF1] (datetime)
AS RANGE RIGHT FOR VALUES ('2022-02-01', '2022-03-01', '2022-04-01',
               '2022-05-01', '2022-06-01', '2022-07-01', '2022-08-01',
               '2022-09-01', '2022-10-01', '2022-11-01', '2022-12-01');

下表顯示在分割資料行 datecol 上使用這個資料分割函數的資料表或索引如何進行分割。 2 月 1 日是函式定義的第一個界限點，因此它會作為分割區 2 的下限。

Partition	1	2	...	11	12
Values	datecol<2022-02-01 12:00AM	datecol>= 2022-02-01 12:00AM 且 datecol<2022-03-01 12:00AM		datecol>= 2022-11-01 12:00AM 且 col1<2022-12-01 12:00AM	datecol>= 2022-12-01 12:00AM

對於 RANGE LEFT 和 RANGE RIGHT，最左側的分割區以資料類型的最小值做為其下限，而最右側的分割區則以資料類型的最大值做為其上限。

在 CREATE PARTITION FUNCTION 中尋找 LEFT 和 RIGHT 資料分割函數的更多範例。

Partition scheme

資料分割是一種資料庫物件，將資料分割函數的資料分割對應至一或多個檔案群組檔案群組。

尋找在 CREATE PARTITION SCHEME 中建立數據分割配置的範例語法。

Filegroups

將分割區放在多個檔案群組的主要原因在於，可確保能獨立對分割區執行備份與還原作業。 這是因為您可以對個別檔案群組執行備份。 使用階層式儲存體時，使用多個檔案群組可將特定分割區指派至特定的儲存層，例如，將較舊且較不常存取的分割區放在較慢且成本較低的儲存體。 所有其他資料分割優點皆適用，無論所使用的檔案群組數目為何，或是特定檔案群組上的分割區位置為何。

管理分割數據表的檔案和檔案群組，可能會隨著時間對系統管理工作造成重大複雜性。 如果使用多個檔案群組對於備份與還原過程沒有幫助，則建議針對所有分割區使用單一檔案群組。 設計檔案和檔案群組的相同規則會套用至分割物件，適用於非分割物件。

在 ALTER DATABASE (Transact-SQL) 檔案及檔案群組選項找到為 SQL Server 及 Azure SQL 受控執行個體建立檔案群組的程式碼範例。

Partitioning column

分割區函數用於分割資料表或索引的資料表或索引資料行。 選取分割資料行時需要考慮以下因素：

• 參與資料分割函數的計算資料行必須明確建立為 PERSISTED。
  • 由於只有一個資料行可作為分割資料行，因此，在某些情況下，用計算資料行串連多個資料行可能會十分有用。
• 所有可作為索引鍵資料行的資料類型，其資料行均可做為分割資料行，除了 timestamp 以外。
• 無法指定大型物件 (LOB) 資料類型的資料行，例如 ntext、text、image、xml、varchar(max)、Nvarchar(max)，以及 varbinary(max)。
• 無法指定 Microsoft .NET Framework 通用語言執行平台 (CLR) 使用者定義型別及別名資料類型資料行。

若要分區物件，請在 CREATE TABLE、ALTER TABLE 和 CREATE INDEX 語句中指定分區配置和分區欄位。

在建立非叢集索引時，如未指定 partition_scheme_name 或檔案群組，且資料表受到分割，則系統會使用相同的分割資料行，將索引放在與基礎資料表相同的資料分割配置。 若要更改現有索引的分割方式，請使用具有 DROP_EXISTING 子句的 CREATE INDEX。 這可讓您分割非分割索引、將分割索引設為非分割，或變更索引的數據分割配置。

Aligned index

在與對應資料表相同的分割區配置上建立的索引。 當資料表及其索引對齊時，資料庫引擎可快速有效地切換分割區，同時維持資料表及其索引的資料分割結構。 索引不一定要參與相同具名的資料分割函數才能與其基底資料表對齊。 因為下列原因，索引與基底資料表的資料分割函式在本質上必然相同：

• 資料分割函式的引數具有相同的資料類型。
• 兩者定義了相同的資料分割數。
• 兩者為資料分割定義了相同的界限值。

對叢集索引進行資料分割

分割叢集索引時，叢集索引鍵必須包含分割資料行。 在對非唯一的叢集索引進行資料分割，且未在叢集索引鍵明確指定分割資料行時，資料庫引擎會依預設將分割資料行加入叢集索引鍵清單。 如果叢集索引是唯一的，您必須明確指定叢集索引鍵包含分割欄位。 如需叢集索引和索引架構的詳細資訊，請參閱叢集索引設計指導方針。

對非叢集索引進行資料分割

分割唯一的非叢集索引時，索引鍵必須包含分割資料行。 根據預設，在對非唯一、非叢集索引進行資料分割時，資料庫引擎會新增分割資料行，作為索引的非索引鍵 (包含在內) 資料行，以確保索引與基底資料表對齊。 如果索引已存在分割資料行，資料庫引擎便不會將分割資料行新增到索引。 如需非叢集索引和索引架構的詳細資訊，請參閱非叢集索引設計指導方針。

Nonaligned index

非對齊索引的分割方式與其對應的數據表不同。 也就是說，索引具有不同資料分割配置，將其置於與基底資料表不同的檔案群組或檔案群組集。 在下列情況下，設計非對齊的數據分割索引很有用：

• 尚未對基底資料表進行資料分割。
• 索引鍵是唯一的，並且不含資料表的分割資料行。
• 您希望使用不同的聯結資料行，讓基底資料表參與具有多個資料表的共置聯結。

Partition elimination

查詢最佳化工具用來只存取相關分割區以滿足查詢篩選準則的程序。

若要深入瞭解分割區排除與相關概念，請參閱資料分割資料表和索引的查詢處理增強功能。

Limitations

• 在 SQL Server 2016 (13.x) SP1 之前，資料分割資料表及索引並非在每個版本的 SQL Server 都供使用。 如需 SQL Server 版本支援的功能清單，請參閱 SQL Server 2022 版本和支援的功能。

• 分割數據表和索引可在 Azure SQL Database 的所有服務層級、網狀架構中的 SQL 資料庫和 Azure SQL 受控實例中使用。

  • 在 Azure SQL Database 和 Fabric 的 SQL 資料庫中，所有分割區都必須放在 PRIMARY 檔案群組上，因為僅提供 PRIMARY 檔案群組。

• 數據表分割可在 Azure Synapse Analytics 的專用 SQL 集區中使用，但語法有一些差異。 深入瞭解在專用 SQL 集區中的資料分割資料表。

• 分割區函數和配置的範圍只限於它們建立所在的資料庫。 在這個資料庫內，分割區函數是在不同於其他函數的個別命名空間中。

• 如果資料分割資料表的任何資料列在分割資料行包含 NULL，則這些資料列將置於最左側的分割區。 然而，如果將 NULL 指定為第一個界限值，並在資料分割函數定義指定了 RANGE RIGHT，則最左側的分割區將維持空白，而 NULL 將置於第二個分割區中。

• Microsoft SQL Server 資料庫引擎預設最多支援 15,000 個分割區。 在 SQL Server 2012 (11.x) 之前的版本，分割區預設限制為 1,000 個。

Performance guidelines

資料庫引擎支援每個資料表或索引的分割區最多 15,000 個。 不過，使用超過 1,000 個分割區會對記憶體、資料分割索引作業、DBCC 命令和查詢產生影響。 本節介紹使用超過 1,000 個分割區對效能帶來的影響，並提供必要的解決方案。

由於每個資料分割資料表或索引最多允許 15,000 個分割區，您可在單一資料表中儲存長時間的資料。 不過，您只應該保留必要的資料，並在效能與資料分割數目之間保持平衡。

記憶體使用量和方針

如果正在使用大量分割區，則建議您至少使用 16 GB 的 RAM。 如果系統沒有足夠的記憶體，數據作語言 （DML） 語句、數據定義語言 （DDL） 語句和其他作業可能會因為記憶體不足而失敗。 具有 16 GB RAM 且執行大量記憶體密集進程的系統，可能會在大量分割區上執行的作業上記憶體不足。 因此，記憶體愈多 (超過 16 GB)，發生效能和記憶體問題的可能性就愈少。

記憶體限制可能會影響資料庫引擎組建資料分割索引的效能或能力。 當索引與其基底資料表未對齊或未與其叢集索引對齊時 (如果資料表已經有叢集索引)，這種狀況尤其明顯。

在 SQL Server 和 Azure SQL 受控執行個體中，您可以增加 index create memory (KB) 伺服器組態選項。 如需詳細資訊，請參閱 伺服器組態：索引建立記憶體。

針對 Azure SQL Database，請考慮在 Azure 入口網站暫時或永久加入資料庫的服務等級目標，以設定更多的記憶體。

資料分割索引作業

在具有超過 1,000 個數據分割的數據表上建立和重建 非對齊索引 是可行的，但不受支援。 這樣做可能會導致效能降低，或在這些作業期間耗用過多的記憶體。

隨著分割區數目的增加，建立和重建對齊索引可能需要更長的時間來執行。 建議您不要同時執行多個建立和重建索引命令，因為您可能會遇到效能和記憶體問題。

當資料庫引擎執行排序以建立資料分割索引時，會先針對每個分割區建立一個排序表。 然後，它會在每個資料分割的各自檔案群組中建置排序表，或者如果指定了 SORT_IN_TEMPDB 索引選項，則在 tempdb 中建置。 每個排序表都需要最小量的記憶體才能建立。 當您在建立對齊基底資料表的資料分割索引時，會使用少量記憶體一次建立一個排序表。 不過，當您在建立非對齊資料分割索引時，則會同時建立排序表。 因此，必須有足夠的記憶體才能處理這些並行排序作業。 分割區的數量越大的話，則需要越多記憶體。 對每個分割區來說，每個排序表的大小下限為 40 個頁面，而每一頁都為 8 KB。 例如，具有 100 個分割區的非對齊資料分割索引，需要足夠的記憶體，才能同時連續排序 4,000 (40 * 100) 頁。 如果此記憶體可用，建置作業將會成功，但效能可能會受到影響。 如果無法使用此記憶體，建立作業將會失敗。 此外，具有 100 個分割區的對齊資料分割索引只需要足夠排序 40 頁的記憶體，因為並不會同時執行排序作業。

針對對齊和非對齊索引，如果資料庫引擎使用查詢平行處理原則在多處理器計算機上建置作業，記憶體需求可能會更大。 這是因為平行處理原則 (DOP) 的程度越大，記憶體的需求也就越大。 舉例來說，如果資料庫引擎將 DOP 設為 4，則具有 100 個資料分割的非對齊資料分割索引需要足夠的記憶體供四個處理器同時排序 4,000 個頁面，也就是 16,000 個頁面。 如果已對齊資料分割索引，則記憶體需求會降低為四個處理器排序 40 頁，或 160 (4 * 40) 頁。 您可使用 MAXDOP 索引選項，以手動方式降低平行處理原則的程度。

DBCC commands

如果分割區的數目較多，則隨著分割區數目增加，DBCC CHECKDB 及 DBCC CHECKTABLE 等 DBCC 命令可能需要更長的時間來執行。

Queries

在對資料表或索引進行資料分割後，使用分割區排除的查詢可在分割區數目較多的情況下獲得類似或更佳的效能。 未使用分割區刪除的查詢，所需執行時間可能會隨著分割區數目的增加而增加。

例如，假設資料表有 1 億個資料列和資料行 A、 B和 C。

• 在案例 1 中，資料表在資料行 A 上被分成 1,000 個分割區。
• 在案例 2 中，資料表的資料行 A上分成 10,000 個資料分區。

資料表查詢若對資料行 WHERE 使用了 A 子句篩選，將會執行資料分割刪除，並掃描一個資料分割。 在案例 2 中，相同的查詢可能會執行得更快，因為分割區中掃描的數據列較少。 查詢若對資料行 B 使用了 WHERE 子句篩選，將會掃描所有資料分割。 查詢在案例 1 中執行的速度可能會比案例 2 更快，因為掃描的數據分割較少。

在數據分割數據行以外的數據行上使用 TOP 或 MAX/MIN 等運算子的查詢，可能會因為必須評估所有分割區而降低效能。

同樣地，如果查詢條件不包含分割欄位，執行單一列搜尋或小範圍掃描的查詢，在分割資料表上會比在非分割資料表上花費更長的時間，因為它需要對每個分割區執行相同數量的搜尋或掃描。 因此，在 OLTP 系統中，資料分割往往無法提升效能，因為這類查詢很頻繁。

如果您經常需要在二或多個資料分割資料表之間進行等聯結來執行查詢，則查詢的分割資料行必須與資料表所據以聯結的資料行相同。 此外，資料表或其索引都應該進行共置。 這表示它們如果不是使用相同命名的資料分割函數，就是使用不同名稱的但本質相同的資料分割函數，以便能：

• 使用相同數目的資料分割參數，並確保對應的參數具有相同的資料類型。
• 定義相同數目的資料分割。
• 為資料分割定義相同的界限值。

如此一來，因為資料分割本身可以聯結，所以查詢最佳化工具處理聯結的速度會更快。 如果查詢聯結未共置或未分割在聯結欄位上的兩個數據表，則數據分割的存在實際上可能會使查詢處理變慢，而不是加速查詢處理。

您可能會發現，在某些查詢中使用 $PARTITION 會很有用。 深入瞭解 $PARTITION。

如需查詢處理中數據分割處理的詳細資訊，包括數據分割數據表和索引的平行查詢執行策略，以及額外的最佳做法，請參閱數據分割 數據表和索引的查詢處理增強功能。

資料分割索引作業期間，統計資料運算的行為會改變

在 Azure SQL Database 中，網狀架構、Azure SQL 受控實例和 SQL Server 2012 （11.x） 和更新版本中的 SQL 資料庫不會藉由在建立或重建數據分割索引時掃描數據表中的所有數據列來建立統計數據。 反之，查詢最佳化工具會使用預設的採樣演算法來產生統計資料。

從低於 2012 （11.x） 版本的 SQL Server 升級具有分割索引的資料庫之後，您可能會注意到這些索引的直方圖數據差異。 這種行為變更可能會影響查詢效能。 若要在掃描資料表中所有資料列時取得分割區索引的統計資料，使用子句 CREATE STATISTICS 時請使用 UPDATE STATISTICS 或 FULLSCAN。

Related content

• 建立分區資料表及索引
• $PARTITION (Transact-SQL)
• 使用 Azure SQL Database 相應放大
• 分割專用 SQL 集區中的資料表
• SQL Server 及 Azure SQL 索引架構與設計指南
• 使用 SQL Server 2008 的資料分割資料表和索引策略
• 如何實作自動滑動視窗
• 大量載入至資料分割資料表
• 分割資料表和索引上的查詢處理增強功能
• 建置大規模關係型數據倉儲的前10個最佳做法