数据分析指导商业行为的价值越来越高, 使得用户对数据实时分析的要求变得越来越高. 使用传统 RDBMS 数据分析架构, 遇到了前所未有的挑战, 高延迟, 数据处理流程复杂和成本过高. 这篇文章讨论如何利用 SQL Server 2016 列存储技术做实时数据分析, 解决传统分析方法的痛点.
传统 RDBMS 数据分析
在过去很长一段时间, 企业均选择传统的关系型数据库做 OLAP 和 Data Warehouse 工作. 这一节讨论传统 RDBMS 数据分析的结构和面临的挑战.
传统 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 列存储技术.
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 数据分析架构
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 的列存储技术, 我们可以建立基于磁盘存储或者基于内存存储的列存储表来进行实时数据分析.
Disk-based Tables with Nonclustered Columnstore Index
使用 SQL Server 2016 列存储索引实现实时分析的第一种方法是为表建立非聚集列存储索引. 在 SQL Server 2012 版本中, 仅支持非聚集列存储索引, 并且表会成为只读, 而无法更新; 在 SQL Server 2014 版本中, 支持聚集列存储索引表, 且数据可更新; 但是非聚集列存储索引表还是只读; 而在 SQL Server 2016 中, 完全支持非聚集列存储索引和聚集列存储索引, 并且表可更新. 所以, 在 SQL Server 2016 版本中, 我们完全可以建立非聚集列存储索引来实现 OLAP 的查询场景. 创建方法示例如下:
- DROP TABLE IF EXISTS dbo.SalesOrder;
- GO
- CREATE TABLE dbo.SalesOrder
- (
- OrderID BIGINT IDENTITY(1,1) NOT NULL
- ,AutoID INT NOT NULL
- ,UserID INT NOT NULL
- ,OrderQty INT NOT NULL
- ,Price DECIMAL(8,2) NOT NULL
- ,OrderDate DATETIME NOT NULL
- ,OrderStatus SMALLINT NOT NULL
- CONSTRAINT PK_SalesOrder PRIMARY KEY NONCLUSTERED (OrderID)
- ) ;
- GO
- --Create the columnstore index with a filtered condition
- CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_SalesOrder
- ON dbo.SalesOrder (OrderID, AutoID, UserID, OrderQty, Price, OrderDate, OrderStatus)
- ;
- GO
在这个实例中, 我们创建了 SalesOrder 表, 并且为该表创建了非聚集列存储索引, 当进行 OLAP 查询分析的时候, SQL Server 会直接从该列存储索引中读取数据.
Memory-based Tables with Columnstore Index
SQL Server 2014 版本引入了 In-Memory OLTP, 又或者叫着 Hekaton, 中文称之为内存优化表, 内存优化表完全是 Lock Free,Latch Free 的, 可以最大限度的增加并发和提高响应时间. 而在 SQL Server 2016 中, 如果你的服务器内存足够大的话, 我们完全可以建立基于内存优化表的列存储索引, 这样的表数据会按列存储在内存中, 充分利用两者的优势, 最大程度的提高查询查询效率, 降低数据库响应时间. 创建方法实例如下:
- DROP TABLE IF EXISTS dbo.SalesOrder;
- GO
- CREATE TABLE dbo.SalesOrder
- (
- OrderID BIGINT IDENTITY(1,1) NOT NULL
- ,AutoID INT NOT NULL
- ,UserID INT NOT NULL
- ,OrderQty INT NOT NULL
- ,Price DECIMAL(8,2) NOT NULL
- ,OrderDate DATETIME NOT NULL
- ,OrderStatus SMALLINT NOT NULL
- CONSTRAINT PK_SalesOrder PRIMARY KEY NONCLUSTERED HASH (OrderID) WITH (BUCKET_COUNT = 10000000)
- ) WITH(MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) ;
- GO
- ALTER TABLE dbo.SalesOrder
- ADD INDEX CCSI_SalesOrder CLUSTERED COLUMNSTORE
- ;
- GO
在这个实例中, 我们创建了基于内存的优化表 SalesOrder, 持久化方案为表结构和数据; 然后在这个内存表上建立聚集列存储索引. 当 OLAP 查询分析执行的时候, SQL Server 可以直接从基于内存的列存储索引中获取数据, 大大提高查询分析的能力.
Minimizing impacts of OLTP
考虑到 OLTP 数据源的高并发, 低延迟要求的特性, 在某些非常高并发事务场景中, 我们可以采用以下方法最大限度减少对 OLTP 的影响:
- Filtered NCCI + Clustered B-Tree Index
- Compress Delay
- Offloading OLAP to AlwaysOn Readable Secondary
- Filtered NCCI + Clustered B-Tree Index
带过滤条件的索引在 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 的结构图(图片来自微软培训教程):
实现方法参见以下代码:
- -- create demo table SalesOrder
- DROP TABLE IF EXISTS dbo.SalesOrder;
- GO
- CREATE TABLE dbo.SalesOrder
- (
- OrderID BIGINT IDENTITY(1,1) NOT NULL
- ,AutoID INT NOT NULL
- ,UserID INT NOT NULL
- ,OrderQty INT NOT NULL
- ,Price DECIMAL(8,2) NOT NULL
- ,OrderDate DATETIME NOT NULL
- ,OrderStatus SMALLINT NOT NULL
- CONSTRAINT PK_SalesOrder PRIMARY KEY NONCLUSTERED (OrderID)
- ) ;
- GO
- /*
- - OrderStatus Description
- - 0 => 'Placed'
- - 1 => 'Closed'
- - 2 => 'Paid'
- - 3 => 'Pending'
- - 4 => 'Shipped'
- - 5 => 'Received'
- */
- CREATE CLUSTERED INDEX CI_SalesOrder
- ON dbo.SalesOrder(OrderStatus)
- ;
- GO
- --Create the columnstore index with a filtered condition
- CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_SalesOrder
- ON dbo.SalesOrder (AutoID, Price, OrderQty, orderstatus)
- WHERE orderstatus = 5
- ;
- GO
在这个实例中, 我们创建了 SalesOrder 表, 并在 OrderStatus 字段上建立了 Clustered B-Tree 结构的索引 CI_SalesOrder, 然后再建立了带过滤条件的非聚集列存储索引 NCCI_SalesOrder. 当客人还未收到货物的订单, 会处于前面五中状态, 属于需要经常更新的热数据, SQL Server 查询会根据 Clustered B-Tree 索引 CI_SalesOrder 来查询数据; 客人已经收货的订单, 处于第六种状态, 属于冷数据, SQL Server 查询冷数据会直接从非聚集列存储索引中获取数据. 从而最大限度减少对 OLTP 影响的同时, 提高查询效率.
Compress Delay
如果按照业务逻辑层面很难明确划分出数据的冷热程度, 也就是说很难从过滤条件来逻辑区分数据的冷热. 这种情况下, 我们可以使用延迟压缩 (Compress Delay) 技术从时间层面来区分冷热数据. 比如: 我们定义超过 60 分钟的数据为冷数据, 60 分钟以内的数据为热数据, 那么我们可以在创建列存储索引的时候添加 WITH 选项 COMPRESSION_DELAY = 60 Minutes. 当数据产生超过 60 分钟以后, 数据会被压缩存放到列存储索引中(冷数据),60 分钟以内的数据会驻留在 Delta Store 的 B-Tree 结构中, 这种延迟压缩的技术不但能够达到隔离 OLAP 对 OLTP 作用, 还能最大限度的减少列存储索引碎片的产生.
实现方法参见以下例子:
- -- create demo table SalesOrder
- DROP TABLE IF EXISTS dbo.SalesOrder;
- GO
- CREATE TABLE dbo.SalesOrder
- (
- OrderID BIGINT IDENTITY(1,1) NOT NULL
- ,AutoID INT NOT NULL
- ,UserID INT NOT NULL
- ,OrderQty INT NOT NULL
- ,Price DECIMAL(8,2) NOT NULL
- ,OrderDate DATETIME NOT NULL
- ,OrderStatus SMALLINT NOT NULL
- CONSTRAINT PK_SalesOrder PRIMARY KEY NONCLUSTERED (OrderID)
- ) ;
- GO
- --Create the columnstore index with a filtered condition
- CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_SalesOrder
- ON dbo.SalesOrder (AutoID, Price, OrderQty, orderstatus)
- WITH(COMPRESSION_DELAY = 60 MINUTES)
- ;
- GO
- SELECT name
- ,type_desc
- ,compression_delay
- FROM sys.indexes
- WHERE object_id = object_id('SalesOrder')
- AND name = 'NCCI_SalesOrder'
- ;
检查索引信息截图如下:
Offloading OLAP to AlwaysOn Readable Secondary
另外一种减少 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 的影响.
具体建表代码实现如下:
- -- create demo table SalesOrder
- DROP TABLE IF EXISTS dbo.SalesOrder;
- GO
- CREATE TABLE dbo.SalesOrder
- (
- OrderID BIGINT IDENTITY(1,1) NOT NULL
- ,AutoID INT NOT NULL
- ,UserID INT NOT NULL
- ,OrderQty INT NOT NULL
- ,Price DECIMAL(8,2) NOT NULL
- ,OrderDate DATETIME NOT NULL
- ,OrderStatus SMALLINT NOT NULL
- CONSTRAINT PK_SalesOrder PRIMARY KEY NONCLUSTERED (OrderID)
- ) ;
- GO
- /*
- - OrderStatus Description
- - 0 => 'Placed'
- - 1 => 'Closed'
- - 2 => 'Paid'
- - 3 => 'Pending'
- - 4 => 'Shipped'
- - 5 => 'Received'
- */
- CREATE CLUSTERED INDEX CI_SalesOrder
- ON dbo.SalesOrder(OrderStatus)
- ;
- GO
- --Create the columnstore index with a filtered condition
- CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_SalesOrder
- ON dbo.SalesOrder (AutoID, Price, OrderQty, orderstatus)
- WHERE orderstatus = 5
- ;
- GO
为了能够直观的看到利用 SQL Server 2016 列存储索引实现实时分析的效果, 我虚拟了一个网络汽车销售订单系统, 使用 NodeJs + SQL Server 2016 Columnstore Index + Socket.IO 来实现实时订单销量和销售收入的分析页面.
总结
这篇文章讲解利用 SQL Server 2016 列存储索引技术实现数据实时分析的两种方法, 以解决传统 RDBMS 数据分析的高延迟, 高成本的痛点. 第一种方法是 Hekaton + Clustered Columnstore Index; 第二种方法是 Filtered Nonclustered Columnstore Index + Clustered B-Tree. 本文并以此理论为基础, 展示了一个网络汽车在线销售系统的实时订单分析页面.
来源: http://stor.51cto.com/art/201809/583645.htm