透過 Azure Synapse Analytics 編製專用 SQL 集區的資料表索引

發行項
01/23/2025

Azure Synapse Analytics 中專用 SQL 集區中索引數據表的建議和範例。

索引類型

專用 SQL 集區提供數個索引選項，包括叢集數據行存放區索引、叢集索引和非叢集索引，以及稱為堆積的非索引選項。

若要建立具有索引的數據表，請參閱 CREATE TABLE （專用 SQL 集區）檔。

叢集數據行存放區索引

根據預設，當數據表上未指定任何索引選項時，專用SQL集區會建立叢集數據行存放區索引。叢集數據行存放區數據表同時提供最高層級的數據壓縮和最佳的整體查詢效能。叢集數據行存放區數據表通常會優於叢集索引或堆積數據表，通常是大型數據表的最佳選擇。基於這些原因，當您不確定如何編製數據表索引時，叢集數據行存放區是最佳起點。

若要建立叢集數據行存放區數據表，請在WITH子句中指定 CLUSTERED COLUMNSTORE INDEX ，或讓WITH子句保持關閉：

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( CLUSTERED COLUMNSTORE INDEX );

在某些情況下，叢集數據行存放區可能不是一個很好的選項：

叢集資料行存放區索引不支援 varchar(max)、nvarchar(max) 與 varbinary(max)。考慮改用堆積或叢集索引。
數據行存放區數據表對於暫時性數據可能不太有效率。請考慮堆積和臨時表。
少於 6 千萬個資料列的小型資料表。考慮改用堆積資料表。

堆積數據表

當您暫時在專用 SQL 集區中登陸數據時，您會發現使用堆積數據表可讓整體程式更快。這是因為堆積的載入速度比索引數據表快，而且在某些情況下，可以從快取完成後續讀取。如果您只要在執行其他轉換之前暫存載入的資料，則將資料表載入堆積資料表遠快於將資料載入叢集資料行存放區資料表。此外，將數據載入臨時表的速度比將數據表載入永久記憶體更快。載入數據之後，您可以在資料表中建立索引，以加快查詢效能。

一旦數據列超過 6000 萬個數據列，叢集數據行存放區數據表就會開始達到最佳壓縮。對於小型查閱表，小於 6000 萬個數據列，請考慮使用 HEAP 或叢集索引，以加快查詢效能。

若要建立堆積數據表，請在WITH子句中指定HEAP：

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( HEAP );

注意

如果您經常在堆積數據表上執行 INSERT、 UPDATE或 DELETE 作業，建議您使用 ALTER TABLE 命令在維護排程中包含數據表重建。例如： ALTER TABLE [SchemaName].[TableName] REBUILD 。這種做法有助於減少片段，進而改善讀取作業期間的效能。

叢集與非叢集索引

當需要快速擷取單一數據列時，叢集索引可能會優於叢集數據行存放區數據表。對於需要單一或極少數數據列查閱才能以極端速度執行的查詢，請考慮叢集索引或非叢集次要索引。使用叢集索引的缺點是，只有受益的查詢是對叢集索引數據行使用高度選擇性篩選的查詢。若要改善其他數據行的篩選，可以將非叢集索引新增至其他數據行。不過，新增至數據表的每個索引都會增加載入的空間和處理時間。

若要建立叢集索引數據表，請在WITH子句中指定CLUSTERED INDEX：

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( CLUSTERED INDEX (id) );

若要在數據表上新增非叢集索引，請使用下列語法：

CREATE INDEX zipCodeIndex ON myTable (zipCode);

最佳化叢集資料行存放區索引

叢集數據行存放區數據表會將數據組織成區段。擁有高區段品質是在資料行存放區資料表上達到最佳查詢效能的關鍵。壓縮的資料列群組中的資料列數目可以測量區段品質。區段品質是最佳的，其中每個壓縮的數據列群組至少有 100-K 個數據列，而且效能提升，因為每個數據列群組的數據列數目接近 1,048,576 個數據列，這是數據列群組可以包含的數據列最多。

您可以在系統上建立及使用下列檢視，以計算每個數據列群組的平均數據列，並識別任何次佳的叢集數據行存放區索引。此檢視的最後一個數據行會產生可用來重建索引的 SQL 語句。

