SQL Server 列存儲索引第四篇：實時運營數據分析

2020 年 11 月 1 日
筆記
SQL Server, SQL Server 管理

實時運營數據分析（real-time operational analytics ）是指同時在同一張數據表上執行分析處理和業務處理。分析查詢主要是對海量數據執行聚合查詢，而事務主要是指對數據表進行少量數據的更新和查找。

運營工作負載（Operational workload）是指對開展業務至關重要的業務交易。例如，一家零售商店有一個交易系統來創建或修改新訂單，而一家信用卡公司則跟蹤供應商代表其客戶收取的所有費用。這些交易系統對企業至關重要，因為任何停機時間或速度放緩都會直接影響企業的利潤。因此，這些系統專為性能/可伸縮性而設計，並具有高可用性。對業務工作負載同樣重要的是企業用來回答諸如「完成訂單的平均時間是多少時間」之類的問題的分析。

大多數客戶通過在另一台服務器上創建數據倉庫來實現分析工作負載（analytics workload），周期性把交易系統產生的數據通過ETL（提取，轉換和加載）同步到數據倉庫。

一，傳統的BI系統

在傳統的BI系統中，把用於公司日常事務的工作負載（OLTP）和用於報表分析的工作負載分開，分別對應於運營數據庫和數據倉庫，通過把兩種工作負載分開，儘可能地避免分析查詢對交易數據庫的影響。

分析數據通常存儲在專用於用於報表分析查詢的數據倉庫或數據集市中。日常的事務數據直接寫入到運營數據庫，為了使分析盡量準備準確，需要數據工程師設計ETL（提取，轉換和加載）程序，定期把運營數據轉移到分析數據庫（即數據倉庫）中。

儘管有ETL程序定期從事務數據庫把數據同步到分析數據庫，但是數據的同步不可避免地存在一定的時延，這就導致分析數據庫中的數據還是跟運營數據庫存在一定的差異（GAP），使得數據分析的結果存在失真的可能性。

二，什麼是實時運營數據分析

傳統的BI系統存在數據延遲的原因是用於分析處理和事務處理的表是分開的，如果分析處理和事務處理在同一基礎表上執行，那麼就不會存在時間延遲的問題。

實時運營分析的目標是單個數據源的場景，例如企業資源計劃（ERP）應用程序，用戶可以在其上執行日常的事務處理和分析工作。

實時分析在行存儲表上使用可更新的非聚集列存儲索引，列存儲索引維護數據的副本，分別在數據的單獨副本上運行事務處理和分析工作，這樣避免了在同一個數據表運行兩個工作負載，可以最大程度地減少同時運行的兩個工作負載對查詢性能的影響。

當基表的數據更新時，SQL Server自動更新列存儲索引，通過這種設計，用戶可以對最新數據實時運行分析，分析查詢使用的始終是最新的數據，分析處理的結果是最接近現實數據的，因此，得到的分析結果是最真實和最新的。

用戶只需要在基表上創建一個非聚集的列存儲索引，就可以實現實時運營數據分析：

CREATE NONCLUSTERED COLUMNSTORE INDEX index_name
ON table_name (colum_list) ;

在表上創建非聚集的列存儲索引，底層的物理結構如下圖所示：

對運營數據進行實時分析，存在兩個挑戰：

第一個挑戰是，當數據庫模式已針對OLTP優化時，如何獲得良好的分析查詢性能？傳統的數據倉庫（DW）使用星型或雪花模式為分析查詢提供最佳性能。但是，OLTP數據庫的架構已高度規範化（即數據重複最少），這主要是因為大量表之間的連接複雜性，在用於分析查詢時可能會導致性能不佳。列存儲索引（即NCCI）可以顯着提高查詢速度，可以克服查詢的複雜性，並在幾秒鐘內仍然可以提供大多數分析查詢。在這一點上，必須強調的是，使用實時操作分析的分析查詢性能不會像使用專用DW時所能達到的那樣快，但是關鍵好處是能夠進行實時分析。一些企業可能選擇進行實時運營分析，同時仍保留用於極端分析的專用DW。有些客戶已經在生產中部署了NCCI，並取消了專用DW。

第二個挑戰是，如何最小化或消除分析對事務性工作負載的影響？實際上，交易系統會跟蹤其業務交易，交易工作量的任何減緩都是不可接受的。雖然SQL Server提供了多個選項來最小化分析對運營工作負載的影響，但是這種影響是不可避免的。

參考文檔：

Get started with Columnstore for real-time operational analytics

Real-Time Operational Analytics Using In-Memory Technology

Real-Time Operational Analytics – Overview nonclustered columnstore index (NCCI)

Tags: SQL Server SQL Server 管理