主页 / dba / 问题 / 158651
Accepted
Jason
Asked: 2016-12-20 18:44:53 +0800 CST

在保持行顺序的同时创建聚集列存储索引的代码

  • 772

我想通过创建聚集列存储索引将行存储表转换为列存储表。表中有三列：id、time 和 value。

该表在创建列存储索引之前按 id 和时间排序；但是，在创建列存储索引后，行顺序就搞砸了。我认为这可能是由于并行性并添加了maxdop = 1选项，但这并没有解决问题。谁能帮我这个？

这是创建表和索引的代码：

-- creating rowstore table
drop table if exists tab1_rstore
select id, time, value
into tab1_rstore
from tab0
order by id_loan, period
option(maxdop 1)

-- creating clustered index on rowstore table
create clustered index idx on tab1_rstore (id,time)

-- creating columnstore table 
select * 
into tab1_cstore
from tab1_rstore
    option(maxdop 1)

-- comparing the first two rows from these two tables
    select top 2 *
from tab1_rstore

    select top 2 *
from tab1_cstore

查询结果截图：

c-index 前的前两行

-- creating clustered columnstore index
create clustered columnstore index idx on tab1_cstore 
with (maxdop = 1) 

-- comparing the top two rows again
select top 2 *
from tab1_rstore

select top 2 *
from tab1_cstore

使用列存储索引的查询结果截图：

在此处输入图像描述

我的理解是行的顺序是由压缩算法决定的，我们对此无能为力，请参阅此处文档中的限制和限制，并引用以下引用：

不能包含用于对索引进行排序的 ASC 或 DESC 关键字。列存储索引根据压缩算法进行排序。排序会消除许多性能优势。

我在 Windows 10 64 位上使用 SQL Server 2016 开发人员版。

sql-server sql-server-2016

2 个回答

  1. Best Answer

    聚集列存储索引与聚集行存储索引根本不同。您可能已经注意到聚集列存储索引没有键列规范。没错：聚集列存储索引是没有键的索引 - 所有列都“包含”。

    对于聚集列存储索引，我听到的最直观的描述是将其视为面向列的堆表（“RID”所在的位置rowgroup_id, row_number）。

    如果您需要索引来支持直接排序和/或点/小范围选择，您可以在 SQL Server 2016 中的聚集列存储之上创建可更新的行存储 b 树索引。

    在许多情况下，这根本没有必要，因为列存储访问和批处理模式排序是如此之快。人们对行存储性能“了解”的许多事情都需要重新学习以用于列存储。扫描和散列很好:)

    也就是说，列存储当然有其行组的结构（以及关于每个段中的最小值/最大值的元数据），这在可以从行组/段消除中受益的查询中很有用。

    这方面的一项重要技术是首先创建具有所需顺序的聚集行存储索引，然后使用该WITH (DROP_EXISTING = ON, MAXDOP = 1)选项创建聚集列存储索引。在您的示例中：

    CREATE [UNIQUE] CLUSTERED INDEX idx 
ON dbo.tab1_cstore (id, time)
WITH (MAXDOP = 1);

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

    随着时间的推移，需要注意保持行组/段消除的好处。此外，虽然列存储已按行组隐式分区，但您也可以显式分区。

    我不能 100% 确定您要测试什么，但段内值的“顺序”确实是由压缩算法决定的。我关于创建列存储索引的观点DROP_EXISTING是关于流入段创建过程的数据的排序，以便整体段将以特定方式排序。在该段内，所有赌注都已关闭。

    • 15

  2. 顺便说一句，我相信 SQL Server Tiger 团队意识到需要ORDER BY在列存储索引构建上使用子句或类似内容，以便最大限度地消除行组的机会。

    此外，此时确保在构建列存储索引时使用，就像在另一个答案中一样，这一点非常重要。如果不这样做，您将导致将数据划分为多个线程，这可能会大大降低最有效的行组消除的可能性。MAXDOP = 1

    • 2

相关问题