丨目录：
· 写在最前
· 数据表的构建优化
· 数据的查询优化
· 优化结果展示
· 优化总结
· 关于我们
▐ 写在最前1、本文概述
阿里妈妈品牌合约广告，在2021年广告售卖策略发生变化，大促订单量为历史的至少3倍以上。为了保证运营同学的大促下单体验，广告库存管理系统急需性能优化。经过一系列优化后，最终在双11前完成计算性能5-10倍的提升。
系统底层计算引擎为Hologres，通过SQL完成大部分计算逻辑。本文结合阿里妈妈广告库存管理系统，分享Hologres数据库在数据表建立、数据表查询、数据表运维三个阶段的分析与优化方法。
2、阿里妈妈广告库存管理系统简介互联网广告业务初始阶段，拥有流量的媒体与广告资源的代理商是市场的主要参与者。线下广告的业务逻辑被照搬到线上，由广告代理公司和媒体签订协议，确保某些广告位在某时间段被指定广告商占有，同时广告商按整体合同支付广告费用，也就是早期的CPT合约广告。
为了提升广告投放效果，需要对广告进行更精细化的售卖，于是合约式广告重点发展为CPM计费的展示量合约广告，俗称"CPM广告"。展示量合约指的是约定某种受众条件下的展示量，然后按照事先约定好的单位展示量价格来结算。这种合约又被称为担保式投送即GD。实际执行中，在未能完成合约中的投放量时，可能要求媒体承担一定的赔偿。
广告库存管理系统是阿里妈妈合约保量广告能力实现的重要系统之一，主要负责广告库存的分配。在展示量合约广告中，库存中存放的是预估的未来N天的广告的展示量信息。广告主通过询量(库存余量查询)与锁量(库存余量锁定)来确认并购买所需的广告量完成下单，后续投放引擎精准的按时按量完成广告投放。
由于广告库存数据量庞大且定向条件众多，导致单次计算量较大，在亿到百亿级别。由于下单请求量极大，尤其是大促期间出现井喷，当天可达到数十万，所以单次请求耗时需要稳定在秒级别，才能保证用户良好的下单体验。询量计算建模分为两种：细粒度占量计算建模和竞争系数计算建模。
细粒度占量计算：计算逻辑相对简单，适用于定向少，单次计算量在千万到亿级别，不支持库存腾挪的场景。
竞争系数计算：定向条件众多，单次计算量在十亿级别，支持库存腾挪，可配合二部图求解最大化库存。
3、Hologres的简介Hologres是阿里巴巴自主研发的一站式实时数仓产品，支持高并发和低延时地分析处理PB级数据，并首次提出HSAP（Hybrid Serving/Analytical Processing）服务分析一体的理念。在实时数据的高频点写、点查时，bulkload导入和分析性查询依然能够保证其稳定性与准确性。具体细节可详见：
Hologres官方使用文档：
https://help.aliyun.com/product/113622.html
Hologres实现原理解析：
https://zhuanlan.zhihu.com/p/360750135
链接工具Holoweb：
https://holoweb.dw.alibaba-inc.com
一、数据表的构建优化Hologres的快速查询与数据表构建方式强相关，使用的合理表设计不仅能够提高性能，并且可以节约计算和存储的成本。广告库存管理表非常复杂，定向和计算逻辑较多，数据量在亿级别。
为了方便理解，结合业务逻辑将库存管理的计算流程进行抽象化简，用最基础最通用的方式，举例完成全程的优化细节讲解。细粒度占量方式和单次总量余量库存相关计算如下图所示：
库存预估表：预估未来每天每个资源位广告请求量的明细表，天级别全表更新。
库存占量表：广告主下单占用的库存的明细表。每次在BP的下单操作会将锁量数据写入。
库存余量计算：根据过滤条件，筛选库存预估明细中未被占用的总量。
1.1 表组的构建表组又称Table Group，简称TG。表组是Hologres重要理念之一，主要用于指定单次计算的执行并行度，即shard count(分片的数量)。Hologres的shard本质上完成了数据“分库分表”的效果，所有的shard并行执行。强相关的表在建立时指定相同的表组，数据在分布时会根据设置的字段值构建在一起，在执行计算时直接Local Join来加速计算。
询量业务在同一数据库中有多个业务线，如品牌特秀、品牌优酷等。计算量与性能要求不同，需要表组隔离。
-- 创建 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需要重建表组，重建关联表。
1.2 数据表的建立由上图所示，构建两张表：库存预估表和库存占量表。两张表通常会绑定使用，为了高效的计算，需要在数据表建立时指定相关参数才能发挥Hologres全部性能。
1.2.1 表存储类型选取主要分为行存和列存两种存储类型(行列混合存在最新版本支持，在此不进行讨论)：
行存：主要用于点查整行的场景，高QPS的依照主键的查询。Blink/Flink的维表和结果表必须使用行存。
列存：主要用于多列的分析型复杂关联查询，如Join、GROUP BY等。
1.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。
1.2.3 建表SQL的执行最终建表语句如下所示：
--库存预估表(简化)BEGIN;CREATE TABLE public.predict_base_table ( session_id text NOT NULL, adzone_id bigint NOT NULL, pv double precision, target_date bigint NOT NULL);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;CREATE TABLE public.locked_pv_table ( session_id text NOT NULL, target_date bigint NOT NULL, business_id bigint NOT NULL, product_priority bigint NOT NULL);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 IS NOT NULL) WHEN TRUE THEN pv ELSE 0 END ) AS locked_pv_numFROM ( SELECt base_pv.adzone_id ,base_pv.target_date ,pv * pv_ratio AS pv ,ecpm2 AS cost ,locked_pv.session_id AS lock_pv_session ,locked_pv.member_id AS member_id FROM predict_base_table base_pv LEFT JOIN locked_pv_table locked_pv ON base_pv.session_id = locked_pv.session_id AND base_pv.target_date = locked_pv.target_date WHERe base_pv.adzone_id IN (123123) AND base_pv.target_date IN (20211227, 20211229, 20211228, 20211230, 20211231) ) aaGROUP BY target_date;
2.1 执行性能分析SQL分析方法分为两种：HoloWeb分析SQL，直接查看执行计划。分析的内容主要包含：内存占用、耗时分布、执行计划合理性等。
2.1.1 HoloWeb诊断分析在Hologres更新到0.10版本后，HoloWeb提供了强大的SQL分析功能，可以让刚入门的同学快速上手完成SQL的基本分析。
查看历史慢SQL与耗时在HoloWeb的诊断与优化中查看历史慢SQL，可根据关键字查询对应的SQL。在右侧耗时分布可查看3阶段耗时。点击详情可查看具体耗时信息。
黄色部分：执行计划生成耗时(Optimization Cost)
橙色部分：任务启动耗时(Start Query Cost)
黑色部分：执行获取结果信息耗时(Get Result Cost)
SQL执行计划分析找到需要优化的SQL，将其写在编辑器中，选择"运行分析'或'执行计划'。
运行分析查看（explain analyze）：会真实执行该SQL，并返回执行过程，产出更准确的分析结果。
执行计划查看（explain）：不执行SQL，只查看生成执行计划，产出信息为预估结果。
执行结果中可按照内存、耗时查看不同面板，并且查看每个节点的数据量(行数)的传递。
也可以直接查看执行计划面板，查看文字版本。
2.1.2 手动查看执行计划虽然HoloWeb可视化了执行计划，并做了非常友好的节点流程图，适合初步接触Hologres同学使用，但详细参数还是需要手动执行SQL来查看。手动查看执行计划方式并不复杂，只需要在执行的SQL前面增加关键字：explain 或 explain analyse。
explain SQL：与HoloWeb中的执行计划结果相同，该语句不会真正执行，而是只返回执行计划。
explain analyze SQL：与HoloWeb中的运行分析相同，该语句会真正执行，并且返回详细执行过程。
返回结果的解读为了方便讲解，以下图为例进行explain analyze SQL产出的执行结果的解读，返回结果如下所示：
执行节点：执行计划为多组执行节点构成。每组执行节点包含一个执行命令和一个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执行信息。
节点类型详解通过上面的介绍，已经掌握了执行计划的查看方法，下面介绍每个执行节点的类型与含义，如下所示：
2.2 执行性能优化耗时点主要出现在三个步骤，并进行逐一优化：
生成执行计划（Optimization Cost）
启动任务（Start Query Cost）
执行任务（Get Result Cost）
2.2.1 执行计划生成的优化正常执行计划生成一般在毫秒级别，说明执行计划生成存在问题(黄色 >> 橙色)。
如上图所示，执行计划生产时间占比较高，原因有以下几点：
执行计划不准确
描述：对于新导入Hologres的数据表和持续更新的数据表，Hologres的执行计划产生有时并非准确。如分布列正确但产生了重分布、大表Join小表时大表进行了重分布、执行计划生成就是慢、执行计划不符合预期等等。
优化方案：对表进行analyze。通过MaxCompute导入Hologres的数据表完毕后进行一次analyze。或者配置DataWorks调度，将持续插入数据的表进行定时analyze。analyze语句不会阻塞任务。库存占量表会持续写入下单占量信息，该表每1小时进行一次analyz。
附文档Analyze：
https://help.aliyun.com/document_detail/216943.html
analyze predict_base_table;
使用大量外表
描述：在广告库存管理系统中使用了达摩盘人群数据（即用户id与人群包id的映射数据）。该数据是数据引擎团队维护的人群标签、人群包相关的数据，通过Hologres建立MaxCompute外表实现的。大量的外表访问会导致执行计划生成以及后续执行较慢。
优化方案：人群数据缓存，即外表导入Hologres后使用。建一张本库的Hologres数据表，将第一次使用的人群数据插入到该表中。后续如果重复使用该人群时，将直接读取本库中的该数据表，避免使用外表。
本质：空间换时间。(Hologres存储空间使用盘古文件，完全足够使用)。
执行计划算法不匹配
描述：在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；
2.2.2 启动任务的优化顾名思义，任务启动耗时远高于生成执行计划和执行时长。这意味着大量时间都用于等待资源而未启动任务。
资源不足
描述：当执行的SQL较多且TG设置的shard较多时，部分SQL会因为无法获取资源而进行等待。可以通过HoloWeb中的诊断能优化，查看活跃Query数量与开始时间。如果SQL较多且每个SQL执行耗时都很短，则可明确是资源不足。如下图所示：
优化方案：实例扩容或调整TG的shard count。注意TG的shard count不建议轻易调整，建议先咨询Hologres同学再进行调整。
使用业务：全部业务线。双11当天将集群cu资源翻倍，确保可提高并行执行SQL的数量。
2.2.3 执行任务的优化正常的任务执行耗时如下所示。根据执行计划的分析，我们可以精准定位到问题所在，并进行逐一解决。主要优化点有两个：内存消耗、时间消耗。
多表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倍。
Unnest与Array
描述：在品牌优酷业务中使用系数占比范式进行库存余量，于是在库存占用表中存放的并不是明细数据，而是竞争订单列表。即存放的数据类似 类型，有一个Array字段。而后续计算时，通过unnest进行打散使用。该计算方式在执行计划中触发重分布，且内存消耗较大。
优化方案：
Array与unnest去除：将系数表中的Array打散成N行，并通过添加索引的方式能够快速定位。即存储换性能，Hologres对大量数据的unnest操作压力较大。
增加冗余列：根据业务逻辑，打散后补充version=0的无效值，虽然增加了存储，但是提高了计算性能。
结果：该SQL性能提升2倍。
节点内存膨胀
描述：根据执行计划，发现某个节点输入数据量突然膨胀，产出后数据量急剧减少。在品牌优酷业务中，需要将订单间的竞争关系打散，即A X B的类似笛卡尔积的操作，会导致内存的急剧膨胀。并且随着订单数量的增多而OOM。
优化方案：
前置过滤：在每张表读取的时候首先进行一次过滤，然后嵌套该select结果进一步使用。
稀疏数据压缩：由于优酷本身的业务逻辑，定向条件众多但未必全部选择。于是发现存储的维度中大量数据为无效值，导致内存中加载了大量无效数据。由于该竞争系数表是10亿级别的数据量，所以需要对该冗余字段调整。删除该字段并调整表结构，解决内存问题。
预计算：由于竞争系数范式需要将全部订单的竞争关系产出，所以历史订单计算可以通过提前计算完毕存储进来使用的方法完成，提升单次询量的计算量。
结果：该SQL性能提升1倍，内存消耗降低10倍。
三、优化结果展示由于询量业务计算复杂，数据量较大，一次请求包含了10+个SQL，并配合应用内部做逻辑处理，所以总计耗时较长。最终的优化结果也并不是某一个优化点能产出的，上述全部内容均为本次优化的细节。
3.1 双11业务指标展示 3.1.1 场景1：品牌特秀极端case:
15人群包: 400s -> 60s，耗时降低85%
6亿人群: 400s -> 110s，耗时降低72% 3.1.2 场景2：品牌优酷
品牌优酷由于定向条件复杂(20+)，资源位众多，导致计算逻辑更繁琐，数据量也达到百亿规模。
开机图: 300s->110.3s，耗时降低63%
全域星：167s->98.6s，耗时降低41%
内存消耗：
单并行计算：700G->14G，消耗降低98% 3.1.3 场景3：品牌搜索询价
品牌搜索询价依托平台能力升级，使用并行调度内核之后，复杂的询价策略节点间可并行执行，提高了计算性能。
平均耗时：488s -> 310s，耗时降低36%
3.2 双11单SQL耗时优化展示 四、优化总结系统的性能优化是一个精益求精的过程，永无止境，既是技术深度的增加也是业务理解的提高。根据计算引擎实现细节，配合业务逻辑的适配才能达到最终的优化效果。
在阿里妈妈库存管理系统的全链路优化项目中，HologresSQL只是其中一个环节。本文借此项目，分享了较为基础的HologresSQL优化手段和方案，希望大家可以运用到自己的业务中，节省学习成本，提升系统能力。
▐ 关于我们我们是阿里妈妈业务引擎工程团队，致力于打造新一代智能化、Serverless化的广告业务引擎。我们的工作涉及搜索/展示/内容广告引擎、广告索引和策略数据链路、智能创意制作和投放引擎等，欢迎感兴趣的伙伴加入。
投递简历邮箱:
alimama_tech@service.alibaba.com
END
也许你还想看
丨面向数智营销的 AI FAAS 解决方案
丨广告深度学习计算：异构硬件加速实践
丨广告深度学习计算：召回算法和工程协同优化的若干经验
欢迎关注「阿里妈妈技术」，了解更多~
疯狂暗示↓↓↓↓↓↓↓