|
身为数据分析师，你是否在面对海量数据时，感觉即席查询/透视分析如同老牛拉车，半天出不了结果？当宝贵时间浪费在漫长等待上，高效工作成为奢望。其实，这些都是即席查询和透视分析性能不足惹的祸。
别担心，今天就为你剖析如何有效提升它们的性能，助你突破效率瓶颈。正所谓事出必有因，那致使即席查询和透视分析性能糟糕的根源究竟何在？接下来，让我们一同深入剖析，揭开影响其性能的诸多因素的神秘面纱。
影响报表慢的因素主要是5个，而这5个因素里面，我们主要能解决的是报表制作！
那问题又来了，报表加载又受什么影响？答案就在下图：
从上图来看，即席查询和透视分析都是报表类型，没有过多复杂的报表定义、图片以及渲染的内容，那实际主要影响报表性能的主要就是受影响取数，那这个取数到底又是个何方神圣呢？
把报表加载的黑盒子拆开后，实际就是数据模型走MDX或者SQL到业务数据库取数，再把数据返回。那对于即席、透视来说，无非就是如何减少SQL个数、提高SQL执行效率，根因找到了，那我们来看看应该如何解决。
即席查询和透视分析只包含清单表、交叉表、筛选器组件。因此数据请求较少，只有1个表格的数据请求和若干筛选器产生的数据请求，若开启总行数，也会有总行数的数据请求。
如下图即席查询报表，实际最终执行的SQL是3条，1条为清单表组件的SQL、2条为筛选器组件的SQL。
执行的SQL如下：
从上面示例来看，表组件的SQL是固定的，筛选器也是需要的，所以直接减少SQL条数是比较难的，应该从提高SQL执行效率方面下手。
对Smartbi熟悉的小伙伴应该知道，数据模型分OLAP引擎及SQL引擎，先简单了解一下，什么情况会走SQL引擎和OLAP引擎，具体如下：
1、SQL引擎
（1）即席查询都走SQL引擎
（2）透视分析只要不包含排名、计算成员、命名集、计算度量都支持走SQL引擎；其中计算度量如果表达式仅涉及加(+)、减(-)、乘(*)、除(/)，或者仅使用case when/IIF函数，也支持走SQL引擎(SQL引擎2.0全部库都支持)
2、OLAP引擎
（1）涉及多维计算（如排名、计算度量基于MDX函数表达式，计算成员、命名集、快速计算）
更多详细的说明可以参考wiki文档：SQL引擎V2.0介绍
从上面得知，实际Smartbi的数据模型就是SQL引擎和OLAP引擎，OLAP引擎实际就是走MDX，MDX主要是受解析和计算影响；SQL引擎主要是受执行的SQL影响。具体影响因素如下：
（1）MDX — 解析 — 数据模型不合理
①双向筛选滥用：SQL疯狂JOIN，效率暴跌！
双向筛选的说明可查阅文章：【上篇】99% 的人都忽略了！强大模型的崛起秘密在于设置正确关系
②错误使用事实表关联事实表：可能会出现表join表，复杂关联拖慢查询！
（2）MDX — 解析 — 筛选器不合理
①使用事实表中的维度字段：使用事实表的维度可能会查询量暴增！
（3）MDX — 计算 — OLAP服务器性能
①排序：排序的逻辑是取组件/查询上所有字段的所有数据进行排序，数据量大的情况下，性能有一定影响。
②时间计算：时间计算需要获取维度成员的数据再进行计算，故在维度成员较多情况下时间计算的度量较多时，性能有一定影响。
③不压缩空行：实现该功能需要在内存中进行cross join，导致多出来很多无效的组合，性能有一定影响。
④大模型：通常一个组件会取多个事实表的字段，当关联关系特别复杂时，处理子图的时候需要获取多个子图形成大宽表，性能有一定影响。
（4）SQL质量黑洞
①私有查询SQL质量差，数据库原地爆炸！（私有查询是在数据模型中创建“SQL查询”等）
- 在私有查询中对数据量特别大的表做group by
- 在私有查询中对数据量较大的结果集做order by
- ...
②SQL执行慢，查询时间长！
③业务库查询性能查差
基于影响查询性能的因素，方案推荐如下：
1、合理构建数据模型
2、数据抽取
- 私有查询SQL慢或数据库本身存在性能问题，可考虑数据模型抽取的方案来提升查询效率。
- 私有查询中有参数进行过滤，且参数用在where部分或者join部分，无法在报表层面用筛选器代替的情况，可以先通过ETL将私有查询中的源表都落地到MPP，再基于MPP中的表创建数据模型来提升查询效率。
注：若源表数据量特别大或者用户本身数据库性能也比较好的情况下，抽取方案的性能未必比原库性能高。
3、规范化SQL
4、开启SQL引擎
原理说明：因为走olap引擎的取数逻辑需要有MDX解析、执行多个SQL、OLAP计算结果集等环节，有些可以直接执行SQL的情况直接走SQL引擎会少许多环节，因此默认开启SQL引擎会更好，让系统自动判断走OLAP引擎还是SQL引擎。具体说明可查阅：SQL引擎V2.0介绍
注：V11已默认开启SQL引擎
5、特殊筛选器设为文本输入
- 筛选器若是被联动/传参并且是隐藏的，可将控件类型设为文本输入，减少备选值的请求，从而提高查询效率。
6、开启数据模型缓存
以上就是针对即席查询、透视分析优化性能的一系列有效方式，这些方法涵盖了数据模型构建、数据处理、SQL 使用以及系统设置等多个关键方面，具有很强的实用性和可操作性，大家可以深入学习并应用到实际工作中。
【有奖互动】亲爱的数据达人们，我们诚挚邀请您分享提升即席查询&透视分析性能的技巧，精彩回复将获得惊喜麦豆奖励！ | 
学习
