前言：本文是Hologres阿里妈妈用户根据实际业务总结的经验贴，希望通过本文内容，能帮助大家更好的理解在Hologres中如何做性能调优，从而帮助业务实现更优的性能。

作者：安嘉琪阿里妈妈及商家营销研发

写在最前

1、本文概述

本人是阿里妈妈广告产品技术事业部业务数据的开发同学，负责的业务能力之一就是阿里妈妈大规模广告库存管理系统。业务于2019年确认使用Hologres，在保证稳定性和准确性的前提下，配合版本升级逐步优化计算性能。

2021年由于广告售卖策略变化，大促请求量为历史的至少3倍以上。为了保证运营同学的大促下单体验，系统急需性能优化。经过一系列优化后，最终在双11前完成计算性能5-10倍的提升。

本文旨在以广告库存管理系统为例，根据自己的一些经验，讲述数据表建立、数据表查询、数据表运维三个阶段的分析与优化方法（红色文字部分为重点内容）帮助大家在自己的业务中灵活自主的完成Hologres计算的优化。

本文主要为Hologres分析型查询，即Hologres内表SQL的优化方案，不涉及点写、点查与bulkload导入和外存表的相关内容

2、阿里妈妈广告库存管理系统简介

互联网广告业务初始阶段，拥有流量的媒体与广告资源的代理商是市场的主要参与者。线下广告的业务逻辑被照搬到线上，由广告代理公司和媒体签订协议，确保某些广告位在某时间段被指定广告商占有，同时广告商按整体合同支付广告费用，也就是早期的CPT合约广告。

为了提升广告投放效果，需要对广告进行更精细化的售卖，于是合约式广告重点发展为CPM计费的展示量合约广告，俗称"CPM广告"。展示量合约指的是约定某种受众条件下的展示量，然后按照事先约定好的单位展示量价格来结算。这种合约又被称为担保式投送即GD。实际执行中，在未能完成合约中的投放量时，可能要求媒体承担一定的赔偿。

广告库存管理系统是阿里妈妈合约保量广告能力实现的重要系统之一，主要负责广告库存的分配。在展示量合约广告中，库存中存放的是预估的未来N天的广告的展示量信息。广告主通过询量(库存余量查询)与锁量(库存余量锁定)来确认并购买所需的广告量完成下单，后续投放引擎精准的按时按量完成广告投放。

由于广告库存数据量庞大且定向条件众多，导致单次计算量较大，在亿到百亿级别。由于下单请求量极大，尤其是大促期间出现井喷，当天可达到数十万，所以单次请求耗时需要稳定在秒级别，才能保证用户良好的下单体验。

询量计算建模分为两种：细粒度占量计算建模和竞争系数计算建模

细粒度占量计算：计算逻辑相对简单，适用于定向少，单次计算量在千万到亿级别，不支持库存腾挪的场景。
竞争系数计算：定向条件众多，单次计算量在十亿级别，支持库存腾挪，可配合二部图求解最大化库存。

3、Hologres的简介

Hologres是阿里巴巴自主研发的一站式实时数仓产品，支持高并发和低延时地分析处理PB级数据，并首次提出HSAP（Hybrid Serving/Analytical Processing）服务分析一体的理念。在实时数据的高频点写、点查时，bulkload导入和分析性查询依然能够保证其稳定性与准确性。具体细节可详见官方文档与本人的原理解析Ata

移步>>

一、Hologres连接工具

要使用Hologres必然要有工具，这里直接分场景做相关推荐，操作可参考官方文档，本文仅罗列业务中常用的工具，其余工具本文不再赘述。

1.1 HoloWeb

一个优于HoloStudio的Hologres开发工具，并且支持用户管理、SQL分析和链接查询等多个功能，文章后续会频繁使用。适合数据查询、SQL分析、安全运维等一系列操作使用。

1.2 HoloClient

Hologres更新到0.10版本后主推的一个client，其中封装了JDBC和点查点写的接口。API自动封装了同步和异步IO的方法以供使用。适合Blink/Flink等数据点查点写异步IO的场景，虽然有JDBC的封装，但大量数据SQL操作还是建议用JDBC完成，移步Holo Client使用文档

