[鐵人賽][Denali 新特性探險26]Columnstore Indexes
概述
Denali 新增了一個新索引類型名為資料行存放區索引(ColumnStore Index)，
主要是提高data warehouse(OLAP)查詢效能，ColumnStore Index不像以往採用紀錄為單位(each row)儲存體方法(row store)，
而是改以欄位為單位(each column)儲存體方法(Column Store)。
Column store儲存體方法會提高緩衝命中率(data buffer可以存放更多詞條)並減少I/O(僅讀取所需欄位、
且資料連續故可以減少發生跨page讀取)，
同時也具有較佳的平行運算能力，兩者儲存方式差異如下圖。
擷取Columnstore Indexes for Fast DW QP SQL Server 11。
注意事項
1. 一個資料表只能有一個ColumnStore。
2. 只能在資料表(B-tree or heap)上建立。
3. 無法使用過濾條件。
4. 索引必須partition-aligned。
5. 資料表將變成唯讀(only select)。
6. 索引欄位不能含有計算的資料行。
7. 建立columnstore index不能使用include關鍵字。
Note:以目前相關限制來看，比較不太適和在OLTP環境中使用。
建立ColumnStore Index
--建立資料表
create table mycolumnstore
(
c1 int identity(1,1),
c2 varchar(30),
c3 date
)
--建立clustered index
create clustered index cix_c1
on mycolumnstore(c1)
--建立nonclustered columnstore index
create nonclustered columnstore index nix_cs
on mycolumnstore(c2,c3)
--新增資料
Insert into mycolumnstore select 'rico',GETDATE()
前面我有提到，建立columnstore會將資料表變成唯讀，如要新增資料請依下面步驟執行。
資料變更步驟(注意:操作大資料表相當耗費系統資源)
1. 停用columnstore index
2. 重建columnstore index
select * from mycolumnstore
--停用columnstore index
alter index nix_cs on mycolumnstore disable
--新增資料
insert into mycolumnstore
select 'rico',GETDATE()
go 1000
--重建columnstore index
alter index nix_cs on mycolumnstore rebuild
查詢效能測試
資料表: QTPLG
資料總筆數: 5008907
建立兩種類型索引
--create nonclustered index
create nonclustered index nix_BILL_NO_TYPE
on dbo.QTPLG(BILL_NO_TYPE)
--create noncluster columnstore index
create nonclustered columnstore index nixcs_BILL_NO_TYPE
on dbo.QTPLG(BILL_NO_TYPE)
執行查詢
--using normal nonclustered index
select t1.BILL_NO_TYPE,
count(1)
from dbo.QTPLG t1 with(index(nix_BILL_NO_TYPE))
where BILL_NO_TYPE in ('H','U')
group by BILL_NO_TYPE
I/O和時間統計資訊。
執行計畫(擷取部分)。
--using columnstore index
select t1.BILL_NO_TYPE,
count(1)
from dbo.QTPLG t1 with(index(nixcs_BILL_NO_TYPE))
where BILL_NO_TYPE in ('H','U')
group by BILL_NO_TYPE
CPU執行數目:4。批次執行模式(某些操作行為會使用該模式)。
執行計畫(擷取部分)。
結果比較表
結論
如果你的查詢類型大部分是在處理彙總資料，那透過columnstore index絕對可以提高不少查詢效能。
透過結果比較表得知使用columnstore index經過時間改善了約3.3倍，CPU時間改善了約13.2倍。
這裡我也順便提供微軟的測試結果給大家參考一下。
CPU時間改善16倍、經過時間改善455倍。