iT邦幫忙

DAY 26
6

MS SQL Server新特性探險:探險代碼 Denali系列 第 26

[Denali 新特性探險26]Columnstore Indexes

前面5天我介紹了 Denali 管理方面的新特性,
接下來我將介紹效能和可擴展性方面的新特性,
效能和可擴展性方面大概就屬 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

--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 


I/O和時間統計資訊。


CPU執行數目:4。批次執行模式(某些操作行為會使用該模式)。


執行計畫(擷取部分)。

結果比較表

結論
如果你的查詢類型大部分是在處理彙總資料,
那透過columnstore index絕對可以提高不少查詢效能。
透過結果比較表得知使用columnstore index經過時間改善了約3.3倍,
CPU時間改善了約13.2倍。這裡我也順便提供微軟的測試結果給大家參考一下。

CPU時間改善16倍、經過時間改善455倍。

MS SQL Server新特性探險:探險代碼 Denali 全系列文章


上一篇
[Denali 新特性探險25]Contained Databases(3)
下一篇
[Denali 新特性探險27]SSIS Improvement(1)
系列文
MS SQL Server新特性探險:探險代碼 Denali30

尚未有邦友留言

立即登入留言