1.3 JDBC

与传统数据库差距不大，通过JDBC链接执行SQL访问Hologres，并且支持Mybatis、Jpa等多种框架的访问。适合大量SQL进行数据分析的场景。当前版本只支持单个SQL的事务机制保障。

二、数据表的构建优化

Hologres的快速查询与数据表构建方式强相关，使用的合理表设计不仅能够提高性能，并且可以节约计算和存储的成本。广告库存管理表非常复杂，定向和计算逻辑较多，数据量在亿级别。

为了方便理解，结合业务逻辑将库存管理的计算流程进行抽象化简，用最基础最通用的方式，举例完成全程的优化细节讲解。细粒度占量方式和单次总量余量库存相关计算如下图所示：

调优1.png

库存预估表：预估未来每天每个资源位广告请求量的明细表，天级别全表更新
库存占量表：广告主下单占用的库存的明细表。每次在BP的下单操作会将锁量数据写入
库存余量计算：根据过滤条件，筛选库存预估明细中未被占用的总量

2.1 表组的构建

表组又称Table Group，简称TG。表组是Hologres重要理念之一，主要用于指定单次计算的执行并行度，即shard count(分片的数量)。Hologres的shard本质上完成了数据“分库分表”的效果，所有的shard并行执行。强相关的表在建立时指定相同的表组，数据在分布时会根据设置的字段值构建在一起，在执行计算时直接Local Join来加速计算

多个复杂业务同时使用一个Hologres数据库时可以使用不同TG做执行能力的区分，合理使用集群资源

Table Group表组、Table Group Shard分片与Local Join可参考Table Group最佳实践

询量业务在同一数据库中有多个业务线，如品牌特秀、品牌优酷等。计算量与性能要求不同，需要表组隔离

-- 创建 64个Shard的TGCALL HG_CREATE_TABLE_GROUP ('inquiry_system_texiu_tg',64);-- 创建 128个Shard的TGCALL HG_CREATE_TABLE_GROUP ('inquiry_system_youku_tg',128);

注: 集群扩容可配合调整shard count，调整shard count需要重建表组，重建关联表

2.2 数据表的建立

由上图所示，构建两张表：库存预估表和库存占量表。两张表通常会绑定使用，为了高效的计算，需要在数据表建立时指定相关参数才能发挥Hologres全部性能。其他业务也可根据自己的场景做类比，如广告单元和广告计划通过campaign_id关联、请求信息和点击信息通过session_id关联等等。分析与优化过程如下：

2.2.1 表存储类型选取

主要分为行存和列存两种存储类型(行列混合存在最新版本支持，在此不进行讨论)：

行存：主要用于点查整行的场景，高QPS的依照主键的查询。Blink/Flink的维表和结果表必须使用行存
列存：主要用于多列的分析型复杂关联查询，如Join、GROUP BY等

这里选择列存为最匹配当前的使用场景（OLAP）

2.2.2 表索引构建

根据两张表的字段和使用场景，进行以下分析：

Join条件：请求id、日期
过滤条件：资源位id、日期、优先级
聚合条件：日期
排序条件：优先级、日期

Hologres建表通用属性参数：

orientation：指定行存还是列存，这里我们选择列存(column)
distribution_key：分布列，与业务强耦合且优化所需的重要参数，正确的使用该属性计算性能会有数量级的提升。当前的Join条件一定出现在请求id和日期上，这两个字段作为分布列，让路由后的数据可以做Local Join。设置错误将会导致大量不合理的数据shuffle以及数据处理热点
clustering_key：聚簇索引。该索引主要是将shard中数据块进行排序，进行快速查找，适用于range的过滤，即适合大小判断的过滤条件。由上图所知，日期、优先级字段用于大小比较，使用聚簇索引。注意：当多个字段同时设置聚簇索引时，将会服从最左原则，将会部分字段排序失效
bitmap_columns：位图索引。该索引主要是将可枚举的取值不多的列做bitmap编码进行快速定位查询，只有列存表能使用该索引。日期、优先级、资源位id均会用于单值查询，使用bitmap索引
dictionary_encoding_columns：字典编码，一般用于字符串字段中，在0.9前的版本会自动关联
table_group：指定表所属的table group，如果不指定则放入默认table group。

