前面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。
注意事項
建立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會將資料表變成唯讀,如要新增資料請依下面步驟執行。
資料變更步驟(注意:操作大資料表相當耗費系統資源)
停用columnstore index
重建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 全系列文章