在办公笔记本玩转基于星型模型的自助探索分析

　　概要：

　　在DELL燃7000笔记本电脑上(i5-7200u)，对亿级事实表&维度表的探索式分析，平均响应性能从11.9秒优化到8.9秒，提升程度约25%，这一切归功于Smartbi+Vertica的高性能自助分析解决方案!

　　难点：

　　星型模型又称Star-schema，是一种数据库的建模(组织数据)的方式，它与三范模型3-NF的知名度等高。由于这类模型都是以“事实表”为核心，围绕几个维度表，所以非常形象的被称为“星型”。

　　在没有牺牲空间换时间(OLAP)的数据分析场景下，这样的建模方式非常有利于数据更新，因为维护事实表的增量以及事实表和维度表的数据一致性比较快速，或者说ETL的时间窗口比较小。但其对于查询类型的分析应用，却需要消耗大量的“关联”运算，对CPU来说是比较操作，因此在很多大数据量的数据仓库系统中，往往其查询性能并不好。

　　更具挑战的是，在需要提供自助探索的分析平台上(比如Smartbi的透视分析以及Tableau等)，业务人员无法预料的会动态生成各种查询请求，从技术的角度说就是SQL没有规律，任何字段都可能是where条件、group分组以及计算字段，这就导致索引等传统DBA的手段毫无用武之地。

　　干货

　　关注过Smartbi公众号的同学可能知道，Smartbi在7月份与Vertica进行了战略合作，基于这个新一代列式MPP数据库发布了“高性能自助分析解决方案“，在随后9月的workshop中提供了1个亿级的星型数据模型和22个性能测试案例。

　　我在本人笔记本电脑对V201709版星型模型做了性能测试，平均响应时间为11.9秒，个人感受只能是差强人意。22个测试案例的结果如下，单位为秒：

　　笔记本电脑型号燃7000，配置如下，只不过操作系统为了安装Vertica改成了linux：

　　这个配置和价位是非常亲民的吧，尤其这颗CPU在牙膏厂(Intel)的产品里根本排不上号。

　　言归正传，最近本人研究了一下Smartbi的这个星型数据模型，对其做了2项调整工作，第一是将3个维度表的关联字段改成了整型(当然首先是在维度表增加了车型、姓名、城市的整数编号，其次是在事实表增加这3个字段)，第二是对事实表按年份进行了分区。

　　同样按照22个案例进行了测试，就得到了25%的性能提升，达到8.9秒，结果棒棒的!

　　具体来说，前3个测试案例是对事实表3个字段的分组求和，不涉及任何优化的内容，所以没有什么改变，甚至由于随机性的误差还有一些下降。从第四个开始，2个优化手段开始发挥作用，平均提升更大(30%)。

　　既然此次优化用了2个手段，那么它们各自有多大贡献呢?(原谅本人懒得重新测)

　　将测试案例的三类对比来看，因为”同比计算“和”条件汇总“都用到年份作为条件，我们暂且可以认为它们更能体现按年做分区的优化作用，这里它们分别提升了27%和32%，比普通的全表汇总提升的21%更有效果，就认为有5%-10%的提升吧。

　　另外从这个图可以看到，以前同比计算的平均性能比全表汇总明显要慢，但优化后基本差不多了，都在11秒左右。而按年条件汇总的平均性能从6秒提升到4秒，真的是非常优秀了!

　　总结

　　只有用列式数据库，才可能降低大数据量分析对IO的硬性要求，使得采用笔记本做数据分析成为可能。但能把1亿数据量的星型模型玩转自助分析的，目前也就是Smartbi+Vertica，最后给Smartbi透视分析的同环比计算、分组字段和自由钻取点个赞，以后有空再继续挑战这个任务!

　　写在最后：该优化的模型已经被Smartbi采纳，用于后续的workshop活动!