CREATE VIEW dbo.vColumnstoreDensity
AS
SELECT
        GETDATE()                                                               AS [execution_date]
,       DB_Name()                                                               AS [database_name]
,       s.name                                                                  AS [schema_name]
,       t.name                                                                  AS [table_name]
,       MAX(p.partition_number)                                                 AS [table_partition_count]
,       SUM(rg.[total_rows])                                                    AS [row_count_total]
,       SUM(rg.[total_rows])/COUNT(DISTINCT rg.[distribution_id])               AS [row_count_per_distribution_MAX]
,       CEILING((SUM(rg.[total_rows])*1.0/COUNT(DISTINCT rg.[distribution_id]))/1048576) AS [rowgroup_per_distribution_MAX]
,       SUM(CASE WHEN rg.[State] = 0 THEN 1                   ELSE 0    END)    AS [INVISIBLE_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE 0    END)    AS [INVISIBLE_rowgroup_rows]
,       MIN(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE NULL END)    AS [INVISIBLE_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE NULL END)    AS [INVISIBLE_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE NULL END)    AS [INVISIBLE_rowgroup_rows_AVG]
,       SUM(CASE WHEN rg.[State] = 1 THEN 1                   ELSE 0    END)    AS [OPEN_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE 0    END)    AS [OPEN_rowgroup_rows]
,       MIN(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE NULL END)    AS [OPEN_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE NULL END)    AS [OPEN_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE NULL END)    AS [OPEN_rowgroup_rows_AVG]
,       SUM(CASE WHEN rg.[State] = 2 THEN 1                   ELSE 0    END)    AS [CLOSED_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE 0    END)    AS [CLOSED_rowgroup_rows]
,       MIN(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE NULL END)    AS [CLOSED_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE NULL END)    AS [CLOSED_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE NULL END)    AS [CLOSED_rowgroup_rows_AVG]
,       SUM(CASE WHEN rg.[State] = 3 THEN 1                   ELSE 0    END)    AS [COMPRESSED_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE 0    END)    AS [COMPRESSED_rowgroup_rows]
,       SUM(CASE WHEN rg.[State] = 3 THEN rg.[deleted_rows]   ELSE 0    END)    AS [COMPRESSED_rowgroup_rows_DELETED]
,       MIN(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE NULL END)    AS [COMPRESSED_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE NULL END)    AS [COMPRESSED_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE NULL END)    AS [COMPRESSED_rowgroup_rows_AVG]
,       'ALTER INDEX ALL ON ' + s.name + '.' + t.NAME + ' REBUILD;'             AS [Rebuild_Index_SQL]
FROM    sys.[dm_pdw_nodes_db_column_store_row_group_physical_stats] rg
JOIN    sys.[pdw_nodes_tables] nt                   ON  rg.[object_id]          = nt.[object_id]
                                                    AND rg.[pdw_node_id]        = nt.[pdw_node_id]
                                                    AND rg.[distribution_id]    = nt.[distribution_id]
JOIN    sys.[pdw_permanent_table_mappings] mp                 ON  nt.[name]               = mp.[physical_name]
JOIN    sys.[tables] t                              ON  mp.[object_id]  = t.[object_id]
JOIN    sys.[schemas] s                             ON t.[schema_id]    = s.[schema_id]
JOIN    sys.[partitions] p                          ON P.object_id      = t.object_id
GROUP BY
        s.[name]
,       t.[name];

現在您已建立檢視，請執行此查詢，以識別具有少於 100-K 個數據列的數據列群組數據表。如果您要尋找更理想的區段品質，您可以增加 100 K 的閾值。

SELECT    *
FROM    [dbo].[vColumnstoreDensity]
WHERE    COMPRESSED_rowgroup_rows_AVG < 100000
        OR INVISIBLE_rowgroup_rows_AVG < 100000;

執行查詢之後，您就可以開始查看數據並分析結果。下表說明在數據列群組分析中要尋找的專案。