2.2.3 建表SQL的执行

最终建表语句如下所示：

--库存预估表(简化)BEGIN;CREATETABLE public.predict_base_table(    session_id textNOTNULL,    adzone_id bigintNOTNULL,    pv doubleprecision,    target_date bigintNOTNULL);CALL set_table_property('public.predict_base_table','bitmap_columns','adzone_id,target_date');CALL set_table_property('public.predict_base_table','clustering_key','target_date:asc');CALL set_table_property('public.predict_base_table','table_group','inquiry_system_texiu_tg');CALL set_table_property('public.predict_base_table','distribution_key','session_id,target_date');CALL set_table_property('public.predict_base_table','orientation','column');COMMIT;--库存占量表(简化)BEGIN;CREATETABLE public.locked_pv_table(    session_id textNOTNULL,    target_date bigintNOTNULL,    business_id bigintNOTNULL,    product_priority bigintNOTNULL);CALL set_table_property('public.locked_pv_table','bitmap_columns','target_date,product_priority');CALL set_table_property('public.locked_pv_table','clustering_key','target_date:asc,product_priority:asc');CALL set_table_property('public.locked_pv_table','table_group','inquiry_system_texiu_tg');CALL set_table_property('public.locked_pv_table','distribution_key','session_id,target_date');CALL set_table_property('public.locked_pv_table','orientation','column');COMMIT;

更加详细的索引创建和说明见官方文档

三、数据的查询优化

Hologres查询的优化需要先大概了解SQL的执行流程，如下图所示，进行大概的描述：

Client提交SQL到FE。FE是SQL的入口，主要负责解析与任务生成
FE内部的执行优化器将SQL翻译成执行计划，并提交给协调器
协调器从管理器中获取资源信息，根据执行计划产出执行分片，下发给不同Worker节点节点完成操作

所以SQL执行分为三个阶段：①生成执行计划(Optimization)，②协调资源启动任务(Start Query)，③任务执行(Get Result)

执行计划是任务执行的细节，是分析和优化的主要入手点，读懂并且预判执行计划是进行SQL优化的基础。以下述SQL为例进行分析：

SELECT  adzone_id
,target_date
,sum(pv)AS all_pv_num
,sum(            case(lock_pv_session ISNOTNULL) WHEN TRUE THEN pv ELSE 0 END
)AS locked_pv_num
FROM(SELECT  base_pv.adzone_id,base_pv.target_date,pv * pv_ratio AS pv
,ecpm2 AS cost
,locked_pv.session_idAS lock_pv_session
,locked_pv.member_idAS member_id
FROM    predict_base_table base_pv
            LEFT JOIN locked_pv_table locked_pv
ON      base_pv.session_id= locked_pv.session_idAND     base_pv.target_date= locked_pv.target_dateWHERE   base_pv.adzone_idIN(123123)AND     base_pv.target_dateIN(20211227,20211229,20211228,20211230,20211231)) aa
GROUPBY target_date;

3.1 执行性能分析

查询优化首先要对执行的SQL进行详细的分析，点对点完成各个步骤的问题的解决。分析方法如下所示。

SQL分析方法分为两种：HoloWeb分析SQL，直接查看执行计划

分析的内容主要包含：内存占用、耗时分布、执行计划合理性等

3.1.1 HoloWeb诊断分析

在Hologres更新到0.10版本后，HoloWeb提供了强大的SQL分析功能，可以让刚入门的同学快速上手完成SQL的基本分析。

查看历史慢SQL与耗时

在HoloWeb的诊断与优化中查看历史慢SQL，可根据关键字查询对应的SQL。在右侧耗时分布可查看3阶段耗时。点击详情可查看具体耗时信息

调优3.png

黄色部分：执行计划生成耗时(Optimization Cost)
橙色部分：任务启动耗时(Start Query Cost)
黑色部分：执行获取结果信息耗时(Get Result Cost)

