Quick BI性能优化解决方案及实践

原标题：QuickBI性能优化解决方案及实践

简介：企业在数据中台初步建设完成以后，怎样让客户直观感受到数据中台的价值企业决策者、各部门管理人员、业务运营人员如何通过统一的窗口，快速看到数据中台提供的数据，并利用这些数据全方位的支持企业发展基于QuickBI构建的企业数据门户，有效的解决了上述问题。

写在前面
QuickBI数据门户在企业数据中台建设中的重要性
企业在数据中台初步建设完成以后，怎样让客户直观感受到数据中台的价值企业决策者、各部门管理人员、业务运营人员如何通过统一的窗口，快速看到数据中台提供的数据，并利用这些数据全方位的支持企业发展
基于QuickBI构建的企业数据门户，有效的解决了上述问题。
QuickBI数据门户是数据中台提供给业务人员使用的门户和窗口，以场景化分析的方式，为企业各类人员和角色，提供统一的可视化服务。作为真正触达用户的可视化工具，QuickBI数据门户在企业数据中台建设中的重要性尤为突出。
为什么要对QuickBI进行优化
企业数据中台建设完成后，数据中台作为企业统一数据的供给方，越来越多的部门和业务人员会成为需求方，希望通过数据中台的数据支持业务。随着需求方越来越多，并发要求越来越高，作为统一入口的QuickBI数据门户的压力随之越来越大。因此，随着数据中台在企业内推广和使用的逐步深入，需要对QuickBI进行全面优化，以满足不断增长的业务需要。
本文旨在说明的问题本篇文章基于实际客户案例中QuickBI性能优化的实践探索，总结提出数据中台建设中的测试方法和性能优化解决方案，抛砖引玉，供其他类似项目参考。
典型的数据中台技术架构
基于阿里云数据中台整体解决方案，对数据中台技术实现进行选型及设计，典型的数据中台技术架构如下图所示，整个技术架构选型包含五个层次：业务数据源存储技术、数据源接入技术、数据中台数据存储与计算技术、数据服务及数据应用技术。

数据存储计算，数据中台中离线数据存储和计算采用MaxCompue离线计算引擎；实时计算部分采用阿里云SeamCompue流式计算技术实现；数据研发与管理采用Daaphi智能数据构建与管理大数据研发平台。
数据服务层，主要分API接口和数据库服务两种方式，一般普通查询使用RDS，多维分析使用ADB，搜索服务使用ElaicSeach，在线接口使用OeSeice服务。
数据应用层，使用阿里云智能报表工具QuickBI实现各种定制数据报表分析需求；以及基于阿里云产品技术体系实现客户个性化数据应用需求。其中基于QuickBI产品的数据中台门户如图中橙色部分所示。
QuickBI压测方法
1、压测目标
QuickBI数据中台门户压测目标主要围绕着两类发布变更和用户体验反馈，提前做好：性能卡点、性能调优工作，满足日常客户报表的极致体验、以及性能特殊诉求。

两个卡点：
1压测，保障上线内容无性能问题以及隐患
2新的报表上线时，需要对新上线报表进行简单压测，避免单一报表导致整个系统性能出现瓶颈。
一个检查点：
当客户直观使用感受数据中台门户报表显示过慢时，对系统整体压测，检查性能瓶颈点进行优化。
从而保障数据中台门户满足客户日常报表可视化性能需求。
2、压测策略
1压测环境
QuickBI数据中台门户通常在客户现场只有正式环境，QuickBI门户页面压测接口全为查询类请求，压测执行不会对线上数据造成污染。当然为了避免影响线上用户，会在用户低峰期如凌晨节点执行压测。大部分项目数据门户环境如下：

2压测实施
具体实施压测时，主要流程如下：

1获取客户需求，如客户需求的可能是要支持100个并发，返回时间不超过3、日常峰值用户多少PV等。
2根据客户需求结合线上PV访问量预估，计算出经验qp和极限qp。
3前期准备，需要跟客户沟通压测时间点以及压测方案，同时压测期间协调产品方跟进，观察产品在压测时是否触发异常。
4压测工具准备
5压测数据准备
QuickBI门户涉及多个页面，一个页面包括多个接口请求，如果通过手工抓接口录API入参的方式工作量极大，而且接口入参数据时效不高、容易出错。因此需要一种实时页面录制请求的方式实现压测数据快速准备。
3、压测方式
通常数据中台QuickBI门户的性能瓶颈是在提供数据服务的数据库，因此在进行压测时，我们主要通过区分不同数据源类型的报表页面，进行压测，如下表所示:
下表所示:

1摸高测试
以最初始的qp开始施加压力，观察系统个性指标和业务指标，稳定没问题后就往上调高qp并发数，依次循环，最后达到目标qp甚至超过目标qp，稳定一段时间，记录目标qp下的系统各项关键指标及业务指标；
2恒定压力测试
一般在小于目标qp稳定压力，持续试压至少2mi，观察系统表现，没问题后，调整到目标qp，持续施压2mi或者更长，观察系统表现，记录系统各项关键指标及业务成功率。
3混合压测
指的是多场景同时压测，这种情况往往需要充分评估好多个场景总的流量对模块甚至产品的影响。
各模块混合压测时，需要评估好各自qp对模块及对下游模块可能造成的影响。
某客户QuickBI性能优化实践
1、QuickBI数据门户压测与调优
在实际客户案例中，为了从根本上解决QuickBI数据门户性能问题，采用如下方案进行整体的优化与压测验证：

首先优化分为任务优化和产品优化：
任务优化
1第一轮压测：首先对QuickBI进行一轮压测，记录当前系统性能数据。
2查找优化对象：ADB产品根据op10耗时SQL，针对性的探查性能瓶颈，QuickBI产品侧通过查找元仓，找到自定义SQL数据集，并筛选非传参且耗时高的数据集。
3优化：
ADB以优化表DDL为主和规格评估为主，通过在ADB库中查询INFORMATION_SCHEMA.PARTITIONS，获得各表组分区如下：

为了使数据分布均匀，避免长尾问题，根据产品建议，通过重命名原表->创建新表->数据回写的方式，将ADB中非128分区的表进行DDL改造，分区调整为128分区。
QuickBI通过将自定义SQL数据集固化成结果表的方式，降低使用此类数据集时每次查询复杂SQL的性能消耗。通过元仓查询到的此类数据集如下，其中有两个数据集不是传参类型自定义SQL数据集，并且SQL非常复杂，对ADB系统性能影响非常大，针对这两个数据集进行优化调整，将处理逻辑下沉在Daaphi的ad层，并将结果表同步至ADB，供QuickBI的报表直接访问。

4第二轮压测：对QuickBI各场景页面进行第二轮压测，验证优化效果。
产品优化：
1QuickBI产品：后续升级为3.6.1版本后，支持数据缓存功能，可以将各场景默认展示页的数据进行缓存，降低对后端数据库的性能消耗。
2ADB：优化完成后，可视情况进行限流，从而在资源紧张情况下保障绝大部分用户的正常使用。后续从2.0版本升级为3.0版本，写入效率预计提升50%，读取效率预计提升40%，并且ADB3.0版本支持存储计算分离。
另外在QuickBI独立部署正式上线期间，GTS侧进行现场重保，各相关产品侧在远程进行重保，进而保障客户数据门户环境平稳运行。
与此同时，因ADB资源不足，对ADB规格进行评估，联合商务侧临时使用代金券，将ADB资源由进行临时扩容，优先保障客户上线，根据上线后实际客户使用情况决定是否保持扩容规格。
系统调优的目的在于满足客户对数据中台数据门户性能的需求，那么对数据门户的压测必不可少，经过分析，20个qp即可满足当前客户的使用需求，在实际进行压测是，针对数据门户场景分别进行压测，压测方式如下：

2、事后监控
在扩容和调优完成后，我们还需要对系统的使用情况进行监控，监控指标如下：

通过监控指标项发现，扩容和优化后：
1每日1：30~8：30左右，数据中台数据写入ADB库，CPU等资源会占用较高，使用率可以达到90%+，但因是非业务使用时间，所以对业务使用无影响。
2工作时间CPU使用平均40%左右
业务人员在日间使用期间，系统当前配置在理论上可以支持100用户并发20qp的使用，而且客户侧在短期内会进行数据中台门户系统推广，因此保留当前系统配置，应对后续推广的用户涌入。
QuickBI性能优化建议
1、QuickBI开发规范
总结上述性能测试和性能优化的结果，开发人员在使用QuickBI进行可视化开发的过程中，应遵循一定的开发规范，以保证在前期就规避一些性能风险，提升整体平台的性能。
自定义SQL建模

