基于Tez引擎的 Hive SQL 性能优化 —— 为什么脚本运行越来越慢

目录

• 基于Tez引擎的 Hive SQL 性能优化 —— 为什么脚本运行越来越慢
  • 背景
  • 分析 SQL 执行计划
  • 查看表统计信息
  • 优化和改写 SQL 脚本
  • 总结

基于Tez引擎的 Hive SQL 性能优化 —— 为什么脚本运行越来越慢

背景

一年前为业务部门分析离线数据开发了一个 Hive SQL 脚本，脚本早期运行耗时也就10分钟左右，但最近用户反馈脚本性能变慢每次都要一个多小时才能计算完，甚至会超时报错，Hive 脚本运行时间超过2小时实例就会被杀掉回收集群资源，防止集群资源被长期占用。于是便有本篇优化记录。

分析 SQL 执行计划

原 SQL 脚本有两个变量，一个过滤数据期间月份，一个过滤组织范围，替换变量后使用explain查看SQL的执行计划。

-- SQL 语句仅保留优化分析相关字段
select
    t.col1,
    t.col2,
    t.col3,
    ...
from base_detail_table t
join user_config_period p
    on t.period = p.period
join (
    select
        a.map_col1,
        a.map_col2,
        a.map_col3,
        ...
    from user_config_mapping a
    left join dm_dim_account b
        on a.code = b.code
    ...
) mp
    on if(mp.map_col1 = 'ALL', true, mp.map_col1 = t.col1)
join dm_dim_org eo
    on if(mp.map_col2 = 'ALL', true, mp.map_col2 = eo.col1)
    and t.col2 = eo.col1
    and eo.col1 = 'ORG1'
...(省略其他关联)
where
    t.period = '20260901'

1. CBO 优化后表关联出现笛卡尔积

从前几行可以看出 Hive 是使用 CBO 对 SQL 进行优化，而优化结果出现2段笛卡尔积边(XPROD_EDGE)，其他的都是广播边(BROADCAST_EDGE)，在原SQL中没有 cross join 之类的关联语句。

几种常见的边类型

边类型	说明
SIMPLE_EDGE	shuffle 边，按 key 分组
BROADCAST_EDGE	广播边，表所有数据会复制到后续 顶点(Vertex) 的内存中
CUSTOM_SIMPLE_EDGE	使用 count(1) 等函数会出现
XPROD_EDGE	笛卡尔积边，常见 cross join

2. 第1段笛卡尔积表关联分析

在执行计划中 Vertex Map5 + Map7 通过笛卡尔积关联生成 Reducer6 的数据，

user_config_period 表和主表使用 period 进行内关联，CBO 优化谓词下推后关联条件退化为常量 ( t.period = p.period --> p.period = '20260901' )，没有了等值关联配置表被编排去和 mp 子查询 cross join，数据行row估算为 3千万 (29720019 = 1415239 × 21)，数据超过广播阈值转成 shuffle。

3. 第2段笛卡尔积表关联分析

第2段出现的地方是 Map 1 join Reducer 6 即主表(base_detail_table)关联前面第1段笛卡尔积生成的结果数，这一段关联的预估行数进一步膨胀到 1480亿 (148002514577967 = 29720019 × 4979893)。

引起这个现象是 on if(mp.map_col1 = 'ALL', true, mp.map_col1 = t.col1) (截图中标记2的位置)关联条件CBO优化时无法提取等值关联，只能先全关联shuffle之后再 Filter 过滤数据(截图中标记3的位置)。

查看表统计信息

SQL 数据逻辑由一张主表 + 几张维表 / 配置表组成，主表数据量大概3亿+行，配置表数据量一般只有几十到几千行。观察统计信息发现配置表 numFiles、numRows、rawDataSize、totalSize 等几个参数都是0，配置表是使用 dataX 工具将数据写入集群 HDFS 文件，Hive Metastore 组件没有维护相关的统计信息。

对比主表和配置表截图，配置表的统计信息中行数等信息都为0，是影响 Hive CBO 优化结果的一部分因素。

<主表统计信息截图>

<配置表统计信息截图>

优化和改写 SQL 脚本

结合上面分析执行计划和查看表统计信息发现的问题，需要做两方面的优化：

1. 统计表信息

在同步配置表数据后增加调度任务收集表统计信息

analyze table <TABLE_NAME> compute statistics;

2. 消除笛卡尔积

原 SQL 是由多段查询 union 本次仅展示其中一段作为典型分析，根据上面的分析结论优化这段 SQL 目的是消除潜在的笛卡尔积关联。首先将子查询独立出来用临时表保存，其次被 CBO 转换为全关联的 user_config_period 表改用 exists 句式。修改后的 SQL 如下展示。

-- SQL 语句仅保留优化分析相关字段
create temporary table user_config_mapping_temp
as
select
    a.map_col1,
    a.map_col2,
    a.map_col3,
    eo.col1 as map_col4,
    ...
from user_config_mapping a
left join dm_dim_account b
    on a.code = b.code
join dm_dim_org eo
    on (mp.map_col2 = 'ALL' or mp.map_col2 = eo.col1)
    and eo.col1 = 'ORG1'
...
;

select
    t.col1,
    t.col2,
    t.col3,
    ...
from base_detail_table t
join user_config_mapping_temp mp
    on (mp.map_col1 = 'ALL' or mp.map_col1 = t.col1)
    and t.col2 = mp.map_col4
...(省略其他关联)
where
    t.period = '20260901'
and exists (
    select 1
    from user_config_period p
    where t.period = p.period
)

经测试，优化后 SQL 执行计划没有再出现笛卡尔积边(XPROD_EDGE)，脚本运行耗时稳定在10分钟以内。

总结

很多时候系统数据量处于较低水平开发 SQL 之后不会立马发现性能问题，随着系统数据量逐渐增加之前潜藏的问题就会慢慢暴露出来。现在的开发环境往往都是量大任务重，一般完成开发后数据能跑出来对得上就得紧接着开发下个需求，很少开发人员会有多余时间做性能上的检查，而且系统前期没有数据量的情况下也不一定能检查出来。这些注意事项都是要随时间和事件(经验)积累，才能在开发时避免。希望这篇文章也能成为读者的经验积累。