SQL Server 2014 聚集列儲存

標籤：uil   lte   cci   b-tree   drop   dex   升級   提升   維護   

　　SQL Server 自2012以來引入了列儲存的概念，至今2016對列儲存的支援已經是非常友好了。由於我這邊線上環境主要是2014，所以本文是以2014為基礎的SQL Server 的列儲存的介紹。下面我們主要看一下列儲存的發展以及一些原理：

列儲存的開發是想要處理超大量資料進行分析計算，於是在SQL Server 2012時，SQL Server 引入了資料行存放區索引，用以顯著提供高傳統資料倉儲類型語句的效能，並在SQL Server 2014中做了進一步加強。列儲存會將一個列的資料單獨存放在一起，所以主要會有以下兩個優點。

1：同一個列中的資料的相似性比較高，因此壓縮比例會更高。磁碟操作時，磁碟的IO也會相應的降低。當然，當壓縮的資料讀取到記憶體後解壓會需要額外的CPU。

2：由於資料是按照列進行儲存和讀取的，因此如果某些列在訪問中並不需要，那麼實際的操作時也會不訪問這些列，那麼磁碟IO會進一步降低。

3：由於資料是按照列進行儲存和讀取的，大批量的資料彙總訪問等會較以往的行儲存更快。

對於列儲存來說，主要來說就是資料倉儲這個使用情境了，微軟最近幾年也是在這個方面頻頻發力。對於資料倉儲來說，CPU，記憶體，磁碟都可能稱為效能的瓶頸，但是我們指導磁碟的操作來說相比記憶體和CPU效能是最慢的，而列儲存恰恰是對IO的效能提升是很大的，列儲存會減少磁碟的IO操作，提升運算的效率，特別是大量資料的彙總。當然如果是一些線上的精確尋找等操作，列儲存並不是最好的選擇。

　　對於這些效能的提升和儲存 空間的最佳化，主要是和列儲存的實現原理是分不開的（由於非聚集列儲存的功能比較雞肋，我們就不介紹了，因為有非聚集列儲存的表成為了一個唯讀表）：

1：Clustered columnstore inde – 整個表都按照列儲存進行組織，直接替代了傳統的堆表或者叢集索引，可以自由的進行增刪改操作。

2：聚集資料行存放區索引雖然相對於非聚集資料行存放區索引在column store這塊組織架構基本一樣，但是它可以進行增刪改操作。原因是它多了一塊或者多塊行儲存部分，這部分稱之為delta tore。

新插入的資料是直接載入到delta store中的刪除操作只是將資料標識為刪除，實際的刪除需要在rebuild時完成。更新操作會拆分為一個刪除操作和一個插入合并完成。如果一個bulk insert的批次插入的量小於100000，那麼資料會載入到delta store中，否則會載入到columnstore中。當delta store中資料量超過100 0000後，“Tuple mover” 會將其中資料進行歸總放置到column store中。貼一個官方圖，方便更好的理解聚集列儲存：

原理大概看完之後，下面給出SQL Server2014對列儲存的改進：

