透過 Azure Synapse Analytics 編製專用 SQL 集區的資料表索引
建議和範例，以在 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]
, COUNT(DISTINCT rg.[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.[pdw_nodes_column_store_row_groups] 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_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]
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 集區中使用分割區。
下一步
如需開發數據表的詳細資訊，請參閱 開發數據表。