簡介

　　之前已經(jīng)寫過兩篇介紹列存儲(chǔ)索引的文章，但是只有非聚集列存儲(chǔ)索引，今天再來簡單介紹一下聚集的列存儲(chǔ)索引，也就是可更新列存儲(chǔ)索引。在SQL Server 2012中首次引入了基于列存儲(chǔ)數(shù)據(jù)格式的存儲(chǔ)方式。叫做“列存儲(chǔ)索引”。前一篇我已經(jīng)比較了行存儲(chǔ)索引與非聚集的列存儲(chǔ)索引（http://www.cnblogs.com/wenBlog/p/5682024.html）。其中對(duì)于在小表的指定值或者小范圍的查詢來講，尤其針對(duì)事務(wù)性的負(fù)載行存儲(chǔ)是很合適的。但是對(duì)于分析性負(fù)載像數(shù)據(jù)倉庫和BI，在查詢中將會(huì)對(duì)大量數(shù)據(jù)進(jìn)行全掃描，例如事實(shí)表，這時(shí)候列存儲(chǔ)索引就是更好地選擇。

列存儲(chǔ)索引結(jié)構(gòu)

　　在列存儲(chǔ)索引中，數(shù)據(jù)按照獨(dú)立列組織到一起形成索引結(jié)構(gòu)。每列都數(shù)據(jù)都位于被高度壓縮的數(shù)據(jù)集中，叫做數(shù)據(jù)段。這個(gè)數(shù)據(jù)段只包含該列的值，對(duì)于大型表它分到多個(gè)數(shù)據(jù)段中，每個(gè)數(shù)據(jù)段中只含有100萬行數(shù)據(jù)，這就叫做行組、數(shù)據(jù)段由一個(gè)或者多個(gè)數(shù)據(jù)頁組成。數(shù)據(jù)將在內(nèi)存和硬盤上以數(shù)據(jù)段的形式傳輸。

　　這種索引提高了數(shù)據(jù)倉庫的查詢效率。這種通過壓縮獲得數(shù)據(jù)格式要比B-Tree結(jié)構(gòu)的壓縮率高7倍多。同時(shí)由于列存儲(chǔ)索引使用了批處理模式執(zhí)行，數(shù)據(jù)處理也是批處理的，較少了CPU的使用。列存儲(chǔ)索引強(qiáng)化了檢索數(shù)據(jù)的速度，與行存儲(chǔ)不同的是不用查詢所有列。因?yàn)檫@個(gè)原因，更少數(shù)據(jù)被讀取到內(nèi)存中，再到處理器緩存處理。相關(guān)的這些因素都會(huì)減少硬盤IO，提高整體查詢的性能。

　　在2014中列存儲(chǔ)索引有以下限制：

　　　　　　　　　　　　　　　　　　最多支持1024列在你的索引中；

　　　　　　　　　　　　　　　　　　列存儲(chǔ)索引不能被定義為唯一性索引；

　　　　　　　　　　　　　　　　　　不能創(chuàng)建視圖；

　　　　　　　　　　　　　　　　　　不能包含稀疏列；

　　　　　　　　　　　　　　　　　　不能使用ALTER INDEX來修改索引，只能drop然后重新創(chuàng)建；

　　　　　　　　　　　　　　　　　　不能使用INCLUDE關(guān)鍵字。

　　　　　　　　　　　　　　　　　　不能排序列；

　　　　　　　　　　　　　　　　　　不能使用FILESTREAM屬性。

　　　　　　　　　　　　　　　　　　當(dāng)然還有一些數(shù)據(jù)類型不能包含在列存儲(chǔ)索引中（binary , varbinary , ntext , text, , image, varchar(max) , nvarchar(max), uniqueidentifier, rowversion , sql_variant,精度大于18 的decimal，CLR 和xml等）　　　

 

另一方面，對(duì)于索引列900字節(jié)的限制也不適用與列存儲(chǔ)索引。

在SQL Server2012 中，只能創(chuàng)建非聚集列存儲(chǔ)索引，并且不能更新。為了更新你必須刪除索引，然后進(jìn)行插入、更新或者刪除的操作后在重建索引。

在2014中列存儲(chǔ)索引得到了不小的提升，比如消除了只讀限制。增加了聚集列存儲(chǔ)索引，列存儲(chǔ)索引作為了表的存儲(chǔ)方式，存儲(chǔ)表的數(shù)據(jù)。

比較聚集和非聚集列存儲(chǔ)索引

區(qū)別

聚集列存儲(chǔ)索引

非聚集列存儲(chǔ)索引

索引列需要指定列上創(chuàng)建所有列都包含在內(nèi)
 存儲(chǔ) 額外增加百分之10的空間作為索引 壓縮十倍的數(shù)據(jù)量，如果表之前是頁壓縮，則可以壓縮5倍左右
 更新 是 否
 排序 在創(chuàng)建之前進(jìn)行排序 否

 

 

列存儲(chǔ)索引的結(jié)構(gòu)圖：

