概述

SQL Server 2014被號稱是微軟數據庫的一個革命性版本，其性能的提升的幅度是有史以來之最。

可更新的列存儲索引作為SQL Server 2014的一個關鍵功能之一，在提升數據庫的查詢性能方面貢獻非常突出。據微軟統計，在面向OLAP查詢統計類系統中，相比其他SQL傳統版本的數據庫，報表查詢的性能最大可提升上十倍。

下面我們從存儲的角度來了解下SQL Server 2014的可更新列存儲索引。

什么是列存儲

微軟為了提升SQL Server的查詢性能，更好的支持大數據分析，早在SQL Server 2012中就引入了列存儲的技術，

列存儲的本質是將一個張表按照不同的列拆分，然后每一列單獨存儲，這樣一來，存儲的單位由原來的每一行變成了每一列。

像下面這張表，表中10個列分布在10個頁面中，在page1中包括了表中ROW1到ROWn中列C1的數據，在page2中包括ROW1到ROWn中列C2的數據，后面依次類推。

備注：我之前有博文做過簡單介紹，大家可以參考這個地址：http://www.cnblogs.com/i6first/p/3217584.html

這樣做的好處就是：

• 更好的數據壓縮，減少磁盤的空間占用

數據的相似性越高、重復的值越多，壓縮的效果就越明顯。
列存儲中的每一列數屬于同一種數據類型，表達的是同一個數據概念（比如都是性別），內容重復度很高，因此相比行壓縮和頁壓縮而言，壓縮效率會更好。
這樣一來不僅可以有效節省磁盤空間，而且可以在同樣的內存中記錄更多的數據，提升查詢的性能。

• 提升查詢的性能

在一個查詢中，我們往往只是想獲取表中我們感興趣的一列或者某幾列的數據，
傳統的查詢做法必須把表中的所有的數據都掃描一遍，從而篩選出這些指定的列，
當表中包含的列比較多、數據量時，這種查詢的效率的就會很低，
但如果使用了列存儲，因為每一個列都是集中且彼此獨立地存儲，查詢時只需要掃描這些指定列所在的存儲區域就好了，不需要讀其他不需要列的數據。查詢的范圍就小了很多。

比如這個查詢：

select c1 from myuser1

因為mytable表中所有的C1列都是集中存儲的，一次查詢只掃描C1列的存儲區域就好了。

什么是列存儲索引

在傳統的表上創建列存儲索引后便可實現表的列存儲。

在SQL Server 2014中，有兩種列存儲索引：列非聚集索引和列聚集索引。

(1)、列非聚集索引的特點

•      跟非聚集索引一樣，創建列非聚集索引時必須為索引列創建一個副本，占用額外的磁盤空間，不過因為數據壓縮的原因，其占用的空間會比較小
•      列非聚集索引的表是不可以更新的

備注：SQL Server 2012上只能建列非聚集索引，不能建列聚集索引。

(2)、列聚集索引的特點

• 索引頁就是數據頁，高壓縮率大大減少磁盤空間的占用
• 可以對表進行更新
• 列聚集索引必須是表的唯一索引，如果表中存在聚集索引或者非聚集索引，則必須刪除原來的索引才能創建列存儲索引
• 只有企業版、開發版、評估版中才能使用
• 不會改變列的物理存儲順序，其目的主要是為了提升性能和實現較高的數據壓縮

備注：讀者可以訪問此地址，了解更多關于列聚集索引相關特性及使用限制。另外，下文如無特別說明，所描述的列存儲均是包含列聚集索引的存儲結構。

創建列聚集索引

列存儲索引創建時不需要指定列名，索引一旦創建完成，表中所有的行就會以列的方式存儲。

CREATE CLUSTERED COLUMNSTORE INDEX ci_myUser ON MyUser1; GO

為了更好的說明在列聚集索引創建時“行表”——>“列存儲”的過程，我做了一張簡圖，并對每個序號的說明如下：

①：首先，表被拆分成一個或者多個行組（ROW GROUP）；

