文章/答案/技术大牛

发布

社区首页 >问答首页 >加载(和查询)大型集群列存储表的正确和可执行的模式是什么？

问加载(和查询)大型集群列存储表的正确和可执行的模式是什么？
EN

Database Administration用户

提问于 2021-01-10 14:16:46

回答 2查看 527关注 0票数 2

我正在对集群列存储表进行一些测试--包括分区表和非分区表。在我的研究中，我看到了这种模式的味道：

-- step 1: make new table
SELECT TOP 0 * INTO [Fact].[Order_CCI] FROM [Fact].[Order];

-- step 2: rowstore by date

CREATE CLUSTERED INDEX [CCI_Order] ON [Fact].[Order_CCI]
(
  [Order Date Key]
) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) 
ON [ps_OrderDate]([Order Date Key])

-- step 3: insert data

INSERT INTO [Fact].[Order_CCI] WITH(TABLOCK)
SELECT
  [Order Key]
  ,[City Key]
  ,[Customer Key]
  ,[Stock Item Key]
  ,[Order Date Key]
  ....

FROM [Fact].[Order]

-- step 4: replace rowstore with columnstore

CREATE CLUSTERED COLUMNSTORE INDEX [CCI_Order]
ON [Fact].[Order_CCI]
WITH (DROP_EXISTING = ON, COMPRESSION_DELAY = 0)

在切换到CCI之前，它使用行存储索引按所需列对数据进行排序。

但在其他文章或帖子中，我看到插入声明中有明确的“订单”：

INSERT INTO [Fact].[Order_CCI] WITH(TABLOCK)
SELECT
  [Order Key]
  ,[City Key]
  ,[Customer Key]
  ,[Stock Item Key]
  ,[Order Date Key]
  ....

FROM [Fact].[Order]
ORDER BY [Order Date Key]

我有这些问题。

对于使用分区的大型(500米)表，这是最具性能的代码模式吗？相反，作者https://janizajcbi.com/2018/09/14/row-store-to-column-store-story/在循环中填充分区。

订单是需要的吗？我在一个小(30m行)的表上运行了这个模式，不管有没有按顺序排序，CCI元数据都显示段的日期对齐/排序。我认为不需要这样的命令。

但我只是在大表上运行了相同的程序，并且没有对日期对齐/排序。但请再次注意，它是一个大得多的表，也是分区的。

下面是没有分区但确实导致排序段的SQL模式。

CREATE TABLE dbo.RiskExposure (
 ...
);
GO

INSERT INTO dbo.RiskExposure WITH (TABLOCK)
(
  ...
)
SELECT
...
FROM dbo.RiskExposureStage
WHERE Close_of_Bus_ > '2020-08-31'
GO

CREATE CLUSTERED INDEX CCI_RiskExposure
  ON dbo.RiskExposure ([Close_of_Bus_]);
GO

CREATE CLUSTERED COLUMNSTORE INDEX CCI_RiskExposure
  ON dbo.Risk WITH (MAXDOP = 1, DROP_EXISTING = ON);

下面是一个更完整的测试SQL，我运行的测试做了分区，但没有导致排序段。

CREATE TABLE dbo.RiskExposure (
  ...
);
GO


INSERT INTO dbo.RiskExposure WITH (TABLOCK)
(
  ...
)
SELECT
  ...
FROM dbo.RiskExposureStage
WHERE Close_of_Bus_ > '2020-08-31' -- test with a subset
GO

CREATE CLUSTERED INDEX CCI_RiskExposure
    ON dbo.RiskExposure ( [Close_of_Bus_] )
        WITH (DATA_COMPRESSION = PAGE)
            ON ps_QuarterlyPartitionScheme3m( [Close_of_Bus_] ); 

CREATE CLUSTERED COLUMNSTORE INDEX CCI_RiskExposure
    ON dbo.RiskExposure
        WITH (DROP_EXISTING = ON)
            ON ps_QuarterlyPartitionScheme3m( [Close_of_Bus_] ); 

GO

ALTER INDEX CCI_RiskExposure
    ON dbo.RiskExposure
    REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

-- get stats

SELECT
    partitions.partition_number,
    column_store_segments.segment_id,
    column_store_segments.min_data_id,
    column_store_segments.max_data_id,
    column_store_segments.row_count
FROM sys.column_store_segments
  INNER JOIN sys.partitions
    ON column_store_segments.hobt_id = partitions.hobt_id
  INNER JOIN sys.indexes
    ON indexes.index_id = partitions.index_id
      AND indexes.object_id = partitions.object_id
  INNER JOIN sys.tables
    ON tables.object_id = indexes.object_id
  INNER JOIN sys.columns
    ON tables.object_id = columns.object_id
      AND column_store_segments.column_id = 
     columns.column_id
WHERE tables.name = 'RiskExposure'
  AND columns.name = 'Close_of_bus_'
ORDER BY tables.name, columns.name, partitions.partition_number, column_store_segments.segment_id;

