数据库内核月报 - 2017 / 02

SQL Server · 特性介绍 · 聚集列存储索引

摘要

微软在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正真强大的地方。下面这张来自微软官方的图描述了聚集列存储索引的物理存储结构:

01.png

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的手绘图(画外音:手绘图是一种情怀,就像劳斯莱斯是纯手工打造一样):

02.png

数据操作

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

03.png

接下来,我们看看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操作的时候,数据库系统并不会直接从Clustered 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。
参见微软官方的流程图:

04.png

这个流程图把大致的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

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

05.png

理论解释了,伪代码写好了,流程图也手绘了,接下来测试三种场景的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 

执行结果如下:

06.png

从展示的结果来看,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

执行结果展示如下:

07.png

总的记录数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时,执行结果展示如下:

08.png

从这个结果来看,当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的执行结果展示如下:

09.png

从这个结果分析可以得出结论,当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只读角色

引用文章