Share via

SQL Server 2016 新功能搶先看 - 資料行存放區索引

  • Article

SQL Server 2016 搶先看系列第十彈 ！

本系列文已經介紹 SQL Server 2016 在安全性、混合雲應用、效能調校及開發等方面所推出的嶄新功能或改良， SQL Server 2016 搶先看系列中您可以看到：

l 安全性相關：動態資料遮罩、Always Encrypted、資料列層級安全性。

l 混合雲應用相關：受管理的備份、Stretch Database、資料庫檔案快照備份、備份至 URL 支援 Azure 儲存體區塊 Blob。

l 效能調校相關：Live Query Statistics、Query Store、多重 tempdb 資料檔。

l 開發相關：支援 JSON 格式輸出、Temporal Tables。

當然 SQL Server 2016 的新功能不只如此，隨著 CTP 版本的陸續釋出，將會有更多令人期待的功能被實作出來，請鎖定 TechNet 台灣部落格，我們會持續為各位帶來第一手的消息。

除了上述功能外，還有哪些是 SQL Server 2016 搶先看系列還沒介紹到的呢？本文將介紹用來提升查詢效能的資料行存放區索引（columnstore indexes）在這個版本幾項重要的改良。

本文章將包含以下重點：

資料行存放區索引

資料行存放區索引是SQL Server 用來處理大型資料或資料倉儲工作負載進行唯讀查詢時，提供查詢效能改善及藉由高度資料壓縮來降低儲存成本及 IO 的一項重要技術。使用時須考慮其特性及使用情境，對於使用完整資料表掃描的查詢，資料行存放區索引則相當有用，若查詢是要查找資料搜尋特定的值則不適用。

SQL Server 2012、SQL Server 2012 PDW（Parallel Data Warehouse） 和 SQL Server 2014 支援使用資料行存放區索引來加速資料倉儲的查詢，其中，SQL Server 2012 導入非叢集資料行存放區索引，和以「批次」為單位來處理資料的向量式查詢執行功能；SQL Server 2014 新增了可更新的叢集資料行存放區索引，到了 SQL Server 2016 更強化資料行存放區索引多項功能。

各版本資料行存放區索引功能比較

下表為 SQL Server 2016 資料行存放區索引的功能列表，以及與其他版本的比較。

SQL Server 版本

資料行存放區

索引功能

SQL Server 2012

SQL Server 2014

SQL Server 2016 CTP2

SQL Database V12 Premium 版本

多執行序查詢使用批次模式執行（Batch execution for multi-threaded queries）

是

是

是

是

單一執行序查詢使用批次模式（Batch execution for single-threaded queries）

-

-

是

是

封存壓縮選項（Archival compression option）

-

是

是

是

快照和讀取認可快照隔離（Snapshot isolation and read-committed snapshot isolation）

-

-

是

是

建立資料表時指定資料行存放區索引（Specify columnstore index when creating a table）

-

-

是

是

AlwaysOn 支援資料行存放區索引（AlwaysOn supports columnstore indexes）

是

是

是

是

AlwaysOn 可讀取次要支援唯讀資料行存放區索引（AlwaysOn readable secondary supports read-only columnstore indexes）

是

是

是

是

AlwaysOn 可讀取次要支援可更新的資料行存放區索引（AlwaysOn readable secondary supports updateable columnstore indexes）

-

-

是

-

於堆積或btree的唯讀非叢集資料行存放區索引（Read-only nonclustered columnstore index on heap or btree）

是

是

是（在唯讀檔案群組建立可讀取的非叢集資料行存放區索引）

是（在唯讀檔案群組建立可讀取的非叢集資料行存放區索引）

於堆積或btree的可更新非叢集資料行存放區索引（Updateable nonclustered columnstore index on heap or btree）

-

-

是

-

在擁有資料行存放區索引的堆積或btree允許btree索引（Additional btree indexes allowed on a heap or btree that has a columnstore index）

是

是

是

-

可更新的叢集資料行存放區索引（Updateable clustered columnstore index）

-

是

是

是

