亿级数据分析性能杀手锏 - 列式存储

存储 存储软件
我们的用户不会那么轻易的放过“折磨”我们 IT 工程师的机会,就仅仅围绕月份来做统计,可能还会加上,产品类型,地区等等维度。这样一来,就更加麻烦了,眼珠子恨不得贴到屏幕上,一分一秒的看着时间的流逝,可惜了青春啊~

看似非常简单的一条 SQL,如下图,我们求解的无非就是月总销售量

  • SELECT  OrderMonth, SUM( OrderAmount) AS Amount
  • FROM FctOrderSales WITH(NOLOCK)
  • WHERE OrderMonth BETWEEN '2017-01-01' AND '2018-12-31'
  • GROUP BY OrderMonth 

一旦 FctOrderSales 的数据量增长,恐怕不等个半把个小时,是出不来的。

再加上,我们的用户不会那么轻易的放过“折磨”我们 IT 工程师的机会,就仅仅围绕月份来做统计,可能还会加上,产品类型,地区等等维度。这样一来,就更加麻烦了,眼珠子恨不得贴到屏幕上,一分一秒的看着时间的流逝,可惜了青春啊~

[[226442]]

事实上,大家肯定会出一定的方案来解决这种效率奇慢的查询,比如:

1 加个索引

2 加个分区

3 ETL 先算好聚合数据

4 。。。

解决方案总是有的。在这里我们来看看另一种玩法,列式存储。

上图中,表格中的数据,就是典型的row-based data page 行式存储数据页。一行相邻一行的存储在一个数据页上,一列肩并着一列存储在一行上。而 Columnar Storage Layout 就是列式存储, 每一个列的数据都被存在一个数据文件中,比如date_key 按照顺序存储在 date_key file 中,Product_sk 也一样,按照二维表中的顺序,存储在 product_sk 文件中。每个列式存储文件中,对应行号上存储的数据,都是表结构中相应行号的列数据。即要获取原表结构中,第20行的数据,那么要分别从这些列式存储的文件中,取得各自的第20行数据,集合起来!

考虑以下场景,比如我们就要分析 2013年,每个月,某两个品种,69,31 的销量:

SELECT 

    getMonth(date_Key) AS Month

  • getProductName(product_sk) AS Product
  •  SUM(quantity) AS Quantity

FROM FctSalesOrdinary

GROUP BY getMonth(date_Key), getProductName(product_sk)

在这里,让我们做出这些假设:

1 date_key是存在一个数据文件里面的,product_sk 是存在另一个数据文件里面的。2013年整年的销售数据有 200W 条,每个文件的段能存储100W,(按照 SQL Server 的存储容量来计算),这样就总共抓取 2个段, 按照每次读取一个segment的机制,连续读取 2个连续段就只有一次读取,因此磁头读取不需要再次寻址。

2 假设2013年***条数据在源数据表中,是第100万条数据,2013年***一条数据是第299万条数据。

按照上面的图解释,我们读取2013年数据的时候,读取product_sk的字段,即读取 第100万条数据到 299万条数据,然后根据product_sk做限制。比起row-based data page(行式存储数据页),其他字段store,promotion,customer字段统统舍去,少读了很多的无效数据。

列式存储还带有一个压缩的选项。因为每个列式存储文件都是存储的同质数据,那么对这些同质数据进行压缩,会有很好的压缩效率。压缩带来的好处是,减少了吞吐量,使得内存容下了更多的数据,并且还可以有效使用 CPU L1 Cache, 这种技术称为 vectorized processing.

 *参考The Design and Implementation of Modern Column-Oriented Database systems.

为什么列式存储更适合分析性数据仓库:

1. 分析决定了一定是读取大范围连续属性的数据。不是随机读,而是顺序读,速度快很多

2. 请求基本上是采取多个维度同时读的方式,而不会读取所有的列。这样很多row-based data page (行式存储数据页)都会舍去不必要的相邻列的数据。

3. 列式存储的机制:一个数据文件中单独存储的是整个列的数据,按照segment来分段,一次至少读取一个段。一个段中可以存储大量的同质数据。

目前支持列式存储的数据有:

  • Greenplum
  • PostgreSQL
  • MariaDB
  • Microsoft Azure SQL Data Warehouse
  • Microsoft SQL Server 2012 及以上
  • BIRT Analytics ColumnarDB
  • IBM Db2
  • Oracle Database/Exadata
  • SAP HANA
  • TeraData
  • Apache HBase
  • ClickHouse
  • Apache Parquet 

以上是我们常用的数据库品牌,还有一些小众数据库,比如 MonetDB,kdb+ 等也支持。所以趁早用起来吧

责任编辑:武晓燕 来源: 有关SQL
相关推荐

2021-06-17 13:10:47

数据分析自助式分析数据

2019-04-22 08:07:31

MySQL数据库索引

2013-08-13 09:07:20

大数据

2022-02-25 08:55:19

BitMapRedis面试题

2010-01-15 09:43:49

Windows 7杀手

2022-02-10 09:04:18

RediSDS数据结构

2015-06-15 14:48:21

苹果iOS9软实力

2010-03-23 17:06:01

2011-06-27 22:08:42

台式机评测

2019-08-29 10:46:42

2011-07-04 10:00:02

虚拟化

2014-12-01 15:20:36

微信点评商家

2015-08-11 14:38:34

2021-03-18 10:31:27

Redis宕机日志

2021-02-22 09:01:13

Redis宕机日志

2024-02-19 00:06:06

数据分析系统Doris

2009-02-19 11:20:45

TIBCOSOA系统集成

2009-09-03 14:52:12

RHEL5.4发布KVM

2010-01-18 17:02:06

2011-06-29 10:08:24

服务器虚拟化解决方案
点赞
收藏

51CTO技术栈公众号