資料行	如何使用此數據
[table_partition_count]	如果資料表分割，則您可以預期看到更高的開啟資料列群組計數。理論上來說，散發中的每個分割區都會有與其相關的開啟資料列群組。請將此要素歸至您的分析中。您可以藉由完全移除資料分割來改善壓縮，進而將分割的小型資料表最佳化。
[row_count_total]	資料表的資料列計數總計。舉例來說，您可以使用此值計算處於壓縮狀態之資料列的百分比。
[row_count_per_distribution_MAX]	如果所有資料列均平均散發，則該值將是每個散發的目標資料列數。請以 compressed_rowgroup_count 比較此值。
[COMPRESSED_rowgroup_rows]	資料表的資料列數量總計，以資料行存放區格式呈現。
[COMPRESSED_rowgroup_rows_AVG]	如果資料列群組的平均資料列數目明顯少於資料列的數目上限，則請考慮使用 CTAS 或 ALTER INDEX REBUILD 重新壓縮資料
[COMPRESSED_rowgroup_count]	資料行存放區格式的資料列群組數目。如果此數目相對於資料表來說非常高，則表示資料行存放區密度為低度。
[COMPRESSED_rowgroup_rows_DELETED]	系統會以資料行存放區格式，有邏輯地刪除資料列。如果此數目相對於資料表大小而言為高度，請考慮重新建立分割區或重建索引，以實體方式將它們移除。
[COMPRESSED_rowgroup_rows_MIN]	搭配 AVG 與 MAX 資料行使用此項目，來了解資料行存放區中資料列群組的值範圍。低於負載閾值 (每個分割對齊的散發各 102,400 個) 的數目代表可在資料負載中實現最佳化
[COMPRESSED_rowgroup_rows_MAX]	如上所示
[OPEN_rowgroup_count]	開啟的資料列群組正常。我們可以合理預期每個資料行散發 (60) 都有一個 OPEN 資料列群組。過多的數目表示跨分割區進行資料載入。請再次檢查分割策略以確保其正確性
[OPEN_rowgroup_rows]	每個資料列群組都能包含 1,048,576 個資料列，這是上限。請使用此值，查看開啟資料列群組目前有多滿
[OPEN_rowgroup_rows_MIN]	開啟群組指出資料正涓流載入資料表中，或是先前的負載溢出到此資料列群組中的剩餘資料列。請使用 MIN、MAX 和 AVG 資料行，查看在 OPEN 資料列群組中設定了多少資料。如果是小型資料表，則可能是所有資料的 100%！如果是這種情形，ALTER INDEX REBUILD 會將資料強制到資料行存放區。
[OPEN_rowgroup_rows_MAX]	如上所示
[OPEN_rowgroup_rows_AVG]	如上所示
[CLOSED_rowgroup_rows]	查看已關閉的數據列群組數據列做為檢查。
[CLOSED_rowgroup_count]	如果有發現關閉資料列群組的話，其數目應該很少。您可以使用 ALTER INDEX ... REORGANIZE 命令，將關閉資料列群組轉換為壓縮資料列群組。不過正常來說並不需要這麼做。關閉群組會由背景的 “Tuple Mover” 程序自動轉換為資料行存放區資料列群組。
[CLOSED_rowgroup_rows_MIN]	關閉資料列群組應有非常高的投放率。如果關閉資料列群組的投放率不高，則需要進一步分析資料行存放區。
[CLOSED_rowgroup_rows_MAX]	如上所示
[CLOSED_rowgroup_rows_AVG]	如上所示
[Rebuild_Index_SQL]	SQL 用於重建資料表的資料行存放區索引

索引維護的影響

檢視中的數據vColumnstoreDensity行Rebuild_Index_SQL包含ALTER INDEX REBUILD可用來重建索引的語句。重建索引時，請確定您配置足夠的記憶體給重建索引的會話。若要這樣做，請將具有在此數據表上索引重建許可權的用戶資源類別增加到建議的最小值。如需範例，請參閱本文稍後的重建索引以改善區段品質。

對於具有已排序叢集數據行存放區索引的數據表， ALTER INDEX REBUILD 將會使用tempdb重新排序數據。在重建作業期間監視tempdb。如果您需要更多 tempdb 空間，請相應增加資料庫集區。在索引重建完成之後相應減少。

對於具有已排序叢集數據行存放區索引的數據表， ALTER INDEX REORGANIZE 不會重新排序數據。若要重新排序資料，請使用 ALTER INDEX REBUILD。

