title: SQL Server 2014聚集列存储索引

author: 风移

摘要

微软在SQL Server 2012引入了列存储技术，使得OLAP和Data warehouse场景性能提升10X，并且数据压缩能力超过传统表7X。这项技术包含三个方面的创新：列存储索引、Batch Mode Processing和基于Column Segment的压缩。但是，SQL Server 2012列存储索引的一个致命缺点是列存储索引表会进入只读状态，用户无法更新操作。SQL Server 2014引入了可更新聚集列存储索引技术来解决列存储索引表只读的问题，使得列存储索引表使用的范围和场景大大增加。

名词解释

SQL Server 2014使用聚集列存储索引来解决列存储索引表只读问题的同时，引入了几个全新的名称。

Clustered Column Store Index

聚集列存储索引是一个可更新的整张表数据物理存储结构，并且每张表只允许创建一个聚集列存储索引，不能与其他的索引并存。我们可以对聚集列存储索引进行Insert、Delete、Update、Merge和Bulk Load操作。这也是SQL Server 2014 Clustered Columnstore Index正真强大的地方。下面这张来自微软官方的图描述了聚集列存储索引的物理存储结构：

Delta Store

由于column store是基于column segment压缩而存放的结构，为了减少DML操作导致的列存储索引碎片和提高更新性能，系统在做数据更新操作（Insert和Update）的时候，不会直接去操作已经压缩存储的column store（这样系统开销成本太高），而是引入一个临时存储的中间结构Delta Store。Delta Store结构和传统的基于B-Tree结构的row store没有任何差异，它存在的意义在于提高系统的DML操作效率、提升Bulk Loading的性能和减少Clustered Column Store Index碎片。

Delete Bitmap

对于更新操作中的删除动作，会比较特殊，聚集列存储索引采用的是标记删除的方式，而没有立即物理删除column store中的数据。为了达到标记删除的目的，SQL Server 2014引入了另一个B-Tree结构，它叫着Delete Bitmap，Delete Bitmap中记录了被标记删除的Row Group对应的RowId，在后续查询过程中，系统会过滤掉已经被标记删除的Row Group。

Tuple Mover

当不断有数据插入Clustered Column Store Index表的时候，Delta Store中存储的数据会越来越多，当大量数据存储在Delta Store中以后，势必会导致用户的查询语句性能降低（因为Delta Store是B-Tree结构，相对列存储结构性能降低）。为了解决这个问题，SQL Server 2014引入了一个全新的后台进程，叫着Tuple Mover。Tuple Mover后台进程会周期性的检查Closed Delta Store并且将其压缩转化为相应的Column Store Segment。Tuple Mover进程每次读取一个Closed Delta Store，在此过程中，并不会阻塞其他进程对Delta Store的读取操作（但会阻塞并发删除和更新操作）。当Tuple Mover完成压缩处理和转化以后，会创建一个用户可见的新的Column Store Segment，并使Delta Store结构中相应的数据不可见（正真的删除操作会等待所有读取该Delta Store进程退出后），在这以后的用户读取行为会直接从压缩后的Column Store中读取。
来一张Tuple Mover的手绘图（画外音：手绘图是一种情怀，就像劳斯莱斯是纯手工打造一样）：

数据操作

基于Delta Store和Delete Bitmap的特殊设计，SQL Server 2014聚集列存储索引看起来是做到了可更新操作，实际上聚集列存储索引本身是不可变的。它是在借助了两个可变结构以后，达到了可更新的目的。这三部分结构示意图如下所示：

接下来，我们看看SQL Server 2014聚集列存储索引表DML操作原理。其中DML操作包括：INSERT、DELETE、UPDATE、MERGE和BULK操作，其中以BULK批量数据操作最为复杂，也是这一节需要详细讲解的地方。

INSERT

当我们执行INSERT操作的时候，INSERT的这一条新的记录不是立即进入Column Store中，而是进入Delta Store B-Tree结构中，Delta Store结构中存储的数据会在重组（Reorganize）聚集列存储索引的时候进入Column Store。INSERT完成后的数据读取操作，SQL Server会从Column Store和Delta store两个结构中读取，然后返回给用户。

DELETE

当我们执行DELETE操作的时候，数据库系统并不会直接从Column Store中直接删除数据，而是往Delete Bitmap结构中插入一条带有rowid的记录，系统会在聚集列存储索引重建（Rebuild）的时候最终删除Column Store中的数据。DELETE操作完成后的数据读取操作，SQL Server从Column Store中读取数据，然后通过rowid过滤掉在Delete Bitmaps中已经标记删除的数据，最后返回给用户。

UPDATE

