title: sql server 2016 列存儲技術做實時分析
資料分析指導商業行為的價值越來越高，使得使用者對資料實時分析的要求變得越來越高。使用傳統rdbms資料分析架構，遇到了前所未有的挑戰，高延遲、資料處理流程複雜和成本過高。這篇文章讨論如何利用sql server 2016列存儲技術做實時資料分析，解決傳統分析方法的痛點。
在過去很長一段時間，企業均選擇傳統的關系型資料庫做olap和data warehouse工作。這一節讨論傳統rdbms資料分析的結構和面臨的挑戰。
傳統關系型資料庫做資料分析的架構，按照功能子產品可以劃分為三個部分：
oltp子產品：oltp的全稱是online transaction processing，它是資料産生的源頭，對資料的完整性和一緻性要求很高；對資料庫的反應時間（rt: response time）非常敏感；具有高并發，多事務，高響應等特點。
etl子產品：etl的全稱是extract transform load。他是做資料清洗、轉化和加載工作的。可以将etl了解為資料從oltp到data warehouse的“搬運工”。etl最大的特定是具有延時性，為了最大限度減小對oltp的影響，一般會設計成按小時，按天或者按周來周期性運作。
olap子產品：olap的全稱是online analytic processing，它是基于資料倉庫（data warehouse）做資料分析和報表呈現的終端産品。資料倉庫的特點是：資料形态固定，幾乎或者很少發生資料變更，統計查詢分析讀取資料量大。
傳統的rdbms分析模型圖，如下圖展示（圖檔直接截取自微軟的教育訓練材料）：
從這個圖，我們可以非常清晰的看到傳統rdbms分析模型的三個大的部分：在圖的最左邊是oltp業務場景，負責采集和産生資料；圖的中部是etl任務，負責“搬運”資料；圖的右邊是olap業務場景，負責分析資料，然後将分析結果交給bi報表展示給最終使用者。企業使用這個傳統的架構長達數年，遇到了不少的挑戰和困難。
商場如戰場，戰機随息萬變，資料分析結果指導商業行為的價值越來越高，使得資料分析結果變得越來越重要，使用者對資料實時分析的要求變得越來越高。使用傳統rdbms分析架構，遇到了前所未有的挑戰，主要的痛點包括：
資料延遲大
資料處理流程冗長複雜
成本過高
資料延遲大：為了減少對oltp子產品的影響，etl任務往往會選擇在業務低峰期周期性運作，比如淩晨。這就會導緻olap分析的資料源data warehouse相對于oltp有至少一天的時間差異。這個時間差異對于某些實時性要求很高的業務來說，是無法接受的。比如：銀行卡盜刷的檢查服務，是需要做到秒級别通知持卡人的。試想下，如果你的銀行卡被盜刷，一天以後才收到銀行發過來的短信提醒，會是多麼糟糕的體驗。
資料處理流程冗長複雜：資料是通過etl任務來抽取、清洗和加載到data warehouse中的。為了保證資料分析結果的正确性，etl還必須要解決一系列的問題。比如：oltp變更資料的捕獲，并同步到data warehouse；周期性的進行資料全量和增量更新來確定oltp和data warehouse中資料的一緻性。整個資料流冗長，實作邏輯異常複雜。
成本過高：為了實作傳統的rdbms資料分析功能，必須新增data warehouse角色來儲存所有的oltp資料備援，專門提供分析服務功能。這勢必會加大了硬體、軟體和維護成本投入；随之還會到來etl任務做資料抓取、清洗、轉換和加載的開發成本和時間成本投入。
那麼，sql server有沒有一種技術既能解決以上所有痛點的方法，又能實作資料實時分析呢？當然有，那就是sql server 2016列存儲技術。
為了解決olap場景的查詢分析，微軟從sql server 2012開始引入列存儲技術，大大提高了olap查詢的性能；sql server 2014解決了列存儲表隻讀的問題，使用場景大大拓寬；而sql server 2016的列存儲技術徹底解決了實時資料分析的業務場景。使用者隻需要做非常小規模的修改，便可以可以非常平滑的使用sql server 2016的列存儲技術來解決實時資料分析的業務場景。這一節讨論以下幾個方面：
sql server 2016資料分析架構
disk-based tables with nonclustered columnstore index
memory-based tables with columnstore index
minimizing impacts of oltp
sql server 2016資料分析架構相對于傳統的rdbms資料分析架構有了非常大的改進，變得更加簡單。具體展現在olap直接接入oltp資料源，如此就無需data warehouse角色和etl任務這個“搬運工”了。
olap直接接入oltp資料源：讓olap報表資料源直接接入oltp的資料源頭上。sql server會自動選擇合适的列存儲索引來提高資料分析查詢的性能，實作實時資料分析的場景。
不再需要etl任務：由于olap資料源直接接入oltp的資料，沒有了data warehouse角色，是以不再需要etl任務，進而大大簡化了資料處理流程中的各環節，沒有了相應的開發維護和時間成本。
sql server 2016實時分析架構圖，展示如下（圖檔來自微軟教育訓練教程）：
sql server 2016之是以能夠實作如此簡化的實時分析，底氣是來源于sql server 2016的列存儲技術，我們可以建立基于磁盤存儲或者基于記憶體存儲的列存儲表來進行實時資料分析。
使用sql server 2016列存儲索引實作實時分析的第一種方法是為表建立非聚集列存儲索引。在sql server 2012版本中，僅支援非聚集列存儲索引，并且表會成為隻讀，而無法更新；在sql server 2014版本中，支援聚集列存儲索引表，且資料可更新；但是非聚集列存儲索引表還是隻讀；而在sql server 2016中，完全支援非聚集列存儲索引和聚集列存儲索引，并且表可更新。是以，在sql server 2016版本中，我們完全可以建立非聚集列存儲索引來實作olap的查詢場景。建立方法示例如下：
在這個執行個體中，我們建立了salesorder表，并且為該表建立了非聚集列存儲索引，當進行olap查詢分析的時候，sql server會直接從該列存儲索引中讀取資料。
sql server 2014版本引入了in-memory oltp，又或者叫着hekaton，中文稱之為記憶體優化表，記憶體優化表完全是lock free、latch free的，可以最大限度的增加并發和提高響應時間。而在sql server 2016中，如果你的伺服器記憶體足夠大的話，我們完全可以建立基于記憶體優化表的列存儲索引，這樣的表資料會按列存儲在記憶體中，充分利用兩者的優勢，最大程度的提高查詢查詢效率，降低資料庫響應時間。建立方法執行個體如下：
在這個執行個體中，我們建立了基于記憶體的優化表salesorder，持久化方案為表結構和資料；然後在這個記憶體表上建立聚集列存儲索引。當olap查詢分析執行的時候，sql server可以直接從基于記憶體的列存儲索引中擷取資料，大大提高查詢分析的能力。
考慮到oltp資料源的高并發，低延遲要求的特性，在某些非常高并發事務場景中，我們可以采用以下方法最大限度減少對oltp的影響：
filtered ncci + clustered b-tree index
compress delay
offloading olap to alwayson readable secondary
帶過濾條件的索引在sql server産品中并不是什麼全新的概念，在sql server 2008及以後的産品版本中，均支援建立過濾索引，這項技術允許使用者建立存在過濾條件的索引，以加速特定條件的查詢語句使用過濾索引。而在sql server 2016中支援存在過濾條件的列存儲索引，我們可以使用這項技術來區分資料的冷熱程度（資料冷熱程度是指資料的修改頻率；冷資料是指幾乎或者很少被修改的資料；熱資料是指經常會被修改的資料。比如在訂單場景中，訂單從生成狀态到客戶收到貨物之間的狀态，會被經常更新，屬于熱資料；而客人一旦收到貨物，訂單資訊幾乎不會被修改了，就屬于冷資料）。利用過濾列存儲索引來區分冷熱資料的技術，是使用聚集b-tree索引來存放熱資料，使用過濾非聚集列存儲索引來存放冷資料，這樣sql server 2016的優化器可以非常智能的從非聚集列存儲索引中擷取冷資料，從聚集b-tree索引中擷取熱資料，這樣使得olap操作與oltp事務操作邏輯隔離開來，最終olap最大限度的減少對oltp的影響。
下圖直覺的表示了filtered ncci + clustered b-tree index的結構圖（圖檔來自微軟教育訓練教程）：
實作方法參見以下代碼：
在這個執行個體中，我們建立了salesorder表，并在orderstatus字段上建立了clustered b-tree結構的索引ci_salesorder，然後再建立了帶過濾條件的非聚集列存儲索引ncci_salesorder。當客人還未收到貨物的訂單，會處于前面五中狀态，屬于需要經常更新的熱資料，sql server查詢會根據clustered b-tree索引ci_salesorder來查詢資料；客人已經收貨的訂單，處于第六種狀态，屬于冷資料，sql server查詢冷資料會直接從非聚集列存儲索引中擷取資料。進而最大限度減少對oltp影響的同時，提高查詢效率。
如果按照業務邏輯層面很難明确劃分出資料的冷熱程度，也就是說很難從過濾條件來邏輯區分資料的冷熱。這種情況下，我們可以使用延遲壓縮（compress delay）技術從時間層面來區分冷熱資料。比如：我們定義超過60分鐘的資料為冷資料，60分鐘以内的資料為熱資料，那麼我們可以在建立列存儲索引的時候添加with選項compression_delay = 60 minutes。當資料産生超過60分鐘以後，資料會被壓縮存放到列存儲索引中（冷資料），60分鐘以内的資料會駐留在delta store的b-tree結構中，這種延遲壓縮的技術不但能夠達到隔離olap對oltp作用，還能最大限度的減少列存儲索引碎片的産生。
實作方法參見以下例子：
檢查索引資訊截圖如下：
另外一種減少olap對oltp影響的方法是利用alwayson隻讀副本，這種情況，可以将olap資料源從oltp剝離出來，接入到alwayson的隻讀副本上。alwayson的主副本負責事務處理，隻讀副本可以作為olap的資料分析源，這樣實作了olap與oltp的實體隔離，将影響減到最低。架構圖如下所示（圖檔來自微軟教育訓練教程）：
在訂單系統場景中，使用者收到貨物過程，每個訂單會經曆6中狀态，假設為placed、canceled、paid、pending、shipped和received。在前面5中狀态的訂單，會被經常修改，比如：打包訂單，出庫，更新快遞資訊等，這部分經常被修改的資料稱為熱資料；而訂單一旦被客人接受以後，訂單資料就幾乎不會被修改，這部分資料稱為冷資料。這個例子就是使用sql server 2016 filtered ncci + clustered b-tree索引的方式來邏輯劃分出資料的冷熱程度，sql server在查詢過程中，會從非聚集列存儲索引中取冷資料，從b-tree索引中取熱資料，最大限度提高olap查詢效率，減少對oltp的影響。
具體建表代碼實作如下：
這篇文章講解利用sql server 2016列存儲索引技術實作資料實時分析的兩種方法，以解決傳統rdbms資料分析的高延遲、高成本的痛點。第一種方法是hekaton + clustered columnstore index；第二種方法是filtered nonclustered columnstore index + clustered b-tree。本文并以此理論為基礎，展示了一個網絡汽車線上銷售系統的實時訂單分析頁面。
<a href="https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/06/real-time-operational-analytics-filtered-nonclustered-columnstore-index-ncci/">real-time operational analytics: filtered nonclustered columnstore index (ncci)</a>
<a href="https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/07/real-time-operational-analytics-memory-optimized-table-and-columnstore-index/">real-time operational analytics: memory-optimized tables and columnstore index</a>
<a href="https://blogs.technet.microsoft.com/dataplatforminsider/2015/12/09/real-time-operational-analytics-using-in-memory-technology/">real-time operational analytics using in-memory technology</a>