【干货】星型模型这么设计，性能提升25%！

概要

在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个性能测试案例。

普通笔记本对1亿数据做探索分析，你疯了！

效率不再如蜗牛，如此提升最轻松！

通过在个人笔记本电脑对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透视分析的同环比计算、分组字段和自由钻取点个赞，以后有空再继续挑战这个任务！