於叢集資料行存放區索引的Btree索引（Btree index on a clustered columnstore index）

-

-

是

-

於記憶體最佳化資料表的資料行存放區索引（Columnstore index on a memory-optimized table）

-

-

是

-

支援使用篩選條件來定義的非叢集資料行存放區索引（Nonclustered columnstore index definition supports using a filtered condition）

-

-

是

-

光看功能描述想必不是很清楚，因此我們就以實際的範例來介紹這些新功能該如何實作。

建立資料表時指定資料行存放區索引

SQL Server 2012/2014 不允許在建立資料表時同時建立資料行存放區索引，因此必須先建立資料表，再以 ALTER DATABASE 陳述式來建立資料行存放區索引，例如下列T-SQL指令碼：

create table CCITab1

(

 col1 int,

 col2 varchar(10)

)

go

 

create clustered columnstore index idx_ci on CCITab1

go

現在不論是叢集或非叢集資料行存放區索引，都可以在建立資料表時一併建立，例如下列 T-SQL 指令碼：

use master

go

 

if exists (select * from sys.databases where name = 'vNextDB')

        alter database vNextDB set single_user with rollback immediate

        drop database vNextDB

go

 

create database vNextDB

go

 

use vNextDB

go

 

--在堆積中建立叢集資料行存放區索引

if exists (select * from sys.tables where name = 'CCITab1')

        drop table CCITab1

go

 

create table CCITab1

(

 col1 int,

 col2 varchar(10),

 index idx_ci clustered columnstore

)

go

 

--在叢集資料表中建立叢集資料行存放區索引

if exists (select * from sys.tables where name = 'CCITab2')

        drop table CCITab2

go

 

create table CCITab2

(

 col1 int primary key,

 col2 varchar(10),

 index idx_ci clustered columnstore

)

go

 

--在堆積中建立非叢集資料行存放區索引

if exists (select * from sys.tables where name = 'NCCITab1')

        drop table NCCITab1

go

 

create table NCCITab1

(

 col1 int,

 col2 varchar(10),

 index idx_ncci nonclustered columnstore(col1)

)

go

 

--在叢集資料表中建立非叢集資料行存放區索引

if exists (select * from sys.tables where name = 'NCCITab2')

        drop table NCCITab2

go

 

create table NCCITab2

(

 col1 int primary key,

 col2 varchar(10),

 index idx_ncci nonclustered columnstore(col1)

)

go

 

--在堆積中建立非叢集索引和非叢集資料行存放區索引

if exists (select * from sys.tables where name = 'NCCITab3')

        drop table NCCITab3

go

 

create table NCCITab3

(

 col1 int,

 col2 varchar(10),

 index idx_nci nonclustered (col1),

 index idx_ncci nonclustered columnstore(col1)

)

go

 

--在叢集資料表中建立非叢集索引和非叢集資料行存放區索引

if exists (select * from sys.tables where name = 'NCCITab4')

        drop table NCCITab4

go

 

create table NCCITab4

(

 col1 int primary key,

 col2 varchar(10),

 index idx_nci nonclustered(col2),

 index idx_ncci nonclustered columnstore(col1)

)

go

執行結果如下圖所示：

可更新資料的非叢集資料行存放區索引

在 SQL Server 2012/2014 一旦在資料表中建立非叢集資料行存放區索引，當您嘗試對資料表進行 INSERT、UPDATE、DELETE 或 MERGE 等 DML 操作時，會發生類似下圖的錯誤訊息。

由錯誤訊息可看出，必須先暫時將非叢集資料行存放區索引停用，待完成資料異動相關的 DML 操作後再重建索引，例如下列 T-SQL 指令碼。

--停用非叢集資料行存放區索引

alter index idx_ncci on NCCITab1 disable

go

--新增資料

insert into NCCITab1 values (1,'abc')

go

--重建非叢集資料行存放區索引

alter index idx_ncci on NCCITab1 rebuild

go

