列存储索引 - 数据加载指南Columnstore indexes - Data loading guidance

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

使用标准 SQL 大容量加载和渗透插入方法将数据加载到列存储索引的选项和建议。Options and recommendations for loading data into a columnstore index by using the standard SQL bulk loading and trickle insert methods. 将数据加载到列存储索引是任何数据仓库过程必不可少的组成部分,因为它会将数据移到索引中,准备进行分析。Loading data into a columnstore index is an essential part of any data warehousing process because it moves data into the index in preparation for analytics.

你是列存储索引的初学者?New to columnstore indexes? 请参阅列存储索引 - 概述列存储索引 - 体系结构See Columnstore indexes - overview and Columnstore Index Architecture.

什么是大容量加载?What is bulk loading?

大容量加载指的是将大量的行添加到数据存储的方式。Bulk loading refers to the way large numbers of rows are added to a data store. 这是将数据移到列存储索引的最高效方法,因为它对成批的行进行操作。It is the most performant way to move data into a columnstore index because it operates on batches of rows. 大容量加载将行组填充到最大容量,并将它们直接压缩到列存储中。Bulk loading fills rowgroups to maximum capacity and compresses them directly into the columnstore. 只有在加载结束时未达到每行组至少 102,400 行的行才会进入增量存储。Only rows at the end of a load that don't meet the minimum of 102,400 rows per rowgroup go to the deltastore.

要执行大容量加载,可以使用 bcp 实用工具Integration Services,或从临时表中选择行。To perform a bulk load, you can use bcp Utility, Integration Services, or select rows from a staging table.

加载到聚集列存储索引Loading into a clustered columnstore index

如图所示,大容量加载:As the diagram suggests, a bulk load:

  • 不会预先为数据排序。Does not pre-sort the data. 按接收顺序将数据插入行组。Data is inserted into rowgroups in the order it is received.
  • 如果批大小 >= 102400,行将直接插入压缩的行组。If the batch size is >= 102400, the rows are directly into the compressed rowgroups. 建议选择不低于 102400 的批大小,以提高批量导入的效率,因为这样可以避免在后台线程“元组发动机 (TM)”最终将行移到压缩行组之前,将数据行移到增量行组。It is recommended that you choose a batch size >=102400 for efficient bulk import because you can avoid moving data rows to a delta rowgroups before the rows are eventually moved to compressed rowgroups by a background thread, Tuple mover (TM).
  • 如果批大小 < 102,400 或者剩余行数 < 102,400,行会载入增量行组。If the batch size < 102,400 or if the remaining rows are < 102,400, the rows are loaded into delta rowgroups.

备注

在包含非聚集列存储索引数据的行存储表上, SQL ServerSQL Server 始终将数据插入到基表。On a rowstore table with a nonclustered columnstore index data, SQL ServerSQL Server always inserts data into the base table. 数据永远不会直接插入到列存储索引。The data is never inserted directly into the columnstore index.

大容量加载有以下这些内置的性能优化:Bulk loading has these built-in performance optimizations:

  • 并行加载: 你可以有多个并发大容量加载(使用 bcp 或批量插入),每个都加载一个单独的数据文件。Parallel loads: You can have multiple concurrent bulk loads (bcp or bulk insert) that are each loading a separate data file. 与行存储大容量加载到 SQL ServerSQL Server 不同,不需要指定 TABLOCK,因为每个批量导入线程以独占方式将数据载入具有排他锁的独立行组(压缩或增量行组)。Unlike rowstore bulk loads into SQL ServerSQL Server, you don't need to specify TABLOCK because each bulk import thread will load data exclusively into a separate rowgroups (compressed or delta rowgroups) with exclusive lock on it. 使用 TABLOCK 会在表中强制使用排他锁,并且会无法并行导入数据。Using TABLOCK will force an exclusive lock on the table and you will not be able to import data in parallel.
  • 最小日志记录: 大容量加载对直接进入压缩行组的数据使用最小日志记录。Minimal logging: A bulk load uses minimal logging on data that goes directly to compressed rowgroups. 进入增量行组的任何数据都将被完全记录。Any data that goes to a delta rowgroup is fully logged. 这包括任何少于 102400 行的批大小。This includes any batch sizes that are less than 102,400 rows. 但是,使用大容量加载的目标是让大部分数据绕过增量行组。However, with bulk loading the goal is for most of the data to bypass delta rowgroups.
  • 锁定优化: 加载到压缩行组时将获取行组上的 X 锁。Locking Optimization: When loading into compressed rowgroup, the X lock on rowgroup is acquired. 但是,大容量加载到增量行组时,在行组上获取 X 锁,但 SQL ServerSQL Server 仍会锁定 PAGE/EXTENT 锁,因为 X 行组锁不是锁定层次结构的一部分。However, when bulk loading into delta rowgroup, an X lock is acquired at rowgroup but SQL ServerSQL Server still locks the locks PAGE/EXTENT because X rowgroup lock is not part of locking hierarchy.

