SQL Server 列存储索引 第四篇：实时运营数据分析

SQL Server 列存储系列：

实时运营数据分析（real-time operational analytics ）是指同时在同一张数据表上同时执行用于处理日常交易的业务数据（也称作业务查询）和用于数据分析的报表数据（也称作分析查询）。分析查询主要是对海量数据执行聚合查询，对数据进行分析和挖掘，通常需要对大量数据进行处理，执行较长，而业务查询主要是指对数据表进行少量数据的更新和查找，主要是支持日常的交易数据，对时间要求非常严格，需要快速响应用户的查询和更新。

运营工作负载（Operational workload）是指对开展业务至关重要的业务交易。例如，一家零售商店有一个交易系统来创建或修改新订单，而一家信用卡公司则跟踪供应商代表其客户收取的所有费用。 这些交易系统对企业至关重要，因为任何停机时间或速度放缓都会直接影响企业的利润。 因此，这些系统专为性能/可伸缩性而设计，并具有高可用性。 对业务工作负载同样重要的是企业用来回答诸如“完成订单的平均时间是多少时间”之类的问题的分析。

大多数解决方案是在不影响业务查询的前提下，通过在另一台服务器上创建数据仓库，用于实现分析查询的工作负载（analytics workload），周期性把交易系统产生的数据通过ETL（提取，转换和加载）同步到数据仓库中，以更新数据分析的结果。

一，传统的BI系统

在传统的BI系统中，把用于公司日常事务（实时查询交易数据）的工作负载（OLTP）和用于报表分析的工作负载分开，分别对应于运营数据库和数据仓库，通过把这两种工作负载分开，尽可能地避免分析查询对交易数据库的影响。

分析数据通常存储在专用于用于报表分析查询的数据仓库或数据集市中，日常的事务性数据直接写入到运营数据库，为了使数据分析尽量准确地反映事实，需要数据工程师设计ETL（提取，转换和加载）程序，定期把运营数据转移到分析数据库（即数据仓库）中。

尽管有ETL程序定期从事务数据库把数据同步到分析数据库，但是数据的同步不可避免地存在一定的时延，这就导致分析数据库中的数据还是跟运营数据库存在一定的差异（GAP），使得数据分析的结果存在失真的可能性。一份数据两份存储，这也增加了存储、算力等成本，并且需要专业人员来维护数据，增加了系统的复杂性。

二，什么是实时运营数据分析

传统的BI系统之所以存在数据延迟，是因为用于数据分析的数据仓库和实时的交易数据是分开存储的，如果分析查询和业务查询在同一基础表上执行，那么就不会存在时间延迟的问题。实时运营分析的目标是实现在单个数据源上同时执行分析查询和业务查询。

在行存储表上创建可更新的非聚集列存储索引，列存储索引维护数据的副本，分别在数据的单独副本上运行业务处理和分析工作，这样避免了在同一个数据表运行两个工作负载，可以最大程度地减少同时运行的两个工作负载对业务查询性能的影响。

当基表的数据更新时，SQL Server自动更新列存储索引，通过这种设计，用户可以对最新的运营数据进行实时分析，分析查询使用的始终是最新的数据，分析处理的结果是最接近现实数据的，因此，得到的分析结果是最真实和最新的。

实现实时运营数据分析，很简单，用户只需要在基表上创建一个非聚集的列存储索引，就可以实现实时运营数据分析：

CREATE NONCLUSTERED COLUMNSTORE INDEX index_name
ON table_name (colum_list) ;

在表上创建非聚集的列存储索引，底层的物理结构如下图所示：

对运营数据进行实时分析，存在两个挑战：

第一个挑战是，当数据库模式已针对OLTP优化时，如何获得良好的分析查询性能？传统的数据仓库（DW）使用星型或雪花模式为分析查询提供最佳性能。但是，OLTP数据库的架构已高度规范化（即数据重复最少），这主要体现在表之间存在大量的连接，在用于分析查询时，表连接可能会导致查询性能不佳。列存储索引（Nonclustered columstore index，NCCI）可以显著地提高分析查询的速度，不过，需要注意的是，使用实时运营数据分析时，分析查询的性能不会像使用专用DW时所能达到的那样快，但是，关键好处是能够进行实时分析。一种折衷的解决方案是：选择性进行实时运营分析，同时仍保留用于极端分析的专用DW。

第二个挑战是，如何最小化或消除分析工作对事务性工作负载的影响？实际上，交易系统会跟踪其业务交易，交易工作量的任何减缓都是不可接受的。虽然SQL Server提供了多个选项来最小化分析对运营工作负载的影响，但是这种影响是不可避免的。

三，提高实时运营数据分析的性能

延迟压缩可以提高实时运营数据分析的性能，延迟压缩是指当新数据到达deltastore后，延迟到一定的时间后，才对数据进行压缩存储。

通过积累更多的数据，能够提高数据压缩的效率，延迟延迟能够提高事务查询的性能，减少存储空间，进而提高分析查询的性能。

 

 

 

参考文档：

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)

posted @ 2020-11-01 13:34  悦光阴  阅读(985)  评论(0)  编辑  收藏  举报