使用有序聚集列存储索引优化性能Performance tuning with ordered clustered columnstore index

当用户查询 Synapse SQL 池中的列存储表时,优化器会检查每个段中存储的最小值和最大值。When users query a columnstore table in Synapse SQL pool, the optimizer checks the minimum and maximum values stored in each segment. 超出查询谓词边界的段不会从磁盘读取到内存。Segments that are outside the bounds of the query predicate aren't read from disk to memory. 如果要读取的段的数目及其总大小较小,则查询性能可以更快。A query can get faster performance if the number of segments to read and their total size are small.

有序与无序聚集列存储索引Ordered vs. non-ordered clustered columnstore index

默认情况下,对于不是使用索引选项创建的每个表,某个内部组件(索引生成器)将在该表中创建无序的聚集列存储索引 (CCI)。By default, for each table created without an index option, an internal component (index builder) creates a non-ordered clustered columnstore index (CCI) on it. 每个列中的数据压缩成单独的 CCI 行组段。Data in each column is compressed into a separate CCI rowgroup segment. 每个段的值范围都有元数据,因此,在执行查询期间,不会从磁盘中读取超出查询谓词边界的段。There's metadata on each segment's value range, so segments that are outside the bounds of the query predicate aren't read from disk during query execution. CCI 提供最高级别的数据压缩,可减少要读取的段大小,因此查询可以更快地运行。CCI offers the highest level of data compression and reduces the size of segments to read so queries can run faster. 但是,由于索引生成器在将数据压缩成段之前不会将数据排序,因此可能会出现值范围重叠的段,从而导致查询从磁盘中读取更多的段,需要更长的时间才能完成。However, because the index builder doesn't sort data before compressing them into segments, segments with overlapping value ranges could occur, causing queries to read more segments from disk and take longer to finish.

创建有序 CCI 时,Synapse SQL 引擎会先按顺序键将内存中的现有数据排序,然后,索引生成器将这些数据压缩成索引段。When creating an ordered CCI, the Synapse SQL engine sorts the existing data in memory by the order key(s) before the index builder compresses them into index segments. 使用有序数据可以减少段重叠的情况,使查询更有效地消除段,因而可提高性能,因为要从磁盘读取的段数更少。With sorted data, segment overlapping is reduced allowing queries to have a more efficient segment elimination and thus faster performance because the number of segments to read from disk is smaller. 如果可以一次性在内存中为所有数据排序,则可以避免段重叠的情况。If all data can be sorted in memory at once, then segment overlapping can be avoided. 由于数据仓库中的表较大,因此这种情况不经常发生。Due to large tables in data warehouses, this scenario doesn't happen often.

若要检查列的段范围,请结合表名称和列名称运行以下命令:To check the segment ranges for a column, run the following command with your table name and column name:

SELECT o.name, pnp.index_id, 
cls.row_count, pnp.data_compression_desc, 
pnp.pdw_node_id, pnp.distribution_id, cls.segment_id, 
cls.column_id, 
cls.min_data_id, cls.max_data_id, 
cls.max_data_id-cls.min_data_id as difference
FROM sys.pdw_nodes_partitions AS pnp
   JOIN sys.pdw_nodes_tables AS Ntables ON pnp.object_id = NTables.object_id AND pnp.pdw_node_id = NTables.pdw_node_id
   JOIN sys.pdw_table_mappings AS Tmap  ON NTables.name = TMap.physical_name AND substring(TMap.physical_name,40, 10) = pnp.distribution_id
   JOIN sys.objects AS o ON TMap.object_id = o.object_id
   JOIN sys.pdw_nodes_column_store_segments AS cls ON pnp.partition_id = cls.partition_id AND pnp.distribution_id  = cls.distribution_id
JOIN sys.columns as cols ON o.object_id = cols.object_id AND cls.column_id = cols.column_id
WHERE o.name = '<Table Name>' and cols.name = '<Column Name>'  and TMap.physical_name  not like '%HdTable%'
ORDER BY o.name, pnp.distribution_id, cls.min_data_id 


Note

在有序 CCI 表中,同一批 DML 或数据加载操作生成的新数据将在该批中排序,而表中的所有数据不会经过全局排序。In an ordered CCI table, the new data resulting from the same batch of DML or data loading operations are sorted within that batch, there is no global sorting across all data in the table. 用户可以重新生成 (REBUILD) 有序 CCI 来对表中的所有数据进行排序。Users can REBUILD the ordered CCI to sort all data in the table. 在 Synapse SQL 中,列存储索引重新生成是一项脱机操作。In Synapse SQL, the columnstore index REBUILD is an offline operation. 对于已分区的表,每次将对一个分区执行重新生成。For a partitioned table, the REBUILD is done one partition at a time. 重新生成的分区中的数据是“脱机”的,在对该分区完成重新生成之前,这些数据不可用。Data in the partition that is being rebuilt is "offline" and unavailable until the REBUILD is complete for that partition.