如果列存储索引上有非聚集 B 树索引,则该索引本身没有锁定优化或日志记录优化,但对聚集列存储索引的上述优化仍然存在。If you have a nonclustered B-tree index on a columnstore index, there is no locking or logging optimization for the index itself but the optimizations on clustered columnstore index as described above are still there.

计划大容量加载大小,以便最大程度地减少增量行组Plan bulk load sizes to minimize delta rowgroups

当大多数的行压缩到列存储中而不位于增量行组中时,列存储索引的性能最佳。Columnstore indexes perform best when most of the rows are compressed into the columnstore and not sitting in delta rowgroups. 最好调整加载大小,以便行直接进入列存储并尽量绕过增量存储。It's best to size your loads so that rows go directly to the columnstore and bypass the deltastore as much as possible.

以下方案说明何时将加载的行直接转到列存储中以及何时将它们转到增量存储中。These scenarios describe when loaded rows go directly to the columnstore or when they go to the deltastore. 在示例中,每个行组可以具有 102,400-1,048,576 行。In the example, each rowgroup can have 102,400-1,048,576 rows per rowgroup. 在实践中,如果内存有压力,行组的最大大小可以小于 1,048,576 行。In practice, the maximum size of a rowgroup can be smaller than 1,048,576 rows when there is memory pressure.

要大容量加载的行Rows to Bulk Load 已添加到压缩行组的行Rows Added to the Compressed Rowgroup 已添加到增量行组的行Rows Added to the Delta Rowgroup
102,000102,000 00 102,000102,000
145,000145,000 145,000145,000

行组大小:145,000Rowgroup size: 145,000
00
1,048,5771,048,577 1,048,5761,048,576

行组大小:1,048,576。Rowgroup size: 1,048,576.
11
2,252,1522,252,152 2,252,1522,252,152

行组大小:1,048,576、1,048,576、155,000。Rowgroup sizes: 1,048,576, 1,048,576, 155,000.
00
     

以下示例显示将 1,048,577 行加载到表的结果。The following example shows the results of loading 1,048,577 rows into a table. 这些结果显示列存储(作为压缩的列段)中的一个 COMPRESSED 行组以及增量存储中的 1 行。The results show that one COMPRESSED rowgroup in the columnstore (as compressed column segments), and 1 row in the deltastore.

SELECT object_id, index_id, partition_number, row_group_id, delta_store_hobt_id, 
  state, state_desc, total_rows, deleted_rows, size_in_bytes   
FROM sys.dm_db_column_store_row_group_physical_stats  

用于批量加载的行组和增量存储Rowgroup and deltastore for a batch load

使用临时表提高性能Use a staging table to improve performance

如果加载数据只是为了在运行更多转换之前暂存数据,那么,将表加载到堆表将会比将数据加载到聚集列存储表快得多。If you are loading data only to stage it before running more transformations, loading the table to heap table will be much faster than loading the data to a clustered columnstore table. 此外,将数据加载到 [临时表][临时] 也会比将表加载到永久性存储快得多。In addition, loading data to a [temporary table][Temporary] will also load much faster than loading a table to permanent storage.

数据加载的常见模式是将数据加载到临时表,执行某种转换,然后使用以下命令将其加载到目标表A common pattern for data load is to load the data into a staging table, do some transformation and then load it into the target table using the following command

INSERT INTO <columnstore index>  
SELECT <list of columns> FROM <Staging Table>  

此命令以类似于 BCP 或批量插入的方式将数据加载到列存储索引,但操作是以单个批完成的。This command loads the data into the columnstore index in similar ways to BCP or Bulk Insert but in a single batch. 如果临时表中的行数 < 102400,行将加载到增量行组;否则,行将直接加载到压缩行组。If the number of rows in the staging table < 102400, the rows are loaded into a delta rowgroup otherwise the rows are directly loaded into compressed rowgroup. 一个重要限制是此 INSERT 操作是单线程操作。One key limitation was that this INSERT operation was single threaded. 要并行加载数据,可以创建多个临时表,或者针对临时表中不重叠的行范围发出 INSERT/SELECTTo load data in parallel, you could create multiple staging table or issue INSERT/SELECT with non-overlapping ranges of rows from the staging table. SQL Server 2016 (13.x)SQL Server 2016 (13.x) 中解除了这种限制。This limitation goes away with SQL Server 2016 (13.x)SQL Server 2016 (13.x). 以下命令从临时表并行加载数据,但需要指定 TABLOCKThe command below loads the data from staging table in parallel but you will need to specify TABLOCK.