一般而言，每個行組中的行數必須滿足最小為102,400、最大1,048,576后才能轉換成列存儲。但如果直接在表上創建列存儲索引時，這條規則可以“忽視”，因為即使表的行數少于102,400，也可以形成能夠轉接為列存儲的行組。

其實簡單想想也能理解，因為索引創建時，SQL Server不可能等著表著行數增大到102,400后再去形成列存儲。

在下面我演示了一個示例，表MyUser1有102行數據，我在表上創建了一個列存儲索引，大家可以看到只有一個包含了102行的行組，且該行組已經轉換為列存儲了。

SELECT COUNT(*)AS rows_count FROM myuser1

SELECT i.object_id, object_name(i.object_id) AS TableName, i.name
AS IndexName,
    CSRowGroups.state_description,CSRowGroups.row_group_id,CSRowGroups.total_rows, CSRowGroups.*, 100*(total_rows - ISNULL(deleted_rows,0))/total_rows AS PercentFull
FROM sys.indexes AS i JOIN sys.column_store_row_groups AS CSRowGroups 
ON i.object_id = CSRowGroups.object_id
AND i.index_id = CSRowGroups.index_id --WHERE object_name(i.object_id) = '<table_name>' 
ORDER BY object_name(i.object_id), i.name, CSRowGroups.row_group_id;

圖中，該表只有一個行組，行組ID為0（row_group_id），因為state_description為compressed（下文會詳細介紹state_description的值所代表的意義），說明該行組已經按照列方式存儲了。

②：將行組按列劃分列塊；

行組在達到指定的大小后（102400-1048576），必須按照列進行拆分，每一列形成一個列塊。每個列塊包含了這一列的所有數據。

再來看我的示例，表MyUser1一共有33列，按照列塊的定義，就會有33個列塊。

SELECT max_column_id_used FROM SYS.TABles WHERE OBJECT_ID=OBJECT_ID('MYUSER1')

SELECT i.name, p.object_id, p.index_id, i.type_desc, 
            COUNT(*) AS number_of_segments FROM sys.column_store_segments AS s 
INNER JOIN sys.partitions AS p 
        ON s.hobt_id =
            p.hobt_id INNER JOIN sys.indexes AS i 
ON p.object_id = i.object_id
GROUP BY i.name, p.object_id, p.index_id,
        i.type_desc ; GO

圖示：myuser1表的列數33。

圖示：myuser1表的列塊數33（number_of_segments)。

③：每個列塊被壓縮后存儲在物理磁盤上；

步驟2中列塊的形成不是目的，只是手段。

列塊必須壓縮后才能真正按照列方式存儲，根據MSDN說法，壓縮后形成的列存儲最大可以節省7倍的磁盤空間，

在如下示例中，我做了兩個表，simpletable上有列聚集索引，simpletable_nocci上沒有列聚集索引，只有聚集索引，每張表都包含了1048577行數據，且數據內容完全相同。

我們通過sp_spaceused來查看下兩個表的磁盤空間占用情況，

sp_spaceused 'simpletable_nocci'
GO sp_spaceused 
'simpletable'
GO

顯然，simpletable_nocci的磁盤空間占用亮是simpletable的2倍多。

INSERT和BULK INSERT

在含有列聚集索引的表插入的行需要經過行——行組——列塊——列存儲的過程。這與傳統表中插入數據是不同的。。

下面我們通過兩種SQL Server insert方法來了解列存儲中插入數據的過程。

（1） INSERT

          Insert，也稱為TRICKLE  Insert，我們通常使用的insert into就是TRICKLE  Insert。

          在SQL 2014中，每次insert的行不會直接寫入到列存儲中，

          因為這樣會產生大量的索引碎片，而且這種零散的插入不能獲得很好的壓縮效果，影響列存儲和查詢的性能。

          對于這些新插入的數據，SQL 2014中引入了DELTA STORE臨時表，

          新些插入的行還是按照行的方式存儲在DELTA STORE中，并可以通過B-Tree進行檢索。也就說，DELTA STORE其實傳統表的存儲結構一樣，也是行式存儲。

         如圖：

          當DELTA STORE中行組的行數達到所要求的1048,576行時，該行組就會被標記為CLSOED，不再允許新數據插入。

          然后SQL 2014的后臺進程Move Truple掃描到CLSOED的行組時，會將該行組從delta store遷到列存儲，最后將該行組標記為COMPRESSED。

          如圖：

