背景

目前市面上大数据查问剖析引擎层出不穷，如Spark，Hive，Presto等，因其敌对的SQL语法，被广泛应用于各畛域剖析，公司外部也有优良的ODPS SQL供用户应用。笔者所在团队的我的项目也借用ODPS SQL去检测业务中潜在的平安危险。在给业务方应用与答疑过程中，咱们发现大多含有性能瓶颈的SQL，次要集中在数据歪斜与数据收缩问题中。因而，本文次要基于团队理论开发教训与积攒，并联合业界对大数据SQL的应用与优化，尝试给出绝对系统性的解决方案。本文次要波及业务SQL执行层面的优化，暂不波及参数优化。若设置参数，首先确定执行层面哪个阶段（Map/Reduce/Join）工作执行工夫较长，从而设置对应参数。本文次要分为以下三个局部：第一局部，会引入数据歪斜与数据收缩问题。第二局部，介绍当数据歪斜与数据收缩产生时，如何排查与定位。第三局部，会从零碎层面给出常见优化思路。

问题篇

数据歪斜

数据歪斜是指在分布式计算时，大量雷同的key被散发到同一个reduce节点中。针对某个key值的数据量比拟多，会导致该节点的工作数据量远大于其余节点的均匀数据量，运行工夫远高于其余节点的均匀运行工夫，连累了整体SQL执行工夫。其次要起因是key值散布不均导致的Reduce解决数据不平均。本文将从Map端优化，Reduce端优化和Join端优化三方面给出相应解决方案。

数据收缩

数据收缩是指工作的输入条数/数据量级比输入条数/数据量级大很多，如100M的数据作为工作输出，最初输入1T的数据。这种状况不仅运行效率会升高，局部工作节点在运行key值量级过大时，有可能产生资源有余或失败状况。

排查定位篇

本节次要关注于业务SQL自身引起的长时间运行或者失败，对于集群资源状况，平台故障自身暂不思考在内。1.首先查看输出数据量级。与其余天相比有无显著量级变动，是否因为数据量级的问题人造引起工作运行工夫过长，如双11，双十二等大促节点。2.察看执行工作拆分后各个阶段运行工夫。与其余天相比有无显著量级变动；在整个执行工作中工夫耗时占比状况。3.最耗时阶段中，察看各个Task的运行状况。Task列表中，察看是否存在某几个Task实例耗时显著比均匀耗时更长，是否存在某几个Task实例解决输出/输入数据量级比均匀数据量级生产产出更多。4.依据步骤3中定位代码行数，定位问题业务解决逻辑。

优化篇

数据歪斜

1.Map端优化

1.1 读取数据合并

在数据源读取查问时，动静分区数过多可能造成小文件数过多，每个小文件至多都会作为一个块启动一个Map工作来实现。对于文件数量而言，等于 map数量 * 分区数。对于一个Map工作而言，其初始化的工夫可能远远大于逻辑解决工夫，因而通过调整Map参数把小文件合并成大文件进行解决，防止造成很大的资源节约。

1.2 列裁剪

缩小应用select * from table语句，过多抉择无用列会减少数据在集群上传输的IO开销；对于数据抉择，须要加上分区过滤条件进行筛选数据。

1.3 谓词下推

在不影响后果的状况下，尽可能将过滤条件表达式凑近数据源地位，使之提前执行。通过在map端过滤缩小数据输入，升高集群IO传输，从而晋升工作的性能。

1.4 数据重散布

在Map阶段做聚合时，应用随机散布函数distribute by rand()，管制Map端输入后果的散发，即map端如何拆分数据给reduce端（默认hash算法），打乱数据分布，至多不会在Map端产生数据歪斜。

2.Reduce端优化

2.1 关联key空值测验

局部实例产生长尾效应，很大水平上因为null值，空值导致，使得Reduce时含有脏值的数据被散发到同一台机器中。针对这种问题SQL，首先确认蕴含有效值的数据源表是否能够在Map阶段间接过滤掉这些异样数据；如果后续SQL逻辑依然须要这些数据，能够通过将空值转变成随机值，既不影响关联也能够防止汇集。

SELECT  ta.idFROM    taLEFT JOIN tbON      coalesce(ta.id , rand()) = tb.id;

2.2 排序优化

Order by为全局排序，当表数据量过大时，性能可能会呈现瓶颈；Sort by为部分排序，确保Reduce工作内后果有序，全局排序不保障；Distribute by依照指定字段进行Hash分片，把数据划分到不同的Reducer中；CLUSTER BY：依据指定的字段进行分桶，并在桶内进行排序，能够认为cluster by是distribute by+sort by。对于排序而言，尝试用distribute by+sort by确保reduce中后果有序，最初在全局有序。

-- 原始脚本select *from user_pay_tablewhere dt = '20221015'order by amtlimit 500;-- 改良脚本SELECT  *FROM    user_pay_tableWHERE   dt = '20221015'DISTRIBUTE BY ( CASE                   WHEN amt < 100                  THEN 0                   WHEN amt >= 100 AND age <= 2000 THEN 1                   ELSE 2                 END ) SORT BY amtLIMIT 500;

3.Join端优化

3.1 大表join小表

通过将须要join的小表散发至map端内存中，将Join操作提前至map端执行，防止因散发key值不平均引发的长尾效应，复杂度从（M*N）降至（M+N），从而进步执行效率。ODPS SQL与Hive SQL应用mapjoin，SPARK应用broadcast。

3.2 大表join大表

长尾效应由热点数据导致，能够将热点数据退出白名单中，通过对白名单数据和非白名单数据别离解决，再合并数据。

具体表现为打散歪斜key，进行两端聚合(针对聚合)或者拆分歪斜key进行打散而后再合并数据。

数据收缩

1.防止笛卡尔积

Join关联条件有误，表Join进行笛卡尔积，造成数据量爆炸。

2.关联key区分度校验

关注JoinKey区分度，key值区分度越低（distinct数量少），越有可能造成数据爆炸状况。如用户下的性别列，交易下的省市列等。

3.聚合操作误用

局部聚合操作须要将两头后果记录下来，最初再生成最终后果，这使得在select操作时，依照不同维度去重Distinct、不同维度开窗计算over Partition By可能会导致数据收缩。针对这种业务逻辑，能够将一个SQL拆分成多个SQL别离进行解决操作。

总结

大数据SQL优化是一项波及知识面较广的工作，除了剖析现有执行打算之外，还须要学习相应查问剖析引擎设计原理。针对咱们日常遇到的问题现总结分享给大家，供大家查阅。

参考资料：

ODPS SELECT语法：https://help.aliyun.com/document_detail/73777.html?utm_conten...

Presto Query Lifecycle：https://varada.io/blog/presto/accelerate-presto-trino-queries...

A Definitive Guide To Hive Performance Tuning- 10 Excellent Tips：https://www.hdfstutorial.com/blog/hive-performance-tuning/

Presto Performance: Speed, Optimization & Tuning：https://ahana.io/learn/presto-performance/

Hive Optimizing Joins：https://docs.cloudera.com/HDPDocuments/HDP2/HDP-2.0.0.2/ds_Hi...

点击立刻收费试用云产品 开启云上实际之旅！

原文链接

本文为阿里云原创内容，未经容许不得转载。