以下是未分区表的测试的统计数据子集。请注意，行组已打包(除#35外)并已排序。

partition_number    segment_id  min_data_id max_data_id row_count
1   0   737668  737673  1048576
1   1   737673  737677  1048576
1   2   737677  737680  1048576
1   3   737680  737684  1048576
1   4   737684  737686  1048576
1   5   737686  737689  1048576
1   6   737689  737691  1048576
1   7   737691  737693  1048576
1   8   737693  737695  1048576
....
1   37  737786  737789  1048576
1   38  737789  737794  1048576
1   39  737794  737799  742086
1   40  737799  737799  34421

下面是使用分区的测试的统计数据。请注意，没有排序行组。注意，为了简洁起见，我只显示中间分区。

partition_number    segment_id  min_data_id max_data_id row_count

17  0   737698  737721  1048576
17  1   737698  737708  1048576
17  2   737698  737716  1048576
17  3   737698  737711  1048576
17  4   737712  737728  1048576
17  5   737716  737717  324458
17  6   737717  737744  1048576
17  7   737721  737723  1048576
17  8   737708  737768  1048576
17  9   737711  737788  1048576
17  10  737728  737734  1048576
17  11  737744  737747  1048576
17  12  737788  737789  643250
17  13  737723  737726  1048576
17  14  737768  737772  1048576
17  15  737734  737739  1048576
17  16  737747  737750  1048576
17  17  737726  737728  1048576
17  18  737772  737776  1048576
17  19  737728  737728  193276
17  20  737739  737742  1048576
17  21  737750  737752  1048576
17  22  737742  737743  314208
17  23  737776  737779  1048576
17  24  737752  737755  1048576
17  25  737779  737781  1048576
17  26  737755  737757  1048576
17  27  737781  737784  1048576
17  28  737757  737760  1048576
17  29  737784  737786  920474
17  30  737760  737764  686860
17  31  737716  737789  967708
17  32  737728  737743  507484

在重新运行通过添加MAXDOP=1进行分区的查询之后，我将得到排序段，就像在非分区测试中一样。因此，我的错误是遗漏了分区测试的关键设置。现在，我正在考虑插入新数据的正确逻辑是什么。我经常看到这样的建议: 1.创建阶段表2.负载暂存表3.将聚集索引添加到阶段表4. INSERT INTO <Final Table> SELECT <list columns> FROM <Staging Table>

sql-server

etl

columnstore

回答 2

Database Administration用户

发布于 2021-01-10 16:44:21

INSERT的order不能保证按顺序加载数据。特别是插入。。。SELECT可以并行化，生成的表不会按任何特定顺序加载。

即使是单线程，也会被INSERT忽略.选择。恩格

drop table if exists test 
go
select *
into test 
from FactInternetSales where 1=0

create clustered columnstore index cci_test on test

insert into test --with (tablock)
select s.* from FactInternetSales s
order by ProductKey

插入没有排序操作符：

这里记录了这一点：

当与SELECT...INTO语句一起使用以插入来自另一个源的行时，order子句并不保证按指定的顺序插入行。

选择-按条款订购(Transact-SQL)

如果表足够大，您可以检查列段，以确定它们实际上不是由ProdctKey排序的。

select object_name(p.object_id) table_name, s.segment_id, c.name column_name,  s.min_data_id, s.max_data_id
from sys.column_store_segments s
join sys.partitions p
  on s.hobt_id = p.hobt_id 
left join sys.columns c
  on c.object_id = p.object_id 
  and c.column_id = s.column_id
where c.name = 'ProductKey'

票数 2

Database Administration用户

发布于 2021-01-10 14:49:45

也许还有其他的东西可以帮助你改进你想要达到的过程，但我知道以下几点：

首先创建行存储索引，然后为初始数据加载和创建索引创建列存储索引，这将使服务器的工作几乎加倍。但是这样做，您将得到一个更好的对齐列存储索引，这将导致稍后对表进行更高效的查询。因此，如果您希望在以后对表进行查询时尽量减少服务器的前期负载，并为此付费，或者先使用load，那么将来使用列存储索引的查询将更有效。这取决于您将进行什么样的查询以及多久进行一次查询。
如果您选择创建行存储索引，那么最好先将数据插入无索引表，然后再创建行存储索引(因此您将交换上面的步骤2和步骤3)。Rowstore索引通常在拥有整个数据集之前对数据进行最有效的排序。
对于TABLOCK，您应该使用INSERT提示(如第一个示例中所示)。这将最小化日志记录，并自动获取单个表级锁，而不是试图获取最终升级的多个行级锁。因此，在大型INSERT中使用它肯定会带来性能上的好处。
我同意您的观点，我不认为ORDER BY在创建列存储索引时会给您带来任何好处，但是当查询利用列存储索引时，可能会影响对齐和将来的查询性能(参见#1)。
我相信(虽然不是肯定的)，在表完全加载数据之后创建列存储索引将是创建它的最佳时机(与#2的推理相同)。

撇开这些点不说，您的表正处于我建议使用分区的位置，因为您将能够更细致地选择如何维护该数据(通过分区)，这意味着您可以更聪明地进行更有效的操作，而无需使用资源争用来破坏服务器。

票数 1

页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://dba.stackexchange.com/questions/282944

复制

相似问题

问加载(和查询)大型集群列存储表的正确和可执行的模式是什么？
EN

回答 2

Database Administration用户

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问加载(和查询)大型集群列存储表的正确和可执行的模式是什么？EN

回答 2

Database Administration用户

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问加载(和查询)大型集群列存储表的正确和可执行的模式是什么？
EN