MaxCompute 笛卡尔积逻辑的参数优化&复杂JOIN逻辑优化

最新推荐文章于 2024-08-13 07:15:00 发布

阿里云技术

最新推荐文章于 2024-08-13 07:15:00 发布

阅读量882

点赞数 1

文章标签： odps java 数据库大数据 MaxCompute

本文链接：https://blog.csdn.net/weixin_43970890/article/details/127263639

版权

本文介绍了在MaxCompute（ODPS）中遇到的笛卡尔积逻辑参数优化和复杂JOIN逻辑优化问题。针对笛卡尔积导致的数据倾斜，提出了调整参数如`odps.sql.mapper.split.size`和`odps.sql.reducer.instances`等的解决方案。对于复杂的JOIN逻辑，通过理解原始逻辑，提出使用窗口函数替代JOIN或优化JOIN类型（如将左JOIN替换为内JOIN并利用mapjoin）的方法。此外，还强调了脚本优化中避免重复INSERT INTO和利用WITH语句提高代码可读性的要点。

摘要由CSDN通过智能技术生成

1. 优化概述

最近协助一个项目做下优化任务的工作。因为主要数据都是报表，对数对的昏天暗地的不敢随便调整SQL逻辑，所以本身只想做点参数调整，但是逼不得已后来还是改了一下SQL。

这篇文章主要讲一个SQL优化反映的两个优化点。分别是：

一、笛卡尔积逻辑的参数优化。

二、一个复杂JOIN逻辑的优化思路。

2. 笛卡尔积逻辑参数优化

早期版本MaxCompute（ODPS）好像对笛卡尔积的支持并不友好，对笛卡尔积逻辑检测很严格，但是现在的版本就比较正常了，在项目里最近这段时间遇到了2个性能问题都是这种笛卡尔积导致的。

笛卡尔积会造成数据量的膨胀，如果是一些数据量是几千几万的小表的一些关联，好像大家也体会不到什么性能问题，不过如果某个表是几千万、几亿这个规模就可能导致单个数据处理的WORKER超出其预估，导致SQL运行时间超出预期。

ef="">2.1. 发现问题

一般对于特别去调优的场景，其实不好好看看输入输出的数据量，看看逻辑是不太好发现笛卡尔积的逻辑的。所以，一般是先从执行日志的问题着手。

如上图所示，很明显JOIN阶段有数据倾斜。J4_1_3_6_job_0阶段，10分钟只是执行了2个backups（我没有截全，其实时间更久）。

ef="">2.2. 对付倾斜的暴力参数方法

倾斜的核心逻辑是有个别WORKER处理的数据比其他WORKER要多数倍，并且超过了1个WORKER处理能力太多。一般我们的WORKER处理数据的时间在秒级是最常见的，但是到几分钟这种程度的倾斜一般也能接受。毕竟不倾斜的数据真实情况下很难遇到，但是有backups的场景就说明超出了这个WORKER的处理能力，这个程序居然跑挂了。然后又启动了一个WORKER，继续跑。

那这种情况，不管这个数据到底怎么倾斜，为什么倾斜，最简单暴力的方法就是：

1-把单个WORKER处理的数据量变小；

-- 让map阶段的worker变多【默认256，值域1-最大值】

SET odps.sql.mapper.split.size = 16;

-- 让reduce阶段的worker变多【默认-1，动态；最大1111，手动最大9999】

SET odps.sql.reducer.instances = 99;

-- 让join阶段的worker变多【默认-1，动态；最大1111，手动最大9999】

SET odps.sql.joiner.instances = 200;

注意：一个worker至少占用1core的CPU，如果集群不够大，或者分配给项目的资源有限，都会让执行的WORKER排队执行。再有，如果本来1个WORKER就能很快执行完的数据，你拆分成很多份，反而会让处理步骤更多，导致处理时间更慢。线上任务资源紧张的时候，要获取很多WORKER执行也会困难，导致长期的等待足够多的资源释放。所以，不是越大越好，最好是牛刀小试，浅尝即止。

2-让单个WORKER拥有更多的计算资源；

-- 让map阶段的CPU变多【默认100，值域50-800，官方认为没必要调整】

SET odps.sql.mapper.cpu = 800;

-- 让map阶段的内存变多【默认1024，值域256-12288，少量场景可调整】

SET odps.sql.mapper.memory