如需詳細資訊，請參閱使用已排序的叢集資料行存放區索引微調效能。

資料行存放區索引品質不佳的原因

如果您識別出區段品質不佳的數據表，您想要找出根本原因。以下是區段品質不佳的一些其他常見原因：

建置索引時的記憶體壓力
大量 DML 作業
小型或棘手的載入作業
太多分割區

這些因素可能會導致數據行存放區索引明顯小於每個數據列群組的最佳 1 百萬個數據列。它們也會造成數據列移至差異數據列群組，而不是壓縮的數據列群組。

建置索引時的記憶體壓力

每個壓縮數據列群組的數據列數目與數據列的寬度和可用來處理數據列群組的記憶體數量直接相關。將數據列寫入記憶體壓力下的數據行存放區數據表時，數據行存放區區段品質可能會受到影響。因此，最佳做法是提供寫入數據行存放區索引數據表的會話，以盡可能存取記憶體。由於記憶體與並行之間有取捨，因此正確的記憶體配置指引取決於數據表中每個數據列的數據、配置給系統的數據倉儲單位，以及您可以提供給寫入數據至數據表之會話的並行位置數目。

大量 DML 作業

大量更新和刪除數據列的 DML 作業可能會造成資料行存放區效率低下。當修改數據列群組中的大部分數據列時，這特別如此。

從壓縮的資料列群組刪除資料列僅會以邏輯方式將資料列標示為已刪除。資料列會保留在壓縮的資料列群組中，直到重建資料分割或資料表為止。
插入數據列會將數據列加入至名為差異數據列群組的內部數據列存放區數據表。在差異數據列群組已滿且標示為已關閉之前，插入的數據列不會轉換成數據行存放區。一旦數據列群組達到 1,048,576 個數據列的最大容量，就會關閉。
以資料行存放區格式更新數據列會以邏輯刪除的形式處理，然後進行插入。插入的數據列可以儲存在差異存放區中。

超過每個數據分割對齊散發 102,400 個數據列大量閾值的批次更新和插入作業會直接移至數據行存放區格式。不過，假設平均分佈，您必須在單一作業中修改超過61.44萬個數據列，才能發生這種情況。如果指定數據分割對齊分佈的數據列數目小於 102,400，則數據列會移至差異存放區，並留在該處，直到插入或修改足夠的數據列以關閉數據列群組，或重建索引為止。

小型或棘手的載入作業

流入專用 SQL 集區的小型負載有時也稱為小負載。它們通常代表系統所擷取之數據的接近常數數據流。不過，由於此數據流接近連續，因此數據列的磁碟區並不特別大。數據通常遠遠低於直接載入數據行存放區格式所需的臨界值。

在這些情況下，最好先將數據登陸 Azure Blob 記憶體，並讓它在載入之前累積。這項技術通常稱為 微批處理。

太多分割區

另一件事是分割對叢集數據行存放區數據表的影響。在數據分割之前，專用SQL集區已將數據分割成60個資料庫。分割會進一步分割您的數據。如果您分割數據，請考慮每個分割區至少需要 1 百萬個數據列，才能受益於叢集數據行存放區索引。如果您將數據表分割成 100 個數據分割，則數據表至少需要 60 億個數據列，才能受益於叢集數據行存放區索引（60 個散發 100 個 分割區 100 萬個數據列）。如果您的 100 個分割區數據表沒有 60 億個數據列，請減少分割區數目，或改為考慮使用堆積數據表。

當您的數據表載入某些數據之後，請遵循下列步驟，以識別並重建具有子最佳叢集數據行存放區索引的數據表。

重建索引以改善區段品質

步驟 1：識別或建立使用適當資源類別的使用者

立即改善區段品質的一個快速方法是重建索引。上述檢視所傳回的 SQL 包含 ALTER INDEX REBUILD 語句，可用來重建索引。重建索引時，請確定您配置足夠的記憶體給重建索引的會話。若要這樣做，請將具有在此數據表上索引重建許可權的用戶資源類別增加到建議的最小值。

以下是如何藉由增加其資源類別，為使用者配置更多記憶體的範例。若要使用資源類別，請參閱工作負載管理的資源類別。

