性能优化专题 - 性能优化 - 《有数BI用户手册》

性能优化

更新时间: 2022-12-28 15:06:26

1. 概述
2. 影响性能的因素
3. BI平台侧优化
4. 数据底表侧优化

1. 概述

性能，是指在本产品中工作的速度，它可以表示数据分析的速度，例如远程访问数据库并加载、转换数据时，可以感受到不同的数据库可能速度不一样；也可以表示BI平台中访问的组件或报告的加载速度。

影响性能的原因是多样的，为了提高性能，我们需要定位到具体影响因素，针对性地进行优化以便更好地调整报告，在不影响报告结果的同时提高性能。

2. 影响性能的因素

您在查看报告时，每一个图表背后的数据其实都是系统下发SQL去底层数据库查询得到的，大致可分为以下几个阶段：

首先是查询准备和组件推导时间，即BI平台需要知道查询什么样的数据、生成什么样的图表；
然后是在数据库执行SQL获取数据的过程（该过程的耗时很大程度上由数据源的性能和查询复杂度决定）；
等SQL执行完，BI平台拿到数据，前端渲染绘制图表，进行数据的可视化展现。

以上不同阶段，无论哪个阶段“慢”都会导致最终报告加载慢，当然我们也可以通过一些针对性的操作来避免某些过程的“慢”。本文目的就是提供这类平台侧及数据侧的最优操作指南，帮助大家制作一份“性能更优”的报告。

3. BI平台侧优化

3.1 数据模型

同一个模型中关联的表越多，生成的查询SQL越复杂，执行速度慢，导致报告性能差，建议单个模型中关联的表不超过5个

3.2 报告制作

（1）单个报告页面筛选器和图表数过多

单个报告页的组件数越多，组件加载时间也就越长，建议单个报告页面筛选器和图表总数不超过100个。过多的图表可通过添加多个报告页进行展示，不同页面展示不同数据也更助于分析思路的沉淀哦～

（2）报告中存在未使用的模型，也会影响报告页面的加载，建议将无用的模型删除（数据医生的诊断功能支持一键删除）。

3.3 其他小技巧

转换字段类型为日期/日期时间类型时，可选择用原始类型进行比较

将“字符串”类型的字段转换为“日期、日期时间”类型时，使用原始类型（即字符串类型）进行比较则不需要在SQL中进行字段类型转换，可提高查询性能。

例如：有「销售日期」字段，该字段的原始类型为：字符串，在模型/报告中将该字段转换为：日期。假如对「销售日期」字段进行筛选，筛选「销售日期」在2021年11月11日之后的数据，则SQL为：

TO_DATE(CAST('t1'.'销售日期' AS TIMESTAMP))>=CAST('2021-11-11' AS TIMESTAMP)

若「销售日期」字段仍然为“字符串”类型，这个时候筛选「销售日期」在2021年11月11日之后的数据，则SQL为：

't1'.'销售日期' >='2021-11-11'

可见，使用原始类型（即字符串类型）进行筛选时，SQL语法简单很多，查询性能也因此有所提升。因此设置日期筛选时，建议选择用原始类型进行比较。具体步骤如下：

（1）转换数据类型：

（2）筛选优化设置的弹窗中选择“以原始类型进行筛选比较”：

筛选器相关的更多优化可见：筛选器优化

4. 数据底表侧优化

如果您购买的是本产品私有部署版本，可参考此小节。

4.1 分区筛选

若您使用的是有数大数据平台impala数据源，则可以针对「分区筛选」进行优化操作。

使用的数据表中含有分区时，添加分区筛选则默认选中最近分区，只扫描指定分区的数据，不再扫描全表，极大提高查询性能。具体操作步骤如下：

（1）数据连接开启数仓信息同步：

（2）模型侧加上分区筛选器：

（3）分区筛选器作用于报告时，模型中没有隐藏的分区字段，生成的图表默认带上该分区字段的分区筛选器并默认选择最近分区（若模型隐藏分区字段，则不会在报告上加筛选器）：

4.2 使用Parquet存储格式

什么是Parquet存储格式？

Parquet为高性能列式存储格式，由于OLAP查询的特点，列式存储可以提升其查询性能。同时Parquet格式支持压缩，可以降低数据存储量，并且在查询时减少磁盘IO。因此使用Parquet存储可以提升其查询性能，从而提高报告性能。

具体操作

（1）在有数大数据平台数据管理中，新建离线表时，存储格式选择PARQUET

（2）使用SQL，在创建表时，使用STORED AS PARQUET

CREATE TABLE dm_some_hive_table (
  some_field STRING COMMENT 'a field',
  ...
)
STORED AS parquet #存储使用parquet

4.3 使用分区表

什么是分区表？

表分区是一种数据组织方案，在此方案中，表数据根据一个或多个表列中的值划分到多个称为“数据分区” 的存储对象中。每个数据分区都是单独存储的。分区表在查询时结合分区筛选，对于大数据量查询有较大性能提升，建议总数据量百万以上的表，尽量都用分区表。

具体操作

（1）在有数大数据平台数据管理新建离线表时，开启分区表选项，并设定分区字段

（2）使用SQL，在创建表时，使用PARTITIONED BY**

CREATE TABLE dm_some_hive_table (
  some_field STRING COMMENT 'a field',
  ...
)
PARTITIONED BY (`ds` string COMMENT '按照天进行分区')
STORED AS parquet

4.4 减少（分区）小文件数量

什么是“小文件？

Hive/Spark等运行SQL任务，在将运算好的数据写回HDFS时，有时候会产生大量小文件，相关信息可以在表详情查看：

如果是分区表，则可以在“分区信息”看到具体分区的文件数：

大量的小文件在查询时，增加元数据同步压力，也增加HDFS NameNode访问压力，尤其在并发查询稍多时，查询性能会直线下降且十分影响整个查询引擎集群的查询处理。

如何减少？

目前通常数据底表大多是离线表（T+1时效），每天使用猛犸任务调度执行。因此可以在任务末尾使用distribute by cast(rand() * 10 as int);语句，可以降低分区文件数量，对文件数过多的底表的查询性能有较大优化。示例：

insert overwrite
  table dm.dm_youdata_online_domain_kpi_info_p 
  partition (ds = '${azkaban.flow.1.days.ago}') 
select
  m.domain_id as id,
  m.name as name
from dim.dim_online_entity_domain_content
left join (
    select
      domain_id,
      visit_date
    from
      dw.dw_online_entity_domain_visit_record
    where
      ds = '${azkaban.flow.1.days.ago}'
) n on m.domain_id = n.domain_id 
group by 
    m.domain_id,
    m.name
  distribute by cast(rand() * 10 as int); ###

处理建议

代码片段中的10可以按需调整，一般如果单分区中数据量较大（相应存储量大）时，该数值可以增大，但不建议超过100；
在有数中使用的表的生成任务中加一下即可；
如果表本身文件数不多且无分区（例如维表）或历史分区中的文件数较少，则可以不用处理。

4.5 表统计信息

在有数大数据平台自助分析模块，对非分区表执行“compute stats”，分区表执行“compute incremental stats后面接分区”来添加表的统计信息。