SQL Server 2016 的非叢集資料行存放區索引現在支援 DML 操作，換言之，您可以在建立非叢集資料行存放區索引的堆積或資料表任意進行資料異動，不需要像之前那樣先停用再重建非叢集資料行存放區索引（如下圖所示）。

於叢集資料行存放區索引的 Btree 索引

在 SQL Server 2012/2014 無法於具有叢集資料行存放區索引的資料表中建立非叢集索引，如同錯誤訊息所示，必須以非叢集資料行存放區索引來取代叢集資料行存放區索引才可以建立非叢集索引。

而 SQL Server 2016 取消上述限制，增加支援在叢集資料行存放區索引的資料表中建立一個以上的非叢集索引，例如下列 T-SQL 指令碼：

--在堆積中建立叢集資料行存放區索引

if exists (select * from sys.tables where name = 'CCITab1')

        drop table CCITab1

go

 

create table CCITab1

(

 col1 int,

 col2 varchar(10),

 index idx_cci clustered columnstore

)

go

 

create nonclustered index idx_nci on CCITab1(col2)

go

 

--在叢集資料表中建立叢集資料行存放區索引

if exists (select * from sys.tables where name = 'CCITab2')

        drop table CCITab2

go

 

create table CCITab2

(

 col1 int primary key,

 col2 varchar(10),

 index idx_ci clustered columnstore

)

go

 

create nonclustered index idx_nci on CCITab2(col2)

go

 

--於叢集資料行存放區索引的資料表建立非叢集索引和內涵資料行索引

if exists (select * from sys.tables where name = 'CCITab4')

        drop table CCITab4

go

 

create table CCITab4

(

 col1 int,

 col2 varchar(10),

 col3 bit,

 col4 nvarchar(10),

 index idx_cci clustered columnstore,

 index idx_nci nonclustered(col2),

)

 

create nonclustered index idx_ncii on CCITab4(col4) include (col3)

go

執行結果如下圖所示：

記憶體最佳化資料表支援資料行存放區索引

SQL Server 2016 支援在記憶體最佳化資料表使用資料行存放區索引，使得記憶體最佳化資料表的查詢效能更為提升，但僅限叢集資料行存放區索引，非叢集資料行存放區索引則不支援。

下列 T-SQL 指令碼示範建立記憶體最佳化資料表，並於建立資料表時新增叢集資料行存放區索引。

use master

go

 

--建立資料庫

if exists(select * from sys.databases where name = 'imoltp')

        alter database imoltp set single_user with rollback immediate

        drop database imoltp

go

 

create database imoltp

go

 

--啟用In-Memory OLTP

alter database imoltp add filegroup imoltp_mod contains MEMORY_OPTIMIZED_DATA

alter database imoltp add file (name='imoltp_file', filename='c:\imoltp_mod') to filegroup imoltp_mod

go

 

-- 建立記憶體最佳化資料表

 use imoltp

 go

 

-- 持久保存（預設）

 create table dbo.ShoppingCart (

   ShoppingCartId int not null primary key nonclustered hash with (bucket_count=2000000),

   UserId int not null index ix_UserId nonclustered hash with (bucket_count=1000000),

   CreatedDate datetime2 not null,

   TotalPrice money,

   index idx_cci clustered columnstore

 )

 with (memory_optimized=on)

 go

執行結果如下圖所示：

支援使用篩選條件來定義的非叢集資料行存放區索引

篩選索引是最佳化的非叢集索引，藉由篩選述詞對資料表中的部分資料列建立索引，與針對所有資料列的索引相較之下，具備提升查詢效能和計畫的品質及降低索引維護和儲存成本等優勢。

SQL Server 2016 增加在非叢集資料行存放區索引支援篩選條件的功能，以使用篩選索引所帶來的好處，例如下列的 T-SQL 指令碼。

--在叢集資料表中建立篩選條件的非叢集資料行存放區索引

if exists (select * from sys.tables where name = 'NCCITab5')

        drop table NCCITab5

go

 

create table NCCITab5

(

 col1 int primary key,

 col2 varchar(10)

)

go

 

create nonclustered columnstore index idx_ncci on NCCITab5(col2)

