資料分割資料表與索引

  • 發行項

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體

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

在 SQL Server 2016 (13.x) SP1 之前，資料分割資料表及索引並非在每個版本的 SQL Server 都供使用。 如需 SQL Server 版本支援的功能清單，請參閱 SQL Server 2022 版本和支援的功能。 資料分割資料表及索引可在 Azure SQL 資料庫以及 Azure SQL 受控執行個體的所有服務層級使用。

資料表分割也可在 Azure Synapse Analytics 的專用 SQL 集區使用，但部分語法有所差異。 深入瞭解在專用 SQL 集區中的資料分割資料表。

重要

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

資料分割的優點

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

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

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

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

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

元件和概念

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

分割區函數

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

分割區函數會定義分割區的數目，以及資料表擁有的分割區界限。 例如，如果資料表包含銷售訂單資料，您可能希望根據 datetime 資料行 (例如銷售日期) 將資料表分割為 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 的下限。

資料分割 1 2 ... 11 12
值 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 (Transact-SQL) 尋找 LEFT 和 RIGHT 資料分割函數的更多範例。

分割區配置

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

在 CREATE PARTITION SCHEME (Transact-SQL) 找到用於建立資料分割設定的語法範例。

檔案群組

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

在管理資料分割資料表的檔案和檔案群組時，隨著時間過去，系統管理工作的複雜度便可能顯著增加。 如果使用多個檔案群組對於備份與還原過程沒有幫助，則建議針對所有分割區使用單一檔案群組。 適用於非分割物件的檔案及檔案群組設計規則同樣適用於分割物件。

注意

Azure SQL 資料庫未完全支援資料分割功能。 由於 Azure SQL Database 僅支援 PRIMARY 檔案群組，因此所有分割區都必須放在 PRIMARY 檔案群組。

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

資料分割資料行

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

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

若要分割物件，請在 CREATE TABLE (Transact-SQL)、ALTER TABLE (Transact-SQL) 以及 CREATE INDEX (Transact-SQL) 陳述式指定資料分割配置及分割資料行。

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

對齊的索引

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

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

對叢集索引進行資料分割

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

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

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

未對齊的索引

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

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

分割區刪除

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

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

限制

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

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

效能指導方針

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

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

記憶體使用量和方針

如果正在使用大量分割區，則建議您至少使用 16 GB 的 RAM。 如果系統的記憶體不足，則資料操作語言 (DML) 陳述式、資料定義語言 (DDL) 陳述式和其他運算子可能會因記憶體不足而失敗。 RAM 為 16 GB 且執行許多記憶體密集處理序的系統，可能會在針對大量分割區執行的作業時記憶體不足。 因此，記憶體愈多 (超過 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 命令

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

查詢

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

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

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

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

如果查詢在分割資料行以外的資料行上使用類似 TOP 或 MAX/MIN 的運算子，則可能會因為資料分割而遇到效能降低的情況，因為所有分割區都必須評估。

同樣地，如果查詢述詞不包含資料分割資料行，則執行單一資料列搜尋或小型範圍掃描的查詢會比針對非資料分割資料表的資料表花更長的時間，因為分割區有多少，系統就必須執行多少次搜尋或掃描。 因此，在 OLTP 系統中，資料分割往往無法提升效能，因為這類查詢很頻繁。

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

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

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

您可能會發現在某些查詢使用 $PARTITION 很有用。 深入瞭解 $PARTITION (Transact-SQL)。

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

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

在 Azure SQL 資料庫、Azure SQL 受控執行個體及 SQL Server 2012 (11.x) 與更新版本中，在建立或重建資料分割索引時，系統不會掃描資料表的所有資料列來建立統計資料。 反之，查詢最佳化工具會使用預設的採樣演算法來產生統計資料。

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

相關內容

在下列文章深入瞭解資料分割資料表以及索引策略：