INSERT INTO <columnstore index> WITH (TABLOCK) 
SELECT <list of columns> FROM <Staging Table>  

从临时表加载到聚集列存储索引时,可以使用以下优化:There are following optimizations available when loading into clustered columnstore index from staging table:

  • 日志优化: 将数据载入压缩行组时,最大限度地减少记录的信息。Log Optimization: Minimally logged both when the data is loaded into compressed rowgroup. 将数据载入增量行组时,不会进行少量的日志记录。No minimal logging when data gets loaded into delta rowgroup.
  • 锁定优化: 加载到压缩行组时将获取行组上的 X 锁。Locking Optimization: When loading into compressed rowgroup, the X lock on rowgroup is acquired. 但是,对于增量行组,在行组上获取 X 锁,但 SQL ServerSQL Server 仍会锁定 PAGE/EXTENT 锁,因为 X 行组锁不是锁定层次结构的一部分。However, with delta rowgroup, an X lock is acquired at rowgroup but SQL ServerSQL Server still locks the locks PAGE/EXTENT because X rowgroup lock is not part of locking hierarchy.

如果你有一个或多个非聚集索引,则索引本身不会经过锁定或日志记录优化,但是,针对聚集列存储索引的上述优化仍然存在If you have or more nonclustered indexes, there is no locking or logging optimization for the index itself but the optimizations on clustered columnstore index as described above are still there

什么是渗透插入?What is trickle insert?

渗透插入指的是将单个行移到列存储索引的方式。Trickle insert refers to the way individual rows move into the columnstore index. 渗透插入使用 INSERT INTO 语句。Trickle inserts use the INSERT INTO statement. 使用渗透插入时,所有行将进入增量存储。With trickle insert, all of the rows go to the deltastore. 这适用于少量的行,但对于较大负载而言并不实用。This is useful for small numbers of rows, but not practical for large loads.

INSERT INTO <table-name> VALUES (<set of values>)  

备注

使用 INSERT INTO 将值插入聚集列存储索引的并发线程可能会将行插入相同的增量存储行组。Concurrent threads using INSERT INTO to insert values into a clustered columnstore index can insert rows into the same deltastore rowgroup.

一旦行组包含 1,048,576 行,增量行组就会标记为已关闭但仍可供查询和更新/删除操作使用,但新插入的行会进入现有或新建的增量存储行组。Once the rowgroup contains 1,048,576 rows, the delta rowgroup us marked closed but it is still available for queries and update/delete operations but the newly inserted rows go into an existing or newly created deltastore rowgroup. 后台线程 元组发动机 (TM) 将每隔大约 5 分钟定期压缩已关闭的增量行组。There is a background thread Tuple Mover (TM) that compresses the closed delta rowgroups periodically every 5 minutes or so. 你可以显式调用以下命令来压缩已关闭的增量行组You can explicitly invoke the following command to compress the closed delta rowgroup

ALTER INDEX <index-name> on <table-name> REORGANIZE  

如果你要强制关闭并压缩增量行组,可以执行以下命令。If you want force a delta rowgroup closed and compressed, you can execute the following command. 如果你已完成加载行并且不希望插入任何新行,则可能需要运行此命令。You may want run this command if you are done loading the rows and don't expect any new rows. 通过显式关闭并压缩增量行组,可以进一步节省存储空间,提高分析查询性能。By explicitly closing and compressing the delta rowgroup, you can save storage further and improve the analytics query performance. 最佳做法之一是在不希望插入新行时调用此命令。A best practice is to invoke this command if you don't expect new rows to be inserted.

ALTER INDEX <index-name> on <table-name> REORGANIZE with (COMPRESS_ALL_ROW_GROUPS = ON)  

加载到已分区表的工作原理How loading into a partitioned table works

对于已分区数据, SQL ServerSQL Server 首先将每一行分配给一个分区,然后对该分区内的数据执行列存储操作。For partitioned data, SQL ServerSQL Server first assigns each row to a partition, and then performs columnstore operations on the data within the partition. 每个分区都具有自己的行组以及至少一个增量行组。Each partition has its own rowgroups and at least one delta rowgroup.

后续步骤Next steps

博客文章现托管在 techcommunity 上,撰写日期为 2015 年 3 月 11 日:有关聚集列存储索引的数据加载性能注意事项Blog post now hosted on techcommunity, written 2015-03-11: Data Loading performance considerations with Clustered Columnstore indexes.