通過MS SQL列存儲索引實現大數據解決方案
現如今的大數據處理方案需要在比以往更短的時間內應對越來越大的數據量。MS SQL 2012版本首次引入了列存儲（CS）索引技術，這也是SQL Server首次嘗試從傳統的行存儲結構轉變為面向列的存儲方案，他們承諾這種方案能夠以最低限度的額外工作量換取更高的性能。
MS SQL列存儲索引的主要目標是將盡量多的數據加載至內存中，在進行數據處理時將訪問內存，而不是直接從磁盤中讀取。這種處理方式有兩大優點，一是速度更快，二是硬盤的IOPS（每秒讀寫次數）消耗更低。但這一功能尚未臻完美。2012版本中的問題在于它僅支持只讀模式，雖然這一缺點在2014版本中通過聚集列存儲索引（Clustered CS Index）得到了彌補，允許用戶在表中修改數據。但在使用聚集列存儲索引的同時，就無法建立普通的索引，也不支持計算列、外鍵和觸發器。在處理數據的時候，了解在哪種場景中使用哪種索引方案最為高效，這一點至關重要。
歷史背景
列存儲技術背后的思想并不是微軟首創的，早在上世紀70年代，基于列的存儲系統就與傳統的行存儲數據庫管理系統一同出現了。最廣為人知的基于列的關系型數據庫軟件系統之一是上世紀90年代問世的Sybase IQ，它目前歸屬于SAP。在之后的歲月中不斷出現了各種新產品，這一趨勢改變了商業智能這一技術領域的發展，也拓展了大數據技術的市場。這些產品中最為知名的包括Vertica、ParAccel、Kognito、Infobright以及SAND。列存儲技術在MS SQL Server 2012中首次亮相，它的基本思想是將數據按照列分組后再保存，而不是按照行進行分組。
傳統的RDBMS中的數據類似于下表中的呈現，每一頁中的數據會包含多個列，或許會包括更多的行：
以下是一份示例數據:
目前為止一切良好。
過量的數據讀取與生產力的下降
問題來了：如果有一張表包含了大量的列，并假設要獲取某個指定的結果只需要返回1至3列的數據。在這種情況下，MS SQL仍然需要讀取包含了所有列數據的整個頁，并“返回”所需的那部分數據。這就造成了過量的數據讀取，從而導致了生產力的下降。
讓我們考慮一下微軟所提出的新的存儲方式，以了解MS SQL如何幫助我們解決這一問題：
在上表中，數據是按照列進行分組的。這樣分組的數據具有很高的一致性，因而提高了數據的壓縮比。這樣一來，就可以將更多的數據直接加載至內存中，而不是從磁盤中進行讀取。如果在查詢中只需要某一列數據，MS SQL Server就只會讀取對應數據所在的頁。正如我之前所說，這種方式具有兩個優點：更快的速度和更低的硬盤IOPS消耗。
在數據的一致性程度與數據的壓縮比之間存在著一種確定的相關性。基于列的數據組織方式通常會假定數據具有一定的一致性，可能的變化較少（在我們的示例中僅有兩個值：NY和CA），這也意味著能夠將更多的數據進行壓縮后加載至內存中。數據的可變性越大，壓縮的CPU時間就越長，并且壓縮比也越低。但即便如此，列存儲仍然具有高效性，因為系統只需要處理必要的數據，而不是遍歷包含數據的全部頁。因此，在處理數據時，應牢記所選擇的數據的類型將影響處理的速度，這一點十分重要。
同樣重要的是了解SQL Server列存儲索引在使用方面的限制。在MS SQL 2012與2014中的列存儲技術存在著巨大的差異，MS SQL 2014支持聚集與非聚集的列存儲索引，而2012僅支持非聚集索引。以下是列存儲的使用限制：
- 在列存儲索引中不可使用以下數據類型：
- binary(n)、varbinary(n)（在2014及更高版本中允許使用，但不包括varbinary(max)）
- image、text、ntext、varchar(max)、nvarchar(max)
- sql_variant
- xml
- 只能通過刪除及創建索引的方式重建索引，而不可使用ALTER INDEX命令
- 在視圖或索引視圖中無法使用列存儲索引
- 列存儲索引無法結合使用以下特性：
- 列存儲索引不可包含多于1024個列
- 對應的表不可包含唯一性約束、主鍵約束或外鍵約束
使用列存儲索引時所隱含的一些挑戰
雖然列存儲索引技術看上去前景非常光明，但在2012版中對于它的使用仍然有許多重大限制。
最大的不便之處在于，使用了列存儲索引的表將無法進行數據的變更、新增或刪除。實際上，這張表已經進入了只讀狀態。用戶不得不采取以下方式應對：首先刪除索引，加載變更后再重建索引。對于海量數據來說，整個過程所占用的超長時間足以抵消列存儲索引功能所帶來的正面效果。
在MS SQL Server 2014版中宣布引入了聚集列存儲索引，它允許在表中進行數據修改。但問題在于，使用列存儲索引之后，就無法使用計算字段、外鍵與觸發器了。因此，在使用列存儲索引功能時，對以上所提及的限制要事先有所警覺。
列存儲的驚艷效果
列存儲功能體驗足以令人驚艷，讓我們來看一下以下示例：我們在表中保存的銷售數據超過5千萬條記錄。現在讓我們嘗試一下計算每個客戶的銷售總額。
運行以下查詢：
-- 清除SQL SERVER查詢緩存 DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE SET STATISTICS IO ON SET STATISTICS TIME ON SELECT CUSTOMER_ID, SUM(AMOUNT) FROM ORDER_DETAILS GROUP BY CUSTOMER_ID
以下是運行的結果：
表'ORDER_DETAILS'。掃描5次，邏輯讀132615，物理讀取0，預讀132622，lob邏輯讀0，lob物理讀0，lob預讀0.
SQL Server執行時間:
CPU time = 12699 ms, elapsed time = 11064 ms.
執行計劃:
(點擊放大圖像)
聚集掃描詳細信息：
現在讓我們來創建一個聚集列存儲索引，并再次運行該查詢（在查詢執行前必須先清除緩存）。應當指出的是，在這種數據量的表中創建索引大約需要一分鐘左右時間。我們還必須刪除表中的聚集索引，因為一張表無法包含兩個聚集索引，這是使用列存儲索引的限制，正如我們在上文中所說的。
CREATE CLUSTERED COLUMNSTORE INDEX columnstore_idx ON ORDER_DETAILS
現在讓我們再一次運行之前的那個查詢：
-- 清除SQL SERVER查詢緩存 DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE SET STATISTICS IO ON SET STATISTICS TIME ON SELECT CUSTOMER_ID, SUM(AMOUNT) FROM ORDER_DETAILS GROUP BY CUSTOMER_ID
結果如下：
表'ORDER_DETAILS'。掃描4次，邏輯讀35262，物理讀23，預讀48195，lob邏輯讀0，lob物理讀0，lob預讀0。
SQL Server執行時間:
CPU time = 1248 ms, elapsed time = 1634 ms.
執行計劃:
(點擊放大圖像)
列存儲索引掃描詳細信息：
是不是驚艷到你了？對你的反應我們期待以久了。
如你所見，使用列存儲索引之后，讀取次數下降了4倍，而執行時間下降了10倍。
但如果是對數據進行搜索呢？
讓我們嘗試在一個使用了列存儲索引的表中運行以下查詢：
-- 清除SQL SERVER查詢緩存 DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE SET STATISTICS IO ON SET STATISTICS TIME ON declare @p1 float declare @p2 float set @p1 = 100.0 set @p2 = 200.0 SELECT CUSTOMER_ID, AMOUNT FROM ORDER_DETAILS WHERE CUSTOMER_ID = 651 AND AMOUNT BETWEEN @p1 AND @p2
(1571 row(s) affected)
表'ORDER_DETAILS'。掃描4次，邏輯讀36031，物理讀21，預讀52794，lob邏輯讀0，lob物理讀0，lob預讀0。
SQL Server執行時間：
CPU time = 79 ms, elapsed time = 1125 ms.
在我看來結果還可以。
現在讓我們刪除列存儲索引，并創建我們所需的索引：
CREATE INDEX idx1 ON ORDER_DETAILS(CUSTOMER_ID, AMOUNT)
然后再次運行相同的查詢
(1571 row(s) affected)表'ORDER_DETAILS'。掃描1次，邏輯讀9，物理讀1，預讀5，lob邏輯讀0，lob物理讀0，lob預讀0。
(1 row(s) affected)
SQL Server執行時間：
CPU time = 0 ms, elapsed time = 254 ms.
很顯然，列存儲索引此時就毫無用武之地了。當然了，沒有哪種“驚艷”是與生俱來的。并且可以有把握的說，記錄的數量越多，服務器的負載越大，列存儲索引的效率就越低。之所以產生這種結果，是因為這種查詢需要讀取大量的信息，并且無法使用普通的“搜索”索引。好消息是，微軟已經宣布在MS SQL 2016中同時支持列存儲索引與B樹索引了。
但是，如果我們既想要實現快速的搜索，同時又想要進行快速的計算并獲得統計數據，我們該怎么辦呢？在MS SQL 2012中，我們只剩下一種選擇：定期地創建一個備份表，為其添加必要的聚集索引。由于在MS SQL 2014中引入了聚集列存儲索引，我們就能夠更新數據了，但必須在主表與備份表中同時進行數據的添加與修改。不過，如果你問我最好的辦法是什么，我會回答你還是耐心等待2016問世吧！
在SQL Server 2016中我們將看到以下一些主要變化：
- 之前版本中的非聚集列存儲索引都是只讀的。在2016中，行存儲表（即不包含聚集列存儲索引的表）中將能夠創建一個可更新的非聚集列存儲索引。
- 在之前版本中，使用列存儲索引的表不支持普通的非聚集索引。在2016中，非聚集列存儲索引定義支持使用某種經過濾的條件，在使用聚集列存儲索引的表中可定義一至多個非聚集的普通行存儲索引。
- 在聚集列存儲索引表中將支持使用主鍵與外鍵，通過使用B樹的方式強制這些約束。
總的來說，列存儲索引技術并非適用一切場合的銀彈，但它確實能夠表現出非凡的結果。對于通過聯機分析處理（OLAP）的Cube進行數據匯總的場景，它表現十分完美，能夠促進生產力的提高。不過，常規的索引以及常規的表優化手段在大數據處理方面仍有用武之地，同樣能夠表現出良好的結果，前提是你知道怎樣以及何時使用他們。
關于作者
Aleksandr Shavlyuga 于1999年畢業于白俄羅斯國立大學（Belarusian State University），隨后在多個IT公司中擔任Delphi開發者、軟件架構師、數據庫架構師以及管理員。他在2007加入 Itransition 擔任.NET開發者，并在2008年升任高級開發者。Aleksandr擁有Brainbench頒發的ANSI SQL和MS SQL Programmer證書。他現在專注于打造高伸縮的web應用與數據庫設計。他在技術方面感興趣的領域包括.NET以及Oracle和MS SQL Server數據庫。