RowGroup1的Row已經達到行組的最大值，該行組被標記為CLOSED，表示不能在接受新數據插入。

RowGroup2的Row小于1048576行，即使滿足行組要求的最小值，也還是在OPEN狀態，直到達到行組最大大小。

行組的狀態可以通過如下語句查詢得到：

SELECT i.object_id, object_name(i.object_id) AS TableName, CSRowGroups.state_description
            FROM sys.indexes AS i JOIN sys.column_store_row_groups AS CSRowGroups
ON i.object_id = CSRowGroups.object_id
AND i.index_id = CSRowGroups.index_id --WHERE object_name(i.object_id) = '<table_name>' 
ORDER BY object_name(i.object_id), i.name, row_group_id;

（2）BULK INSERT

         Bulk insert可以理解為一種高性能的插入方法，Bulk Insert常常用于大數據導入操作，其性能要比Trickle  Insert好很多，

        （有興趣的讀者可以自己驗證下，插入相同行的數據，哪個更快一點。）

         也正是基于此，Bulk Insert在列存儲中插入數據的方法與和Trickle  Insert也有些區別。

         當一次Bulk Insert的數據達到行組的最小值102,400時，該行組可以不經過delta Store而直接按照列方式存儲。

         這里需要引起我們注意的是，之前我們說Trickle  Insert的列存儲形成過程，不僅要求行組達到1048576，而且還必須先存儲在delta Store中。

        由此可見在大批量的數據導入中，Bulk insert是首選的方法。

         下面的實例中，我將一個含有102,400的t1.txt通過bulk insert導入到數據庫中，可以看到該行組的結果直接變為COMPRESSED。

bulk insertBULK INSERT simpletable FROM 'd:\temp\t1.txt'

       t1.txt的格式如下：

不過如果一次Bulk Insert插入的數據大于一個行組但小于兩個行組時，多出的這部分數據必須也存儲在delta Store中。 

DELETE和UPDATE

因為列聚集索引的表可能同時包含行存儲的delta store和列存儲，所以在處理刪除和更新時，兩個不同區域會有所不同。

（1）delete

我們先看刪除操作：

• 如果刪除的行在列存儲中，SQL Server只是從邏輯上刪除它，其占用的物理空間并不會釋放

          SQL Server 2014 的delete bitmap是用來跟蹤列存儲中的每一個記錄刪除情況的表，它跟delta store一樣也是基于行和B-tree的方式存儲。

         當某一行需要被刪除時，delete bitmap會將該行對應的bit標記為刪除狀態，實際上行的所在的物理區域沒有發生變化。

          這樣就要求所有的查詢語句必須先掃描delete bitmap，對于哪些已被記為刪除的記錄就不要到物理存儲查找了，也不需要出現在查詢的結果中。

•      如果刪除的行在Delta Store中，這跟傳統行存儲的刪除方式沒有區別，SQL Server會從邏輯上和物理上都刪除該數據。

（2）update

          理解了insert和delete后，我們再來看update就非常簡單了。

• 如果update發生在列存儲中

SQL Server會將該行在delete bitmap中bit標記為刪除狀態，同時插入一新行到delta store中。

• 如果update發生在delta store中

SQL Server直接更新delta store中這行的數據。

結論

       盡管SQL Server 2014的列存儲已經支持數據更新，但并不意味著生產環境下的報表能夠從中獲益。

       列存儲天生是為OLAP設計，其數據特征更趨向于靜態，即使是數據的導入，微軟也建議使用bulk insert，

       所以如果數據庫的日常操作中存在大量的增、刪、改等操作，使用列存儲的技術可能會適得其反。