SQL执行计划分析

找到需要优化的SQL，将其写在编辑器中，选择"运行分析'或'执行计划'

运行分析查看（explain analyze）：会真实执行该SQL，并返回执行过程，产出更准确的分析结果
执行计划查看（explain）：不执行SQL，只查看生成执行计划，产出信息为预估结果

调优4.png

执行结果中可按照内存、耗时查看不同面板，并且查看每个节点的数据量(行数)的传递

也可以直接查看执行计划面板，查看文字版本

调优7.png

3.1.2 手动查看执行计划

虽然HoloWeb可视化了执行计划，并做了非常友好的节点流程图，适合初步接触Hologres同学使用，但详细参数还是需要手动执行SQL来查看。

手动查看执行计划方式并不复杂，只需要在执行的SQL前面增加关键字：explain 或explain analyse

explain SQL : 与HoloWeb中的执行计划结果相同，该语句不会真正执行，而是只返回执行计划
explain analyze SQL：与HoloWeb中的运行分析相同，该语句会真正执行，并且返回详细执行过程

返回结果的解读

为了方便讲解，以下图为例进行explain analyze SQL产出的执行结果的解读，返回结果如下所示：

调优8.png

执行节点：执行计划为多组执行节点构成。每组执行节点包含一个执行命令和一个node执行信息。解读如下：

128:1：该节点使用128个Worker节点并行执行，产出数据由1个Worker节点接收。因为此处为Gather，也就是结果接收返回节点，所以永远是1Worker节点
row_count: 返回行数
open_time: 分为min、avg、max，标识Worker节点准备数据开始执行的耗时
get_next_time：范围min、avg、max，标识Worker节点拉取并处理数据的耗时

并行执行：每一级执行节点都有一个箭头与之对应，当箭头处于同一级别时，意味着节点在并行执行
叶子节点：叶子节点是数据最初的拉取节点，当设置索引时，可以查看是否命中索引。命中索引的过滤逻辑耗时极短
耗时概述：3个阶段的耗时展示。并且可以通过增加set参数打印详细node执行信息

3.1.3 执行节点类型详解

通过上面的介绍，已经掌握了执行计划的查看方法，下面介绍每个执行节点的类型与含义，如下所示：

节点类型	节点触发	节点描述
数据访问算子	产生于叶子节点来源于where中的过滤条件如in, >, <, = 等	Seq Scan：顺序遍历，性能较差 Index Scan：索引遍历，命中索引 ......
链接算子	来源于Left Join, Right Join, Join 根据on的字段完成表链接	Hash Join: 哈希散列链接 Nested Loop Join：循环嵌套链接 Sort Merge Join：排序合并链接
聚合算子	产生于GROUP BY，配合聚合函数使用	Hash Aggregate：根据字段产出哈希值进行运算 Streaming Aggregate: 流式聚合，当前暂未遇到
数据移动算子	由数据传递过程逻辑发生，如嵌套查询、数据Join、数据聚合等，会在上述算子执行前后发生	Redistribute Motion: 数据重分布，重要的耗时算子，Join字段、聚合字段与表设置分布列字段不匹配，导致数据重新整合分配 Broadcast Motion：数据广播，重要的耗时算子，数据扫描了全部Worker节点进行读 Gather：数据读取，一般出现在select最外层，需要将Worker节点里面结算结果返回给master透出
其他算子	用户使用的不同关键字触发	Sort，Limit，Append等

可以根据已有知识预知SQL执行计划，与真实执行结果作对比，找到不符合预期的节点，才能进一步进行优化

3.2 执行性能优化

执行性能通俗而言就是执行时间较长，根据上述内容所知，耗时点主要出现在三个步骤：生成执行计划（Optimization Cost）、启动任务（Start Query Cost）、执行任务（Get Result Cost）。下文从3个部分分别介绍优化点和优化方案

3.2.1 执行计划生成的优化

正常执行计划生成一般在毫秒级别，说明执行计划生成存在问题(黄色 >> 橙色)

如上图所示，执行计划生产时间占比较高，原因有以下几点：