当我们执行UPDATE操作的时候，数据库系统将这个操作分解成DELETE和INSERT操作，即先标记删除老的这条记录然后插入新的记录。SQL Server系统会在Delete Bitmaps中先插入一条带有rowid的记录，然后在Delta Store中插入一条新的记录数据。

MERGE

当我们执行MERGE操作的时候，数据库系统会将这个操作分解为相应的INSERT、DELETE或者是UPDATE操作。

BULK LOADING

在Bulk LOADING大批次数据导入介绍之前，我们必须要重点介绍几个重要的数字：

102400：数据是否直接进入Column Store的Row Group 行数的最小临界值。

1048576：一个Row Group可以容纳的最大行数。

BatchSize：Bulk Insert操作的参数，表示每批次处理的记录数。

Rows：需要批量导入聚集列存储索引表的记录总数，Rows应该总是大于等于0的整数。

在聚集列存储索引表场景中，微软SQL Server 2014推荐使用Bulk Insert进行大批次数据的更新，效率更高，维护成本也更低。聚集列存储索引针对Bulk Insert处理的逻辑是：如果Bulk Insert操作的总记录条数（Rows）小于102400条，那么所有数据会被加载到Delta Store结构中；如果Rows大于等于102400，会参照Bulk Insert的BatchSize进一步细分：BatchSize小于102400时，所有数据全部进入Delta Store；BatchSize大于等于102400时，大部分数据进入Column Store，剩下小部分数据进入Delta Store。比如：rows总共有102400 * 2 + 3 = 204803条，BatchSize为102399时，所有数据会进入Delta Store；BatchSize为102400时，会有两个Row Group的数据共204800进入Column Store，剩下3条数据进入Delta Store。
参见微软官方的流程图：

这个流程图把大致的Bulk Insert数据流向说清楚了，但是它没有把几个具体的数字和相关的逻辑表达的很清楚。现在，我们需要把详细逻辑理解清楚，以此来指导我们进行Bulk Insert来提高大批次数据导入的效率。为了表达清楚Bulk Insert的详细逻辑，参见下面的伪代码，每一个BEGIN END之间是一个语句块:

IF Rows < 102400
BEGIN
        All Rows Insert into Delta Store
END
ELSE IF Rows >= 102400 & Rows < 1048576
BEGIN
    IF BatchSize < 102400
    BEGIN
        All Rows Insert into Delta Store Batchly
    END
    ELSE IF BatchSize >= 102400
    BEGIN
        SomeData Insert into Column Store Batchly
        SomeData Remaining Insert into Delete Store
    END
END
ELSE IF Rows >= 1048576
BEGIN
    IF BatchSize < 102400
    BEGIN
        All Rows Insert into Delta Store Batchly
    END
    ELSE IF BatchSize >= 102400 & BatchSize < 1048576
    BEGIN
        SomeData Insert into Column Store Batchly
        SomeData Remaining Insert into Delete Store
    END
    ELSE IF BatchSize >= 1048576
    BEGIN
        SomeData Insert into Column Store Batchly
        IF SomeData Remaining >= 102400
        BEGIN
            Some of the SomeData Remaining will be Inserted into Column Store Batchly (batchsize = 102400)
            The left of the SomeData Remaining will be Inserted into Delta Store
        END
        ELSE IF SomeData Remaining < 102400
        BEGIN
            Some of the SomeData Remaining will be Inserted into Delta Store
        END
    END
END

伪代码写出来的逻辑还是很丑陋，也比较复杂，难于理解。再次手绘工作流程图：

理论解释了，伪代码写好了，流程图也手绘了，接下来测试三种场景的Bulk LOADING。

创建测试环境

准备测试数据库、临时存储过程、需要用到的三个数据文件。

-- Create testing database
IF DB_ID('ColumnStoreDB') IS NULL
    CREATE DATABASE ColumnStoreDB;
GO

USE ColumnStoreDB
GO
-- create temp procedure
IF OBJECT_ID('tempdb..#UP_ReCreateTestTable', 'P') IS NOT NULL
BEGIN
    DROP PROC #UP_ReCreateTestTable
END
GO

CREATE PROC #UP_ReCreateTestTable
AS
BEGIN
    SET NOCOUNT ON
    -- create demo table SalesOrder
    IF OBJECT_ID('dbo.SalesOrder', 'U') IS NOT NULL
    BEGIN
        EXEC('DROP TABLE dbo.SalesOrder')
    END
    CREATE TABLE dbo.SalesOrder
    (
        OrderID INT NOT NULL
        ,AutoID INT NOT NULL
        ,UserID INT NOT NULL
        ,OrderQty INT NOT NULL
        ,Price DECIMAL(8,2) NOT NULL
        ,UnitPrice DECIMAL(19,2) NOT NULL
        ,OrderDate DATETIME NOT NULL
    );

    CREATE CLUSTERED COLUMNSTORE INDEX CCI_SalesOrder 
    ON dbo.SalesOrder;
