透過 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 集區時，您可能看到使用堆積資料表讓整體流程更快速。 這是因為堆積的載入速度比索引資料表還要快，而在某些情況下，可以從快取進行後續的讀取。 如果您載入資料只是在做執行更多轉換之前的預備，將資料表載入堆積資料表會遠快於將資料載入叢集資料行存放區資料表。 此外，將資料載入暫存資料表會比將資料表載入永久儲存體快速。 載入資料後，您可以在資料表中建立索引，加快查詢效能。
超過 6 億個資料列後，叢集資料行存放區資料表會開始達到最佳壓縮。 若是小於 6 億個資料列的小型查閱資料表，為加快查詢效能，請考慮使用堆積或叢集索引。
若要建立堆積資料表，只需在 WITH 子句中指定 HEAP︰
CREATE TABLE myTable
(
id int NOT NULL,
lastName varchar(20),
zipCode varchar(6)
)
WITH ( HEAP );
叢集與非叢集索引
需要快速擷取單一資料列時，叢集索引可能會優於叢集資料行存放區資料表。 若要疾速執行，查詢時需要單一或少數資料列查閱，請考慮使用叢集索引或非叢集次要索引。 使用叢集索引的缺點是受益的只有在叢集索引資料行上使用高度選擇性篩選的查詢。 若要改善其他資料行的篩選，請將非叢集索引加入其他資料行。 但加入資料表的每個索引均會增加載入的空間和處理時間。
若要建立叢集索引資料表，只要在 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);
最佳化叢集資料行存放區索引
叢集資料行存放區資料表會將資料組織成不同區段。 擁有高區段品質是在資料行存放區資料表上達到最佳查詢效能的關鍵。 壓縮的資料列群組中的資料列數目可以測量區段品質。 每個壓縮資料列群組至少有 10 萬個資料列時，區段品質最佳，而當每個資料列群組的資料列數趨近 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];
現在您已建立檢視，請執行查詢，識別資料列群組少於 10 萬個資料列的資料表。 若尋求更理想的區段品質，建議您提高 10 萬的閾值。
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 |
索引維護的影響
Rebuild_Index_SQLvColumnstoreDensity檢視中的資料行ALTER INDEX REBUILD，包含可用於重建索引的陳述式。 重建索引時，請確定配置足夠的記憶體給重建索引的工作階段。 若要執行這項作業，請增加使用者的資源類別，同時使用者需有權將資料表上的索引重建為建議的最小值。 如需範例，請參閱本文稍後的重建索引以提升區段品質。
若資料表有已排序的叢集資料行存放區索引，ALTER INDEX REBUILD 會使用 tempdb 重新排序資料。 在重建作業期間監視 tempdb。 如果您需要更多 tempdb 空間，請擴大資料庫集區。 當索引重建完成之後，請相應減少回來。
若資料表有已排序的叢集資料行存放區索引，ALTER INDEX REORGANIZE 不會重新排序資料。 若要重新排序資料，請使用 ALTER INDEX REBUILD。
如需詳細資訊，請參閱使用已排序的叢集資料行存放區索引微調效能。
資料行存放區索引品質不佳的原因
如果您已識別區段品質不佳的資料表，便會想要找出根本原因。 以下是區段品質不佳的一些其他常見原因︰
- 建立索引時的記憶體壓力
- 大量的 DML 作業
- 小型或緩慢移動的載入作業
- 太多資料分割
這些因素可能會導致資料行存放區索引在每個資料列群組中的資料列大幅少於最佳的 100 萬個。 它們也會造成資料列移至差異資料列群組，而不是壓縮的資料列群組。
建立索引時的記憶體壓力
每個壓縮資料列群組的資料列數目，直接與資料列寬度以及可用來處理資料列群組的記憶體數量相關。 當資料列在記憶體不足的狀態下寫入資料行存放區資料表時，資料行存放區區段品質可能會降低。 因此，最佳做法是讓寫入資料行存放區索引資料表的工作階段，存取較多的記憶體。 因為記憶體與並行間有所取捨，正確的記憶體配置指引會根據資料表各資料列中的資料、配置給系統的資料倉儲單位，及工作階段將資料寫入資料表時，您可提供的並行插槽數目執行。
大量的 DML 作業
更新和刪除資料列的大量 DML 作業，會造成資料行存放區沒有效率。 尤其是修改資料列群組中多數的資料列後。
- 從壓縮的資料列群組刪除資料列僅會以邏輯方式將資料列標示為已刪除。 資料列會保留在壓縮的資料列群組中，直到重建資料分割或資料表為止。
- 插入資料列會將資料列新增至名為差異資料列群組的內部資料列存放區資料表。 在差異資料列群組已滿且標示為已關閉之前，插入的資料列不會轉換成資料行存放區。 一旦達到 1,048,576 個資料列的容量上限，資料列群組就會關閉。
- 更新資料行存放區格式的資料列會做為邏輯刪除和插入來處理。 插入的資料列可儲存在差異存放區。
針對每個資料分割對齊分佈的 102,400 個資料列大量臨界值，超出臨界值的批次更新和插入作業會直接進入資料行存放區格式。 不過，假設在平均分佈情況下，您將需要在單一作業中修改超過 6.144 百萬個資料列才會發生這種情況。 如果指定資料分割對齊發佈的資料列數目少於 102,400 個，資料列會移至差異存放區，並存放在差異存放區，直到插入足夠的資料列，或修改資料列以關閉資料列群組，或重新建立索引。
小型或緩慢移動的載入作業
流入專用 SQL 集區的小型負載，有時也稱為緩慢負載。 它們通常代表系統接近連續擷取的串流。 不過，因為這個串流已接近連續狀態，所以資料列的容量並沒有特別大。 通常資料遠低於直接載入資料行存放區格式所需的閾值。
在這些情況下，最好先將資料登陸到 Azure Blob 儲存體中，並讓它在載入之前累積。 這項技術通常稱為 微批次處理。
太多資料分割
另一個考慮事項是資料分割對於叢集資料行存放區資料表的影響。 資料分割前，專用 SQL 集區已將資料分割成 60 個資料庫。 進一步分割會分割您的資料。 如果您將資料分割，請考慮到每個資料分割需要有至少 1 百萬個資料列，使用叢集資料行存放區索引才會有幫助。 如果資料表分割成 100 個分割區，您的資料表需要至少 60 億個資料列，才能受益於叢集資料行存放區索引 (60 個發佈 100 個分割區 1 百萬個資料列)。 如果您的 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 集區使用分割。
後續步驟
如需開發資料表的詳細資訊，請參閱開發資料表。