1、执行计划不准确

描述：对于新导入Hologres的数据表和持续更新的数据表，Hologres的执行计划产生有时并非准确。如分布列正确但产生了重分布、大表Join小表时大表进行了重分布、执行计划生成就是慢、执行计划不符合预期等等
优化方案：对表进行analyze，详情见文档Analyze。通过MaxCompute导入Hologres的数据表完毕后进行一次analyze。或者配置DataWorks调度，将持续插入数据的表进行定时analyze。analyze语句不会阻塞任务。库存占量表会持续写入下单占量信息，该表每1小时进行一次analyze

analyze predict_base_table;

2、使用大量外表

描述：在广告库存管理系统中使用了达摩盘人群数据（即用户id与人群包id的映射数据）。该数据是数据引擎团队维护的人群标签、人群包相关的数据，通过Hologres建立MaxCompute外表实现的。大量的外表访问会导致执行计划生成以及后续执行较慢
优化方案：人群数据缓存，即外表导入Hologres后使用。建一张本库的Hologres数据表，将第一次使用的人群数据插入到该表中。后续如果重复使用该人群时，将直接读取本库中的该数据表，避免使用外表
本质：空间换时间。(Hologres存储空间使用盘古文件，完全足够使用)

3、执行计划算法不匹配

描述：在Join逻辑较多，数据量较大时，会出现执行计划较慢的情况，主要原因是当前版本执行计划生产算法适配当前场景较差，后续版本会进一步优化。一般情况不需要调整
说明：Hologres的匹配算法有三种：Query、greedy、exhaustive

Query: 严格的按照编写的Join顺序进行执行。
greedy：贪心算法，通过贪心遍历Join逻辑产出执行计划。产出较快，但当前无法适配全部SQL，会导致部分SQL执行计划无法生成，建议使用JDBC 连接级别并进行人工验证。
exhaustive：动态规划，也是Hologres默认算法。产出一般较快，但是在询量业务场景存在耗时较长的情况，但可以适配全部SQL，生成的执行计划执行时间较短

优化方案：手动修改执行计划生成算法
使用建议：这种是属于比较高级的用法，可以先联系Hologres同学再尝试修改。

--数据库级别修改算法的参数，生效后数据库默认算法被调整ALTER DATABASE db_name set optimizer_join_order=exhaustive;--链接级别修改算法的参数，当前jdbc链接断开，参数失效set optimizer_join_order=exhaustive；

3.2.2 启动任务的优化

顾名思义，任务启动耗时远高于生成执行计划和执行时长。(橙色 >> 黄色 ≈ 黑色)

这意味着大量时间都用于等待资源而未启动任务

调优10.png

资源不足

描述：当执行的SQL较多且TG设置的shard较多时，部分SQL会因为无法获取资源而进行等待。可以通过HoloWeb中的诊断能优化，查看活跃Query数量与开始时间。如果SQL较多且每个SQL执行耗时都很短，则可明确是资源不足。如下图所示

调优11.png

优化方案: 实例扩容或调整TG的shard count。注意TG的shard count不建议轻易调整，建议先咨询Hologres同学再进行调整
使用业务：全部业务线。双11当天将集群cu资源翻倍，确保可提高并行执行SQL的数量

3.2.3 执行任务的优化

正常的任务执行耗时如下所示(橙色 ≈ 黄色 << 黑色)。根据执行计划的分析，我们可以精准定位到问题所在，并进行逐一解决。主要优化点有两个：内存消耗、时间消耗。如下所示

调优12.png

多表Join的重分布(Redistribute Motion)

描述：对于优酷询量场景，有非常多(近20个定向条件)，而且并非简单的过滤，所以会导致大量的子表Join。这些子表字段不一，完全无法适配我们所描述的分布列统一的要求。即A Join B Join C Join D没有公共交集字段。这样会导致大量的重分布。上亿数据的重分布导致大量耗时
优化方案：

