共计 3181 个字符，预计需要花费 8 分钟才能阅读完成。
背景
目前市面上大数据查问剖析引擎层出不穷，如 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.id
FROM ta
LEFT JOIN tb
ON 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_table
where dt = '20221015'
order by amt
limit 500
;
-- 改良脚本
SELECT *
FROM user_pay_table
WHERE dt = '20221015'
DISTRIBUTE BY ( CASE
WHEN amt < 100 THEN 0
WHEN amt >= 100 AND age <= 2000 THEN 1
ELSE 2
END )
SORT BY amt
LIMIT 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…
点击立刻收费试用云产品 开启云上实际之旅！
原文链接
本文为阿里云原创内容，未经容许不得转载。