萬碼學(xué)堂,電腦培訓(xùn),計(jì)算機(jī)培訓(xùn),Java培訓(xùn),JavaEE開發(fā)培訓(xùn),青島軟件培訓(xùn),軟件工程師培訓(xùn)

如圖增量存儲(chǔ)部分我們叫做deltastore，用于存儲(chǔ)不夠最小行組大小的數(shù)據(jù)。流程就是將行數(shù)據(jù)提取成列數(shù)據(jù)，然后進(jìn)行壓縮存儲(chǔ)，多余的部分放到deltastore中。

聚集索引插入、刪除和更新實(shí)現(xiàn)邏輯：

插入新行的時(shí)候，值被存儲(chǔ)在deltastore中，直到達(dá)到最小rowgroup（行組）大小時(shí)，然后壓縮并移動(dòng)到列存儲(chǔ)數(shù)據(jù)段中。

刪除數(shù)據(jù)時(shí)，行將被刪除從deltastore存儲(chǔ)中，但是在列存儲(chǔ)索引數(shù)據(jù)段中只是被標(biāo)記為刪除，除非重建后才會(huì)被真的刪除。

更新的時(shí)候，在deltastore存儲(chǔ)中行數(shù)據(jù)被刪除，然后在列存儲(chǔ)數(shù)據(jù)段中被標(biāo)記為刪除，新的列別插入到deltastore中。

最后當(dāng)重建索引的時(shí)。SQLServer將會(huì)刪除所有標(biāo)記為刪除的數(shù)據(jù)段，數(shù)據(jù)存儲(chǔ)在deltastore中的將與數(shù)據(jù)段中的數(shù)據(jù)合并，然后進(jìn)行壓縮。

 

 

下面我們來展示下如何從列存儲(chǔ)索引中獲得性能：

 

我們首先創(chuàng)建一個(gè)事實(shí)表在數(shù)據(jù)庫中腳本如下:

萬碼學(xué)堂,電腦培訓(xùn),計(jì)算機(jī)培訓(xùn),Java培訓(xùn),JavaEE開發(fā)培訓(xùn),青島軟件培訓(xùn),軟件工程師培訓(xùn)

 1 USE SQLShackDemo 2  3 GO 4 --創(chuàng)建表 5 CREATE TABLE [dbo].[FactFinance]( 6  7 [FinanceKey] [int] NOT NULL, 8  9 [DateKey] [int] NOT NULL,10 11 [OrganizationKey] [int] NOT NULL,12 13 [DepartmentGroupKey] [int] NOT NULL,14 15 [ScenarioKey] [int] NOT NULL,16 17 [AccountKey] [int] NOT NULL,18 19 [Amount] [float] NOT NULL,20 21 [Date] [datetime] NULL22 23 ) ON [PRIMARY]24 25 GO26 27 --創(chuàng)建聚集索引:28 29 CREATE CLUSTERED INDEX [IX_FactFinance_FinanceKey_DateKey] ON [dbo].[FactFinance] ( [FinanceKey],[DateKey])30  GO31 32 33 --查詢表:34 35 SELECT [FinanceKey]36 37 ,[DateKey]38 39 ,[OrganizationKey]40 41 ,[DepartmentGroupKey]42 43 FROM [FactFinance]

萬碼學(xué)堂,電腦培訓(xùn),計(jì)算機(jī)培訓(xùn),Java培訓(xùn),JavaEE開發(fā)培訓(xùn),青島軟件培訓(xùn),軟件工程師培訓(xùn)

 

萬碼學(xué)堂,電腦培訓(xùn),計(jì)算機(jī)培訓(xùn),Java培訓(xùn),JavaEE開發(fā)培訓(xùn),青島軟件培訓(xùn),軟件工程師培訓(xùn)

 

讓我們檢查下聚集索引掃描操作符，Estimated I/O Cost（估計(jì)IO花銷） 的值為0.183866，Estimated CPU Cost(估計(jì)CPU花銷)為0.0435069,為了比較列索引的值，我們先記住:

 

現(xiàn)在我們創(chuàng)建列存儲(chǔ)索引在非聚集索引:

 

 

萬碼學(xué)堂,電腦培訓(xùn),計(jì)算機(jī)培訓(xùn),Java培訓(xùn),JavaEE開發(fā)培訓(xùn),青島軟件培訓(xùn),軟件工程師培訓(xùn)

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_FactFinance_FinanceKey_DateKey_OrganizationKey_DepartmentGroupKey]ON [FactFinance]([FinanceKey],[DateKey],[OrganizationKey],[DepartmentGroupKey])GOSELECT [FinanceKey] ,[DateKey] ,[OrganizationKey] ,[DepartmentGroupKey] FROM [FactFinance]

萬碼學(xué)堂,電腦培訓(xùn),計(jì)算機(jī)培訓(xùn),Java培訓(xùn),JavaEE開發(fā)培訓(xùn),青島軟件培訓(xùn),軟件工程師培訓(xùn)

 

 

 

這個(gè)列存儲(chǔ)索引掃描操作符如下所示:

 

如上所示，Estimated I/O Cost從0.183866下降到0.0112731,這是因?yàn)镾QL引擎只檢索需要的列，節(jié)省了IO和內(nèi)存資源。Estimated CPU的時(shí)間沒有變化。

 

