RiCo技術農場

支援Unique Constraints、Primary Key和Foreign Keys可以產生較佳執行計畫，

同時也可保證資料正確性，這改善真的太棒了，下面簡單測試一下。

create table Parent(
	c1 int identity(1,1),
	c2 varchar(20),
	constraint UN_Parent_C1 unique nonclustered (c1),
	index CCI_Parent clustered columnstore )
	insert into Parent (c2)
	        values ('Rico'), ('Sherry'), ('Fei')
 
create table Child(
	serial int,
	c1_Parent int not null,
	constraint FK_Child_Parent_c1 
		foreign key (c1_Parent) 
			references dbo.Parent(c1) )
 
insert into Child (serial, c1_Parent)
	values (1, 1 )

select * from Parent t1 join Child t2 on t2.c1_Parent=t1.c1

--測試支援foreign key
create clustered columnstore index CCI_Child
	on Child

--測試約束
alter table dbo.Parent with check add constraint CK1_c2 check(c2>'')

--測試主鍵
alter table dbo.Parent add constraint PK_Parent primary key nonclustered(c1)

資料列群組是指同時壓縮成資料行存放區格式的一組資料列。

資料列群組的每一資料列群組的資料列數目上限為 1,048,576 個資料列。

 

次要非叢集索引輔助

以前第一個版本只能建立Nonclustered Columnstore Index，且資料表會變成唯獨，

但OLTP環境大多還是以搜尋為主(row store)，所以以前比較適合唯獨資料庫或OLAP環境。

從SQL Server 2016開始，你可以在OLTP環境使用Clustered Columnstore index的高壓縮來提高彙總分析效能，

也可以建立次要非叢集索引來降低相等(point)和範圍(range)查閱(Lookup)對系統效能影響，

Clustered Columnstore Index和B-tree資料位置主要使用2個技術來幫忙。

Row Locator:B-tree內部指標，主要用來定位Row Group ID(from Clustered Columnstore Index)。

Mapping Index:管理Clustered Columnstore Index和Nonclustered B-tree Indexes之間對應關係，

主要用來追蹤Clustered Columnstore index的資料列位置，

由於所有資料列位置在Columnstore index都不是固定的，

當我們新增資料至 Columnstore index資料所在位置可能有以下幾種情況。

Delta-Store:原本資料列位置被對應到Delta-Store ID，並加上一個唯一識別碼。

Row_group:使用row_group ID加上原本資料列所在位置。

當一個資料列從Clustered Columnstore Index移到另一row_group，

並不需要更新整個B-tree非叢集索引，只須更新Mapping Index(一個很小的B-tree結構)即可，

這樣可以避免巨大效能和位置錯亂問題，但相對也會降低一些相等(point)和範圍(range)查閱(Lookup)效能，

可是對於Columnstore index既有優勢卻不會有任何影響，這裡我會透過新DMV sys.internal_partitions 進行一些簡單驗證。

create table TestCCIIndexes(
	c1 int,
	c2 int,
	c3 varchar(10),
	c4 smallint,
	Index PK_TestCCIIndexes Clustered Columnstore ); 

	create nonclustered index idx1
	on TestCCIIndexes (c1,c2);
 
create nonclustered index idx2
	on TestCCIIndexes (c2,c4) include(c3);

	insert into dbo.TestCCIIndexes
	(c1,c2,c3,c4)
	values
	(1,15,'rico',8),
	(2,19,'sherry',256),
	(3,17,'Fei',128),
	(4,12,'Joe',32);

	--查詢資料行存放區索引的內部資料磁碟資料表上每個資料列集的一個資料列
	select object_name(part.object_id) as TableName, 
	part.object_id, part.partition_id,
	ind.name as IndexName, part.index_id, 
	part.hobt_id,
	part.internal_object_type, part.internal_object_type_desc,
	part.row_group_id, part.rows, part.data_compression, part.data_compression_desc
	from sys.internal_partitions part
		left outer join sys.indexes ind
			on part.object_id = ind.object_id and part.index_id = ind.index_id
	where part.object_id = object_id('dbo.TestCCIIndexes');

可以看到兩個結構，COLUMN_STORE_DELTA_STORE儲存我們的資料(4筆)，

COLUMN_STORE_DELETE_BITMAP儲存來自row_group被刪除的資料列，

下面我會執行重建clustered columnstore index並再次查看結構變化。

--重建Clustered Columnstore並改變row_group中的資料列
	alter index PK_TestCCIIndexes
	on dbo.TestCCIIndexes Rebuild;--要注意大索引重建時間

可以看到COLUMN_STORE_DELTA_STORE消失了，

因目前我們只有一個row_group，所以不需要建立Mapping Index。

 

現在我來刪除一筆資料，驗證一下COLUMN_STORE_DELETE_BITMAP的變化

delete dbo.TestCCIIndexes where c1 = 4

COLUMN_STORE_DELETE_BITMAP 的rows欄位顯示1，

但因為還是只有一個row_group，所以還是沒有建立Mapping Index。

--新增資料產生其它row_group
set nocount on
declare @i as int;
set @i = 1;
begin tran
while @i <= 1048581  --1048576為每一資料列群組的資料列數目上限
begin
	insert into dbo.TestCCIIndexes
		default values
 
	set @i = @i + 1;
end;
commit;
 
checkpoint;

alter index ALL ON TestCCIIndexes REORGANIZE ; --must

有兩個row_group時，果然出現COLUMN_STORE_MAPPING_INDEX。

 

相等(point)和範圍(range)查閱(Lookup)效能測試

create clustered columnstore Index PK_StockItemTransactions
	on StockItemTransactions

--Point Lookup
select TransactionTypeID, CustomerID
	from StockItemTransactions
	where InvoiceID = 66878;

--Range Lookup
select top 90 CustomerID
	from StockItemTransactions
	where LastEditedWhen = '2013-01-26 12:00:00.0000000'
	order by CustomerID desc;

建立兩個非叢集索引後，再次執行相同查詢

create index idx1 on StockItemTransactions(InvoiceID)
	include(TransactionTypeID, CustomerID)

	create index idx2 on StockItemTransactions(LastEditedWhen)
	include(CustomerID)

--Point Lookup

--Range Lookup

邏輯讀取比較結果

查詢類型	Clustered Columnstore Index	Nonclustered Index
Point Lookup :Logical Reads	258	3
Range Lookup: Logical Reads	197	3

 

Enjoy SQL Server 2016

 

參考

sys.internal_partitions

Columnstore Indexes Guide

Columnstore Indexes Defragmentation