使用QuickBI进行数据可视化开发，其中的大部分SQL都是QuickBI的SQL引擎自动生成的，此处不需要开发人员关注。而在QuickBI专业版中支持的即席分析SQL功能如上图中，允许开发人员通过自定义的SQL创建数据集，此处需要开发人员遵循以下原则进行SQL开发：
(1只有必须使用即席查询SQL中的参数传递功能，以满足特殊场景需要的时候，才使用即席分析SQL的方式创建数据集。其他场景下，都要求将此数据查询的过程前置到数据计算过程里面，即使用Daaphi等工具将所需加工的数据提前计算好，形成专门的数据表，QuickBI直接使用该数据结果，而不是在QuickBI中，创建数据集的时候进行比较复杂的SQL数据加工。
(2自定义SQL，不建议使用超过3个以上的uio类型的操作。不建议使用超过5个字段以上的goupby操作。不满足的情况，均建议采用上面1中的方式，前置到数据计算环境，将数据处理好，再在QuickBI中使用数据。
(3SQL的编写规范，需要严格遵循数据中台模型设计&数据开发规范的要求编写。
4可通过简单的性能测试衡量在即席分析SQL中编写SQL脚本是否可行，在该过程编写的SQL，页面执行后，返回结果的时间建议不要超过1的时间，否则相应的页面查询很可能无法满足客户对相应时间的要求。
数据集表关联
QuickBI在数据集管理页面，支持通过数据表的关联建立数据集

此处也比较可能产生性能问题。开发过程中需循序以下规范：
1应尽可能减少使用数据表关联的功能，如果能够在Daaphi等数据加工工具提前将关联加工好，则要求此关联过程前置到计算层。
2如前面的1条无法满足，则需要尽可能的使用相同数据源的数据进行关联。
3如果前面12都无法满足，应尽量使用RDS或ADB数据库中的数据集进行关联。尽量避免使用ODPS的数据源进行关联访问。
4尽量避免两个表全关联，或者笛卡尔积的方式进行关联，这样可能造成数据集数据量极大膨胀，产生性能问题。
5可通过简单的性能测试衡量在数据集中进行数据表关联操作是否可行，在数据集中进行关联，页面刷新预览数据时，返回结果的时间建议不要超过1的时间，否则相应的页面查询很可能无法满足客户对相应时间的要求。
数据集缓存
QuickBI在数据集页面，支持对数据集进行缓存配置，如下图：

QuickBI的3.6.1版本以后支持对ODPS和ADB数据源的数据集进行缓存配置，技术上会将开启了缓存的数据集数据缓存到QuickBI安装部署时配置的Redi上面，以减轻对来源数据库的频繁访问，加速查询性能。使用该功能，需要注意：
1Redi数据缓存按小时进行更新，因此开启了缓存的数据集数据不会实时与数据源进行同步，如源数据发生变化，查询结果不会实时响应，只会根据Redi的更新才会识别到最新的数据变化。
2Redi的空间有限具体示安装部署时的配置而定，因此也不建议所有的数据集都开放该缓存功能，而应选择并发查询度较高，性能较差的数据集，有重点的开放该功能。
2、AalyicDBoMySQL表设计规范
ADB数据模型：
ADB是MPP分布式架构，其数据模型如下：

用户在设计表的时候，需要重点关注以下四点：
分布键一级分区键
分布键也成为一级分区键根据分布键的Hah值，将表数据打散到各个数据分片。
所以，在选择分布键时，一定要尽量确保数据分布均匀，避免数据倾斜。这是重中之重！
同时，尽量选择多表joi时的关联键，避免数据hule。
针对一些数据量少且很少更新的码表，可以选择创建为维度表，来避免数据hule，提升性能。
分区键二级分区键
再每一个一级分区下，再根据LiValue，将数据分配多个分块。
分区键通常基于日期，并根据二级分区数的定义，按照分区键值的大小进行排序，保留最大的N个二级分区。这样就赋予数据生命周期的特性。
主键PimayKey
通过主键进行记录的唯一性判断，且分布键和分区键必须包含在主键中。
为了确保主键的性能，通常要选择数值型的列作为主键，并严格控制主键的个数，通常控制在4个列以内。
聚集列ClueedKey
通过将相同的数据物理排序在一起，可以达到降低IO并提升查询性能的效果。通常聚集列选择那些有一定重复数据量、且常常作为查询过滤条件的列，例如商品类型、人员部门等。
3、AalyicDBoMySQL开发规范
AalyicDBoMySQL拥有强大的自研存储、MPP计算引擎和先进的优化器，通常客户无需过于关注SQL是否规范。但是，以下的基本原则可以充分利用ADB的特点，已达到最佳的性能：
尽量避免列上嵌套函数
如下，如果在列上嵌套函数，会导致该列上的索引失效，从而需要扫描全表数据，增加系统资源消耗的同时还会影响查询的性能。

因此，我们在编写SQL时要尽量避免在列上嵌套函数，上面的SQL可以做如下修改：

尽量确保joi时基于分布键：
如果两表Joi是不是基于分布键，则需要进行大量的数据hule，如下：
例如：
表cuome的分布键是UeId
表ode的分布键是OdeId
SQL:
Selec*omcuomecjoiodeooc.ueId=o.ueID
在SQL执行时就需要对ode表hule数据，这样会增加系统的资源消耗，包括内存、网络、CPU等，查询响应时间也会增加

因此，我们需要修改Ode表的分布键为UeID，这样上面的SQL在执行时会在单个ECU本地内完成计算，从而提升性能，如下：

尽量多的添加过滤条件
默认情况下，客户无需关心哪些列需要创建索引，ADB会在所有的列上创建索引。但是如果过滤条件的过滤性不佳，甚至是缺失，则无法发挥ADB强大的自研索引的性能，需要进行全量数据的扫描。因此，我们需要根据业务和数据的特性，尽可能多的添加过滤条件。
作者:数据中台君
本文为阿里云原创内容，未经允许不得转载