IO強(qiáng)化與之前相比是明顯的，我們也可以比較兩個(gè)查詢，啟用I/O statistics，檢查IO的hits 表現(xiàn)如下:

 

萬碼學(xué)堂,電腦培訓(xùn),計(jì)算機(jī)培訓(xùn),Java培訓(xùn),JavaEE開發(fā)培訓(xùn),青島軟件培訓(xùn),軟件工程師培訓(xùn)

SET STATISTICS IO ON GO
 SELECT [FinanceKey] ,[DateKey] ,[OrganizationKey] ,[DepartmentGroupKey] FROM [FactFinance] with (index (IX_FactFinance_FinanceKey_DateKey)) 
GO SELECT [FinanceKey] ,[DateKey] ,[OrganizationKey] ,[DepartmentGroupKey] FROM [FactFinance] with (index(IX_FactFinance_FinanceKey_DateKey_OrganizationKey_DepartmentGroupKey))

萬碼學(xué)堂,電腦培訓(xùn),計(jì)算機(jī)培訓(xùn),Java培訓(xùn),JavaEE開發(fā)培訓(xùn),青島軟件培訓(xùn),軟件工程師培訓(xùn)

 

正如所示，比較執(zhí)行計(jì)劃，使用列存儲(chǔ)索引的要比行索引的好四倍，那么期望一下處理大數(shù)據(jù)時(shí)的10倍性能：

 

當(dāng)比較邏輯讀時(shí)你也能發(fā)現(xiàn)相似的結(jié)果。明顯這個(gè)邏輯讀也是四倍+關(guān)系。

那么我們可以根據(jù)下圖概括一下傳統(tǒng)的行索引與列存儲(chǔ)所以的一般性區(qū)別：

萬碼學(xué)堂,電腦培訓(xùn),計(jì)算機(jī)培訓(xùn),Java培訓(xùn),JavaEE開發(fā)培訓(xùn),青島軟件培訓(xùn),軟件工程師培訓(xùn)

列存儲(chǔ)索引的創(chuàng)建

也能夠使用SSMS創(chuàng)建索引： Indexes -> New Index ->Non-Clustered Columnstore Index 如下:

 

與非聚集索引創(chuàng)建類似，選擇列，然后這些列沒有排序也不能使用Include選項(xiàng):

 

下圖中我在SQL Server2014 企業(yè)版中，創(chuàng)建聚集索引:

 

需要注意的是如果在表上已經(jīng)有其他索引，嘗試創(chuàng)建聚集列存儲(chǔ)索引就會(huì)出現(xiàn)錯(cuò)誤，正如我們之前說的，同一個(gè)表中不能或者其他索引:

不用選擇列，所有數(shù)據(jù)都包含在內(nèi)了:

幾個(gè)好的應(yīng)用場景：

如果你有大型的事實(shí)表并且存在查詢問題的，或者SSAS存在其他性能問題的，列存儲(chǔ)是一個(gè)不錯(cuò)的方案。一下兩種情況是經(jīng)過測(cè)試的比較好的應(yīng)用場景:

  • 對(duì)于高頻率響應(yīng)的報(bào)表/儀表板,尤其分析當(dāng)性能表現(xiàn)不佳的時(shí)候，會(huì)有很不錯(cuò)的性能。

  • 對(duì)于ETL的過程來講，源數(shù)據(jù)的列存儲(chǔ)索引將會(huì)極大提高性能，如果數(shù)據(jù)足夠大甚至可以考慮臨時(shí)創(chuàng)建列存儲(chǔ)索引。然后執(zhí)行ETL。

 

總結(jié):

列存儲(chǔ)索引是一個(gè)使用SQL Server性能優(yōu)化的方案，通過減少IO消耗，尤其對(duì)數(shù)據(jù)倉庫和BI查詢都是由明顯性能提升。它通過排序數(shù)據(jù)作為列存儲(chǔ)，然后壓縮，并使用批處理來處理數(shù)據(jù)。當(dāng)然，必須要確保使用列存儲(chǔ)索引的使用帶來了好處，而不會(huì)引起其他性能問題才能使用。比如需要注意使用的硬件環(huán)境和數(shù)據(jù)，如果沒有join、過濾、或者聚合導(dǎo)出巨大的數(shù)據(jù)量沒有足夠的內(nèi)存則將被暫時(shí)放入硬盤進(jìn)行switch off,從而引起查詢性能下降。盡量在使用之前在測(cè)試環(huán)境中測(cè)試是否適合使用，同時(shí)還要關(guān)注其他環(huán)節(jié)是否受影響。

補(bǔ)充，在2016中增加的幾個(gè)我認(rèn)為不錯(cuò)新的feature：

基于聚集列存儲(chǔ)索引的 B 樹索引；

基于內(nèi)存優(yōu)化表的列存儲(chǔ)索引；

CREATE TABLE 和 ALTER TABLE 中的列存儲(chǔ)索引的壓縮延遲選項(xiàng)；

單線程查詢的批處理執(zhí)行。