END
GO

三个数据文件，分别存在102399、204803和2199555条记录，使用BCP从上一期测试环境SQL Server 2012数据库导出，BCP导出脚本如下：

BCP  "SELECT TOP 102399 * FROM ColumnStoreDB.dbo.SalesOrder" QueryOut "Scenario1.102399Rows" /c /T /S CHERISH-PC\SQL2012
BCP  "SELECT TOP 204803 * FROM ColumnStoreDB.dbo.SalesOrder" QueryOut "Scenario2.204803Rows" /c /T /S CHERISH-PC\SQL2012
BCP  "SELECT TOP 2199555 * FROM ColumnStoreDB.dbo.SalesOrder" QueryOut "Scenario3.2199555Rows" /c /T /S CHERISH-PC\SQL2012

测试需要的环境至此准备完毕。

Rows小于102400

这个场景需要总共需要导入的数据量为102399条，小于102400条记录数，所以数据无法直接进入Column Store结构Row Group中。

-- Scenario 1 : BULK LOADING ROWS:  102399 = 102400 - 1

EXEC #UP_ReCreateTestTable

BEGIN TRAN Scenario1

BULK INSERT dbo.SalesOrder
FROM 'C:\Temp\Scenario1.102399Rows'
WITH (
    BATCHSIZE = 102400
    ,KEEPIDENTITY
);

SELECT * 
FROM sys.column_store_row_groups 
WHERE object_id = object_id('dbo.SalesOrder','U')
ORDER BY row_group_id DESC;

SELECT 
    database_name = DB_NAME(resource_database_id)
    ,resource_type
    ,resource_description
    ,request_mode
    ,request_type
    ,request_status
    --,* 
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

ROLLBACK TRAN Scenario1
-- END Scenario 1

执行结果如下：

从展示的结果来看，Rows为102399条数据数小于Row Group进入Column Store的最小值102400。所以数据直接进入了Delta Store结构，并且数据在Bulk Insert的时候，会对表对应的Row Group加上X锁。

Rows大于等于102400小于1048576

这个场景需要总共导入204803条记录。

-- Scenario 2 : BULK LOADING ROWS: 204803 = 102400 * 2 + 3

EXEC #UP_ReCreateTestTable

BEGIN TRAN Scenario2

BULK INSERT dbo.SalesOrder
FROM 'C:\Temp\Scenario2.204803Rows'
WITH (
    BATCHSIZE = 102400    -- 102400 / 102401
    ,KEEPIDENTITY
);

SELECT * 
FROM sys.column_store_row_groups 
WHERE object_id = object_id('dbo.SalesOrder','U')
ORDER BY row_group_id DESC;

SELECT 
    database_name = DB_NAME(resource_database_id)
    ,resource_type
    ,resource_description
    ,request_mode
    ,request_type
    ,request_status
    --,* 
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

ROLLBACK TRAN Scenario2
-- END Scenario 2

执行结果展示如下：

总的记录数Rows为204803 = 102400 * 2 + 3 超过102400并且Batch Size是大于等于102400的，所以，最后数据会插入到Column Store的2个Row Groups，剩下的3条数据进入Delta Store存储结构，在数据导入过程中，对三个Row Group加了X锁。

Rows大于等于1048576

这种情况相对来说是最为复杂的，我们以Bulk Insert 2199555 （等于1048576 * 2 + 102400 + 3）条记录，BatchSize分别102399和1048577为例。

-- Scenario 3 : BULK LOADING ROWS: 2199555 = 1048576 * 2 + 102400 + 3

EXEC #UP_ReCreateTestTable

BEGIN TRAN Scenario3

BULK INSERT dbo.SalesOrder
FROM 'C:\Temp\Scenario3.2199555Rows'
WITH (
    BATCHSIZE = 102399 -- 102399  / 1048577
    ,KEEPIDENTITY
);

SELECT * 
FROM sys.column_store_row_groups 
WHERE object_id = object_id('dbo.SalesOrder','U')
ORDER BY row_group_id DESC;

SELECT 
    database_name = DB_NAME(resource_database_id)
    ,resource_type
    ,resource_description
    ,request_mode
    ,request_type
    ,request_status
    --,* 
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

ROLLBACK TRAN Scenario3
-- END Scenario 3

当BatchSize为102399时，执行结果展示如下：

