一次大表join倾斜优化实战

1. 背景介绍

在短视频/直播平台的数仓建设中，大表关联（JOIN）是常见的计算场景。以 vid_view_record_detail_di（用户视频播放记录表）和 vid_basic_info_df（视频基础信息表）的关联为例，两表数据量分别为：80亿+（播放记录表）、1亿+（视频基础信息表），通过视频ID（vid）进行关联获取视频基础信息时因数据倾斜导致作业性能劣化：

1、数据分布不均衡：头部热门视频（如爆款视频）的播放记录可能占据总数据的80%以上，引发关联时的热点Key问题；

2、资源浪费：部分Reduce节点因处理超量数据长时间未完成，其他节点空闲；

3、作业超时风险：倾斜导致任务卡在99%进度，甚至因单个节点OOM而失败。

传统方案（如直接增加Reduce并行度、mapjoin等）效果有限，而随机前缀优化法通过改造关联键分布，结合分治思想可有效解决倾斜问题。

2. 问题复现

2.1 表结构说明

-- 用户播放记录表（日增量80亿+）
CREATE TABLE vid_view_record_detail_di (
  vid BIGINT COMMENT '稿件ID', 
  user_id BIGINT COMMENT '用户ID',
  view_duration INT COMMENT '播放时长(秒)',
  dt STRING COMMENT '分区日期'
);

-- 视频基础信息表（全量维度表1亿+）
CREATE TABLE vid_basic_info_df (
  vid BIGINT COMMENT '稿件ID',
  author_id BIGINT COMMENT '作者ID',
  category STRING COMMENT '视频分类',
  publish_time TIMESTAMP COMMENT '发布时间'
);

2.2 倾斜的代码段

-- 视频播放记录关联视频维表补全视频基础信息（因vid倾斜导致性能问题）
SELECT 
  t1.vid,
  t1.user_id,
  t1.view_duration,
  t1.dt,
  t2.author_id,
  t2.category,
  t2.publish_time
FROM vid_view_record_detail_di t1
LEFT JOIN vid_basic_info_df t2
ON t1.vid = t2.vid
WHERE t1.dt = '2025-01-01'
GROUP BY a.category;

3. 根因分析

倾斜数据验证:

-- 检查vid_view_record_detail_di的Key分布
SELECT vid, COUNT(1) AS cnt 
FROM vid_view_record_detail_di 
WHERE dt = '2025-01-01'
GROUP BY vid 
ORDER BY cnt DESC 
LIMIT 1000;

• 结果示例：Top5 vid播放量与其他视频播放量存在明显断层，800w直接到60w

• 执行瓶颈定位：结合Spark/MapReduce作业日志显示：单个Reduce阶段处理耗时占比超过90%，其他reduce都在30min以内执行完成，剩下的reduce执行了2h都还没结束（此处不再贴图）
• 解决过程：尝试过参数调整、mapjoin（右表也很大，无法使用mapjoin）等措施均无效

4. 随机前缀解决方案

核心思想：通过打散热点Key，将热点key单reduce任务分治为多任务并行处理

4.1 过滤出热点key

此处需要定义一下热点key，统计播放记录表，根据播放量倒序，找出播放量存在明显断层的部分（以上述案例为例，确定播放量>=800w的视频为热点key）

-- 检查vid_view_record_detail_di的Key分布
SELECT vid, COUNT(1) AS cnt 
FROM vid_view_record_detail_di 
WHERE dt = '2025-01-01'
GROUP BY vid 
ORDER BY cnt DESC 
LIMIT 1000;

4.2 维表热点key扩容

仅对上述热点key进行扩容，扩容倍数怎么确定？

一般可以取热点key之外的视频播放量的一个中位数，比如是500000，然后用 最大播放量/中位数，即本例中：23500100/500000=47，取整数50，当然也可以不用这么取，盲拍一个倍数比方10、20等等都行，但要保证「最大播放量/倍数」的值不能太大，否则仍然可能倾斜。

好了，倍数确定后接下来就是数据扩容了：

1、维表扩容 

将热点key打标并扩容50倍：

-- 热点key打标
with vid_flag as (
    select
      t1.vid,
      t1.author_id,
      t1.category,
      t1.publish_time,
      if(t2.vid is not null, 1, 0) as is_hot_key
    from
      vid_basic_info_df as t1
    left join 
       (SELECT  --这里会转换为mapjoin
          vid,
          COUNT(1) AS cnt
        FROM vid_view_record_detail_di
        WHERE dt = '2025-01-01'
        GROUP BY vid
        having count(1) > 8000000
      ) as t2 
      on t1.vid = t2.vid
)
 
--热点key扩容,并添加1～50随机数（热点key扩容50倍，并与非热点key合并）
full_vid as (
select 
  concat_ws('_',t1.vid,FLOOR(RAND()*50)+1) as vid,
  t1.author_id,
  t1.category,
  t1.publish_time,
  t1.is_hot_key
from 
  (select * from vid_flag where is_hot_key=1) as t1
join 
  (SELECT posexplode(split(space(9), ' ')) AS (pos, dummy)) as t2   --涉及笛卡尔积，需要开启相关参数
union all 
select
  t1.vid,
  t1.author_id,
  t1.category,
  t1.publish_time
from vid_flag
where is_hot_key=0
)

2、播放记录表热点key随机前缀添加

--播放记录表热点key添加1～50随机前缀
view_record_rand as (
  select 
    if(t2.vid is not null,concat_ws('_',t1.vid,FLOOR(RAND()*50)+1),t1.vid) as vid,
    t1.user_id,
    t1.view_duration,
    t1.dt,
    if(t2.vid is not null, 1, 0) as is_hot_key
  from vid_view_record_detail_di as t1
  left join 
    (SELECT  --这里会转换为mapjoin
            vid,
            COUNT(1) AS cnt
     FROM vid_view_record_detail_di
     WHERE dt = '2025-01-01'
     GROUP BY vid
     having count(1) > 8000000
    ) as t2
  on t1.vid=t2.vid
  WHERE t1.dt = '2025-01-01'
)

3、处理后的数据关联&前缀去除

--热点key添加随机前缀的播放记录表 join 扩容后的维表,并去除随机前缀
select 
  split(t1.vid,'_')[1] as vid,   --注意不同引擎可能不一样，presto从1开始，hive/spark从0开始
  t1.user_id,
  t1.view_duration,
  t1.dt,
  t2.author_id,
  t2.category,
  t2.publish_time
from view_record_rand as t1
left join full_vid as t2
on t1.vid=t2.vid 

到此为止，就解决了大表x大表join数据倾斜问题，中间有个「计算热点key」在多个地方使用可写入一个临时表提升下性能（不过影响不大），经优化后，原来发生倾斜可能执行3个小时的任务，之后稳定在30min左右执行完，没有再出现过倾斜问题

有问题欢迎批评指正，大家一起交流～～