where col2 is not null

執行結果如下圖所示：

使用叢集資料行存放區索引的資料表支援建立外部索引鍵

在 SQL Server 2014 若資料表已經存在叢集資料行存放區索引，在建立外部索引鍵時會發生下圖的錯誤訊息。

SQL Server 2016 已經取消這項限制，您可以在具有叢集資料行存放區索引的資料表中建立外部索引鍵，例如下列的 T-SQL 指令碼。

if exists (select * from sys.tables where name = 'tempTab')

        drop table tempTab

go

 

create table tempTab

(c1 varchar(10) primary key)

go

 

if exists (select * from sys.tables where name = 'CCITab3')

        drop table CCITab3

go

 

create table CCITab3

(

 col1 int,

 col2 varchar(10),

 index idx_cci clustered columnstore,

 constraint fk_CCITab3_tempTab foreign key (col2)

        references tempTab(c1)

 )

go

執行結果如下圖所示：

綜合比較

最後我們以幾個不同情境來比較不同索引在資料彙總的效能差異，本實驗會在 3 個含有 62,563,201 個資料列的資料表（如下圖所示）分別建立「叢集索引和非叢集索引(內含資料行)」、「叢集資料行存放區索引和非叢集索引(內含資料行)」和「叢集索引和非叢集資料行存放區索引」。

相關資料表定義及索引說明如下：

一、 TransactionDetails 資料表的結構及索引定義如下圖所示。

接著以下列 T-SQL 進行來測試使用「叢集索引和非叢集索引(內含資料行)」的效能。

select TransactionDate,count(ProductID),sum(Quantity),avg(ActualCost)

from TransactionDetails

where ProductID between 2000 and 100000

and TransactionDate between '2005-01-01' and '2010-01-01'

group by TransactionDate

執行結果如下圖所示：

二、 TransactionDetailsCI 資料表的結構及索引定義如下圖所示。

以下列 T-SQL 進行來測試使用「叢集資料行存放區索引和非叢集索引(內含資料行)」的效能。

select TransactionDate,count(ProductID),sum(Quantity),avg(ActualCost)

from TransactionDetailsCI

where ProductID between 2000 and 100000

and TransactionDate between '2005-01-01' and '2010-01-01'

group by TransactionDate

執行結果如下圖所示：

由執行計畫可看到叢集資料行存放區索引使用到批次執行模式。

三、 TransactionDetailsNCI 資料表的結構及索引定義如下圖所示。

以下列 T-SQL 進行來測試使用「叢集索引和非叢集資料行存放區索引」的效能。

select TransactionDate,count(ProductID),sum(Quantity),avg(ActualCost)

from TransactionDetailsNCI

where ProductID between 2000 and 100000

and TransactionDate between '2005-01-01' and '2010-01-01'

group by TransactionDate

執行結果如下圖所示：

由執行計畫可看到非叢集資料行存放區索引使用到批次執行模式。

上述測試結果整理如下表：

項目 使用索引

叢集索引和非叢集索引(內含資料行)

叢集資料行存放區索引和非叢集索引(內含資料行)

叢集索引和非叢集資料行存放區索引

執行模式

Row

Batch

Batch

掃描計數

5

4

8

邏輯讀取

259,870

0

0

實體讀取

3

0

0

讀取前讀取

258,546

0

0

LOB邏輯讀取

0

133,924

170,662

LOB實體讀取

0

4

7

LOB讀取前讀取

0

363,138

616,904

區段會讀取

0

63

57

略過區段

0

0

6

執行時間

1分01秒

18秒

21秒

上述測試是以測試資料來模擬，但已經可以看出資料行存放區索引在提升查詢效能上的效果，但可能會因為伺服器硬體規格、資料表結構設計和工作負載等客觀條件而有所不同，各位可以依實際環境來斟酌使用，以找出最適合的索引組合。

若您對於 SQL Server 有任何問題請點這裡發問

TechDays 2015 相關課程 - SQL Server 2016 全新功能進階介紹，下載簡報請點這裡