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)。 可改为考虑使用堆或聚集索引。
• 对瞬态数据使用列存储表可能会降低效率。 考虑使用堆表和临时表。
• 包含少于 6000 万行的小型表。 可以考虑使用堆表。

将数据暂时移入专用 SQL 池时，你可能会发现使用堆表可让整个过程更快速。 这是因为堆的加载速度比索引表还要快，在某些情况下，可以从缓存执行后续读取。 如果您加载数据的目的是为了在执行更多转换之前暂存它，那么将数据加载到堆表中要比加载到聚集列存储表中快得多。 此外，将数据载入临时表也比将表载入永久存储更快速。 在加载数据后，可以在表中创建索引来提高查询性能。

超过 6 千万行后，聚集列存储表开始达到最佳压缩性能。 对于少于 6000 万行的小型查找表，请考虑使用 HEAP 或聚集索引来提高查询性能。

要创建堆表，请在 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]
,       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];

现在已创建视图，请运行此查询来识别哪些表的行组中包含的行少于 10 万个。 如果要实现更理想的段质量，你可能需要将 10 万的阈值提高。

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

运行查询后就可以开始查看数据并分析结果。 下表解释了要在行组分析中查看的内容。

Rebuild_Index_SQL 视图中的列 vColumnstoreDensity 包含一个 ALTER INDEX REBUILD 语句，该语句可用于重建索引。 重建索引时，请确保将足够的内存分配给要重建索引的会话。 为此，请提高用户的资源类，该用户有权将此表中的索引重建为建议的最小值。 有关示例，请参阅本文后面的重建索引以提升段质量。

对于包含有序聚集列存储索引的表，ALTER INDEX REBUILD 会使用 tempdb 对数据进行重新排序。 重新生成操作期间监视 tempdb。 如果需要更多 tempdb 空间，请纵向扩展数据库池。 完成索引重新生成之后，缩小为原空间大小。

对于具有有序聚合列存储索引的表，ALTER INDEX REORGANIZE 不会对数据重新排序。 若要对数据重新排序，请使用 ALTER INDEX REBUILD。

有关有序聚集列存储索引的详细信息，请参阅使用有序聚集列存储索引优化性能。

如果已识别出段质量不佳的表，你需要确定根本原因。 下面是段质量不佳的其他一些常见原因：

1. 构建索引时的内存压力
2. 大量的 DML 操作
3. 小型或渗透负载操作
4. 过多的分区

这些因素可能导致列存储索引在每个行组中的行数显著少于最佳的 100 万行。 它们还会造成行转到增量行组而不是压缩的行组。

每个压缩行组的行数，与行宽度以及可用于处理行组的内存量直接相关。 当行在内存不足的状态下写入列存储表时，列存储分段质量可能降低。 因此，最佳做法是为向列存储索引表写入数据的会话尽可能提供最多的内存访问权限。 因为内存与并发性之间有所取舍，正确的内存分配指导原则取决于表的每个行中的数据、已分配给系统的数据仓库，以及可以提供给将数据写入表的会话的并发访问槽位数。

更新和删除行的大量 DML 操作可能造成列存储低效。 当行组中的大多数行已修改时尤其如此。

• 从压缩的行组中删除某行只会以逻辑方式将该行标记为已删除。 该行仍会保留在压缩的行组中，直到重新生成分区或表为止。
• 插入某行会将该行添加到名为增量行组的内部行存储表中。 在增量行组已满且标记为已关闭之前，插入的行不会转换成列存储。 达到 1,048,576 个行的容量上限后，行组会关闭。
• 更新采用列存储格式的行将依次作为逻辑删除和插入进行处理。 插入的行可以存储在增量存储中。

超出按分区对齐分布的 102,400 行批量阈值的批量更新和插入操作将直接采用列存储格式。 但是，假设是平均分布，你需要在单次操作中修改超过 614.4 万行才能发生这种情况。 如果给定的按分区对齐分布的行数小于 102,400，则行将转到增量存储并在该处保留，直到插入或修改了足够的行来关闭行组，或直到重建索引为止。

流入专用 SQL 池的小型负载有时也称为渗透负载。 它们通常代表系统将引入的接近恒定的数据流。 但由于此流接近连续状态，因此行的数量并不特别大。 通常数据远低于直接加载到列存储格式所需的阈值。

在这些情况下，最好先将数据保存到 Azure Blob 存储中，并让它在加载之前累积。 此技术通常称为微批处理。

另一个考虑因素是分区对聚集列存储表的影响。 分区之前，专用 SQL 池已将数据分散到 60 个数据库。 进一步分区会分割数据。 如果将数据分区，则要考虑每个分区至少需要 100 万行，才能受益于使用聚集列存储索引。 如果将表划分为 100 个分区，则表至少需要 60 亿行才能从聚集列存储索引中受益（60 个分布区 100 个分区 100 万行）。 如果包含 100 个分区的表没有 60 亿行，请减少分区数目，或考虑改用堆表。

在表中加载一些数据后，请遵循以下步骤来识别并重建聚集列存储索引质量欠佳的表。

立即提升段质量的快速方法是重建索引。 上述视图返回的 SQL 将包含可用于重建索引的 ALTER INDEX REBUILD 语句。 重建索引时，请确保将足够的内存分配给要重建索引的会话。 为此，请提高用户的资源类，该用户有权将此表中的索引重建为建议的最小值。

以下示例演示如何通过提高资源类向用户分配更多内存。 若要使用资源类，请参阅用于工作负荷管理的资源类。

EXEC sp_addrolemember 'xlargerc', 'LoadUser';

以现在使用更高资源类别的步骤 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 REBUILD”部分和 ALTER INDEX。

重新运行识别出段质量不佳的表的查询，并验证段质量是否已改善。 如果段质量并未改善，原因可能是表中的行太宽。 请考虑在重建索引时使用较高的资源类或 DWU。

此示例使用 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 池中使用分区。

有关开发表的详细信息，请参阅开发表。