查询性能Query performance

有序 CCI 带来的查询性能提升程度取决于查询模式、数据大小、数据排序的合理性、段的物理结构,以及为查询执行选择的 DWU 和资源类。A query's performance gain from an ordered CCI depends on the query patterns, the size of data, how well the data is sorted, the physical structure of segments, and the DWU and resource class chosen for the query execution. 在设计有序 CCI 表时,用户应在选择排序列之前考虑所有这些因素。Users should review all these factors before choosing the ordering columns when designing an ordered CCI table.

具有所有这些模式的查询在使用有序 CCI 时运行速度往往更快。Queries with all these patterns typically run faster with ordered CCI.

  1. 查询具有相等性、不相等性或范围谓词The queries have equality, inequality, or range predicates
  2. 谓词列和有序 CCI 列相同。The predicate columns and the ordered CCI columns are the same.
  3. 谓词列的使用顺序与有序 CCI 列的列序号相同。The predicate columns are used in the same order as the column ordinal of ordered CCI columns.

在此示例中,表 T1 具有一个已按 Col_C、Col_B 和 Col_A 顺序排序的聚集列存储索引。In this example, table T1 has a clustered columnstore index ordered in the sequence of Col_C, Col_B, and Col_A.


CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON  T1
ORDER (Col_C, Col_B, Col_A)

如果使用有序 CCI,查询 1 的性能比其他三个查询更好。The performance of query 1 can benefit more from ordered CCI than the other three queries.

-- Query #1: 

SELECT * FROM T1 WHERE Col_C = 'c' AND Col_B = 'b' AND Col_A = 'a';

-- Query #2

SELECT * FROM T1 WHERE Col_B = 'b' AND Col_C = 'c' AND Col_A = 'a';

-- Query #3
SELECT * FROM T1 WHERE Col_B = 'b' AND Col_A = 'a';

-- Query #4
SELECT * FROM T1 WHERE Col_A = 'a' AND Col_C = 'c';

数据加载性能Data loading performance

将数据载入有序 CCI 表中的性能类似于将数据载入已分区的表。The performance of data loading into an ordered CCI table is similar to a partitioned table. 由于需要执行数据排序操作,将数据载入有序 CCI 表所需的时间可能比载入无序 CCI 表更长,但之后,查询可以使用有序 CCI 更快地运行。Loading data into an ordered CCI table can take longer than a non-ordered CCI table because of the data sorting operation, however queries can run faster afterwards with ordered CCI.

以下示例将数据载入采用不同架构的表的性能做了比较。Here is an example performance comparison of loading data into tables with different schemas.

Performance_comparison_data_loading

以下示例对使用 CCI 和有序 CCI 的查询性能做了比较。Here is an example query performance comparison between CCI and ordered CCI.

Performance_comparison_data_loading

减少段重叠Reduce segment overlapping

重叠段的数目取决于要排序的数据的大小、可用内存,以及创建有序 CCI 期间的最大并行度 (MAXDOP) 设置。The number of overlapping segments depends on the size of data to sort, the available memory, and the maximum degree of parallelism (MAXDOP) setting during ordered CCI creation. 以下选项可以在创建有序 CCI 时减少段重叠情况。Below are options to reduce segment overlapping when creating ordered CCI.

  • 在更高的 DWU 上使用 xlargerc 资源类,以便在索引生成器将数据压缩成段之前,有更多的内存可用于数据排序。Use xlargerc resource class on a higher DWU to allow more memory for data sorting before the index builder compresses the data into segments. 进入索引段后,数据的物理位置不可更改。Once in an index segment, the physical location of the data cannot be changed. 段内部或者段之间不会发生数据排序。There's no data sorting within a segment or across segments.

  • 使用 MAXDOP = 1 创建有序 CCI。Create ordered CCI with MAXDOP = 1. 用于创建有序 CCI 的每个线程针对一部分数据运行,并在本地为数据排序。Each thread used for ordered CCI creation works on a subset of data and sorts it locally. 已由不同线程排序的数据不会经过全局排序。There's no global sorting across data sorted by different threads. 使用并行线程可以减少创建有序 CCI 所需的时间,但生成的重叠段比使用单个线程时更多。Using parallel threads can reduce the time to create an ordered CCI but will generate more overlapping segments than using a single thread. 目前,MAXDOP 选项只可用于通过 CREATE TABLE AS SELECT 命令创建有序 CCI 表。Currently, the MAXDOP option is only supported in creating an ordered CCI table using CREATE TABLE AS SELECT command. 通过 CREATE INDEX 或 CREATE TABLE 命令创建有序 CCI 时不支持 MAXDOP 选项。Creating an ordered CCI via CREATE INDEX or CREATE TABLE commands does not support the MAXDOP option. 例如,For example,