● 支援資料的讀和寫
● 在打破了資料唯讀限制後，資料行存放區索引使用的範圍和情境大大增加
● 相比傳統的ad-hoc的增刪改操作，在SQL Server2014還是推薦使用bulk insert和分區交換來進行大批次資料的更新，效率更高，維護成本也會降低
● 支援更多的資料類型
● 添加了更多的資料類型支援：(n)varchar(max), varbinary(max), XML, Spatial, CLR
● 基本說來，SQL Server2014的列儲存支援所有的non-blob資料類型
● 整個表可建立並且只能建立一個聚集資料行存放區索引。傳統的行儲存會需要非叢集索引協助提高訪問效率，但是列儲存無需這樣。並且由於只有一份資料，因此儲存需要的磁碟空間大大降低
● 非聚集列索引仍然支援，並且還是唯讀結構。
當我們有了聚集資料行存放區索引後，就不需要非聚集列索引了，因為此時所有的資料都是按照列儲存了。但是如果表上需要添加Constraints或者工作負載仍然需要B-tree形式的非叢集索引，那麼我們還是只能考慮使用非聚集資料行存放區索引。
● 語句的執行上有以下改進
○ 基於向量的計算方式得到改
○ 支援更多的文法
■ 所有的join方式(包括OUTER, HASH, SEMI (NOT IN, IN)
■ UNION ALL
■ Scalar aggregates
■ “Mixed mode” plans
● 對bitmap和spill操作有進一步的改進
● 對hash join有所改進

　　其實我在SQL Server 2014列儲存的實踐當中，還發現有幾個不是非常友好的地方

1：SQL Server 2014聚集列儲存並不支援視圖功能，這個還是比較坑的，因為列儲存的主要應用情境就是資料倉儲，有很多視圖來說要提供報表或者提供給報表部門查詢許可權，通過視圖能夠隱藏很多敏感資訊，而不支援視圖就會很難做決定來具體修改為列儲存了

2：SQL Server 2014聚集列儲存並不支援alwayson從庫的查詢

3：SQL Server 2014  12.0.2版本對列儲存有漏洞，alwayson日誌同步的時候容易造成內部鎖爭用，影響主從的同步，這點功能我們可能要升級SP1補丁才能解決，我這邊從庫升級後至今沒有出現這個問題，這也是SQL Server 2014列儲存的一個BUG吧

以上也算是在生產環境走過的坑，因為考慮不是很周全走了不少路。希望大家能夠引以為戒。除了以上幾個坑以外，列儲存還不支援以下的功能：

在資料行存放區索引中不可使用以下資料類型：
binary(n)、varbinary(n)（在2014及更高版本中允許使用，但不包括varbinary(max)），image、text、ntext、varchar(max)、nvarchar(max)，sql_variant，xml
只能通過刪除及建立索引的方式重建索引，而不可使用ALTER INDEX命令
在視圖或索引檢視表中無法使用資料行存放區索引
資料行存放區索引無法結合使用以下特性：分發，變更資料擷取，變更追蹤，Filestream
資料行存放區索引不可包含多於1024個列
對應的表不可包含唯一性限制式、主鍵約束或外鍵約束

接下來我們看一下列儲存的一些實踐：

1：建立列儲存的表

CREATE TABLE maxiangqian( id [int] NOT NULL, age [int] NOT NULL, sex [tinyint] NOT NULL, name varchar(20)); GO CREATE CLUSTERED COLUMNSTORE INDEX cci_Simple ON maxiangqian; GO :

2：行叢集索引轉換為列儲存：

CREATE TABLE maxiangqian( id [int] NOT NULL, age [int] NOT NULL, sex [tinyint] NOT NULL, name varchar(20)); GO CREATE CLUSTERED INDEX cl_simple ON maxiangqian (id); GO CREATE CLUSTERED COLUMNSTORE INDEX cl_simple ON maxiangqian WITH (DROP_EXISTING = ON);

或者說我們也可以直接刪除叢集索引，然後再

CREATE CLUSTERED COLUMNSTORE INDEX cl_simple ON maxiangqian 

效果是一樣一樣的。

3 將一個堆錶轉化為列儲存表：

第一步就是刪除堆表現有的索引，然後建立聚集資料行存放區索引：

CREATE TABLE maxiangqian(      id [int] NOT NULL,       age [int] NOT NULL,       sex [tinyint] NOT NULL,       name varchar(20));  GO  create index pid on maxiagnqian(id)drop index pid on maxiangqian CREATE CLUSTERED COLUMNSTORE INDEX cci_Simple ON maxiangqian;  GO  :

上面基本上已經滿足你建立列儲存的一些功能，下面我們看一下怎麼把一個聚集列儲存的錶轉化為普通表：

CREATE CLUSTERED INDEX pid   ON maxiangqian  WITH ( DROP EXISTING = ON );  或者DROP INDEX cci_Simple   ON MyFactTable; 

OK，我們基本上已經可以知道怎麼建立資料行存放區索引了

但是我們指導由於列儲存刪除的時候只是標記，所以說列儲存如果經常更新刪除，片段還是會很大的，下面我們看下怎麼消除片段---重建：

CREATE CLUSTERED COLUMNSTORE INDEX cci_Simple   ON maxiagnqian  WITH ( DROP_EXISTING = ON );  ALTER INDEX   cci_Simple ON maxiangqian REBUILD PARTITION = ALL  WITH ( DROP_EXISTING = ON );  

以上兩種方式是都可以實現的。

其實對於列儲存來說，臥鋪，我這邊給我比較大的驚喜就是磁碟空間的節約，列儲存的壓縮比例可以達到10:1甚至15:1，而且相對來說對於我資料倉儲一些大批量的彙總操作效能提升。在節省空間的又提高效能的情況下，你還有什麼理由不選用列儲存呢。

 

SQL Server 2014 聚集列儲存