我们的用户不会那么轻易的放过 "折磨" 我们 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 工程师的机会, 就仅仅围绕月份来做统计, 可能还会加上, 产品类型, 地区等等维度. 这样一来, 就更加麻烦了, 眼珠子恨不得贴到屏幕上, 一分一秒的看着时间的流逝, 可惜了青春啊~
事实上, 大家肯定会出一定的方案来解决这种效率奇慢的查询, 比如:
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+ 等也支持. 所以趁早用起来吧
来源: http://stor.51cto.com/art/201804/570950.htm