从这个结果来看，当BatchSize小于102400时，所有的数据Bulk插入操作都是进入Delta Store结构（后台进程Tuple Mover会将Delta Store结构中数据迁移到Column Store结构）。由于数据不是直接进入Column Store结构，而是全部数据聚集在Delta Store结构中（Delta Store是传统的B-Tree）。根据之前的介绍，这个时候的用户查询操作，系统会取Column Store和Delta Store两者中的数据，势必会给Delta Store带来巨大读取压力。因为，这部分新插入的200多万条数据无法使用列存储的优势，还是必须走传统的B-Tree结构查询。
如果调大BatchSize的值为比Row Group中可以存放的最大记录数还大。BatchSize为1048577的执行结果展示如下：

从这个结果分析可以得出结论，当BatchSize修改为1048577后，Bulk Insert操作的数据会直接进入Column Store，形成三个Row Group，而不是暂存在Delta Store结构中，仅剩下2条数据存放在Delta Store中。让我们来看看这个结果到底是怎么形成的，首先分解下总的记录数2199555，拆开来可以表示为：1048576 * 2 + 102400 + 3，换句话说，当BatchSize设置为1048577时，我们每个Row Group中可以直接存放1048576条记录，剩下的102403条记录也满足Row Group存放的最小记录数，SQL Server取了102401条放入Column Store，最后余下2条不满足Row Group存放的最小记录数，所以存放到了Delta Store结构中。这样做可以最大限度的发挥Column Store的优点，而避免Delta Store B-Tree查询的缺点，从而最大限度的提升查询性能。

Bulk Insert总结

Bulk Insert操作总结，如果总的记录数Rows小于102400，所有的数据记录直接进入Delta Store；如果总的记录数大于等于102400，但小于1048576，并且Batch Size也大于等于102400，绝大多数数据会进入Column Store，少量会进入Delta Store；如果总的记录数大于等于1048576，并且Batch Size也大于等于102400，绝大多数数据会进入Column Store，少量会进入Delta Store，最优的Batch Size值是1048576，使得压缩的Column Store中每个Row Group可以存放最多的数据记录数。

限制条件

相对于SQL Server 2012列存储索引表的限制而言，详情参见SQL Server · 特性分析 · 2012列存储索引技术，SQL Server 2014聚集列存储索引的限制有了很大改进的同时，也加入了新的限制。

限制改善

SQL Server 2014列存储索引相对于SQL Server 2012，有了不少的改善，比如：

SQL Server 2014既支持Nonclustered Columnstore Index也指出Clustered Columnstore Index，并且Clustered Columnstore Index表支持DML可更新操作。使得列存储索引使用的范围和场景大大增加。

开始支持二进制类型：binary(n)、varbinary(n)，但不包varbinary(max)。

支持精度大于18位的decimal和numeric数据类型。

支持Uniqueidentifier数据类型

Change tracking：Clustered Columnstore Index表支持；而Nonclustered Columnstore Index表不支持，因为表之只读的。

Change data capture：Clustered Columnstore Index表支持；而Nonclustered Columnstore Index表不支持，因为表是只读的。

新增限制

SQL Server 2014同样也新增了不少的限制，比如：

整个表仅允许建立一个聚集列存储索引，不允许再有其他的索引。

聚集列存储索引表不支持Linked Server，链接服务器不可访问聚集列存储索引表；非聚集列存储索引表是支持链接服务器的。

聚集列存储索引没有对数据做任何排序。按照常理，“聚集”类型意味着数据排序，但聚集列存储索引表是没有按照任何列物理排序的，这个需要特别注意。

聚集列存储索引表不支持游标和触发器；非聚集列存储索引表是支持游标和触发器的。

Readable secondary：在SQL Server 2014 AlwaysOn 场景中，Clustered Columnstore Index不支持secondary只读角色；Nonclustered Columnstore Index支持secondary只读角色

引用文章

Enhancements to SQL Server Column Stores

Columnstore Indexes Described.aspx)

The Illusion of Updateable Clustered ColumnStore Indexes

SQL Server 2014聚集列存储索引

author: 风移

摘要

名词解释

Clustered Column Store Index

Delta Store

Delete Bitmap

Tuple Mover

数据操作

INSERT

DELETE

UPDATE

MERGE

BULK LOADING

创建测试环境

Rows小于102400

Rows大于等于102400小于1048576

Rows大于等于1048576

Bulk Insert总结

限制条件

限制改善

新增限制

引用文章

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

SQL Server 2014聚集列存储索引

author: 风移

摘要

名词解释

Clustered Column Store Index

Delta Store

Delete Bitmap

Tuple Mover

数据操作

INSERT

DELETE

UPDATE

MERGE

BULK LOADING

创建测试环境

Rows小于102400

Rows大于等于102400小于1048576

Rows大于等于1048576

Bulk Insert总结

限制条件

限制改善

新增限制

引用文章

热门文章

最新文章

相关课程

相关电子书

相关实验场景