Join顺序调整：为了加速执行计划生成，将表Join顺序由大到小按顺序调整
子表合并：将各个定向条件子表的字段统一，即添加冗余字段，让多张子表合并为一张，减少Join次数。品牌优酷合并了各个维度的定向系数表，从而减少耗时
维表变参数：某些维表非常小(50-100行)，强行Join会导致数据广播(Broadcast Motion)，导致节点耗时急剧增加。于是将该数据载入应用内存，在JDBC提交SQL是通过参数传递进来使用

结果：该SQL性能提升5倍，内存消耗降低10倍

2、Unnest与Array

描述：在品牌优酷业务中使用系数占比范式进行库存余量，于是在库存占用表中存放的并不是明细数据，而是竞争订单列表。即存放的数据类似类型，有一个Array字段。而后续计算时，通过unnest进行打散使用。该计算方式在执行计划中触发重分布，且内存消耗较大
优化方案：

Array与unnest去除：将系数表中的Array打散成N行，并通过添加索引的方式能够快速定位。即存储换性能，Hologres对大量数据的unnest操作压力较大
增加冗余列：根据业务逻辑，打散后补充version=0的无效值，虽然增加了存储，但是提高了计算性能

结果：该SQL性能提升2倍

3、节点内存膨胀

描述：根据执行计划，发现某个节点输入数据量突然膨胀，产出后数据量急剧减少。在品牌优酷业务中，需要将订单间的竞争关系打散，即A X B的类似笛卡尔积的操作，会导致内存的急剧膨胀。并且随着订单数量的增多而OOM
优化方案：

前置过滤：在每张表读取的时候首先进行一次过滤，然后嵌套该select结果进一步使用
稀疏数据压缩：由于优酷本身的业务逻辑，定向条件众多但未必全部选择。于是发现存储的维度中大量数据为无效值，导致内存中加载了大量无效数据。由于该竞争系数表是10亿级别的数据量，所以需要对该冗余字段调整。删除该字段并调整表结构，解决内存问题
预计算：由于竞争系数范式需要将全部订单的竞争关系产出，所以历史订单计算可以通过提前计算完毕存储进来使用的方法完成，提升单次询量的计算量

结果：该SQL性能提升1倍，内存消耗降低10倍

四、优化结果展示

由于询量业务计算复杂，数据量较大，一次请求包含了10+个SQL，并配合应用内部做逻辑处理，所以总计耗时较长。最终的优化结果也并不是某一个优化点能产出的，上述全部内容均为本次优化的细节。

4.1 双11业务指标展示

4.1.1 场景1：品牌特秀

优化结论

平均耗时：40s -> 25s，耗时降低35%

极端case:

15人群包: 400s -> 60s，耗时降低85%

6亿人群: 400s -> 110s，耗时降低72%

4.1.2 场景2：品牌优酷

品牌优酷由于定向条件复杂(20+)，资源位众多，导致计算逻辑更繁琐，数据量也达到百亿规模

优化结论

平均耗时：

开机图: 300s->110.3s，耗时降低63%

全域星：167s->98.6s，耗时降低41%

内存消耗：

单并行计算：700G->14G，消耗降低98%

4.1.3 场景3:品牌搜索询价

品牌搜索询价依托平台能力升级，使用并行调度内核之后，复杂的询价策略节点间可并行执行，提高了计算性能

平均耗时：488s -> 310s，耗时降低36%

4.2 双11业务单SQL耗时优化展示

调优13.png

总结

系统的性能优化永远是一个精益求精的过程，永无止境，既是技术深度的增加也是业务理解的提高。根据计算引擎实现细节，配合业务逻辑的适配才能达到最终的优化效果。

在阿里妈妈库存管理系统的全链路优化项目中，HologresSQL只是其中一个环节，还有各种其他应用层面的优化并未在此列出，是多个团队合作的最终成果。

本文借此项目，讲述了较为基础的HologresSQL优化手段和方案，期望大家可以运用到自己的业务中，节省学习成本，提升系统能力。

如果未来有好的建议或有相关疑问，欢迎与本人沟通交流，共同学习，共同进步~

了解Hologres：https://www.aliyun.com/product/bigdata/hologram

使用实践｜Hologres性能调优全方位解读