EXEC sp_addrolemember 'xlargerc', 'LoadUser';

步驟 2：使用較高的資源類別使用者重建叢集數據行存放區索引

以步驟 1 （LoadUser）的使用者身分登入，其現在使用較高的資源類別，並執行 ALTER INDEX 語句。請確定此使用者具有重建索引之數據表的 ALTER 許可權。這些範例示範如何重建整個數據行存放區索引，或如何重建單一數據分割。在大型數據表上，一次重建單一分割區的索引比較實用。

或者，您可以使用 CTAS 將資料表複製到新的資料表，而不是重建索引。哪一種方式最好？對於大量數據，CTAS 通常比 ALTER INDEX 更快。對於較小的數據量，ALTER INDEX 更容易使用，而且不需要您交換數據表。

-- Rebuild the entire clustered index
ALTER INDEX ALL ON [dbo].[DimProduct] REBUILD;

-- Rebuild a single partition
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5;

-- Rebuild a single partition with archival compression
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5 WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

-- Rebuild a single partition with columnstore compression
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5 WITH (DATA_COMPRESSION = COLUMNSTORE);

在專用 SQL 集區中重建索引是離線作業。如需重建索引的詳細資訊，請參閱數據行存放區索引重組和 ALTER INDEX 中的 ALTER INDEX REBUILD 一節。

步驟 3：確認叢集數據行存放區區段品質已改善

重新執行查詢，該查詢識別出區段品質不佳的數據表，並確認區段品質已改善。如果區段質量沒有改善，可能是數據表中的數據列會特別寬。在重建索引時，請考慮使用較高的資源類別或 DWU。

使用 CTAS 和數據分割切換重建索引

此範例使用 CREATE TABLE AS SELECT （CTAS）語句和數據分割切換來重建數據表分割區。

-- Step 1: Select the partition of data and write it out to a new table using CTAS
CREATE TABLE [dbo].[FactInternetSales_20000101_20010101]
    WITH    (   DISTRIBUTION = HASH([ProductKey])
            ,   CLUSTERED COLUMNSTORE INDEX
            ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                                (20000101,20010101
                                )
                            )
            )
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
WHERE   [OrderDateKey] >= 20000101
AND     [OrderDateKey] <  20010101
;

-- Step 2: Switch IN the rebuilt data with TRUNCATE_TARGET option
ALTER TABLE [dbo].[FactInternetSales_20000101_20010101] SWITCH PARTITION 2 TO  [dbo].[FactInternetSales] PARTITION 2 WITH (TRUNCATE_TARGET = ON);

如需使用 CTAS 重新建立分割區的詳細資訊，請參閱在專用 SQL 集區中使用分割區。

如需開發數據表的詳細資訊，請參閱開發數據表。

共用方式為

透過 Azure Synapse Analytics 編製專用 SQL 集區的資料表索引

索引類型

叢集數據行存放區索引

堆積數據表

叢集與非叢集索引

最佳化叢集資料行存放區索引

索引維護的影響

資料行存放區索引品質不佳的原因

建置索引時的記憶體壓力

大量 DML 作業

小型或棘手的載入作業

太多分割區

重建索引以改善區段品質

步驟 1：識別或建立使用適當資源類別的使用者

步驟 2：使用較高的資源類別使用者重建叢集數據行存放區索引

步驟 3：確認叢集數據行存放區區段品質已改善

使用 CTAS 和數據分割切換重建索引

意見反應

其他資源

共用方式為

透過 Azure Synapse Analytics 編製專用 SQL 集區的資料表索引

索引類型

叢集數據行存放區索引

堆積數據表

叢集與非叢集索引

最佳化叢集資料行存放區索引

索引維護的影響

資料行存放區索引品質不佳的原因

建置索引時的記憶體壓力

大量 DML 作業

小型或棘手的載入作業

太多分割區

重建索引以改善區段品質

步驟 1：識別或建立使用適當資源類別的使用者

步驟 2：使用較高的資源類別使用者重建叢集數據行存放區索引

步驟 3：確認叢集數據行存放區區段品質已改善

使用 CTAS 和數據分割切換重建索引

相關內容

意見反應

其他資源