CREATE TABLE Table1 WITH (DISTRIBUTION = HASH(c1), CLUSTERED COLUMNSTORE INDEX ORDER(c1) )
AS SELECT * FROM ExampleTable
OPTION (MAXDOP 1);
  • 将数据载入表之前,预先按排序键将数据排序。Pre-sort the data by the sort key(s) before loading them into tables.

下面是有序 CCI 表分布示例,该表根据上述建议清除了段重叠情况。Here is an example of an ordered CCI table distribution that has zero segment overlapping following above recommendations. 该有序 CCI 表是使用 MAXDOP 1 和 xlargerc,基于 20-GB 的堆表通过 CTAS 在 DWU1000c 数据库中创建的。The ordered CCI table is created in a DWU1000c database via CTAS from a 20-GB heap table using MAXDOP 1 and xlargerc. CCI 已按照不包含重复项的 BIGINT 列进行排序。The CCI is ordered on a BIGINT column with no duplicates.

Segment_No_Overlapping

在大型表中创建有序 CCICreate ordered CCI on large tables

创建有序 CCI 是一项脱机操作。Creating an ordered CCI is an offline operation. 对于不包含分区的表,在有序 CCI 创建过程完成之前,用户无法访问数据。For tables with no partitions, the data won't be accessible to users until the ordered CCI creation process completes. 对于已分区的表,由于引擎将按分区创建有序的 CCI 分区,因此,在尚未进行有序 CCI 创建操作的情况下,用户仍可以访问分区中的数据。For partitioned tables, since the engine creates the ordered CCI partition by partition, users can still access the data in partitions where ordered CCI creation isn't in process. 在大型表中创建有序 CCI 的过程中,可以使用此选项来尽量减少停机时间:You can use this option to minimize the downtime during ordered CCI creation on large tables:

  1. 在目标大型表(名为 Table_A)中创建分区。Create partitions on the target large table (called Table_A).
  2. 使用与表 A 相同的表架构和分区架构创建空的有序 CCI 表(名为 Table_B)。Create an empty ordered CCI table (called Table_B) with the same table and partition schema as Table A.
  3. 将一个分区从表 A 切换到表 B。Switch one partition from Table A to Table B.
  4. 针对 表 B 运行 ALTER INDEX <Ordered_CCI_Index> ON <Table_B> REBUILD PARTITION = <Partition_ID>,以重新生成换入的分区。Run ALTER INDEX <Ordered_CCI_Index> ON <Table_B> REBUILD PARTITION = <Partition_ID> on Table B to rebuild the switched-in partition.
  5. 针对 Table_A 中的每个分区重复步骤 3 和 4。Repeat step 3 and 4 for each partition in Table_A.
  6. 将所有分区从 Table_A 切换到 Table_B 并重新生成这些分区后,删除 Table_A,并将 Table_B 重命名为 Table_A。Once all partitions are switched from Table_A to Table_B and have been rebuilt, drop Table_A, and rename Table_B to Table_A.

示例Examples

A.检查有序列和序号:A. To check for ordered columns and order ordinal:

SELECT object_name(c.object_id) table_name, c.name column_name, i.column_store_order_ordinal 
FROM sys.index_columns i 
JOIN sys.columns c ON i.object_id = c.object_id AND c.column_id = i.column_id
WHERE column_store_order_ordinal <>0

B.若要更改列序号,请在顺序列表中添加或删除列,或者从 CCI 更改为有序 CCI:B. To change column ordinal, add or remove columns from the order list, or to change from CCI to ordered CCI:

CREATE CLUSTERED COLUMNSTORE INDEX InternetSales ON  InternetSales
ORDER (ProductKey, SalesAmount)
WITH (DROP_EXISTING = ON)

后续步骤Next steps

有关更多开发技巧,请参阅 开发概述For more development tips, see development overview.